oracle9i SQL Scripts

問題：
在建立oracle9i 資料庫時，要執行指令碼來建立資料字典等。
這些指令碼有哪些？各有什麼作用？[@more@]

解：
references << C:DownloadsB10501_01server.920a96536ch53.htm >>

create database ...; 呼叫了 sql.bsq

建立資料字典有三個指令碼：
catalog.sql 必需
catproc.sql 必需
catclust.sql RAC必需

------------------------------------------------------------------
其它常用指令碼說明：
ScriptName NeededFor Run By Description
----------- ------------------------ --------- -------------------------------------
catblock.sql Performance management sys
catexp7.sql Exporting data to Oracle7 SYS
catoctk.sql Security SYS Creates the Oracle Cryptographic Toolkit package
dbmspool.sql Performance management SYS or SYSDBA Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool
utlexpt1.sql Constraints Any user Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints. Can handle both physical and logical rowids.
(這個在資料去重時用到。另外一個名稱是utlexcpt.sql 內容一樣，前者是1999年echong修改過的。後者是92年glumpkin從EXCEPT.SQL 改名來的。
@?/rdbms/admin/utlexcpt.sql
alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions;
create table dups as select * from cz where rowid in (select row_id from exceptions);
delete from cz where rowid in ( select row_id from exceptions);
insert into cz select distinct * from dups;
select *from cz;

到oracle10g之後，省事兒，去重可以使用insert into error logging子句了。參考另外一個筆記。
)
utllockt.sql Performance monitoring SYS or SYSDBA Displays a lock wait-for graph, in tree structure format
(使用這個utllockt.sql 可以隨時查詢會話之間鎖的等待 執行之前，
set linesize 120
@?/rdbms/admin/utllockt
)
utlrp.sql PL/SQL SYS Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.
utlsampl.sql Examples SYS or any user with DBA role Creates sample tables, such as emp and dept, and users, such as scott
(要新增scott使用者，來做練習，用這個建scott使用者 和 emp 表)
utltkprf.sql Performance management SYS Creates the TKPROFER role to allow the TKPROF profiling utility to be run by non-DBA users
(alter session set sql_trace=true;
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
Alter session set SQL_Trace = true;
執行應用程式
Alter session set SQL_Trace=false;
TKPROF inputfile outputfile [optional Parameters]
tkprof orclshad.trc trace.out sys=no explain=scott/tiger
)
utlvalid.sql Partitioned tables Any user Creates tables required for storing output of ANALYZE TABLE ...VALIDATE STRUCTURE of a partitioned table
(如果要對分割槽表進行analyze ， 需要先執行這個指令碼，否則報ORA-14508
e.g ANALYZE TABLE myparttable validate structure cascade;
)
utlxplan.sql Performance management Any user Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN statement
(@utlxplan.sql ; explain plan for select ... ; @utlxplp.sql)