What is the equivalent of IBM's SYSIBM.SYSTABLES in Oracle?

Question

Hi,

What are the equivalent of SYSIBM. in Oracle?

Answer 1

In Oracle, there are three forms of most data dictionary tables that describe different types of objects

• ALL_*
• USER_*
• DBA_*

The DBA_* tables have information about all of the specified objects in the database. So DBA_TABLES will have information about every table in the database whether or not you have access to the underlying object.

The ALL_* tables have information about all of the specified objects in the database that you have access to. So ALL_TABLES will have information about all the tables in the database that you have access to.

Finally, USER_* tables have information about all of the specified objects in the database that you own. So USER_TABLES will have information about all the tables that you own (i.e. all the tables in your schema).

By default, normal users do not have access to the DBA_* tables, just to the USER_* and ALL_* tables. If you want to query the DBA_* tables, you would need either the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role.

In addition to the DBA/ ALL/ USER_* data dictionary view, Oracle also provides V$ views for dynamic performance information (i.e. V$SESSION includes information about all the sessions in the database). For every V$ view, there is also a GV$ view that shows you information from all instances if you're on a RAC cluster.