Oracle 表空间不足导致的系统无法登陆问题及解决方法

某医院的 HIS 系统突然无法登陆，并且出现以下提示信息：

SYSAUX 和 SYSTEM 表空间都满了 怎么处理_数据库

初步判断是表空间不足导致的问题，登录服务器查看表空间信息如下：

一、查看表空间信息

SQL> select tablespace_name,file_name,user_bytes/1024/1024 sizeMB 
     from dba_data_files 
     order by tablespace_name;

TABLESPACE_NAME           FILE_NAME                         SIZEMB
------------------------- ------------------------------------ ------------------
AUDIT_TBS     +DATA/hisdb/datafile/audit_tbs.266.1034788047  1023
DATA_AIS      +DATA/hisdb/datafile/data_ais.263.1034788049     29
DATA_APPLYOUT +DATA/hisdb/datafile/data_applyout.323.1034788051       25599
DATA_AQU      +DATA/hisdb/datafile/data_aqu.322.1034788067     39
DATA_CAS      +DATA/hisdb/datafile/data_cas.321.1034788067   5119
DATA_COM      +DATA/hisdb/datafile/data_com.320.1034788067   5119
DATA_EMR      +DATA/hisdb/datafile/data_emr.319.1034788069   5119
DATA_EXECDRUG +DATA/hisdb/datafile/data_execdrug.318.1034788069       25599
DATA_EXECUNDRUG     +DATA/hisdb/datafile/data_execundrug.317.1034788087     20479
DATA_FEEDETAIL+DATA/hisdb/datafile/data_feedetail.316.1034788101      15359
DATA_FEEINFO  +DATA/hisdb/datafile/data_feeinfo.315.1034788109        15359
DATA_FIN      +DATA/hisdb/datafile/data_fin.314.1034788119  13639
DATA_GOA      +DATA/hisdb/datafile/data_goa.313.1034788125     39
DATA_ITEMLIST +DATA/hisdb/datafile/data_itemlist.312.1034788125       25599
DATA_LIS      +DATA/hisdb/datafile/data_lis.311.1034788143   5119
DATA_LOG      +DATA/hisdb/datafile/data_log.310.1034788143   5119
DATA_MEDICINELIST   +DATA/hisdb/datafile/data_medicinelist.309.1034788143   20479
DATA_MET      +DATA/hisdb/datafile/data_met.308.1034788157  20479
DATA_ORDER    +DATA/hisdb/datafile/data_order.305.103478822520479
DATA_ORDER    +DATA/hisdb/datafile/data_order.304.103478824320479
DATA_ORDER    +DATA/hisdb/datafile/data_order.306.103478819731999
DATA_ORDER    +DATA/hisdb/datafile/data_order.307.103478816931999
DATA_ORDER    +DATA/hisdb/datafile/data_order2.dbf           5119
DATA_ORDER    +DATA/hisdb/datafile/data_order3.dbf           5119
DATA_OTHER    +DATA/hisdb/datafile/data_other.303.1034788255  399
DATA_OUTPUT   +DATA/hisdb/datafile/data_output.302.1034788255         25599
DATA_PHA      +DATA/hisdb/datafile/data_pha.301.1034788271  10239
DATA_RECIPEDETAIL   +DATA/hisdb/datafile/data_recipedetail.300.1034788275   10239
DATA_RECORD   +DATA/hisdb/datafile/data_record.299.1034788281         20479
DATA_SEM      +DATA/hisdb/datafile/data_sem.298.1034788293     29
DATA_USER     +DATA/hisdb/datafile/data_user.297.1034788293            8919
EMR5          +DATA/hisdb/datafile/emr5.dbf                  5999
EMR5          +DATA/hisdb/datafile/emr503.dbf               32699
EMR5          +DATA/hisdb/datafile/emr502.dbf               32699
EMR5          +DATA/hisdb/datafile/emr501.dbf               30719
EMR52012      +DATA/hisdb/datafile/emr52012.dbf               199
EMR52013      +DATA/hisdb/datafile/emr52013.dbf               199
EMR52014      +DATA/hisdb/datafile/emr52014.dbf               199
EMR52015      +DATA/hisdb/datafile/emr52015.dbf               199
EMR52016      +DATA/hisdb/datafile/emr52016.dbf               199
EMR52017      +DATA/hisdb/datafile/emr52017.dbf               199
EMR52018      +DATA/hisdb/datafile/emr52018.dbf               199
EMR52019      +DATA/hisdb/datafile/emr52019.dbf               199
EMR52020      +DATA/hisdb/datafile/emr5202003.dbf           10239
EMR52020      +DATA/hisdb/datafile/emr5202002.dbf           30719
EMR52020      +DATA/hisdb/datafile/emr52020.dbf             18569
EMR52020      +DATA/hisdb/datafile/emr5202001.dbf           30719
EMR52020      +DATA/hisdb/datafile/emr5202104.dbf.dbf       32699
--================================================================================
--  表空间 EMR52021 对应的文件  +DATA/hisdb/datafile/emr5202101.dbf、
--  +DATA/hisdb/datafile/emr5202102.dbf、+DATA/hisdb/datafile/emr5202103.dbf、
--  +DATA/hisdb/datafile/emr5202104.dbf、+DATA/hisdb/datafile/emr5202105.dbf、
--  +DATA/hisdb/datafile/emr5202106.dbf 都已经达到上限 32GB
EMR52021      +DATA/hisdb/datafile/emr5202106.dbf           32699
EMR52021      +DATA/hisdb/datafile/emr5202105.dbf           32699
EMR52021      +DATA/hisdb/datafile/emr5202104.dbf           32699
EMR52021      +DATA/hisdb/datafile/emr5202103.dbf           32699
EMR52021      +DATA/hisdb/datafile/emr5202102.dbf      32766.9375
EMR52021      +DATA/hisdb/datafile/emr5202101.dbf           30719
--==================================================================================
EMR52022      +DATA/hisdb/datafile/emr5202201.dbf           32699
INDEX_AIS     +DATA/hisdb/datafile/index_ais.296.1034788297    29
INDEX_APPLYOUT+DATA/hisdb/datafile/index_applyout.295.1034788297      15239
INDEX_AQU     +DATA/hisdb/datafile/index_aqu.294.1034788309    29
INDEX_CAS     +DATA/hisdb/datafile/index_cas.293.1034788309  5119
INDEX_COM     +DATA/hisdb/datafile/index_com.292.1034788309  5119
INDEX_EMR     +DATA/hisdb/datafile/index_emr.291.1034788311    29
INDEX_EXECDRUG+DATA/hisdb/datafile/index_execdrug.290.1034788311      10239
INDEX_EXECUNDRUG    +DATA/hisdb/datafile/index_execundrug.289.1034788317    10239
INDEX_FEEDETAIL     +DATA/hisdb/datafile/index_feedetail.288.1034788321     12287
INDEX_FEEINFO +DATA/hisdb/datafile/index_feeinfo.287.1034788329       13939
INDEX_FIN     +DATA/hisdb/datafile/index_fin.286.1034788337  6569
INDEX_GOA     +DATA/hisdb/datafile/index_goa.285.1034788343    29
INDEX_ITEMLIST+DATA/hisdb/datafile/index_itemlist.284.1034788343      15359
INDEX_LIS     +DATA/hisdb/datafile/index_lis.283.1034788355  5119
INDEX_LOG     +DATA/hisdb/datafile/index_log.282.1034788355  5119
INDEX_MEDICINELIST  +DATA/hisdb/datafile/index_medicinelist.281.1034788355  10949
INDEX_MET     +DATA/hisdb/datafile/index_met.280.1034788361 10179
INDEX_ORDER   +DATA/hisdb/datafile/index_order.279.1034788369         10239
INDEX_OTHER   +DATA/hisdb/datafile/index_other.278.10347883755119
INDEX_OUTPUT  +DATA/hisdb/datafile/index_output.277.1034788375         8679
INDEX_PHA     +DATA/hisdb/datafile/index_pha.276.1034788381 16159
INDEX_RECIPEDETAIL  +DATA/hisdb/datafile/index_recipedetail.258.1034788387   5119
INDEX_RECORD  +DATA/hisdb/datafile/index_record.325.1034788389         3059
INDEX_SEM     +DATA/hisdb/datafile/index_sem.268.1034788391    29
INDEX_USER    +DATA/hisdb/datafile/index_user.271.1034788391  299
NDQS          +DATA/hisdb/datafile/ndqsdata01.dbf             499
NEUCBUS       +DATA/hisdb/datafile/neucbus_data1            23119
--==================================================================================
--  表空间 NEUICU 对应的文件  +DATA/hisdb/datafile/neuicu_data1 达到上限 32GB
NEUICU        +DATA/hisdb/datafile/neuicu_data1        32766.9375
--===================================================================================
NFEMR         +DATA/hisdb/datafile/nfemr.dbf                  199
NTSDATA       +DATA/hisdb/datafile/ntsdata01.dbf             2047
--====================================================================================
--  表空间 SYSAUX 对应的文件  +DATA/hisdb/datafile/sysaux.326.1034787445 和 
--  +DATA/hisdb/datafile/sysaux001 达到上限 32GB
SYSAUX        +DATA/hisdb/datafile/sysaux.326.1034787445    32739
SYSAUX        +DATA/hisdb/datafile/sysaux001                32699
--===================================================================================
--===================================================================================
--  表空间 SYSTEM 对应的文件  +DATA/hisdb/datafile/system.275.1034787445 达到上限 32GB
SYSTEM        +DATA/hisdb/datafile/system.275.1034787445    32749
--==================================================================================
UNDOTBS1      +DATA/hisdb/datafile/undotbs1.265.1034787445  30719
UNDOTBS2      +DATA/hisdb/datafile/undotbs2.264.1034787607  20479
USERS         +DATA/hisdb/datafile/users.274.1034787445     30719

二、问题原因分析

Oracle 的数据存储在数据表（table）中，而 table 必须保存在表空间（tablespace）中。一个 Oracle 表空间可以对应一个或多个数据文件，如果数据文件可以设置为自动增长，当表空间不足时，数据文件的大小会自动增长，但 Oracle 规定单个文件的增长上限为 32 GB。

如果数据文件的大小达到 32 GB 时，表空间大小就没办法扩充了，如果是普通的用户表空间大小不足，则该表空间中的数据表无法插入数据。如果是系统表空间（system 表空间）大小不足，应为系统表空间存放了数据字典等数据，会导致整个数据库无法进行任何的数据操作，甚至无法登录数据库。

根据表空间的查询结果，发现：SYSTEM、SYSAUX、 EMR52021、NEUICU 四个表空间对应的数据文件都已经达到了32 GB。

三、解决方法

当表空间对应的数据文件达到上限（32GB）时，需要给表空间增加数据文件。语法如下：

ALTER TABLESPACE 表空间名称 ADD DATAFILE 数据文件名 [文件属性];

--文件属性：
size x M|G                       -- 数据文件的初始大小
autoextend off | on next x M|G   -- 数据文件是否能自动扩展，每次自动扩展多少

针对数据库存在的问题，需要为 SYSTEM、SYSAUX、 EMR52021、NEUICU 四个表空间增加数据文件，执行如下命令：

