Problem in drop tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Problem in drop tablespace

  1. #1
    Join Date
    May 2009
    Location
    Muscat,Oman
    Posts
    11

    Exclamation Problem in drop tablespace

    I WANT TO KNOW IS THIS PROCESS ARE CORRECT FOR DROP A TABLESPACE


    startup open;

    alter tablespace raja offline;


    SQL> DROP TABLESPACE RAJA INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE RAJA INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist



    THIS IS THE ERROR I FOUND
    BUT ONCE I DROP ANOTHER TABLESPACE WHICH IS AUTOMATICALLY GENERATED BY THE ORACLE "SYS_UNDOTS"


    SQL> ALTER tablespace SYS_UNDOTS offline;

    Tablespace altered.

    SQL> DROP TABLESPACE SYS_UNDOTS INCLUDING CONTENTS AND DATAFILES ;

    Tablespace dropped.


    THE ABOVE IS WORKING BUT I WANT TO DROP A USER TABLESPACE

    LET ME KNOW THE REASON FOR THIS DIFFERENCE ?
    IS THERE ANY DIFF BETWEEN "SYS_" TABLESPACE AND "USER"(created by dba)

    EVEN I CREATE A NEW TABLESPACE, THAT ALSO CANT ABLE TO DROP.. SAME PROBLEM FOR THAT TOO
    Last edited by abdulhameedom; 05-05-2009 at 06:04 PM. Reason: SAME PROBLEM FOR OTHER NEW TBS

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    At first glance it looks like a privs issue; the account you are using to log into the database might not have privs on the affected table seatting on raja tablespace.

    I'm so proud of myself, I didn't ask what possesed you to drop sys_undots... oops, did I said that out loud?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2009
    Location
    Muscat,Oman
    Posts
    11

    Question System tbs

    i drop the tablespace "SYS_UNDOTS" because it have more space than the "SYSTEM Tablespace", because of that i coudnt able to create a new tablespace.

    now am facing the drop tablespace problem..

    (am really sorry for disturbing u am just a child in this DBA ocean,, i want to sail in this , so please don't mistaken me by my question)

    Quote Originally Posted by abdulhameedom View Post
    I WANT TO KNOW IS THIS PROCESS ARE CORRECT FOR DROP A TABLESPACE


    startup open;

    alter tablespace raja offline;


    SQL> DROP TABLESPACE RAJA INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE RAJA INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist



    THIS IS THE ERROR I FOUND
    BUT ONCE I DROP ANOTHER TABLESPACE WHICH IS AUTOMATICALLY GENERATED BY THE ORACLE "SYS_UNDOTS"


    SQL> ALTER tablespace SYS_UNDOTS offline;

    Tablespace altered.

    SQL> DROP TABLESPACE SYS_UNDOTS INCLUDING CONTENTS AND DATAFILES ;

    Tablespace dropped.


    THE ABOVE IS WORKING BUT I WANT TO DROP A USER TABLESPACE

    LET ME KNOW THE REASON FOR THIS DIFFERENCE ?
    IS THERE ANY DIFF BETWEEN "SYS_" TABLESPACE AND "USER"(created by dba)

    EVEN I CREATE A NEW TABLESPACE, THAT ALSO CANT ABLE TO DROP.. SAME PROBLEM FOR THAT TOO

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by abdulhameedom View Post
    i drop the tablespace "SYS_UNDOTS" because it have more space than the "SYSTEM Tablespace", because of that i coudnt able to create a new tablespace.
    Please don't take this the wrong way, you look like an adventurous person -chances are software development would be a better fit than database administration for a person with such natural predisposition.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2009
    Location
    Muscat,Oman
    Posts
    11

    Unhappy i hate development

    i am really sorry to say,, i hate the development side,, i almost lost my carrier, i want to survive, i dont want to be a looser in this world again,,
    i want to be a DBA that is the only way with me,, i already choose it as my future..

  6. #6
    Join Date
    Oct 2006
    Posts
    175
    To perform any task, you need the privilege to do it. So is for dropping tablespaces, please see if you the privilege to do so.
    Well like you said it yourself, if you want to survive as a DBA, then pleasseee don't fiddle with system objects, at least as for now. We'll look at it when you grow a bit older. :P :P

  7. #7
    Join Date
    May 2009
    Location
    Muscat,Oman
    Posts
    11

    Thank u Brother

    Thank u for ur kind help..
    if Gods wish u can see my growth..

    thank u once again...

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