This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 29, 2012 7:27 AM by 846231 RSS

Database Reorg Expert?

846231 Newbie
Currently Being Moderated
Hi All,

11.2.0.1

We have lots of development databases, like 50+. They share on one or more database servers.

Each project has its own database. The developers loads data, test, then delete, then load again data (refreshing the old one from onsites new data). and the cycle goes on and on.
The database sometimes blown out of space when some dev loaded actual onsite prod data which Afterward they will truncate.

Usually the average database testing size is 10Gb, but they grow sometime to 50Gb.

My issue is even if they delete or truncate big tables, the storage are still used up. So I was task to reorg the database every 6 months, to freeup.

My question is how so I list all the database that is worth reorganizing? meaning the ones that I can release or freeaup space like 2Gb or more.

For example I have a table that was loaded worth 50Gb size data and was truncated. How can I select these tables? Or am I making sense at all :(


Thanks a lot,

Kinz
  • 1. Re: Database Reorg Expert?
    Girish Sharma Guru
    Currently Being Moderated
    http://docs.oracle.com/html/A86647_01/reorg.htm

    and

    http://www.allenhayden.com/cgi/getdoc.pl?file=reorg.pdf

    links may be of your interest.

    Regards
    Girish Sharma
  • 2. Re: Database Reorg Expert?
    846231 Newbie
    Currently Being Moderated
    Thanks girish,

    But those docs are long time old as of 9i.

    Maybe its not applicable to 11.2.0.1? :(


    Thanks
  • 3. Re: Database Reorg Expert?
    846231 Newbie
    Currently Being Moderated
    Hi again,

    Suppose i have a table with 10Million rows, and i deleted 9Million rows. What is the select command such that I can see the space beeing freed by the 9Million rows and the index counterpart that were deleted?


    Thanks a lot,

    Kinz
  • 4. Re: Database Reorg Expert?
    Girish Sharma Guru
    Currently Being Moderated
    Please read below link and in the last Jaffar Hussain has posted the PL/SQL code which may help you for sure :

    Re: How to reorg a database

    Regards
    Girish Sharma
  • 5. Re: Database Reorg Expert?
    P.Forstmann Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Suppose i have a table with 10Million rows, and i deleted 9Million rows. What is the select command such that I can see the space beeing freed by the 9Million rows and the index counterpart that were deleted?
    There is no such SQL statement for table because you need to use DBMS_SPACE PL/SQL package for this.

    Tom Kyte has written a PL/SQL procedure named show_space for this purpose:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5350053031470#2653229400346592147

    Edited by: P. Forstmann on 28 déc. 2012 11:42
  • 6. Re: Database Reorg Expert?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi again,

    Support i have a table with 10Million rows, and i deleted 9Million rows. What is the select command such that I can see the space beeing freed by the 9Million rows and the index counterpart that were deleted?

    The key question, in your particular case, is how much time are you allowed to deny access to a database while doing a re-org. You could simply choose to write some code that identifies all tables in a given tablespace and issues "alter table XXX move", do this for each "user" tablespace, then rebuild all invalid indexes. There are a number of little traps, of course - you may have write special case code to deal with materialized views, LOBs, partitioned tables, queue tables, index organized tables, clusters, etc. (I'm assuming that your databases all use locally managed tablespaces when I say this - dictionary managed may need more subtle code).

    If you can simply rebuild everything by brute force, whether it needs it or not, then you're done. To be a little more sophisticated, you could note that each database should be about 10GB, and only run the code against a given database if it was a given percentage larger than expected. You could limit this to just those tablespaces which were known to be too big. Inevitably a very simple scheme would find a few edge cases where the results were unexpected - but if you're doing this to deal with databases that have become much larger than they should be and really do have a lot of empty space, then a simple scheme should be relatively safe.

    Regards
    Jonathan Lewis
  • 7. Re: Database Reorg Expert?
    EdStevens Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi again,

    Suppose i have a table with 10Million rows, and i deleted 9Million rows. What is the select command such that I can see the space beeing freed by the 9Million rows and the index counterpart that were deleted?


    Thanks a lot,

    Kinz
    If I had a table with 10 million rows and I deleted 9 million rows, I'd assume that at some point in the near future that table would grow to 10 million rows. And since I expect it to grow back to 10 million rows, I wouldn't waste my time trying to give back space that I'm just going to need again anyway. Disk is cheap. In fact, at this point in the development of technology, it's probably the least expensive resource we deal with. Disk is far less expensive than a DBA's time.
  • 8. Re: Database Reorg Expert?
    846231 Newbie
    Currently Being Moderated
    I thank you all :)
    If I had a table with 10 million rows and I deleted 9 million rows, I'd assume that at some point in the near future that table would grow to 10 million rows. And since I expect it to grow back to 10 >million rows
    For the sake of argument, supposing the table will not grow anymore because this is just a test database and only sampling data is needed.
  • 9. Re: Database Reorg Expert?
    EdStevens Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    I thank you all :)
    If I had a table with 10 million rows and I deleted 9 million rows, I'd assume that at some point in the near future that table would grow to 10 million rows. And since I expect it to grow back to 10 >million rows
    For the sake of argument, supposing the table will not grow anymore because this is just a test database and only sampling data is needed.
    You still have to ask what is gained? reorging the table will free up space within the tablespace. The data file will continue to occupy all the space it did prior to the operation.
  • 10. Re: Database Reorg Expert?
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    I thank you all :)
    If I had a table with 10 million rows and I deleted 9 million rows, I'd assume that at some point in the near future that table would grow to 10 million rows. And since I expect it to grow back to 10 >million rows
    For the sake of argument, supposing the table will not grow anymore because this is just a test database and only sampling data is needed.
    DROP & CREATE table.
  • 11. Re: Database Reorg Expert?
    846231 Newbie
    Currently Being Moderated
    Hi Jon,

    This is just a dev/test database with demo data and simple database structures. We do have complex db objects. We usually export import from the demo data as base table which just took less than 30 mins.
    The average size for test database is like 2Gb. But because of exceptions(very rare) some data grows to 50Gb when actual onsite data is being used. My point really is just want to prove to my boss if there can be space freed if reorg is done. I want to tell him that based on the report there is not enough space that can be freed and can not justify the reorg activity.


    Thanks
  • 12. Re: Database Reorg Expert?
    846231 Newbie
    Currently Being Moderated
    Hi sb,
    DROP & CREATE table.
    The problem is I don't know which table to drop. I am just giving a sample and I really do not know which table is a candidate for "drop & create".

    Thanks
  • 13. Re: Database Reorg Expert?
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi sb,
    DROP & CREATE table.
    The problem is I don't know which table to drop. I am just giving a sample and I really do not know which table is a candidate for "drop & create".

    Thanks
    total disk space is the same before & after any "reorg".
    You are simply rearranging the deck chairs on the Titantic.
    Since there is sufficient disk space to hold all the tables now, the same data & table will exist after any activity that you may do.
  • 14. Re: Database Reorg Expert?
    EdStevens Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi sb,
    DROP & CREATE table.
    The problem is I don't know which table to drop. I am just giving a sample and I really do not know which table is a candidate for "drop & create".

    Thanks
    since this is a test database, and the data is - by your own admission - a sampling of production, how do you expect Oracle to know which tables you should drop/truncate/reorg? Now you are talking about knowing your application and knowing your data. oracle doesn't know about that.
1 2 Previous Next

Legend

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