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--