--// Lista o tamanho dos tablespace
SET LINES 9999
SELECT
d.status "Status",
d.tablespace_name "Nome",
d.contents "Tipo",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Tamanho (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') || '/' || TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Usado (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Usado %"
FROM
sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT
d.status "Status",
d.tablespace_name "Nome",
d.contents "Tipo",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Tamanho (M)",
TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') || '/' || TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Usado (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Usado %"
FROM
sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
ORDER BY 2;
--// Lista os Datafiles
SELECT name FROM v$datafile ORDER BY 1;
--// Lista um Tablespaces específico
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'TS_EMS5';
--// Altera o tamanho de um tablespace
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DTSHML\DFIND_EMS5.DBF' RESIZE 10G;