1、为 SYSTEM 表空间增加数据文件
ALTER TABLESPACE SYSTEM 
     ADD DATAFILE '+DATA/hisdb/datafile/system_bak' SIZE 500M 
     autoextend on next 100M;
     
ALTER TABLESPACE SYSTEM 
     ADD DATAFILE '+DATA/hisdb/datafile/system_bak02' SIZE 500M 
     autoextend on next 100M;
2、为 SYSAUX 表空间增加数据文件
ALTER TABLESPACE SYSAUX
     ADD DATAFILE '+DATA/hisdb/datafile/sysaux001' SIZE 500M 
     autoextend on next 100M;

ALTER TABLESPACE SYSAUX
     ADD DATAFILE '+DATA/hisdb/datafile/sysaux002' SIZE 500M 
     autoextend on next 100M;
3、为 EMR52021 表空间增加数据文件
ALTER TABLESPACE EMR52021 
     ADD DATAFILE '+DATA/hisdb/datafile/emr_bak.dbf' SIZE 500M 
     autoextend on next 100M;
4、为 NEUICU 表空间增加数据文件
ALTER TABLESPACE NEUICU 
     ADD DATAFILE '+DATA/hisdb/datafile/neuicu_data11' SIZE 500M 
     autoextend on next 100M;

添加数据文件之后的结果如下：

SQL> select tablespace_name,file_name,user_bytes/1024/1024 sizeMB 
     from dba_data_files 
     order by tablespace_name;

