Thinking Out Loud

January 7, 2016

ORA-01652: unable to extend temp segment even with Autoextend ON

Filed under: 11g,12c,oracle — mdinh @ 12:22 am

I have encountered a very strange feature and wondering if anyone has experienced this.

Tracing was enabled for “ORA-01652: unable to extend temp segment” using alter system set events ‘1652 trace name errorstack level 1’;.

The tablespace is configured to autotextend by 64MB with max size 31GB and currently has 4MB Free.

“create index x on abc(txt) tablespace users;” failed with ORA-01652: unable to extend temp segment by 128 in tablespace USERS.

I understand objects are first created as temporary segments in USERS tablespace and this does not occur if there is no tracing enable for event 1652.

Any ideas or explanations?

ARROW:(MDINH@hawklas):PRIMARY> alter database datafile 4 autoextend on next 64m;

Database altered.

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 echo on
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            160 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
          4             6          7

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> ALTER SESSION SET TRACEFILE_IDENTIFIER = "mdinh_test2";

Session altered.

ARROW:(MDINH@hawklas):PRIMARY> alter system set events '1652 trace name errorstack level 1';

System altered.

ARROW:(MDINH@hawklas):PRIMARY> create index x on abc(txt) tablespace users;
^Ccreate index x on abc(txt) tablespace users
                  *
ERROR at line 1:
ORA-01013: user requested cancel of current operation



ARROW:(MDINH@hawklas):PRIMARY> alter database datafile
  2   '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m;

Database altered.

ARROW:(MDINH@hawklas):PRIMARY>
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$ tail -20  alert_hawklas.log
  Current log# 3 seq# 75 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_3_c7129gc8_.log
  Current log# 3 seq# 75 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_3_c7129go4_.log
Wed Jan 06 15:27:16 2016
Archived Log entry 4037 added for thread 1 sequence 74 ID 0xbc2f7b69 dest 1:
Wed Jan 06 15:46:41 2016
OS Pid: 3999 executed alter system set events '1652 trace name errorstack level 1'
Wed Jan 06 15:47:08 2016
Errors in file /u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/hawklas_ora_3999_mdinh_test2.trc:
ORA-01652: unable to extend temp segment by 128 in tablespace USERS
Wed Jan 06 15:50:09 2016
Thread 1 advanced to log sequence 76 (LGWR switch)
  Current log# 1 seq# 76 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_1_c7128lk6_.log
  Current log# 1 seq# 76 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_1_c7128lvc_.log
Wed Jan 06 15:50:16 2016
Archived Log entry 4038 added for thread 1 sequence 75 ID 0xbc2f7b69 dest 1:
Wed Jan 06 15:51:01 2016
alter database datafile
 '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m
Completed: alter database datafile
 '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$

Updated Jan 07, 2016 based on comments.

There are sufficient space from the OS.
Size of the table is 96MB and size of the created index is 104MB

From the alert log, the ORA-01652 did not occur if tracing is turned off for the event – alter system set events ‘1652 trace name errorstack off’;

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;
Enter value for tbs: users

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            160 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
          4             6          7

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='ABC';

SUM(BYTES)/1024/1024
--------------------
                  96

ARROW:(MDINH@hawklas):PRIMARY> !df -h /oradata
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg01-LogVol01
                       59G   35G   22G  62% /

ARROW:(MDINH@hawklas):PRIMARY> alter system set events '1652 trace name errorstack off';

System altered.

ARROW:(MDINH@hawklas):PRIMARY> create index x on abc(txt) tablespace users;

Index created.

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 echo on
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            288 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
         29            30          5

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='ABC';

SUM(BYTES)/1024/1024
--------------------
                  96

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='X';

SUM(BYTES)/1024/1024
--------------------
                 104

ARROW:(MDINH@hawklas):PRIMARY>
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$ tailf alert_hawklas.log
Thu Jan 07 13:34:50 2016
Starting background process CJQ0
Thu Jan 07 13:34:51 2016
CJQ0 started with pid=26, OS id=3088
Thu Jan 07 13:37:28 2016
OS Pid: 3097 executed alter system set events '1652 trace name errorstack off'
Thu Jan 07 13:37:59 2016
Starting background process SMCO
Thu Jan 07 13:37:59 2016
SMCO started with pid=24, OS id=3101
Thu Jan 07 13:41:03 2016
Thread 1 advanced to log sequence 78 (LGWR switch)
  Current log# 3 seq# 78 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_3_c7129gc8_.log
  Current log# 3 seq# 78 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_3_c7129go4_.log
Thu Jan 07 13:41:10 2016
Archived Log entry 4040 added for thread 1 sequence 77 ID 0xbc2f7b69 dest 1:
Advertisements

4 Comments »

  1. How big is the table which is indexed?

    select bytes from dba_segments where segment_name = ‘ABC’;

    create index x on abc(txt) tablespace users;

    Regards,
    Martin

    Comment by Martin Decker — January 7, 2016 @ 1:43 pm | Reply

    • Thanks for your comment. Size of the table is 96MB and size of the created index is 104MB.

      Comment by mdinh — January 7, 2016 @ 10:28 pm | Reply

  2. Hello, Oracle says it should be able to increase the size of tablespace USERS (from 160 MB currently all the way to 32GB if needed), but somehow it cannot (ORA-01652: unable to extend…), so maybe the cause lies outside of Oracle. Check the relevant ASM diskgroup and the “/oradata/HAWKLAS/datafile/” mountpoint: maybe be you’ve run out of disk space?

    Comment by Dear DBA Frank (@fdernoncourt) — January 7, 2016 @ 1:49 pm | Reply

    • Thanks for your comment. There is sufficient space from the FS. Updated blog.

      Comment by mdinh — January 7, 2016 @ 10:27 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: