相信看过TOM的《ORACLE 9i/10g/11g编程艺术》的同学还在怀恋TOM的配置环境。

sqlplus参数设置：

在$ORACLE_HOME/sqlplus/admin目录下。用vi编辑下面内容：
vi $ORACLE_HOME/sqlplus/admin/login.sql
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 700 --行宽
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

注意：必须从该文件所在目录内启动SQL*PLUS才有效

设置autotrace：
cd $ORACLE_HOME/rdbms/admin
以sys运行utlxplan.sql;
运行create public synonym plan_table for plan_table;
运行grant all on plan_table to public;

cd $ORACLE_HOME/sqlplus/admin;
以sys运行plustrce.sql;
运行grant plustrace to public;

set autotrace off;
set autotrace on;
set autotrace on explain;
set autotrace on statistics;
set autotrace traceonly;

配置Statspack：
以sysdba运行$ORACLE_HOME\rdbms\admin\spcreate.sql；
回滚脚本：spdrop.sql,安装日志：spcpkg.lis

runstats脚本：
exec runStats_pkg.rs_start;
insert into t1 select * from big_table where rownum <= 1000000;commit;
exec runStats_pkg.rs_middle;
begin for x in (select * from big_table where rownum<=1000000) loop insert into t2 values x; end loop; commit; end;
/
exec runStats_pkg.rs_stop;

mystat查看redo字节数:
@mystat "redo size"
update big_table set owner = lower(owner) where rownum <= 1000;
@mystat2

show_space用于打印数据库段的空间利用率：
exec show_space('BIG_TABLE');

《ORACLE 9i/10g/11g编程艺术》配置环境