Oracle 10g - Shrink table

Hi,

I several table sin my DB that has over 20 millions rows, but Im planning to delete most them and leave them with only 50-100k...

After I do it, I dont wanna have oversized tablespaces, fragmented tables/indexes...

So, after I do this massive delete (after any massive delete of a table size in Oracle 10g), do I need to run any commands to avoid fragmentations, indexes corrupted..? By doing this the index will be resized automatically?

And is there any command that I can run to shrink the tablespaces to a size that fits better for this new table size I will have? - How can I know the new size that I need... I want to have less dbf files...

Tks,
Joao
joaotellesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I would suggest you create a new tablespace of the correct size, delete all the rows then move the table into the new tablespace (alter table move).

This will in essence 'shrink' the table.  You can then resize the original tablespace if there are no other tables in it or no extents allocated above the new size you are trying to use.

As far as indexes, etc...  once space is allocated, it remains with the object even if there is no data in the allocated space.  This is not a bad thing since allocating new space takes time.

A simple rebuild online should fix any issues.

If the table will ever grow again, best to allow for the growth now.
0
DavidSenior Oracle Database AdministratorCommented:
If your database instance is in archivelog mode (where each delete is recorded for recovery), you are likely going to run into committing those transactions -- not to mention the indexes.  BTW are there any dependent (child) tables involved?

Some would advise you to switch the instance into noarchivelog for the file manipulation, provided you could recover the data you want to keep from backup.

Would you consider renaming the existing table (after disabling or dropping indexes), then recreate the target using CREATE TABLE AS SELECT (...)?  Here's your syntax.
0
joaotellesAuthor Commented:
Tks for the replies...

I will try to answer your questions and then write here what I think I should do:

1- Yes there will be child tables involved... there are several tables that will have rows deleted.

==

So, what I think I should do is: pls correct me if Im wrong:

1- Put in noarchivelog mode

2- Delete the table rows ( I have a built script for it)

3- Resize the tablespace:

After the deletion is complete, is there a way to estimate the size of all the tables in a tablespace, so I can resize it?

I dont have enough space in disk to create a new TB with the existing one... This plan would be to create a new TB with the space the tables in it get now (not sure how to get this number),,, and move all tables to it, them remove the old TB?

After the table rows deletion the dbfs will be still there right? Until we resize the TB correct?

 4- After the resize, rebuild all involved indexes of these tables...

====

Is this accurate?

Do I have to worry that after the table goes from 50M rows to 50k, that it might get fragmented?

Tks,
Joao
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>is there a way to estimate the size of all the tables in a tablespace, so I can resize it?

Yes and no.  Just because you have the size doesn't mean you can resize the datafiles.  Oracle can have allocated blocks with data nead the end of the datafile.  If so, you cannot resize below that.

This is where the MOVE command comes in handy.  At a very high level, it will keep the data as close to the physical end of the file as it can.

>>1- Put in noarchivelog mode

If this is a production database or a database that is being backed up, this can be a bad idea.  You need to understand what you are doing if you decide to do this.

>>I dont have enough space in disk to create a new TB with the existing one

It only needs to be as big as the table in question.  You can move it there, then move it back to the original tablespace.  When everything is done, drop the new tablespace.

Read up on the High Water Mark (HWM) in datafiles.  You'll get an idea of what is happening.
0
joaotellesAuthor Commented:
Can I do this move with several tables at the same time?

Because it will not be only one table...

So, as afra I undertood, I can delete the rows of the table, create a TB with the table size, move it to this TB, nad then move back to the original TB...

But after this, is there a way to shrink this original TB.. I think this is the goal here... to have less dbf files...
0
slightwv (䄆 Netminder) Commented:
You can resize a datafile easy enough.  The trouble comes with the HWM for allocated space in the datafile.  You cannot resize a datafile smaller than the HWM.

The docs have the resize command:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#i1106377

>>Can I do this move with several tables at the same time?

Nope. One at a time.  You could create a select statement to generate the necessary SQL commands.
0
joaotellesAuthor Commented:
I do not want to resize the datafiles size ... but the number of datafiles in the TB...

I have one newbie question... When I delete all the rows I need to delete from a table:

1- Is there a way to decrease the number of dbf files of the TB that the table belongs? Because since the table will decrse its size, shouldnt the dbfs number decrease as well?

2- If the answer to the question #1 is no (which I think it is)... Is there a way to decrease the number of dbf files of the TB that the table belongs by reallocating the data on the other dbfs.. What I mean is since the amount of data will decrease with the deletion of rows, the dbf files will be more empty...

PS: Is this what the MOVE thing will do? Put all the data of the tables into a new TB with less dbf files? Then when I move back the dnfs will be with the right size?

I think Im ok with the indexes.. just leave it for the last.
0
slightwv (䄆 Netminder) Commented:
>>Because since the table will decrse its size, shouldnt the dbfs number decrease as well?

