CODEWITHSUNDEEP
oracle-databasemacosdockerrecycle-bin
user13746660
user13746660

Reputation:

How to exclude and lookup the value of RecycleBin in Oracle

When querying constraints in Oracle 11g, execute the following query.

SELECT 
                CC.OWNER as "owner"
            ,   CC.TABLE_NAME as "tableName"
            ,   CC.COLUMN_NAME as "columnName"
            ,   CC.CONSTRAINT_NAME as "constraintName"
            ,   CC.POSITION as "position"
    FROM ALL_CONS_COLUMNS CC 
    WHERE CC.OWNER IN ('<schemaName>')
    ORDER BY CC.POSITION ASC;

However, the data in recyclebin is also searched as shown below. (For reference, the owner in the figure below is the schema name Docker.)

enter image description here

I'm running on Mac OS Docker. Does it appear because it is a Docker environment?

I don't need this data at all, so I want to check it out.

Any way other than clearing the data in recyclebin with purge recyclebin command?

Upvotes: 0

Views: 306

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21095

You may use the fact, that ALL_TABLES does not return tables that were dropped, so a simple inner join on OWNER, TABLE_NAME will do the elimination:

SELECT 
                CC.OWNER as "owner"
            ,   CC.TABLE_NAME as "tableName"
            ,   CC.COLUMN_NAME as "columnName"
            ,   CC.CONSTRAINT_NAME as "constraintName"
            ,   CC.POSITION as "position"
    FROM ALL_CONS_COLUMNS CC 
    JOIN ALL_TABLES T on T.OWNER = CC.OWNER and T.TABLE_NAME = CC.TABLE_NAME /* Added line */
    WHERE CC.OWNER IN ('REPORTER')
    ORDER BY CC.POSITION ASC;

Upvotes: 2

Related Questions