How To Refresh Data Dictionary in Oracle?

Question

i deleted some of my tables in oracle

DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;

but the primary key constraint of deleted table is still in data dictionary (all_constraints). now my question is how to update data dictionary so it does not show primary key of deleted tables? for example i used this query to select the hr schema:

SELECT A.TABLE_NAME,A.COLUMN_NAME,A.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS B WHERE B.OWNER='HR' AND A.OWNER='HR'AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.constraint_type='P';

and the result is:

REGIONS                        REGION_ID
COUNTRIES                      COUNTRY_ID
LOCATIONS                      LOCATION_ID
DEPARTMENTS                    DEPARTMENT_ID
JOBS                           JOB_ID
EMPLOYEES                      EMPLOYEE_ID
JOB_HISTORY                    EMPLOYEE_ID
JOB_HISTORY                    START_DATE
BIN$rRfAMUTnQROvBfuhBCT6RQ==$0 ID
BIN$87Rd5FE7Rj2eQbim0H2EzA==$0 PK_B
BIN$bQeeD0M/QMSpLzYjZqAGeA==$0 PK_A
BIN$RzMu6nZiQ2yuj+4xQf7eAQ==$0 PK_A

rows with weird table name are the ones the i had deleted.

Answer 1

Your dropped table is in the recycle bin.

Your original constraint names aren't visible - you can't see the primary key name any more (in that view; you can retrieve it from user_recyclebin) - but the constraint is still available internally so it can be restored quickly if you restore the table with flashback table hr.admin_emp to before drop.

The objects that appear in the data dictionary view as BIN$... will only disappear when you purge the recycle bin. But once you do that, they are gone for ever, and you cannot restore them without restoring the whole database, so only do that when you're really sure you don't want them any more.

You cannot, should not, and must not attempt to manually update any data dictionary object, or anything else that Oracle manages internally.

Answer 2

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide):

PURGE DBA_RECYCLEBIN;

Tables can also be droped without sending them to the recyclebin. Example:

DROP TABLE t1 PURGE;