Search This Blog

Saturday, September 3, 2011

How to find the dependencies of table

Today i come across one OTN thread and learn about dbms_utility. dependency plsql package.
with this package we can check the objects dependency on another objects.

 SQL} create table TEST
  2  (col varchar2(10));

Table created.

SQL} create or replace procedure PR_TEST is
  2  i number;
  3  begin
  4  select count(*) into i from test;
  5  dbms_output.put_line(to_char(i));
  6  end;
  7  /

Procedure created.

SQL} set serveroutput on

SQL} exec dbms_utility.get_dependency('TABLE','TAJ','TEST');

PL/SQL procedure successfully completed.

There are other subprogram are listed below of dbms_utility plsql package.

Table 141-3 DBMS_UTILITY Package Subprograms
ACTIVE_INSTANCES ProcedureReturns the active instance
ANALYZE_DATABASE ProcedureAnalyzes all the tables, clusters and indexes in a database
ANALYZE_PART_OBJECT ProcedureAnalyzes the given tables and indexes
ANALYZE_SCHEMA ProcedureAnalyzes all the tables, clusters and indexes in a schema
CANONICALIZE ProcedureCanonicalizes a given string
COMMA_TO_TABLE ProceduresConverts a comma-delimited list of names into a PL/SQL table of names
COMPILE_SCHEMA ProcedureCompiles all procedures, functions, packages, views and triggers in the specified schema
CREATE_ALTER_TYPE_ERROR_TABLE ProcedureCreates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement
CURRENT_INSTANCE FunctionReturns the current connected instance number
DATA_BLOCK_ADDRESS_BLOCK FunctionGets the block number part of a data block address
DATA_BLOCK_ADDRESS_FILE FunctionGets the file number part of a data block address
DB_VERSION ProcedureReturns version information for the database
EXEC_DDL_STATEMENT ProcedureExecutes the DDL statement in parse_string
FORMAT_CALL_STACK FunctionFormats the current call stack
FORMAT_ERROR_BACKTRACE FunctionFormats the backtrace from the point of the current error to the exception handler where the error has been caught
FORMAT_ERROR_STACK FunctionFormats the current error stack
GET_CPU_TIME FunctionReturns the current CPU time in 100th's of a second
GET_DEPENDENCY ProcedureShows the dependencies on the object passed in.
GET_HASH_VALUE FunctionComputes a hash value for the given string
GET_PARAMETER_VALUE FunctionGets the value of specified init.ora parameter
GET_TIME FunctionFinds out the current time in 100th's of a second
INVALIDATE ProcedureInvalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings
IS_CLUSTER_DATABASE FunctionFinds out if this database is running in cluster database mode
MAKE_DATA_BLOCK_ADDRESS FunctionCreates a data block address given a file number and a block number
NAME_RESOLVE ProcedureResolves the given name
NAME_TOKENIZE ProcedureCalls the parser to parse the given name
OLD_CURRENT_SCHEMA FunctionReturns the session value that was returned by SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
OLD_CURRENT_USER FunctionReturns the session value that was returned by SYS_CONTEXT ('USERENV', 'CURRENT_USER')
PORT_STRING FunctionReturns a string that uniquely identifies the version of Oracle and the operating system
SQLID_TO_SQLHASH FunctionConverts a SQL ID into a hash value
TABLE_TO_COMMA ProceduresConverts a PL/SQL table of names into a comma-delimited list of names
VALIDATE ProcedureMakes invalid database objects valid

find more info : dbms_utility


Anuradha said...

hai taj,

thanks for the tip.


Mohammed Taj said...

Thanks Anuradha for kind comments.