|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
In
versions prior to oracle9i generation of DDL code for objects like
tables and indexes
was not an easy thing to do. One had to build PLSQL
routines that builds the DDL statements. Oracle9i has supplied the
dbms_metadata package that helps us in reverse engineering the DDL
code. Here are some examples of using the dbms_metadata package:
(1) Generate table creation statement along with the STORAGE clause:
set long 10000
select dbms_metadata.get_ddl('TABLE','TEST_OBJECTS','RESEARCH_USER') from dual;
(2) Generate table creation statement without the STORAGE clause:
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
'STORAGE',false)
set long 10000
select dbms_metadata.get_ddl('TABLE','TEST_OBJECTS','RESEARCH_USER') from dual;
(3) Generate referential integrity constraints:
set long 10000
SELECT dbms_metadata.get_ddl('REF_CONSTRAINT', CONSTRAINT_NAME) FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R' and table_name = 'TEST_OBJECTS';
(4) Generate code for Primary Keys and Unique Keys:
set long 10000
SELECT dbms_metadata.get_ddl('CONSTRAINT',CONSTRAINT_NAME) from user_constraints
where constraint_type in ('P','U') and table_name = 'TEST_OBJECTS';
(5) Generate statement for Indexes:
set long 10000
SELECT dbms_metadata.get_ddl('INDEX',INDEX_NAME) from user_indexes where index_name not
in (select constraint_name from user_constraints where constraint_type in ('P','U'));
(6) An example:
This example script will work from sqlplus and accepts the table name and the table owner and
generates DDL for the table and its dependent objects. Make sure you grant select_catalog_role
to the user running this script.
SQL> set long 100000000
SQL> exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE)
SQL> select dbms_metadata.get_ddl('TABLE','&tab_name','&tab_owner') from dual
union all
-- Get DDL for the associated indexes except for the ones associated with PK and UK constraints
select dbms_metadata.get_ddl('INDEX',index_name,owner) from dba_indexes where owner=
'&&tab_owner' and table_name = '&&tab_name' and not exists (select distinct '1' from
dba_constraints where owner='&&tab_owner' and table_name = '&&tab_name' and
dba_constraints.index_name = dba_indexes.index_name)
union all
-- Get DDL for the COMMENT statements on the table columns
select dbms_metadata.get_dependent_ddl('COMMENT','&&tab_name','&&tab_owner') from dual
union all
---- Get DDL for the triggers associated with the table
select dbms_metadata.get_dependent_ddl('TRIGGER','&&tab_name','&&tab_owner') from dual
union all
-- Get DDL for the GRANT on the table
select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&&tab_name','&&tab_owner') from dual;
|
