【Oracle】-【权限-ORA-04043】- ORA-04043: object

转载

mb5ff2f24b42377 2013-07-26 18:34:00

文章标签 sql hive oracle 数据库访问权限 文章分类 MySQL 数据库

用非dba账号(但赋予了DBA角色)登录一个新的10g数据库想看下版本号，
SQL> desc v$instance;
ERROR:
ORA-04043: object "SYS"."V_$INSTANCE" does not exist
奇怪，之前有个9i的库，同样的账号就可以查看呢？？？

分析：
这里显示的是"SYS"."V_$INSTANCE"对象不存在，我们知道，当账户没有某个对象的权限时，查询该对象，可能返回的就是对象不存在的错误(我觉得有时还是有点歧义，或者说有点误导的)。如下是04043的说明，也没有提到是因为无权限的原因。
ORA-04043: object string does not exist
Cause: An object name was specified that was not recognized by the system. There are several possible causes:
- An invalid name for a table, view, sequence, procedure, function, package, or package body was entered. Since the system could not recognize the invalid name, it responded with the message that the named object does not exist.
- An attempt was made to rename an index or a cluster, or some other object that cannot be renamed.
Action: Check the spelling of the named object and rerun the code. (Valid names of tables, views, functions, etc. can be listed by querying the data dictionary.)

那好，说这个"SYS"."V_$INSTANCE"不存在，就登录sys，
SQL> desc V$INSTANCE
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)
说明这个对象是存在的，只是现在只能通过这个SYS账户才能查询。
再从V$INSTANCE查询出错的原因分析，这个V$是视图，它提示的是V_$INSTANCE找不到，说明V$INSTANCE是对V_$INSTANCE的封装。

赋予dcsopen查询v$instance的权限：
SQL> GRANT SELECT ON v$instanceTO dcsopen;
GRANT SELECT ON v$instance TO dcsopen
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
不让赋予它的查询权限？

是否V$INSTANCE还有其它的对象？除了这个同义词。
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = 'V$INSTANCE';
OWNER OBJECT_TYPE
------------------------------ -------------------
PUBLIC SYNONYM
除了同义词外，应该是没有其它的同名对象了。

再尝试：
SQL> CREATE PUBLIC SYNONYM P_INSTANCE for V$INSTANCE;
Synonym created.
SQL> grant select on p_instance to dcsopen;
grant select on p_instance to dcsopen
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
仍旧不允许赋予查询v$instance的权限。

以前对于同义词的理解中，如果账户没有访问同义词对应基表的访问权限，查询同义词就会提示找不到对象的报错。
SQL> create public synonym p_instance for v$instance;
Synonym created.
SQL> grant select on p_instance to dcsopen;
grant select on p_instance to dcsopen
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
也是不允许。

这里提示的fixed tables/views，表明可能与Dynamic Performance Views有关，因为DPV是基于fixed tables的。这些表是Oracle底层C结构体的精髓表现。

那我们看下上面提到的V_$INSTANCE，
SQL> set long 5000
SQL> SELECT text
2 FROM dba_views
3 WHERE owner = 'SYS'
4 AND view_name = 'V_$INSTANCE';
TEXT
--------------------------------------------------------------------------------
select "INSTANCE_NUMBER","INSTANCE_NAME","HOST_NAME","VERSION","STARTUP_TIME","S
TATUS","PARALLEL","THREAD#","ARCHIVER","LOG_SWITCH_WAIT","LOGINS","SHUTDOWN_PEND
ING","DATABASE_STATUS","INSTANCE_ROLE","ACTIVE_STATE","BLOCKED" from v$instance
郁闷了，怎么又调用回来了？v$instance。

有帖子建议别查询传统意义的视图字典了，查询v$fixed_view_definition这个，
V$FIXED_VIEW_DEFINITION
This view contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.
SQL> SELECT view_definition
2 FROM v$fixed_view_definition
3 WHERE view_name = 'V$INSTANCE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSION , STARTUP_TIME , S
TATUS , PARALLEL , THREAD# , ARCHIVER , LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PEND
ING, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED from GV$INSTANCE wher
e inst_id = USERENV('Instance')
我们看到V$INSTANCE真正的定义了。那继续：
SQL> SELECT view_definition
2 FROM v$fixed_view_definition
3 WHERE view_name = 'GV$INSTANCE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxstim,decode(ksuxssts,0
,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE','UNKNOWN'),decode(ksuxsshr,0,'N
O',1,'YES',2,NULL),ksuxsthr,decode(ksuxsarc,0,'STOPPED',1,'STARTED','FAILED'),de
code(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'),deco
de(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INSTANCE','UNKNOWN'), decode(qui_s
tate,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 k
vittag = 'kcbwst'
此时我们就可以知道V$INSTANCE真正使用的fixed tables。

对于这个问题的解决方法，可能不用上面这些复杂的查询，其实就是：
SQL> GRANT SELECT ON v_$instance TO dcsopen;
Grant succeeded.
既然提示"V_$INSTANCE"找不到，那就赋予它的权限就行了。

SQL> select owner, table_name from user_tab_privs;
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$DATABASE
SYS V_$INSTANCE

SQL> desc v$instance;
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)

这个问题是只是让我们获得了一种了解V$视图真正参考对象的方法。这个过程还是值得总结与记录的。