group by tablespace_name) t, (select tablespace_name, sum(bytes) free_bytes from dba_free_space group by tablespace_name) f where t.tablespace_name = f.tablespace.name and t.tablespace_name in ('XXXXTablespaceName‘) order by t.tablespace_name;
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(+);
SELECT DFQ.TABLESPACE_NAME "Tablespace Name" ,DFQ.TOTALSPACE "Total Size MB" , (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) "Free Space MB" ,ROUND (100 * ( (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) / DFQ.TOTALSPACE)) || '%' "Free Space %" FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES)...
I am using the query bellow to check the usage of tablespaces in my oracle DBs: select t.tablespace_name, t.size_mb, f.free_mb, round((f.free_mb*100)/t.size_mb,2) percent_free from (select tablespace_name, round(sum(bytes)/1024/1024,2) size_mb from dba_data_files group by t...
tablespace_size tablespace_blocks, (tablespace_size&dbblocks)/(10241024) tablespace_mb, used_percent from dba_tablespace_usage_metrics; How to check the highest allocated extent The highest allocated extent is the high watermark of the data file. This information is helpful when reducing the size ...
1、DBA_TABLESPACE_USAGE_METRICS的USED_SPACE是已经分配的空间，对应 V$FILESPACE_USAGE 的ALLOCATED_...
DBA_TABLESPACE_USAGE_METRICS describestablespace usage metrics for all types of tablespaces, including permanent,temporary, and undo tablespaces. 从官网的说明,这个视图很方便,通过DBA_TABLESPACE_USAGE_METRICS视图就可以查看所有类型表空间的使用情况,包括永久,临时和undo 表空间。
vim /home/oracle/scripts/check_tablespace.sh 1. 脚本内容如下: 复制 #!/bin/bash# tablespace usagepchecksource ~/.bash_profilefunctioncheck{sqlplus -S"/ as sysdba"<< EOFsetnumwidth 20setlinesize 200setpagesize 200spool /tmp/ora_tablespace.txtselectaa.tablespace_name,round(bb.maxs, 2)"MAX(...
oracle tablespace usage status select a.tablespace_name, a.bytes / 1024 / 1024 "Sum MB", (a.bytes - b.bytes) / 1024 / 1024 "used MB", b.bytes / 1024 / 1024 "free MB", round(((a.bytes - b.bytes) / a.bytes) * 100, 2)||'%' "percent_used"...
1、创建脚本check_tablespace.sh Oracle表空间的信息需要SQL语句查询得到,因此,我们首先创建一个获取表空间信息的原始脚本/h ome/oracle/check_tablespace.sh,这个脚本由oracle用户去执行,脚本内容如下: #!/bin/bash # tablespace usagep check source ~/.bash_profile ...