月归档：十一月 2011

plsql dev中Dynamic Performance Tables not accessible分析解决

发表于 2011 年 11 月 9 日由惜分飞

相信很多使用plsql dev的朋友多遇到过类此如下面的提示：

Dynamic Performance Tables not accessible, 
Automatic Statistics Disabled for this session 

You can disable statistics in the preference menu,or obtanin select 
priviliges on the v$session,v$sesstat and v$statname tables

一、产生该提示原因
plsql dev在用户运行过程中，要收集用户统计信息，但是由于你现在登录的用户没有访问v$session,v$sesstat and v$statname视图的权限，所以不能收集当前用户的统计信息，和plsql dev工具中配置的Automatic Statistics相冲突，所以就出现了这个提示，试验验证：

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 10 04:31:57 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@XFF>create user chf identified by xifenfei;

User created.

sys@XFF>grant create session,resource to chf;

Grant succeeded.

sys@XFF>conn chf/xifenfei
Connected.
chf@XFF>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;

no rows selected

创建一个chf用户，授权create session,resource，无v$session,v$sesstat and v$statname视图访问权限，使用plsql dev登录并查询user_tables表(登录时不会提示，只有用户执行了查询或者相关类此操作时候才会提示)
Dynamic Performance Tables not accessible

二、解决问题
根据警告提示，可以有两种方法解决这种警告
1、关闭plsql dev统计功能
在 Tools->Preferences->Options里把Automatic Statistics前的那个勾子去掉，保存
2、给访问用户授权访问相关视图
授权访问v_$session,v_$sesstat,v_$statname,注意不能直接对v$视图进行授权

chf@XFF>conn / as sysdba
Connected.
sys@XFF>grant select on v_$session to chf;

Grant succeeded.

sys@XFF>grant select on v_$sesstat to chf;

Grant succeeded.

sys@XFF>grant select on v_$statname to chf;

Grant succeeded.

sys@XFF>conn chf/xifenfei
Connected.

chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;

TABLE_NAME
------------------------------
V_$SESSION
V_$SESSTAT
V_$STATNAME

三、问题分析
通过上面的解决方法，为什么授权访问v$session,v$sesstat and v$statname视图就可以Statistics用户的信息了呢？请见下面的两张图
图1：通过plsql dev中的tools–>session选项看用户统计信息
通过plsql dev中的session选项看用户统计信息
图2：通过sql语句查询用户统计信息

通过两张图的比较可能会发现，他们的数值有一点点出入，那是因为我先通过tools查询出用户统计信息，再通过sql查询，所以图1中的数据有些选项会比图2小那么一点点，通过对v$session,v$sesstat and v$statname视图分析，发现其实plsql dev就是通过下面sql实现统计功能，也从而进一步说明了，为什么plsql dev收集统计信息需要对v$session,v$sesstat and v$statname视图授于访问权限

SELECT C.NAME, B.STATISTIC#, B.VALUE
  FROM V$SESSION A, V$SESSTAT B, V$STATNAME C
 WHERE A.SID = B.SID
   AND A.AUDSID = USERENV('SESSIONID')
   AND B.STATISTIC# = C.STATISTIC#
   ORDER BY C.STATISTIC#;

发表在 Oracle | 一条评论

深入分析数据库版本相关视图

发表于 2011 年 11 月 9 日由惜分飞

1、dba_registry视图

