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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Extract DDL of subpartitioned table with interval partitioning

  1. Anonymous says:

    Very useful! Thanks!

  2. Vikash Kumar says:

    I have a table with 100 partition and each partition have 40 subpartitions. Now I need to generate DDL with only partition and subpartition list including only one partition/subpartition.. Is it possible ?

    • Bobby says:

      I don’t know how to do it with DBMS_METADATA. I guess you could make an empty copy of the original table and drop all of the partitions and subpartitions except one each and then extract the DDL for the empty table.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.