Solved

Reclaim space

Posted on 2011-02-14
11
575 Views
Last Modified: 2012-05-11
How to reclaim space after deleting rows from a table?
0
Comment
Question by:msimons4
  • 6
  • 3
  • 2
11 Comments
 
LVL 76

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 76

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
 
LVL 34

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now