SQL Script

Generate Create Tablespace Script

Updated: 26-Nov-1998
Version: 1.0

Description

This script generates a create tablespace script from an existing database. It is especially useful of you need a duplicate database on a different machine and have a large number of tablespaces.

The database will then be ready for a full database import.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

spool gents.lst
set serveroutput on size 1000000
DECLARE

  CURSOR get_ts IS SELECT * FROM dba_tablespaces
                   WHERE tablespace_name != 'SYSTEM';

  CURSOR get_df (p_ts VARCHAR2) IS
                   SELECT * from dba_data_files
                   WHERE tablespace_name = p_ts;

  l_str VARCHAR2(10);

BEGIN

FOR ts_rec IN get_ts LOOP

  dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);

  -- For each tablespace loop through the datafiles
  FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP

    IF get_df%ROWCOUNT = 1 THEN
       l_str := 'DATAFILE';
    ELSE
       l_str := ',';
    END IF;

    dbms_output.put_line (l_str||' '
                ||chr(39)||df_rec.file_name||chr(39)
                ||' SIZE '||df_rec.bytes||' REUSE ');

  END LOOP;

   dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
        ||' NEXT '||ts_rec.next_extent
        ||' MINEXTENTS '||ts_rec.min_extents
        ||' MAXEXTENTS '||ts_rec.max_extents
        ||' PCTINCREASE '||ts_rec.pct_increase||' ) ONLINE;');

   dbms_output.new_line;

END LOOP;

END;
/ 
spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Generate Create Tablespace Script

Updated: 26-Nov-1998Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 26-Nov-1998
Version: 1.0