Home » RDBMS Server » Server Administration » CREATE TABLESPACE AND ORA-03214 (10.2.0.1)
CREATE TABLESPACE AND ORA-03214 [message #548168] Tue, 20 March 2012 10:28 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
when i create tablespace specifiled size less than 81,it occurs an error :ora-03214,the following code


SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 64K;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 64K
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 65K;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 65K
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 79K;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 79K
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 80K;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 80K
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/lonion/TEST01.dbf' SIZE 81K;

Tablespace created.

SQL> SELECT TABLESPACE_NAME,BLOCK_SIZE ,INITIAL_EXTENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TEST';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
TEST                                 8192          65536



why the size is 81 can create tablespace successfully?
Re: CREATE TABLESPACE AND ORA-03214 [message #548184 is a reply to message #548168] Tue, 20 March 2012 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The file must at least contains the file header which is 64K and then it must at least be able to contain one segment.
The minimal size for this second condition depends on the type of tablespace but anyway it is at least 2 blocks.
So 64K+2*8K=80K.

Regards
Michel
Re: CREATE TABLESPACE AND ORA-03214 [message #548316 is a reply to message #548184] Wed, 21 March 2012 06:40 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
SQL> CREATE TABLESPACE TEST2 DATAFILE '/u01/app/oracle/oradata/lonion/TEST02.dbf' SIZE 72K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50K;
CREATE TABLESPACE TEST2 DATAFILE '/u01/app/oracle/oradata/lonion/TEST02.dbf' SIZE 72K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50K
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> CREATE TABLESPACE TEST2 DATAFILE '/u01/app/oracle/oradata/lonion/TEST02.dbf' SIZE 73K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50K;

Tablespace created.

SQL> 



in this case,how to calculate the minimum required(i mean 73)?
Re: CREATE TABLESPACE AND ORA-03214 [message #548318 is a reply to message #548316] Wed, 21 March 2012 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the type of tablespace.
Anyway, who wants to create a tablespace that can't contain any object?
Who wants to create a tablespace of 73K?
This is the kind of study that is nothing but a waste of time.

Regards
Michel
Re: CREATE TABLESPACE AND ORA-03214 [message #548320 is a reply to message #548318] Wed, 21 March 2012 06:57 Go to previous message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
yeah ,a tablespace of 73k is impossible.
i just want to know the relation between the size of the tablespace and the size of the extent.
Previous Topic: Database users size
Next Topic: TO KNOW USED SPACE OF DATA FILE
Goto Forum:
  


Current Time: Thu Aug 21 09:34:27 CDT 2014

Total time taken to generate the page: 0.10610 seconds