Oracle Metadata

Metadata is nothing but data about your data. It is the data regarding DB objects created by you in your database. You can query the meta data tables too to locate the desired information like search for a column in all tables, or look for a code in all procedures, functions, packages & triggers, or generate some DML or DDL statements using info available in meta data tables or views.

Following are some of the meta data tables, whose knowledge can be helpful in your work.

#	1	2
Meta Data Table Name	DBA_ALL_TABLES	DBA_COL_COMMENTS
	ALL_ALL_TABLES	ALL_COL_COMMENTS
	USER_ALL_TABLES	USER_COL_COMMENTS
Purpose	Stores details of all relational tables in the database / user.	Stores details of comments on the columns of all tables and views in the database / user.
#	3	4
Meta Data Table Name	DBA_COLL_TYPES	DBA_CONS_COLUMNS
	ALL_COLL_TYPES	ALL_CONS_COLUMNS
	USER_COLL_TYPES	USER_CONS_COLUMNS
Purpose	Stores details of all named collection types (arrays, nested tables, object tables, and so on) in the database / user.	Stores details of all columns in the database / user that are specified in constraints.
#	5	6
Meta Data Table Name	DBA_CONSTRAINTS	DBA_DATA_FILES
	ALL_CONSTRAINTS	–
	USER_CONSTRAINTS	–
Purpose	Stores details of all constraint definitions on all tables in the database / user.	Stores details of database files.
#	7	8
Meta Data Table Name	DBA_DB_LINKS	DBA_DDL_LOCKS
	ALL_DB_LINKS	–
	USER_DB_LINKS	–
Purpose	Stores details of all database links in the database / user.	Stores details of all DDL locks held in the database and all outstanding requests for a DDL lock.
#	9	10
Meta Data Table Name	DBA_DML_LOCKS	DBA_DEPENDENCIES
	–	ALL_DEPENDENCIES
	–	USER_DEPENDENCIES
Purpose	Stores details of all DML locks held in the database and all outstanding requests for a DML lock.	Stores details of all dependencies in the database / user between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.
#	11	12
Meta Data Table Name	DBA_DIRECTORIES	DBA_EXTERNAL_TABLES
	ALL_DIRECTORIES	ALL_EXTERNAL_TABLES
	–	USER_EXTERNAL_TABLES
Purpose	Stores details of all directory objects in the database / user.	Stores details of all external tables in the database / user.
#	13	14
Meta Data Table Name	DBA_FREE_SPACE	DBA_INDEXES
	–	ALL_INDEXES
	USER_FREE_SPACE	USER_INDEXES
Purpose	Stores details of the free extents in all tablespaces in the database / user.	Stores details of all indexes in the database / user. To gather statistics for this view, use the DBMS_STATS package. This view supports parallel partitioned index scans.
#	15	16
Meta Data Table Name	DBA_INVALID_OBJECTS	DBA_JOBS
	–	ALL_JOBS
	–	USER_JOBS
Purpose	Stores details of all invalid objects in the database / user.	Stores details of all jobs in the database / user.
#	17	18
Meta Data Table Name	DBA_JOBS_RUNNING	DBA_LOBS
	–	ALL_LOBS
	–	USER_LOBS
Purpose	Stores details of all jobs that are currently running in the instance.	Stores details of the BLOBs and CLOBs contained in all tables in the database / user.
#	19	20
Meta Data Table Name	DBA_MVIEWS	DBA_NESTED_TABLE_COLS
	ALL_MVIEWS	ALL_NESTED_TABLE_COLS
	USER_MVIEWS	USER_NESTED_TABLE_COLS
Purpose	Stores details of all materialized views in the database / user.	Stores details of the columns of all nested tables in the database / user.
#	21	22
Meta Data Table Name	DBA_NESTED_TABLES	DBA_OBJECTS
	ALL_NESTED_TABLES	ALL_OBJECTS
	USER_NESTED_TABLES	USER_OBJECTS
Purpose	Stores details of all nested tables contained in all tables in the database / user.	Stores details of all objects in the database / user.
#	23	24
Meta Data Table Name	DBA_PARALLEL_EXECUTE_CHUNKS	DBA_PARALLEL_EXECUTE_TASKS
	–	–
	USER_PARALLEL_EXECUTE_CHUNKS	USER_PARALLEL_EXECUTE_TASKS
Purpose	Stores details of the chunks for all tasks in the database / user.	Stores details of all tasks in the database / user.
#	25	26
Meta Data Table Name	DBA_RECYCLEBIN	DBA_ROLE_PRIVS
	–	–
	USER_RECYCLEBIN	USER_ROLE_PRIVS
