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
Advertisements
hi thanks for the note this statement is not working for 8.1.7 right ?
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
Good, Oracle always … but if you use ||'/' is better:
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || '/'
from dba_tablespaces tb;
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.
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’);