Introduction.

In February  i had set up a database ( 11.2.02.) on Linux with ASM.  One funny thing there was that the only data tablespace was requested to be in size of a 4.5 TB  just for holding data and indexes. Recently I was asked by the the Application team to break up the tablespace into two tablespaces. In itself that is not that big a deal of course but during recreate of the tablespace i got a nice error messages which triggers me to this mini post :

Details.

This is how I had set up the create / alter of the tablespace at first:

create tablespace MY_DATA datafile  '+MYTS_DATA01' size 50G autoextend off
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO autoallocate;

create tablespace MY_DATA
*
ERROR at line 1:
ORA-01144: File size (6553600 blocks) exceeds maximum of 4194303 blocks

After  using Google ( always happy to find blogs that point into the right direction . I found out that the error message was triggered by the db_block_size was only 8k in my database.  (you can also take a look at  ORA-01144 on Create Tablespace or Resize of Datafile (Doc ID 104280.1).

After that the fix  for this issue was that datafile size simply needed to be smaller for my action to become succesfull:

My example for one of the tablespaces now looks like this :

#!/bin/ksh
#

${ORACLE_HOME}/bin/sqlplus /nolog << EOF
set echo on
set feedback on
spool CreaAdd.lst
--
connect / as sysdba
set echo on
set feedback on
set timing on

drop tablespace MY_DATA including contents and datafiles;

create tablespace MY_DATA
  datafile  '+MYTS_DATA01' size 30G autoextend off
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO autoallocate;
alter database backup controlfile to trace;
EOF

x=1
while [ $x -le 68 ]
do
 $ORACLE_HOME/bin/sqlplus /nolog << EOF
 set echo on
 set feedback on
 set timing on
 spool step6_MY_DATA_$x.lst
 connect / as sysdba
 alter tablespace MY_DATA add datafile  '+MYTS_DATA01' size 30G autoextend off;
 quit 
EOF
x=$(( $x + 1 ))
done

exit

Happy end .. now i only need wait till the datafiles are added .

Happy reading ,

Mathijs.