happydba

statspack 的若干使用技巧

February 26th, 2009 happydba No comments

一如何修改statspack的脚本产生自定义报表？
通常statspack报表可以满足大部分的需要,有时我们需要对产生报表的脚本,进行一些微小的修改,这样产生的报表就更有用途啦.比如说某些SQL很多,但在statspack产生的报表中,每个SQL只显示5行,结果有些比较长的SQL就只能看到一部分;又如在top events部分,标准的报表只显示top 5,其实我们可以显示更多的events,那如何修改呢？

用编辑工具(在linux下用vi)打开$ORACLE_HOME/rdbms/admin/sprepins.sql
define top_n_events = 5; // top 5 events
define top_n_sql = 65; // top sql
define top_n_segstat = 5; // top 5 segstat
define num_rows_per_hash=5; // 每个SQL显示5行
就看到在该脚本中已经定义了一些常数,我们只需要把它改为我们需要的值就可以啦.
define top_n_events = 10; // top 10 events
define top_n_sql = 65; // top sql
define top_n_segstat = 10; // top 10 segstat
define num_rows_per_hash=10; // 每个SQL显示10行

二如何用crontab定期产生statspack的报表？
看了一段时间的statspack报表后,就懒于每天手工去产生一个报表,那如何产系统自动产生一个报表呢?
经过测试,用crontab可以方便地产生报表,然后通过sendmail直接发到相关人员的邮箱中.
[oracle@oracle sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile
/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s “perfstat report” ddd@eee.fff < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

说明:从早上8点到晚上8点之间进行快照收集,9点执行cron进程启动,产生报表的快照也限于当天收集的快照,将当天最小的snap_id与最大的snap_id放到两个文件中,在sheel中读出,并计算出一个报表名称,最后产生的报表通过sendmail发送到相关人员的邮箱,然后每天晚只需要收邮件就可以看到当天的报表啦。

Categories: 未分类 Tags: statspack

Oracle ASH 和Session Tracing

February 26th, 2009 happydba No comments

1.认识V$ACTIVE_SESSION_HISTORY视图

从Oracle10g开始引入了V$ACTIVE_SESSION_HISTORY视图，用于查询用户活动会话的历史信息。

1.1.相关参数设置

ASH缺省每一秒收集一下活动会话的情况,间隔时间由_ash_sampling_interval 参数确定。

V$ACTIVE_SESSION_HISTORY中的数据在被新数据周期性地覆盖前保留30 分钟，当数据从这个动态性能视图中清除时，这些数据被送到活动工作负载信息库（Active Workload Repository，AWR）中，它是一个基于磁盘的信息库。被清除的ASH（活动会话历史）数据可以在 DBA_HIST_ACTIVE_SESSION_HIST视图中看到，能够看到过去的会话的等待事件，在默认状态下，AWR中的数据7天后即被清除。

Sql代码

SQL> show parameter statistics;
NAME TYPE VALUE
—————- ——————– ——-
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

SQL> show parameter statistics;

NAME TYPE VALUE
------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

1.2.包含的内容

V$ACTIVE_SESSION_HISTORY包含top wait events, top SQL, top SQL command types,top sessions等等对于诊断故障非常有用的信息。

Sql代码

SQL> desc v$active_session_history;
Name Null? Type
———- ————– —————–
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3) –统计数字采集的时间
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
SESSION_TYPE VARCHAR2(10)
FLAGS NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_OPCODE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
TOP_LEVEL_SQL_ID VARCHAR2(13)
TOP_LEVEL_SQL_OPCODE NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30)
SQL_PLAN_OPTIONS VARCHAR2(30)
SQL_EXEC_ID NUMBER
SQL_EXEC_START DATE
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
QC_INSTANCE_ID NUMBER
QC_SESSION_ID NUMBER
QC_SESSION_SERIAL# NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
SESSION_STATE VARCHAR2(7)
TIME_WAITED NUMBER
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_SESSION NUMBER
BLOCKING_SESSION_SERIAL# NUMBER
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
CURRENT_ROW# NUMBER
CONSUMER_GROUP_ID NUMBER
XID RAW(8)
REMOTE_INSTANCE# NUMBER
IN_CONNECTION_MGMT VARCHAR2(1)
IN_PARSE VARCHAR2(1)
IN_HARD_PARSE VARCHAR2(1)
IN_SQL_EXECUTION VARCHAR2(1)
IN_PLSQL_EXECUTION VARCHAR2(1)
IN_PLSQL_RPC VARCHAR2(1)
IN_PLSQL_COMPILATION VARCHAR2(1)
IN_JAVA_EXECUTION VARCHAR2(1)
IN_BIND VARCHAR2(1)
IN_CURSOR_CLOSE VARCHAR2(1)
SERVICE_HASH NUMBER
PROGRAM VARCHAR2(48)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)

SQL> desc v$active_session_history;
Name Null? Type
------------ -------- ----------------------------
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3) --统计数字采集的时间
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
SESSION_TYPE VARCHAR2(10)
FLAGS NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_OPCODE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
TOP_LEVEL_SQL_ID VARCHAR2(13)
TOP_LEVEL_SQL_OPCODE NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30)
SQL_PLAN_OPTIONS VARCHAR2(30)
SQL_EXEC_ID NUMBER
SQL_EXEC_START DATE
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
QC_INSTANCE_ID NUMBER
QC_SESSION_ID NUMBER
QC_SESSION_SERIAL# NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
SESSION_STATE VARCHAR2(7)
TIME_WAITED NUMBER
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_SESSION NUMBER
BLOCKING_SESSION_SERIAL# NUMBER
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
CURRENT_ROW# NUMBER
CONSUMER_GROUP_ID NUMBER
XID RAW(8)
REMOTE_INSTANCE# NUMBER
IN_CONNECTION_MGMT VARCHAR2(1)
IN_PARSE VARCHAR2(1)
IN_HARD_PARSE VARCHAR2(1)
IN_SQL_EXECUTION VARCHAR2(1)
IN_PLSQL_EXECUTION VARCHAR2(1)
IN_PLSQL_RPC VARCHAR2(1)
IN_PLSQL_COMPILATION VARCHAR2(1)
IN_JAVA_EXECUTION VARCHAR2(1)
IN_BIND VARCHAR2(1)
IN_CURSOR_CLOSE VARCHAR2(1)
SERVICE_HASH NUMBER
PROGRAM VARCHAR2(48)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)

1.3.生成ASH报表

V$ACTIVE_SESSION_HISTORY是生成ASH报表的来源，可以通过OEM来生成report,也可以通过Oracle新提供的一个脚本来完成这个工作，这个脚本是:$ORACLE_HOME/rdbms/admin/ashrpt.sql

1.4.查询用户在最近1小时内等待了多长时间

Sql代码

SELECT s.sid,
s.username,
SUM(h.wait_time + h.time_waited) “total wait time”
FROM v$active_session_history h, v$session s, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
AND h.session_id = s.sid
group by s.sid, s.username;

SELECT s.sid,
       s.username,
       SUM(h.wait_time + h.time_waited) "total wait time"
  FROM v$active_session_history h, v$session s, v$event_name e
 WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
   AND h.session_id = s.sid
 group by s.sid, s.username;

1.5.查询用户在最近1小时内执行SQL等待了多长时间

Sql代码

SELECT h.user_id,
u.username,
sql.sql_text,
SUM(h.wait_time + h.time_waited) “total wait time”
FROM v$active_session_history h,
v$sqlarea sql,
dba_users u,
v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
AND h.sql_id = sql.sql_id
AND h.user_id = u.user_id
group by h.user_id, u.username, sql.sql_text

SELECT h.user_id,
       u.username,
       sql.sql_text,
       SUM(h.wait_time + h.time_waited) "total wait time"
  FROM v$active_session_history h,
       v$sqlarea                sql,
       dba_users                u,
       v$event_name             e
 WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
   AND h.sql_id = sql.sql_id
   AND h.user_id = u.user_id
 group by h.user_id, u.username, sql.sql_text

1.6.查询在最近1小时内引起最多等待时间的数据库对象

Sql代码

SELECT o.owner,
o.object_name,
o.object_type,
SUM(h.wait_time + h.time_waited) “total wait time”
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
group by o.owner, o.object_name, o.object_type

SELECT o.owner,
       o.object_name,
       o.object_type,
       SUM(h.wait_time + h.time_waited) "total wait time"
  FROM v$active_session_history h, dba_objects o, v$event_name e
 WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
   AND h.current_obj# = o.object_id
   AND e.event_id = h.event_id
 group by o.owner, o.object_name, o.object_type

2.Oracle Session Tracing

2.1.建立用户登陆触发器设置客户端标识符

Sql代码

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’) || ‘:’ ||
SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’)
INTO v_user_identifier
FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
  v_user_identifier varchar2(64);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'OS_USER') || ':' ||
         SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    INTO v_user_identifier
    FROM dual;
  DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/

2.2.查询会话

Sql代码

SELECT SID, CLIENT_IDENTIFIER, SERVICE_NAME, ACTION, MODULE FROM V$SESSION;
SID CLIENT_IDENTIFIER SERVICE_NAME ACTION MODULE
— ————————— ———— —————- ——————–
150 Administrator:172.18.17.181 orcl PlSqlDev.exe
143 Administrator:172.18.17.181 orcl SQL Window - New PL/SQL Developer
142 Administrator:172.18.17.181 orcl Main session PL/SQL Developer

SELECT SID, CLIENT_IDENTIFIER, SERVICE_NAME, ACTION, MODULE FROM V$SESSION;

SID CLIENT_IDENTIFIER        SERVICE_NAME ACTION          MODULE
--- ---------------------------    ------------ ---------------- --------------------
150 Administrator:172.18.17.181    orcl                      PlSqlDev.exe
143 Administrator:172.18.17.181    orcl         SQL Window - New PL/SQL Developer
142 Administrator:172.18.17.181    orcl         Main session     PL/SQL Developer

