Tuesday, July 5, 2011

Getting a tablespace’s DDL

If you want to copy a tablespace structure and you don’t have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:

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

PL/SQL procedure successfully completed.

SQL> set linesize 150
SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) from dba_tablespaces order by TABLESPACE_NAME;

CREATE TABLESPACE "MYTABLESPACE" DATAFILE
'/mydb/mytablespace.dbf' SIZE 5242880
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE "PERFSTAT" DATAFILE
'/mydb/perfstat.dbf' SIZE 201326592
AUTOEXTEND ON NEXT 16777216 MAXSIZE 402653184
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE "SYSAUX" DATAFILE
'/mydb/sysaux.dbf' SIZE 419430400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE "SYSTEM" DATAFILE
'/mydb/system.dbf' SIZE 419430400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT DICTIONARY DEFAULT NOCOMPRESS
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50);


CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/mydb/temp.dbf' SIZE 10737418240
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;


CREATE UNDO TABLESPACE "UNDO" DATAFILE
'/mydb/undo.dbf' SIZE 838860800
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER DATABASE DATAFILE
'/mydb/undo.dbf' RESIZE 5368709120;


CREATE TABLESPACE "USERS" DATAFILE
'/mydb/users.dbf' SIZE 536870912
AUTOEXTEND ON NEXT 1048576 MAXSIZE 2048M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;


7 rows selected.

As you might notice with the UNDO tablespace the original CREATE TABLESPACE statement comes first, and if there is any modification to the tablespaces that sentences come next.

More information:

How to extract DDL for all Tablespaces

No comments:

Post a Comment