Oracle Data Dictionary Views
Last Updated on :November 15, 2019
Oracle provides set of read only views that provides information about database.In this tutorial we will look into different types of Data Dictionary Views and how can we access data dictionary view with SQL statement.
Oracle provides 3 sets of data dictionary views. All data dictionary view starts with prefix USER_ , ALL_ or DBA_
- USER Data Dictionary View
- ALL Data Dictionary View
- DBA Data Dictionary View
USER Data Dictionary View
USER views provide all database object information in your current schema. If you login to HR schema, querying USER view shows all the objects belongs to HR schema.
You can execute below query to get all USER view details
SELECT * FROM dictionary WHERE table_name LIKE 'USER%';
Some of the commonly used USER Data dictionary view details are provided in below
| USER_TABLES | List all tables owned by current schema |
| USER_VIEWS | List all the views owned by current user |
| USER_DB_LINKS | List all the DB links owned by current schema |
| USER_SOURCE | Source code of stored objects (Package,Procedure , Function,Trigger etc) accessible to the user |
| USER_TRIGGERS | List triggers owned by current schema |
| USER_JOBS | All jobs owned by this user |
| USER_FREE_SPACE | Free extents in tablespaces accessible to the user |
| USER_TABLESPACES | List of accessible tablespaces to the user |
ALL Data Dictionary View
ALL Views provides all database object information in your current schema with all other object information current USER schema can access
If you login to HR schema, querying ALL_ view shows all the objects belongs to HR schema with all other objects details granted to HR schema
DBA Data Dictionary View
DBA_ Views list all object information in entire database. DBA or User granted to ‘SELECT ANY DICTIONARY’ privilege will be able to access all DBA_ views.