2.3.事件的等待时间

Sql代码

select session_id,
client_id,
event,
sum(wait_time + time_waited) ttl_wait_time
from v$active_session_history active_session_history
where sample_time between sysdate - 60 / 2880 and sysdate
group by session_id, client_id, event
order by 2;
SESSION_ID CLIENT_ID EVENT TTL_WAIT_TIME
———- ————————— ———————– ————-
150 Administrator:172.18.17.181 36493
150 Administrator:172.18.17.181 db file sequential read 8632
142 Administrator:172.18.17.181 7372

select session_id,
       client_id,
       event,
       sum(wait_time + time_waited) ttl_wait_time
  from v$active_session_history active_session_history
 where sample_time between sysdate - 60 / 2880 and sysdate
 group by session_id, client_id, event
 order by 2;

SESSION_ID CLIENT_ID                    EVENT                    TTL_WAIT_TIME
---------- --------------------------- ----------------------- -------------
150       Administrator:172.18.17.181                       36493
150       Administrator:172.18.17.181 db file sequential read 8632
142       Administrator:172.18.17.181                       7372

2.4.总的等待时间

Sql代码

select client_id, event, sum(wait_time + time_waited) ttl_wait_time
from v$active_session_history active_session_history
where sample_time between sysdate - 60 / 2880 and sysdate
group by client_id, event
order by 1;
CLIENT_ID EVENT TTL_WAIT_TIME
————————— ———————– ————-
Administrator:172.18.17.181 db file sequential read 8632
Administrator:172.18.17.181 3865

select client_id, event, sum(wait_time + time_waited) ttl_wait_time
  from v$active_session_history active_session_history
 where sample_time between sysdate - 60 / 2880 and sysdate
 group by client_id, event
 order by 1;

CLIENT_ID                EVENT                TTL_WAIT_TIME
--------------------------- ----------------------- -------------
Administrator:172.18.17.181 db file sequential read 8632
Administrator:172.18.17.181                    3865

2.5.查看客户端标识符

Sql代码

SQL> SELECT SYS_CONTEXT(‘USERENV’, ‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER
2 FROM DUAL;
CLIENT_IDENTIFIER
—————————–
Administrator:172.18.17.181

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER
  2    FROM DUAL;

CLIENT_IDENTIFIER
-----------------------------
Administrator:172.18.17.181

2.6.清除客户端标识符

Sql代码

DBMS_SESSION.CLEAR_IDENTIFIER

DBMS_SESSION.CLEAR_IDENTIFIER

Categories: oracle, tuning Tags: ash, tunning

Oracle中密码设置管理（使用自带脚本utlpwdmg.sql）

February 23rd, 2009 happydba No comments

使用脚本utlpwdmg.sql可以方便地启动数据库的密码管理。该脚本位于$ORACLE_HOME/rdbms/admin目录下。

启动密码管理以前：

SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

SQL> select * from dba_profiles where resource_type=’PASSWORD’;
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

启用密码管理：

SQL> @?/rdbms/admin/utlpwdmg.sql

Function created

Profile altered

启动后：

SQL> select * from dba_profiles where resource_type=’PASSWORD’;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 3
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 90
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD 1800
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD .0006
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 10

如果新设定的密码不符合复杂性检验规则，就会报错。例如：

SQL> alter user lh identified by lh;

alter user lh identified by lh

ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user

取消密码管理：

SQL>alter profile DEFAULT limit <password_parameter> unlimited;
如：
SQL>alter profile DEFAULT limit password_reuse_time unlimited;

停止密码检验函数：

SQL>alter profile DEFAULT limit password_verify_function null;

熟悉utlpwdmg.sql脚本有助我们更深入了解oracle密码检查机制。我们可以修改或自定义密码检验函数。

附utlpwdmg.sql脚本内容（红色自己是我修改的）：

Rem
Rem $Header: utlpwdmg.sql 31-aug-2000.11:00:47 nireland Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) Oracle Corporation 1996, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      utlpwdmg.sql – script for Default Password Resource Limits
Rem
Rem    DESCRIPTION
Rem      This is a script for enabling the password management features
Rem      by setting the default password resource limits.
Rem
Rem    NOTES
Rem      This file contains a function for minimum checking of password
Rem      complexity. This is more of a sample function that the customer
Rem      can use to develop the function for actual complexity checks that the
Rem      customer wants to make on the new password.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nireland    08/31/00 – Improve check for username=password. #1390553
Rem    nireland    06/28/00 – Fix null old password test. #1341892
Rem    asurpur     04/17/97 – Fix for bug479763
Rem    asurpur     12/12/96 – Changing the name of password_verify_function
Rem    asurpur     05/30/96 – New script for default password management
Rem    asurpur     05/30/96 – Created
Rem

– This script sets the default password resource parameters
– This script needs to be run to enable the password features.
– However the default resource parameters can be changed based
– on the need.
– A default password complexity function is also provided.
– This function makes the minimum complexity checks like
– the minimum length of the password, password not same as the
– username, etc. The user may enhance this function according to
– the need.
– This function must be created in SYS schema.
– connect sys/<password> as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);

BEGIN
   digitarray:= ’0123456789′;
   chararray:= ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
   punctarray:=’!”#$%&()“*+,-/:;<=>?_’;

   — Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, ‘Password same as or similar to user’);
   END IF;

   — Check for the minimum length of the password
   — wangnc modified,2008-9-25 15:11:30,change 4 to 16.
   IF length(password) < 16 THEN
      raise_application_error(-20002, ‘Password length less than 16‘);
   END IF;

   — Check if the password is too simple. A dictionary of words may be
   — maintained and a check may be made so as not to allow the words
   — that are too simple for the password.
   – wangnc modified,2008-9-25 15:12:17,add ‘dba’, ‘manager’, ‘tiger’
   IF NLS_LOWER(password) IN (‘welcome’, ‘database’, ‘account’, ‘user’, ‘password’, ‘oracle’, ‘computer’, ‘abcd’,
     ‘dba’, ‘manager’, ‘tiger’) THEN
      raise_application_error(-20002, ‘Password too simple’);
   END IF;

   — Check if the password contains at least one letter, one digit and one
   — punctuation mark.
   — 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, ‘Password should contain at least one digit, one character and one punctuation’);
   END IF;
   — 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, ‘Password should contain at least one \
              digit, one character and one punctuation’);
   END IF;
   — 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, ‘Password should contain at least one \
              digit, one character and one punctuation’);
   END IF;

   <<endsearch>>
   — Check if the password differs from the previous password by at least
   — 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) – length(password);

     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20004, ‘Password should differ by at \
         least 3 characters’);
       END IF;
     END IF;
   END IF;
   — Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

– This script alters the default parameters for Password Management
– This means that all the users on the system have Password Management
– enabled and set to the following values unless another profile is
– created with parameter values set to different value or UNLIMITED
– is created and assigned to the user.

/*
PASSWORD_LIFE_TIME 90 –用于指定口令有效期
PASSWORD_GRACE_TIME 10 –用于指定口令宽限期(为了强制用户定期改变口令,以上二者必须同时设置.)
PASSWORD_REUSE_TIME 1800 –用于指定口令可重用时间.
PASSWORD_REUSE_MAX UNLIMITED –用于指定在重用口令之前口令需要改变的次数.(需要主要,使用口令历史选项时,只能使用以上两种其中的一个选项.并将另一个选项设置为UNLIMITED.)
FAILED_LOGIN_ATTEMPTS 3 –用于指定连续登陆的最大失败次数.
PASSWORD_LOCK_TIME 1/1440 –用于指定帐户被锁定的天数.
PASSWORD_VERIFY_FUNCTION verify_function; –如果要禁用口令校验函数,可以将PASSWORD_VERIFY_FUNCTION选项设置为NULL.
*/

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

–End–

Categories: 安全, oracle Tags: 密码

oracle 使用存储过程分割字符串为数组

February 23rd, 2009 happydba No comments

type tt_type is table of varchar2(32) INDEX BY BINARY_INTEGER;

–使用”,”将分割字符串转换为数组，返回为数组个数，

function toArray(Liststr in varchar2, V1 out tt_type) return integer is

–FieldNames转化为数组
    TmpStr varchar2(32);
    Str    varchar2(4000);
    j      integer;
begin
    Str := Liststr;
    j   := 0;
    IF Instr(Liststr, ‘,’, 1, 1) = 0 THEN
      V1(j) := Liststr;
      j := j + 1;
    else
      While Instr(str, ‘,’, 1, 1) > 0 Loop
        TmpStr := Substr(str, 1, Instr(str, ‘,’, 1, 1) – 1);

        V1(j) := TmpStr;
        str := SubStr(Str, Instr(str, ‘,’, 1, 1) + 1, length(str));
        j := j + 1;
      end loop;
      if not str is null then
      –将最后一个保存
        V1(j) := str;
        j := j + 1;
      end if;
    end if;
    return J;
end;

Categories: oracle, PL/SQL Tags:

sqlplus限制命令使用的方法

February 23rd, 2009 happydba No comments

Oracle中sqlplus命令限制（使用自带脚本pupbld.sql）

有时出于安全考虑需要禁止一些业务系统的数据库用户执行sqlplus命令，方法很简单：在运行命令之前，将这些命令限制到一个由 SQL*Plus 引用的“特殊位置”。此特殊位置是SYSTEM 模式中一个名为PRODUCT_USER_PROFILE 的表。如果该表不存在，则您在每次启动 SQL*Plus 时将获得一个类似“Product User Profile Not Loaded”这样的警告。

为了创建这个表，需要运行pupbld.sql脚本。通常，这个脚本在$ORACLE_HOME/sqlplus/admin 路径中运行，具体的位置由系统决定。记住用system用户执行：

SQL> conn system/passwd

SQL> @?/sqlplus/admin/pupbld.sql

