General Syntax
===========================================
DBMS_METADATA.get_ddl(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
DBMS_METADATA.get_dependent_ddl(
object_type IN VARCHAR2,
base_object_name IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER DEFAULT 10000)
RETURN CLOB;
object_type
This is a list of all possible objects.
Common objects are Table, Index, Trigger, Synonym, Index, Db_link etc.
===========================================
Using DBMS_METADATA
===========================================
===========================================
Configuring the output in sqlplus
===========================================
SET LONG 5000
SET PAGESIZE 0
SET LINESIZE 400
COL DDL_CMD FOR A400 WORD_WRAP
SET FEEDBACK OFF
===========================================
Configuring the output
===========================================
Use DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform(PARAM,VALUE) to manipulate the generated output.
SQLTERMINATOR
When set to true, would add ';' at the end of each generated SQL.
SEGMENT_ATTRIBUTES
When set to false would remove the storage clause from the generated SQL
CONSTRAINTS_AS_ALTER
When set to true Would generate Constraints in a separate SQL statement.
REF_CONSTRAINTS
When set to false would not generate Reference Constraints.
Example:
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.session_transform,'SQLTERMINATOR', true);
DBMS_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.session_transform,'SEGMENT_ATTRIBUTES', false);
DBMS_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.session_transform,'REF_CONSTRAINTS', false);
END;
/
To have the output in one line:
Default output:
CREATE UNIQUE INDEX "MY_USER"."MY_TABLE_PK" ON "MY_USER"."MY_TABLE" ("IMSI", "DAY", "NETWORK_ID")
To have the output in one line:
COL DDL_CMD FOR A400 WORD_WRAP
SET LINESIZE 400
SET HEADING OFF
CREATE UNIQUE INDEX "MY_USER"."MY_TABLE_PK" ON "MY_USER"."MY_TABLE" ("IMSI", "DAY", "NETWORK_ID") ;
===========================================
Get the DDL for Table===========================================
DBMS_METADATA.get_ddl
Example:
--Generate DDL for a table
set long 1000
set pagesize 0
set linesize 400
COL DDL_CMD FOR A400 WORD_WRAP
SELECT DBMS_METADATA.get_ddl('INDEX','MY_TABLE_A_IX1') DDL_CMD
FROM DUAL;
> would get the CREATE INDEX MY_TABLE_A_IX1 ... SQL
===========================================
Get the DDL for Table Related Objects
===========================================
Would get the DDL for dependent objects, per specified object type, without the need to know the name of each index, etc...
DBMS_METADATA.get_dependent_ddl
For example:
===========================================
Get the Foreign Constraints DDL
===========================================
Option A.
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', 'MY_TABLE') FROM DUAL;
But with this option, in case there are no FK Constraints, an exception is thrown.
Example:
SQL> SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT','MY_TABLE') FROM DUAL;
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT','MY_TABLE') FROM DUAL
ORA-31608: specified object of type REF_CONSTRAINT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 2700
ORA-06512: at "SYS.DBMS_METADATA", line 4302
ORA-06512: at line 1
Option B.
Use join with USER_CONSTRAINTS
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', table_name)
FROM USER_TABLES t
WHERE table_name IN ('MY_TABLE')
AND EXISTS (SELECT 1
FROM USER_CONSTRAINTS
WHERE table_name = t.table_name
AND constraint_type = 'R');
Option C.
Get all DDL for FK referenceing CUSTOMER table:
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT',table_name)
FROM USER_CONSTRAINTS
WHERE r_constraint_name IN
(SELECT constraint_name FROM USER_CONSTRAINTS
WHERE table_name = 'CUSTOMER' AND constraint_type = 'P');
===========================================
Addition - Generating enable and disable script for Referencing Foreign Keys.
===========================================
Example for table CUSTOMER.
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
===========================================
Remove owner from the output
===========================================
When running DDL generation of an object, it is generated as "OWNER".TABLE_NAME".
What if you want ta general SQL, without the owner?
Need to use REPLACE() function to replace '"SYS_CONTEXT (''USERENV'',''SESSION_USER'')".' with null.
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''TABLE'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM CRI_TABLES_LIST;
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''INDEX'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES WHERE table_name IN (SELECT table_name FROM CRI_TABLES_LIST);
===========================================
Putting it all together
===========================================
Example of generating scripts to recreate a table
set long 10000
set pagesize 0
set linesize 120
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'SQLTERMINATOR', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'SEGMENT_ATTRIBUTES', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'REF_CONSTRAINTS', false);
END;
/
spool cre_customer.sql
--get CREATE TABLE
SELECT DBMS_METADATA.get_ddl('TABLE','CUSTOMER') FROM DUAL;
spool off
spool cre_customer_index.sql
--get CREATE INDEX
SELECT DBMS_METADATA.get_dependent_ddl('SYNONYM','CUSTOMER') FROM DUAL;
spool off
spool cre_customer_fk.sql
--get FOREIGN KEYS
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', table_name)
FROM USER_TABLES t
WHERE table_name IN ('CUSTOMER')
AND EXISTS (SELECT 1
FROM USER_CONSTRAINTS
WHERE table_name = t.table_name
AND constraint_type = 'R');
spool off
spool disable_referencing_fk.sql
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
spool off
spool enable_referencing_fk.sql
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
spool off
spool drop_customer_index.sql
SELECT 'DROP INDEX '||index_name||';' FROM USER_INDEXES WHERE table_name = 'CUSTOMER';
spool off
spool drop_customer_constraints.sql
SELECT 'DROP CONSTRAINT '||constraint_name||';' FROM USER_CONSTRAINTS WHERE table_name = 'CUSTOMER';
spool off
Now, say you need to rename and recreate the table.
@disable_referencing_fk.sql
RENAME TABLE CUSTOMER TO CUSTOMER_ORIG;
--Drop objects referencing CUSTOMER_ORIG;
@drop_customer_index.sql
@drop_customer_constraints.sql
@cre_cutomer.sql
@cre_customer_index.sql
@cre_customer_fk.sql
@enable_referencing_fk.sql
===========================================
Putting it all together, example II
===========================================
The output is three files:
gen_gen_cre_tables.sql
gen_gen_cre_indexes.sql
gen_gen_cre_fk.sql
@./set_vipuser.sql
connect &&vipuser/&&vippass@&&connectstr
SET SERVEROUTPUT ON
SPOOL install_cenrtalized_ri.log APPEND
SET VERIFY OFF
EXEC DBMS_OUTPUT.ENABLE(100000);
SET HEADING OFF
SET LONG 5000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET NEWPAGE NONE
PROMPT =================================================
PROMPT Start Generate Create Tables script
PROMPT =================================================
SPOOL OFF
PROMPT SPOOL install_cenrtalized_ri.log APPEND
PROMPT SET LONG 5000
PROMPT COLUMN TEXT FORMAT A300 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 500
PROMPT SET FEEDBACK OFF
SPOOL gen_cre_tables.sql
PROMPT SPOOL gen_gen_cre_tables.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''TABLE'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM CRI_TABLES_LIST;
PROMPT SPOOL OFF
SPOOL OFF
SPOOL gen_cre_indexes.sql
PROMPT SPOOL gen_gen_cre_indexes.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''INDEX'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES WHERE table_name IN (SELECT table_name FROM CRI_TABLES_LIST);
PROMPT SPOOL OFF
SPOOL OFF
SPOOL gen_cre_fk.sql
PROMPT SPOOL gen_gen_cre_fk.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_dependent_ddl(''REF_CONSTRAINT'', '''||USER_CONSTRAINTS.table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES,
USER_CONSTRAINTS,
CRI_TABLES_LIST
WHERE USER_TABLES.table_name = CRI_TABLES_LIST.table_name
AND USER_TABLES.table_name = USER_CONSTRAINTS.table_name
AND USER_CONSTRAINTS.CONSTRAINT_TYPE='R';
PROMPT SPOOL OFF
SPOOL OFF
@gen_cre_tables.sql
@gen_cre_indexes.sql
@gen_cre_fk.sql
SPOOL install_cenrtalized_ri.log APPEND
PROMPT =================================================
PROMPT Finished Generate Enable Constraints script
PROMPT =================================================
SPOOL OFF
EXIT;
=================
Views
===========================================
DBMS_METADATA Reference
===========================================
set pagesize 0
set linesize 400
COL DDL_CMD FOR A400 WORD_WRAP
SELECT DBMS_METADATA.get_ddl('TABLE','MY_TABLE_A') DDL_CMD
FROM DUAL;
FROM DUAL;
--Generate DDL for a table under another tablespace
set long 80000
set pagesize 0
set linesize 400
COL DDL_CMD FOR A400 WORD_WRAP
spool SGA_W_MOCO_SUBSCRIBER.sql
SELECT REPLACE (DBMS_METADATA.get_ddl('TABLE','SGA_W_MOCO_SUBSCRIBER','LAB_QANFV_ALLQQ'),'IGT_TABLE_BIG','IGT_TABLE_BIG_01')||';' DDL_CMD FROM DUAL;
spool off
> would get the CREATE TABLE MY_TABLE_A ... SQL
FROM DUAL;
> would get the CREATE INDEX MY_TABLE_A_IX1 ... SQL
===========================================
Get the DDL for Table Related Objects
===========================================
Would get the DDL for dependent objects, per specified object type, without the need to know the name of each index, etc...
For example:
set long 5000
set pagesize 0
SELECT DBMS_METADATA.get_dependent_ddl('INDEX','MY_TABLE_A') FROM DUAL;
> would get ALL the CREATE INDEX SQLs for table MY_TABLE_A.
SELECT DBMS_METADATA.get_dependent_ddl('SYNONYM','MY_TABLE_A') FROM DUAL;
> would get ALL the CREATE SYNONYM SQLs for table MY_TABLE_A.
set pagesize 0
SELECT DBMS_METADATA.get_dependent_ddl('INDEX','MY_TABLE_A') FROM DUAL;
> would get ALL the CREATE INDEX SQLs for table MY_TABLE_A.
SELECT DBMS_METADATA.get_dependent_ddl('SYNONYM','MY_TABLE_A') FROM DUAL;
> would get ALL the CREATE SYNONYM SQLs for table MY_TABLE_A.
===========================================
Get the Foreign Constraints DDL
===========================================
Option A.
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', 'MY_TABLE') FROM DUAL;
But with this option, in case there are no FK Constraints, an exception is thrown.
Example:
SQL> SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT','MY_TABLE') FROM DUAL;
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT','MY_TABLE') FROM DUAL
ORA-31608: specified object of type REF_CONSTRAINT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 2700
ORA-06512: at "SYS.DBMS_METADATA", line 4302
ORA-06512: at line 1
Option B.
Use join with USER_CONSTRAINTS
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', table_name)
FROM USER_TABLES t
WHERE table_name IN ('MY_TABLE')
AND EXISTS (SELECT 1
FROM USER_CONSTRAINTS
WHERE table_name = t.table_name
AND constraint_type = 'R');
Option C.
Get all DDL for FK referenceing CUSTOMER table:
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT',table_name)
FROM USER_CONSTRAINTS
WHERE r_constraint_name IN
(SELECT constraint_name FROM USER_CONSTRAINTS
WHERE table_name = 'CUSTOMER' AND constraint_type = 'P');
===========================================
Addition - Generating enable and disable script for Referencing Foreign Keys.
===========================================
Example for table CUSTOMER.
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER')
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER')
===========================================
Remove owner from the output
===========================================
When running DDL generation of an object, it is generated as "OWNER".TABLE_NAME".
What if you want ta general SQL, without the owner?
Need to use REPLACE() function to replace '"SYS_CONTEXT (''USERENV'',''SESSION_USER'')".' with null.
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''TABLE'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM CRI_TABLES_LIST;
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''INDEX'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES WHERE table_name IN (SELECT table_name FROM CRI_TABLES_LIST);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_dependent_ddl(''REF_CONSTRAINT'', '''||USER_CONSTRAINTS.table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES,
USER_CONSTRAINTS,
CRI_TABLES_LIST
WHERE USER_TABLES.table_name = CRI_TABLES_LIST.table_name
AND USER_TABLES.table_name = USER_CONSTRAINTS.table_name
AND USER_CONSTRAINTS.CONSTRAINT_TYPE='R';
===========================================
Putting it all together
===========================================
Example of generating scripts to recreate a table
set long 10000
set pagesize 0
set linesize 120
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'SQLTERMINATOR', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'SEGMENT_ATTRIBUTES', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,'REF_CONSTRAINTS', false);
END;
/
spool cre_customer.sql
--get CREATE TABLE
SELECT DBMS_METADATA.get_ddl('TABLE','CUSTOMER') FROM DUAL;
spool off
spool cre_customer_index.sql
--get CREATE INDEX
SELECT DBMS_METADATA.get_dependent_ddl('SYNONYM','CUSTOMER') FROM DUAL;
spool off
spool cre_customer_fk.sql
--get FOREIGN KEYS
SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', table_name)
FROM USER_TABLES t
WHERE table_name IN ('CUSTOMER')
AND EXISTS (SELECT 1
FROM USER_CONSTRAINTS
WHERE table_name = t.table_name
AND constraint_type = 'R');
spool off
spool disable_referencing_fk.sql
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
spool off
spool enable_referencing_fk.sql
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM USER_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from USER_CONSTRAINTS
WHERE constraint_type = 'P' AND table_name = 'CUSTOMER');
spool off
spool drop_customer_index.sql
SELECT 'DROP INDEX '||index_name||';' FROM USER_INDEXES WHERE table_name = 'CUSTOMER';
spool off
spool drop_customer_constraints.sql
SELECT 'DROP CONSTRAINT '||constraint_name||';' FROM USER_CONSTRAINTS WHERE table_name = 'CUSTOMER';
spool off
Now, say you need to rename and recreate the table.
@disable_referencing_fk.sql
RENAME TABLE CUSTOMER TO CUSTOMER_ORIG;
--Drop objects referencing CUSTOMER_ORIG;
@drop_customer_index.sql
@drop_customer_constraints.sql
@cre_cutomer.sql
@cre_customer_index.sql
@cre_customer_fk.sql
@enable_referencing_fk.sql
===========================================
Putting it all together, example II
===========================================
The output is three files:
gen_gen_cre_tables.sql
gen_gen_cre_indexes.sql
gen_gen_cre_fk.sql
@./set_vipuser.sql
connect &&vipuser/&&vippass@&&connectstr
SET SERVEROUTPUT ON
SPOOL install_cenrtalized_ri.log APPEND
SET VERIFY OFF
EXEC DBMS_OUTPUT.ENABLE(100000);
SET HEADING OFF
SET LONG 5000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET NEWPAGE NONE
PROMPT =================================================
PROMPT Start Generate Create Tables script
PROMPT =================================================
SPOOL OFF
PROMPT SPOOL install_cenrtalized_ri.log APPEND
PROMPT SET LONG 5000
PROMPT COLUMN TEXT FORMAT A300 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 500
PROMPT SET FEEDBACK OFF
SPOOL gen_cre_tables.sql
PROMPT SPOOL gen_gen_cre_tables.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''TABLE'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM CRI_TABLES_LIST;
PROMPT SPOOL OFF
SPOOL OFF
SPOOL gen_cre_indexes.sql
PROMPT SPOOL gen_gen_cre_indexes.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_ddl(''INDEX'', '''||table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES WHERE table_name IN (SELECT table_name FROM CRI_TABLES_LIST);
PROMPT SPOOL OFF
SPOOL OFF
SPOOL gen_cre_fk.sql
PROMPT SPOOL gen_gen_cre_fk.sql
PROMPT SET LONG 5000
PROMPT COLUMN DDL_CMD FORMAT A200 WORD_WRAP
PROMPT SET PAGESIZE 0
PROMPT SET LINESIZE 200
PROMPT SET FEEDBACK OFF
PROMPT EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
SELECT 'SELECT REPLACE (DBMS_METADATA.get_dependent_ddl(''REF_CONSTRAINT'', '''||USER_CONSTRAINTS.table_name||'''),''"''||SYS_CONTEXT (''USERENV'',''SESSION_USER'')||''"'')||'';'' AS DDL_CMD FROM DUAL;'
FROM USER_TABLES,
USER_CONSTRAINTS,
CRI_TABLES_LIST
WHERE USER_TABLES.table_name = CRI_TABLES_LIST.table_name
AND USER_TABLES.table_name = USER_CONSTRAINTS.table_name
AND USER_CONSTRAINTS.CONSTRAINT_TYPE='R';
PROMPT SPOOL OFF
SPOOL OFF
@gen_cre_tables.sql
@gen_cre_indexes.sql
@gen_cre_fk.sql
SPOOL install_cenrtalized_ri.log APPEND
PROMPT =================================================
PROMPT Finished Generate Enable Constraints script
PROMPT =================================================
SPOOL OFF
EXIT;
=================
Views
=================
To get one view:
SELECT DBMS_METADATA.get_ddl('VIEW','ALL_ACTIVE_ICA') view_sql
FROM DUAL;
To get allviews:
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.session_transform,'SQLTERMINATOR', true);
DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.session_transform,'SEGMENT_ATTRIBUTES', false);
DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.session_transform,'REF_CONSTRAINTS', false);
END;
/
SELECT 'SELECT DBMS_METADATA.get_ddl(''VIEW'','''||view_name||''') view_sql FROM DUAL;'
FROM USER_VIEWS
ORDER BY view_name;
SELECT DBMS_METADATA.get_ddl('VIEW','ALL_ACTIVE_ICA') view_sql FROM DUAL;
SELECT DBMS_METADATA.get_ddl('VIEW','ALL_EXTERNAL_SERVICE_NAMES') view_sql FROM DUAL;
SELECT DBMS_METADATA.get_ddl('VIEW','ALL_GENERAL_HSC') view_sql FROM DUAL;
SELECT DBMS_METADATA.get_ddl('VIEW','ALL_HSC_STATUS') view_sql FROM DUAL;
DBMS_METADATA Reference
===========================================
DBMS_METADATA Oracle Documentation
Nice Example for DBMS_METADATA usage
No comments:
Post a Comment