首页 » ORACLE » How to check and reclaim allocated unused-space for the segments(预测segment回收空间)

How to check and reclaim allocated unused-space for the segments(预测segment回收空间)

A few days ago a friend from qq asked me a question,What should we do if the hard disk free space inot large enough for oracle?and do not want to buy a new hard disk.

some ways you can reference,for example,decrease PCTFREE\compress table or index\check datafile HWM,reset less\reclaim allocated and unused space for segments

check datafile HWM read http://www.anbob.com/?p=1622

the following I dome how to check and reclaim allocated unused-space for segments

SQL> conn anbob/anbob
Connected.
SQL> create table bigtab as select rownum rid,rownum||'a' rname from dual connect by rownum<=1000000;

SQL> delete from bigtab  where mod(rid,480)<400;

833360 rows deleted.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats(user,'BIGTAB');

PL/SQL procedure successfully completed.

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='BIGTAB';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
    167076       2520            0          0          12

SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='BIGTAB';

     BYTES
----------
  20971520

SQL> set serveroutput on
SQL> exec show_space2('BIGTAB',p_owner_1=>'ANBOB');
Total Blocks............................2560
Total Bytes.............................20971520
Unused Blocks...........................40
Unused Bytes............................327680
Last Used Ext FileId....................4
Last Used Ext BlockId...................3849
Last Used Block.........................88

above output show us had allocate 2560 blocks space for bigtab segmets,below the HWM has 2520 blocks,above HWM has 40 blocks .However , you know I have deleted about one-fifth of the rows.
Now I used segment advisor performs analysis on the segments and makes recommendations on how space can be reclaimed.

DECLARE
  l_object_id  NUMBER;
BEGIN
  -- Create a segment advisor task for the anbob.bigtab table.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'bigtab_segment_adviosr',
    task_desc         => 'Segment Advisor For BIGTAB');

  DBMS_ADVISOR.create_object (
    task_name   => 'bigtab_segment_adviosr',
    object_type => 'TABLE',
    attr1       => 'ANBOB', 
    attr2       => 'BIGTAB', 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'bigtab_segment_adviosr',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => 'bigtab_segment_adviosr');
end;
/

--display the finding

SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT  f.message, f.more_info FROM   dba_advisor_findings f
       JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE  f.task_name ='bigtab_segment_adviosr'


SQL> SELECT  f.message, f.more_info FROM   dba_advisor_findings f
  2         JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
  3  WHERE  f.task_name ='bigtab_segment_adviosr';

MESSAGE
------------------------------------------------------------------------------------------
MORE_INFO
-------------------------------------------------------------------------------------------
Enable row movement of the table ANBOB.BIGTAB and perform shrink, estimated savings is 13977563 bytes.
Allocated Space:20971520: Used Space:6993957: Reclaimable Space :13977563:



alter table bigtab enable row movement;
alter table bigtab shrink space;

execute dbms_stats.gather_table_stats(user,'BIGTAB');

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='BIGTAB';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
    166624        415            0          0          12

SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='BIGTAB';

     BYTES
----------
   3538944

SQL> SELECT BYTES,blocks FROM USER_SEGMENTS WHERE SEGMENT_NAME='BIGTAB';

     BYTES     BLOCKS
---------- ----------
   3538944        432


SQL>  exec show_space2('BIGTAB',p_owner_1=>'ANBOB');
Total Blocks............................432
Total Bytes.............................3538944
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................4
Last Used Ext BlockId...................1801
Last Used Block.........................47

sys@ANBOB>select 20971520-3538944 from dual;

20971520-3538944
----------------
        17432576

From above output show us had allocate 432 blocks space for bigtab segmets,actual reclaimable Space 17432576 bytes.

Now I used segment advisor performs analysis on the specified tablespaces(eg. users) and makes recommendations on how space can be reclaimed.

EXEC DBMS_ADVISOR.delete_task('USERS_SEGMENT_ADVISOR');

DECLARE
  l_object_id  NUMBER;
