Oracle 10G compresses data files
Caveat: I am not an Oracle sysadmin other than personal installations. Take everything I say with a lot of salt.
I am assuming that you have created auto-expanding data files and they have been expanded from what you think they should contain. There is an ALTER DATABASE clause that will resize the file, here is an example from the Oracle SQL link:
ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;
However, I really don't think you want to do it blindly. A more suitable approach IMO would be to use the export command to dump the tables found in this datafile and then recreate the tablespace.
source to share
Here's a way, courtesy of Tom Keith, to get the block size of your db, specify how much space you can recover, and create alter ... commands to actually execute the database. Hope this helps,
http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html
Craig Glendenning
ALTER TABLESPACE .... RESIZE is only allowed outside of HWM. So you may have many unused segments below. Before this operation, do the following:
ALTER TABLE ... SHRINK SPACE on some tables in this tablespace / data file to reorganize the contents of the data files.
This may be a long task, but you can generate commands from SQL.
source to share
@Dave's answer about exporting and importing is the correct and best choice to free up space. Also there is no reduction command in Oracle, @Dave is right, but there is a resize command which, as @David says, " Datafiles can be changed up to the last used block" BUT there is one thing mentioned that free blocks in a datafile can be allocated thus
0101000001111000000000000000000001110000000000
|---------
where: 0 - free block 1 - used block
resizing the data file to the last used block, it becomes like this:
010100000111100000000000000000000111
|---------
But what about other free blocks in the data file? They are not available to other data files or the system itself.
If the data file was like this:
1111111111111100000000000000000
then resizing would be useful, but not in the previous version.
Anyway, to determine what size you can resize the data file, here is the script:
select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id
Don't worry that the script won't strip any used blocks from the data file.
source to share
For standard data files in Oracle, you cannot compress them. You will need to do something like:
- Move segments to a different tablespace, or export and release them
- Discard data file
- Create a new smaller data file
- Move segments back to the first tables or import them from a dump file
For a "bigfile" tablespace - that means CREATE BIGFILE TABLESPACE was used to create it - you can use ALTER TABLESPACE .. RESIZE ...
source to share