oracle表空间路径6,查看表空间，表空间路径，增加表空间

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