Right way to get references to an object in Oracle

oracleoracle-10g

I want to find all the objects referencing my object.

I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that separately)

Direct References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Also, on the internet, I have come across queries like the following.

Direct and Indirect References to Object

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (SELECT object_id
                           FROM public_dependency 
                           CONNECT BY PRIOR object_id = referenced_object_id
                           START WITH referenced_object_id = :objectId)

However, if my understanding is correct, this query doesn't return direct references only, but also indirect dependencies.

For example if I want the references for object_A. If object_B references it, and object_C referenced object_B. This query would return object_B and object_C as references to object_A. Am I right?

Best Answer

After talking with some people with more Experience in Oracle than me, they told me I was right in my understanding.

This query returns the references to an object (except the constraints)

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS  
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name

Related Solutions

Oracle View Ignoring External WHERE Clause

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

Delete an oracle instance, the right way

You can delete databases with DBCA which takes care of most of it.

Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually.

sqlplus / as sysdba
startup mount exclusive restrict
exit
rman target /
drop database including backups noprompt;
exit

After this, you still have to remove the entry that belongs to the database from /etc/oratab, remove init.ora/spfile, password file from $ORACLE_HOME/dbs, and clean log directories (adump, bdump, cdump, udump).

Related Question