1、表空间使用情况查看sql
SELECT
b.file_name filename,
b.tablespace_name tabname,
b.bytes/1024/1024 countsize,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedsize,
sum(nvl(a.bytes,0))/1024/1024 freesize,
sum(nvl(a.bytes,0))/(b.bytes)*100 freeutil
FROM dba_free_space a,dba_data_files b
WHERE a.file_id(+)=b.file_id
GROUP BY b.file_name, b.tablespace_name,b.file_id,b.bytes
ORDER BY b.file_id;
2、表空间收缩办法
一、查找表空间文件id
select file#,name from v$datafile;
二、根据文件id查找最大快id
select max(block_id) from dba_extents where file_id=n;
MAX(BLOCK_ID)
-------------
13657
三、查找全局参数,块大小。
show parameter db_block_size;
NAME TYPE VALUE
----------------------------- ------- -----------
db_block_size integer 8192
四、计算最大块所在位置占用空间大小
select 13657*8192/1024/1024 from dual;
13657*8192/1024/1024
-----------
106.695313
五、缩空间命令,根据计算出的大小比106m大,则缩放空间因该大于106m 选择107或更大。
alter database datafile '/oracle/oradata/svm/undotbs01.dbf‘ resize 107M;
3、查看用户表空间使用情况
SELECT owner, tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)"
FROM dba_segments
GROUP BY owner, tablespace_name
ORDER BY SUM (BYTES) DESC;