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>