We receive this error ORA-01652 while inserting data,creating index, table when we don’t have enough space in tablespace.
Solution is to add sufficient space in the tablespace identified in the error ORA-01652 There are three ways to do it
- Add datafile to the tablespace
- Resize the existing datafile in the tablespace
- auto-extent the datafile in the tablespace
This can happen with permanent tablespace and Temporary tablespace Each one is explained below with example below
How to Fix ORA-01652 in Permanent Tablespace
- we can use ALTER TABLESPACE to add datafile in tablespace like
Syntax ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile> Example ALTER TABLESPACE TEST ADD DATAFILE '/u01/oracle/TEST/oradata/test_4.dbf' SIZE 1000M With Oracle ASM ALTER TABLESPACE TEST ADD DATAFILE '+DATA ' SIZE 1000M; Alter tablespace to add multiple datafilesSQL>alter tablespace tablespace_name add datafile
<’destination 1’> size 10M autoextend on next 124M,
<’destination 2’> size 10M autoextend on next 124M,
<’destination 3’> size 10M autoextend on next 124M;
2. we can use ALTER TABLESPACE resize datafile in tablespace like
First find the location of present datafiles in the tablespace select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%&1%'; Then look at the space in the mount where datafile is present using df -k command And then resize the datafile Syntax Alter database datafile <datafile> resize <size> Example select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'EXAMPLE_DB'; /u01/oracle/testdb/example_db01.dbf 1000 Alter database datafile '/u01/oracle/testdb/example_db01.dbf' resize 2000M; With Oracle ASM diskgroups ALTER DATABASE DATAFILE '+DATA/testdb/example_db01.dbf' resize 2000M;
3. How to modify the existing Datafile auto extend characteristics in a Tablespace
you can modify datafile auto extend using alter database datafile option
Syntax
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;
How to in TEMP tablespace
If this is happening for the temporary tablespace, we can add temp-file to get it working and at the same time we can look out for the session using high temporary tablespace to clear the temp space. We can then tune that sql so that it does not consume high amount of temporary tablespace and does not cause issues to the Other oracle session in getting the temp tablespace
We can get the total space in the TEMP tablespace using the command
select sum(bytes)/1024/1024/1024 Total_space from dba_temp_files;
Free space using the command
For Non RAC SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;
For RAC select inst_id, tablespace_name, segment_file, total_blocks, used_blocks, free_blocks, max_used_blocks, max_sort_blocks from gv$sort_segment;
And then add temp file using the below command
Syntax ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size> Example ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/TEST/oradata/temp_4.dbf' SIZE 1000M; With ASM ALTER TABLESPACE TEST ADD TEMPFILE '+DATA ' SIZE 1000M;
We can check for the session using temp segment using the query
col inst_id format 999
col sid format 99999
col status format a11
col program format a20 trunc
col machine format a17 trunc
col action format a39
col module format a39
col blocks heading "TEMP BLOCKS"
SELECT b.blocks, s.inst_id, s.sid, s.status,
s.action,
s.program,
s.machine,
s.module
FROM gv$session s,
gv$sort_usage b
WHERE s.saddr = b.session_addr
and s.inst_id = b.inst_id
and b.blocks > 100000
order by b.blocks desc;
Hope you like the details on ORA-01652 error and solutions . Please do let me know the feedback on it and how do you solve this problem in your oracle database environment
Related Article
How to check tablespace in Oracle Database
What you need to know about create tablespace statement in Oracle
How to shrink the datafile in Oracle
How to move LOB segment to another tablespace