Friday, September 12, 2008

generating all tablespace ddl from a database

So if you want to get the DDL for tablespaces :

SQL*Plus: Release - Production on Fri Sep 12 16:40:54 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set long 32000
SQL> set linesize 32000
SQL> set pagesize 0

SQL> exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

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

spool it to a file

et voila

No comments: