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;[@more@]

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;

oracle 10g ,11g 自動生成建立表空間的語句

相關文章