DB version: 11.2.0.4
Platform : Oracle Linux 6.5
Usually, I use the below script to generate DDL of tables. Today I was trying to generate the DDL for a Reference Partitioned table. But, when I execute DBMS_METADATA.GET_DDL from SQL*PLus, it can generate only part of the table's DDL. It just stops generating after line number 1125.
But, toad can easily generate the DDL for this and I believe toad uses DBMS_METADATA.GET_DDL internally.
Any workaround for this ?
I am providing the DDL to reproduce this issue.
This is the first time I am working with 'Reference Partitioning'.
In reference partitioning, whenever a partition is added to the 'parent' table, a corresponding partition will be created in the child table automatically with the same name. In 'Sample DDL to reproduce issue' section below when you add monthly partitions for 150 years in PARENT_TAB, corresponding partitions will be generated in CHILD_TAB too. Once the partitions are added , try to generate the DDL of CHILD_TAB using the below DBMS_METADATA.GET_DDL script. It will take roughly 3 minutes to add all the partitions provided by the partition generation script.
--- DDL extraction script start ---- This is the script I use to generate DDLs of tables
set long 32767 pages 0 lines 256
set trimspool on
column line format A254 WORD_WRAPPED
set sqlblanklines on
set feedback off
set tab off
set newpage none
spool ddl-table-CHILD_TAB.sql
exec dbms_output.enable(2000000);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
select replace(lower(dbms_metadata.get_ddl(object_type => 'TABLE', name => 'CHILD_TAB', schema => 'SCOTT')),'"',NULL) line from dual ;
set trimspool off
select chr(10)|| chr(10) from dual;
prompt ++++++++++ Index DDLs shown below ++++++++++++++
select chr(10)|| chr(10) from dual;
select replace(lower(dbms_metadata.get_ddl(object_type => 'INDEX', name => index_name , schema => 'SCOTT')),'"',NULL) line
from user_indexes
where table_name = 'CHILD_TAB';
spool off
--- DDL extraction script end ----
--Sample DDL to reproduce the issue
--- Parent table
CREATE TABLE parent_tab
(
ORDER_SEQ_ID NUMBER(18) CONSTRAINT NN_PROC_92_ORDER_SEQ_ID NOT NULL,
HIST_SEQ_ID NUMBER(18) CONSTRAINT NN_PROC_92_HIST_SEQ_ID NOT NULL,
HIST_PROCESS_ID NUMBER(9) NOT NULL,
HIST_POSITION_ID NUMBER(9),
HIST_ORDER_STATE_ID NUMBER(9) NOT NULL,
HIST_ORDER_STATUS_ID NUMBER(9) NOT NULL,
HIST_REPORTING_STATUS_ID NUMBER(9) NOT NULL,
TIMESTAMP_IN DATE NOT NULL,
TIMESTAMP_OUT DATE NOT NULL,
TASK_TIMESTAMP_IN DATE NOT NULL,
USERNAME VARCHAR2(32 CHAR) NOT NULL,
AUTHOR VARCHAR2(32 CHAR) NOT NULL,
TRANS_TYPE CHAR(1 BYTE) NOT NULL,
TASK_ID NUMBER(9) NOT NULL,
TASK_TYPE VARCHAR2(20 BYTE) NOT NULL,
COMPLETION_DATE_EXPECTED DATE,
COMPL_EXPECTED_ID NUMBER(9),
COMPL_EXPECTED_TYPE VARCHAR2(20 BYTE),
PARENT_HIST_SEQ_ID NUMBER(18),
ORDER_NODE_INDEX NUMBER(18),
TASK_ORDER_NODE_ID NUMBER(9),
TASK_ORDER_NODE_TYPE VARCHAR2(20 BYTE),
TERM_HIST_MAP_SEQ_ID NUMBER(18),
EFFECT_HIST_MAP_SEQ_ID NUMBER(18),
CARTRIDGE_ID NUMBER(6) NOT NULL,
COORD_SET_ID NUMBER(18),
ROLLBACK VARCHAR2(1 BYTE),
CONTEXT_ID NUMBER(18),
EXECUTION_MODE VARCHAR2(1 BYTE) NOT NULL,
BASE_HIST_SEQ_ID NUMBER(18),
COMPENSATOR_HIST_ID NUMBER(18),
COMPENSATION_ROOT_HIST_ID NUMBER(18),
COMPONENT_NODE_INDEX NUMBER(18),
COMPENSATION_STAGE VARCHAR2(20 BYTE)
)
PARTITION BY RANGE(TIMESTAMP_IN)
(
PARTITION orders_jan2016 VALUES LESS THAN(TO_DATE('01/FEB/2016','DD/MON/YYYY'))
);
alter table parent_tab add CONSTRAINT pk_parent_tab PRIMARY KEY (ORDER_SEQ_ID, HIST_SEQ_ID) ;
CREATE TABLE child_tab
(
ORDER_SEQ_ID NUMBER(18) CONSTRAINT NN_EVENT_10_ORDER_SEQ_ID NOT NULL,
ORDER_HIST_ID NUMBER(18) CONSTRAINT NN_EVENT_10_ORDER_HIST_ID NOT NULL,
EVENT_ID NUMBER(9) CONSTRAINT NN_EVENT_10_EVENT_ID NOT NULL,
POOLER_ID NUMBER(6) NOT NULL,
EVENT_TYPE CHAR(1 BYTE) NOT NULL,
NOTIFICATION_ID NUMBER(9) NOT NULL,
NOTIFICATION_HIST_ID NUMBER(18) NOT NULL,
EVENT_DEF_ID NUMBER(9) NOT NULL,
TIMESTAMP DATE DEFAULT NULL NOT NULL,
CARTRIDGE_ID NUMBER(6) NOT NULL,
EXECUTION_MODE VARCHAR2(1 BYTE) NOT NULL,
STATUS VARCHAR2(20 BYTE),
CONSTRAINT FK_PARENT_TAB
FOREIGN KEY (ORDER_SEQ_ID, ORDER_HIST_ID)
REFERENCES parent_tab (ORDER_SEQ_ID,HIST_SEQ_ID)
ON DELETE CASCADE
ENABLE VALIDATE
)
PARTITION BY REFERENCE (FK_PARENT_TAB)
(
partition
);
-- The follow SQL will dynamically generate monthly partitions for 150 years starting from JAN2016
-- Place the output in an SQL file and execute. Remove JAN2016 partition as it is already part of PARENT_TAB table.
-- The below script is provided by Frank Kulash . Thank You Frank
WITH got_mnths AS
(
SELECT ADD_MONTHS ( DATE '2016-01-01'
, LEVEL - 1
) AS this_mnth
, ADD_MONTHS ( DATE '2016-01-01'
, LEVEL
) AS next_mnth
FROM dual
CONNECT BY LEVEL < 12 * 150
)
SELECT 'ALTER TABLE parent_tab add PARTITION orders_'
|| TO_CHAR (this_mnth, 'monyyyy')
|| ' VALUES LESS THAN (TO_DATE ('''
|| TO_CHAR (next_mnth, 'DD/MON/YYYY')
|| ''', ''DD/MON/YYYY''));' AS txt
FROM got_mnths
ORDER BY to_number(substr(this_mnth,11));
It will take roughly 3 minutes to add all the above partitions.