oracle 处理表空间不足_resize 数据文件

概述：生产RAC环境巡检数据库时，发现某个表空间剩余空间剩余4.9%，虽然数据文件已经开启自动扩容，但前两天看到篇文章，数据文件自定扩容会影响性能，决定手动调整。

可用处理方法：
1.resize 数据文件增大
2.增加数据文件
3.不用管，自动扩展

由于此表空间下数据文件已经有2个，都未使用到32G，决定resize数据文件。

处理步骤

1.查看表空间对应的数据文件，数据文件ID等信息。

	SQL> select file_name,file_id,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files where tablespace_name='TBS_32K_TRANS_DATA3';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID BYTES/1024/1024 MAXBYTES/1024/1024
---------- --------------- ------------------
+DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879
        11            9520              20480

+DATA/hjkldatafile/tbs_32k_trans_data3.286.947068679
        21           20480                  0

2.备份数据文件，由于/u01 资源有限，所以讲备份集放在+DATA下，数据文件扩容后，即可删掉

RMAN> run {
2> allocate channel d1 type disk;
3> backup datafile 11 format '+DATA/dbf_%U_%s.dbf';
4> release channel d1;}

allocated channel: d1
channel d1: SID=114 instance=hjkl1 device type=DISK

Starting backup at 2019-07-29 15:37:40
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879
channel d1: starting piece 1 at 2019-07-29 15:37:40
channel d1: finished piece 1 at 2019-07-29 15:39:15
piece handle=+DATA/dbf_9hu7sjm4_1_1_10545.dbf tag=TAG20190729T153740 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:35
Finished backup at 2019-07-29 15:39:15

Starting Control File and SPFILE Autobackup at 2019-07-29 15:39:15
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/c-2382121461-20190729-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2019-07-29 15:39:16

released channel: d1


RMAN> list backup of datafile 11;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
10459   Full    8.95G      DISK        00:01:34     2019-07-29 15:39:14
        BP Key: 10459   Status: AVAILABLE  Compressed: NO  Tag: TAG20190729T153740
        Piece Name: +DATA/dbf_9hu7sjm4_1_1_10545.dbf
  List of Datafiles in backup set 10459
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  11      Full 4589616327 2019-07-29 15:37:40 +DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879

3.resize 数据文件

SQL> alter database datafile '+DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879' resize 30G;

Database altered.

4.查看alert日志，有无报错

Mon Jul 29 14:59:19 2019
Archived Log entry 45684 added for thread 1 sequence 22728 ID 0xffffffff8dfc49f5 dest 1:
Mon Jul 29 15:42:55 2019
alter database datafile '+DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879' resize 30G
Mon Jul 29 15:44:23 2019
Completed: alter database datafile '+DATA/hjkl/datafile/tbs_32k_trans_data3.273.885902879' resize 30G

至此，完成！

观察数据库无异常可删除备份！

心得：生成环境改变数据库时，最好先做一下备份，有备无患。