Wednesday, August 21, 2013

Extract all tablespaces DDL

Nice and easiest way to to extract the DDL for all tablepaces..

 Generate the DDL using the below query and you can re create the table spaces in any environment with any changes you want to make.

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql

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

spool off

(sample) output will look something like this:

"
  CREATE TABLESPACE "SYSTEM" DATAFILE
  '/u02/oracle/oradata/system01.dbf' SIZE 314572800
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
   ALTER DATABASE DATAFILE
  '/u02/oracle/oradata/QPUAT/system01.dbf' RESIZE 4194304000
 "
"
  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  '/u02/oracle/oradata/undotbs01.dbf' SIZE 209715200
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  '/u02/oracle/oradata/QPUAT/undotbs01.dbf' RESIZE 1782579200
 "

No comments:

Post a Comment