Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:


set linesize 200 long 50000 pagesize 5000

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name )
from dba_tablespaces
where tablespace_name in ( '&your_tablespace' );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be very useful.

exp/imp


exp userid=" '/ as sysdba' " rows=n file=my.dmp full=y
imp userid=" '/ as sysdba' " file=my.dmp full=y show=y > full_structure.sql 2>&1

Warning! exp could not get the contents of the encrypted tablespaces.

expdp/impdp

expdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR content=metadata_only full=y
impdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR sqlfile=full_structure.sql