Extract DDL of subpartitioned table with interval partitioning

I have a table with interval partitioning and subpartitions and I want to extract the DDL including all the system generated partitions and subpartitions which get created as new rows with dates beyond the last partition’s high value get inserted.

Here is my script:

set linesize 1000
set pagesize 0
set long 2000000000
set longchunksize 1000
set head off;
set verify off;
set termout off;

column u new_value us noprint;
column n new_value ns noprint;

select name n from v$database;
select user u from dual;
set sqlprompt &ns:&us>

set head on
set echo on
set termout on
set trimspool on

set serveroutput on size 1000000

spool &ns.getddl2.log

set define off

drop table clobout;

create table clobout (doc clob);

declare
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','ORIGSCHEMA');
DBMS_METADATA.SET_FILTER(h,'NAME','ORIGTABLE');

-- Request that the schema name be modified.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','ORIGSCHEMA',
'NEWSCHEMA');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_NAME','ORIGTABLE',
'NEWTABLE');

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Specify that segment attributes are not to be returned.

dbms_metadata.set_transform_param(th,'CONSTRAINTS',false);
dbms_metadata.set_transform_param(th,'REF_CONSTRAINTS',false);
dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true);
dbms_metadata.set_transform_param(th,'EXPORT',true);

-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);

insert into clobout values (doc);
commit;

-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/ 

select doc from clobout;

drop table clobout;

spool off

I changed the schema and tables names I actually used to be ORIGSCHEMA, ORIGTABLE, NEWSCHEMA, NEWTABLE in the script above.

I wanted a copy like this so I could copy the production statistics to this new table.  I wanted to rename the resulting table and schema.  The key to this was setting EXPORT to true.  Without it you don’t get all the generated partitions and subpartitions.

FYI.  I also had to change all the storage clauses manually to STORAGE(INITIAL 1M) because the table has 40,000 subpartitions and the initial extent size made it too large to fit in my target tablespace.  I had to manually edit the output file to do this but I’m sure there is a way to do it programatically.

– Bobby