How to manage space

See How to Transportable Tablespaces for other useful tips
See Tablespaces for infos on tablespaces

Unused space over segments
    
declare
  TOTAL_BLOCKS              number;
  TOTAL_BYTES               number;
  UNUSED_BLOCKS             number;
  UNUSED_BYTES              number;
  LAST_USED_EXTENT_FILE_ID  number;
  LAST_USED_EXTENT_BLOCK_ID number;
  LAST_USED_BLOCK           number;

begin
  dbms_space.unused_space('ALDO','SYS_LOB0000065630C00003$$','LOB',
     TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
     LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
     LAST_USED_BLOCK);

    dbms_output.put_line('OBJECT_NAME               = EMP');
    dbms_output.put_line('-----------------------------------');
    dbms_output.put_line('TOTAL_BLOCKS              = '||TOTAL_BLOCKS);
    dbms_output.put_line('TOTAL_BYTES               = '||TOTAL_BYTES);
    dbms_output.put_line('UNUSED_BLOCKS             = '||UNUSED_BLOCKS);
    dbms_output.put_line('UNUSED BYTES              = '||UNUSED_BYTES);
    dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||LAST_USED_EXTENT_FILE_ID);
    dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
    dbms_output.put_line('LAST_USED_BLOCK           = '||LAST_USED_BLOCK);

end;
/

Determine the total disk space for a user

SELECT  sum(bytes)/power(1024, 0) bytes, sum(bytes)/power(1024, 1) Kb, sum(bytes)/power(1024, 2) Mb, sum(bytes)/power(1024, 3) Giga, 
sum(bytes)/power(1024, 4) Tera
FROM DBA_DATA_FILES d 
WHERE d.tablespace_name in 
	  (select distinct tablespace_name from  dba_segments  Where OWNER = upper('&username'));

Determine the total disk space for tables and relative indexes

select sum(bytes) bytes_total, sum(bytes)/1024/1024 MB_total, sum(bytes)/1024/1024/1024 GB_total, 
sum(bytes)/1024/1024/1024/1024 TB_total from (
	select * from dba_segments where owner||'.'||segment_name in ('VCO.G_TRANCHE', 'VCO.G_DTL_MOV_CONTABILE', 'VCO.DRIVER_EVENTO')
	and owner='VCO'
	and segment_type in ('TABLE', 'TABLE PARTITION')
	union all
	select s.* from dba_segments s, dba_indexes i
	where s.owner = i.owner and s.segment_name = i.index_name
	and i.table_owner||'.'||i.table_name in ('VCO.G_TRANCHE', 'VCO.G_DTL_MOV_CONTABILE', 'VCO.DRIVER_EVENTO')
)

Determine all datafiles used by a user

SELECT  * FROM DBA_DATA_FILES d 
WHERE d.tablespace_name in 
	  (select distinct tablespace_name from  dba_segments  Where OWNER = upper('&username'))
order by 1;

Determine all tablespaces used by a user

select distinct tablespace_name from  dba_segments  Where OWNER = upper('&username') order by 1;

See which objects for a user are on a tablespace

select * from  dba_segments  Where OWNER = upper('&username') and tablespace_name = upper('&tablespace_name')

Resizing a datafile

alter database datafile '/oradata/al11/users.dbf' resize 100M;

Determine the total allocated space

select a.tablespace_name, trunc(sum(a.bytes/1024/1024))  as Allocated_MB
, sum(a.bytes)/1024/1024/1024 GB_total, 
sum(a.bytes)/1024/1024/1024/1024 TB_total
from  dba_extents a
group by a.tablespace_name 
order by a.tablespace_name;

Database now has 100 data elements, how much space will it need to feet 150?

