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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1060532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-1060532/