SQL> set line 200
SQL> col comp_name for a35 
SQL> col version for a12
SQL> col status for a6
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                           VERSION      STATUS
----------------------------------- ------------ ------
Spatial                             10.2.0.5.0   VALID
Oracle interMedia                   10.2.0.5.0   VALID
OLAP Catalog                        10.2.0.5.0   VALID
Oracle Enterprise Manager           10.2.0.5.0   VALID
Oracle XML Database                 10.2.0.5.0   VALID
Oracle Text                         10.2.0.5.0   VALID
Oracle Expression Filter            10.2.0.5.0   VALID
Oracle Rule Manager                 10.2.0.5.0   VALID
Oracle Workspace Manager            10.2.0.5.0   VALID
Oracle Data Mining                  10.2.0.5.0   VALID
Oracle Database Catalog Views       10.2.0.5.0   VALID
Oracle Database Packages and Types  10.2.0.5.0   VALID
JServer JAVA Virtual Machine        10.2.0.5.0   VALID
Oracle XDK                          10.2.0.5.0   VALID
Oracle Database Java Packages       10.2.0.5.0   VALID
OLAP Analytic Workspace             10.2.0.5.0   VALID
Oracle OLAP API                     10.2.0.5.0   VALID

17 rows selected.

SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY" ("COMP_ID", "COMP_NAME", "VERSION", "STATUS", "MODIFIED", "NAMESPAC
E", "CONTROL", "SCHEMA", "PROCEDURE", "STARTUP", "PARENT_ID", "OTHER_SCHEMAS") A
S
  SELECT r.cid, r.cname, r.version,
       SUBSTR(dbms_registry.status_name(r.status),1,11),
       TO_CHAR(r.modified,'DD-MON-YYYY HH24:MI:SS'),
       r.namespace, i.name, s.name, r.vproc,
       DECODE(bitand(r.flags,1),1,'REQUIRED',NULL), r.pid,
       dbms_registry.schema_list_string(r.cid)
FROM registry$ r, user$ s, user$ i
WHERE r.schema# = s.user# AND r.invoker#=i.user#

SQL> DESC registry$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CID                                                   NOT NULL VARCHAR2(30)
 CNAME                                                          VARCHAR2(255)
 SCHEMA#                                               NOT NULL NUMBER
 INVOKER#                                              NOT NULL NUMBER
 VERSION                                                        VARCHAR2(30)
 STATUS                                                NOT NULL NUMBER
 FLAGS                                                 NOT NULL NUMBER
 MODIFIED                                                       DATE
 PID                                                            VARCHAR2(30)
 BANNER                                                         VARCHAR2(80)
 VPROC                                                          VARCHAR2(61)
 DATE_INVALID                                                   DATE
 DATE_VALID                                                     DATE
 DATE_LOADING                                                   DATE
 DATE_LOADED                                                    DATE
 DATE_UPGRADING                                                 DATE
 DATE_UPGRADED                                                  DATE
 DATE_DOWNGRADING                                               DATE
 DATE_DOWNGRADED                                                DATE
 DATE_REMOVING                                                  DATE
 DATE_REMOVED                                                   DATE
 NAMESPACE                                             NOT NULL VARCHAR2(30)
 ORG_VERSION                                                    VARCHAR2(30)
 PRV_VERSION                                                    VARCHAR2(30)

SQL> SELECT BANNER,VERSION,modified,prv_version FROM SYS.registry$;

BANNER                                                                           VERSION      MODIFIED            PRV_VERSION
-------------------------------------------------------------------------------- ------------ ------------------- ----------------
Oracle Database Catalog Views Release 10.2.0.5.0 - 64bi                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Packages and Types Release 10.2.0.5.0 - Production               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Workspace Manager Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.3
JServer JAVA Virtual Machine Release 10.2.0.5.0 - Production                     10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XDK Release 10.2.0.5.0 - Production                                       10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Java Packages Release 10.2.0.5.0 - Production                    10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Expression Filter Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Data Mining Release 10.2.0.5.0 - Production                               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Text Release 10.2.0.5.0 - Production                                      10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XML Database Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Rule Manager Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle interMedia Release 10.2.0.5.0 - Production                                10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
OLAP Analytic Workspace Release 10.2.0.5.0 - Production                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle OLAP API Release 10.2.0.5.0 - Production                                  10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
OLAP Catalog Release 10.2.0.5.0 - Production                                     10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Spatial Release 10.2.0.5.0 - Production                                          10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Oracle Enterprise Manager Release 10.2.0.5.0 - Production                        10.2.0.5.0   2011-11-02 17:23:47 10.2.0.4.0

