Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: Proper drop and delete of tablespace

    I'm trying to drop and delete a tablespace, including all that is in it. I'm running OracleXE locally. After deleting the tablespace and databasefiles, I tried to logon as a system user, but Oracle gave me a ORA-01033 instead.

    To drop the tablespace, I logged on as system and executed:
    DROP TABLESPACE [name] INCLUDING CONTENTS;

    A search on the web gave to a page that suggested to:
    - logon as sysdba
    - issue shutdown
    - issue startup
    - (possibly followed by a recover)

    I did the recover but all I got are other ORA-error messages:
    ORA-00283, ORA-01110, ORA-01157, ORA-01110: basically they are all telling me that I'm missing files and that it's not working.

    So the next time, I guess I'll issue a DROP ... AND DATAFILES;

    My question is: how do I prevent this from reoccuring and how do I fix this?

    thanx

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I may have solved the issue by following this weblogs solution: http://mennan.kagitkalem.com/Solutio...dORA01110.aspx

    Still.. I'd like to know how to properly drop an entire tablespace w/o this kind of hassle. Does: DROP TABLESPACE [name] INCLUDING CONTENTS AND DATAFILES; suffice?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how do I prevent this from reoccuring
    Stop issuing DROP TABLESPACE command; when apparently you are not qualified to realize the consequences.

    >how do I fix this?
    Most likely, by doing a complete re-installation.

    What are the last 200 lines in the alert_SID.log file?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What tablespace are you trying to remove. Unless you created a new tablespace of your own, EVERY tablespace in XE is needed for the database to run correctly.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    It's not a system tablespace but one which is created for an application. I had it created by running the affiliated database scripts that also create the tables, indices, etc. I made a mistake to overlook the creation of other tablespaces including a temporary one. I knew something smelly was going on when I looked at the scripts and thought it safer to drop the whole lot instead of truncating all tables I could find. When I deleted the directory the ts files were in, I assumed I had dropped all.

    So the error wasn't so much on the drop of the tablespace, but of the missing of other tablespaces I had removed manually. I fixed that by following the link I posted earlier.

    By including the "AND DATAFILES" I'm pretty sure I have all ends covered (except for the users) so I can create and drop any time of day.

    btw: I haven't found a alert_SID.log, where could it be?

Posting Permissions

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