Hi to everyone,
I started this discussion to know your thoughts about an scenario I faced few hours ago.
Maybe there wont be a correct answer and I definitively wont feel better (to @sol.beach ) if there is one.
Is just to discuss this situation because in my work I am the only person who is DBA and I feel alone (just kidding).
But in alert log I see the error ORA-1688 and the solution it is quiet simple (well, currently I increase the maxsize of datafiles). But this is not the reason of this discussion. The reason is know if Oracle, instead to try looking for a TWO different extent size ( see 1) ) why not use the extents size currently available. I mean, for me it would be very clear if Oracle just looking for just one extent size and not for TWO different sizes and then raise an error.
I mean, Will be better if Oracle use the free extents available and print something in the alert log before raise the ORA-1688? Will have any sense? Or I just have to accept the Oracle decisions and be happy?
Hope I explained well my doubt and hope not make you waste your time reading this post.
1) About the error
Tue Jan 19 23:10:33 2016
ORA-1688: unable to extend table CDC.OLE partition P_OLE13 by 32 in tablespace CC_TBSD001P_OLE
ORA-1688: unable to extend table CDC.OLE partition P_OLE13 by 256 in tablespace CC_TBSD001P_OLE
$ oerr ora 1688
01688, 00000, "unable to extend table %s.%s partition %s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Here I want to point the extent size requesting by Oracle. 32 blocks and 256 Blocks. Two different block sizes. Two different extents, one with 32 blocks and another with 256 blocks.
2) About OS & Database version:
$ uname -a
Linux dbscc-sf1 2.6.39-400.245.1.el5uek #1 SMP Wed Dec 17 22:13:06 PST 2014 x86_64 x86_64 x86_64 GNU/Linux
SQL> SELECT banner FROM v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
5 rows selected.
I know, it is an unsupported version. I am working in the upgrade
3) About database block size
SQL> SHOW PARAMETERS db_block_size
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ --------------- -------------------------
db_block_size integer 32768 <<<<< 32K
4) About tablespace attributes
SQL> SELECT tablespace_name
,block_size
,extent_management
,segment_space_management
FROM dba_tablespaces
WHERE tablespace_name='CC_TBSD001P_OLE'
;
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
CC_TBSD001P_OLE 32768 LOCAL AUTO
1 row selected.
5) About datafiles sizing
SQL> SELECT tablespace_name
,file_id
,bytes/(1024*1024) curr_size
,maxbytes/(1024*1024) max_allowed_size
FROM dba_data_files
WHERE tablespace_name='CC_TBSD001P_OLE'
ORDER BY file_id
;
TABLESPACE_NAME FILE_ID CURR_SIZE MAX_ALLOWED_SIZE
------------------ ------- ---------- ----------------
CC_TBSD001P_OLE 40 2042 2048 /*** Here no matter the autoextend on, because maxsize is same as current size ***/
CC_TBSD001P_OLE 41 2042 2048
CC_TBSD001P_OLE 42 2042 2048
CC_TBSD001P_OLE 43 2042 2048
CC_TBSD001P_OLE 44 2042 2048
CC_TBSD001P_OLE 45 2042 2048
CC_TBSD001P_OLE 46 2042 2048
CC_TBSD001P_OLE 47 2042 2048
CC_TBSD001P_OLE 48 2042 2048
CC_TBSD001P_OLE 49 2042 2048
CC_TBSD001P_OLE 50 2042 2048
CC_TBSD001P_OLE 51 2042 2048
CC_TBSD001P_OLE 52 2042 2048
CC_TBSD001P_OLE 53 2042 2048
CC_TBSD001P_OLE 54 2042 2048
CC_TBSD001P_OLE 55 2042 2048
16 rows selected.
6) About Free extents available in Datafiles/Tablespace
SQL> SELECT tablespace_name
,count(*) total_free_extents
,blocks extent_size_in_blocks
FROM dba_free_space
WHERE tablespace_name='CC_TBSD001P_OLE'
GROUP BY tablespace_name
,blocks
ORDER BY blocks DESC
;
TABLESPACE_NAME TOTAL_FREE_EXTENTS EXTENT_SIZE_IN_BLOCKS
------------------------------ ------------------ ---------------------
CC_TBSD001P_OLE 3232 64 /*** There are 3,232 extents, each one with 64 database blocks ***/
1 row selected.
I want to point that there is not any extent with 32 and any 256 blocks size.
Please check, just are free extents with 64 blocks each. Each extent size is 64 blocks.
Thanks in advance for your time and thoughts.
Regards,
Juan M