RE: Reverse engineer tablespaces.

That's true, It also will not work for 8i temp tablespaces using the tempfile syntax. I just checked it against a small 8.0.5 system.

> -----Original Message-----
> From: Rachel Carmichael [SMTP:carmichr_at_hotmail.com]
> Sent: Wednesday, May 23, 2001 10:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Reverse engineer tablespaces.
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Holman, Rodney
  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).