Alex Lima

Extract all tablespaces DDL

Nice way to extract the DDL for all tablepaces.. This also can be used for other objects as well.

SQL>set heading off;
SQL>set echo off;
SQL>Set pages 999;
SQL>set long 90000;

SQL>spool ddl_list.sql

SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

SQL>spool off

5 comments

    • Hi Ahmeed,

      I think this package was introduced in 9i and upwards, but using exp and imp you can extract the DDL.

      Example:
      # include grants, indexes and constraints on the table without no data
      exp userid=user/password rows=n grants=y indexes=y constraints=y tables=owner.table_name
      # to generate the SQL of the table
      imp file=expdat.dmp full=y show=y log=user.sql
      or
      imp file=expdat.dmp full=y indexfile=user.sql

      Also AskTom following article http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1113105138675 discusses it further.
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1113105138675

  2. Good, Oracle always … but if you use ||'/' is better:

    select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || '/'
    from dba_tablespaces tb;

  3. Absolutely right. This package was introduced in Oracle 9i and not available in Oracle 8i. Package itself available in Oracle 8i but extracting DDL of tablespace was not in same. Excellent article and knowledge sharing. Buddy keep it up.

  4. If you’re building a skeleton database based off of an existing database and want to exclude certain core tablespaces this command is even better:

    select dbms_metadata.get_ddl(‘TABLESPACE’,tb.tablespace_name) || ‘/’ from dba_tablespaces tb where tablespace_name not i
    n (‘SYSTEM’,’SYSAUX’,’UNDOTBS1′,’TEMP’,’USERS’);