Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;

Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;

You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)

FROM USER_TABLES U;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)

FROM USER_INDEXES U;  

In case if you need a View DDL, you can run below command;
SQL>select DBMS_METADATA.GET_DDL('VIEW',<View_Name>’) from dual;
or
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>','<schema_name>') from DUAL;

You can run below command to generate the DDL statements of a SCHEMA
set pages 30000
set linesize 1000
set lines 500

SQL> SELECT DBMS_METADATA.GET_DDL('USER','<schema_name>') FROM dual;

You can run below command to generate DDL statements for more than one schema

SQL> SELECT DBMS_METADATA.GET_DDL('USER',U.USERNAME) FROM DBA_USERS U WHERE USERNAME IN ('USER1','USER2');

You can run below command to generate DDL statement of the System Grant/ role granted/object granted to a schema owner

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',<Schema_Name>’) from dual;

You can run below command to generate DDL statement of the role
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;

You can also use SQL Developer to get the DDL script for a specific object.

You can refer oracle documentation for more details on DBMS_METADATA

Thanks & Regards

http://oracleracexpert.com