Solved

Reclaim space

Posted on 2011-02-14
11
587 Views
Last Modified: 2012-05-11
How to reclaim space after deleting rows from a table?
0
Comment
Question by:msimons4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 total points
ID: 34890742
Typically you do not need to.  Assuming the tables will eventually regrow, leave the extents allocated.

That said:  define reclaim space:  unallocate the extents for the tables, shrink the datafiles, or something else?
0
 

Author Comment

by:msimons4
ID: 34891029
By reclaim space I mean when I issue the linux command du -h, have the total gigabytes for the datafile directory go down by the amount that the 7,000,000 rows that I just deleted account for. I will not need this space again.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 34891062
How large ables/objects left in the tablespace involved?

If you have space available elsewhere temporarily: Move those objects into a different tablespace, resize the datafiles once ALL objects have been removed.  Then move the objects back to the original tablesapce.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
ID: 34891802
Your options vary a bit depending on which version of Oracle you have.  Can you tell us which Oracle database version you have?

But, you are correct, in any version of Oracle when you delete records, the space is *NOT* automatically made available for other tables to use.  By default, the space is only available for new records to be added into the table you just deleted the records from.

Your easiest option is likely:
alter table [table_name] move [tablespace_name];

The value you use for [tablespace_name] can either by a different tablespace, or the same tablespace the table is in now (assuming it has enough free space).  EIther way, after doing an "alter table ... move ..." command, you must manually rebuild any indexes on the table.

A couple other options are:
1. a. create table [new_name] as select * from [current_name];
    b. check for constraints, triggers, default values, indexes and grants on the current table
    c. drop table [current_name];
    d. rename [new_name] to [current_name]
    e. create the same constraints, triggers, default values, indexes and grants that the original table had.

2. a. export the contents of the [current_table]
    b. truncate table [current_table]
    c. disable any triggers on the current table
    d. import the data exported in 2.a
    e. enable any/all triggers disabled in 2.c
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 34891849
>>A couple other options are:

There needs to be a datafile resize in there somewhere.  They are wanting to free up the disk space not just release the allocated extents for the table.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
ID: 34892074
Slightwv: You correct, I had missed that.

Msimons4: In addition to the steps I described above (to help reclaim the space inside the database, so it is available to other tables) you will need to resize (shrink) the datafile(s) that used to contain these records.  But, depending on whether other tables in the tablespace also occupy space near the end(s) of the datafile(s) or not, you may have to move some or all objects from this tablespace to another tablespace before you can shrink this tablespace.
0
 

Author Comment

by:msimons4
ID: 34892190
I like the idea of moving to another tablespace ect, but I have some lobsegments that give me the error must use VARRAY before the storage parameter and I have other lobsegments that are of long datatypes which can't be moved. What should I do about these two errors.
Thanks
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 34892356
Long datatypes are a bear to mess with.

How much data is left?  You might be better off using the export/import method mentioned above.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 34892373
Or,  create new objects described above but instead of dropping the old tables, truncate them in place.  Then resize the datafile and insert the data back in.

That way you shouldn't have to mess with any possible constraint issues.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 34899235
While trying to confirm something for your other question, I came across the following link.  Starting in 10g, there are some new features for tablespace reorganization/shrinking/resizing that I forgot about if you are using ASSM.

http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/
0
 

Author Comment

by:msimons4
ID: 34908539
Great article !!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question