Querying tables listed in DBA_Tables

Question

A third party product we have at my company uses Oracle as a backend. I'm attempting to log into the Oracle database and look at the schema and data. I've logged in as sys/sysdba, created a user with a default tablespace of that created by the application, and granted the user all necessary permissions to query the structures. I've also set O7_DICTIONARY_ACCESSIBILITY to true to allow querying of the data dictionary objects.

After logging in as the user and querying User_Tables nothing is returned. But when I query DBA_Tables the tables I'd expect to find are returned. I'm new to Oracle so I'm not quite certain how a non-system table can be in the tablespace, but not a user_table.

More importantly, how do you query the data in these tables? Whenever I attempt a simple "Select *" from the tables I get a "table or view does not exist" error.

Thanks in advance.

Answer 1

The default tablespace you set for a user controls what tablespace objects owned by that user are created in. It has nothing to do with what objects they can query.

• USER_TABLES returns information about the tables that a particular user owns. It does not sound like your user owns any tables, so you would expect that to be empty.
• ALL_TABLES returns information about the tables that a particular user has access to. If you granted the appropriate privileges, your user should see the tables in this data dictionary view.
• DBA_TABLES returns information about every table in the database even if you don't necessarily have access to the underlying table.

If you are trying to query data from one of the tables, are you specifying the schema name (the OWNER column in ALL_TABLES)? If you do not own an object, you generally need to use fully qualified names to reference it, i.e.

SELECT *
  FROM schema_owner.table_name

You can avoid using fully qualified names if

• You create a synonym (public or private) for the object

• You change the CURRENT_SCHEMA for the session. This changes the default schema that a name is resolved under. It does not affect permissions and privileges. You can change the current schema with the command

  ALTER SESSION SET current_schema = new_schema_name

  You would have to do this for each session the user creates-- potentially in a login trigger.