1 2 Previous Next 15 Replies Latest reply: Nov 15, 2012 1:47 AM by 973949 RSS

    How to shrink a tablespace in Oracle 8.0.3

    973949
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Please don't forget to mark this thread as answered
                        • 9. Re: How to shrink a tablespace in Oracle 8.0.3
                          jgarry
                          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
                            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
                              Pavan Kumar
                              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
                                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
                                  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
                                    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