ORACLE DB user_constraints without tables

Question

I have an Oracle schema with no tables (I dropped them). When I look at the user_constraints table, however

    SELECT * FROM user_constraints;

I see a number of rows. How is it possible to delete these constraints?

    DELETE FROM user_constraints WHERE owner='owner';

did not work, it gave me an "insufficient privileges" error.

    SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name  FROM user_constraints;

    OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_OWNER                        R_CONSTRAINT_NAME            
    ORA_AI_1_9                     BIN$z2XwnFUHEHrgQ4sGOR4Qeg==$0 P               BIN$z2XwnFUJEHrgQ4sGOR4Qeg==$0                                                               

    ORA_AI_1_9                     BIN$z1NhC6g4oErgQ4sGOR6gSg==$0 P               BIN$z1NhC6g6oErgQ4sGOR6gSg==$0                                                               

    ORA_AI_1_9                     BIN$z1anK5OEEHrgQ4sGOR4Qeg==$0 P               BIN$z1anK5OGEHrgQ4sGOR4Qeg==$0                                                               

    ORA_AI_1_9                     BIN$z1NhC6hhoErgQ4sGOR6gSg==$0 P               BIN$z1NhC6hjoErgQ4sGOR6gSg==$0

When you say that you deleted them, do you really mean that you dropped the tables? Or did you merely delete the data? Are there rows in `user_tables`? — Justin Cave, Nov 27 '12 at 16:10

score 3 · Accepted Answer · answered Nov 27 '12 at 16:44

so are you on 10/11g and have the recylebin perhaps?

SQL> select count(*) from user_tables;

  COUNT(*)
----------
         0

SQL> select count(*) from user_constraints;

  COUNT(*)
----------
         0

SQL> create table foo(id number primary key);

Table created.

SQL> ALTER SESSION SET recyclebin = ON;

Session altered.

SQL> select count(*) from user_tables;

  COUNT(*)
----------
         1

SQL> select count(*) from user_constraints;

  COUNT(*)
----------
         1

SQL> drop table foo;

Table dropped.

SQL> select count(*) from user_constraints;

  COUNT(*)
----------
         1

SQL> select count(*) from user_tables;

  COUNT(*)
----------
         0

SQL>

you can purge the reclebin to fix this.

SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from user_constraints;

  COUNT(*)
----------
         0

score 1 · Answer 2 · answered Nov 27 '12 at 16:31

1

you can give statement like DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

answered Nov 27 '12 at 16:31

Ajith Sasidharan

1,155
7
7

i droped the latest tables like this. before I used the gui of SQL DEVELOPER. right now I have no tables. – user1069968 Nov 27 '12 at 16:41

score 0 · Answer 3 · answered Nov 27 '12 at 16:33

0

First, you should never do DML directly against a data dictionary table like user_constraints.

Second, do you own constraints that are created on tables owned by other users? What does

SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
  FROM user_constraints

return?

answered Nov 27 '12 at 16:33

Justin Cave

227,342
24
367
384

A table with ~300 rows. im the only user. its an account from our school for every student. – user1069968 Nov 27 '12 at 16:37
@user1069968 - So there are no other schemas in this database? That seems highly unlikely in a school setting-- it would be very, very unusual to give every student a separate database instance. Can you edit your question to post the actual results of this query (feel free to post just the first few rows of data). – Justin Cave Nov 27 '12 at 16:41

ORACLE DB user_constraints without tables

3 Answers3

Linked