Re: how to general a db creation script from an exiting db?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 28 Aug 2002 09:11:47 -0700
Message-ID: <92eeeff0.0208280811.5c0b929b_at_posting.google.com>

defection_at_163.net (fee pack) wrote in message news:<4e732814.0208280216.5c8cc74c_at_posting.google.com>...
> alter database backup controlfile to trace;
> the info from the controlfile seems not enough,
> any other way to generate scripts like:
>
> create database
> ...
>
> create rollback segment
> ...
> alter rollback segment r0 online;
> ...
>
> create tablespace temp datafile
> ...
>
> create tablespace tools datafile
> ...
>
> create tablespace users datafile
> ...

Yes you can. Use SQL to generate SQL for you. Only limitation is your imagination. e.g. You can create script to create TEMP tablespace. Assuming you have *only* one datafile for TEMP,

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SET LINESIZE 100
SPOOL CreateTemp.sql
SELECT 'CREATE TABLESPACE ' || a.tablespace_name || CHR(10) ||

       'DATAFILE ' || CHR(39) || b.file_name || CHR(39) || ' SIZE ' || b.bytes || ' REUSE' || CHR(10) ||

       'DEFAULT STORAGE (INITIAL ' || a.initial_extent ||
       ' NEXT ' || a.next_extent ||
       ' MINEXTENTS ' || a.min_extents ||
       ' MAXEXTENTS ' || a.max_extents ||
       ' PCTINCREASE ' || a.pct_increase || ') ' || CHR(10) ||

a.status || ';'
FROM sys.dba_tablespaces a, sys.dba_data_files b WHERE a.tablespace_name = 'TEMP'
AND a.tablespace_name = b.tablespace_name; SPOOL OFF

-- 
CREATE TABLESPACE TEMP
DATAFILE 'C:\ORACLE\ORA817\ORADATA\TEST\TEMP.ORA' SIZE 80609280 REUSE
DEFAULT STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0) ONLINE;

If you have more then one datafile for a tablespace, use CURSOR FOR
loop in a PLSQL block.

//Rauf Sarwar

Received on Wed Aug 28 2002 - 18:11:47 CEST