?
Solved

Reclaim space

Posted on 2011-02-14
11
Medium Priority
?
590 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 1500 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 1500 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 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 1500 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 500 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 1500 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 1500 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 1500 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 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