记一次ORACLE表空间修复

一.问题描述

表空间 Users的97 percent已满.

表空间 TSP_EMR的85 percent已满.

表空间 TSP_OUTPADM的85  percent已满.

二.问题分析

以Users表空间为例，其他类似

USERS表空间是默认用户表空间，在创建一个用户并没有指定此用户使用表空间时，该用户所有信息都会放入到users表空间中，使用查询表空间语句：

select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB"   

from    dba_data_files 

where tablespace_name='USERS';  

查询users表空间，发现已占满 , 使用sql：

select t.TABLE_NAME,t.NUM_ROWS 

  from all_tables t 

where tablespace_name='USERS' order by num_rows desc;

查询使用USERS表空间的表，按行级降序排序，发现多个表使用USERS表空间，存在大量数据导致USER表占满.

三.问题处理

1、扩展表空间：

alter datafile ‘/oracle_data/orcl/users02.dbf’ resize 30G;

2、扩展到最大30G(最大)文件无法继续扩展，可增加数据文件：

alter tablespace users  add datafile  '/oracle_data/orcl/users27.dbf' 
 size 1024m autoextend on next 1024m maxsize 30G;

3、truncate删除无用表释放空间

假如未释放，对TEST表进行收缩shrink，执行下面三个语句：
 启用行迁移：alter table  TEST    enable row movement;
 shrink表test：alter TABLE  TEST  shrink SPACE;
 关闭行迁移：alter table  TEST  DISABLE row movement;
注：数据被删除后（无论是 delete 还是 truncate table），数据文件大小不会缩小， Oracle “高水位”所致(可以具体了解)，想要降低数据文件大小需降低高水位的正确做法是先降低HWM，再确定实际占有大小，再resize数据文件，执行如下4个语句：
(1)查询表空间文件编号：

select file#, name from v$datafile;
(2)根据文件 ID 查询这个数据文件最大数据块(data block)的编号：select max(block_id) from dba_extents where file_id=4;
(3)计算该表空间实际占用的空间，先查询数据块大小： select value from v$parameter where name='db_block_size'，咱默认是8192.
(4)计算实际占用磁盘大小： select 65673 * 8 / 1024 from dual;
(5)把数据文件大小resize到比实际占用磁盘大小大一些就行了：
 alter database datafile '/oracle/oradata/dbaxj/users01.dbf' resize 600m;
这样数据文件大小就变小了，节约空间

4、需要使用的表，修改表空间

alter table USSD_UNREPORT_FAIL move tablespace new_tablespace

建表时需养成习惯，指定好表空间

参考：https://blog.csdn.net/u013050593/article/details/77160693