pupbld.sql脚本内容如下（红色的内容是我添加的，表示禁止WENDING结尾的数据库用户操作列出的那些sqlplus命令，可以根据实际情况增删改）：

–
– Copyright (c) Oracle Corporation 1988, 2003. All Rights Reserved.
–
– NAME
–   pupbld.sql
–
– DESCRIPTION
–   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables. These
–   tables allow SQL*Plus to disable commands per user. The tables
–   are used only by SQL*Plus and do not affect other client tools
–   that access the database. Refer to the SQL*Plus manual for table
–   usage information.
–
–   This script should be run on every database that SQL*Plus connects
–   to, even if the tables are not used to restrict commands.

– USAGE
– sqlplus system/<system_password> @pupbld
–
– Connect as SYSTEM before running this script
– If PRODUCT_USER_PROFILE exists, use its values and drop it

DROP SYNONYM PRODUCT_USER_PROFILE;

CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS
SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,
DATE_VALUE FROM PRODUCT_USER_PROFILE;

DROP TABLE PRODUCT_USER_PROFILE;
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG);

– Create SQLPLUS_PRODUCT_PROFILE from scratch

CREATE TABLE SQLPLUS_PRODUCT_PROFILE
(
PRODUCT        VARCHAR2 (30) NOT NULL,
USERID         VARCHAR2 (30),
ATTRIBUTE      VARCHAR2 (240),
SCOPE          VARCHAR2 (240),
NUMERIC_VALUE DECIMAL (15,2),
CHAR_VALUE     VARCHAR2 (240),
DATE_VALUE     DATE,
LONG_VALUE     LONG
);

– Remove SQL*Plus V3 name for sqlplus_product_profile

DROP TABLE PRODUCT_PROFILE;

– Create the view PRODUCT_PRIVS and grant access to that

DROP VIEW PRODUCT_PRIVS;
CREATE VIEW PRODUCT_PRIVS AS
SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,
NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
FROM SQLPLUS_PRODUCT_PROFILE
WHERE USERID = ‘PUBLIC’ OR USER LIKE USERID;

GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;
DROP PUBLIC SYNONYM PRODUCT_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
DROP SYNONYM PRODUCT_USER_PROFILE;
CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE;
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

– End of pupbld.sql

– wangnc added, 2008-9-25 16:47:11, insert data
– 所有业务系统用户禁止执行sqlplus一些命令

delete PRODUCT_USER_PROFILE where product=’SQL*Plus’ and userid=’%WENDING’;

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'HOST’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'ALTER’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'AUDIT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'ANALYZE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'CREATE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'DELETE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'DROP’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'LOCK’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'NOAUDIT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'RENAME’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'SELECT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'UPDATE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'VALIDATE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'TRUNCATE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'GRANT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'REVOKE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'SET ROLE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'SET TRANSACTION’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'DECLARE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'BEGIN’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'EXECUTE’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'COPY’,'DISABLED’);

–insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
–values (‘SQL*Plus’,'%WENDING’,'SET’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'EDIT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'PASSWORD’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'SPOOL’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'START’,'DISABLED’);

–insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
–values (‘SQL*Plus’,'%WENDING’,'QUIT’,'DISABLED’);

–insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
–values (‘SQL*Plus’,'%WENDING’,'EXIT’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'RUN’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'GET’,'DISABLED’);

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values (‘SQL*Plus’,'%WENDING’,'SAVE’,'DISABLED’);

commit;

注意：

禁用SQL*Plus SET指令的同时也会禁用SQL SET ROLE和SET TRANSACTION命令。
禁用SQL*Plus START的同时也会禁用SQL*Plus @和@@命令。
禁用SQL*Plus HOST的同时也会禁用等同命令（如VMS上的$以及UNIX上的!）。

–End–

Categories: 未分类 Tags:

ORACLE SQL PLUS 使用技巧

February 23rd, 2009 happydba No comments

一. ORACLE SQL PLUS 使用技巧:

—- ①查找重复记录:

SELECT DRAWING,DSNO FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
—- 执行上述SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录。
—- 删除重复记录:

DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
—- 执行上述SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录。
—- ② 快速编译所有视图

—- 当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。

SQL >SPOOL ON.SQL
SQL >SELECT ‘ALTER VIEW ‘||TNAME||’
COMPILE;’ FROM TAB;
SQL >SPOOL OFF
然后执行ON.SQL即可。
SQL >@ON.SQL
当然，授权和创建同义词也可以快速进行，如：
SQL >SELECT ‘GRANT SELECT ON ’
||TNAME||’ TO USERNAME;’ FROM TAB;
SQL >SELECT ‘CREATE SYNONYM
‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB;

③ 用外联接提高表连接的查询速度
在作表连接(常用于视图)时，常使用以下方法来查询数据:
SELECT PAY_NO, PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE >=120000);
—- 但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOT IN要进行一条一条的比较，共需要10000*10000次比较后，才能得到结果。该用外联接后，可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE >=12000;
—- ④ 怎样读写文本型操作系统文件
—- 在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下：

DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN(
‘C:\’,’TEST.TXT’,’A’);
UTL_FILE.PUT_LINE(FILE_HANDLE,’
HELLO,IT’S A TEST TXT FILE’);
UTL_FILE.FCLOSE(FILE_HANDLE);
END;
—- 相关UTL_FILE数据库包详细信息可以参见相关资料。

[/url]续一

—- ⑤ 怎样在数据库触发器中使用列的新值与旧值

—- 在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用LD就可以了，使用某列修改后的新值，用:NEW就可以了。如LD.DEPT_NO,:NEW.DEPT_NO。

—- 二.ORACLE DEVELOPER 2000使用技巧：

—- ① 改变FORM(FMX模块)运行时的Runform4.5的题头:

—- DEVELOPER2000中FMX默认题头为：Developer/2000 Forms Runtime for Windows 95 / NT 你可以改为自己定义的标题,

—- 1. 在Form级触发器中添加触发WHEN-NEW-FORM-INSTANCE

—- 2. 在此触发器中写如下代码：

set_window_property(FORMS_MDI_WINDOW,TITLE,’POINT
SYSTEM 欢迎使用’);
—- ② 如何隐藏菜单中的window选项:
—- 在创建自己的菜单时，最后选项总有window项，下面介绍如何去掉它,

—- 1. 创建一个Menu

—- 2. 在Menu中建立一个Item,命名为WINDOW。

—- 3. 设置该Item属性如下:

—-

Menu Item Type:Magic
Command Type:Null
Magic Item:Window
Lable:为空

—- ③ 怎样创建动态下拉列表List
—- Developer 2000 中的列表是通过设置相关属性而完成数据项的列表设置的，但那只是静态的，有时你想让某项成为动态的列表，随输入数据的改变而改变，就需要动手去编个小程序。下面详细介绍怎样去做:

—- 有块EBOP_CABLE_ACCOUNT，下有SPECIFICATION数据项，当一进入该模块时，就将SPECIFICATION项在数据库中存储的值动态显示出来，先在Form4.5中建立一个PRCEDURE，命名为DYN_LIST:

PROCEDURE DYN_LIST IS
        CURSOR C1 IS
SELECT DISTINCT(SPECIFICATION)
FROM EBOP_CABLE_ACCOUNT;
        CNT    NUMBER;
    i      NUMBER;
        TNAME EBOP_CABLE_
ACCOUNT.SPECIFICATION%TYPE;
BEGIN
        CLEAR_LIST(‘EBOP_CABLE_
ACCOUNT.SPECIFICATION’);
        SELECT COUNT(DISTINCT
(SPECIFICATION)) INTO CNT FROM EBOP
_CABLE_ACCOUNT;
        open C1;
        FOR i IN 1..CNT LOOP
        FETCH C1 INTO TNAME;
        EXIT WHEN C1%NOTFOUND
OR C1%NOTFOUND IS NULL;
            ADD_LIST_ELEMENT
(‘EBOP_CABLE_ACCOUNT
.SPECIFICATION’,i,TNAME,TNAME);
        END LOOP;
        DELETE_LIST_ELEMENT
(‘EBOP_CABLE_ACCOUNT.SPECIFICATION’,CNT+1);
        CLOSE C1;
END;
然后在FORM的WHEN-NEW
-FORM-INSTANCE触发子中加入一行：
DYN_LIST;
—- 这样一进入该FMX，就会动态刷新该列表。除此之外，SPECIFICATION数据项改为列表项。
—- ④ 当显示多条记录且数据项特别多时，如何组织录入及显示界面:

—- 如上图所示，PRN代码及设备代码在画布1(CONTENT型)上，其它数据项在画布2(STACK型)上，所有数据项为一个表的列或一个块的数据项。在拉动水平滚动条时或用TAB或敲回车键时，将看到全部数据项。这种排布方法适用于数据项特别多又想显示多条记录时用。主要制作顺序为：先建立两个画布，画布1(CONTENT型)，画布2(STACK型)，然后建立块，选画布时用画布1，这样所有项都显示在画布1上，然后选中除PRN代码及设备代码之外的所有数据项，选TOOLS菜单下的PROPERTIES选项，将这些数据项的CANVAS属性选为画布2(STACK型)，然后调整整体位置就可以了。

—- ⑤ 如何在FORM的受限触发子中提交保存数据

—- 在FORM中很多触发子是不能用COMMIT WORK语句的，当你在该触发子中使用了UPDATE，DELETE等操作并想立即存盘时，就需要COMMIT WORK语句了。首先在服务器端建立DB_SQL_COMMIT这个过程(采用ORACLE7.3数据库)，

PROCEDURE DB_SQL_COMMIT IS
source_cursor integer;
ignore integer;
V7 NUMBER :=2;
BEGIN
source_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,’COMMIT WORK’,V7);
ignore:=dbms_sql.execute(source_cursor);
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END;
—- 然后在FORM中该触发子中调用过程DB_SQL_COMMIT;就可以了，当然你可以根据自己需要将该过程加入参数，这样通过参数可以得到执行DML语句的权限。

[url=http://www.itpub.net/misc.php?action=viewratings&tid=4353&pid=21206]续二

- ⑥ 如何在FORM中实现某数据项自动按记录序号加一操作

—- 设块名为VO,要操作的数据项为VO_ID,在该块中建立块级触发子WHEN-CREATE- RECORD，加入如下代码：

:VO_ID:=:System.Trigger_Record;
—- 这样每当生成新记录时VO_ID就会自动加一了。
—- ⑦ 如何在一个FORM中调用另一个FORM，或在一个块中调用另一个块时显示特定的记录有时用户会要求在调用另一个FORM时，只显示相关的记录，举例如下，在一个FORM的块中有一个按钮，在按钮触发子中加入如下代码：

DECLARE
      PM
PARAMLIST;
BEGIN
        PM:=GET_PARAMETER_LIST(‘PM’);
        IF NOT ID_NULL(PM) THEN
          DESTROY_PARAMETER_LIST(‘PM’);
    END IF;
    PM:=CREATE_PARAMETER_LIST(‘PM’);
………………….
ADD_PARAMETER(PM,’THE_WHERE’,
TEXT_PARAMETER,’EM_NAME=”EM4”
AND EM_PROJECT_NAME=”支架预制”’);
OPEN_FORM(‘PAYMENT’,ACTIVATE,SESSION,PM);
END；
—- 其中EM_NAME，EM_PROJECT_NAME为本FORM某块的数据项，PAYMENT为要调用的FORM模块。这样通过传递参数列表就可以得到想要的结果。在FORM PAYMENT.FMB中，建立一参数THE_WHERE，CHAR型，长1000，然后在PAYMENT.FMB中建立FORM级触发子WHEN-NEW-FORM-INSTANCE，在该触发子中加入以下语句：
IF ARAMETER.THE_WHERE IS NOT NULL THEN
SET_BLOCK_PROPERTY(‘PAYMENT’,
DEFAULT_WHERE,ARAMETER.THE_WHERE);
END IF;
—- 其中PAYMENT为要显示的块，这样通过参数传递就得到想要的某些特定条件的数据了。
—- ⑧ 在FORM中当有主从块时，连续输入记录如何避免被不断的提示保存：

—- 每输入一条主记录和若干条该主记录的从记录后，此时再导航到主块输下一条记录，FORM就会提示你是否要保存记录，而你并不希望FORM提示，让它自动保存，此时你可以到Program Units中找到过程PROCEDURE Clear_All_Master_Details，然后在这个过程中找到语句

Clear_Block(ASK_COMMIT);
—- 将其改为Clear_Block(DO_COMMIT);就可以了。
—- ⑨ 在Report开始时选择排序项:

—- 在报表开始的Parameter Form中选择报表按哪个数据项排序，

—- 1. 先在USER PARAMETER 中创建SORT参数，为字符型，长20。

—- 2. 初始值选’责任方’,然后将这四个值输入到DATA SELECTION中，形成列表。

—- 3. 然后处理QUERY中的SQL语句:

select CHARGER,FCO_NO,EM_NAME,FCO
_NO,DESCRIPTION, FCR_POINT
from FCR_MAIN
ORDER BY DECODE(:SORT,’责任方’,CHARGER,’FCO号’,
FCO_NO,’FCR号’,FCR_NO,’FCR号’,EM_NAME)

续三

—- ⑩ 在Developer 2000中如何读写操作系统文件
—- 在用Developer 2000的开发工具开发应用程序时，经常碰到需要读写外部文件的问题，可以用ORACLE 带的包TEXT_IO来完成这项需求。例如:

DECLARE
        IN_FILE                TEXT_IO.FILE_TYPE;
        OUT_FILE                TEXT_IO.FILE_TYPE;
        LINE_BUFER        VARCHAR2(80);
/*若不用IN_FILE,可以将各字段联接在一起赋值给此变量*/
BEGIN
        IN_FILE:=TEXT_IO.FOPEN
(‘C:\TEMP\TEST1.TXT’,’r’);
        OUT_FILE:=TEXT_IO.FOPEN

(‘C:\TEMP\TEST2.TXT’,’w+’);
    LOOP
        TEXT_IO.GET_LINE(IN_FILE,LINE_BUFER);
        TEXT_IO.PUT(LINE_BUFER);
        TEXT_IO.NEW_LINE;
        TEXT_IO.PUT_LINE(OUT_FILE,LINE_BUFER);
    END LOOP;
    EXCEPTION
        WHEN no_data_found THEN
TEXT_IO.PUT_LINE(‘CLOSING THE FILE ,PLEASE WAITING….’);
TEXT_IO.FCLOSE(IN_FILE);
TEXT_IO.FCLOSE(OUT_FILE);
END;
—- 三.数据库管理

—- ① 在删除一个表中的全部数据时，须使用TRUNCATE TABLE 表名;因为用DROP TABLE，DELETE * FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。

Categories: oracle, sqlplus Tags: sqlplus

CentOS 5连接HP EVA8000实现multipath

February 23rd, 2009 happydba No comments

硬件环境：Dell PowerEdge 2850，安装一块HP的HBA卡，Qlogic芯片，具体型号待查。HBA卡连接到光纤交换机，HP EVA 8000通过四根光纤与光纤交换机相连。
操作系统：CentOS 5.2
首先在HP的网络上通过HTTP或FTP下载驱动程序。相应文档也可以从HP网站上下载。
根据文档中的说明，解压缩下载的程序，并执行./INSTALL -f安装，执行时报如下错误：
Cannot compile QLogic driver for this Linux version
Driver build unsuccessful
看起来安装程序不能正确识别操作系统，修改/etc/redhat-release为如下内容：
Red Hat Enterprise Linux Server release 5.2 (Tikanga)
重新执行安装程序，成功：
[root@setup hp_qla2x00-2007-10-05]# ./INSTALL -f
Writing new /etc/hp_qla2x00.conf…done
Installing hp_qla2x00src RPM…

Preparing… ########################################### [100%]
kernel-devel package is installed
1:hp_qla2x00src ########################################### [100%]
/etc/hp_qla2x00.conf already exists. Using parameters in the current file.
Copying /opt/hp/src/hp_qla2x00src/libqlsdm-ia32.so to /usr/lib/libqlsdm.so
Adding line for libqlsdm.so to /etc/hba.conf
Kernel modules directory: /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/drivers/scsi/qla2xxx
Kernel build directory: /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/build

Building FC driver modules…

make clean
make: Entering directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′
make: Leaving directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′

make -j5 -C /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/build M=/opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25 modules
make: Entering directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2100.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2100_fw.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2200.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2200_fw.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2300.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2300_fw.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2322.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2322_fw.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2400.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/ql2400_fw.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_os.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_init.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_mbx.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_iocb.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_isr.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_gs.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_dbg.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_sup.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_rscn.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_attr.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_xioct.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_inioct.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_fo.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_foln.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_cfg.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_cfgln.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_32ioctl.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla_ip.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx_conf.o
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xip.o
HOSTCC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras/qla_nvr
HOSTCC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras/qla_opts
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2100.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2200.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2300.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2322.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2400.o
Building modules, stage 2.
MODPOST
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2100.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2200.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2300.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2322.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2400.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xip.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx.mod.o
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx_conf.mod.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2100.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2200.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2300.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2322.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2400.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xip.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx.ko
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/qla2xxx_conf.ko
make: Leaving directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′

Copying makefile for intermodule.ko

Building intermodule.ko…

make -C /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/build M=/opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras clean
make: Entering directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′
make: Leaving directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′
-j5 -C /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/build M=/opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras modules
make: Entering directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′
CC [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras/intermodule.o
Building modules, stage 2.
MODPOST
CC /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras/intermodule.mod.o
LD [M] /opt/hp/src/hp_qla2x00src/qla2xxx-8.01.07.25/extras/intermodule.ko
make: Leaving directory `/usr/src/kernels/2.6.18-92.1.6.el5.centos.plus-PAE-i686′

Creating /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/kernel
Copying intermodule.ko to /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/kernel
Copying qla2xxx.ko to /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/drivers/scsi/qla2xxx
Copying qla2300.ko to /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/drivers/scsi/qla2xxx
Copying qla2400.ko to /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/drivers/scsi/qla2xxx
Copying qla2xxx_conf.ko to /lib/modules/2.6.18-92.1.6.el5.centos.plusPAE/kernel/drivers/scsi/qla2xxx
Running depmod -a

adding line to /etc/modprobe.conf: alias scsi_hostadapter2 qla2xxx_conf
adding line to /etc/modprobe.conf: alias scsi_hostadapter3 qla2xxx
adding line to /etc/modprobe.conf: alias scsi_hostadapter4 qla2300
adding line to /etc/modprobe.conf: alias scsi_hostadapter5 qla2400
adding line to /etc/modprobe.conf: options qla2xxx ql2xmaxqdepth=16 qlport_down_retry=30 ql2xloginretrycount=30 ql2xfailover=1 ql2xlbType=1 ql2xautorestore=0xa ConfigRequired=0
Adding “remove qla2xxx” line to /etc/modprobe.conf

Creating new initrd – initrd-2.6.18-92.1.6.el5.centos.plusPAE.img
Creating initial kernel inventory for smart_compile

Making symbolic link from /opt/hp/src/hp_qla2x00src/master.sh to /usr/sbin/hp_compile_qldriver
qla2x00 driver source can be found in /opt/hp/src/hp_qla2x00src

Installing fibreutils…

Preparing… ########################################### [100%]
1:fibreutils ########################################### [100%]

重新启动，或重新加载驱动模块：
# /opt/hp/src/hp_qla2x00src/unload.sh
# modprobe qla2xxx_conf
# modprobe qla2xxx
# modprobe qla2300
# modprobe qla2400
这时候执行fdisk -l，发现已经正常识别磁盘。

设置自动编译选项，使得在升级内核时自动编译相应模块：
[root@setup hp_qla2x00src]# ./set_parm -a
Auto-compile is now set to “yes”
Writing new /etc/hp_qla2x00.conf…done
adding line to /etc/modprobe.conf: alias scsi_hostadapter2 qla2xxx_conf
adding line to /etc/modprobe.conf: alias scsi_hostadapter3 qla2xxx
adding line to /etc/modprobe.conf: alias scsi_hostadapter4 qla2300
adding line to /etc/modprobe.conf: alias scsi_hostadapter5 qla2400
adding line to /etc/modprobe.conf: options qla2xxx ql2xmaxqdepth=16 qlport_down_retry=30 ql2xloginretrycount=30 ql2xfailover=1 ql2xlbType=1 ql2xautorestore=0xa ConfigRequired=0
Adding “remove qla2xxx” line to /etc/modprobe.conf
关于set_parm的用法，可以使用./set_parm -h命令查看。

参考文献：http://h50146.www5.hp.com/products/software/oe/linux/mainstream/support/doc/option/fibre.html。是日文的，但是只要看到几个关键链接就足够了。

Categories: 存储 Tags: 多路径, eva, hp

exec FLUSH DATABASE MONITORING INFO;

February 21st, 2009 happydba No comments

使用命令：
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur.
Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate these views with the latest information.

每15分钟,smon会刷新统计结果到数据字典中,oracle使用这些表的数据去判断表的统计数据是否过期,
如果当表的数据改变超过10%,oracle就认为该表的统计数据已经过期.
Oracle 10g默认表是 monitoring,即使表在建立的时候指定是nomonitoring也是会被忽忽略的
这是由参数：statistics_level决定的，默认值=TYPICAL
如果设置为BASIC将disable monitoring

系统将自动收集表的统计信息(由GATHER_STATS_JOB完成，
可以通过 EXEC DBMS_SCHEDULER.DISABLE(‘GATHER_SCHEDULER_JOB’)来取消系统自动收集)，
且每3个小时由SMON写入到数据字典（USER_TABLES.NUM_ROWS）里。

经过更深入的研究,发现8i之前,数据库是每3小时自动将SGA的信息FLUSH到DBA_TAB_MODIFICATIONS中,而9i将这个时间改成每15分钟.
在10.2.0.3中测试发现,这个视图并不会自动刷新,且通过exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO手动刷新时也存在一个bug,即timestamp 栏位的内容不会改变.在10.2.0.4中已修复了此bug.

Categories: dbms_stats, oracle Tags:

Gather Statistics with DBMS_STATS by Jeff

February 20th, 2009 happydba 1 comment

Contents

Overview
Missing statistics
Analyze vs. DBMS_STATS
What gets collected?
Where are the statistics stored?
Compute statistics vs. Estimate statistics
DBMS_STATS functions and variable definitions
DBMS_STATS in action (Examples)
Automated table monitoring and stale statistics gathering example
How to determine if dictionary statistics are RDBMS-generated or user-defined

Overview

Oracle’s cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statement’s predicate chooses) of predicates and to estimate the “cost” of each execution plan. The COB will use the selectivity of a predicate to estimate the cost of a particular access method and to determin the optimal join order. Statistics are used to quantify the data distribution and storage characteristics of tables, columns, indexes and partitions. The COB uses these statistics to estimate how much I/O and memory are required to execute a SQL statement using a particular execution plan. Statistics are stored in the data dictionary, and they can be exported from one database and imported into another. Situations in where you would want to perform this, might be to transfer production statistics to a test system to simulate the real environment, even though the test system may only have small samples of the data.

In order to give the Oracle cost-based optimizer the most up-to-date information about schema objects (and the best chance for choosing a good execution plan) all application tables and indexes to be accessed must be analyzed. New statistics should be gathered on schema objects that are out of date. After loading or deleting large amounts of data would obviously change the number of rows. Other changes like updating a large amount of rows would not effect the number of rows, but may effect the average row length.

Statistics can be generated with the ANALYZE statement or with the package DBMS_STATS (introduced in Oracle8i). The DBMS_STATS package is great for DBA’s in managing database statistics only for use by the COB. The package itself allows the DBA to create, modify, view and delete statistics from a standard, well-defined set of package procedures. The statistics can be gathered on tables, indexes, columns, partitions and schemas, but note that it does not generate statistics for clusters.

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:

The dictionary.

A table created in the user’s schema for this purpose.

Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currently parsed SQL statements that access the object.

The next time such a statement executes, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics the next time Oracle parses them.

When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type if you analyze the column or domain index.

Missing statistics

When statistics do not exist on schema objects, the optimizer uses the following default values.

Tables

Statistic Default Value Used by Optimizer

Cardinality 100 rows

Avg. row len 20 bytes

No. of blocks 100

Remote cardinality 2000 rows

Remote average row length 100 bytes

Indexes

Statistic Default Value Used by Optimizer

Levels 1

Leaf blocks 25

Leaf blocks/key 1

Data blocks/key 1

Distinct keys 100

Clustering factor 800 (8*no. of blocks)

Analyze vs. DBMS_STATS

The following is a quick overview of the two.

Analyze

The only method available for collecting statistics in Oracle 8.0 and lower.

ANALYZE can only run serially.

ANALYZE cannot overwrite or delete certain types of statistics that where generated by DBMS_STATS. (analyze不能改写或删除某些由dbms_stats收集的统计信息)

ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values. (analyze对分区表和分区索引手机全局的信息而不是直接收集某个分区的信息，这样就会操场某些信息的不准确性，如唯一值的数量)

For partitioned tables and indexes, ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics. (先收集独立分区的信息，然后计算分区的全局信息)

For composite partitioning, ANALYZE gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics. (混合分区先收集子分区信息，由子分区信息计算上级分区信息和全局信息)

ANALYZE can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information. (analyze能收集更多的信息,这些信息并不被优化器所使用，比如迁移行、table/index/cluster的结构完整性)

No easy way of knowing which tables or how much data within the tables have changed. The DBA would generally re-analyze all of their tables on a semi-regular basis.

DBMS_STATS

Only available for Oracle 8i and higher.

Statistics can be generated to a statistics table and can then be imported or exported between databases and re-loaded into the data dictionary at any time. This allows the DBA to experiment with various statistics.

DBMS_STATS routines have the option to run via parallel query or operate serially.

Can gather statistics for sub-partitions or partitions.

Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command. (某些DDL命令，如create index能自动生成统计信息，而不用执行完ddl之后再收集)

DBMS_STATS does not generate information about chained rows and the structural integrity of segments.

The DBA can set a particular table, a whole schema or the entire database to be automatically monitored when a modification occurs. When enabled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the DBA_TAB_MODIFICATIONS view. Oracle 9i introduced a new function in the DBMS_STATS package called: FLUSH_DATABASE_MONITORING_INFO. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.
．判断陈旧的统计数据

为了判断数据库对象是否需要新的统计数据，oracle提供一个表监控工具。当statistics_level=typical或all时，监控被开启。监控追踪表自从上次统计数据被收集以来，表被insert,update,delete的近似值，还有表是否被truncate。表的变化信息可以查看user_tab_modifications视图。随着数据修改，当oracle移植信息到视图的时候，可能会有几分钟的迟延。DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程来立即反映在内存中显著的监控信息。

当options参数被设置为gather stale或gather auto ，Gather_database_stats或gather_schema_stats过程使用旧的统计数据来收集新的统计数据。如果监控表的已经被修改了超过10%，那么这些统计数据被认为是旧的，需要再次被收集。

DBA_TAB_MODIFICATIONS:This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.

Use of table monitoring in conjunction with DBMS_STATS stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)

What gets collected?

Table Statistics

Oracle collects the following statistics for a table. Statistics marked with an asterisk are always computed exactly. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES in the columns shown in parentheses.

Number of rows (NUM_ROWS) 多少行

* Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 高水位下的block数量

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) 从未使用的空块数

Average available free space in each data block in bytes (AVG_SPACE) 平均每个块的可用空间

Number of chained rows. [Not collected by DBMS_STATS] (CHAIN_COUNT) 行迁移数

Average row length, including the row’s overhead, in bytes (AVG_ROW_LEN) 平均行长

 Index Statistics

Oracle collects the following statistics for an index. Statistics marked with an asterisk are always computed exactly. For conventional indexes, the statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns in parentheses.

* Depth of the index from its root block to its leaf blocks (BLEVEL) 根块到叶块的深度

Number of leaf blocks (LEAF_BLOCKS) 叶块的数量

Number of distinct index values (DISTINCT_KEYS) distinct值的数量

Average number of leaf blocks per index value (AVG_LEAF_BLOCKS_PER_KEY) 平均每个索引值的叶块数量

Average number of data blocks per index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) 平均每索引值的数据块数。

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR) 聚集因子

Where are the statistics stored?

Statistics are stored into the Oracle Data Dictionary, in tables owned by SYS. Views are created on these tables to retrieve data more easily. These views are prefixed with DBA_ or ALL_ or USER_. For ease of reading, we will use DBA_% views, but ALL_% views or USER_% views could be used as well.

Conventions Used

- Statistics available only since 8.0.X rdbms release         : (*)
- Statistics available only since 8.1.X rdbms release         : (**)
- Statistics not available at partition or subpartition level : (G)
- Statistics not available at subpartition level              : (GP)

Table level statistics can be retrieved from:

DBA_ALL_TABLES – (8.X onwards)

DBA_OBJECT_TABLES – (8.X onwards

DBA_TABLES – (all versions)

DBA_TAB_PARTITIONS – (8.X onwards)

DBA_TAB_SUBPARTITIONS – (8.1 onwards) Columns to look at are:

  NUM_ROWS                         : Number of rows (always exact even when computed
                                     with ESTIMATE method)
  BLOCKS                           : Number of blocks which have been used even
                                     if they are empty due to delete statements
  EMPTY_BLOCKS                     : Number of empty blocks (these blocks have
                                     never been used)
  AVG_SPACE                        : Average amount of FREE space in bytes in blocks
                                     allocated to the table : Blocks + Empty Blocks
  CHAIN_CNT                        : Number of chained or migrated rows
  AVG_ROW_LEN                      : Average length of rows in bytes
  AVG_SPACE_FREELIST_BLOCKS (*)(G) : Average free space of blocks in the freelist
  NUM_FREELIST_BLOCKS       (*)(G) : Number of blocks in the freelist
  SAMPLE_SIZE                      : Sample defined in ESTIMATE method (0 if COMPUTE)
  LAST_ANALYZED                    : Timestamp of last analysis
  GLOBAL_STATS             (**)    : For partitioned tables, YES means statistics
                                     are collected for the TABLE as a whole
                                     NO means statistics are estimated from statistics
                                     on underlying table partitions or subpartitions 
  USER_STATS               (**)    : YES if statistics entered directly by the user

Index level statistics can be retrieved from:

DBA_INDEXES – (all versions )

DBA_IND_PARTITIONS – (8.X onwards)

DBA_IND_SUBPARTITIONS – (8.1 onwards ) Columns to look at are:

  BLEVEL                       : B*Tree level : depth of the index from its root
                                 block to its leaf blocks
  LEAF_BLOCKS                  : Number of leaf blocks
  DISTINCT_KEYS                : Number of distinct keys
  AVG_LEAF_BLOCKS_PER_KEY      : Average number of leaf blocks in which each
                                 distinct key appears (1 for a UNIQUE index)
  AVG_DATA_BLOCKS_PER_KEY      : Average number of data blocks in the table that
                                 are pointed to by a distinct key
  CLUSTERING_FACTOR            : - if near the number of blocks, then the table is
                                   ordered : index entries in a single leaf block
                                   tend to point to rows in same data block
                                 - if near the number of rows, the table is
                                   randomly ordered : index entries in a single
                                   leaf block are unlikely to point to rows in
                                   same data block
  SAMPLE_SIZE                  : Sample defined in ESTIMATE method (0 if COMPUTE)
  LAST_ANALYZED                : Timestamp of last analysis
  GLOBAL_STATS            (**) : For partitioned indexes, YES means statistics
                                 are collected for the INDEX as a whole
                                 NO means statistics are estimated from statistics
                                 on underlying index partitions or subpartitions
  USER_STATS              (**) : YES if statistics entered directly by the user
  PCT_DIRECT_ACCESS   (**)(GP) : For secondary indexes on IOTs, percentage of
                                 rows with VALID guess

Column level statistics can be retrieved from:

DBA_TAB_COLUMNS – (all versions)

DBA_TAB_COL_STATISTICS – (Version 8.X onwards)

DBA_PART_COL_STATISTICS – (Version 8.X onwards)

DBA_SUBPART_COL_STATISTICS – (Version 8.1 onwards) The last three views extract statistics data from DBA_TAB_COLUMNS. Columns to look at are:

  NUM_DISTINCT                 : Number of distinct values
  LOW_VALUE                    : Lowest value
  LOW_VALUE                    : Highest value
  DENSITY                      : Density
  NUM_NULLS                    : Number of columns having a NULL value
  AVG_COL_LEN                  : Average length in bytes
  NUM_BUCKETS                  : Number of buckets in histogram for the column
  SAMPLE_SIZE                  : Sample defined in ESTIMATE method (0 if COMPUTE)
  LAST_ANALYZED                : Timestamp of last analysis
  (**)GLOBAL_STATS             : For partitioned tables, YES means statistics
                                 are collected for the TABLE as a whole
                                 NO means statistics are estimated from statistics
                                 on underlying table partitions or subpartitions
  (**)USER_STATS               : YES if statistics entered directly by the user

Compute statistics vs. Estimate statistics

Both computed and estimated statistics are used by the Oracle optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements. COMPUTE STATISTICS

COMPUTE STATISTICS instructs Oracle to compute exact statistics about the analyzed object and store them in the data dictionary. When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.

To perform an exact computation, Oracle requires enough space to perform a scan and sort of the table. If there is not enough space in memory, then temporary space may be required. For estimations, Oracle requires enough space to perform a scan and sort of only the rows in the requested sample of the table. For indexes, computation does not take up as much time or space, so it is best to perform a full computation.

Some statistics are always computed exactly, such as the number of data blocks currently containing data in a table or the depth of an index from its root block to its leaf blocks. 某些信息总被精确地统计，如表当前使用的block数、index的高度

Use estimation for tables and clusters rather than computation, unless you need exact values. Because estimation rarely sorts, it is often much faster than computation, especially for large tables.

ESTIMATE STATISTICS

ESTIMATE STATISTICS instructs Oracle to estimate statistics about the analyzed object and stores them in the data dictionary. When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.

To estimate statistics, Oracle selects a random sample of data. You can specify the sampling percentage and whether sampling should be based on rows or blocks.

estimate statistics会随机选择样本大小。你可以定义基于row数或block数样本的大小

Row sampling reads rows without regard to their physical placement on disk. This provides the most random data for estimates, but it can result in reading more data than necessary. For example, in the worst case a row sample might select one row from each block, requiring a full scan of the table or index. 行取样不考虑行的物理分布，这种方式提供了最大的随机数据来估算。但是可能造成比需要的多读了很多数据。

Block sampling reads a random sample of blocks and uses all of the rows in those blocks for estimates. This reduces the amount of I/O activity for a given sample size, but it can reduce the randomness of the sample if rows are not randomly distributed on disk. Block sampling is not available for index statistics. 索引统计不支持block取样方式。

Notes on estimating statistics

The default estimate of the analyze command reads the first approx 1064 rows of the table so the results often leave a lot to be desired. 默认大概取1064行进行估算

The general consensus is that the default value of 1064 is not sufficient for accurate statistics when dealing with tables of any size. Many claims have shown that estimating statistics on 30 percent produces very accurate results. I personally have been running estimate 35 percent. This seems to produce very accurate numbers. It also saves a lot of time over full scans. 1064可能太小，我个人总是选用35%

Note that if an estimate does 50% or more of a table Oracle converts the estimate to a full compute statistics. 如果取样>=50%，oracle就会做full compute

DBMS_STATS functions and variable definitions

Most of the DBMS_STATS procedures include the three parameters statown, stattab, and statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics. The stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown parameter is specified). Users may create multiple tables with different stattab identifiers to hold separate sets of statistics.

Additionally, users can maintain different sets of statistics within a single stattab by using the statid parameter, which can help avoid cluttering the user’s schema.

For all of the SET or GET procedures, if stattab is not provided (i.e., NULL), then the operation works directly on the dictionary statistics; therefore, users do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab is not NULL, then the SET or GET operation works on the specified user statistics table, and not the dictionary.

Create Stats Table
DBMS_STATS.CREATE_STAT_TABLE (
  ownname  VARCHAR2,
  stattab  VARCHAR2,
  tblspace VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

stattab : Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly.

tblspace : Tablespace in which to create the stat tables. If none is specified, then they are created in the user’s default tablespace.

Drop Stats Table
DBMS_STATS.drop_stat_table (
  ownname VARCHAR2,
  stattab VARCHAR2);
ownname : Name of the schema.

stattab : User stat table identifier.

Gather Schema Stats
DBMS_STATS.gather_schema_stats (
  ownname          VARCHAR2,
  estimate_percent NUMBER   DEFAULT NULL,
  block_sample     BOOLEAN  DEFAULT FALSE,
  method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
  degree           NUMBER   DEFAULT NULL,
  granularity      VARCHAR2 DEFAULT 'DEFAULT',
  cascade          BOOLEAN  DEFAULT FALSE,
  stattab          VARCHAR2 DEFAULT NULL,
  statid           VARCHAR2 DEFAULT NULL,
  options          VARCHAR2 DEFAULT 'GATHER',
  objlist     OUT  ObjectTab,
  statown          VARCHAR2 DEFAULT NULL);
ownname : Schema to analyze (NULL means current schema).

estimate_percent : Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100).

block_sample : Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt : Method options of the following format (the phrase 'SIZE 1' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer] SIZE :
specifies the maximum number of partitions (buckets) in the histogram.

Default value: 75

Range of values: 1 – 254

This value is passed to all of the individual tables.

degree : Degree of parallelism (NULL means use table default value).

granularity : Granularity of statistics to collect (only pertinent if the table is partitioned). 仅在分区表中生效

DEFAULT: Gather global- and partition-level statistics. 默认收集全局和分区信息

SUBPARTITION: Gather subpartition-level statistics. 收集子分区信息

PARTITION: Gather partition-level statistics. 收集分区信息

GLOBAL: Gather global statistics. 收集全局信息

ALL: Gather all (subpartition, partition, and global) statistics.

cascade : Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the gather_index_stats procedure on each of the indexes in the schema in addition to gathering table and column statistics. cascade时索引信息统计不会使用并行方式

stattab : User stat table identifier describing where to save the current statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

options : Further specification of which objects to gather statistics for:

GATHER: Gather statistics on all objects in the schema.

GATHER STALE: Gather statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

GATHER EMPTY: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.

LIST STALE: Return list of stale objects as determined by looking at the *_tab_modifications views.

LIST EMPTY: Return list of objects which currently have no statistics.

objlist : List of objects found to be stale or empty.

statown : Schema containing stattab (if different than ownname).

Export Schema Stats
DBMS_STATS.export_schema_stats (
  ownname VARCHAR2,
  stattab VARCHAR2,
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

stattab : User stat table identifier describing where to store the statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

statown : Schema containing stattab (if different than ownname).

Import Schema Stats
DBMS_STATS.import_schema_stats (
  ownname VARCHAR2,
  stattab VARCHAR2,
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

stattab : User stat table identifier describing from where to retrieve the statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

statown : Schema containing stattab (if different than ownname).

Delete Schema Stats
DBMS_STATS.delete_schema_stats (
  ownname VARCHAR2,
  stattab VARCHAR2 DEFAULT NULL,
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

stattab : User stat table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

statown : Schema containing stattab (if different than ownname).

Set Table Stats 手工设置统计信息
DBMS_STATS.set_table_stats (
  ownname  VARCHAR2,
  tabname  VARCHAR2,
  partname VARCHAR2 DEFAULT NULL,
  stattab  VARCHAR2 DEFAULT NULL,
  statid   VARCHAR2 DEFAULT NULL,
  numrows  NUMBER   DEFAULT NULL,
  numblks  NUMBER   DEFAULT NULL,
  avgrlen  NUMBER   DEFAULT NULL,
  flags    NUMBER   DEFAULT NULL,
  statown  VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

tabname : Name of the table.

partname : Name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.

stattab : User stat table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the table (partition).

numblks : Number of blocks the table (partition) occupies.

avgrlen : Average row length for the table (partition).

flags : For internal Oracle use (should be left as NULL).

statown : Schema containing stattab (if different than ownname).

Get Table Stats
DBMS_STATS.get_table_stats (
  ownname     VARCHAR2,
  tabname     VARCHAR2,
  partname    VARCHAR2 DEFAULT NULL,
  stattab     VARCHAR2 DEFAULT NULL,
  statid      VARCHAR2 DEFAULT NULL,
  numrows OUT NUMBER,
  numblks OUT NUMBER,
  avgrlen OUT NUMBER,
  statown     VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

tabname : Name of the table to which this column belongs.

partname : Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.

stattab : User stat table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the table (partition).

numblks : Number of blocks the table (partition) occupies.

avgrlen : Average row length for the table (partition).

statown : Schema containing stattab (if different than ownname).

Get Index Stats
DBMS_STATS.GET_INDEX_STATS (
  ownname      VARCHAR2,
  indname      VARCHAR2,
  partname     VARCHAR2 DEFAULT NULL,
  stattab      VARCHAR2 DEFAULT NULL,
  statid       VARCHAR2 DEFAULT NULL,
  numrows  OUT NUMBER,
  numlblks OUT NUMBER,
  numdist  OUT NUMBER,
  avglblk  OUT NUMBER,
  avgdblk  OUT NUMBER,
  clstfct  OUT NUMBER,
  indlevel OUT NUMBER,
  statown      VARCHAR2 DEFAULT NULL);
ownname : Name of the schema.

indname : Name of the index.

partname : Name of the index partition for which to get the statistics. If the index is partitioned and if partname is NULL, then the statistics are retrieved for the global index level.

stattab : User stat table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the index (partition).

numlblks : Number of leaf blocks in the index (partition).

numdist : Number of distinct keys in the index (partition).

avglblk : Average integral number of leaf blocks in which each distinct key appears for this index (partition).

avgdblk : Average integral number of data blocks in the table pointed to by a distinct key for this index (partition).

clstfct : Clustering factor for the index (partition).

indlevel : Height of the index (partition).

statown : Schema containing stattab (if different than ownname).

DBMS_STATS in action (Examples)

Create Stats Table

BEGIN
  DBMS_STATS.create_stat_table (
    ownname  => 'scott',
    stattab  => 'stats_table',
    tblspace => 'users');
END;
/

Drop Stats Table

BEGIN
  DBMS_STATS.drop_stat_table (
    ownname => 'scott',
    stattab => 'stats_table');
END;
/

Gather Schema Stats to Data Dictionary

BEGIN
  DBMS_STATS.gather_schema_stats (
    ownname          => 'scott',
    estimate_percent => null,
    block_sample     => false,
    method_opt       => 'FOR ALL COLUMNS SIZE 1',
    degree           => null,
    granularity      => 'ALL',
    cascade          => true,
    options          => 'GATHER');
END;
/

Gather Schema Stats to Stats Table

BEGIN
  DBMS_STATS.gather_schema_stats (
    ownname          => 'scott',
    estimate_percent => null,
    block_sample     => false,
    method_opt       => 'FOR ALL COLUMNS SIZE 1',
    degree           => null,
    granularity      => 'ALL',
    cascade          => true,
    stattab          => 'stats_table',
    statid           => 'TEST1',
    options          => 'GATHER',
    statown          => 'scott');
END;
/

Export Schema Statistics from Data Dictionary to Stats Table

BEGIN
  DBMS_STATS.export_schema_stats (
    ownname  => 'scott',
    stattab  => 'stats_table_backup',
    statid   => 'BACKUP_TEST1',
    statown  => 'scott');
END;
/

Import Schema Statistics from Data Dictionary to Stats Table

BEGIN
  DBMS_STATS.import_schema_stats (
    ownname  => 'scott',
    stattab  => 'stats_table',
    statid   => 'TEST1',
    statown  => 'scott');
END;
/

Delete Schema Stats from Data Dictionary

BEGIN
  DBMS_STATS.delete_schema_stats ('scott');
END;
/

Delete Schema Stats from Stats Table

BEGIN
  DBMS_STATS.delete_schema_stats (
    ownname  => 'scott',
    stattab  => 'stats_table_backup',
    statid   => 'BACKUP_TEST1',
    statown  => 'scott');
END;
/

Set Table Stats (Manually) in the Data Dictionary

BEGIN
  DBMS_STATS.set_table_stats (
    ownname  => 'scott',
    tabname  => 'emp',
    partname => null,
    numrows  => 650000000,
    numblks  => 53455443,
    avgrlen  => 212,
    flags    => null);
END;
/

Set Table Stats (Manually) in the Stats Table

BEGIN
  DBMS_STATS.set_table_stats (
    ownname  => 'scott',
    tabname  => 'emp',
    partname => null,
    stattab  => 'stats_table',
    statid   => 'TEST1',
    numrows  => 650000000,
    numblks  => 53455443,
    avgrlen  => 212,
    flags    => null,
    statown  => 'scott');
END;
/

Get Table Statistics in SQL*Plus Variables

SQL> variable NUMROWS number
SQL> variable NUMBLKS number
SQL> variable AVGRLEN number

BEGIN
  DBMS_STATS.get_table_stats (
    'scott',
    'emp',
    NUMROWS=>:numrows,
    NUMBLKS=>:numblks,
    AVGRLEN=>:avgrlen);
END;
/

PL/SQL procedure successfully completed.

SQL> print NUMROWS NUMBLKS AVGRLEN

   NUMROWS
----------
      1000

   NUMBLKS
----------
        28

   AVGRLEN
----------
        92

Get Index Statistics in SQL*Plus Variables

SQL> variable NUMROWS number
SQL> variable NUMLBLKS number
SQL> variable NUMDIST number
SQL> variable AVGLBLK number
SQL> variable AVGDBLK number
SQL> variable CLSTFCT number
SQL> variable INDLEVEL number

BEGIN
  DBMS_STATS.get_index_stats (
    'SCOTT',
    'EMP_PK',
    NUMROWS   => :NUMROWS,
    NUMLBLKS  => :NUMLBLKS,
    NUMDIST   => :NUMDIST,
    AVGLBLK   => :AVGLBLK,
    AVGDBLK   => :AVGDBLK,
    CLSTFCT   => :CLSTFCT,
    INDLEVEL  => :INDLEVEL);
 END;
/

PL/SQL procedure successfully completed.

SQL> print NUMROWS NUMLBLKS NUMDIST AVGLBLK AVGDBLK CLSTFCT INDLEVEL

   NUMROWS
----------
      1000

  NUMLBLKS
----------
         3

   NUMDIST
----------
      1000

   AVGLBLK
----------
         1

   AVGDBLK
----------
         1

   CLSTFCT
----------
        15

  INDLEVEL
----------
         1

Automated table monitoring and stale statistics gathering example

You can automatically gather statistics or create lists of tables that have stale or no statistics.

To automatically gather statistics, run the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures with the OPTIONS and objlist parameters. Use the following values for the options parameter:

GATHER STALE : Gathers statistics on tables with stale statistics.

GATHER : Gathers statistics on all tables. (default)

GATHER EMPTY : Gathers statistics only on tables without statistics.

LIST STALE : Creates a list of tables with stale statistics.

LIST EMPTY : Creates a list of tables that do not have statistics.

The objlist parameter identifies an output parameter for the LIST STALE and LIST EMPTY options. The objlist parameter is of type DBMS_STATS.OBJECTTAB.

Step 1 : Perform a quick analyze to load in base statistics
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname           => 'scott',
  estimate_percent  => null,              -- Small table, lets compute
  block_sample      => false,
  method_opt        => 'FOR ALL COLUMNS',
  degree            => null,              -- No parallelism used in this example
  granularity       => 'ALL',
  cascade           => true,              -- Make sure we include indexes
  options           => 'GATHER'           -- Gather mode
  );
END;
/

PL/SQL procedure successfully completed.
Step 2 : Examine the current statistics
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name='EMP';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP                                  1500         28          92
Step 3 : Turn on Automatic Monitoring
Now turn on automatic monitoring for the emp table. This can be done using the alter table method. Starting with Oracle 9i, you can also perform this at the “schema”, and “entire database” level. I provide the syntax for all three methods below. Monitor only the EMP table.
alter table emp monitoring;

Table altered.
Monitor all of the tables within Scott’s schema. (Oracle 9i and higher)
BEGIN
  DBMS_STATS.alter_schema_tab_monitoring('scott', true);
END;
/

PL/SQL procedure successfully completed.
Monitor all of the tables within the database. (Oracle 9i and higher) Note: Although the option to collect statistics for SYS tables is available via ALTER_DATABASE_TAB_MONITORING, Oracle continues to recommend against this practice until the next major release after 9i Release 2. Also note that the ALTER_DATABASE_TAB_MONITORING procedure in the DBMS_STATS package only monitors tables; there is an ALTER INDEX…MONITORING statement which can be used to monitor indexes. Thanks to Nabil Nawaz for providing this and pointing out an error I made in the previous version of this article.
BEGIN
  DBMS_STATS.alter_database_tab_monitoring (
    monitoring => true,
    sysobjs    => false);      -- Don't set to true, see note above.
END;
/

PL/SQL procedure successfully completed.
Step 4 : Verify that monitoring is turned on.
Note: The results of the following query are from running the alter table ... statement on the emp table only.
SELECT table_name, monitoring
FROM user_tables
ORDER BY monitoring;

TABLE_NAME                     MONITORING
------------------------------ ----------
DEPT                           NO
EMP                            YES
Step 5 : Delete some rows from the database.
SQL> DELETE FROM emp WHERE rownum < 501;

500 rows deleted.

SQL> commit;

Commit complete.
Step 6 : Wait until the monitered data is flushed.
Data can be flushed in several ways.

In Oracle 8i, you can wait it out for 3 hours.

In Oracle 9i and higher, you only need to wait 15 minutes.

In either version, restart the database.

For immediate results in Oracle 9i and higher, use the DBMS_STATS.flush_database_monitoring_info package.

．判断陈旧的统计数据

为了判断数据库对象是否需要新的统计数据，oracle提供一个表监控工具。当statistics_level=typical或all时，监控被开启。监控追踪表自从上次统计数据被收集以来，表被insert,update,delete的近似值，还有表是否被truncate。表的变化信息可以查看user_tab_modifications视图。随着数据修改，当oracle移植信息到视图的时候，可能会有几分钟的迟延。DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程来立即反映在内存中显著的监控信息。

当options参数被设置为gather stale或gather auto ，Gather_database_stats或gather_schema_stats过程使用旧的统计数据来收集新的统计数据。如果监控表的已经被修改了超过10%，那么这些统计数据被认为是旧的，需要再次被收集。

OK, I’m impatient…
exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.
Step 7 : Check for what it has collected.
As user “scott”, check USER_TAB_MODIFICATIONS to see what it was collected.
SELECT * FROM user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
---------- -------------- ----------------- ------- ------- ------- --------- ---------
EMP                                               0       0     500 18-SEP-02 NO
Step 8 : Execute DBMS_STATS to gather stats on all “stale” tables.
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname           => 'scott',
    estimate_percent  => null,
    block_sample      => false,
    method_opt        => 'FOR ALL COLUMNS',
    degree            => null,
    granularity       => 'ALL',
    cascade           => true,
    options           => 'GATHER STALE');
END;
/

PL/SQL procedure successfully completed.
Step 9 : Verify that the table is no longer listed in USER_TAB_MODIFICATIONS.
SQL> SELECT * FROM user_tab_modifications;

no rows selected.
Step 10 : Examine some of new statistics collected.
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables where table_name='EMP';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP                                  1000         28          92

How to determine if dictionary statistics are RDBMS-generated or user-defined

The following section explains how to determine if your dictionary statistics are RDBMS-generated or set by users through one of the DBMS_STATS.SET_xx_STATS procedures. This is crucial for development environments that are testing the performance of SQL statements with various sets of statistics. The DBA will need to know if the relying statistics are RDBMS-defined or user-defined.

RDBMS-generated statistics are generated by the following:

ANALYZE SQL command

DBMS_UTILITY.ANALYZE_SCHEMA procedure

DBMS_UTILITY.ANALYZE_DATABASE procedure

DBMS_DDL.ANALYZE_OBJECT procedure

8.1 DBMS_STATS.GATHER_xx_STATS procedures

User generated statistics are only done through the use of the DBMS_STATS.SET_xx_STATS procedures The column USER_STATS from DBA_TABLES, ALL_TABLES, USER_TABLES displays:

YES, when statistics are entered directly by a user.

NO, when statistics are generated by RDBMS through an ANALYZE statement.

Categories: dbms_stats, oracle Tags: dbms_stats

CREATE DATABASE Statement Fails With ORA-1501 ORA-1519 ORA-604 ORA-942 Errors

February 20th, 2009 happydba No comments

HTML clipboard .style1 { color: #FF0000; } .style2 { background-color: #C0C0C0; }


文档 ID:	434557.1	类型:	PROBLEM
Modified Date:	08-NOV-2007	状态:	PUBLISHED

In this Document
Symptoms
Cause
Solution
References

Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.

Symptoms

CREATE DATABASE statement fails with ORA-604 / ORA-942 while executing SQL.BSQ :

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file ‘%ORACLE_HOME%\RDBMS\ADMIN\SQL.BSQ’ near line ..
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist

Cause

The SYSAUX tablespace is too small.

To capture the failing SQL statements, add the following line to the database init.ora file and re-run the create database command.

EVENT=”604 trace name errorstack level 3: 10046 trace name context forever, level 4″

The trace file generated in udump (or user_dump_dest location) shows the failing SQL statement to be:

PARSE ERROR #6:len=148 dep=2 uid=0 oct=3 lid=0 tim=3388826914006940 err=942
select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and
to_number(bitand(flags, 16)) = 16 order by dropsc
EXEC #5:c=10439,e=12665,p=0,cr=57,cu=67,mis=0,r=0,dep=1,og=4,tim=3388826914007099
ERROR #5:err=604 tim=201164619
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist
Offending statement at line 6377
create table aw_obj$ /* Analytical Workspace Object table */
(awseq# number, /* aw sequence number */
oid number(20), /* object number, up to UB8MAXVAL */
objname varchar2(256), /* object name, ref NAMESIZE in xsobj.c */
gen# number(10), /* generation number */
objtype number(4), /* object type */
partname varchar2(256), /* partition name */
objdef blob, /* object definition */
objvalue blob, /* object value */
compcode blob) /* compiled code body */
lob(objdef) store as (enable storage in row)
lob(objvalue) store as (enable storage in row)
lob(compcode) store as (enable storage in row)
tablespace sysaux
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file ‘//DD:SQLBSQ’ near line 6377
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist

Re-running the CREATE DATABASE, but preceding it with ‘alter system recyclebin=off‘ statement, to disable the recyclebin option gives the following results:

ORA-1652: unable to extend temp segment by 16 in tablespace SYSAUX
12:09:54.66 00030000 Errors in file ORADBTS0.TRACE.D070523.T100855.G0030000:
ORA-00604: error occurred at recursive SQL level 1
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

The actual CREATE DATABASE statement was:

CREATE DATABASE TS0
MAXDATAFILES 255
MAXLOGFILES 255
CONTROLFILE REUSE
LOGFILE GROUP 1 (‘ORADBTS0.REDO.G1.M1′,
‘ORADBTS0.REDO.G1.M2′) SIZE 25063424 REUSE,
GROUP 2 (‘ORADBTS0.REDO.G2.M1′,’ORADBTS0.REDO.G2.M2′) SIZE 25063424 REUSE
DATAFILE ‘ORADBTS0.DATA.SYSTEM.D01′ SIZE 200156K REUSE,
‘ORADBTS0.DATA.SYSTEM.D02′ SIZE 200156K REUSE
SYSAUX DATAFILE ‘ORADBTS0.DATA.SYSAUX.D01′ SIZE 200156 REUSE
UNDO TABLESPACE “UNDOTBS”
DATAFILE ‘ORADBTS0.DATA.UNDO.D01′ SIZE 200156K,
‘ORADBTS0.DATA.UNDO.D02′ SIZE 200156K
DEFAULT TABLESPACE USERS
DATAFILE ‘ORADBTS0.DATA.USER.D01′ SIZE 40316 K REUSE,
‘ORADBTS0.DATA.USER.D02′ SIZE 40316 K REUSE
DEFAULT TEMPORARY TABLESPACE “TEMP”
TEMPFILE ‘ORADBTS0.DATA.TEMP.D01′ SIZE 236924 K REUSE
CHARACTER SET “EE8EBCDIC870S”

The SYSAUX datafile has a size specification of 200156 bytes. The intention was that this should have been 200156K to match the SYSTEM and UNDO tablespaces.

Solution

Increase the size of SYSAUX tablespace in the CREATE DATABASE statement.

From Oracle® Database Administrator’s Guide10g Release 2 (10.2)

Chapter 2 – Creating an Oracle Database
Creating the SYSAUX Tablespace

“The SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed … ”

References

Note 265253.1 – 10g Recyclebin Features And How To Disable it( _recyclebin )

Categories: ora-err, oracle Tags: 10046, event, ora-924

Older Entries

Archive

statspack 的若干使用技巧

Oracle ASH 和Session Tracing

Oracle中密码设置管理（使用自带脚本utlpwdmg.sql）

oracle 使用存储过程分割字符串为数组

sqlplus限制命令使用的方法

ORACLE SQL PLUS 使用技巧

CentOS 5连接HP EVA8000实现multipath

exec FLUSH DATABASE MONITORING INFO;

Gather Statistics with DBMS_STATS by Jeff

CREATE DATABASE Statement Fails With ORA-1501 ORA-1519 ORA-604 ORA-942 Errors

Applies to:

Symptoms

Cause

Solution

References

Random Posts

Categories

Blogroll

Archives

Meta

Tables
Statistic	Default Value Used by Optimizer
Cardinality	100 rows
Avg. row len	20 bytes
No. of blocks	100
Remote cardinality	2000 rows
Remote average row length	100 bytes
Indexes
Statistic	Default Value Used by Optimizer
Levels	1
Leaf blocks	25
Leaf blocks/key	1
Data blocks/key	1
Distinct keys	100
Clustering factor	800 (8*no. of blocks)

Archive

Applies to:

Symptoms

Cause

Solution

References

Random Posts

Tag Cloud

Categories

Blogroll

Archives

Meta