其它
1、查询表空间大小：
- 10g和11g：https://www.xmmup.com/oracle-11gchaxunbiaokongjiandaxiao.html
- 12c：https://www.xmmup.com/oracle-12cchaxunbiaokongjiandaxiao.html
2、查询数据文件（含临时和Undo）详情
https://www.xmmup.com/oraclechaxunshujuwenjianhanlinshiheundowenjianxiangqing.html
10g和11g查询表空间大小详细版
WITH wt1 AS
(SELECT ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
ts.bigfile,
df.ts_df_count
FROM dba_tablespaces ts,
(SELECT 'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM dba_data_files d
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT 'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM dba_temp_files d
GROUP BY TABLESPACE_NAME) df,
(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
FROM dba_free_space
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a, dba_tablespaces d
WHERE a.tablespace = d.tablespace_name
GROUP BY tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
t.TABLESPACE_NAME TS_Name,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 2) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
MAXSIZ,
2) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.FORCE_LOGGING,
t.CONTENTS,
t.EXTENT_MANAGEMENT,
t.SEGMENT_SPACE_MANAGEMENT,
t.RETENTION,
t.DEF_TAB_COMPRESSION,
t.bigfile,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT to_number('') TS#,
'ALL TS:' TS_Name,
round(SUM(t.all_bytes) / 1024 / 1024, 2) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 2) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
'' FORCE_LOGGING,
'' CONTENTS,
'' EXTENT_MANAGEMENT,
'' SEGMENT_SPACE_MANAGEMENT,
'' RETENTION,
'' DEF_TAB_COMPRESSION,
'' bigfile,
to_number('') ts_df_count
FROM wt1 t
order by CONTENTS,TS#
;
类似结果：
TS# TS_NAME TS_SIZE_M FREE_SIZE_M USED_SIZE_M USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING TS_DF_COUNT
---------- -------------------- ---------- ----------- ----------- ---------- ---------- ------------ ---------- ---------- -----------
0 SYSTEM 490 6 484 98.839 32 1.478 8192 LOGGING 1
1 UNDOTBS1 30 18 12 40.625 32 .037 8192 LOGGING 1
2 SYSAUX 330 8 322 97.5 32 .982 8192 LOGGING 1
3 TEMP 20 19 1 5 32 .003 8192 NOLOGGING 1
4 USERS 5 2 3 60 32 .009 8192 LOGGING 1
6 EXAMPLE 100 32 68 68.25 32 .208 8192 NOLOGGING 1
ALL TS: 975 85 891 91.333 192
7 rows selected.
10g和11g查询表空间大小简易版
WITH wt1 AS
(SELECT df.TABLESPACE_NAME,
df.all_bytes,
df.MAXSIZ,
nvl(fs.FREESIZ, 0) FREESIZ
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM dba_data_files d
GROUP BY TABLESPACE_NAME) df,
(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
FROM dba_free_space
GROUP BY TABLESPACE_NAME) fs
WHERE fs.TABLESPACE_NAME(+) = df.TABLESPACE_NAME)
SELECT t.TABLESPACE_NAME TS_Name,
round(t.all_bytes / 1024 / 1024, 2) ts_size_m,
round(t.freesiz / 1024 / 1024, 2) Free_Size_m,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024, 2) Used_Size_m,
round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 2) MAX_Size_free_g,
(t.all_bytes) ts_size,
(t.freesiz) Free_Size,
(t.all_bytes - t.FREESIZ) Used_Size,
(MAXSIZ) MAX_Size,
((MAXSIZ - (t.all_bytes - t.FREESIZ))) MAX_Size_free
FROM wt1 t;
12c及其以上版本查询表空间大小详细版
set pagesize 9999 line 9999
col TS_Name format a30
col PDBNAME format a15
col TS_NAME format a20
col TYPE format a12
col LOGGING format a10
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,
TYPE,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM (WITH wt1 AS (SELECT ts.CON_ID,
(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)
AND a.CON_ID = tS.con_id) TS#,
ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,'D',nvl(fs.FREESIZ, 0),'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM cdb_tablespaces ts,
(SELECT d.CON_ID,
'D' TYPE,
TABLESPACE_NAME,
count(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUP BY d.CON_ID,TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
'T',
TABLESPACE_NAME,
count(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM cdb_temp_files d
GROUP BY d.CON_ID,TABLESPACE_NAME) df,
(SELECT d.CON_ID,
TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUP BY d.CON_ID,TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace = d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUP BY d.CON_ID,tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
AND ts.CON_ID = fs.CON_ID(+))
SELECT T.CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL ELSE T.PDBNAME END) PDBNAME,
TS#,
t.TABLESPACE_NAME TS_Name,
CONTENTS type,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 2) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ,2) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT DISTINCT T.CON_ID,
'' PDBNAME,
to_number('') TS#,
'ALL TS:' TS_Name,
'' type,
round(SUM(t.all_bytes) / 1024 / 1024, 2) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 2) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
to_number('') ts_df_count
FROM wt1 t
GROUP BY rollup(CON_ID,PDBNAME)
)
ORDER BY CON_ID,TYPE,TS#;
