Oracle Sql Queries to view dependencies and partitioning.

This is the list of queries to find out the dependencies between databse objects and to find out the partitioning details of a table.


1:

select * from dict

Description: It will give you the list of all the system tables that are available with the comments on how a particular table can help you.

2 :

SELECT *  FROM All_Dependencies WHERE owner = 'GLOS_DB' AND NAME LIKE '%XYZ%'

Description :  It describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.This view does not display the SCHEMAID column.

3:

SELECT * from all_source
where TYPE IN('PROCEDURE','PACKAGE','FUNCTION',)
AND TEXT LIKE '%XYZ%'

Description : It will give you the list of all those database objects which you have mentioned in the TYPE IN clause and where your table is being referred . There are total  7 types which are as follows :
1 FUNCTION
2 JAVA SOURCE
3 PACKAGE
4 PACKAGE BODY
5 PROCEDURE
6 TRIGGER
7 TYPE

4:

select * from all_mviews where Mview_name = 'schema name.MV_NAME'

Description: This will give you all the information related to your mentioned Materialized view like its last refresh_date, compile state etc.

5:

SELECT * FROM DBA_VIEWS WHERE TEXT LIKE '%XYZ%'

Description : It will give you the details of the views that are accessible to a DBA. Details such as its owner name, its type etc.



6:

select * from All_Views where Text like '%XYZ%'  AND TYPE LIKE 'VIEW'

Description : It will give you the details of the views that are accessible to users. Details such as its owner name, its type etc.

7:

select* from  DBA_DEPENDENCIES

Description : DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.
For example :

SELECT type, name FROM SYS.DBA_DEPENDENCIES WHERE referenced_type   = 'TABLE' AND referenced_owner = 'GLOS_DB' AND referenced_name = 'GETS_GS_FINANCIAL_OASIS'AND type !=   'UNDEFINED'

8:

Select * from all_objects where Object_name like'%XYZ%'

Description : This will give you the information of all the objects that are accessible to the users.

9:

Select * from all_errors

Description : It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) accessible to the current user. It gives you the information like name of the object, type of the object, line in which error has came, position in the line where error occurred and text of the error.

10:

select TABLE_NAME from user_tab_cols where COLUMN_NAME like ' %XYZ%'

Description: This will give you the list of all the tables where the mentioned column is used.


Partitioning Related Queries :

1:

select * from all_part_tables

Description : It will list out all the tables that have been partitioned. It will give the information such as table name, partition type, sub partition type etc.

2:

select * from ALL_TAB_PARTITIONS where table_name like '˜XYZ'

Description : It will list out all the partitions and details of the partitions like partition name, sub partition count etc existing for  a table

3:

select * from All_Tab_Subpartitions where table_name like 'XYZ'

Description : It describes, for each table subpartition accessible to the current user, the subpartition name, name of the table and partition to which it belongs, and its storage attributes.

4:

select * from all_subpartition_templates

Description : It describes the subpartition templates accessible to the current user.