Purpose	Stores details of information about all recycle bins in the database / user.	Stores details of the roles granted to all users and roles in the database / user.
#	27	28
Meta Data Table Name	DBA_ROLES	DBA_SCHEDULER_JOB_LOG
	–	ALL_SCHEDULER_JOB_LOG
	–	USER_SCHEDULER_JOB_LOG
Purpose	Stores details of all roles in the database / user.	Stores details of log information for all Scheduler jobs in the database / user.
#	29	30
Meta Data Table Name	DBA_SCHEDULER_JOB_RUN_DETAILS	DBA_SCHEDULER_JOBS
	ALL_SCHEDULER_JOB_RUN_DETAILS	ALL_SCHEDULER_JOBS
	USER_SCHEDULER_JOB_RUN_DETAILS	USER_SCHEDULER_JOBS
Purpose	Stores details of log run details for all Scheduler jobs in the database / user.	Stores details of information about all Scheduler jobs in the database / user.
#	31	32
Meta Data Table Name	DBA_SCHEDULER_RUNNING_JOBS	DBA_SCHEDULER_SCHEDULES
	ALL_SCHEDULER_RUNNING_JOBS	ALL_SCHEDULER_SCHEDULES
	USER_SCHEDULER_RUNNING_JOBS	USER_SCHEDULER_SCHEDULES
Purpose	Stores details of information about all running Scheduler jobs in the database / user.	Stores details of information about all Scheduler schedules in the database / user.
#	33	34
Meta Data Table Name	DBA_SOURCE	DBA_SYNONYMS
	ALL_SOURCE	ALL_SYNONYMS
	USER_SOURCE	USER_SYNONYMS
Purpose	Stores details of the text source of all stored objects in the database / user.	Stores details of all synonyms in the database / user.
#	35	36
Meta Data Table Name	DBA_SYS_PRIVS	DBA_TAB_COLS
	–	ALL_TAB_COLS
	USER_SYS_PRIVS	USER_TAB_COLS
Purpose	Stores details of system privileges granted to (current) users and roles.	Stores details of the columns of all tables, views, and clusters in the database / user.
#	37	38
Meta Data Table Name	DBA_TAB_COLUMNS	DBA_TAB_COMMENTS
	ALL_TAB_COLUMNS	ALL_TAB_COMMENTS
	USER_TAB_COLUMNS	USER_TAB_COMMENTS
Purpose	Stores details of the columns of all tables, views, and clusters in the database / user.	Stores details of comments on all tables and views in the database / user.
#	39	40
Meta Data Table Name	DBA_TAB_PRIVS	DBA_TABLES
	ALL_TAB_PRIVS	ALL_TABLES
	USER_TAB_PRIVS	USER_TABLES
Purpose	Stores details of all object grants in the database / user.	Stores details of all relational tables in the database / user.
#	41	42
Meta Data Table Name	DBA_TABLESPACES	DBA_TEMP_FILES
	–	–
	USER_TABLESPACES	–
Purpose	Stores details of all tablespaces in the database / user.	Stores details of
#	43	44
Meta Data Table Name	DBA_TRIGGERS	DBA_TYPES
	ALL_TRIGGERS	ALL_TYPES
	USER_TRIGGERS	USER_TYPES
Purpose	Stores details of all triggers in the database / user.	Stores details of all object types in the database / user.
#	45	46
Meta Data Table Name	DBA_USERS	DBA_VIEWS
	ALL_USERS	ALL_VIEWS
	USER_USERS	USER_VIEWS
Purpose	Stores details of all users of the database / user.	Stores details of all views in the database / user.

Following are some of the meta data views, whose knowledge can be helpful in your work.

#	Meta Data View Name	Purpose
1	V$VERSION	Lists the version number of Oracle Database
2	V$SESSION	Lists session information for each current session.
3	V$LOCK	Lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
4	V$SQL	Lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered.
5	V$DATABASE	Lists statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
6	V$INSTANCE	Lists the state of the current instance.
7	V$DATAFILE	Lists datafile information from the control file.
8	V$SQLAREA	Lists statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
9	V$ACTIVE_SESSION_HISTORY	Lists sampled session activity in the database.
10	V$SESSION_CONNECT_INFO	Lists information about network connections for all currently logged in sessions.
11	V$SES_OPTIMIZER_ENV	Lists the contents of the optimizer environment used by each session.
12	V$DEAD_CLEANUP	Lists the dead processes and killed sessions present in the instance and their cleanup
13	V$OBJECT_DEPENDENCY	Lists the objects depended on by a package, procedure, or cursor that is currently loaded in the shared pool.
14	V$HANG_INFO	Lists information about hangs found on the cluster.
15	V$HANG_SESSION_INFO	Lists information about sessions involved in hangs described by V$HANG_INFO.
16	V$SESSION_WAIT	Lists the current or last wait for each session.
17	V$OPEN_CURSOR	Lists cursors that each user session currently has opened and parsed, or cached.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.