Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Max Size Datafile in 10g
comments below
koert54 wrote:
> Connor,
>
> Any idea how they managed to do is ? I guess the DBA increased in
size for
> bigfile tablespaces ...
>
> kind regards
> K
>
Its an interesting guess, but seems to be wrong. Before I get into that though - no-one seems to have answered Howard's question about the number of files you can have in a bigfile tablespace. the number of files you can have in such a tablespace is exactly one.
I created a bigfile tablespace thus (the word big used somewhat ironically admittedly).
16-SEP-2004 10:03_at_NL1010>CREATE BIGFILE TABLESPACE TEST 2 DATAFILE 'C:\ORACLE\ORADATA\NL1010\1.DBF' SIZE 100M; Tablespace created.
did some tests of which more later and then issued
16-SEP-2004 10:17_at_NL1010>alter tablespace test
2 add datafile 'c:\oracle\oradata\nl1010\test02.dbf' size 100m;
alter tablespace test
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
so the limit on the tablespace size is the limit for a *bigfile* which I think we have established as 2^32 blocks, or to use a more appropriate term rather insanely large (I thought it was measured in terabytes rather than exabytes - but to be honest I can't be bothered to do the maths,I'd probably get it wrong anyway and the limit might well be 2^32 -1 blocks).
anyway your question got me sort of interested so I did the following
16-SEP-2004 10:05_at_NL1010>CREATE TABLE TBIG
2 TABLESPACE TEST
3 AS SELECT * FROM ALL_OBJECTS;
Table created.
!6-SEP-2004 10:05_at_NL1010>CREATE INDEX IDX_TEST ON TBIG(OBJECT_NAME) 2 TABLESPACE TEST; Index created.
then I went looking for the physical storage of the index - I'm after a branch block with the dba stored in it.
16-SEP-2004 10:06_at_NL1010>SELECT HEADER_BLOCK,HEADER_FILE
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME='IDX_TEST';
HEADER_BLOCK HEADER_FILE
------------ -----------
787 7
1 row selected.
16-SEP-2004 10:07_at_NL1010>ALTER SYSTEM DUMP DATAFILE 7 BLOCK 788; System altered.
This looks like this
Start dump data blocks tsn: 7 file#: 7 minblk 788 maxblk 788
buffer tsn: 7 rdba: 0x00000314 (1024/788)
scn: 0x0000.0009ffc7 seq: 0x01 flg: 0x00 tail: 0xffc70601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00000314
Object id on Block? Y
seg/obj: 0xc8f5 csc: 0x00.9ff96 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x311 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag LckScn/Fsc
Branch block dump
etc...
all the dba records are in this shortened form. (I don't know if this is an artifact of not having large amounts of used blocks in the tablespace).
In comparison I did the exact same test for a table defined in the same way in my (bog standard) tablespace users
16-SEP-2004 10:09_at_NL1010>CREATE TABLE TEST2 TABLESPACE USERS 2 AS SELECT * FROM ALL_OBJECTS; Table created.
16-SEP-2004 10:10_at_NL1010>CREATE INDEX IDX_TEST2 ON TEST2(OBJECT_NAME) 2 ;
Index created.
16-SEP-2004 10:10_at_NL1010>SELECT HEADER_BLOCK,HEADER_FILE
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME='IDX_TEST2';
HEADER_BLOCK HEADER_FILE
------------ -----------
88844 5
1 row selected.
16-SEP-2004 10:11_at_NL1010>ALTER SYSTEM DUMP DATAFILE 5 BLOCK 88845; System altered.
and the dump of the index looks like
Start dump data blocks tsn: 5 file#: 5 minblk 88845 maxblk 88845
buffer tsn: 5 rdba: 0x01415b0d (5/88845)
scn: 0x0000.000a0163 seq: 0x01 flg: 0x00 tail: 0x01630601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01415b0d
Object id on Block? Y
seg/obj: 0xc8f7 csc: 0x00.a0150 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1415b09 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag LckScn/Fsc
Branch block dump
etc..
here the dba records are in the more usual form. I also notice that the rdba records for both blocks have the same length.
So thus far I believe that the dba is changed for bigfile tablespaces but not necessarily to be larger, because you don't care about the relative file number anymore, but that the rdba isn't.
I haven't seen any research on this subject at all.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Received on Thu Sep 16 2004 - 05:07:28 CDT