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 etc3. Query to find all view in a database
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
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.
SELECT *
FROM sys.all_synonyms
WHERE table_name LIKE '%TABLE1%'
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.
FROM sys.all_procedures
WHERE object_type = 'FUNCTION'
,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_idThe above query will fecth the objects that are locked.
