Solved

Reclaim space

Posted on 2011-02-14
11
586 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 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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 107
ORA-00923: FROM keyword not found where expected 3 80
having some issue on pl sql procedure 1 23
add more rows to hierarchy 3 26
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

820 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