Re: recreate database script not work

FOn 30 Mar 2007 13:33:41 -0700, "Hai" <perfect77063_at_gmail.com> wrote:

>Hi
>I got a script which is supposed to regenerate database systax.
>However I have tested several time, the output file 'init.sql' is
>empty. I tried to debug, and found out there is no date in DBASE_TEMP
>table. This script 'gen_dbse_9_re-create_a_database.sql' is associated
>with another script 'gen_dbse_9_re-create_a_tablespace.sql' to
>regenerating create tablespace script.
>
>Could you take a look and see what is wrong?
>=================================================================
>REM gen_dbse_9.sql
>REM This script can be used to generate the commands to re-create an
>existing database
>REM This script is applicable only in Oracle9i onwards
>
>REM set echo off term on verify off feedback off pagesize 0 trimspool
>on
>
>select 'Creating database build script...' from DUAL;
>
>accept oracle_sid prompt "Enter the SID of the database: "
>
>accept init_loc prompt "Enter the location for the init.ora file: "
>
>set term off
>
>drop table DBASE_TEMP;
>
>create table DBASE_TEMP (
> Lineno NUMBER,
> Text VARCHAR2(100));
>
>declare
> cursor PARAM_CURSOR is
> select Name,
> Value,
> Description
> from V$PARAMETER
> where Isdefault = 'FALSE'
> order by Name;
>
> cursor LOGFILE_CURSOR (LF_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# = LF_Group_Num
> order by A.Group#;
>
> cursor DFILE_CURSOR is
> select A.Maxextend,
> A.Inc,
> B.File_Name,
> B.Bytes
> from SYS.FILEXT$ A,
> DBA_DATA_FILES B
> where B.File_ID = A.File#(+)
> and Tablespace_Name = 'SYSTEM'
> order by File_ID;
>
> cursor TSPACE_CURSOR is
> select Text
> from TSPACE_TEMP
> order by Tspace_Name, Lineno;
>
> cursor TEMP_SPACE_CURSOR is
> select Username,
> Default_Tablespace,
> Temporary_Tablespace
> from DBA_USERS
> where Username in ('SYS','SYSTEM');
>
> cursor ROLLBACK_SEGS_CURSOR is
> select A.Segment_Name,
> A.Owner,
> A.Tablespace_Name,
> A.Initial_Extent,
> A.Next_Extent,
> A.Min_Extents,
> A.Max_Extents,
> A.Pct_Increase,
> A.Status,
> B.Optsize
> from DBA_ROLLBACK_SEGS A,
> V$ROLLSTAT B
> where A.Segment_Id = B.USN(+)
> and A.Segment_id > 0
> and A.Segment_Name != 'RB_TEMP'
> order by A.Owner, A.Segment_Name;
>
> Lv_PC_Name V$PARAMETER.Name%TYPE;
> Lv_PC_Value V$PARAMETER.Value%TYPE;
> Lv_PC_Description V$PARAMETER.Description%TYPE;
> Lv_LF_Group_Num V$LOG.Group#%TYPE;
> Lv_LF_Bytes V$LOG.Bytes%TYPE;
> Lv_LF_Members V$LOG.Members%TYPE;
> Lv_LF_Member V$LOGFILE.Member%TYPE;
> Lv_DF_MaxExtend SYS.FILEXT$.MaxExtend%TYPE;
> Lv_DF_Inc SYS.FILEXT$.Inc%TYPE;
> Lv_DF_File_Name DBA_DATA_FILES.File_Name%TYPE;
> Lv_DF_Bytes DBA_DATA_FILES.Bytes%TYPE;
> Lv_TS_Text TSPACE_TEMP.Text%TYPE;
> Lv_TE_Temp_Tspace DBA_USERS.Temporary_Tablespace%TYPE;
> Lv_TE_Deflt_Tspace DBA_USERS.Default_Tablespace%TYPE;
> Lv_TE_Username DBA_USERS.Username%TYPE;
> Lv_RB_Segment_Name DBA_ROLLBACK_SEGS.Segment_Name%TYPE;
> Lv_RB_Owner DBA_ROLLBACK_SEGS.Owner%TYPE;
> Lv_RB_Tablespace_Name DBA_ROLLBACK_SEGS.Tablespace_Name%TYPE;
> Lv_RB_Initial_Extent DBA_ROLLBACK_SEGS.Initial_Extent%TYPE;
> Lv_RB_Next_Extent DBA_ROLLBACK_SEGS.Next_Extent%TYPE;
> Lv_RB_Min_Extents DBA_ROLLBACK_SEGS.Min_Extents%TYPE;
> Lv_RB_Max_Extents DBA_ROLLBACK_SEGS.Max_Extents%TYPE;
> Lv_RB_Pct_Increase DBA_ROLLBACK_SEGS.Pct_Increase%TYPE;
> Lv_RB_Status DBA_ROLLBACK_SEGS.Status%TYPE;
> Lv_RB_Optsize V$ROLLSTAT.Optsize%TYPE;
>
> Lv_FileHandle UTL_FILE.FILE_TYPE;
>
> Lv_String VARCHAR2(2000);
> Lv_Lineno NUMBER := 0;
> Lv_First_Time BOOLEAN;
> Lv_Size NUMBER;
> Lv_No_Of_Groups INTEGER;
> Lv_InitOra_Location VARCHAR2(40);
> Lv_InitOra_Name VARCHAR2(30);
> Lv_Database_Name VARCHAR2(8);
> Lv_MaxLogFiles INTEGER;
> Lv_MaxLogMembers INTEGER;
> Lv_LogMode VARCHAR2(15);
> Lv_CharacterSet VARCHAR2(64);
>
> Lv_Nchar_CharacterSet VARCHAR2(64);
> Lv_Dbtimezone VARCHAR2(64);
> Lv_Default_Temp_Tablespace VARCHAR2(64);
>
> Lv_Group INTEGER;
> Lv_Members INTEGER;
> Lv_Block_Size NUMBER;
> Lv_DF_Count NUMBER := 0;
> Lv_Platform_Slash VARCHAR2(1);
>
> procedure WRITE_OUT(P_Line INTEGER, P_String VARCHAR2) is
> begin
> insert into dbase_TEMP (Lineno, Text)
> values (P_Line,P_String);
> end;
>
>begin
>
> Lv_Lineno := 1;
> Lv_InitOra_Location := '&&init_loc';
> Lv_InitOra_Name := 'init' ||
> '&&oracle_sid'||
> '.ora';
>
> Lv_FileHandle :=
> UTL_FILE.FOPEN(Lv_InitOra_Location,Lv_InitOra_Name,'W');
>
> open PARAM_CURSOR;
> loop
> fetch PARAM_CURSOR
> into Lv_PC_Name,
> Lv_PC_Value,
> Lv_PC_Description;
> exit when PARAM_CURSOR%NOTFOUND;
>
> if (UPPER(Lv_PC_Name) = 'DB_NAME')
> then
> Lv_Database_Name := Lv_PC_Value;
> end if;
>
> Lv_String := '# ' || Lv_PC_Description;
> UTL_FILE.PUTF(Lv_FileHandle,'%s\n',Lv_String);
>
> Lv_String := RPAD(Lv_PC_Name,45) || ' = ' || Lv_PC_Value;
> UTL_FILE.PUTF(Lv_FileHandle,'%s\n\n',Lv_String);
> end loop;
>
> close PARAM_CURSOR;
> UTL_FILE.FCLOSE(Lv_FileHandle);
>
> Lv_Lineno := 1;
>
> Lv_String := 'CREATE DATABASE ' || Lv_Database_Name;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) || 'CONTROLFILE REUSE';
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> select MAX(Group#)
> into Lv_No_Of_Groups
> from V$LOG;
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,LPAD(' ',5) ||'LOGFILE');
> Lv_First_Time := TRUE;
>
> for Lv_Index IN 1 .. Lv_No_Of_Groups
> loop
> open LOGFILE_CURSOR (Lv_Index);
> loop
> fetch LOGFILE_CURSOR
> into Lv_LF_Group_Num,
> Lv_LF_Bytes,
> Lv_LF_Members,
> Lv_LF_Member;
> exit when LOGFILE_CURSOR%NOTFOUND;
>
> Lv_Size := Lv_LF_Bytes / 1024;
>
> if (Lv_First_Time = TRUE)
> then
> Lv_String := ' ';
> Lv_First_Time := FALSE;
> else
> Lv_String := ' ,';
> end if;
>
> if (Lv_LF_Members = 1)
> then
> Lv_String := Lv_String ||
> 'GROUP ' ||
> LPAD(Lv_LF_Group_Num,3) ||
> ' ''' ||
> Lv_LF_Member ||
> '''' ||
> ' SIZE ' ||
> Lv_Size ||
> 'K REUSE';
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
> else
> if (LOGFILE_CURSOR%ROWCOUNT = 1)
> then
> Lv_String := Lv_String ||
> 'GROUP ' ||
> LPAD(Lv_LF_Group_Num,3) ||
> ' (''' ||
> Lv_LF_Member ||
> '''' ;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
> else
> Lv_String := LPAD(' ',15) ||
> ',''' ||
> Lv_LF_Member ||
> '''';
> if (LOGFILE_CURSOR%ROWCOUNT = Lv_LF_Members)
> then
> Lv_String := Lv_String ||
> ') SIZE ' ||
> Lv_Size ||
> 'K REUSE';
> end if;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, Lv_String);
> end if;
>
> end if;
> end loop;
> CLOSE LOGFILE_CURSOR;
> end loop;
>
> select Value
> into Lv_Block_Size
> from SYS.V_$PARAMETER
> where UPPER(Name) = 'DB_BLOCK_SIZE';
>
> open DFILE_CURSOR;
>
> loop
> fetch DFILE_CURSOR into Lv_DF_MaxExtend,
> Lv_DF_Inc,
> Lv_DF_File_Name,
> Lv_DF_Bytes;
> exit when DFILE_CURSOR%NOTFOUND;
> Lv_DF_Count := Lv_DF_Count + 1;
>
> if (Lv_DF_Count > 1)
> then
> Lv_String := ' ,''';
> else
> Lv_String := 'DATAFILE ''';
> end if;
>
> Lv_String := LPAD(' ',5) ||
> Lv_String ||
> Lv_DF_File_Name ||
> ''' SIZE ' ||
> (Lv_DF_Bytes)/1024 ||
> 'K REUSE' ;
>
> WRITE_OUT(Lv_Lineno, Lv_String);
> Lv_Lineno := Lv_Lineno + 1;
>
> if (Lv_DF_MaxExtend IS NOT NULL)
> then
> Lv_String := LPAD(' ',12)
>||
> 'AUTOEXTEND ON NEXT '
>||
> (Lv_DF_Inc * Lv_Block_Size) / 1024
>||
> 'K MAXSIZE '
>||
> (Lv_DF_MaxExtend * Lv_Block_Size) / 1024
>||
> 'K';
>
> WRITE_OUT(Lv_Lineno, Lv_String);
> Lv_Lineno := Lv_Lineno + 1;
> end if;
> end loop;
> close DFILE_CURSOR;
>
> select MAX(Group#),
> MAX(Members)
> into Lv_Group,
> Lv_Members
> from SYS.V_$LOG;
>
> Lv_MaxLogFiles := Lv_Group * Lv_Members * 4;
> Lv_MaxLogMembers := Lv_Members * 2;
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) || 'MAXLOGFILES ' || Lv_MaxLogFiles;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) || 'MAXLOGMEMBERS ' || Lv_MaxLogMembers;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXLOGHISTORY 160');
>
> Lv_Lineno := Lv_Lineno +1;
> WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXDATAFILES 255');
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXINSTANCES 1');
>
> select log_Mode
> into Lv_LogMode
> from SYS.V_$DATABASE;
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,LPAD(' ',5) || Lv_LogMode);
>
> select Value
> into Lv_Nchar_CharacterSet
> from V$NLS_PARAMETERS
> where UPPER(parameter) = 'NLS_NCHAR_CHARACTERSET';
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) || 'NATIONAL CHARACTER SET ' ||
>Lv_Nchar_CharacterSet;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> select Property_Value
> into Lv_Dbtimezone
> from DATABASE_PROPERTIES
> where UPPER(property_name) = 'DBTIMEZONE';
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) || 'SET TIME_ZONE = ''' || Lv_Dbtimezone
>|| '''';
> WRITE_OUT(Lv_Lineno,Lv_String);
>
>
> select Value
> into Lv_CharacterSet
> from V$NLS_PARAMETERS
> where UPPER(parameter) = 'NLS_CHARACTERSET';
>
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := LPAD(' ',5) ||
> 'CHARACTER SET ' ||
> Lv_CharacterSet ||
> ';';
> WRITE_OUT(Lv_Lineno,Lv_String);
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Create a Temporary Rollback Segment');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Create Rollback Segment rb_temp;');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Alter Rollback Segment rb_temp Online;');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'commit;');
>
> if (INSTR(Lv_DF_File_Name,'/',1) > 0)
> then
> Lv_Platform_Slash := '/';
> else
> Lv_Platform_Slash := '\';
> end if;
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Start catalog.sql script');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
> Lv_String := '@$ORACLE_HOME' ||
> Lv_Platform_Slash ||
> 'rdbms' ||
> Lv_Platform_Slash ||
> 'admin' ||
> Lv_Platform_Slash ||
> 'catalog.sql;';
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, Lv_String);
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'commit;');
>
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Start catproc.sql script');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
> Lv_String := '@$ORACLE_HOME' ||
> Lv_Platform_Slash ||
> 'rdbms' ||
> Lv_Platform_Slash ||
> 'admin' ||
> Lv_Platform_Slash ||
> 'catproc.sql;';
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, Lv_String);
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'commit;');
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Create Tablespaces');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
> open TSPACE_CURSOR;
> loop
> fetch TSPACE_CURSOR
> into Lv_TS_Text;
> exit when TSPACE_CURSOR%NOTFOUND;
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_TS_Text);
> end loop;
> close TSPACE_CURSOR;
>
> select Property_Value
> into Lv_Default_Temp_Tablespace
> from DATABASE_PROPERTIES
> where UPPER(property_name) = 'DEFAULT_TEMP_TABLESPACE';
>
> Lv_Lineno := Lv_Lineno + 1;
> Lv_String := ' ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ' ||
>Lv_Default_Temp_Tablespace || ' ;';
> WRITE_OUT(Lv_Lineno,Lv_String);
>
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Alter sys and system ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
> open TEMP_SPACE_CURSOR;
> loop
> fetch TEMP_SPACE_CURSOR
> into Lv_TE_Username,
> Lv_TE_Deflt_Tspace,
> Lv_TE_Temp_Tspace;
> exit when TEMP_SPACE_CURSOR%NOTFOUND;
>
> Lv_String := 'Alter user ' ||
> Lv_TE_Username ||
> ' default tablespace ' ||
> Lv_TE_Deflt_Tspace ||
> ' temporary tablespace ' ||
> Lv_TE_Temp_Tspace ||
> ';';
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
> end loop;
> close TEMP_SPACE_CURSOR;
>
> Lv_Lineno := Lv_Lineno + 1;
>
> WRITE_OUT(Lv_Lineno, ' ');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem Create Rollback segments');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno, ' ');
>
> open ROLLBACK_SEGS_CURSOR;
> loop
> fetch ROLLBACK_SEGS_CURSOR
> into Lv_RB_Segment_Name,
> Lv_RB_Owner,
> Lv_RB_Tablespace_Name,
> Lv_RB_Initial_Extent,
> Lv_RB_Next_Extent,
> Lv_RB_Min_Extents,
> Lv_RB_Max_Extents,
> Lv_RB_Pct_Increase,
> Lv_RB_Status,
> Lv_RB_Optsize;
> exit when ROLLBACK_SEGS_CURSOR%NOTFOUND;
>
> if (UPPER(Lv_RB_Owner) = 'PUBLIC')
> then
> Lv_String := 'CREATE PUBLIC ROLLBACK SEGMENT ';
> else
> Lv_String := 'CREATE ROLLBACK SEGMENT ';
> end if;
>
> Lv_String := Lv_String ||
> Lv_RB_Segment_Name;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_String := ' ' ||
> 'TABLESPACE ' ||
> Lv_RB_Tablespace_Name ;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,' STORAGE (');
>
> Lv_String := ' ' ||
> 'Initial ' ||
> (Lv_RB_Initial_Extent) / 1024 ||
> ' K' ;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_String := ' ' ||
> 'Next ' ||
> (Lv_RB_Next_Extent) / 1024 ||
> ' K' ;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_String := ' ' ||
> 'Minextents ' ||
> Lv_RB_Min_Extents;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> Lv_String := ' ' ||
> 'Maxextents ' ||
> Lv_RB_Max_Extents;
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
> if ( Lv_RB_Optsize is NOT NULL)
> then
> Lv_String := ' ' ||
> 'Optimal ' ||
> (Lv_RB_Optsize);
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
> end if;
>
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,' );');
>
> Lv_String := 'Alter Rollback Segment ' ||
> Lv_RB_Segment_Name ||
> ' ' ||
> Lv_RB_STATUS ||
> ';';
> Lv_Lineno := Lv_Lineno + 1;
> WRITE_OUT(Lv_Lineno,Lv_String);
>
>
> end loop;
> close ROLLBACK_SEGS_CURSOR;
>
>exception
> WHEN NO_DATA_FOUND
> THEN
> DBMS_OUTPUT.PUT_LINE ('Reached end of file');
>
> WHEN UTL_FILE.INTERNAL_ERROR
> THEN
> DBMS_OUTPUT.PUT_LINE ('Internal File Error');
>
> WHEN UTL_FILE.INVALID_FILEHANDLE
> THEN
> DBMS_OUTPUT.PUT_LINE ('Invalid File Handle');
>
> WHEN UTL_FILE.INVALID_MODE
> THEN
> DBMS_OUTPUT.PUT_LINE ('Invalid file mode specified');
>
> WHEN UTL_FILE.INVALID_OPERATION
> THEN
> DBMS_OUTPUT.PUT_LINE ('Invalid file operation specified');
>
> WHEN UTL_FILE.INVALID_PATH
> THEN
> DBMS_OUTPUT.PUT_LINE ('Invalid file path specified');
>
> WHEN UTL_FILE.WRITE_ERROR
> THEN
> DBMS_OUTPUT.PUT_LINE ('A Write error occurred');
>
> WHEN UTL_FILE.READ_ERROR
> THEN
> DBMS_OUTPUT.PUT_LINE ('A file read error occurred');
>
> WHEN OTHERS
> THEN
> DBMS_OUTPUT.PUT_LINE ('An unknown exception occurred');
>end;
>/
>set trimspool on
>spool init.sql
>select text
> from DBASE_TEMP
> order by Lineno
>/
>spool off

The script is just a very inefficient and cumbersome attempt to mimic the functionality of alter database backup controlfile to trace. The results of this trace file can easily be edited into a create database statement.
Also rollback segments are a thing of the past, and the script doesn't allow for an undo tablespace. (Occurs in the create database statement). So if you *do* have an undo tablespace in your init.ora, the script will fail anyway.
Also dbca allows you to save all create scripts prior to execution. Looking at the coding style, it is probably a former KLoney script, and someone updated it to 9i and failed miserably. I would stop trying to repair it.
The strange also is there is NO commit in the script, so if it does fail, you loose the last line, and the rest is committed.

-- 

Sybrand Bakker
Senior Oracle DBA