select tablespace_name,file_id,bytes/1024/1024,file_name
from dba_data_files order by file_id;--查看用户和默认表空间的关系
select username,default_tablespace from dba_users; --查看用户和默认表空间的关系
Oracle表空间很重要，表空间不足直接导致程序无法链接数据库，造成业务瘫痪。所以每天要登录平台查看表空间。查看oracle表空间的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;--查找存放表空间的数据文件的路径
SELECT ddf.file_name,ddf.bytes/1024/1024 "Sum MB"
FROM Dba_Data_Files ddf
where ddf.tablespace_name = 'USERS';--增加10G表空间
ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracleDB/DATA/hebsjb/system06.dbf' SIZE 10240M;这种情况并一定是有问题哦,可能你的脚本有问题,应该是temp表空间确实有过使用率达到99%以上的记录,但目前可能为空...
用偶这个脚本试试?
set linesize 100
set pagesize 100
set echo off
set feedback off
col "USED_SPACE(M)" format 99999999.99
col "USED_RATE(%)" format 999.99
col SUM_BLOCKS format 999999999
col "SUM_SPACE(M)" format 999999999
col "FREE_SPACE(M)" format 99999999.99
col tablespace_name format a30
select t.* from (
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
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 --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
NVL(USED_SPACE, 0) "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0)/NVL(SPACE, 0) * 100, 2) "USED_RATE(%)",
NVL(D.SPACE, 0) - NVL(F.USED_SPACE, 0) "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
NVL(ROUND(SUM(BYTES)/(1024*1024), 2), 0) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
NVL(ROUND(SUM(BLOCKS*8192)/(1024*1024), 2), 0) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)
) t
order by "USED_RATE(%)" desc;
查看结果：
查看某表所属表空间：
select table_name,tablespace_name from user_tables where table_name='AUD$'；
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29812751/viewspace-1695717/ ，如需转载，请注明出处，否则将追究法律责任。
