7-44. Data Dictionary

The data dictionary can be used to query about users, objects, constraints and storage.

By querying the Oracle with SELECT * FROM dictionary WHERE table_name like 'USER_%', we get the list of views that the user owns.

For example:

To find out the name of the view used to retrieve the indexes that INVENTORY user owns, we query the SELECT * FROM dictionary WHERE table_name like 'USER_IND%'. There should be a row where the table_name is USER_INDEXES.

To list all constraints that the INVENTORY user can see, we should use the view ALL_CONSTRAINTS. We use ALL because we want to include other user's constraints and its own constraints.

To list the names of tables created in your schema: SELECT table_name, tablespace_name FROM user_tables;

To see significant information about any sequences in the database that you have access to: SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS', 'XDB');

To see which users in this database are currently able to log in: SELECT username, account_status FROM dba_users WHERE account_status = 'OPEN';

To see what information you can view about all the indexes in the database: DESCRIBE dba_indexes;