This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 14, 2012 11:47 PM by 973949 RSS

How to shrink a tablespace in Oracle 8.0.3

973949 Newbie
Currently Being Moderated
Hi,

We run Oracle 8.0.3 in a Sun computer with Solaris 5.8.

Recently we had a severe error in our application because one of the oracle tablespaces reached the 2Gb size limit (O.S. max file size).

Now we need to shrink the tablespace and free O.S. disk space, because the application cannot write anymore in the database.

We have deleted all the records of one of the tables, which is the biggest table in the tablespace and contains log information. This table has lots of indexes.

Please, could someone give precise instructions in order to be able to reduce the phisical size of the tablespace?

I have read dozens of threads about tablespace shrinking, but I have not found a good/detailed solution for Oracle 8.0.3

I really appreciate any help in this issue.

Regards
  • 1. Re: How to shrink a tablespace in Oracle 8.0.3
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi,



    I could find below notes which for Release: 8.1.7 to 11.2, by the way I suggest plan to your db upgrade asap

         How to Shrink the Datafile of Temporary Tablespace [ID 273276.1]

    For previous see:
    How to Resize a Datafile [ID 1029252.6]

    Regard
    Helios

    Edited by: Helios- Gunes EROL on Nov 13, 2012 1:42 PM
  • 2. Re: How to shrink a tablespace in Oracle 8.0.3
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check the following
    http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
    ALTER DATABASE DATAFILE '<full path and name of the file>' RESIZE [K|M|G];
  • 3. Re: How to shrink a tablespace in Oracle 8.0.3
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    here is the admin guide for 8.0.4 (the most closest available); http://docs.oracle.com/cd/A58617_01/server.804/a58397/ch9.htm#534

    Herald ten Dam
    http://htendam.wordpress.com
  • 4. Re: How to shrink a tablespace in Oracle 8.0.3
    973949 Newbie
    Currently Being Moderated
    Hi, thanks for your help.

    As I said, I have read lots of threads where people give lots of different advices.

    What I am looking for is someone with experience, someone that has faced this same problem and has solved it

    Thanks everyone for your help.

    Regards
  • 5. Re: How to shrink a tablespace in Oracle 8.0.3
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    how did you delete those records? It sounds you did it with the delete statement. But if all records are gone, then it is better to do a truncate of the table (truncate table xxxxxxxx). After the truncate the table has only the initial size, as will the indexes have. After that you can possible shrink the datafile. Another option could be to drop the table (having of course the creation scripts), shrink the datafile and then recreate the table.

    Herald ten Dam
    http://htendam.wordpress.com
  • 6. Re: How to shrink a tablespace in Oracle 8.0.3
    EdStevens Guru
    Currently Being Moderated
    970946 wrote:
    Hi,

    We run Oracle 8.0.3 in a Sun computer with Solaris 5.8.
    That's scary in itself. You are "only" 7 generations out of support. Is your OS equally ancient? Your hardware?
    Recently we had a severe error in our application because one of the oracle tablespaces reached the 2Gb size limit (O.S. max file size).
    That's not a max size for the TS, just for one data file. You could have simply added another file to the TS ......


    >
    Now we need to shrink the tablespace and free O.S. disk space, because the application cannot write anymore in the database.

    We have deleted all the records of one of the tables, which is the biggest table in the tablespace and contains log information. This table has lots of indexes.

    Please, could someone give precise instructions in order to be able to reduce the phisical size of the tablespace?

    I have read dozens of threads about tablespace shrinking, but I have not found a good/detailed solution for Oracle 8.0.3

    I really appreciate any help in this issue.

    Regards
  • 7. Re: How to shrink a tablespace in Oracle 8.0.3
    973949 Newbie
    Currently Being Moderated
    I am maintaining a production application that I cannot modify. The company that designed the system is not available anymore and they were not aware of the 2 Gb limitation.

    My solution, after deleting the records, could be shrinking the table and then the tablespace and then I will have more than ten years of margin until the next crash.

    Thanks for your help.

    Regards
  • 8. Re: How to shrink a tablespace in Oracle 8.0.3
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please don't forget to mark this thread as answered
  • 9. Re: How to shrink a tablespace in Oracle 8.0.3
    jgarry Guru
    Currently Being Moderated
    Are you sure you can't just mount the filesystem with large files option?

    ulimit -f

    man ulimit for that setting. You might be able to unlimit it (I don't remember, I have a vague memory of Oracle install barfing on unlimited at some version).
  • 10. Re: How to shrink a tablespace in Oracle 8.0.3
    973949 Newbie
    Currently Being Moderated
    Hi Osama,

    I am still waiting for an answer from someone with experience in shrinking a tablespace.

    Regards
  • 11. Re: How to shrink a tablespace in Oracle 8.0.3
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Oracle 8i - most of the people are getting upgraded their environments. Coming to your issue, I suggest to refer to Oracle 8i documentation. Little hard to recollect things over the period of time with options available/restricted.

    - Pavan Kumar N
  • 12. Re: How to shrink a tablespace in Oracle 8.0.3
    973949 Newbie
    Currently Being Moderated
    Hi Pavan, thanks for your answer.

    I know my system is running an obsolete version of Oracle, but we cannot upgrade.

    What I am asking for is help from someone with experience in shrinking tablespaces in Oracle 8.

    Regards
  • 13. Re: How to shrink a tablespace in Oracle 8.0.3
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    did you truncate the table? That will give back space in the tablespace. After that all the tables/indexes can grow in the the tablespace. Maybe it is then possible to resize the datafile.

    Herald ten Dam
    http://htendam.wordpress.com
  • 14. Re: How to shrink a tablespace in Oracle 8.0.3
    andrewmy Journeyer
    Currently Being Moderated
    You haven't told us whether your server has diskspace contraints. If the server is short of diskspace it is going to be tricky to perform housekeeping since reorganization usually requires you to have spare disk capacity to move tables around. e.g.

    - delete old unwanted data from tables in old_tablespace
    - define one or more smaller datafiles for new_tablespace
    - move all the tables from old_tablespace to new_tablespace
    - drop old_tablespace

    If your server has spare diskapace, you should just add a datafile to the tablespace and save all the bother. One command and you are done.

    alter tablespace ... add datafile....
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points