关于数据表空间问题总结

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