set feedback offset trimspool onset lines 180set serveroutput on size 900000set verify offWHENEVER SQLERROR EXIT FAILURE ROLLBACK;WHENEVER OSERROR EXIT FAILURE ROLLBACK;[@more@]
REM $Header: auclondb.sql 115.3 2010/10/11 09:50:58 gong noship $
REM
REM dbdrv: none
REM +==========================================================================+
REM | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA |
REM | All rights reserved. |
REM +==========================================================================+
REM | NAME
REM | auclondb.sql
REM |
REM | DESCRIPTION
REM |
REM | Script to create script (aucrdb.sql) that creates a database with
REM | tablespaces and file structures similar to the database against
REM | which the script is run.
REM |
REM | USAGE
REM |
REM | sqlplus @auclondb.sql <10|11>
REM |
REM +==========================================================================+
set feedback off
set trimspool on
set lines 180
set serveroutput on size 900000
set verify off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
DEFINE target_dbver="&1"
spool aucrdb.sql
declare
DEF_MAX_DB_FILES CONSTANT number := 500;
DEF_PATH_PREFIX CONSTANT varchar2(200) := '?/dbf/';
L_TABCHAR CONSTANT varchar2(1) := ' ';
CREATE_DATABASE CONSTANT varchar2(30) := 'CRDB';
-- Fixed bug 3634436: added name for the case such that exported database
-- version less than 10. i.e. 8i or 9i where there is no such
-- a datafile name.
SYSAUX_FILENAME CONSTANT varchar2(30) := '?/dbf/sysaux01.dbf';
QUOTE_START CONSTANT varchar2(1) := '"';
QUOTE_END CONSTANT varchar2(1) := '"';
L_DB_FILES number;
L_CHARSET varchar2(100);
L_NCHARSET varchar2(100);
L_INSTANCE_NAME varchar2(30);
L_INSTANCE_VERSTR varchar2(30);
L_INSTANCE_MAJOR_VER number;
L_TARGET_DBVER number;
type t_tablespace is table of varchar2(30) index by binary_integer;
rollback_tablespaces t_tablespace;
rollback_tbsp_initialized boolean := FALSE;
procedure print(msg in varchar2,
indent in number default 0) is
begin
dbms_output.put(rpad(' ', indent+1, L_TABCHAR)||nvl(msg, ' '));
end;
procedure println(msg in varchar2 default null,
indent in number default 0) is
begin
print(nvl(msg, L_TABCHAR), indent);
dbms_output.put_line(L_TABCHAR);
end;
procedure get_instance_info(X_instance_name out NOCOPY varchar2,
X_instance_version out NOCOPY varchar2,
X_instance_major_ver out NOCOPY number)
is
begin
select upper(instance_name),
version,
substr(version, 1, instr(version, '.', 1)-1)
into X_instance_name, X_instance_version, X_instance_major_ver
from v$instance
where rownum = 1;
end;
function get_name(X_name varchar2) return varchar2
is
begin
return(QUOTE_START||X_name||QUOTE_END);
end;
procedure header_comments
is
l_date_time varchar2(30);
begin
l_date_time := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
println(rpad('REM ', 79, '='));
println('REM ');
println('REM NAME');
println('REM aucrdb.sql');
println('REM ');
println('REM DESCRIPTION');
println('REM ');
println('REM Script to create a database with tablespaces and files');
println('REM similar to that of the database against which the script');
println('REM was generated.');
println('REM ');
println('REM Database Instance : '||l_instance_name);
println('REM Database Version : '||l_instance_verstr);
println('REM Creation Date : '||l_date_time);
println('REM ');
println('REM NOTES');
println('REM BEFORE RUNNING THE SCRIPT YOU MUST REVIEW');
println('REM IT FOR THE FOLLOWING : ');
println('REM - Database Characterset information');
println('REM - Location and size of the data files');
println('REM - Tablespace storage information');
println('REM - Rollback Segment information');
println('REM ');
println(rpad('REM ', 79, '='));
end;
procedure section_comments(X_msg in varchar2)
is
begin
println;
println(rpad('REM ', 75, '-'));
println('REM '||X_msg);
println(rpad('REM ', 75, '-'));
println;
end;
function get_db_param(X_param in varchar2) return varchar2
is
l_param_value varchar2(255);
begin
select value
into l_param_value
from v$parameter
where name = X_param
and rownum = 1;
return(l_param_value);
exception
when NO_DATA_FOUND then
return(null);
end;
function file_name(X_file_name in varchar2) return varchar2
is
l_slash_pos number;
begin
l_slash_pos := instr(X_file_name, '/', -1);
if (l_slash_pos = 0) then
l_slash_pos := instr(X_file_name, '', -1);
end if;
if (l_slash_pos > 0) then
return(''''||DEF_PATH_PREFIX||
substr(X_file_name, l_slash_pos+1)||'''');
else
return(''''||X_file_name||'''');
end if;
end;
function size_str(X_sizeBytes in number) return varchar2
is
begin
if (X_sizeBytes >= (1024*1024))
then
return(round(X_sizeBytes/(1024*1024))||'M');
elsif (X_sizeBytes >= 1024)
then
return(round(X_sizeBytes/(1024))||'K');
else
return(X_sizeBytes);
end if;
end;
function get_nls_param(X_param in varchar2) return varchar2
is
l_param_value varchar2(255);
begin
select value
into l_param_value
from v$nls_parameters
where parameter = X_param
and rownum = 1;
return(l_param_value);
exception
when NO_DATA_FOUND then
return(null);
end;
procedure log_file_info
is
cursor c_log_group is
select group#, bytes, members
from v$log
order by group#;
cursor c_log_file(X_group number) is
select group#, member
from v$logfile
where group# = X_group
order by member;
begin
println('LOGFILE');
for log_grp in c_log_group
loop
if (c_log_group%rowcount > 1) then
println(',');
end if;
println('GROUP '||log_grp.group#||' (', 1);
for log_file in c_log_file(log_grp.group#)
loop
if (c_log_file%rowcount > 1) then
println(',');
end if;
print(' '||file_name(log_file.member), 1);
end loop;
println('');
print(') SIZE '||size_str(log_grp.bytes), 1);
end loop;
println('');
end;
procedure system_tablespace_info
is
cursor c_file(tbsp in varchar2) is
select file_name, bytes
from dba_data_files
where tablespace_name = tbsp
order by file_name;
cursor c_file_temp(tbsp in varchar2) is
select file_name, bytes
from dba_temp_files
where tablespace_name = tbsp
order by file_name;
cursor c_undo_tablespaces(def_undo in varchar2) is
select tablespace_name, contents
from dba_tablespaces
where contents = 'UNDO' and
tablespace_name <> upper(def_undo);
cursor c_temp_tablespaces is
select tablespace_name, contents
from dba_tablespaces
where contents = 'TEMPORARY';
cursor c_tgroup is
select group_name, tablespace_name
from dba_tablespace_groups;
default_undo_tbsp varchar2(30);
default_temp_tbsp varchar2(30);
default_temp_done boolean := FALSE;
begin
/* SYSTEM tablespace */
println('DATAFILE');
for c_rec in c_file('SYSTEM')
loop
if (c_file%rowcount > 1)
then
println(',');
end if;
print(file_name(c_rec.file_name)||
' SIZE '||size_str(c_rec.bytes), 1);
end loop;
println('');
/* SYSAUX tablespace */
println('SYSAUX DATAFILE');
for c_rec in c_file('SYSAUX')
loop
if (c_file%rowcount > 1)
then
println(',');
end if;
print(file_name(c_rec.file_name)||
' SIZE '||size_str(c_rec.bytes), 1);
end loop;
println('');
/* Default undo tablespace */
select value into default_undo_tbsp from v$parameter where name='undo_tablespace';
println('UNDO TABLESPACE "' || default_undo_tbsp ||'"');
println('DATAFILE');
for c_rec in c_file(default_undo_tbsp)
loop
if (c_file%rowcount > 1)
then
println(',');
end if;
print(file_name(c_rec.file_name)||
' SIZE '||size_str(c_rec.bytes), 1);
end loop;
println('');
println('EXTENT MANAGEMENT LOCAL');
/* Temporary tablespaces */
for trec in c_temp_tablespaces
loop
if (default_temp_done=FALSE )
then
println('DEFAULT TEMPORARY TABLESPACE "'||trec.tablespace_name||'"');
else
println('CREATE TEMPORARY TABLESPACE "'||trec.tablespace_name||'"');
end if;
println('TEMPFILE');
for frec in c_file_temp(trec.tablespace_name)
loop
if (c_file_temp%rowcount > 1)
then
println(',');
end if;
print(file_name(frec.file_name)||' SIZE '||size_str(frec.bytes), 1);
end loop;
println('');
println('EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M');
println('/');
println('');
default_temp_done := TRUE;
end loop;
/* other undo tablespaces */
for trec in c_undo_tablespaces(default_undo_tbsp)
loop
println('CREATE UNDO TABLESPACE "'||trec.tablespace_name||'"');
println('DATAFILE');
for frec in c_file(trec.tablespace_name)
loop
if (c_file%rowcount > 1)
then
println(',');
end if;
print(file_name(frec.file_name)||' SIZE '||size_str(frec.bytes));
end loop;
println('');
println('EXTENT MANAGEMENT LOCAL AUTOALLOCATE');
println('/');
println('');
end loop;
/* create tablespace groups */
for trec in c_tgroup
loop
println('ALTER TABLESPACE "'||trec.tablespace_name||'" TABLESPACE GROUP "'||trec.group_name||'";');
end loop;
/* change the default temporary tablespace */
select property_value into default_temp_tbsp from DATABASE_PROPERTIES
where property_name='DEFAULT_TEMP_TABLESPACE';
if (default_temp_tbsp <> 'SYSTEM')
then
println('ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "'||default_temp_tbsp||'";');
println('');
println('prompt');
println('prompt Ignore any errors related to the setting');
println('prompt of the default temporary tablespace');
println('prompt');
println('');
end if;
end;
procedure tablespace_info is
cursor c_tablespaces is
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX')
and contents not in ('UNDO','TEMPORARY')
order by tablespace_name;
cursor c_file(X_tablespace in varchar2) is
select file_name, bytes
from dba_data_files
where tablespace_name = X_tablespace
order by file_name;
begin
section_comments('Create Tablespaces');
for trec in c_tablespaces
loop
println('CREATE TABLESPACE '||
get_name(trec.tablespace_name));
println('DATAFILE');
for frec in c_file(trec.tablespace_name)
loop
if (c_file%rowcount > 1) then
println(',');
end if;
print(file_name(frec.file_name)||
' SIZE '||size_str(frec.bytes), 1);
end loop;
println('');
/* All tablespaces are converted to locally managed, auto segment */
println('EXTENT MANAGEMENT LOCAL AUTOALLOCATE');
println('SEGMENT SPACE MANAGEMENT AUTO');
println('/');
println('');
end loop;
end;
procedure create_db
is
begin
section_comments('Create Database');
println('CREATE DATABASE');
println('MAXDATAFILES '||nvl(L_DB_FILES, DEF_MAX_DB_FILES), 1);
println('CHARACTER SET '||L_CHARSET, 1);
if (L_CHARSET <> L_NCHARSET) then
println('NATIONAL CHARACTER SET '||L_NCHARSET, 1);
end if;
log_file_info;
system_tablespace_info;
end;
/* Main procedure */
begin
if ('&target_dbver' not in ('10','11')) then
raise_application_error(-20001, 'Invalid target database version : '||
'&target_dbver');
end if;
L_TARGET_DBVER := '&target_dbver';
get_instance_info(L_INSTANCE_NAME, L_INSTANCE_VERSTR, L_INSTANCE_MAJOR_VER);
if (L_TARGET_DBVER < L_INSTANCE_MAJOR_VER) then
raise_application_error(-20001,
'Instance database version ('|| L_INSTANCE_MAJOR_VER||
') cannot be higher than the target version('||
L_TARGET_DBVER||')');
end if;
L_DB_FILES := get_db_param('db_files');
L_CHARSET := get_nls_param('NLS_CHARACTERSET');
L_NCHARSET := get_nls_param('NLS_NCHAR_CHARACTERSET');
/*
if (L_CHARSET = 'UTF8') then
L_NCHARSET := 'UTF8';
else
L_NCHARSET := 'AL16UTF16';
end if;
*/
header_comments;
create_db;
tablespace_info;
end;
.
spool aucrdb.sql
/
spool off
commit;
exit;