【Oracle】-【权限-ORA-04043】- ORA-04043: object "SYS"."V_$DATABASE" does not exist解决(二)

那我们看下上面提到的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