Controlfile creation script

declare

cursor c1 is

select file_name from dba_data_files;

cursor c2 is

select a.group# grp,a.member mem,b.bytes/1024/1024  byt

from V$logfile a ,V$log b

where a.group#=b.group#

order by a.group#;

cursor c3 is

select tablespace_name,file_name,bytes/1024/1024 as tbyt from dba_temp_files;

max_grp pls_integer;

cnt PLS_INTEGER;

val1 PLS_INTEGER;

val2 PLS_INTEGER;

val3 PLS_INTEGER;

pval varchar2(100);

name varchar2(50);

log_mode varchar2(12);

begin

select name,log_mode into name,log_mode from V$database;

select PROPERTY_VALUE into pval from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';

DBMS_OUTPUT.PUT_LINE('CREATE CONTROLFILE SET DATABASE  '||name||'  RESETLOGS  '||log_mode);

select records_total into val1 from V$controlfile_record_section 

where type='REDO LOG';

select records_total into val2 from V$controlfile_record_section 

where type='DATAFILE';

select records_total into val3 from V$controlfile_record_section 

where type='LOG HISTORY';

DBMS_OUTPUT.PUT_LINE('MAXLOGFILES '||val1);

DBMS_OUTPUT.PUT_LINE('MAXDATAFILES '||val2);

DBMS_OUTPUT.PUT_LINE('MAXLOGHISTORY '||val3);

DBMS_OUTPUT.PUT_LINE('LOGFILE');

select max(group#) into max_grp from V$logfile;

select count(1) into cnt from DBA_DATA_FILES;

for rec1 in c2

loop

if rec1.grp!=max_grp then

DBMS_OUTPUT.PUT_LINE('GROUP '||rec1.grp||'  '''||rec1.mem||''''||'   size  '||rec1.byt||'M,');

else

DBMS_OUTPUT.PUT_LINE('GROUP '||rec1.grp||'  '''||rec1.mem||''''||'   size  '||rec1.byt||'M');

DBMS_OUTPUT.PUT_LINE('DATAFILE');

end if;

end loop;

for rec in c1

loop

if c1%rowcount!=cnt then

DBMS_OUTPUT.PUT_LINE(''''||rec.file_name||''',');

else

DBMS_OUTPUT.PUT_LINE(''''||rec.file_name||'''');

end if;

end loop;

DBMS_OUTPUT.PUT_LINE('CHARACTERSET    '||pval||';');

for rec2 in c3

loop

dbms_output.put_line('alter tablespace ' ||rec2.tablespace_name||' add tempfile ');

DBMS_OUTPUT.PUT_LINE(''''||rec2.file_name||''''||' SIZE '||rec2.tbyt||'M REUSE AUTOEXTEND OFF;');

end loop;

end;

/