Generating create SQL through dbms_metadata package
DBMS_METADATA
DBMS_METADATA:-This package is used to generate the create SQL for Table,Index,Package etc.
Syntax:-
SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;
Example1:-Generate the CREATE SQL for EMP table OF SCOTT SCHEMA
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;
Example2:-Generate the CREATE SQL for PK_EMP index OF SCOTT SCHEMA
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;
Example3:-Generate the CREATE SQL for EMP_PKG Package Specification OF SCOTT SCHEMA
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;
Example4:-Generate the CREATE SQL for EMP_PKG Package Body OF SCOTT SCHEMA
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;
Example5:-Generate the CREATE SQL for USER TABLESPACE
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;
Example6:-Generate the ALTER SQL for FK_DEPTNO foreign key constraints of SCOTT SCHEMA.
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','EMP','SCOTT') from dual;
Example7:-Generate GRANT SQL for the System privileges grants for a SCOTT schema.
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;
Example8:-Generate the GRANT SQL for the Role grant for SCOTT schema,
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;
Example9:-Generate the GRANT SQL for object grants for SCOTT schema
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;
Example10:-Generate the CREATE SQL for all tables of SCOTT SCHEMA
SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''SCOTT'') from dual;' FROM DBA_TABLES where owner='SCOTT';
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.