oracle10 创建表,oracle 10g ,11g 自动生成创建表空间的语句

最新推荐文章于 2021-11-06 22:40:34 发布

智能迷宫

最新推荐文章于 2021-11-06 22:40:34 发布

阅读量97

点赞数

文章标签： oracle10 创建表

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@]

REM $Header: auclondb.sql 115.3 2010/10/11 09:50:58 gong noship $

REM

REM dbdrv: none

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)

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

begin

return(QUOTE_START||X_name||QUOTE_END);

end;

procedure header_comments

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)

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

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

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

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

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

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

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

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;

智能迷宫

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle10 创建表,oracle 10g ,11g 自动生成创建表空间的语句

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....
复制链接

扫一扫