Oracle Meta Data Queries

This post explains the queries related to Oracle Metadata tables which is useful for finding the database and tables or user information.

1. Query to find particular table in a database.

SELECT *
FROM sys.all_tables
WHERE table_name LIKE 'STG_D%

The above query will get the all tables which is starting with STG_D. This table contains num of rows for each table etc.

2. Query to find particular column in a table database.

SELECT *
FROM sys.all_tab_columns
WHERE column_name LIKE 'NAME%'
The above query will get the all columns where column name starts with NAME. This table contains information like what is the data type of the column, precision, nullable or not, default value enabled or not, what is the low value of that column and what is the highest value of the column value etc
3. Query to find all view in a database
SELECT *
FROM sys.all_views
WHERE view_name LIKE '%NAME%'
The above query will get the all views in a database. It contains text columns where we can see entire view definition.
4. Query to find all data modifications on a table
SELECT *
FROM sys.all_tab_modifications
WHERE table_name LIKE '%TABLE1%'

The above query will show how many inserts, deletes, updates and trucates happened on that table. It shows the partitions that has been truncated.

5. Query to find all synonyms for a table

SELECT *
FROM sys.all_synonyms
WHERE table_name LIKE '%TABLE1%'

The above query will show all synonyms of TABLE1. Synonyms are using when we have table name is quite lengthy or want to read the table from different schema or restrict access to read only.

6. Query to find trigger on a table

SELECT *
FROM sys.all_triggers
WHERE table_name LIKE '%TABLE1%'

The above query will show if there is any trigger on the table TABLE1.

7. Query to find procedures, functions and packages in a database.

SELECT *
FROM sys.all_procedures
WHERE object_type = 'FUNCTION'

The above query will fecth all the functions in the database. For procedure we can use 'PROCEDURE' filter for packages we can package 'PACKAGE'.
8. To find the locked objects by users we can use the below command to identify users and take action accordingly.
SELECT doj.object_name
,vlo.oracle_username
,vlo.os_user_name
,vlo.process
,vlo.locked_mode
,vs.STATUS
,vs.machine
,vs.port
,vs.terminal
,vs.program
,vs.type
,vs.module
FROM dba_objects doj
JOIN v$locked_object vlo ON doj.object_id = vlo.object_id 
JOIN v$session vs ON vs.sid = vlo.session_id
The above query will fecth the objects that are locked.