• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Reclaim space

How to reclaim space after deleting rows from a table?
0
msimons4
Asked:
msimons4
  • 6
  • 3
  • 2
8 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
msimons4Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Mark GeerlingsDatabase AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
msimons4Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
msimons4Author Commented:
Great article !!!
0

Featured Post

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.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now