The
initial list of unused PL/SQL code objects (Packages/Procedures/Functions) can
be identified based on the strategy discussed in Identification section above. Also the unused application
components (Workflows, Forms and Reports) removed above will give us additional
list of PL/SQL code objects that can potentially be removed.
We
then need to verify if the PL/SQL code object is not used in any workflow
activity, view definition, any other package, oracle form, oracle report or
concurrent program executable.
Check
if the PL/SQL code object is used in any workflow activity.
SELECT
*
FROM wf_activities
WHERE UPPER (function) LIKE UPPER('%<PACKAGE_NAME>%')
AND end_date IS NULL
ORDER BY version
|
Check
if the PL/SQL code object is used in any views, packages, procedures, functions
etc.
SELECT
ad.name, -- Referencing
Object
ad.type, -- Type of referencing object
ad.*
FROM all_dependencies ad
WHERE referenced_name LIKE UPPER('%<PACKAGE_NAME>%')
|
Check
if the PL/SQL code object is used in any concurrent executable
SELECT
*
FROM fnd_executables
WHERE UPPER (execution_file_name) LIKE UPPER('%<PACKAGE_NAME>%')
AND execution_method_code = 'I' -- ‘I’ for Oracle PLSQL Code
|
Check
if the PL/SQL code object is used in any form
Go
to $AU_TOP/forms/US
grep
–ir <PACKAGE_NAME> *.fmb
|
Check
if the PL/SQL code object is used in any report
Go
to $CUSTOM_TOP/reports/US
grep
–ir <PACKAGE_NAME> *.rdf
|
The
package, procedure or function can be removed using the following SQL command.
DROP
PACKAGE <PACKAGE_NAME>
DROP
PROCEDURE <PROCEDURE_NAME>
DROP
FUNCTION <FUNCTION_NAME>
|
The
initial list of unused database views can be identified based on the strategy
discussed in Identification section above. Also the unused application
components removed will give us additional list of views that can potentially
be removed.
We
then need to verify if the views are not used in any code such as package/procedure/function,
oracle form, oracle report, table type value set and flex fields.
Check
if the view is used in any other view, package, procedure, function etc.
SELECT
ad.name, --Referencing
Object
ad.type, -- Type of referencing object
ad.*
FROM all_dependencies ad
WHERE referenced_name LIKE UPPER('%<VIEW_NAME>%')
|
Check
if the view is used in any value set definition
SELECT
*
FROM fnd_flex_validation_tables
WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')
|
Check
if the view is used in any descriptive flex field definition
SELECT
*
FROM fnd_descriptive_flexs_vl
WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')
|
Check
if the view is used in any key flex field definition
SELECT
*
FROM fnd_id_flexs
WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')
|
Check
if the view is used in any form
Go
to $AU_TOP/forms/US
grep
–ir <VIEW_NAME> *.fmb
|
Check
if the view is used in any report
Go
to $CUSTOM_TOP/forms/US
grep
–ir <VIEW_NAME> *.rdf
|
The
view can be removed using the following SQL command.
The
initial list of unused database tables can be identified based on the strategy
discussed in Identification section above. Also the unused application
components removed will give us additional list of tables that can potentially
be removed.
We
then need to verify if the tables are not used in any code such as view
definition, package/procedure/function, oracle form, oracle report, table type
value set and flex field.
Check
if the table is used in any views, package, procedure, function etc.
SELECT
name,-- Referencing Object
type -- Type of referencing object
FROM all_dependencies
WHERE referenced_name LIKE UPPER('%<TABLE_NAME>%')
|
Check
if the table is used in any value set definition
SELECT
*
FROM fnd_flex_validation_tables
WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')
|
Check
if the table is used in any descriptive flex field definition
SELECT
*
FROM fnd_descriptive_flexs_vl
WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')
|
Check
if the table is used in any key flex field definition
SELECT
*
FROM fnd_id_flexs
WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')
|
Check
if the table is used in any form
Go
to $AU_TOP/forms/US
grep
–ir <TABLE_NAME> *.fmb
|
Check
if the table is used in any report
Go
to $CUSTOM_TOP/forms/US
grep
–ir <TABLE_NAME> *.rdf
|
Check the latest time a DML Commit
operation was performed on a table. If the table stores transactional data,
this query will give you a good indication of whether the table is used or not.
Note: The following query will not consider
SELECTs performed on the table.
SELECT
SCN_TO_TIMESTAMP(MAX(ora_rowscn))
FROM <TABLE_NAME>
|
The
table can be removed using the following SQL command. It is also a suggested to
take a temporary backup of the table data before dropping the table.
CREATE
TABLE <TABLE_NAME_BKUP>
AS (SELECT * FROM <TABLE_NAME>);
DROP
TABLE <TABLE_NAME>;
|
