Oracle 运维篇+查看表空间使用率

特色：该SQL中表空间最大值为数据文件最大容量之和，这和网上常见的SQL有很大的优化​​​​​​

大纲

--查看表空间使用率（11g新特性）（官方SQL）

--查看表空间使用率（自己写的SQL）（方便且精确）

 

一、查看表空间使用率（11g新特性）

SQL> select * from dba_tablespace_usage_metrics;

SQL> show parameter db_block_size

温馨提示：

• 它的大小计算单位是block，所以需要关联下数据库的block大小
• 表空间总容量如果是可自动扩展的datafile，那么结果就是所有datafile最大的max值的和
• 该视图不稳定，add datafile后不一定及时刷新，可能会等一段时间
• 当表空间drop datafile后该视图由于bug，总容量不会减小，需要重启实例
• 某表空间truncate某张表后，使用率有时不会下降，但作为workaround，手动创建一个表再drop后该视图会刷新

 

二、查看表空间使用率（自己写的SQL）（有时CSDN会将半角空格转为全角空格，用户自行替换后即可直接使用SQL）

温馨提示：如果您发现该SQL有问题或者有更好的写法请留言或私信我进行修改优化

列名	用途	详情
tablespace_name	表空间名称	表空间名称
member	数据文件数	该表空间中包含几个数据文件
MAX_size	表空间最大上限	该表空间中所有数据文件最大可扩展上限之和（即建设自动扩展的表空间达到最大值）
MAX_free	表空间最大剩余值	该表空间中所有数据文件达到最大空间上限之和之前的剩余量
MAX_used	表空间最大使用的值	该表空间目前最大使用了多少空间
MAX_used_percent	表空间最大使用率	该表空间以最大可扩展上限为总量的使用率
Now_Sum	表空间当前最大上限	该表空间当前已分配的空间大小之和
Now_used	表空间当前最大剩余值	该表空间当前已使用的空间
Now_free	表空间当前最大使用的值	该表空间当前已分配的空间为总量的剩余量（这个值基本没什么用，因为对于可自动扩展的数据文件来说它是会变的）
Now_used_percent	表空间当前最大使用率	该表空间当前已分配的空间为总量的使用率（这个值基本没什么用，因为对于可自动扩展的数据文件来说它是会变的）

★ 永久表空间

