Error "ORA-00942: Table or View does not exist" when opening tables under another schema in DB Navigator
説明
Use F12 to bring up DB Navigator. When clicking on a table owned by another user, Error "ORA-00942: Table or View does not exist" displays in the Output window. This issue does not occur in version 6.6
原因
Without DBA Views checked, SQL Navigator uses USER_% or ALL_% views for other user's objects.
With DBA Views, SQL Navigator uses SYS.DBA_% views for other user's objects.
The defect relates specifically to limited privileges users only. SQL Nav is querying SYS.DBA_TABLESPACES instead of SYS.USER_TABLESPACES
対策
WORKAROUND
1. Have DBA grant permission to DBA_TABLESPACES or
2. Revert to version 6.6
STATUS
Waiting for fix in a future release of SQL Navigator for Oracle
Defect ID
SQLNAV-1885
追加情報
DBA Dictionary Views
By default, SQL Navigator gives you USER object data dictionary views, meaning you can see only objects you own or for which you have been granted object privileges.
When you Enable DBA views in SQL Navigator you can...
Edit Profiles, Roles, and Users.
View the following nodes of the DB Navigator tree:
•Roles
•Some nodes under Users
• Datafiles under Tablespaces
•Redo Log Groups
•Rollback Segments
•Partitioned tables in another user’s schema
How to enable DBA views in SQL Navigator: - Oracle Logon Dialog | Enable DBA Views
Oracle requirements to query DBA views:
Your username must have one of the following roles or privileges:
•DBA Role
•SELECT_CATALOG_ROLE Role
•SELECT ANY TABLE Privilege
DBA role and SELECT_CATALOG_ROLE role must be defined as the default roles in Oracle.
How SQL Navigator handles the views
When DBA dictionary views are selected, SQL Navigator interrogates the data dictionary differently:
Regardless of DBA View setting, SQL Navigator uses USER_% views for the logged-on user's objects.
Without DBA Views, SQL Navigator uses ALL_% views for other user's objects.
With DBA Views, SQL Navigator uses SYS.DBA_% views for other user's objects.