begin
DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'USERS_SEGMENT_ADVISOR',
    task_desc         => 'Segment Advisor For USERS');

  DBMS_ADVISOR.create_object (
    task_name   => 'USERS_SEGMENT_ADVISOR',
    object_type => 'TABLESPACE',
    attr1       => 'USERS', 
    attr2       => NULL, 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'USERS_SEGMENT_ADVISOR',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
end;
/


tip:
Bug 4519934 Fixed in 10.2.0.2.
ORA-44003: invalid SQL name
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at line 24


set linesize 160
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A20
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

 WITH qb
       AS (SELECT *
             FROM (  SELECT task_name,
                            object_id,
                            ROUND (
                                 NVL (
                                    TO_NUMBER (
                                       REGEXP_SUBSTR (
                                          REGEXP_SUBSTR (more_info,
                                                         'Reclaimable.*'),
                                          '[0-9]+')),
                                    0) / 1024 / 1024, 2)  reclam_space_MB,
                            more_info,
                            MESSAGE
                       FROM dba_advisor_findings
                      WHERE task_name = 'USERS_SEGMENT_ADVISOR'
                   ORDER BY 3 DESC)
            WHERE ROWNUM <= 10)
SELECT o.TYPE AS object_type,
        o.attr1 AS schema,
        o.attr2 AS object_name,
        qb.reclam_space_MB,
        qb.more_info,
        qb.MESSAGE
   FROM    qb
        JOIN
           dba_advisor_objects o
        ON qb.object_id = o.object_id AND qb.task_name = o.task_name;
        
        
        OBJECT_TYPE          SCHEMA               OBJECT_NAME                    RECLAM_SPACE_MB
-------------------- -------------------- ------------------------------ ---------------
MORE_INFO                                MESSAGE
---------------------------------------- ----------------------------------------
TABLE                XIAN                 ICME_NOPROJECT_SCORE                   3391.99
Allocated Space:3556769792: Used Space:8 Enable row movement of the table XIAN.IC
192: Reclaimable Space :3556761600:      ME_NOPROJECT_SCORE and perform shrink, e
                                         stimated savings is 3556761600 bytes.

TABLE                ICME3                ICME_NOPROJECT_SCORE                   3327.96
Allocated Space:3489660928: Used Space:4 Enable row movement of the table ICME3.I
6401: Reclaimable Space :3489614527:     CME_NOPROJECT_SCORE and perform shrink,
                                         estimated savings is 3489614527 bytes.

TABLE                ICME3                ICME_PROJECT_SCORE                     2052.97
Allocated Space:2152726528: Used Space:3 Enable row movement of the table ICME3.I
0122: Reclaimable Space :2152696406:     CME_PROJECT_SCORE and perform shrink, es
                                         timated savings is 2152696406 bytes.

TABLE                ICME3                ICME_STUDENT_SCORE_LOG                 1991.97
Allocated Space:2088763392: Used Space:3 Enable row movement of the table ICME3.I
0474: Reclaimable Space :2088732918:     CME_STUDENT_SCORE_LOG and perform shrink
                                         , estimated savings is 2088732918 bytes.


TABLE                XIAN                 ICME_PROJECT_SCORE                     1983.99
Allocated Space:2080374784: Used Space:8 Enable row movement of the table XIAN.IC
192: Reclaimable Space :2080366592:      ME_PROJECT_SCORE and perform shrink, est
                                         imated savings is 2080366592 bytes.

INDEX                ICME3                UK_PROJECT_SCORE                          1160
Allocated Space:1216348160: Used Space:1 Perform shrink, estimated savings is 121
162: Reclaimable Space :1216346998:      6346998 bytes.

INDEX                ICME3                IDX_STU_SCORE_LOG_FROMORGID               1024
Allocated Space:1073741824: Used Space:1 Perform shrink, estimated savings is 107
198: Reclaimable Space :1073740626:      3740626 bytes.
..

oops!The result is quite startling,Actually there is so much space is wasted.Now we try to Reclaim store space from some segments.

Carefully optimistic

1,
SQL> select count(*) from XIAN.ICME_NOPROJECT_SCORE;

  COUNT(*)
----------
         0
SQL> select bytes from dba_segments where segment_name='ICME_NOPROJECT_SCORE' and owner='XIAN';

     BYTES
----------
3556769792
SQL> truncate table xian.icme_NOPROJECT_SCORE;
SQL> select bytes from dba_segments where segment_name='ICME_NOPROJECT_SCORE' and owner='XIAN';

     BYTES
----------
3556769792

why?

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','ICME_NOPROJECT_SCORE','XIAN') FROM dual;

 CREATE TABLE "XIAN"."ICME_NOPROJECT_SCORE"
   (    "SCORE_ID" NUMBER(*,0) NOT NULL ENABLE,
...
STORAGE(INITIAL 3512729600 NEXT 1048576


notice:
 INITIAL option of the segment storage


SQL> alter table xian.icme_noproject_score storage(initial 1m);
alter table xian.icme_noproject_score storage(initial 1m)
                                              *
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

SQL> alter table xian.icme_noproject_score move STORAGE (INITIAL 10k);

SQL> select bytes from dba_segments where segment_name='ICME_NOPROJECT_SCORE' and owner='XIAN';

     BYTES
----------
     65536
2,

SQL> select count(*) from icme3.icme_noproject_score;

  COUNT(*)
----------
         9
SQL> select bytes from dba_segments where segment_name='ICME_NOPROJECT_SCORE' and owner='ICME3';
     BYTES
----------
3489660928

SQL> ALTER TABLE ICME3.ICME_NOPROJECT_SCORE ENABLE ROW MOVEMENT;
SQL> ALTER TABLE ICME3.ICME_NOPROJECT_SCORE SHRINK SPACE;
SQL> select bytes from dba_segments where segment_name='ICME_NOPROJECT_SCORE' and owner='ICME3';

     BYTES
----------
    327680
SQL> ALTER TABLE ICME3.ICME_NOPROJECT_SCORE disable ROW MOVEMENT;

3,
SQL> select count(*) from icme3.ICME_STUDENT_SCORE_LOG;

  COUNT(*)
----------
        11
SQL> select bytes,blocks from dba_segments where segment_name='ICME_STUDENT_SCORE_LOG' and owner='ICME3';

     BYTES     BLOCKS
---------- ----------
2088763392     254976

SQL> alter table icme3.ICME_STUDENT_SCORE_LOG deallocate unused keep 1k;

SQL> select bytes,blocks from dba_segments where segment_name='ICME_STUDENT_SCORE_LOG' and owner='ICME3';

     BYTES     BLOCKS
---------- ----------
   2162688        264

note:
Oracle Database frees only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data). Oracle 

deallocates unused space beginning from the end of the object and moving toward the beginning of the object to the high water mark.
"
KEEP integer

Specify the number of bytes above the high water mark that the segment of the database object is to have after deallocation.

    If you omit KEEP and the high water mark is above the size of INITIAL and MINEXTENTS, then all unused space above the high water mark is freed. When the high water mark is 

less than the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is freed.

    If you specify KEEP, then the specified amount of space is kept and the remaining space is freed. When the remaining number of extents is less than MINEXTENTS, then Oracle 

adjusts MINEXTENTS to the new number of extents. If the initial extent becomes smaller than INITIAL, then Oracle adjusts INITIAL to the new size.

    In either case, Oracle sets the value of the NEXT storage parameter to the size of the last extent that was deallocated.

"
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses003.htm#g1056929

4,
--Before the query is to ensure that statistical information is correct
SQL> select num_rows from dba_indexes where index_name='UK_PROJECT_SCORE' and owner='ICME3';

  NUM_ROWS
----------
         3
SQL> select bytes,blocks from dba_segments where segment_name='UK_PROJECT_SCORE' and owner='ICME3';

     BYTES     BLOCKS
---------- ----------
1216348160     148480

SQL> alter index icme3.UK_PROJECT_SCORE SHRINK SPACE;

Index altered.

SQL> select bytes,blocks from dba_segments where segment_name='UK_PROJECT_SCORE' and owner='ICME3';

     BYTES     BLOCKS
---------- ----------
    327680         40

--my db version 10r2

--finished--

打赏

对不起,这篇文章暂时关闭评论。