Re: Full Import

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Fri, 16 Jul 1999 13:55:59 -0700
Message-ID: <378F9C5E.1A2B2C59@us.oracle.com>

If by a snapshot of the tablespaces, you mean a script that queries the data dictionary to generate all the create tablespace statements, sure, it's available. Here's one I got through Support that is available to customers as well. Note that it was written with 7 in mind, so you will need to modify it to get any 8 features (like MINIMUM EXTENT). It might be worthwhile looking on technet to see if there's an updated version.

HTH. Pete

REM

REM                    SCRIPT FOR CREATING TABLESPACES

REM
REM This script must be run by a user with the DBA role. REM
REM This script is intended to run with Oracle7. REM
REM Running this script will in turn create a script to build all the REM tablespaces in the database. This created script, create_tablespaces.sql,
REM can be run by any user with the DBA role or with the 'CREATE TABLESPACE'

REM system privilege.
REM
REM Only preliminary testing of this script was performed. Be sure to test REM it completely before relying on it. REM set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;

set termout on;
select 'Creating tablespace build script...' from dual; set termout off;

create table ts_temp (lineno number, ts_name varchar2(30),

                    text varchar2(800));

DECLARE
   CURSOR ts_cursor IS select tablespace_name,

                                initial_extent,
                                next_extent,
                                min_extents,
                                max_extents,
                                pct_increase,
                                status
                        from    sys.dba_tablespaces
                        where tablespace_name != 'SYSTEM'
                        and status != 'INVALID'
                        order by tablespace_name;
   CURSOR df_cursor (c_ts VARCHAR2) IS select   file_name,
                                                bytes
                                       from     sys.dba_data_files
                                       where    tablespace_name = c_ts
                                         and    tablespace_name != 'SYSTEM'
                                       order by file_name;
   lv_tablespace_name   sys.dba_tablespaces.tablespace_name%TYPE;
   lv_initial_extent    sys.dba_tablespaces.initial_extent%TYPE;
   lv_next_extent       sys.dba_tablespaces.next_extent%TYPE;
   lv_min_extents       sys.dba_tablespaces.min_extents%TYPE;
   lv_max_extents       sys.dba_tablespaces.max_extents%TYPE;
   lv_pct_increase      sys.dba_tablespaces.pct_increase%TYPE;
   lv_status            sys.dba_tablespaces.status%TYPE;
   lv_file_name         sys.dba_data_files.file_name%TYPE;
   lv_bytes             sys.dba_data_files.bytes%TYPE;
   lv_first_rec         BOOLEAN;
   lv_string            VARCHAR2(800);
   lv_lineno            number := 0;

   procedure write_out(p_line INTEGER, p_name VARCHAR2,
             p_string VARCHAR2) is
   begin
     insert into ts_temp (lineno, ts_name, text) values
            (p_line, p_name, p_string);

   end;

BEGIN
   OPEN ts_cursor;
   LOOP

      FETCH ts_cursor INTO lv_tablespace_name,
                           lv_initial_extent,
                           lv_next_extent,
                           lv_min_extents,
                           lv_max_extents,
                           lv_pct_increase,
                           lv_status;
      EXIT WHEN ts_cursor%NOTFOUND;
      lv_lineno := 1;
      lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
      lv_first_rec := TRUE;
      write_out(lv_lineno, lv_tablespace_name, lv_string);
      OPEN df_cursor(lv_tablespace_name);
      LOOP
         FETCH df_cursor INTO lv_file_name,
                              lv_bytes;
         EXIT WHEN df_cursor%NOTFOUND;
         if (lv_first_rec) then
            lv_first_rec := FALSE;
            lv_string := 'DATAFILE ';
         else
            lv_string := lv_string || ',';
         end if;
         lv_string:=lv_string||''''||lv_file_name||''''||
                    ' SIZE '||to_char(lv_bytes) || ' REUSE';
      END LOOP;
      CLOSE df_cursor;
         lv_lineno := lv_lineno + 1;
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' DEFAULT STORAGE (INITIAL ' ||
                      to_char(lv_initial_extent) ||
                      ' NEXT ' || lv_next_extent);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' MINEXTENTS ' ||
                      lv_min_extents ||
                      ' MAXEXTENTS ' || lv_max_extents);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' PCTINCREASE ' ||
                      lv_pct_increase || ')');
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_string := ('   '||lv_status);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='/';
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='                                                  ';
         write_out(lv_lineno, lv_tablespace_name, lv_string);

   END LOOP;
   CLOSE ts_cursor;
END;
/

spool create_tablespaces.sql
set heading off
set recsep off
col text format a80 word_wrap

select text
from ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;

exit

REM

---

kcary_at_my-deja.com wrote:

> In article <378cfb94.13598156_at_news.siol.net>,
> jmodic_at_src.si (Jurij Modic) wrote:
> >
> > Strictly answering the question: you should only run the CREATE
> > DATABASE command following by running the CATALOG.SQL and CATPROC.SQL.
> > This should create both the SYSTEM tablespace, redo logs and the
> > database dictionary. All the other stuff, including the rollback
> > segments definition , tablespace definitions and users specifications
> > are exported during the full export and should be recreated during the
> > full import (if everything goes OK).
> >
> > However, I agree with Thiru (tmgn_at_excite.com), I personally would
> > precreate at least all the tablespaces manually.
> >

>

> Wow, this has been a great thread. May I ask if there is a way to take a
> 'snapshot' of the tablespaces, generating a sql file, reflecting the
> running database. The idea would be to save this file to assist in the
> reconstruction of the database tablespaces before import.

>

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Regards

Pete

Received on Fri Jul 16 1999 - 15:55:59 CDT