ORA-01652: unable to extend temp segment by 128 in tablespace users

“Be alpha”

You find this ora-01652 error often ,when there are huge sorts happening in the database instance.

check the size of the temporary tablespace

select sum(TABLESPACE_SIZE/1024/1024/1024) as TOTAL_SIZE_GB, sum(FREE_SPACE/1024/1024/1024) from dba_temp_free_space;

I insert some records into table

insert into test1 select * from test;

Monitor the sort utilization

select * from v$sort_usage;

Solution:

1)SMON background process cleans up the temporary segments and extents automatically

2)Add a new tempfile to the temp tablespace

SQL> alter tablespace temp add tempfile ‘/home/oracle/oradata/temp02.dbf’ size 500M;

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: