HOME > > Generate Create Tablespace Script

Generate Create Tablespace Script

Anup - Tuesday, May 19, 2009
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 ');
      if df_rec.autoextensible = 'YES' then
         dbms_output.put_line (' AUTOEXTEND ON'
                               ||' NEXT '||df_rec.increment_by );
         if df_rec.maxbytes = 68719443968 then
            dbms_output.put_line (' MAXSIZE UNLIMITED');
         else
            dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
         end if;
      end if;
    END LOOP;
  /* Extent Management Clause */
     dbms_output.put_line  ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );
     if ts_rec.extent_management = 'LOCAL' then
        if ts_rec.allocation_type = 'SYSTEM' then
           dbms_output.put_line (' AUTOALLOCATE ');
        else
           dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);
        end if;
     end if;
     if ts_rec.extent_management = 'DICTIONARY' then
       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||' ) ');
     end if;
     dbms_output.put_line  (' ONLINE;');
     dbms_output.new_line;
  END LOOP;
  END;
  /
Sara Reid said...

This script can be used to generate a CREATE TABLESPACE script. It will be very useful in environments where it is required to recreate databases or simulate the existing database setup in a different environment. Just run this script and you will get the entire tablespace creation script fire it up in the newly setup box.

2gb micro sd karte

Contact me

Get in Touch

Need to get touch with me? Please fill out the form with your enquiry.

Name
Anup Srivastav
Address
Lucknow - Utter Pradesh
Email
myindiandba@gmail.com
Message me