 Database fragmentation and shrinking [message #125979] |
Thu, 30 June 2005 06:06  |
rodionov
Messages: 6
Registered: June 2005
|
Junior Member |
|
|
I've got a problem with my Oracle database. As I have understood my database fragmentation is high and that causes some problems with free space on a disk. Is it possible to do something with my tablespaces to make it un-fragmented?
I can give such example: I have tablespace which size is 32Gb Oracle Enterprise Manager shows that is is used 11Gb, but when I try to make a size of this tablespace 15 Gb an error message is shown: ORA-03297 file contains used data beyond requested RESIZE value.
I'm using Oracle database server 9.2.0.3.
I have to warn you that prioritized task is to unfragment my tablespace or maybe it is possible to do it for a whole database.
[Updated on: Thu, 30 June 2005 07:02] Report message to a moderator |
|
|
|
| Re: Database fragmentation and shrinking [message #125985 is a reply to message #125979] |
Thu, 30 June 2005 06:29  |
shettyshetty
Messages: 18
Registered: June 2005
Location: Malaysia
|
Junior Member |
|
|
Hi Rodionov,
Use the scripts provided below to know how much you can shrink the datafiles without getting ORA-03297 error.(Orginal author of the script is Oracle Guru Tom Kyte)
OR else use ALTER TABLE <name> MOVE... & ALTER INDEX <name> REBUILD commands to move to a new tablespace. After moving all the objects you can drop the tablespace.
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+);
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;
Sunil
|
|
|
|
| Re: Database fragmentation and shrinking [message #126037 is a reply to message #125979] |
Thu, 30 June 2005 11:36 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just to add...I'm not sure I'd use the term fragmentation in this case. To me fragmentation implies having "holes", probably unusable ones, in your files. What you describe sounds to me like simply a case of a tablespace that was created with a much larger size than is currently being used, and you have decided to shrink it to reclaim some of the extra space.
Hopefully you have checked that the tablespace wasn't created with that larger size intentionally, and that by reducing the space you won't be causing problems down the road when the size of your data grows.
Also, in cases of true fragmentation, use locally managed tablespaces and it won't be an issue. Oh and 10g has some new online tablespace shrink capabilities that you may want to investigate (I haven't used them, I've always used the move and rebuild approach described above).
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
