Re: Reverse engineer tablespaces.

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Wed, 23 May 2001 19:12:28 -0700
Message-ID: <F001.0030CCD7.20010523190020@fatcity.com>

am I missing something? If you have more than one datafile in any tablespace, you are going to generate incorrect SQL here. see below

you generate a new create tablespace command for EVERY datafile, even if it's the second one in the tablespace.

TABLESPACE_NAME FILE_NAME

------------------------- --------------------------------------------
COUPONS                   /db09/oradata/testdb/COUPONS01.dbf
COUPONS                   /db09/oradata/testdb/COUPONS02.dbf



SQL> select 'create tablespace '||a.tablespace_name||
  2          ' datafile '||''''||b.file_name||''''||' size '||b.bytes/(1024

*
3 1024)||'M'||

  4          ' default storage ( initial '||a.initial_extent||
  5                            ' next '||a.next_extent||
  6                            ' pctincrease '||a.pct_increase||
  7                            ' maxextents '||a.max_extents||');'
  8  from   dba_tablespaces a,
  9          dba_data_files b

10 where a.tablespace_name = b.tablespace_name 11 order by b.file_id;

create tablespace COUPONS datafile '/db09/oradata/testdb/coupons01.dbf' size 501M default storage ( initial 52428800 next 52428800 pctincrease 0 maxextents 2147483645);
create tablespace COUPONS datafile '/db09/oradata/testdb/coupons02.dbf' size 501M default storage ( initial 52428800 next 52428800 pctincrease 0 maxextents 2147483645);

>From: Rodd Holman <rodney.holman_at_lodgenet.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Reverse engineer tablespaces.
>Date: Wed, 23 May 2001 13:22:55 -0800
>
>Actually this version will create your tablespaces with the same
>structure and file_id's as your current db.
>
>select 'create tablespace '||a.tablespace_name||
> ' datafile '||''''||b.file_name||''''||' size '||b.bytes/(1024 *
>1024)||'M'||
> ' default storage ( initial '||a.initial_extent||
> ' next '||a.next_extent||
> ' pctincrease '||a.pct_increase||
> ' maxextents '||a.max_extents||');'
>from dba_tablespaces a,
> dba_data_files b
>where a.tablespace_name = b.tablespace_name
>order by b.file_id;
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Rodd Holman
> INET: rodney.holman_at_lodgenet.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Get your FREE download of MSN Explorer at http://explorer.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed May 23 2001 - 21:12:28 CDT