A month ago I talked about “Extending the Oracle Data Dictionary for Fine-Grained PL/SQL and SQL Analysis” during the ODTUG Kscope13 conference in New Orleans. Oracle data dictionary views as DBA_IDENTIFIERS or DBA_DEPENDENCIES are in many cases sufficient to analyze static PL/SQL and SQL code within the Oracle database. But what if more detailed analyses are required, such as the use of tables or columns in PL/SQL package units, in SQL statements or in SQL statement clauses? Wouldn’t a DBA_OBJECT_USAGE view – showing DML and query operations on tables/views per database object – be a helpful tool?
TVDCA – the Trivadis PL/SQL and SQL CodeAnalyzer – is such a tool and helps you to overcome several analysis restrictions in an Oracle 10g, 11g or 12c database. At Kscope13 some of my attentive session attendees got a USB stick with TVDCA 0.4.1 Beta. In the meantime, I was busy fixing bugs to proudly present you an updated trial/preview version free of charge in the download section of this blog.
The following query might give you an idea of the functionality of tvdca:
SQL> SELECT object_name, procedure_name, operation, table_name, column_name
2 FROM tvd_object_col_usage_v
3 WHERE owner = 'TVDCA'
4 AND object_type = 'PACKAGE BODY';
OBJECT_NAME PROCEDURE_NAME OPERATION TABLE_NAME COLUMN_NAME
-------------- -------------- --------- -------------------- ----------------
TVD_COLDEP_PKG GET_DEP SELECT DBA_DEPENDENCIES NAME
TVD_COLDEP_PKG GET_DEP SELECT DBA_DEPENDENCIES OWNER
TVD_COLDEP_PKG GET_DEP SELECT DBA_DEPENDENCIES REFERENCED_NAME
TVD_COLDEP_PKG GET_DEP SELECT DBA_DEPENDENCIES REFERENCED_OWNER
TVD_COLDEP_PKG GET_DEP SELECT TVD_PARSED_OBJECTS_V OBJECT_NAME
TVD_COLDEP_PKG GET_DEP SELECT TVD_PARSED_OBJECTS_V OBJECT_TYPE
TVD_COLDEP_PKG GET_DEP SELECT TVD_PARSED_OBJECTS_V OWNER
TVD_COLDEP_PKG PROCESS_VIEW SELECT DBA_TAB_COLUMNS COLUMN_ID
TVD_COLDEP_PKG PROCESS_VIEW SELECT DBA_TAB_COLUMNS OWNER
TVD_COLDEP_PKG PROCESS_VIEW SELECT DBA_TAB_COLUMNS TABLE_NAMEPlease have a look at my slides or the information in the download section if you are interested in learning more about tvdca.