8

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.

jalal rasooly
  • 705
  • 3
  • 7
  • 24
  • 4
    "*but the primary key constraint of deleted table is still in data dictionary*" - I don't believe that. Is it a constraint for the same user? Can you show the output of your SELECT statement that you think proves this point? –  Jan 20 '14 at 09:36
  • 1
    You **do not ** ever remove data from data dictionary using a DML statement. These are views, that are built on the sys views and tables and the data in them is refreshed to reflect the current state of the database. – vishad Jan 20 '14 at 09:45
  • i didn't remove data from data dictionary, i dropped some of my tables but data dictionary still show the constraint of the dropped table! – jalal rasooly Jan 20 '14 at 09:52

2 Answers2

8

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
4

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;
SuRu
  • 739
  • 1
  • 6
  • 19