【笔记】oracle查询表空间的空间占用情况

最新推荐文章于 2024-10-08 06:21:20 发布

cikaoqin5248

最新推荐文章于 2024-10-08 06:21:20 发布

阅读量129

点赞数

文章标签：数据库

select a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
from (select tablespace_name,sum(bytes) bytes 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 ((a.bytes - b.bytes) / a.bytes) desc

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"
from
(select tablespace_name,sum(bytes) bytes 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 ((a.bytes-b.bytes)/a.bytes) desc

查询所有表空间的总容量、已经使用、剩余、已经使用的百分比！

一般来说可以把上面的复杂的查询语句放入一个文件中，需要时再调用，或者创建一个试图，需要时可以查询。
1 写入文件:#vi /home/mzl/percent_used_tablespace.sql
内容：
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"
from
(select tablespace_name,sum(bytes) bytes 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 ((a.bytes-b.bytes)/a.bytes) desc

2 导入：
SQL> @/home/mzl/percent_used_tablespace.sql
SQL> l
1 select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",
2 round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
3 from
4 (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
5 (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7* order by ((a.bytes-b.bytes)/a.bytes) desc
SQL> /

或者创建视图：
SQL>create view percent
SQL>as
SQL>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
SQL>round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
SQL>from
SQL>(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
SQL>(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
SQL>where a.tablespace_name=b.tablespace_name
SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;

SQL> select * from percent;

查看表空间的数据文件是否是自动扩展：
SQL> l
1* select file_name,tablespace_name,autoextensible from dba_data_files
SQL> /

FILE_NAME                                     TABLESPACE_NAME                AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/risenet.dbf      RISENET
/u01/app/oracle/oradata/orcl/perfstat.dbf     PERFSTAT                       NO
/u01/app/oracle/oradata/orcl/example01.dbf    EXAMPLE                        YES
/u01/disk1/users01.dbf                        USERS                          YES
/u01/app/oracle/oradata/orcl/sysaux01.dbf     SYSAUX                         YES
/u01/app/oracle/oradata/orcl/undotbs01.dbf    UNDOTBS1
/u01/disk2/system01.dbf                       SYSTEM                         YES
/u01/app/oracle/oradata/orcl/undotbs02.dbf    UNDOTBS2                       NO
/u01/disk1/pioneer_data.dbf                   PIONEER_DATA                   YES
/u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   NO
/u01/disk3/pioneer_undo.dbf                   PIONEER_UNDO                   NO

FILE_NAME                                     TABLESPACE_NAME                AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/paul01.dbf       PAUL                           NO
/u01/disk1/wenchuan.dbf                       WENCHUAN                       NO

13 rows selected.

比如表空间PIONEER_INDX已经用了83.33％，数据文件不能自动扩展，可以修改成自动扩展，以免数据写满数据文件。
SQL> alter database
2 datafile '/u01/disk2/pioneer_indx.dbf' autoextend on;

Database altered.

SQL> select file_name,tablespace_name,autoextensible from dba_data_files
2 where tablespace_name='PIONEER_INDX';

FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/disk2/pioneer_indx.dbf PIONEER_INDX YES

或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘，可以增加多个数据文件（这样多数据库系统的并发性比较好）
SQL> alter tablespace pioneer_indx
2 add datafile size 30M;

Tablespace altered.

SQL> select file_name,tablespace_name,bytes/1024/1024 "MB" from dba_data_files
2 where tablespace_name='PIONEER_INDX';

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- ------------------------------
        MB
----------
/u01/disk2/pioneer_indx.dbf                   PIONEER_INDX
         6

/u01/disk5/ORCL/datafile/o1_mf_pioneer__45dpy PIONEER_INDX
fty_.dbf
30

----查询表空间使用情况---
使用DBA权限登陆
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;
表空间名表空间大小(M) 已使用空间(M) 使用比空闲空间(M) 最大块(M)
------------------------------ ------------- ------------- ------- ----------- ----------
...
CCEN 10 8.5 85.00 1.5 .94
...

发现表空间只有1.5M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高，ORACLE默认是50%),修改表空间文件扩展方式:

SQL>ALTER DATABASE
DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED
问题解决.

查看表空间是否具有自动扩展的能力
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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22786751/viewspace-619464/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22786751/viewspace-619464/