set line 150
set pages 200
select /* tag_zzt */
       a.tablespace_name,
       a.member,
       round(a.max / 1024 / 1024 / 1024) "MAX_size GB",
       round((a.max - (a.bytes - b.bytes)) / 1024 / 1024 / 1024) "MAX_free GB",
       round((a.bytes - b.bytes) / 1024 / 1024 / 1024) "MAX_used GB",
       round((a.bytes - b.bytes) / a.max * 100) "MAX_used_percent %",
       round(a.bytes / 1024 / 1024) "Now_Sum MB",
       round((a.bytes - b.bytes) / 1024 / 1024) "Now_used MB",
       round(b.bytes / 1024 / 1024) "Now_free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100) "Now_used_percent %"
  from (select tablespace_name,sum(bytes) bytes,sum(decode(maxbytes, 0, bytes, maxbytes)) max,count(file_id) member from dba_data_files group by tablespace_name) a,
       (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by 5, 1;

※ 案例：

① 表空间使用率

	TABLESPACE_NAME	MEMBER	MAX_size GB	MAX_free GB	MAX_used GB	MAX_used_percent %	Now_Sum MB	Now_used MB	Now_free MB	Now_used_percent %
1	UNDOTBS1	1	32	32	0	0	90	6	84	6
2	USERS	3	52	52	0	0	1030	2	1028	0
3	SYSAUX	1	32	31	1	2	560	531	29	95
4	SYSTEM	1	32	31	1	2	710	702	8	99

 ② 数据文件信息

	FILE_NAME	FILE_ID	TABLESPACE_NAME	BYTES	BLOCKS	STATUS	RELATIVE_FNO	AUTOEXTENSIBLE	MAXBYTES	MAXBLOCKS	INCREMENT_BY	USER_BYTES	USER_BLOCKS	ONLINE_STATUS
3	/u01/oracle/oradata/orcl/sysaux01.dbf	2	SYSAUX	587202560	71680	AVAILABLE	2	YES	34359721984	4194302	1280	586153984	71552	ONLINE
4	/u01/oracle/oradata/orcl/system01.dbf	1	SYSTEM	744488960	90880	AVAILABLE	1	YES	34359721984	4194302	1280	743440384	90752	SYSTEM
2	/u01/oracle/oradata/orcl/undotbs01.dbf	3	UNDOTBS1	94371840	11520	AVAILABLE	3	YES	34359721984	4194302	640	93323264	11392	ONLINE
1	/u01/oracle/oradata/orcl/users01.dbf	4	USERS	5242880	640	AVAILABLE	4	YES	34359721984	4194302	160	4194304	512	ONLINE
5	/u01/oracle/oradata/orcl/users02.dbf	5	USERS	1048576	128	AVAILABLE	5	YES	10737418240	1310720	1	983040	120	ONLINE
6	/u01/oracle/oradata/orcl/users03.dbf	6	USERS	1073741824	131072	AVAILABLE	6	YES	10737418240	1310720	1	1072693248	130944	ONLINE

★ 临时表空间

set line 150
set pages 200
select /* tag_zzt */
   a.tablespace_name,
   count(b.file_id) member,
       sum(round(decode(b.maxbytes, 0, b.bytes, b.maxbytes) / 1024 / 1024 / 1024)) "MAX_size GB",
       sum(round(a.allocated_space / 1024 / 1024 / 1024)) "MAX_used GB",
       sum(round((decode(b.maxbytes, 0, b.bytes, b.maxbytes) - a.allocated_space) / 1024 / 1024 / 1024)) "MAX_free GB",
       sum(round(a.allocated_space / decode(b.maxbytes, 0, b.bytes, b.maxbytes) * 100)) "MAX_used_percent %",
       sum(a.TABLESPACE_SIZE / 1024 / 1024) "Now_sum MB",
       sum((a.TABLESPACE_SIZE - a.FREE_SPACE) / 1024 / 1024) "Now_used MB",
       sum(a.FREE_SPACE / 1024 / 1024) "Now_FREE MB",
       sum(a.ALLOCATED_SPACE / 1024 / 1024) "Now_ALLOCATED MB",
       sum(round((a.TABLESPACE_SIZE - a.FREE_SPACE) / a.TABLESPACE_SIZE * 100)) "Now_percent_used %"
  from dba_temp_free_space a, dba_temp_files b
 where a.tablespace_name = b.tablespace_name
 group by a.tablespace_name;

※ 案例：
① 表空间使用率

	TABLESPACE_NAME	MEMBER	MAX_size GB	MAX_used GB	MAX_free GB	MAX_used_percent %	Now_sum MB	Now_used MB	Now_FREE MB	Now_ALLOCATED MB	Now_percent_used %
1	TEMP	3	52	0	52	0	117	9	108	93	24

② 临时文件信息

	FILE_NAME	FILE_ID	TABLESPACE_NAME	BYTES	BLOCKS	STATUS	RELATIVE_FNO	AUTOEXTENSIBLE	MAXBYTES	MAXBLOCKS	INCREMENT_BY	USER_BYTES	USER_BLOCKS
1	/u01/oracle/oradata/orcl/temp01.dbf	1	TEMP	30408704	3712	ONLINE	1	YES	34359721984	4194302	80	29360128	3584
2	/u01/oracle/oradata/orcl/temp02.dbf	2	TEMP	5242880	640	ONLINE	2	YES	10737418240	1310720	1	4194304	512
3	/u01/oracle/oradata/orcl/temp03.dbf	3	TEMP	5242880	640	ONLINE	3	YES	10737418240	1310720	1	4194304	512

 

※ 如果您觉得文章写的还不错， 别忘了在文末给作者点个赞哦 ~

over