oracle經常需要查數據庫表空間大小，使用率，加表空間等，這里總結我經常使用的語句。
一、數據表空間相關：
查詢所有表空間的使用情況：
SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.99'
) USE,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) "Used %"
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.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.99') USE,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"
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';
1. 查詢表空間剩余字節大小--注意替換數據表空間或臨時表空間（大寫）名稱或查詢時直接輸入
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS "FREE SPACE(M)"
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '&tablespace_name'
GROUP BY TABLESPACE_NAME;
注：如果是臨時表空間，請查詢DBA_TEMP_FREE_SPACE
SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SPACE(M)"
FROM DBA_TEMP_FREE_SPACE
WHERE TABLESPACE_NAME = '&tablespace_name';
2. 查詢表空間所有數據文件路徑
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tablespace_name';
注：如果是臨時表空間，請查詢DBA_TEMP_FILES
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = '&tablespace_name';
3、查看表空間是否具有自動擴展的能力（沒有包含臨時表空間）
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
4、查詢所有臨時表空間是否自增長，next_extent有沒有值，如果有就是可以自動擴展
SELECT * FROM dba_tablespaces t where t.CONTENTS='TEMPORARY';
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;
5、查看某用戶的默認數據表空間：
select * from dba_users where username = '大寫'
二、數據表空間：
1、查看所有表名稱、路徑、是否自動增長：
語句一：
SQL> set line 200;
col file_name format a40;
col tablespace_name format a35;
select file_name,autoextensible,maxbytes,increment_by from dba_data_files;
語句二：
select * from dba_data_files;
語句三：
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
2、修改數據表文件無限增長
alter database datafile '/u01/oracle/oradata/srmcps/srmcps_data.dbf' autoextend on next 64m maxsize unlimited;
3、增加表空間
ALTER TABLESPACE srmhdl_data ADD DATAFILE '/home/oracle/oracle/oradata/srmsdpx/srmhdl_data02.dfg' size 64m autoextend on next 64m maxsize unlimited;
4、表空間數據文件位置、大小、使用率
語句一：
SELECT tablespace_name,
100 * (sum_max - sum_alloc + nvl(sum_free, 0)) / sum_max AS capa_per,
(sum_max - sum_alloc + nvl(sum_free, 0)) / 1024 / 1024 AS capa_free,
(sum_alloc - nvl(sum_free, 0)) / 1024 / 1024 as capa_used,
sum_max / 1024 / 1024 as capa_max,
100 * nvl(sum_free, 0) / sum_alloc As per,
nvl(sum_free, 0) / 1024 / 1024 as free,
(sum_alloc - nvl(sum_free, 0)) / 1024 / 1024 as used,
sum_alloc / 1024 / 1024 as max
FROM (SELECT tablespace_name,
sum(bytes) AS sum_alloc,
sum(decode(maxbytes, 0, bytes, maxbytes)) AS sum_max
FROM dba_data_files
GROUP BY tablespace_name),
(SELECT tablespace_name AS fs_ts_name, sum(bytes) AS sum_free
FROM dba_free_space
GROUP BY tablespace_name)
WHERE tablespace_name = fs_ts_name(+)
order by 2, 3;
語句二（列出當前環境所有數據表空間的使用情況）：
select
b.file_name "物理文件名",
b.tablespace_name "表空間",
b.bytes/1024/1024 "大小M",
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
語句三：
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空閑空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
語句四，架構推薦（列出數據表空間的使用率與空閑表空間）：
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(G)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(G)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空閑空間(G)",
F.MAX_BYTES "最大塊(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024*1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024*1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE where tablespace_name<> 'USERS'
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024*1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD where dd.tablespace_name<> 'USERS'
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
查看當前表空間使用總量：
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查詢表空間的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
查看某用戶表空間名稱：
select username,default_tablespace from dba_users where username='SCOTT';--用戶名需要大寫
select username,default_tablespace from dba_users where username='DBYW';
select username,temporary_tablespace from dba_users where username='DBYW';
--查看數據表空間的使用情況（推薦）
Select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,
space-nvl(free_space,0) "used_space",round((1-nvl(free_space,0)/space)*100,2) "used_rate(%)",
Free_space "free_space(M)"
From (select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks
From dba_data_files
Group by tablespace_name) d,
(select tablespace_name,round(sum(bytes)/(1024*1024),2) free_space
From dba_free_space
Group by tablespace_name) f
Where d.tablespace_name=f.tablespace_name(+)
Union all
Select d.tablespace_name,space "sum_space(M)",blocks sum_blocks,used_space "used_space(M)",round(nvl(used_space,0)/space*100,2) "used_rate(%)",
Nvl(free_space,0) "free_space(m)"
From
(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,
Sum(blocks) blocks
From dba_temp_files
Group by tablespace_name) d,
(select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,round(sum(bytes_free)/(1024*1024),2) free_space
From v$temp_space_header
Group by tablespace_name) f
Where d.tablespace_name=f.tablespace_name(+)
order by tablespace_name;
--查看表空間的真實使用情況（推薦）
set linesize 500 pagesize 500
col tablespace_name format a25
col TP_REAL_GB format a15
col TP_REAL_FREE_GB format a20
select all_tp.TP_NAME "TABLESPACE_NAME",
to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') "TP_REAL_GB",
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') "TP_REAL_FREE_GB",
(to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') -
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099')) "TP_REAL_USED_GB",
to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
2),
'FM9999990.0099') || '%' "TP_FREE_RATING"
from ( --表空間總大小
select sum(TP_SIZE_KB) TP_ALL_SIZE_KB, TP_NAME
from ( --自動擴展總大小(maxbytes/bytes取兩者最大值)
select decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes,
ddf.bytes) / 1024 TP_SIZE_KB,
ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES'
union all
--非自動總擴展大小
select BYTES / 1024 TP_SIZE_KB, ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'NO') TP_ALL_SIZE
group by TP_NAME) all_tp,
( --表空間空閑的總大小
select sum(TPF_SIZE_KB) TP_FREE_SIZE_KB, TP_NAME
from ( --數據文件已經分配，空閑空間
select dfs.bytes / 1024 TPF_SIZE_KB,
dfs.tablespace_name TP_NAME
from DBA_FREE_SPACE dfs
union all
--數據文件自動擴展(若maxbytes大于bytes，取差值；若maxbytes小于等于bytes，取0)
select decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes - ddf.bytes,
0) / 1024 TPF_SIZE_KB,
ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES') TP_FREE_SIZE
group by TP_NAME) free_tp
where all_tp.TP_NAME = free_tp.TP_NAME(+);
中文
select all_tp.TP_NAME "表空間名",
to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') "表空間實際大小",
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') "表空間實際剩余大小",
(to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') -
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099')) "表空間實際使用大小",
to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
2),
'FM9999990.0099') || '%' "表空間剩余比率"
from ( --表空間總大小
select sum(TP_SIZE_KB) TP_ALL_SIZE_KB, TP_NAME
from ( --自動擴展總大小(maxbytes/bytes取兩者最大值)
select decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes,
ddf.bytes) / 1024 TP_SIZE_KB,
ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES'
union all
--非自動總擴展大小
select BYTES / 1024 TP_SIZE_KB, ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'NO') TP_ALL_SIZE
group by TP_NAME) all_tp,
( --表空間空閑的總大小
select sum(TPF_SIZE_KB) TP_FREE_SIZE_KB, TP_NAME
from ( --數據文件已經分配，空閑空間
select dfs.bytes / 1024 TPF_SIZE_KB,
dfs.tablespace_name TP_NAME
from DBA_FREE_SPACE dfs
union all
--數據文件自動擴展(若maxbytes大于bytes，取差值；若maxbytes小于等于bytes，取0)
select decode(sign(ddf.maxbytes - ddf.bytes),
1,
ddf.maxbytes - ddf.bytes,
0) / 1024 TPF_SIZE_KB,
ddf.tablespace_name TP_NAME
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES') TP_FREE_SIZE
group by TP_NAME) free_tp
where all_tp.TP_NAME = free_tp.TP_NAME(+);
--查看表空間使用情況（推薦）
ora-00972:identifier is too long問題處理
export NLS_LANG='american_america.AL32UTF8'
set line 2500;
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(G)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(G)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空閑空間(G)",
F.MAX_BYTES "最大塊(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024*1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024*1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE where tablespace_name<> 'USERS'
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024*1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD where dd.tablespace_name<> 'USERS'
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
SELECT a.tablespace_name "表空間名", total / 1024 / 1024 "表空間大小單位M", free / 1024 / 1024 "表空間剩余大小單位M", (total - free) / 1024 / 1024 "表空間使用大小單位M",
Round((total - free) / total, 4 ) * 100 "使用率 [[%]]"
FROM
(SELECT tablespace_name, Sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, Sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
SELECT a.tablespace_name "表空間名",
total "表空間大小",
free "表空間剩余大小",
(total - free) "表空間使用大小",
total / (1024 * 1024 * 1024) "表空間大小(G)",
free / (1024 * 1024 * 1024) "表空間剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
增加表空間大小的四種方法
Meathod1：給表空間增加數據文件
ALTER TABLESPACE App_data ADD DATAFILE
'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' SIZE 50M;
Meathod2：新增數據文件，并且允許數據文件自動增
ALTER TABLESPACE srmhdl_data ADD DATAFILE '/u01/oracle/oradata/srmhdl/srmhdl_data02.dfg' size 64m autoextend on next 64m maxsize unlimited;
如果有多個可以使用如下命令：
alter tablespace venn add datafile '/u01/oracle/oradata/srmhdl/srmhdl_data02.dfg'
size 10M
autoextend on next 100M
maxsize 500M,
'/u01/oracle/oradata/srmhdl/srmhdl_data03.dfg'
size 10M
autoextend on next 100M
maxsize 500M ;
Meathod3：允許已存在的數據文件自動增長
ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod4：手工改變已存在數據文件的大小
ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP02.DBF' RESIZE 100M;
歸檔空間大小增長：
select * from v$recovery_file_dest;
刪除臨時表空間(徹底刪除)：
SQL> drop tablespace orcl_data including contents and datafiles cascade constraints;
統計表占用空間大小參數:
expdp srmhdl/srm999db@srmhdl schemas=srmhdl directory=dir_dump estimate_only=y estimate=statistics
查詢除系統用戶外的用戶數據庫表空間的占用情況：
select *
from (select owner || '.' || tablespace_name name, sum(b) g
from (select owner,
t.segment_name,
t.partition_name,
round(bytes / 1024 / 1024 / 1024, 2) b,
tablespace_name
from dba_segments t)
where owner not in
('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')
group by owner || '.' || tablespace_name)
order by name
