Oracle表空间不够使用处理

1. –oracle对应空间名称

select name from v$tablespace;

2. –查询对应的空间使用率

SELECT a.tablespace_name as tablespace_name,
       to_char(b.total/1024/1024,999999.99) as Total,
       to_char((b.total-a.free)/1024/1024,999999.99) as Used,
       to_char(a.free/1024/1024,999999.99) as Free,
       to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
     (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
  AND a.tablespace_name='UNDOTBS2'
ORDER BY a.tablespace_name;

3. –查询undo的表空间文件路径

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

4. –创建undo的表空间

create undo tablespace undotbs2 datafile 'D:/APP/ADMINISTRATOR/ORADATA/PTSWAP/UNDOTBS02.dbf' size    21000m reuse autoextend on next 800m maxsize unlimited;

5. –切换表空间

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

6. –命令查询切换的存储空间

--> show   parameter undo_tablespace