Home » RDBMS Server » Server Administration » Minimum datafile size
Minimum datafile size [message #239006] Mon, 21 May 2007 03:55 Go to next message
sirisha.2712
Messages: 5
Registered: May 2007
Location: Mumbai
Junior Member
Hello

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
Re: Minimum datafile size [message #239012 is a reply to message #239006] Mon, 21 May 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
File header: min 4k
Bitmap: min 64k
Segment header: min 4k
Segment data block: min 1 block: 4k
Total: 76k

When you gave 73k Oracle rounds up to next block size: 76k.

Regards
Michel
Re: Minimum datafile size [message #239016 is a reply to message #239012] Mon, 21 May 2007 04:56 Go to previous messageGo to next message
sirisha.2712
Messages: 5
Registered: May 2007
Location: Mumbai
Junior Member
Thanks Michel

But I am still confused that why did oracle didn't round up 72k size why only 73k size and also if the database blocksize is different then also the minimum datafile size is different for different blocksize, is there any calculation for calculating the datafile size.
Re: Minimum datafile size [message #239038 is a reply to message #239016] Mon, 21 May 2007 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
72k is a multiple of your block size (4k), so there is nothing to round.
73k is NOT a multiple of your block size, so it rounds up to the next multiple 76k.

Regards
Michel
Re: Minimum datafile size [message #239072 is a reply to message #239038] Mon, 21 May 2007 07:29 Go to previous messageGo to next message
sirisha.2712
Messages: 5
Registered: May 2007
Location: Mumbai
Junior Member
Thanks Michel

I just want to confirm something,

As you explained,

File header: min 4k
Bitmap: min 64k
Segment header: min 4k
Segment data block: min 1 block: 4k
Total: 76k this is for 4k blocksize

and for 2k blocksize it will be in the same way right which will be total of 70k so we can create the file with 69k that means,

File header : min is 1 blocksize
Bitmap: min 64k
Segment header : min is 1 blocksize
segment data block : min is 1 block
is this the only format how the datafile size is calculated in locally managed tablespace.

But in dictionary managed, 2 blocksize is the minimum datafile size.

So how much data can be stored in both LMT & DMT minimum sized datafile?
Re: Minimum datafile size [message #239085 is a reply to message #239072] Mon, 21 May 2007 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your interest of datafile minimum size.
It is just a waste of time.
No one would want to create a datafile of 10k, doesn't it?

Regards
Michel
Re: Minimum datafile size [message #239283 is a reply to message #239085] Mon, 21 May 2007 22:33 Go to previous messageGo to next message
sirisha.2712
Messages: 5
Registered: May 2007
Location: Mumbai
Junior Member
Sorry
if i am disturbing you but it's just the curiosity that i wanted to know the minimum size and how much data can be stored if we create i know its of no use if we create that size datafile.

Re: Minimum datafile size [message #239323 is a reply to message #239283] Tue, 22 May 2007 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The minimum size is the required size to only have ONE block of data.
This is what I tried to show in my previous list.

Regards
Michel

[Updated on: Tue, 22 May 2007 00:56]

Report message to a moderator

Re: Minimum datafile size [message #239334 is a reply to message #239323] Tue, 22 May 2007 01:14 Go to previous messageGo to next message
sirisha.2712
Messages: 5
Registered: May 2007
Location: Mumbai
Junior Member
Thanx
Re: Minimum datafile size [message #510603 is a reply to message #239334] Mon, 06 June 2011 16:18 Go to previous messageGo to next message
markevaughan
Messages: 2
Registered: June 2011
Junior Member
In my DB, I've got datafiles on multiple disks. The db_block_size parameter is 8192. Some datafiles require a minimum size of 5MB, where others have a minimum of 129MB. What makes the difference? It seems that, based upon what was previously written, all of the datafiles should have the same minimum size. I'm a newbie so sorry if this is way too basic.
Re: Minimum datafile size [message #510604 is a reply to message #510603] Mon, 06 June 2011 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What makes the difference?
DBA/folks were not consistent & Oracle does not enforce any implementation details.
Re: Minimum datafile size [message #510606 is a reply to message #510604] Mon, 06 June 2011 17:15 Go to previous messageGo to next message
markevaughan
Messages: 2
Registered: June 2011
Junior Member
But it seems that there should be a setting somewhere that determines that file A on disk B can be this small whereas file C on disk D can be that small, maybe at the file system level?
Re: Minimum datafile size [message #510608 is a reply to message #510606] Mon, 06 June 2011 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But it seems that there should be a setting somewhere that determines that file A on disk B can be this small
>whereas file C on disk D can be that small, maybe at the file system level?
It is the DDL used to create diskfiles/tablespaces.

You can use DBMS_METADATA.GET_DDL to obtain the DDL.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Minimum datafile size [message #510644 is a reply to message #510603] Tue, 07 June 2011 00:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
markevaughan wrote on Mon, 06 June 2011 23:18
In my DB, I've got datafiles on multiple disks. The db_block_size parameter is 8192. Some datafiles require a minimum size of 5MB, where others have a minimum of 129MB. What makes the difference? It seems that, based upon what was previously written, all of the datafiles should have the same minimum size. I'm a newbie so sorry if this is way too basic.

Answer depends on:
- Oracle version
- Tablespace setting
- DDL on objects of this tablespace
- DML on objects of this tablespace that led to object extensions

In addition, it is not wise to think an answer that is 4 years old is still up to date now.

Regards
Michel

Previous Topic: Automatic Diagnostic Repository
Next Topic: Unable to Drop User
Goto Forum:
  


Current Time: Sat Dec 10 16:32:02 CST 2016

Total time taken to generate the page: 0.08449 seconds