CODEWITHSUNDEEP
oracle-database
Jeff Young
Jeff Young

Reputation: 140

Get original partitioning clause with DBMS_METADATA.GET_DDL

I created a table in Oracle with:

CREATE TABLE schema.table_name
(
   col_1                 NUMBER(15),
   col_2                 NUMBER(10),
   col_3                 NUMBER(10),
   col_4                 NUMBER,

)
PARTITION BY HASH (col_1) PARTITIONS 32;

However, when I fetch with:

select  DBMS_METADATA.GET_DDL('TABLE', table_name, owner) ddl
                , table_name 
             from all_tables 
            where owner = 'schema'
              and table_name = 'table_name';

I get:

CREATE TABLE schema.table_name
(
   col_1                 NUMBER(15),
   col_2                 NUMBER(10),
   col_3                 NUMBER(10),
   col_4                 NUMBER,

)
NOLOGGING
PARTITION BY HASH (col_1)
 (PARTITION "SYS_P1" , 
  PARTITION "SYS_P2" , 
  PARTITION "SYS_P3" , 
  PARTITION "SYS_P4" , 
  PARTITION "SYS_P5" ,
  ....
  PARTITION "SYS_P32");

I've tried all of these parameters:

DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);      
DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);   
DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);       
DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);     
DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true)

Is there a way to get the DDL back into the original format?

Upvotes: 1

Views: 3447

Answers (2)

user13988219
user13988219

Reputation: 1

You can eliminate those unnecessary partition details with regexp_replace function. I use the following code:

mystr := regexp_replace ( regexp_replace ( mystr, ' \(*PARTITION [^(BY)](.|[:cntrl:])+', null, 1, 0, 'm'), '^[[:cntrl:][:cntrl:]]+$', null, 1, 0, 'm');

Explanation: first it eliminates all rows which starts with 'PARTITION' except starting with 'PARTITION BY', then it eliminates the empty rows resulted of the first "masking".

Upvotes: 0

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

No, you cannot regain the initial statement you used to create the table/index/foreign key/whatever.

The DDL statement oracle provides here is a DDL statement to produce the exact same result as it is currently existing. In your case this yields a very short result, as it also mentions storage, parallel, tablespace, buffer_pool, etc etc when I try this with my tables.

Therefore, ...PARTITION BY HASH (col_1) (PARTITION "SYS_P1" , .... PARTITION "SYS_P32");... may produce another result than ...PARTITION BY HASH (col_1) PARTITIONS 32;.... An example here would be to use another tablespace or compress option.

What if some default options of your database change and you still want to regain the exact table you had before? When you created teh table, it wasn't compressed. Now it would be if it's the current default value with that same statement you used a while ago.

TL;DR

Answer: No

Why: You cannot reproduce the exact same object with the few options given on creation time, all other not given options are being taken from the default settings of the DB. These may change in time.

Upvotes: 3

Related Questions