_____________________________________________________________________________________

set pages 1000
set lines 200
_____________________________________________________________________________________

TABLESPACE SIZE OVERVIEW
========================
describe DBA_SEGMENTS

select TABLESPACE_NAME, round(sum(BYTES/1024/1024)) MB
from DBA_SEGMENTS
group by TABLESPACE_NAME
order by 1;

--------------------
describe DBA_DATA_FILES

col "Tablespace"   for a22
col "Used MB"      for 99,999,999
col "Free MB"      for 99,999,999
col "Total MB"     for 99,999,999

select df.TABLESPACE_NAME "Tablespace",
TOTALUSEDSPACE "Used MB",
(df.TOTALSPACE - tu.TOTALUSEDSPACE) "Free MB",
df.TOTALSPACE "Total MB",
round(100 * ( (df.TOTALSPACE - tu.TOTALUSEDSPACE)/ df.TOTALSPACE))
"Pct. Free"
from
(select TABLESPACE_NAME,
round(sum(BYTES) / 1048576) TOTALSPACE
from DBA_DATA_FILES
group by TABLESPACE_NAME) df,
(select round(sum(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME
from dba_segments
group by TABLESPACE_NAME) tu
where df.TABLESPACE_NAME = tu.TABLESPACE_NAME order by 1;
_____________________________________________________________________________________

-- -----------------------------------------------------------------------------------
-- File Name    : http://oracle-base.com/dba/monitoring/ts_free_space.sql
-- Author       : Tim Hall
-- Description  : Displays a list of tablespaces and their used/full status.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @ts_free_space.sql
-- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql
-- -----------------------------------------------------------------------------------
SET PAGESIZE 140
COLUMN used_pct FORMAT A11

SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
       RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name
       )
ORDER BY tablespace_name;
_____________________________________________________________________________________

DBA_SEGMENTS
============
describe DBA_SEGMENTS

set pages 1000
set lines 200

select TABLESPACE_NAME, round(sum(BYTES/1024/1024)) MB
from DBA_SEGMENTS
group by TABLESPACE_NAME
order by 1;
_____________________________________________________________________________________

TABLESPACE AND DATAFILE
=======================
column tsname format a20
column filename format a75
select t.NAME "tsname", f.NAME "filename", round(f.BYTES/1024/1024) "MB"
from V$TABLESPACE t, V$DATAFILE f
where t.TS# = f.TS#
order by 1, 2;

column tablespace_name format a20
column file_name format a75
select tablespace_name, file_name, round(bytes/1024/1024) "MB", autoextensible "AE"
from dba_data_files
order by 1;
_____________________________________________________________________________________

RESIZE DATAFILE
===============
select 'ALTER DATABASE DATAFILE ''' || name || ''' RESIZE 2G;' "STMT" from v$datafile order by 1;
_____________________________________________________________________________________

TEMP TABLESPACE
===============

select * from dba_temp_files;
select round(bytes/1024/1024/1024) "GB" from dba_temp_files;
select round(maxbytes/1024/1024/1024) "MAX_GB" from dba_temp_files;

select 'ALTER DATABASE TEMPFILE ''' || name || ''' AUTOEXTEND ON MAXSIZE 20480M;' "STMT" from v$tempfile order by 1;
_____________________________________________________________________________________

DETERMINE SYSAUX USAGE (OCCUPANTS)
==================================
describe v$sysaux_occupants

select occupant_name, space_usage_kbytes
from v$sysaux_occupants
order by 1;

select occupant_name, space_usage_kbytes
from v$sysaux_occupants
order by 2 desc;
_____________________________________________________________________________________

SAMPLES
=======
create tablespace hajo datafile size 10m;

create user hajo identified by hajo default tablespace hajo;
grant connect, resource to hajo;
grant select on sys.dba_tables to hajo;
conn hajo/hajo
create table hajo as select * from sys.dba_tables;
create table hajo2 as select * from sys.dba_tables;

insert into hajo (select * from hajo);
insert into hajo (select * from hajo);
insert into hajo (select * from hajo);

drop table hajo;
create table hajo as select * from sys.dba_tables;

select name, bytes/1024/1024 "MB" from v$datafile;

drop user hajo cascade;
drop tablespace hajo including contents and datafiles;
_____________________________________________________________________________________