Friday, December 12, 2014

Tablespaces DDL - Oracle


There may be situation where you are trying to create a new database similar to old one and it is a fresh install and you need to get the Tablespaces DDL from the old one. This query will be very help full.

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

Sample Output :

"  CREATE TABLESPACE "USERS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' RESIZE 2097152000";

"  CREATE TABLESPACE "TOOLS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/tools01.dbf' SIZE 67108864
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO";

" CREATE TABLESPACE "INDX" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/indx01.dbf' SIZE 268435456
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO";

No comments:

Post a Comment