ORA-3214 When Creating a Tablespace [ID 153869.1]

SQL> Create temporary tablespace temp02 tempfile  '/oracle/oradata/tjcshow/temp02.dbf' size 1M;
Create temporary tablespace temp02 tempfile  '/oracle/oradata/tjcshow/temp02.dbf' size 1M
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

***Checked for relevance on 10-Dec-2012***


Problem Description
-------------------

You are using a database which is based on 8k database blocks.

You create a tablespace and get the following error message:

   SQL> create tablespace verysmall 
     2  datafile '/ora/ora901/oradata/V901/small01.dbf'
     3  size 50k;
   create tablespace verysmall datafile '/ora/ora901/oradata/V901/small01.dbf' 
   *
   ERROR at line 1:
   ORA-03214: File Size specified is smaller than minimum required


Solution Description
--------------------
1. Either increase the size for the datafile :
 
   SQL> create tablespace verysmall
     2  datafile '/ora/ora901/oradata/V901/small01.dbf'
     3  size 88k;

   Tablespace created.

-- OR --

2. Specify the size of the extent allocation:

   SQL> create tablespace verysmall
     2  datafile '/ora/ora901/oradata/V901/small01.dbf'
     3  size 34k
     4  uniform size 10k;

   Tablespace created.


NOTE:
=====
1. In the first example, the ORA-03214 gets raised for a tablespace size of of 80 kB or smaller.
   Large values below 88 kB are rounded upward.

2. In example 2, you may also get an ORA-03249 when using ASSM.
   ASSM was first introduced with Oracle 9i. 
   Starting with 10g Release 2, ASSM will be enabled by default when you create a new tablespace.

   SQL> create tablespace verysmall datafile size 32k uniform size 10k;
   create tablespace verysmall datafile size 32k uniform size 10k
   *
   ERROR at line 1:
   ORA-03249: Uniform size for auto segment space managed tablespace should have
   atleast 5 blocks

   Note that in the example, the datafile for the tablspace is not specified as the storage was on ASM.


Explanation
-----------
1. In 9i, when you create a tablespace, it is by default created as a Locally 
   Managed Tablespace with a minimum extent size of 64K, if the local management
   is system managed.

   Therefore, you must allocate size datafiles to 64 Kbytes + 3 blocks for the bitmap blocks.
   In solution 1, the datafile size could not be set less than 64K + (3*8k)= 88k

2. If you supply the UNIFORM parameter, size the datafiles to 3 blocks + one extent size
   minimum. It offers the means to allocate the first extent without problem.
 
   Therefore in solution 2, the datafile cannot be set less than 
   10K + (3*8k) = 34K

   SQL> create tablespace verysmall
     2  datafile '/ora/ora901/oradata/V901/small01.dbf'
     3  size 32k
     4  uniform size 10K;
   create tablespace verysmall
   *
   ERROR at line 1:
   ORA-03214: File Size specified is smaller than minimum required