A data dictionary is a repository of metadata. The data dictionary of Oracle is stored in the SYS schema.
Each Oracle database has a data dictionary, which is a set of tables and views that serve as a reference about the database.
For example, a data dictionary stores information about both the logical and physical structure of the database.
A data dictionary also stores the valid users of an Oracle database, information about integrity constraints defined for tables in the database, and the amount of space allocated for a schema object and how much of that space is in use, among much other information.
A data dictionary is created when a database is created. To accurately reflect the status of the database at all times, the data dictionary is automatically updated by Oracle Database in response to specific actions, such as when the structure of the database is altered. Database users cannot modify the data dictionary. Various database processes rely on the data dictionary to record, verify, and conduct ongoing work. For example, during database operation, Oracle Database reads the data dictionary to verify that schema objects exist and that users have proper access to them.
Initially, you can just check this two views:
| USER_TABLES | Lists each table that belongs to your Oracle user. |
| USER_TAB_COMMENTS | Shows comments on the tables and views. |
| USER_TAB_COLUMNS | Tells you the names, data types, default values, etc. of each column in each table. |
| USER_COL_COMMENTS | Shows comments on the columns. |
| USER_CONSTRAINTS | Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc. |
| USER_CONS_COLUMNS | Maps constraints to columns (since a constraint can act on one or many columns). |
| USER_INDEXES | Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys). |
| USER_IND_COLUMNS | Maps indexes to columns. |
| USER_VIEWS | Lists all views, along with the text used to originally create them. |
| USER_SYNONYMS | Lists the synonyms and original table names. |
| USER_SEQUENCES | Lists all sequences, including min value, max value, and amount by which to increment. |
| USER_TRIGGERS | Contains trigger names, criteria for activating each trigger, and the code that is run. |
| USER_SOURCE | Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies. |
select *
from all_objects
where object_type in ('TABLE','VIEW')
select name from obj$ where status = 3;
SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
select segment_name, bytes/1024/1024 "Size in MB" from user_segments where segment_name like 'CUSTOMERS_%';
select * from all_synonyms where Owner like 'PUBLIC' and SYNONYM_NAME = UPPER('user_sdo_styles')
SELECT comp_name, status, SUBSTR(version,1,10) AS version FROM dba_registry;