No.  Once space is allocated to an object, it stays allocated to the object.  Remember, increasing space is an expensive operation.  Constantly increasing/reducing file sizes wouldn't be very efficient.

Even Access requires you to 'Compact' a database.

>>When I delete all the rows I need to delete from a table

I don't understand what you are asking here.

>>Is there a way to decrease the number of dbf files of the TB

You can drop datafiles from a tablespace.  You just need to make sure there are no allocated extents in the datafile you wish to drop.

>>Is this what the MOVE thing will do? Put all the data of the tables into a new TB with less dbf files?

Yes.

>>Then when I move back the dnfs will be with the right size?

Only once you are done removing datafiles and resizing the ones you have left.

Oracle doesn't do datafile shrinking automatically.  It will only extend them in size if you give it permission to do so.
0
joaotellesAuthor Commented:
Tks for the answers and sry for the dummy questions...

You mentioned the below:

>> You can drop datafiles from a tablespace.  You just need to make sure there are no allocated extents in the datafile you wish to drop.
Is there a way to make sure for this.. But since delete the rows wouldnt "open" space in the dbf files, so this is not an option I guess...

>> Once space is allocated to an object, it stays allocated to the object.
If I move all the tables from the original "big" TB, to a "small" TB after the rows deletion,  I will be able to delete the "big" TB later? Is there any special procedure I have to do after the big TB deletion and all its dbfs?
Is this possible?

Tks in advance.
0
slightwv (䄆 Netminder) Commented:
>>But since delete the rows wouldnt "open" space in the dbf files, so this is not an option I guess...

It's an option but requires extra work (and extra disk space).

>> I will be able to delete the "big" TB later?

As long as there are no objects left in it.  The dba_segments view will show you this.

>>Is there any special procedure I have to do after the big TB deletion and all its dbfs?

Just drop everything.

The drop tablespace command does what you need:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9004.htm#sthref5868
0
joaotellesAuthor Commented:
Tks for all the help and all the knowledge...

I dont have ANY available disk space at the moment, not even to create a new TB to move the objects to it...

Is the any other option that does not require any add. disk space?

If not I will have to get a new disk....
0
slightwv (䄆 Netminder) Commented:
Depending on what your database looks like and how much data will be left in the tables, do you have free space in a different tablespace that you can use temporarily while moving stuff around?

I've never played with this much and I remember it not working as advertised but there is a reorganize that is available.

Do you have dbConsole or Enterprise Manager available?

You might be able to free up some space with it.

Here is a link with decent information on what I've talked about here and some other options you may be able to use:

http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php
0
slightwv (䄆 Netminder) Commented:
In the link I posted above:  What I've been discussing here is described there as "Manual Tablespace Reorganization.
0
joaotellesAuthor Commented:
I think I will go with the exp/imp option.. What do you think? Do you think it can work?

With this option I wouldnt need to rebuild the indexes... since I would be exp/imp them right?
0
joaotellesAuthor Commented:
ANd this I wouldnt need to do table per table.. I could everything together...
0
slightwv (䄆 Netminder) Commented:
As long as you drop all the objects, the indexes will be recreated.

Pay attention to this part of the exp/imp section:
•Perform any required maintenance, like grants etc.

You may also have issues if you have constraints on the objects you plan on dropping.  If the constraints are on objects NOT in the same tablespace, you would need to take them into account.

As you read in that link:  There is no real easy way to do what you want.

Personally, MOVE has worked the best for me over the years when I've had to shrink a datafile.  That is why I proposed it from the beginning.

I personally feel less can go wrong with the MOVE since you really aren't messing with the objects involved directly like a DROP will.


If you look at how the objects are stored in the datafiles, you might be able to shrink the datafiles with one or tow MOVEs.  It will just require you to do a lot of queries or research using OEM to figure out the best plan of what to move when.

You might get lucky:  After you delete all the rows, maybe a quick release of unused space and a coalesce, you may free up everything to resize/drop datafiles and not have to move anything.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joaotellesAuthor Commented:
The problem with the MOVE is that I dont hav available space... I will check afetr the deletion of the rows... I may have it... If I have it I will go with it... If not, I will go with the imp/exp.


" If the constraints are on objects NOT in the same tablespace"

Do you have something to check this before doing anything?
0
slightwv (䄆 Netminder) Commented:
You keep saying you don't have space.  I just find it very hard to believe you can't find enough room after cleaning up some logs or moving them temporarily to a flash drive long enough to do what you need to do.

All your disks are 100% full?
All your current tablespaces are 100% full?
0
slightwv (䄆 Netminder) Commented:
>>Do you have something to check this before doing anything?

I don't keep scripts like  this around.  When I need them, I Google for them.  Most common scripts are pretty easy to find whenever I need them.

You just need to look for object constraint scripts.

I would also check the DBA_DEPENDENCIES view.
0
joaotellesAuthor Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.