[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1268
  • Last Modified:

Reclaim Unused Space in Segments

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
algotube
Asked:
algotube
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
BobMcCommented:
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
 
SDuttaCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
algotubeAuthor Commented:
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
 
SDuttaCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
algotubeAuthor Commented:
Thank you all who have responded, very much appreciated.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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