17 rows selected.

2、v$version或者PRODUCT_COMPONENT_VERSION视图

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> col product for a35
SQL> col product for a40
SQL> select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION      STATUS
---------------------------------------- ------------ --------------
NLSRTL                                   10.2.0.5.0   Production
Oracle Database 10g Enterprise Edition   10.2.0.5.0   64bi
PL/SQL                                   10.2.0.5.0   Production
TNS for Linux:                           10.2.0.5.0   Production

SQL> set long 1000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('VIEW','PRODUCT_COMPONENT_VERSION','SYS') FROM DUAL;

CREATE OR REPLACE FORCE VIEW "SYS"."PRODUCT_COMPONENT_VERSION" ("PRODUCT", "VERSION", "STATUS") AS
(select
substr(banner,1, instr(banner,'Version')-1),
substr(banner, instr(banner,'Version')+8,
instr(banner,' - ')-(instr(banner,'Version')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Version') > 0
and
((instr(banner,'Version') <   instr(banner,'Release')) or
instr(banner,'Release') = 0))
union
(select
substr(banner,1, instr(banner,'Release')-1),
substr(banner, instr(banner,'Release')+8,
instr(banner,' - ')-(instr(banner,'Release')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Release') > 0
and
instr(banner,'Release') <   instr(banner,' - '))


SQL> COL object_name for a20
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
PUBLIC                         V$VERSION            SYNONYM

SQL> SELECT TABLE_OWNER,TABLE_NAME FROM dba_synonyms a WHERE a.synonym_name='V$VERSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$VERSION

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V_$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            V_$VERSION           VIEW

SQL> select dbms_metadata.get_ddl('VIEW','V_$VERSION','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$VERSION','SYS')
-----------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$VERSION" ("BANNER") AS
  select "BANNER" from v$version

SQL> select * from v$fixed_table where name LIKE '%V%VERSION%';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
GV$VERSION                     4294951314 VIEW       65537
V$VERSION                      4294951045 VIEW       65537

SQL>  COL VIEW_DEFINITION FOR A80
SQL> select * from v$fixed_view_definition where view_name='V$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------
V$VERSION                      select  BANNER from GV$VERSION where inst_id = USERENV('Instance')

SQL> select * from v$fixed_view_definition where view_name='GV$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ -------------------------------------------------------
GV$VERSION                     select inst_id, banner from x$version

SQL> DESC x$version
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 BANNER                                                         VARCHAR2(64)

SQL> SET LINE 200
SQL> SELECT * FROM x$version;

ADDR                   INDX    INST_ID BANNER
---------------- ---------- ---------- ----------------------------------------------------------------
00002AB64240D028          0          1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
00002AB64240D028          1          1 PL/SQL Release 10.2.0.5.0 - Production
00002AB64240D028          2          1 CORE     10.2.0.5.0      Production
00002AB64240D028          3          1 TNS for Linux: Version 10.2.0.5.0 - Production
00002AB64240D028          4          1 NLSRTL Version 10.2.0.5.0 - Production

3、查看v$instance视图

SQL> select version from v$instance;

VERSION
------------
10.2.0.5.0

--通过同v$version同样操作，得出如下语句
 SELECT KS.INST_ID,
        KSUXSINS,
        KSUXSSID,
        KSUXSHST,
        KSUXSVER,
        KSUXSTIM,
        DECODE(KSUXSSTS,
               0,
               'STARTED',
               1,
               'MOUNTED',
               2,
               'OPEN',
               3,
               'OPEN MIGRATE',
               'UNKNOWN'),
        DECODE(KSUXSSHR, 0, 'NO', 1, 'YES', 2, NULL),
        KSUXSTHR,
        DECODE(KSUXSARC, 0, 'STOPPED', 1, 'STARTED', 'FAILED'),
        DECODE(KSUXSLSW,
               0,
               NULL,
               2,
               'ARCHIVE LOG',
               3,
               'CLEAR LOG',
               4,
               'CHECKPOINT',
               5,
               'REDO GENERATION'),
        DECODE(KSUXSDBA, 0, 'ALLOWED', 'RESTRICTED'),
        DECODE(KSUXSSHP, 0, 'NO', 'YES'),
        DECODE(KVITVAL,
               0,
               'ACTIVE',
               2147483647,
               'SUSPENDED',
               'INSTANCE RECOVERY'),
        DECODE(KSUXSROL,
               1,
               'PRIMARY_INSTANCE',
               2,
               'SECONDARY_INSTANCE',
               'UNKNOWN'),
        DECODE(QUI_STATE,
               0,
               'NORMAL',
               1,
               'QUIESCING',
               2,
               'QUIESCED',
               'UNKNOWN'),
        DECODE(BITAND(KSUXSDST, 1), 0, 'NO', 1, 'YES', 'NO')
   FROM X$KSUXSINST KS, X$KVIT KV, X$QUIESCE QU
  WHERE KVITTAG = 'kcbwst';

SQL> set line 90
SQL> desc X$KSUXSINST;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 KSUXSINS                                                       NUMBER
 KSUXSSID                                                       VARCHAR2(16)
 KSUXSHST                                                       VARCHAR2(64)
 KSUXSVER                                                       VARCHAR2(17)
 KSUXSTIM                                                       DATE
 KSUXSSTS                                                       NUMBER
 KSUXSSHR                                                       NUMBER
 KSUXSTHR                                                       NUMBER
 KSUXSARC                                                       NUMBER
 KSUXSLSW                                                       NUMBER
 KSUXSDBA                                                       NUMBER
 KSUXSSHP                                                       NUMBER
 KSUXSSCN                                                       VARCHAR2(16)
 KSUXSROL                                                       NUMBER
 KSUXSDST                                                       NUMBER

SQL> SELECT  KSUXSVER FROM SYS.X$KSUXSINST;

KSUXSVER
-----------------
10.2.0.5.0

发表在 Oracle | 评论关闭

Fatal NI connect error 12170

发表于 2011 年 11 月 8 日由惜分飞

今天在一台服务器的日志文件中，发现如下信息：

Fatal NI connect error 12170.

  VERSION INFORMATION:
	TNS for Linux: Version 11.1.0.7.0 - Production
	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.1.0.7.0 - Production
	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
  Time: 08-NOV-2011 13:57:10
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.100.107.62)(PORT=52357))

查看mos，有幸发现关于该错误的相关文章
Fatal NI connect error 12170′, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log [ID 1286376.1]
做了一些摘要，算是给自己做个记录，也给不能访问mos的朋友一个参考

1、适用范围

Oracle Net Services - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2   [Release: 11.1 to 11.2]
Information in this document applies to any platform.

2、问题原因

These time out related messages are mostly informational in nature.  The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:

#define ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110

ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:

ETIMEDOUT 238 /* Connection timed out */

For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.

3、解决问题

To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

说明：这个问题是由于Automatic Diagnostic Repository中的 Oracle Net diagnostic在默认的情况下是开启的，当数据库和客户端的连接超过特定时间，就会把这样的信息写入到alert日志中，所以这不是一个致命的问题，如果偶尔出现，可以忽略有点类此ora-3136的错误

发表在 Oracle 监听 | 一条评论

17813235971

QQ 咨询

月归档：十一月 2011

plsql dev中Dynamic Performance Tables not accessible分析解决

深入分析数据库版本相关视图

Fatal NI connect error 12170

文章分类

最近发表

月归档：十一月 2011

plsql dev中Dynamic Performance Tables not accessible分析解决

深入分析数据库版本相关视图

Fatal NI connect error 12170

标签云

文章分类

最近发表