[Solved] Insufficient Oracle table space: ora-01653: unable to extend table

Background:

It is a common fault to report an error due to insufficient Oracle table space, especially for the system without regular inspection of the remaining table space

The error code is as follows:

Insufficient Oracle table space error code: ora-01653: unable to extend table

Solution:

1. View the utilization rate of table space:

 1 set linesize 220;
 2 set pagesize 500;
 3 col tbsn for a40;
 4 select total.tablespace_name TBSN,
 5         round(total.MB,3) as total_space,
 6         case when free.MB is null then 0
 7                                else round(free.MB,3)
 8          end as free_space, 
 9        case when free.MB is null then 0
10          else round(free.MB,3)/round(total.MB,3)*100  end as free_rate
11 from ( select tablespace_name, sum(bytes/1024/1024) MB from dba_data_files group by tablespace_name )  total,
12         ( select tablespace_name, sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name )  free
13 where total.tablespace_name=free.tablespace_name(+)
14 order by  free_rate desc;
15 
16

The above code can query which table spaces in the database, the total space size, the remaining space size and the usage percentage, so as to make the table space usage clear at a glance

2. Check that the disk space table is too much, and put the table space in the place with more remaining space (Linux operating system)
DF – H

3. View the table space data file:
be sure to confirm the path and keep it consistent with the original file path

1 set wrap on;
2 set lines 170
3 col file_name for a80
4 select file_id,file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name=upper('&tablespace_name');

 

4. If it is a RAC environment, you should also check the remaining ASM disk space

1 select group_number,name,total_mb,free_mb from v$asm_diskgroup;

5. Add table space (manual condition data file)

1 alter tablespace tablespace_name add datafile '/xxxx/xxxx/xxxxx_number.dbf' size 30g autoextend off;

6. I wrote a capacity expansion SQL for automatically editing and adding data files, which can generate capacity expansion scripts in batches (no 100% success rate is guaranteed, basically no problem)

2

 1 set linesize 150 pagesize 900
 2 SELECT 'alter tablespace ' || TABLESPACE_NAME || ' add datafile ''' ||
 3        substr(file_name, 1, regexp_instr(file_name, '[[:digit:]]+\.') - 1) ||
 4        TO_CHAR(substr(file_name,
 5                       regexp_instr(file_name, '[[:digit:]]+\.'),
 6                       instr(file_name, '.') -
 7                       regexp_instr(file_name, '[[:digit:]]+\.')) + B.RN) ||
 8        '.dbf'' size ' || bytes/1024/1024 || 'm autoextend off;'
 9   FROM DBA_DATA_FILES,
10        (select rownum rn
11           from dba_objects
12          WHERE ROWNUM <= &number_of_datafile) B       
13  WHERE FILE_ID = (SELECT DISTINCT LAST_VALUE(FILE_ID) 
14  OVER(ORDER BY to_number(substr(file_name, regexp_instr(file_name, '[[:digit:]]+\.'), instr(file_name, '.') - regexp_instr(file_name, '[[:digit:]]+\.'))) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
15                     FROM DBA_DATA_FILES
16                    WHERE tablespace_name = '&tablespace_name');

 

 

Similar Posts: