drop table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: drop table

  1. #1
    Join Date
    Jun 2003
    Posts
    108

    drop table

    Hi BBAs

    I had a lot of temp tables in my major user.
    When I droped all temp tables from the user,only one table has not droped.

    Error says: table or view does not exist.

    But I could see the table in my user and dba_usrs table.

    What could be a problem.. please help me.

    Thanx in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    RTM
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2003
    Posts
    108
    What is RTM.

    Can you explain this.Please..............

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    read the manual, sometimes referred to as read the flipping manual :-)

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Re: drop table

    Originally posted by sri_dba
    Hi BBAs

    I had a lot of temp tables in my major user.
    When I droped all temp tables from the user,only one table has not droped.

    Error says: table or view does not exist.

    But I could see the table in my user and dba_usrs table.

    What could be a problem.. please help me.

    Thanx in advance.
    Thank gawd i am not a BBA :-)

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: drop table

    Originally posted by hrishy
    Thank gawd i am not a BBA :-)

    Hrishy :

    I guess you are in one of your phase?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2001
    Posts
    191
    Get the table name from dba_tables, then

    drop table "table name";

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by xyz2000
    Get the table name from dba_tables, then

    drop table "table name";
    Be careful with this!!

    In my environments there are duplicate table names under different schemas. If you fail to specify the OWNER in the drop statement, you will drop whichever table has the public synonym, sometimes this can be (Elmer Fudd voice) vewy vewy bad, heh heh heh.

    Always use the OWNER.TABLENAME just to be safe.

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Mr.Hanky
    If you fail to specify the OWNER in the drop statement, you will drop whichever table has the public synonym, sometimes this can be (Elmer Fudd voice) vewy vewy bad, heh heh heh.

    MH
    Absolute Crap...DDL always needs Owner.Object_Name, if not then it will do DDL on schema logged in and not using the public synonym which might be pointing to any other schema's object.

    PS below

    Code:
    TEST:ABHAY> ed
    Wrote file afiedt.buf
    
      1* create table x(key number)
    TEST:ABHAY> /
    
    Table created.
    
    TEST:ABHAY> create public synonym x for x;
    
    Synonym created.
    
    TEST:ABHAY> grant create table to test;
    
    Grant succeeded.
    
    TEST:ABHAY> conn test/test
    Connected.
    TEST:ABHAY> drop table x;
    drop table x
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    TEST:ABHAY> create table x(key number);
    
    Table created.
    
    TEST:ABHAY> drop table x;
    
    Table dropped.
    
    TEST:ABHAY> desc x
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- --------
     KEY                                                            NUMBER
    
    TEST:ABHAY> desc abhay.x
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- --------
     KEY                                                            NUMBER
    Did it drop using public synonym???

    Abhay.
    Last edited by abhaysk; 12-09-2003 at 04:12 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131
    YOU ARE ABSOLUETLY RIGHT!!

    My bad, I don't know what I was thinking.
    It is still a good practice to specify the owner,table_name though.

    MH
    Last edited by Mr.Hanky; 12-09-2003 at 09:33 AM.
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width