Wednesday, April 28, 2010

How to extract DDL for all Tablespaces

I  want  to  create  all  the  tablespaces  on development from the production so I  need  all tablespace  create script  then  I  will  modify  the  the  size  of datafiles
I need  a  script which  extract  all tablespace  create commands 



SQL>set pages 20000;
SQL>set long 100000;
SQL>set heading off;
SQL>set echo off;
SQL>spool create_ts.sql
SQL>select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

...

...
SQL>spool off

Now  I have all tablespace  create script is  create_ts.sql
Also we  can use  is  for only  one tablespace e.x TSDENEME

SQL>select dbms_metadata.get_ddl('TABLESPACE','TSDENEME') from dual;

No comments: