Посмотреть, как табличные пространства связаны с файлами данных можно так:
(DBA_FREE_SPACE, позволяет определить, сколько свободного места осталось в нашем табличном пространстве.)
BREAK ON tablespace_name SKIP 2
COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
COLUMN allocated_bytes FORMAT 9,999,999,999
COLUMN free_bytes FORMAT 9,999,999,999
SELECT a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;
Информацию об использовании пространства в ТП
без учета autoextensible можно посмотреть так:
SELECT /* + RULE */
df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes
UNION ALL
SELECT /* + RULE */
df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name, bytes_free, bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name, bytes_free, bytes_used) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used
ORDER BY 5 DESC;
SELECT /*+ first_rows */ d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
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.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY')
AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0),
NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0),
a.autoext,
(NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name ,
sum((ss.used_blocks*ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(u.bytes, 0) / 1024 / 1024,
NVL(u.bytes / a.bytes * 100, 0),
a.autoext,
NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024,
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes,
COUNT(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name,sum (bytes) bytes,status
from dba_undo_extents
WHERE status ='ACTIVE'
group by tablespace_name,status
UNION ALL
SELECT tablespace_name,
sum(bytes) bytes,
status from dba_undo_extents
WHERE status ='UNEXPIRED'
group by tablespace_name,status )
group by tablespace_name ) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%' ORDER BY 1
set lines 132
set pages 105
set pause off
set echo off
set feedb on
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
order by 1;
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
Иногда может наблюдаться замедление работы запросов при обращении к DBA_FREE_SPACE, а также появление чрезмерного ожидания операций ввода-вывода.
Для решения этой проблемы нужно:
Удалить содержимое корзины
connect / as sysdba
select count(*) from dba_recyclebin;
purge dba_recyclebin;
Собрать статистику для X$KTFBUE
connect / as sysdba
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);
Проверяем:
column owner format a6
column table_name format a10
column last_anal format a10
SELECT owner, table_name, last_analyzed FROM dba_tab_statistics WHERE table_name='X$KTFBUE';
Размер табличных пространств в блоках
select * from DBA_TABLESPACE_USAGE_METRICS;
Переводим блоки в Гигабайты
SELECT tbm.TABLESPACE_NAME,
round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
--WHERE tbm.tablespace_name = 'tablespace_name'
В мегабайтах
SELECT a.tablespace_name,
ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
ROUND(a.used_percent, 2) AS "USED_PERCENT"
FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
DBA_TABLESPACES b
ON a.tablespace_name = b.tablespace_name;
Для версии 11gR1:
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERCM,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m,
dba_tablespaces t,
dba_data_files d,
dba_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND tt.metrics_name='Tablespace Space Usage'
AND tt.object_name is null
GROUP BY m.tablespace_name
ORDER BY 2 desc;
Для версии 11gR2 и выше:
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERCM,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m,
dba_tablespaces t,
dba_data_files d,
dba_tablespace_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND tt.tablespace_name=d.tablespace_name
AND tt.metrics_name='Tablespace Space Usage'
GROUP BY m.tablespace_name
ORDER BY 2 desc;
В блоках:
select TABLESPACE_ID, NAME, TABLESPACE_SIZE, TABLESPACE_USEDSIZE, RTIME
from DBA_HIST_TBSPC_SPACE_USAGE, v$tablespace
where TABLESPACE_ID=TS#
order by RTIME, dba_hist_tbspc_space_usage.tablespace_id;
В мегабайтах:
select a.TABLESPACE_ID,
c.NAME,
ROUND((a.TABLESPACE_USEDSIZE * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
a.RTIME
from DBA_HIST_TBSPC_SPACE_USAGE a,
DBA_TABLESPACES b,
v$tablespace c
where a.TABLESPACE_ID=c.TS#
and c.name = b.tablespace_name
order by a.RTIME, a.tablespace_id;
(DBA_FREE_SPACE, позволяет определить, сколько свободного места осталось в нашем табличном пространстве.)
BREAK ON tablespace_name SKIP 2
COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
COLUMN allocated_bytes FORMAT 9,999,999,999
COLUMN free_bytes FORMAT 9,999,999,999
SELECT a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;
Информацию об использовании пространства в ТП
без учета autoextensible можно посмотреть так:
SELECT /* + RULE */
df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes
UNION ALL
SELECT /* + RULE */
df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name, bytes_free, bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name, bytes_free, bytes_used) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used
ORDER BY 5 DESC;
Или так:
SELECT /*+ first_rows */ d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
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.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY')
AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0),
NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0),
a.autoext,
(NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name ,
sum((ss.used_blocks*ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(u.bytes, 0) / 1024 / 1024,
NVL(u.bytes / a.bytes * 100, 0),
a.autoext,
NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024,
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes,
COUNT(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name,sum (bytes) bytes,status
from dba_undo_extents
WHERE status ='ACTIVE'
group by tablespace_name,status
UNION ALL
SELECT tablespace_name,
sum(bytes) bytes,
status from dba_undo_extents
WHERE status ='UNEXPIRED'
group by tablespace_name,status )
group by tablespace_name ) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%' ORDER BY 1
Учесть autoextensible можно так:
set lines 132
set pages 105
set pause off
set echo off
set feedb on
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
order by 1;
Или так:
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
Иногда может наблюдаться замедление работы запросов при обращении к DBA_FREE_SPACE, а также появление чрезмерного ожидания операций ввода-вывода.
Для решения этой проблемы нужно:
Удалить содержимое корзины
connect / as sysdba
select count(*) from dba_recyclebin;
purge dba_recyclebin;
Собрать статистику для X$KTFBUE
connect / as sysdba
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);
Проверяем:
column owner format a6
column table_name format a10
column last_anal format a10
SELECT owner, table_name, last_analyzed FROM dba_tab_statistics WHERE table_name='X$KTFBUE';
Начиная с версии 11g можно воспользоваться следующим представлением:
Размер табличных пространств в блоках
select * from DBA_TABLESPACE_USAGE_METRICS;
Переводим блоки в Гигабайты
SELECT tbm.TABLESPACE_NAME,
round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
--WHERE tbm.tablespace_name = 'tablespace_name'
В мегабайтах
SELECT a.tablespace_name,
ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
ROUND(a.used_percent, 2) AS "USED_PERCENT"
FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
DBA_TABLESPACES b
ON a.tablespace_name = b.tablespace_name;
Можно связать с представлением dba_tablespace_thresholds:
Для версии 11gR1:
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERCM,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m,
dba_tablespaces t,
dba_data_files d,
dba_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND tt.metrics_name='Tablespace Space Usage'
AND tt.object_name is null
GROUP BY m.tablespace_name
ORDER BY 2 desc;
Для версии 11gR2 и выше:
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERCM,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m,
dba_tablespaces t,
dba_data_files d,
dba_tablespace_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
AND d.tablespace_name=t.tablespace_name
AND tt.tablespace_name=d.tablespace_name
AND tt.metrics_name='Tablespace Space Usage'
GROUP BY m.tablespace_name
ORDER BY 2 desc;
Исторические данные по росту ТП из AWR
В блоках:
select TABLESPACE_ID, NAME, TABLESPACE_SIZE, TABLESPACE_USEDSIZE, RTIME
from DBA_HIST_TBSPC_SPACE_USAGE, v$tablespace
where TABLESPACE_ID=TS#
order by RTIME, dba_hist_tbspc_space_usage.tablespace_id;
В мегабайтах:
select a.TABLESPACE_ID,
c.NAME,
ROUND((a.TABLESPACE_USEDSIZE * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
a.RTIME
from DBA_HIST_TBSPC_SPACE_USAGE a,
DBA_TABLESPACES b,
v$tablespace c
where a.TABLESPACE_ID=c.TS#
and c.name = b.tablespace_name
order by a.RTIME, a.tablespace_id;
Исторические данные роста базы данных:
select SUM(ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2)) AS "DATABASE_SIZE(MB)",
a.RTIME
from DBA_HIST_TBSPC_SPACE_USAGE a,
DBA_TABLESPACES b,
v$tablespace c
where a.TABLESPACE_ID=c.TS#
and c.name = b.tablespace_name
group by a.RTIME
order by a.RTIME;
Только это будет без redo_size from sys.v_$log и controlfile_size from v$controlfile
А общий размер базы будет таким:
select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual
Или в гигабайтах:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Файловая система:
create tablespace TEST
datafile '/u02/oradata/TESTDB/test_01.dbf' size 128M autoextend on next 128M maxsize unlimited,
'/u02/oradata/TESTDB/test_02.dbf' size 128M autoextend on next 128M maxsize unlimited,
'/u02/oradata/TESTDB/test_03.dbf' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
ASM:
create tablespace TEST
datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited,
'+DATA' size 128M autoextend on next 128M maxsize unlimited,
'+DATA' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
OMF:
create tablespace TEST
datafile size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'TEST';
Файловая система:
alter tablespace TEST add datafile '/u02/oradata/TESTDB/test_04.dbf' size 128M autoextend on next 128M maxsize unlimited;
ASM:
alter tablespace TEST add datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited;
OMF:
alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited;
Файловая система:
alter tablespace TEST drop datafile '/u02/oradata/TESTDB/test_04.dbf'
ASM:
alter tablespace TEST drop datafile '+DATA/test_df4.f';
OMF:
alter tablespace TEST drop datafile 'C:\APP\ORACLE\ORADATA\TESTDB\DATAFILE\O1_MF_TEST_FC98Q9LO_.DBF';
drop tablespace TEST;
drop tablespace TEST including contents and datafiles;
set lines 556
set trimout on
set space 1
set tab off
set pagesize 10000 linesize 300 tab off
col tablespace_name format A22 heading "Tablespace"
col ts_type format A13 heading "TS Type"
col segments format 999999 heading "Segments"
col files format 9999
col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
col used_mb format 9,999,990.000 heading "Used Space|(Mb)"
col Free_mb format 999,990.000 heading "Free Space|(Mb)"
col used_pct format 999 heading "Used|%"
col max_ext_mb format 99,999,990.000 heading "Max Size|(Mb)"
col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
col max_used_pct format 999 heading "Max Used|(%)"
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt files,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (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 d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
0,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 1
/
prompt * Tablespace (TS) types:
prompt . - LM/DM - Local/Dictionary Managed
prompt . - SYS/UNI - SYStem/UNIform Extent Management (LM only)
prompt . - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)
select SUM(ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2)) AS "DATABASE_SIZE(MB)",
a.RTIME
from DBA_HIST_TBSPC_SPACE_USAGE a,
DBA_TABLESPACES b,
v$tablespace c
where a.TABLESPACE_ID=c.TS#
and c.name = b.tablespace_name
group by a.RTIME
order by a.RTIME;
Только это будет без redo_size from sys.v_$log и controlfile_size from v$controlfile
А общий размер базы будет таким:
select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual
Или в гигабайтах:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Создание табличных пространств:
Файловая система:
create tablespace TEST
datafile '/u02/oradata/TESTDB/test_01.dbf' size 128M autoextend on next 128M maxsize unlimited,
'/u02/oradata/TESTDB/test_02.dbf' size 128M autoextend on next 128M maxsize unlimited,
'/u02/oradata/TESTDB/test_03.dbf' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
ASM:
create tablespace TEST
datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited,
'+DATA' size 128M autoextend on next 128M maxsize unlimited,
'+DATA' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
OMF:
create tablespace TEST
datafile size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
Просмотр информации о табличном пространстве:
select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'TEST';
Добавление файлов данных в табличное пространство:
Файловая система:
alter tablespace TEST add datafile '/u02/oradata/TESTDB/test_04.dbf' size 128M autoextend on next 128M maxsize unlimited;
ASM:
alter tablespace TEST add datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited;
OMF:
alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited;
Удаление файлов данных из табличных пространств:
Файловая система:
alter tablespace TEST drop datafile '/u02/oradata/TESTDB/test_04.dbf'
ASM:
alter tablespace TEST drop datafile '+DATA/test_df4.f';
OMF:
alter tablespace TEST drop datafile 'C:\APP\ORACLE\ORADATA\TESTDB\DATAFILE\O1_MF_TEST_FC98Q9LO_.DBF';
Удаление табличных пространств:
drop tablespace TEST;
drop tablespace TEST including contents and datafiles;
Подробная информация о табличных пространствах:
set lines 556
set trimout on
set space 1
set tab off
set pagesize 10000 linesize 300 tab off
col tablespace_name format A22 heading "Tablespace"
col ts_type format A13 heading "TS Type"
col segments format 999999 heading "Segments"
col files format 9999
col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
col used_mb format 9,999,990.000 heading "Used Space|(Mb)"
col Free_mb format 999,990.000 heading "Free Space|(Mb)"
col used_pct format 999 heading "Used|%"
col max_ext_mb format 99,999,990.000 heading "Max Size|(Mb)"
col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
col max_used_pct format 999 heading "Max Used|(%)"
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt files,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (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 d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
0,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 1
/
prompt * Tablespace (TS) types:
prompt . - LM/DM - Local/Dictionary Managed
prompt . - SYS/UNI - SYStem/UNIform Extent Management (LM only)
prompt . - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)
