initial storage value

[anandkl]

Hi friends.
Can we set initial storage parameter value for a table greater than 2gb.

regards
anandkl

[sreddy]

Why do you think you can't ? You can

[anandkl]

I tried this command and it dint work

create table mybat(name varchar2(30))
2 storage (initial 3000M NEXT 3000M minextents 1);
storage (initial 3000M NEXT 3000M minextents 1)
*
ERROR at line 2:
ORA-02218: invalid INITIAL storage option value

[sreddy]

I thought differently. Like initial storage clause for a table of 2+ GB in Size.

Oracle says as follows:

INITIAL
Specify in bytes the size of the object's first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes.

The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks for nonbitmapped segments or 3 data blocks for bitmapped segments, plus one data block for each free list group you specify. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks, and rounds up to the next multiple of 5 data blocks for values greater than 5 data blocks.

[thanigai]

Sreddy,
It may be a wrong qtn???.
please update me on this.....
IF we have 2 datafiles of 1500m each...having initial 2gb next 100m....
1.oracle start writing from/after 5blocksize
2.each file size is less than 2gb so will it continue in 2nd file..?????.
3.If Single extent cannot be shared among the files...it will fail and give error 1653 right?.
Thanks.
Thanigaivasan.

[dave777]

What is the size of your tablespace?
Actually, what is the size of the datafiles in that tablespace?

If your datafiles' size is less then intitial extent you are
specifing, you won't be able to create that tabel.

Regards.

[sreddy]

Originally posted by thanigai
Sreddy,
It may be a wrong qtn???.
please update me on this.....
IF we have 2 datafiles of 1500m each...having initial 2gb next 100m....
1.oracle start writing from/after 5blocksize
2.each file size is less than 2gb so will it continue in 2nd file..?????.
3.If Single extent cannot be shared among the files...it will fail and give error 1653 right?.
Thanks.
Thanigaivasan.

As dave said, I think you cannot specify an extent bigger than the size of datafile. Thats what Oracle meant by saying it depends upon the OS. If OS supports datafile greater than 2GB which inturn allow you to specify bigger extent size.

[thanigai]

Thank you.
That solves my problem. extent size must be less than the file size irrespective of tablespace size(total file sizes).
Thanks.
Thanigaivasan.

[sreddy]

I did test creating a table extent bigger than datafile size of 2GB, its failed and when I modfied extent size from 2100 to 2000 Mb it accepted.

That clarifies always the a table can't extend its single extent beyond the file size limit.

[marist89]

http://otn.oracle.com/docs/products/...block.htm#2528

When you specify an INITIAL value, oracle allocates the extent from contiguous blocks. Since there are a finite number of blocks in a data file, you can't allocate more than that finite number.