The other day I need to get some DDL statements
from the data dictionary, you can use DBMS_METADATA to get the DDL for you.
Let’s take a look at how you can use this,
the DDL we want to extract is from the EMP table from SCOTT schema.
If we use DBMS_METADATA just like that, we
get more than we asked for.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
"USERS" ENABLE,
CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES
"SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
"USERS"
If you don’t need “Storage” clause in the
above DDL, you can turn then off using STORAGE option from set_transform_param method.
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'STORAGE',FALSE);
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE
"USERS" ENABLE,
CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES
"SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE
"USERS"
Similarly if you don’t need “tablespace”
clause in the above DDL, you can turn then off using TABLESPACE option from
set_transform_param method.
demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',FALSE);
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
ENABLE,
CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES
"SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
Similarly if you don’t need “Referential integrity”
constraint in the above DDL, you can turn then off using REF_CONSTRAINT option from
set_transform_param method.
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
demo@ORA12C>
Similarly if you don’t need “Segment
attributes” in the above DDL, you can turn then off using SEGMENT_ATTRIBUTES
option from set_transform_param method.
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')
from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE
)
demo@ORA12C>
Similarly if you don’t need “primary key”
constraint in the above DDL, you can turn then off using CONSTRAINTS option from
set_transform_param method.
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE"
DATE,
"SAL"
NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0)
)
demo@ORA12C>
If you need the sql terminator after the
generated ddl(s) then you can turn them on using SQLTERMINATOR option.
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR"
NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0)
) ;
demo@ORA12C>
To get the grant(s) – you can make use of
“get_dependent_ddl” method from dbms_metadata API.
demo@ORA12C> select
dbms_metadata.get_dependent_ddl('OBJECT_GRANT','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT')
--------------------------------------------------------------------------------
GRANT SELECT ON
"SCOTT"."EMP" TO PUBLIC;
GRANT SELECT ON
"SCOTT"."EMP" TO "DEMO";
demo@ORA12C>
To get the partitioning stuff removed,
just set PARTITIONING option to false.
demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from
dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE
"DEMO"."T"
( "OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23)
)
PARTITION BY HASH
("OBJECT_ID")
(PARTITION "P1"
,
PARTITION "P2"
) ;
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'PARTITIONING',false);
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from
dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE
"DEMO"."T"
( "OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"OBJECT_TYPE"
VARCHAR2(23)
) ;
demo@ORA12C>
One nice thing is that if you don’t need
to modify all the transformation parameter(s) again to go back to default, they
made it really easy to return to the default setting(s).
demo@ORA12C> exec dbms_metadata.set_transform_param(
dbms_metadata.SESSION_TRANSFORM,'DEFAULT');
PL/SQL procedure successfully completed.
demo@ORA12C> select
dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE
"SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME"
VARCHAR2(10),
"JOB"
VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM"
NUMBER(7,2),
"DEPTNO"
NUMBER(2,0),
CONSTRAINT
"PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
"USERS" ENABLE,
CONSTRAINT
"FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES
"SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
"USERS"
demo@ORA12C>
No comments:
Post a Comment