ORA-01658 表空间相关问题解决思路

ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_MAXWELL

错误产生：

SQL> 
SQL> insert into list_part_tab
  2    (id, deal_date, area_code, contents)
  3    select rownum,
  4           to_date(to_char(sysdate - 365, 'J') +
  5                   TRUNC(DBMS_RANDOM.value(0, 365)),
  6                   'J'),
  7           ceil(dbms_random.value(590, 599)),
  8           rpad('*', 400, '*')
  9      from dual
 10    connect by rownum <= 100000;
insert into list_part_tab
            *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_MAXWELL


SQL>

错误分析：

1.首先，查看表空间总大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME                                                                            SUM(BYTES)/1024/1024
------------------------------------------------------------------------------------------ --------------------
SYSTEM                                                                                                      290
DATA01                                                                                                       50
UNDOTBS2                                                                                                    100
SYSAUX                                                                                                      440
UNDOTBS1                                                                                                    165
TBS_MAXWELL                                                                                                 100
USERS                                                                                                    221.25

7 rows selected.

SQL>

2.查看表空间已使用大小及分配情况

SQL> select SEGMENT_TYPE,owner,sum(bytes)/1024/1024 from dba_segments where tablespace_name='TBS_MAXWELL' group by segment_type,owner;

SEGMENT_TYPE                                           OWNER                                                                                                                                                                            SUM(BYTES)/1024/1024
------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------
TABLE                                                  MAXWELLPAN                                                                                                                                                                              .0625
TABLE PARTITION                                        MAXWELLPAN                                                                                                                                                                         96
INDEX                                                  MAXWELLPAN                                                                                                                                                                              .0625

SQL>

3.查看表空间文件路径

SQL> select * from dba_data_files;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                                                                                 BYTES     BLOCKS STATUS                      RELATIVE_FNO AUTOEXTEN   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS          LOST_WRITE_PROTECT
---------- ------------------------------------------------------------------------------------------ ---------- ---------- --------------------------- ------------ --------- ---------- ---------- ------------ ---------- ----------- --------------------- ---------------------
/u02/oradata/CDB1/pdb1/system01.dbf
         9 SYSTEM                                                                                      304087040      37120 AVAILABLE                              1 YES       3.4360E+10    4194302         1280  303038464       36992 SYSTEM         OFF

/u02/oradata/CDB1/pdb1/sysaux01.dbf
        10 SYSAUX                                                                                      461373440      56320 AVAILABLE                              4 YES       3.4360E+10    4194302         1280  460324864       56192 ONLINE         OFF

/u02/oradata/CDB1/pdb1/undotbs01.dbf
        11 UNDOTBS1                                                                                    173015040      21120 AVAILABLE                              9 YES       3.4360E+10    4194302          640  171966464       20992 ONLINE         OFF

/u02/oradata/CDB1/pdb1/users01.dbf
        12 USERS                                                                                       231997440      28320 AVAILABLE                             12 YES       3.4360E+10    4194302          160  230948864       28192 ONLINE         OFF

/u02/oradata/CDB1/pdb1/data01.dbf
        85 DATA01                                                                                       52428800       6400 AVAILABLE                             85 YES        524288000      64000         6400   51380224        6272 ONLINE         OFF

/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf
       126 TBS_MAXWELL                                                                                 104857600      12800 AVAILABLE                            126 NO                 0          0            0  103809024       12672 ONLINE         OFF

/u02/oradata/CDB1/pdb1/undotbs02.dbf
       127 UNDOTBS2                                                                                    104857600      12800 AVAILABLE                            127 NO                 0          0            0  103809024       12672 ONLINE         OFF


7 rows selected.

SQL>

解决问题：

4.增加表空间大小

方法一：可以选择增加新的数据文件：

alter tablespace TBS_MAXWELL add datafile '/u02/oradata/CDB1/pdb1/TBS_MAXWELL_02.dbf' size 100M;

方法二：扩充表空间原有数据文件的大小

SQL> ALTER DATABASE DATAFILE '/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf' RESIZE 300M;

Database altered.

SQL>

方法三：修改表空间使其自动增加

ALTER DATABASE DATAFILE '/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

或者

ALTER TABLESPACE TBS_MAXWELL AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

结果检查，确认是否更改表空间大小成功执行。

查看对应表空间的大小，显示TBS_MAXWELL表空间目前已经显示为300MB.更改成功，问题解决。

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME                                                                            SUM(BYTES)/1024/1024
------------------------------------------------------------------------------------------ --------------------
SYSTEM                                                                                                      290
DATA01                                                                                                       50
UNDOTBS2                                                                                                    100
SYSAUX                                                                                                      440
UNDOTBS1                                                                                                    165
TBS_MAXWELL                                                                                                 300
USERS                                                                                                    221.25

7 rows selected.

SQL>