Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Max Size Datafile in 10g

Re: Max Size Datafile in 10g

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: 16 Sep 2004 03:07:28 -0700
Message-ID: <cibol0$deg@odbk17.prod.google.com>


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  Lck
Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0009ff96

Branch block dump



header address 102515788=0x61c444c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2
kdxcosdc 0
kdxconro 235
kdxcofbo 498=0x1f2
kdxcofeo 2071=0x817
kdxcoavs 1573
kdxbrlmc 789=0x315
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8018] dba: 790=0x316
col 0; len 30; (30):
2f 31 32 30 65 65 38 63 39 5f 49 64 65 6e 74 69 74 79 48 61 73 68 4d 61 70
4b 65 79 49 74
col 1; len 6; (6): 00 00 01 72 00 41
row#1[8007] dba: 791=0x317
col 0; len 5; (5): 2f 31 33 63 62
col 1; TERM
row#2[7996] dba: 792=0x318

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  Lck
Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.000a0150

Branch block dump



header address 102384716=0x61a444c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2
kdxcosdc 0
kdxconro 235
kdxcofbo 498=0x1f2
kdxcofeo 2066=0x812
kdxcoavs 1568
kdxbrlmc 21060366=0x1415b0e
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8018] dba: 21060367=0x1415b0f
col 0; len 30; (30):
2f 31 32 30 65 65 38 63 39 5f 49 64 65 6e 74 69 74 79 48 61 73 68 4d 61 70
4b 65 79 49 74
col 1; len 6; (6): 01 41 59 64 00 41
row#1[8007] dba: 21060368=0x1415b10

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US