Script to Generate and Spool DDL / SQL statements for Tablespaces in Oracle 10g 11g Database

_____________________________________________________________________________________________________________________

As a DBA sometimes we need to spool the scripts for the tablespace for creating the new databases. For that you can use DBMS_METADATA package to extract the DDL script for the tablespace easily.

Below mentioned script you can use for the same purpose.

set heading off

set long 10000

set pages 0

set feedback off

spool tbs.sql

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

spool off

Extract DDL SQL using imp show=y

_____________________________________________________________________________________________________________________

Website Stats