Tom at 15:00, 30 May 2016

2016-05-30T15:00:37Z

Tom: Created page with "See the amount of free space in each datafile: select df.file_name, round(max(df.bytes) / 1048576) file_mb, round(sum(dfs.bytes) / 1048576) file_free_mb..."

2016-05-30T12:21:10Z

Created page with "See the amount of free space in each datafile: <source lang="sql"> select df.file_name, round(max(df.bytes) / 1048576) file_mb, round(sum(dfs.bytes) / 1048576) file_free_mb..."

New page

See the amount of free space in each datafile:
<source lang="sql">
select df.file_name, round(max(df.bytes) / 1048576) file_mb, round(sum(dfs.bytes) / 1048576) file_free_mb
from dba_data_files df
, dba_free_space dfs
where df.tablespace_name = '<tablespace_name>'
and dfs.file_id = df.file_id
group by df.file_name;
</source>

What is the last block and how much can we shrink a datafile?
<source lang="sql">
select file_name, owner, segment_name, partition_name, segment_type, round(file_bytes / 1048576) data_file_mb, round((block_id + blocks) * block_size / 1048576) object_end_mb
, 'alter database datafile ''' || file_name || ''' resize ' || round((block_id + blocks) * block_size / 1048576) || 'M;'
from (
select df.file_name file_name
, df.bytes file_bytes
, rank() over (partition by df.file_name order by block_id desc) rang
, de.owner
, de.segment_name
, de.partition_name
, de.segment_type
, de.block_id
, de.blocks
, ts.block_size
from dba_data_files df
, dba_extents de
, dba_tablespaces ts
where df.tablespace_name = '<tablespace_name>'
and de.file_id = df.file_id
and ts.tablespace_name = df.tablespace_name)
where rang < 2;
</source>
Use rang < 11 to see the 10 last segments for each datafile.

← Older revision		Revision as of 15:00, 30 May 2016
Line 33:		Line 33:
	</source>		</source>
	Use rang < 11 to see the 10 last segments for each datafile.		Use rang < 11 to see the 10 last segments for each datafile.
		+
		+
		+	Rebuild statements:
		+	<source lang="sql">
		+	alter table TABLE_NAME move;
		+	alter index INDEX_NAME rebuild online;
		+	alter index INDEX_NAME rebuild partition PARTITION online;
		+	</source>

Reclaiming free space in datafiles - Revision history

Tom at 15:00, 30 May 2016

Tom: Created page with "See the amount of free space in each datafile: select df.file_name, round(max(df.bytes) / 1048576) file_mb, round(sum(dfs.bytes) / 1048576) file_free_mb..."