select 
--c.tablespace_name, c.mb_allocated,  c.MB_used, 
--c.MB_estimated, c.space_needed
*
from (
	select b.tablespace_name, b.bytes_allocated
	, decode(b.bytes_used, '', 'Not in source database', b.bytes_used) bytes_used
	, decode(b.bytes_estimated, '', 'Not in source database', to_char(b.bytes_estimated, 'FM99999999999999999999')) bytes_estimated
	, b.mb_allocated
	, decode(b.mb_used, '', 'Not in source database', to_char(b.mb_used, 'FM999999999999999999')) mb_used
	, decode(b.bytes_used, '', 'Not in source database',
	  		 decode(sign(bytes_estimated - bytes_allocated), -1, to_char(bytes_estimated * 100 / bytes_allocated, '99') || '% will be used, no space needed', 'Need '||to_char((bytes_estimated - bytes_allocated)/1024/1024, 'FM999999999999') || 'MB, ' || to_char((bytes_estimated - bytes_allocated)/1024/1024/1024, 'FM99999.99') || 'Giga')) space_needed
	, decode(bytes_estimated, '', 'Not in source database', to_char(bytes_estimated/1024/1024, 'FM99999999999999999')) MB_estimated
	from (
		select a.* 
		, (:polizze_tot * bytes_used / :polizze_attuali) bytes_estimated
		, bytes_allocated/1024/1024 MB_allocated
		, bytes_used/1024/1024 MB_used
		from (
			select distinct tablespace_name
			, (select sum(bytes) bytes from dba_data_files where tablespace_name = s.tablespace_name) bytes_allocated
			--IF SOURCE DATABASE IL LOCAL, REMOVE DBLINK
			, (select sum(bytes) from dba_segments@pvgh1_sys.world where tablespace_name = s.tablespace_name) bytes_used
			from dba_segments s
			where owner in ('VCO')
		) a
	) b
) c

Estimate table/partition size
select b.*
, decode(num_rows, 0, 0, /* your estimated rows*/(86425816*2) * blocks / num_Rows) * added * block_size size_est
from (
	select a.*
	, decode(calc, 0, 0, blocks * block_size/calc) added 
	from (
	  select 
	   partition_name, last_analyzed, num_rows, avg_row_len, blocks, 8192 block_size
	  , (avg_row_len * num_rows * (1 + PCT_FREE/100)) calc from dba_tab_partitions
	  where table_name = 'EVENT_BAL_IMPACTS_T'
	) a
) b

Manage Partitioned Tables - Look for uncorrect data spreading

select b.*
, decode(sign(curr_gap_in_rows - row_gap_allowed), -1, 0, sign(curr_gap_in_rows - row_gap_allowed)) GAP_TOO_HIGH 
, decode(row_gap_allowed, 0, 0, curr_gap_in_rows * perc_of_gap_allowed / row_gap_allowed) curr_Gap_in_percentual
from (
	--****************** 
	--* Query 3 / begin* 
	--****************** 
	select a.*
	, (perc_of_gap_allowed  * optimal_avg_rows_for_part / 100) row_gap_allowed
	, (abs(optimal_avg_rows_for_part - num_rows_for_partition)) curr_gap_in_rows
	from (
		--****************** 
		--* Query 2 / begin* 
		--****************** 
		select p.table_owner, p.table_name, partition_name, nvl(num_rows, 0) num_rows_for_partition
		  , nvl(tot_rows, 0) tot_rows_for_entire_table
		  , optimal_avg_rows_for_part, num_partitions num_part_for_entire_table, partition_position
		  --/**********************************/
		  , /*Place here allowed gap parc. => */ 50 perc_of_gap_allowed
		  --/**********************************/
		from 
		dba_tab_partitions p
		, (
		  --****************** 
		  --* Query 1 / begin* 
		  --****************** 
		  select table_owner, table_name, nvl(sum(num_rows), 0) tot_rows, count(*) num_partitions
		  ,  decode(count(*), 0, 0, (nvl(sum(num_rows), 0) / count(*))) optimal_avg_rows_for_part
			from dba_tab_partitions
			group by table_owner, table_name
		  --****************** 
		  --* Query 1 / end  * 
		  --****************** 
		) n
		where p.table_owner = n.table_owner and p.table_name = n.table_Name
		--****************** 
		--* Query 2 / end  * 
		--******************
	) a
	--****************** 
	--* Query 3 / end  * 
	--******************
) b
where not table_owner in ('SYSTEM')
order by 1, 2, partition_position

Check remaining space on SYSTEM tablespace

Select max(bytes)/1024/1024 MB_FREE from dba_free_space where tablespace_name='SYSTEM';

Index Organized Table - determine space
select segment_name, bytes/1024/1024/1024 from dba_segments where segment_name in (
	select object_name from dba_objects where object_id = (
		select object_id+1 from dba_objects 
		where object_name='COSTEDEVENT'
		and object_type = 'TABLE'
		and owner='GENEVA_ADMIN'
	) 
)