TABLESPACE_NAME     FILE_NAME                                       SIZEMB
------------------------- ----------------------------------------- ----------
AUDIT_TBS      +DATA/hisdb/datafile/audit_tbs.266.1034788047            1023
DATA_AIS       +DATA/hisdb/datafile/data_ais.263.1034788049               29
DATA_APPLYOUT       +DATA/hisdb/datafile/data_applyout.323.1034788051       25599
DATA_AQU            +DATA/hisdb/datafile/data_aqu.322.1034788067               39
DATA_CAS            +DATA/hisdb/datafile/data_cas.321.1034788067             5119
DATA_COM            +DATA/hisdb/datafile/data_com.320.1034788067             5119
DATA_EMR            +DATA/hisdb/datafile/data_emr.319.1034788069             5119
DATA_EXECDRUG       +DATA/hisdb/datafile/data_execdrug.318.1034788069       25599
DATA_EXECUNDRUG     +DATA/hisdb/datafile/data_execundrug.317.1034788087     20479
DATA_FEEDETAIL      +DATA/hisdb/datafile/data_feedetail.316.1034788101      15359
DATA_FEEINFO        +DATA/hisdb/datafile/data_feeinfo.315.1034788109        15359
DATA_FIN            +DATA/hisdb/datafile/data_fin.314.1034788119            13639
DATA_GOA            +DATA/hisdb/datafile/data_goa.313.1034788125               39
DATA_ITEMLIST       +DATA/hisdb/datafile/data_itemlist.312.1034788125       25599
DATA_LIS            +DATA/hisdb/datafile/data_lis.311.1034788143             5119
DATA_LOG            +DATA/hisdb/datafile/data_log.310.1034788143             5119
DATA_MEDICINELIST   +DATA/hisdb/datafile/data_medicinelist.309.1034788143   20479
DATA_MET            +DATA/hisdb/datafile/data_met.308.1034788157            20479
DATA_ORDER          +DATA/hisdb/datafile/data_order.305.1034788225          20479
DATA_ORDER          +DATA/hisdb/datafile/data_order.304.1034788243          20479
DATA_ORDER          +DATA/hisdb/datafile/data_order.306.1034788197          31999
DATA_ORDER          +DATA/hisdb/datafile/data_order.307.1034788169          31999
DATA_ORDER          +DATA/hisdb/datafile/data_order2.dbf                     5119
DATA_ORDER          +DATA/hisdb/datafile/data_order3.dbf                     5119
DATA_OTHER          +DATA/hisdb/datafile/data_other.303.1034788255            399
DATA_OUTPUT         +DATA/hisdb/datafile/data_output.302.1034788255         25599
DATA_PHA            +DATA/hisdb/datafile/data_pha.301.1034788271            10239
DATA_RECIPEDETAIL   +DATA/hisdb/datafile/data_recipedetail.300.1034788275   10239
DATA_RECORD         +DATA/hisdb/datafile/data_record.299.1034788281         20479
DATA_SEM            +DATA/hisdb/datafile/data_sem.298.1034788293               29
DATA_USER           +DATA/hisdb/datafile/data_user.297.1034788293            8919
EMR5                +DATA/hisdb/datafile/emr5.dbf                            5999
EMR5                +DATA/hisdb/datafile/emr503.dbf                         32699
EMR5                +DATA/hisdb/datafile/emr502.dbf                         32699
EMR5                +DATA/hisdb/datafile/emr501.dbf                         30719
EMR52012            +DATA/hisdb/datafile/emr52012.dbf                         199
EMR52013            +DATA/hisdb/datafile/emr52013.dbf                         199
EMR52014            +DATA/hisdb/datafile/emr52014.dbf                         199
EMR52015            +DATA/hisdb/datafile/emr52015.dbf                         199
EMR52016            +DATA/hisdb/datafile/emr52016.dbf                         199
EMR52017            +DATA/hisdb/datafile/emr52017.dbf                         199
EMR52018            +DATA/hisdb/datafile/emr52018.dbf                         199
EMR52019            +DATA/hisdb/datafile/emr52019.dbf                         199
EMR52020            +DATA/hisdb/datafile/emr5202003.dbf                     10239
EMR52020            +DATA/hisdb/datafile/emr5202002.dbf                     30719
EMR52020            +DATA/hisdb/datafile/emr52020.dbf                       18569
EMR52020            +DATA/hisdb/datafile/emr5202001.dbf                     30719
EMR52020            +DATA/hisdb/datafile/emr5202104.dbf.dbf                 32699
EMR52021            +DATA/hisdb/datafile/emr_bak.dbf                          499
EMR52021            +DATA/hisdb/datafile/emr5202106.dbf                     32699
EMR52021            +DATA/hisdb/datafile/emr5202105.dbf                     32699
EMR52021            +DATA/hisdb/datafile/emr5202104.dbf                     32699
EMR52021            +DATA/hisdb/datafile/emr5202103.dbf                     32699
EMR52021            +DATA/hisdb/datafile/emr5202102.dbf                32766.9375
EMR52021            +DATA/hisdb/datafile/emr5202101.dbf                     30719
EMR52022            +DATA/hisdb/datafile/emr5202201.dbf                     32699
INDEX_AIS           +DATA/hisdb/datafile/index_ais.296.1034788297              29
INDEX_APPLYOUT      +DATA/hisdb/datafile/index_applyout.295.1034788297      15239
INDEX_AQU           +DATA/hisdb/datafile/index_aqu.294.1034788309              29
INDEX_CAS           +DATA/hisdb/datafile/index_cas.293.1034788309            5119
INDEX_COM           +DATA/hisdb/datafile/index_com.292.1034788309            5119
INDEX_EMR           +DATA/hisdb/datafile/index_emr.291.1034788311              29
INDEX_EXECDRUG      +DATA/hisdb/datafile/index_execdrug.290.1034788311      10239
INDEX_EXECUNDRUG    +DATA/hisdb/datafile/index_execundrug.289.1034788317    10239
INDEX_FEEDETAIL     +DATA/hisdb/datafile/index_feedetail.288.1034788321     12287
INDEX_FEEINFO       +DATA/hisdb/datafile/index_feeinfo.287.1034788329       13939
INDEX_FIN           +DATA/hisdb/datafile/index_fin.286.1034788337            6569
INDEX_GOA           +DATA/hisdb/datafile/index_goa.285.1034788343              29
INDEX_ITEMLIST      +DATA/hisdb/datafile/index_itemlist.284.1034788343      15359
INDEX_LIS           +DATA/hisdb/datafile/index_lis.283.1034788355            5119
INDEX_LOG           +DATA/hisdb/datafile/index_log.282.1034788355            5119
INDEX_MEDICINELIST  +DATA/hisdb/datafile/index_medicinelist.281.1034788355  10949
INDEX_MET           +DATA/hisdb/datafile/index_met.280.1034788361           10179
INDEX_ORDER         +DATA/hisdb/datafile/index_order.279.1034788369         10239
INDEX_OTHER         +DATA/hisdb/datafile/index_other.278.1034788375          5119
INDEX_OUTPUT        +DATA/hisdb/datafile/index_output.277.1034788375         8679
INDEX_PHA           +DATA/hisdb/datafile/index_pha.276.1034788381           16159
INDEX_RECIPEDETAIL  +DATA/hisdb/datafile/index_recipedetail.258.1034788387   5119
INDEX_RECORD        +DATA/hisdb/datafile/index_record.325.1034788389         3059
INDEX_SEM           +DATA/hisdb/datafile/index_sem.268.1034788391              29
INDEX_USER          +DATA/hisdb/datafile/index_user.271.1034788391            299
NDQS                +DATA/hisdb/datafile/ndqsdata01.dbf                       499
NEUCBUS             +DATA/hisdb/datafile/neucbus_data1                      23119
NEUICU              +DATA/hisdb/datafile/neuicu_data11                        499
NEUICU              +DATA/hisdb/datafile/neuicu_data1                  32766.9375
NFEMR               +DATA/hisdb/datafile/nfemr.dbf                            199
NTSDATA             +DATA/hisdb/datafile/ntsdata01.dbf                       2047
SYSAUX              +DATA/hisdb/datafile/sysaux.326.1034787445              32739
SYSAUX              +DATA/hisdb/datafile/sysaux002                            499
SYSAUX              +DATA/hisdb/datafile/sysaux001                          32699
SYSTEM              +DATA/hisdb/datafile/system.275.1034787445              32749
SYSTEM              +DATA/hisdb/datafile/system_bak                           499
SYSTEM              +DATA/hisdb/datafile/system_bak02                         499
UNDOTBS1            +DATA/hisdb/datafile/undotbs1.265.1034787445            30719
UNDOTBS2            +DATA/hisdb/datafile/undotbs2.264.1034787607            20479
USERS               +DATA/hisdb/datafile/users.274.1034787445               30719

96 rows selected.

添加数据文件之后系统恢复正常，问题圆满解决！

四、建议

为了避免表空间不足问题的发生，可以采用如下策略：

1、为每一个表空间添加多个数据文件

使用如下命令为每一个表空间添加多个数据文件，比如每个表空间添加10个数据文件。

ALTER TABLESPACE 表空间名称
     ADD DATAFILE '文件路径名称' SIZE 500M 
     autoextend on next 100M;
2、使用以下命令查看表空间的使用情况

当发现表空间的数据文件大小超过20GB甚至30GB时，及时添加数据文件。

SQL> select tablespace_name,file_name,user_bytes/1024/1024 sizeMB from dba_data_files order by tablespace_name;