?
Solved

Reclaim Unused  Space in Segments

Posted on 2003-03-25
7
Medium Priority
?
1,233 Views
Last Modified: 2012-08-13
I understand that Oracle can automatically allocate new extents for a segment when the segment requires more storage space. However, Oracle does not automatically release (deallocate) extents when a segment has a significant amount of unused space.

I am using this Report to give me information on a segment's High Water Mark:

SET SERVEROUTPUT ON;
 
DECLARE
 segment_owner VARCHAR2(100) := 'M104';
 segment_name VARCHAR2(100) := 'ORDERS';  
 segment_type VARCHAR2(100) := 'TABLE';
 total_blocks NUMBER;
 total_bytes NUMBER;
 unused_blocks NUMBER;
 unused_bytes NUMBER;
 last_used_extent_file_id NUMBER;
 last_used_extent_block_id NUMBER;
 last_used_block NUMBER;
BEGIN
 sys.dbms_space.unused_space(
  segment_owner,
  segment_name,
  segment_type,
  total_blocks,
  total_bytes,
  unused_blocks,
  unused_bytes,
  last_used_extent_file_id,
  last_used_extent_block_id,
  last_used_block );
 dbms_output.put_line('SEGMENT:       '||segment_owner||'.'||segment_name);
 dbms_output.put_line('TYPE:          '||segment_type);
 dbms_output.put_line('TOTAL BLOCKS:  '||total_blocks);
 dbms_output.put_line('TOTAL BYTES:   '||total_bytes);
 dbms_output.put_line('UNUSED BLOCKS: '||unused_blocks);
 dbms_output.put_line('UNUSED BYTES:  '||unused_bytes);
END;
/

SEGMENT:       M104.ORDERS
TYPE:          TABLE
TOTAL BLOCKS:  14
TOTAL BYTES:   114688
UNUSED BLOCKS: 6
UNUSED BYTES:  49152
 
PL/SQL procedure successfully completed.

Oracle documentation says that if you determine that a segment contains a significant number of unused blocks, you can attempt to deallocate one or more extents from the segment using the DEALLOCATE clause.

example:

SQL> SELECT extent_id, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'ORDERS'
  4   ORDER BY extent_id;
 
 EXTENT_ID      BYTES
---------- ----------
         0      16384
         1      16384
         2      16384
         3      16384
         4      16384
         5      16384
         6      16384
 
7 rows selected.
 
SQL> ALTER TABLE orders
  2   DEALLOCATE UNUSED;
 
Table altered.
 
SQL> SELECT extent_id, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'ORDERS'
  4   ORDER BY extent_id;
 
 EXTENT_ID      BYTES
---------- ----------
         0      16384
         1      16384
         2      16384
         3      16384


My question is this when I ran the above PL/SQL report against one of my tables in my PPSPROD enviroment.

SQL> show user
USER is "PPSPROD"
SQL> @C:\orant\sql\space.sql
SEGMENT:       PPSPROD.WORKS_OPERATION
TYPE:          TABLE
TOTAL BLOCKS:  69120
TOTAL BYTES:   141557760
UNUSED BLOCKS: 41616
UNUSED BYTES:  85229568

PL/SQL procedure successfully completed.

It showed UNUSED BLOCKS: 41616

When I checked the extent_id, bytes from user_extents I received this result
showing only one extent:

SQL> SELECT extent_id, bytes
  2  FROM user_extents
  3  WHERE segment_name = 'WORKS_OPERATION'
  4  ORDER BY extent_id;

EXTENT_ID     BYTES
--------- ---------
        0 141557760

I know I can't use ALTER TABLE DEALLOCATE UNUSED to deallocate one or more extents from the segment. What else can I use to release unused space in segments that show only one extent? The problem is that I have many of these segments with very high UNUSED BLOCKS but showing only one extent.



Thank you







0
Comment
Question by:algotube
[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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8205795
You didn't tell us which version of Oracle you have.  There are more options in the later versions of Oracle than if you have Oracle8.0 or 7.3.

In any version of Oracle you can export the table, then drop it and recreate it (smaller if you like and/or with a different pctfree, pctused, etc.) then import the data.  This approach of course requires that table to be unavailable to the application for the time it takes for the export, rebuild and import.

In Oracle8i or higher, you can do an "alter table [table_name] move [new tablespace];".  The new tablespace may have different storage parameters than the current tablespace.  And, you can do this "on-the-fly", but it may not recover as much free space as the export, rebuild, import approach.  Oracle9i has even more options.
0
 
LVL 7

Expert Comment

by:BobMc
ID: 8206750
markgeer pretty much has it covered.
Only other option I would throw into the hat is to

1. Rename table
2. Recreate original table with better storage parameters as select * from renamed table.

3. Drop the renamed table.

This will work in Oracle 7.x also.

HTH
Bob
0
 
LVL 10

Expert Comment

by:SDutta
ID: 8206885
If you have that much free space and can't deallocate, there must have been a lot of deletes on the table. Otherwise, Alter Table Deallocate ... pretty much frees everything above the high water mark even with only one extent.

Upto 8i, export/import works best to defrag tables including its indexes.

If you do it manually with Bob's method or ALTER TABLE ... MOVE ... make sure to regenerate indexes, triggers etc. Also check for any dependent objects that may become invalid.

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:algotube
ID: 8211396
Thanks for everyone who replied. I am runing.

Oracle8 Enterprise Edition VLM Release 8.0.5.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.5.0.0 - Production

Can I still use

SQL> ALTER TABLE orders
 2   DEALLOCATE UNUSED;

with only one extent and would it work?

How high should the UNUSED BLOCKS be before I do something?? Above 100 under 100, above 1000 under 1000??????? what is a good measure????
0
 
LVL 10

Accepted Solution

by:
SDutta earned 200 total points
ID: 8212358
This feature works on 8.0.5.
Syntax is :

ALTER TABLE Orders DEALLOCATE UNUSED;
Where it will deallocate all blocks above the high water mark, in this case all 49152 bytes.
or
ALTER TABLE Orders DEALLOCATE UNUSED KEEP 10K;
Will deallocate only 38912 bytes.

re : "How high should the UNUSED BLOCKS be" the unused blocks don't increase, they are allocated at extent creation time depending on the value of INITIAL and NEXT storage clause. These blocks will keep decreasing as you add rows to the table until the extent becomes full and the next extent is allocated.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8212807
Rather than trying to deallocate unused space in tables, I usually find it much simpler to set up tablespaces where all objects are the same size and all objects have "pct_increase" set to 0.  Yes, there will be some unused blocks in most (if not all) tables and indexes this way, but then the management and reuse of freespace is much simpler, because all objects in the tablespace then look for and can use the same size extents.  This may require three tablespaces for data (one with small extents, one with medium-sized extents, and one with large extents) and three similar tablespaces for indexes.  If you have 100's of tables, you may want multiple tablespaces of each size.

The "deallocate" approach is likely to result in fragmented free space that may be difficult (or impossible) for other objects to use depending on their settings for "pct_increase" and "next".
0
 

Author Comment

by:algotube
ID: 8213187
Thank you all who have responded, very much appreciated.
0

Featured Post

Industry Leaders: 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!

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

762 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