【Oracle】-【許可權-ORA-04043】- object does not exist
用非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 wner = '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$檢視真正參考物件的方法。這個過程還是值得總結與記錄的。
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 wner = '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$檢視真正參考物件的方法。這個過程還是值得總結與記錄的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-767187/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-04043: object DBA_DATA_FILES does not existObject
- 模擬ORA-04043並解決（dba_* does not exist）
- 擁有GRANT ANY OBJECT PRIVILEGE許可權時的許可權回收Object
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- ORA-12545 TNS: Host or Object Does not ExistObject
- ORACLE許可權Oracle
- 如果得知無許可權的 Authorization ObjectObject
- oracle 許可權集合Oracle
- oracle 許可權管理Oracle
- Oracle許可權（一）Oracle
- oracle許可權全集Oracle
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- Oracle 許可權（grant、revoke）Oracle
- Oracle系統許可權Oracle
- Oracle檢視許可權Oracle
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- 系統許可權 GRANT ANY OBJECT PRIVILEGE的作用！Object
- asmcmd does not exist in directoryASM
- Oracle 查詢許可權角色Oracle
- oracle許可權整理檔案Oracle
- oracle使用者許可權Oracle
- oracle列級許可權控制Oracle
- Oracle users / 許可權 / grant privOracle
- Oracle 中的references許可權Oracle
- Oracle ERP許可權管理Oracle
- oracle 使用者許可權Oracle
- 許可權之選單許可權
- ORA-12545: Connect failed because target host or object does not existAIObject
- Oracle RAC的ORA-12545 Connect failed because target host or object does not exist問題OracleAIObject
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- linux 檔案許可權 s 許可權和 t 許可權解析Linux
- javax.media does not existJava
- Oracle 使用者許可權管理與常用許可權資料字典列表Oracle
- Oracle中定義者許可權和呼叫者許可權案例分析Oracle
- Oracle軟體許可權修復Oracle
- Oracle許可權相關查詢Oracle
- Oracle使用者與許可權Oracle
- Oracle角色許可權之Default RoleOracle