Looking for a script that I can run against a database that will reverse engineer the DDL statement for the object in it. I don't have the opportunity to plug in a data modeling tool to do the job.
-----------------------------------------------------
Rem Filename : Generate_createdb.sql
Rem Purpose : To reverse engineer the createdb.sql from a running database
Rem Syntax : sqlplus -s / @Generate_createdb.sql
Rem produces an output called createdb_$ORACLE_SID.sql
Rem
Rem Notes : Developed and tested on Oracle 7.3.3 for Solaris
Rem Autoextending files NOT catered for
Rem Runs ONLY catproc.sql and catalog.sql
Rem Assumes TEMPORARY tablespace is TEMP
Rem Non UNIX databases may need small modifications to the paths
Rem of called scripts i.e. catalog.sql, catproc.sql ...
Rem
Rem History : 20-01-2000 E Augustine Created
Rem 14-02-2000 E Augustine
Rem Database is created in NOARCHIVELOG and then altered
Rem to ARCHIVELOG if originally configured in ARCHIVELOG.
Rem
Set TERM On SERVEROUTPUT On Size 10000 FEEDBACK Off LINESIZE 300 TRIMSPOOL On
Spool createdb_${ORACLE_SID}.sql
Declare
-- Non SYSTEM Tablespaces are created explicitly.
-- The Min(FILE_ID) is used to select the order in which Tablespaces
-- were created.
Cursor C_Tablespaces Is
Select TABLESPACE_NAME, Min(FILE_ID) From DBA_DATA_FILES
Where TABLESPACE_NAME Not Like 'SYSTEM%'
Group By TABLESPACE_NAME
Order By 2;
-- All files for a tablespace.
Cursor C_Datafiles ( P_TS Varchar2 ) Is
Select * From DBA_DATA_FILES
Where TABLESPACE_NAME = P_TS
Order By FILE_ID;
-- Tablespace information
Cursor C_Tablespace_Info ( P_TS Varchar2 ) Is
Select * From DBA_TABLESPACES
Where TABLESPACE_NAME = P_TS;
-- SYSTEM rollback segment is created implicitly hence not selected.
Cursor C_Rollback_Segs ( P_TS Varchar2 ) Is
Select * From DBA_ROLLBACK_SEGS
Where TABLESPACE_NAME = P_TS
And SEGMENT_NAME <> 'SYSTEM'
Order By SEGMENT_ID;
Cursor C_Logfile ( L_GROUP_NUM In Varchar2 ) Is
Select A.Group#, A.Bytes, A.Members, B.Member
From V$LOG A, V$LOGFILE B
Where A.Group# = B.Group#
And A.Group# = L_GROUP_NUM
Order By A.Group#;
FirstTime Boolean;
L_LINE Varchar2(2000); -- A line to output
L_SID Varchar2(20); -- Oracle Database Name
L_LOGMODE Varchar2(50); -- Archivelog or not
L_CHRSET Varchar2(50); -- NLS character set
L_GROUPS Number(9); -- Nos of Log Groups
L_LOGSIZE Number(9); -- Logfile size
L_MEMBERS Number(9); -- Nos Members per Group
L6 Varchar2(9); -- 6 spaces
L4 Varchar2(9); -- 4 spaces
L2 Varchar2(9); -- 2 spaces
FirstTime := TRUE; -- For groups
For L_INDEX In 1.. L_GROUPS Loop
If FirstTime Then
FirstTime := FALSE; -- For groups
L_LINE := ' Group '|| To_Char(L_INDEX) || ' (';
Else
L_LINE := ' ,Group '|| To_Char(L_INDEX) || ' (';
End If;
FirstTime := TRUE; -- For members
For Rec_Logfile In C_Logfile ( L_INDEX ) Loop
If FirstTime Then
FirstTime := FALSE; -- For members
Else
L_LINE := L_LINE ||Chr(10)||L6||L2||' ,';
End If;
L_LINE := L_LINE||''''||Rec_Logfile.MEMBER||'''';
End Loop;
L_LINE := L_LINE || Chr(10)||L6||L2||
' ) Size '||To_Char(L_LOGSIZE)||' K ';
dbms_output.put_line(L_LINE);
End Loop;
--
-- The bootstrapping stuff ...
--
L_LINE := '/'||Chr(10)||Chr(10)||
'Set TERMOUT Off ECHO Off'||Chr(10)||
'@?/rdbms/admin/catalog.sql'||Chr(10)||
'@?/rdbms/admin/catproc.sql'||Chr(10)||
'Set TERMOUT On ECHO On'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
--
-- The Rollback segments in the SYSTEM tablespace
--
Reply With Quote
Bookmarks