[Oracle]-[permission-ORA-04043]-ORA-04043: object & quot; SYS &

Oracle-permission-ORA-04043-ORA-04043: object "SYS ". "V _ $ DATABASE" does not exist solves the problem of logging on to a new 10g DATABASE with a non-dba account (but with a DBA role). To see the version number, SQL> desc v $ instance; ERROR: ORA-04043: object "SYS ". "V _ $ INSTANCE" does not exist strange, there was a 9i library before, the same account can view it ??? Analysis: "SYS" is displayed here ". the "V _ $ INSTANCE" object does not exist. We know that when the account does not have the permission for an object, querying this object may return an error that does not exist (I think it is still a bit ambiguous sometimes, or a little misleading ). The following is a 04043 explanation, which is not mentioned because of the lack of permissions. ORA-04043: object string does not existCause: 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 cocould not recognize the invalid name, it responded with the message that the named object does not exist. -An attempt was made 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 .) well, say "SYS ". "V _ $ INSTANCE" does not exist. log on to sys, SQL> desc V $ INSTANCE Name Null? Type direction -------- ---------------------------- 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 VARCH AR2 (18) ACTIVE_STATE VARCHAR2 (9) BLOCKED VARCHAR2 (3) indicates that this object exists, but it can only be queried through this SYS account. Analyze the cause of an error from the V $ INSTANCE query. This V $ is the view, which indicates that the V _ $ INSTANCE cannot be found, description V $ INSTANCE is the encapsulation of V _ $ INSTANCE. GRANT dcsopen permission TO query 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 does not allow query permission granted to it? Are there other objects in V $ INSTANCE? Except for this synonym. SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = 'v $ instance'; OWNER OBJECT_TYPE ----------------------------------------- PUBLIC SYNONYM should have no object of the same name except synonyms. Try again: 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 still does not allow permission to query v $ instance. In previous understandings of synonyms, if the account does not have the access permission to access the base table corresponding to the synonym, an error will be reported if the synonym cannot be found. 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 is also not allowed. The fixed tables/views shown here indicate that they may be related to Dynamic Performance Views, because DPV is based on fixed tables. These tables are the essence of Oracle's underlying C struct. Let's take a look at the V _ $ INSTANCE mentioned above, SQL> set long 5000SQL> SELECT text 2 FROM dba_views 3 WHERE owner = 'sys '4 AND view_name = 'v _ $ instance'; TEXT explain select "INSTANCE_NUMBER", "INSTANCE_NAME ", "HOST_NAME", "VERSION", "STARTUP_TIME", "STATUS", "PARALLEL", "THREAD #", "ARCHIVER", "LOG_SWITCH_WAIT", "LOGINS ", "SHUTDOWN_PENDING", "DATABASE_STATUS", "INST ANCE_ROLE "," ACTIVE_STATE "," BLOCKED "from v $ instance is depressed. Why is it called again? V $ instance. We recommend that you do not query the view Dictionary of the traditional meaning in some posts. query v $ fixed_view_definition, V $ FIXED_VIEW_DEFINITIONThis 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 col Umns of the dynamic performance tables. SQL> SELECT view_definition 2 FROM v $ fixed_view_definition 3 WHERE view_name = 'v $ instance'; VIEW_DEFINITION explain select INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS, PARALLEL, THREAD #, ARCHIVER, LOG_SWITCH_WAIT, LOGINS, SHUTDOWN_PENDING, DATABASE_S TATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED from GV $ INSTANCE where inst_id = USERENV ('instance') We can see that V $ Instance is actually defined. Continue: SQL> SELECT view_definition 2 FROM v $ fixed_view_definition 3 WHERE view_name = 'gv $ instance'; VIEW_DEFINITION explain select ks. inst_id, ksuxsins, ksuxssid, ksuxshst, ksuxsver, ksuxstim, decode (ksuxssts, 0, 'started', 1, 'mounted', 2, 'open', 3, 'Open migrate', 'unknon'), decode (ksuxsshr, 0, 'No', 1, 'yes', 2, NULL), ksuxsthr, decode (ksuxsarc, 0, 'st OPPED ', 1, 'started', 'failed'), decode (ksu1_sw, 0, NULL, 2, 'archivelog', 3, 'clearlog', 4, 'checkpoint', 5, 'redo generation'), decode (ksuxsdba, 0, 'allowed', 'restricted'), decode (ksuxsshp, 0, 'No ', 'Yes'), decode (kvitval, 0, 'active', 2147483647, 'suincluded', 'instance RECOVERY '), decode (ksuxsrol, 1, 'Primary _ instance ', 2, 'secondary _ instance', 'unknown '), decode (qui_state, 0, 'normal', 1, 'quiescing', 2, 'quiesced', 'unknon '), Decode (bitand (ksuxsdst, 1), 0, 'No', 1, 'yes', 'no') from x $ ksuxsinst ks, x $ kvit kv, x $ quiesce qu where kvittag = 'kcbwst' then we can know the fixed tables that V $ INSTANCE actually uses. The preceding complex queries may not be used TO solve this problem. In fact, SQL> GRANT SELECT ON v _ $ instance TO dcsopen; Grant succeeded. since the prompt "V _ $ INSTANCE" cannot be found, you can grant it the permission. SQL> select owner, table_name from user_tab_privs; OWNER TABLE_NAME -------------------------------- ---------------------------- sys v _ $ databasesys v _ $ instance SQL> desc v $ instance; Name Null? Type direction -------- ---------------------------- 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 VARCH AR2 (18) ACTIVE_STATE VARCHAR2 (9) BLOCKED VARCHAR2 (3) this problem only gives us a way to understand the true reference object of the V $ view. This process is worth summarizing and recording. Note: The content in V $ FIXED_TABLE can be viewed even if the database does not have open (mount or nomount), but a common table view may prompt: ORA-01219: database not open: queries allowed on fixed tables/views only.