In case you are working with HD Space limit, for instance running Oracle XE, you should be aware of following procedure to reduce tablespace files. You may have tried to reduce datafile space with command:
|
1 |
alter database datafile 5 resize 1000M; |
And obtained error:
|
1 2 3 |
* ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value |
In my case, I had an Oracle Xe 11g with big tablespaces (7 GB’s of reserved data) but used less than 1% (I only used 80MB!!). So, why the hell I cannot retrieve my empty space back? This happened because at a certain moment, schemas generated a lot of info which was deleted afterwards. Even though data isn’t already there, tablespace had grown so you have a big gap between deletion and the following inserted data.
First of all, you should discover file ID of your tablespace, you can use following query which detects free segments:
|
1 2 3 |
select file_id, block_id, blocks, bytes, 'FREE' from dba_free_space where tablespace_name = 'TABLESPACE_NAME' order by block_id desc; |
Then you can run this query which indicates which objects you should move (drop and create again is the best option) in order to be able to shrink datafile:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
DECLARE V_FILE_ID NUMBER; V_BLOCK_SIZE NUMBER; V_RESIZE_SIZE NUMBER; BEGIN V_FILE_ID := &FILE_ID; V_RESIZE_SIZE := &RESIZE_FILE_TO; SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID; DBMS_OUTPUT.PUT_LINE ('.'); DBMS_OUTPUT.PUT_LINE ('.'); DBMS_OUTPUT.PUT_LINE ('.'); DBMS_OUTPUT.PUT_LINE ( 'OBJECTS IN FILE ' || V_FILE_ID || ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO ' || V_RESIZE_SIZE || ' BYTES'); DBMS_OUTPUT.PUT_LINE ( '======================================================================='); FOR my_record IN ( SELECT DISTINCT ( OWNER || '.' || SEGMENT_NAME || ' - OBJECT TYPE = ' || SEGMENT_TYPE) ONAME FROM DBA_EXTENTS WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE AND FILE_ID = V_FILE_ID ORDER BY 1) LOOP DBMS_OUTPUT.PUT_LINE (my_record.ONAME); END LOOP; END; / |
Then you can proceed to move data, but before dropping and creating objects, which is quite dangerous, you can try to shrink object used space instead. Since I’m quite lazy, I’ve generated a script which outputs the commands I have to execute to shrink data from all my schema tables:
1) Enabling row movement in order to shrink tables. Execute, copy / paste output and execute output again:
|
1 |
select 'alter table '||table_name||' enable row movement;' from user_tables |
2) Shrinking cascade all tables (which means shrinking associated indexes as well)
|
1 |
select 'alter table '||table_name||' shrink space cascade;' from user_tables |
3) Disabling row movement again:
|
1 |
select 'alter table '||table_name||' disable row movement;' from user_tables |
So after that, I could successfully execute:
|
1 |
alter database datafile 5 resize 1000M; |
For further info, following blogs helped me to understand what I had to do:
- http://samadhandba.wordpress.com/2011/03/07/reducing-datafile-size-to-recover-free-space-%E2%80%93-oracle-database-10g/
- http://oracledbascripts.blogspot.com.es/2010/07/shrink-table-in-oracle-10g11g.html
- http://www.dba-oracle.com/t_ora_03297_file_contains_used_data_beyond_requested_resize_value.htm
Update 2012/11/19:
Thanks to danruca, I implemented an script including two steps to best shrink data:
|
1 2 3 4 5 6 7 8 9 |
begin for c in (select table_name from user_tables where tablespace_name is not null) loop dbms_output.put_line(c.table_name); execute immediate 'ALTER TABLE '||c.table_name||' ENABLE ROW MOVEMENT'; execute immediate 'ALTER TABLE '||c.table_name||' SHRINK SPACE COMPACT'; execute immediate 'ALTER TABLE '||c.table_name||' SHRINK SPACE CASCADE'; execute immediate 'ALTER TABLE '||c.table_name||' DISABLE ROW MOVEMENT'; end loop; end; |
