| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Reverse engineer tablespaces.
Similar to the one below. This can be mucked about with so it doesn't prompt.
Regards
Lee
SCRIPT STARTS HERE
accept ts_name prompt 'Enter TableSpace Name : '
col ord noprint
col res format a90
spool cr_tblsp.out
select tablespace_name || '1' ord,
'create tablespace '|| tablespace_name ||' datafile' res
from dba_tablespaces
where tablespace_name like upper('&ts_name%')
and status <> 'INVALID'
UNION
-- ## get the middle datafiles - need ','
select a.tablespace_name || '3' ord,
' '''|| a.file_name ||''' size '||a.bytes ||',' res from dba_data_files a
where a.tablespace_name like upper('&ts_name%')
and a.status <> 'INVALID'
and a.file_id <> (select max(b.file_id)
from dba_data_files b
where b.tablespace_name = a.tablespace_name)
UNION
' '''|| a.file_name ||''' size '||a.bytes res from dba_data_files a
where a.tablespace_name like upper('&ts_name%')
and a.status <> 'INVALID'
and a.file_id = (select max(b.file_id)
from dba_data_files b
where b.tablespace_name = a.tablespace_name)
-- finish off
initial '||INITIAL_EXTENT ||'
next '||NEXT_EXTENT ||'
minextents '||MIN_EXTENTS ||'
maxextents '||MAX_EXTENTS ||'
pctincrease '||PCT_INCREASE ||')
/' res
spool off
SCRIPT ENDS HERE
-----Original Message-----
Sent: 23 May 2001 21:30
To: Multiple recipients of list ORACLE-L
I'm not sure if this is what your looking for but take a look:
spool db_structure.txt
select 'create tablespace ' || a.tablespace_name ||
' datafile ' || '''' || b.file_name || ''''||' size ' || b.bytes/(1024 *
1024) || 'M' || \
' default storage ( initial ??k next ??k pctincrease ? maxextents ?);'
from dba_tablespaces a, dba_data_files b where a.tablespace_name =
b.tablespace_name;
spool off;
exit;
Roy
>
>So, I need to re-create a database to prepare for migration. The current
DB
>is 8.0.6, and I need to create an 8.1.7 copy. Does anyone have a script to >reverse engineer the CREATE TABLESPACE commands? I started working on one, >but surely I can't be the first to invent the wheel. > >TIA, >Rich Jesse System/Database Administrator >Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Jesse, Rich > INET: Rich.Jesse_at_qtiworld.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).
Roy E. Ferguson II
Intel Sacramento
916-854-1123
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson INET: rferguso_at_level1.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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_acxiom.co.uk 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 Thu May 24 2001 - 02:43:59 CDT
 |
 |