Max Size Datafile in 10g
A. Fuentes
What is the maximum size of a datafile that Oracle 10g server
can support: a)In a Filesystem? b)In a Veritas Raw Volume?
Would it be the maximum file size that the OS filesystem can support?
Any info will be appreciated.
Best,
A. Fuentes
Anton Dischner
see your platform specific documentation,
regards,
Toni
In article <ztw1d.1241$aZ1...@fe2.texas.rr.com>, A. Fuentes
Hans Forbrich
1) Limit imposed by filesystem/Veritas. That stuff is OS dependant - you
ask the OS/filesystem/cfs vendor.
2) Limit imposed by Oracle. Those values are provided in Appendix A of the
"Reference" manual found at http://docs.oracle.com The Installation
guide, specifically the pre-install checklist chapter, discusses the
settings in more detail for OSs.
Connor McDonald
bigfile tablespaces I think are limited to 8 exabytes. (8 million
terabytes). Whether your OS can cope (or whether you have that much
disk!) is another question
hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_...@yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
Howard J. Rogers
> A. Fuentes wrote:
>>
>> Hello Fellow Oracle Users,
>>
>> What is the maximum size of a datafile that Oracle 10g server
>> can support: a)In a Filesystem? b)In a Veritas Raw Volume?
>>
>> Would it be the maximum file size that the OS filesystem can support?
>>
>> Any info will be appreciated.
>>
>> Best,
>>
>> A. Fuentes
>
> bigfile tablespaces I think are limited to 8 exabytes. (8 million
> terabytes). Whether your OS can cope (or whether you have that much
> disk!) is another question
>
> hth
> connor
I haven't bothered to check just yet... are we still allowed 64K files per
bigfile tablespace? Hence the limit would be 64,000-ish times 8 Exabytes??
I was pretty certain 8 Exabytes was the total per-tablespace limit, but I
was just wondering...
If I thought for a minute that anybody on the face of the planet, apart from
the CIA, actually *needed* 8 Exabytes, I'd care enough to look it up...
:-/
Regards
HJR
koert54
Any idea how they managed to do is ? I guess the DBA increased in size for
bigfile tablespaces ...
kind regards
K
"Connor McDonald" <connor_...@yahoo.com> wrote in message
news:4146F6...@yahoo.com...
Hans Forbrich
> I haven't bothered to check just yet... are we still allowed 64K files per
> bigfile tablespace? Hence the limit would be 64,000-ish times 8 Exabytes??
>
The direct link to the theoretical 'physical' limits of 10g is
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/limits002.htm
Yes, they show a limit of 64K files/tablespace.
They also show typical file size limited to 2^22 blocks (but OS dependant),
traditional tablespaces limited to 2^22 blocks, bigfile tabelspaces limited
to 2^32 blocks, and blocks limited to 2^15 = 32KB.
> I was pretty certain 8 Exabytes was the total per-tablespace limit, but I
> was just wondering...
>
> If I thought for a minute that anybody on the face of the planet, apart
> from the CIA, actually needed 8 Exabytes, I'd care enough to look it up...
>
However, for proper national security, I'm sure you'll agree that a year's
worth of full motion video surveillance (2GB/hour [2^32] * 8760 hours/year
[2^13]) for each of the 8 billion [2^34] individuals is required - giving a
baseline requirement of of 2^79 bytes.
And then look at http://pages.prodigy.net/jhonig/bignum/qaearth.html
koert54
cheers,
K
"Hans Forbrich" <forb...@yahoo.net> wrote in message
news:MPD1d.25689$XP3.5269@edtnps84...
Hans Forbrich
> Cool - that's what I was looking for !
>
> cheers,
> K
Unless you are a pure theorist, you need to look at the docco in any case -
there are conditions around these numbers.
Howard J. Rogers
Scary, isn't it?!
You'll be telling me you need to store more bytes than there have been
seconds since the time of the big bang next...
Thanks for the follow-up, though.
Regards
HJR
Joel Garry
> I was pretty certain 8 Exabytes was the total per-tablespace limit, but I
> was just wondering...
>
> If I thought for a minute that anybody on the face of the planet, apart from
> the CIA, actually *needed* 8 Exabytes, I'd care enough to look it up...
Rich Niemec in one of his uplifting flights of fancy seminars asked
people to email him ideas of what you could use such an amount of
information for. I don't believe anyone came up with anything good.
He referred to some research done at UCLA on the total information
quantity in the world currently at 1 Exabyte, but I don't recall who
did it.
jg
--
@home.com is bogus.
http://www.oit.ucla.edu/Documents/10
Mark Townsend
Two potential areas that I know of (at least, we cite them as the reason
we lifted the restrictions in 10g) that may cause data explosions in the
next 5 years
Life sciences (storage of 'individualized' genetic code maps ?) and CERN
(the new linear accelerator and the hunt for the God particle)
Noons
> However, for proper national security, I'm sure you'll agree that a year's
> worth of full motion video surveillance (2GB/hour [2^32] * 8760 hours/year
> [2^13]) for each of the 8 billion [2^34] individuals is required - giving a
> baseline requirement of of 2^79 bytes.
>
> And then look at http://pages.prodigy.net/jhonig/bignum/qaearth.html
Hehehe! Typical CIA: they'd need to store more data than there
are atoms in the earth! And they *still* can't find Osama.
The total IS greater than the sum of the parts...
Howard J. Rogers
> Joel Garry wrote:
>> "Howard J. Rogers" <h...@dizwell.com> wrote in message
>> news:<4146fc78$0$5727$afc3...@news.optusnet.com.au>...
>>
>>>I was pretty certain 8 Exabytes was the total per-tablespace limit, but I
>>>was just wondering...
>>>
>>>If I thought for a minute that anybody on the face of the planet, apart
>>>from the CIA, actually *needed* 8 Exabytes, I'd care enough to look it
>>>up...
> Two potential areas that I know of (at least, we cite them as the reason
> we lifted the restrictions in 10g) that may cause data explosions in the
> next 5 years
>
> Life sciences (storage of 'individualized' genetic code maps ?) and CERN
> (the new linear accelerator and the hunt for the God particle)
Really?
Let me do some maths for a second (warning! warning!).
The human genome is about 3,000,000,000 bases long.
Each base is just a single (English) letter, so can be represented in 1
byte. A human genome would thus require 375,000,000 bytes of storage, which
is about 375MB.
For the approximately 6 billion people on the planet, you would therefore
need 375MB*6,000,000,000 = 2,250,000,000,000MB, which is 2,250,000,000GB,
2,250,000TB, 2,250 Petabytes or 2.25 Exabytes.
So you could comfortably fit an individualised genetic code map for every
person on the face of the Earth in less than one third of a single bigfile
tablespace in a single database.
Were Oracle corporation anticipating some sort of population explosion in
the next five years, or something?!
Mathematical joking aside, it's an interesting idea: two totally
off-the-planet requirements dictating the internal workings of a
mass-produced RDBMS. I dare say it doesn't happen very often.
You *sure* it wasn't just so the marketing department could say theirs was
bigger than IBM's??!
</irony>
Regards
HJR
Hans Forbrich
Note quite. I note that 2^79 is about 6.04e+23
However, Osama being able to hide is now in a mole-hole is now definintely
understood! <groan>
Mark Townsend
> Mark Townsend wrote:
>
>
>>Joel Garry wrote:
>>
>>>"Howard J. Rogers" <h...@dizwell.com> wrote in message
>>>news:<4146fc78$0$5727$afc3...@news.optusnet.com.au>...
>>>
>>>
>>>>
>>>>If I thought for a minute that anybody on the face of the planet, apart
>>>
>>>>from the CIA, actually *needed* 8 Exabytes, I'd care enough to look it
>>>
>>>>up...
>
> He referred to some research done at UCLA on the total information
> quantity in the world currently at 1 Exabyte, but I don't recall who
> did it.
>
>
>>Two potential areas that I know of (at least, we cite them as the reason
>>we lifted the restrictions in 10g) that may cause data explosions in the
>>next 5 years
>>
>>Life sciences (storage of 'individualized' genetic code maps ?) and CERN
>>(the new linear accelerator and the hunt for the God particle)
>
>
>
> Really?
Really.
> Let me do some maths for a second (warning! warning!).
>
> The human genome is about 3,000,000,000 bases long.
> Each base is just a single (English) letter, so can be represented in 1
> byte. A human genome would thus require 375,000,000 bytes of storage, which
> is about 375MB.
>
> For the approximately 6 billion people on the planet, you would therefore
> need 375MB*6,000,000,000 = 2,250,000,000,000MB, which is 2,250,000,000GB,
> 2,250,000TB, 2,250 Petabytes or 2.25 Exabytes.
Right - and if the 1 Exabyte figure for all current information is
correct then this would represent a 200+% increase in a very short time
frame. Sort of what I would call a data explosion.
> So you could comfortably fit an individualised genetic code map for every
> person on the face of the Earth in less than one third of a single bigfile
> tablespace in a single database.
>
> Were Oracle corporation anticipating some sort of population explosion in
> the next five years, or something?!
Nope - obviously there is a fair amount of serendipity in the 8 Exabyte
figure. I don't think we seriously consider that anybody will need this
in the short term future. However, it was fairly easy to predict that
petabyte plus storage environments will be common place in the next 2-3
years, especially as more and more digital data becomes managed online
for longer periods of time (Oracle itself already has around 3 petabytes
of storage inhouse - including (and I joke not) over 3 terabytes of
predominantly powerpoint content in a single database)
So we knew limits needed to be raised, it just so happens that the
bigfile work gave us a nice high 8 exabyte watemark. And we had a couple
of customers that would validate out thinking for us, and can see
themselves going to multiple petabytes in the next few years.
> Mathematical joking aside, it's an interesting idea: two totally
> off-the-planet requirements dictating the internal workings of a
> mass-produced RDBMS. I dare say it doesn't happen very often.
You may be surprised - there are a number of them.
Cern, for instance, will generate around 10 Petabytes of info a year
when they fire the accelerator up. It could run for 10-20 years.
A certain company that does record and backup management and electronic
vaulting (basically for the world) predicts massive movement of content
from paper (and glass) and from current tape and offline storage to
online-all-the-time storage over the next few years - and these guys
literally have mountains (hint) of content that they manage - films,
xrays and medical records, 50 years of email, even source code :-).
Digitizing and storing everything that Hollywood (and Bollywood) has
produced will require huge storage.
Another group (not a company) wants to maintain the family tree history
of every person that has lived on the face of the earth to date. Combine
this with medical history and genomic maps and the figure is very large.
The real kicker - sensor data - imagine a world where EVERYTHING is
tagged with RFIDs (including, perhaps, even money), and sensors
everywhere detect what is currently in and out of range, going from and
coming to. Continuously. Or every vehicle on the face of the earth
reporting current engine statistics and mileage per gallon information.
Or every household reporting electricity usage at the 5 second interval
for spot buying from producers/suppliers. Or in the note too distant
future - the human ADDM device that collects and reports on our own
individual health statistics.
And then you have the mapping guys - earth maps, sea maps, space maps,
body maps, genomic maps, mind maps. Maps with multiple dimensions. Maps
with temperatures, frequencies etc etc etc. The sky is literally the
limit for this.
And then this is just the raw stuff - to use all this data, it has to be
indexed and summarized and backed up - which could increase the actual
storage foot print by a factors of 10 or more.
And the thing is is that this is just what we can see now. Storage
prices will continue to plummet, and new applications will be developed
to take advantage of this fact - the iPod for instance. We don't even
know what the devices of the future will require.
>
> You *sure* it wasn't just so the marketing department could say theirs was
> bigger than IBM's??!
Nope - we will see single figure petabyte databases very soon (they are
being built as we speak), and within 10 years we will see 100+ petabyte
databases and some early exabyte environments. And it's not just
marketing - Jim Gray has an interesting presentation on this - see
http://www.research.microsoft.com/~Gray/talks/Gray%20IIST%20Personal%20Petabyte%20Enterprise%20Exabyte.ppt
> </irony>
>
> Regards
> HJR
>
Daniel Morgan
>>> Life sciences (storage of 'individualized' genetic code maps ?) and CERN
>>> (the new linear accelerator and the hunt for the God particle)
>>
>> Really?
>
> Really.
>
>> Let me do some maths for a second (warning! warning!).
>>
>> The human genome is about 3,000,000,000 bases long.
>> Each base is just a single (English) letter, so can be represented in 1
>> byte. A human genome would thus require 375,000,000 bytes of storage,
>> which
>> is about 375MB.
>>
>> For the approximately 6 billion people on the planet, you would therefore
>> need 375MB*6,000,000,000 = 2,250,000,000,000MB, which is 2,250,000,000GB,
>> 2,250,000TB, 2,250 Petabytes or 2.25 Exabytes.
>
>
> Right - and if the 1 Exabyte figure for all current information is
> correct then this would represent a 200+% increase in a very short time
> frame. Sort of what I would call a data explosion.
And while you can't say it ... I can.
Three of the biggest are two government agencies ... likely we are all
in those databases ... and a certain multimedia company busy squirreling
away very very large BLOBs.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Howard J. Rogers
It was a good answer. But I have to say, a little cynically, I doubt the
world will be a better place for all this data.
Seems to me that in the rush to capture everything, we've forgotten how to
precis or discriminate between the worthless and the priceless.
But that's a topic for another day.
Thanks for the link, by the way.
Regards
HJR
Noons
>
> Nope - we will see single figure petabyte databases very soon (they are
> being built as we speak), and within 10 years we will see 100+ petabyte
> databases and some early exabyte environments. And it's not just
> marketing - Jim Gray has an interesting presentation on this - see
> http://www.research.microsoft.com/~Gray/talks/Gray%20IIST%20Personal%20Petabyte%20Enterprise%20Exabyte.ppt
>
Very interesting. Thanks for an excellent link. In another 10 years I
reckon all we do in terms of storage will be completely upside down.
I had a similar conversation with Barry Mathews of Oracle Australia
when he introduced the 10g stuff. We both saw it going the way
of no need to worry about disk configuration or space usage in another
two versions of Oracle. As for access speed, partitioning will be the
norm. And the partitioning algorithm will be defined in XML and be
dynamic. Physically, there will probably be a layer of hash partitioning
and that's it. Then on top of that a layer of XML-to-hash indexing
(for lack of a better term) and then just an XML dictionary to link
them all (one ring rules them all?<G>). No other way of making sense
of so much data and indexing it in such a way that it will be usable.
A parallel can be drawn with traditional libraries. Most store
many more books than anyone can read in their lifetimes (Access bandwidth).
The speed of reading is the limiting factor. Unless you have a librarian
that knows what he's doing and can catalog and index all that jazz into
something that will send you directly to the info you wanted. Or narrow
it down to just what you want anyway.
The same happens with all this exabyte stuff. There is no way with
current access bandwidth that anyone can search it intelligently.
Even the indexing has to go in before the data goes in: you simply
can't afford to build indexes after the fact. Too many resources.
That opens up a tremendous new job category: data librarian.
A person who can a-priori define how to create a catalog structure
that will make your data usable. And who can make it happen, when
you want that video bit about the event last year in which you thought
it would be interesting to promote your product this year.
And so on.
Ain't storage technology just riveting?
Dusan Bolek
> > Cern, for instance, will generate around 10 Petabytes of info a year
> > when they fire the accelerator up. It could run for 10-20 years.
> world will be a better place for all this data.
>
> Seems to me that in the rush to capture everything, we've forgotten how to
> precis or discriminate between the worthless and the priceless.
>
> But that's a topic for another day.
>
> Thanks for the link, by the way.
>
> Regards
> HJR
Just one small remark about CERN to point out that sometimes huge data
doesn't mean worthless heap of everything. I had a small discussion
with the guy working for CERN on this collider business and if I
understand him correctly (probably not) the reason for this huge
storage is this:
The very principle of research with colliders is to bounce various
particles against each other and waiting what is going to happen. It
can be described as some odd kind of lottery, because almost all
collisions are not winning (e.g. nothing interesting happened) and
only just one in millions can be a catch. The problem is that you
can't tell if something happened or not just by "looking" at. So the
way how to deal with this is to store data about all collisions and
then use complicated program analysing certain patterns to get the
winning ticket. The probability of getting the game winner is
increasing with increasing of data available for analysis. So they not
only want to store really huge amount of data, but the also need them.
--
Dusan Bolek
http://www.db-support.com
Email: spa...@seznam.cz
Pls add "Not Guilty" to the subject, otherwise your email will face an
unpleasant end as SPAM.
Joel Garry
Listen to Scott McNealy. Increase productivity by prohibiting ppt's.
:)
>
> So we knew limits needed to be raised, it just so happens that the
> bigfile work gave us a nice high 8 exabyte watemark. And we had a couple
> of customers that would validate out thinking for us, and can see
> themselves going to multiple petabytes in the next few years.
>
>
> > Mathematical joking aside, it's an interesting idea: two totally
> > off-the-planet requirements dictating the internal workings of a
> > mass-produced RDBMS. I dare say it doesn't happen very often.
>
>
> You may be surprised - there are a number of them.
>
> Cern, for instance, will generate around 10 Petabytes of info a year
> when they fire the accelerator up. It could run for 10-20 years.
>
> A certain company that does record and backup management and electronic
> vaulting (basically for the world) predicts massive movement of content
> from paper (and glass) and from current tape and offline storage to
> online-all-the-time storage over the next few years - and these guys
> literally have mountains (hint) of content that they manage - films,
> xrays and medical records, 50 years of email, even source code :-).
> Digitizing and storing everything that Hollywood (and Bollywood) has
> produced will require huge storage.
>
> Another group (not a company) wants to maintain the family tree history
> of every person that has lived on the face of the earth to date. Combine
> this with medical history and genomic maps and the figure is very large.
Just tell them it's not OK to convert dead people! They agreed not to
do that!
>
> The real kicker - sensor data - imagine a world where EVERYTHING is
> tagged with RFIDs (including, perhaps, even money), and sensors
"...put a $50 bill in the microwave and Hamilton's eye exploded..."
> everywhere detect what is currently in and out of range, going from and
> coming to. Continuously. Or every vehicle on the face of the earth
> reporting current engine statistics and mileage per gallon information.
{For those outside the US,] there is a criminal trial going on (in
Redwood City, no less), where the police put a GPS tracking device on
the truck of the fellow suspected of murdering his pregnant wife. It
has come out in trial that at one point the GPS recorded his Chevy was
going 30,000MPH...
> Or every household reporting electricity usage at the 5 second interval
> for spot buying from producers/suppliers. Or in the note too distant
When they put in my new water meter, nobody told the meter reader
about it (even though the new one is obvious to casual passers-by, and
they removed the old one). So they've been "estimating" my usage
based on past usage, which is way under, because the new meter has
higher pressure, so more water gets used on landscaping while I
recalibrate it. So my next water bill will be several thousand
dollars...
> future - the human ADDM device that collects and reports on our own
> individual health statistics.
Don't get me started on ^*%^*!#@ programs that keep kicking me out as
non-average! I'm not dead yet! I have a ridiculously low Medical
Record Number at Kaiser, that should count for something...
Seems that technology is outpacing analysis...
>
> And then you have the mapping guys - earth maps, sea maps, space maps,
> body maps, genomic maps, mind maps. Maps with multiple dimensions. Maps
> with temperatures, frequencies etc etc etc. The sky is literally the
> limit for this.
And all I want is a stable link to "my house from space."
http://terraserver.microsoft.com/addressimage.aspx?t=1&s=10&alon=-117.21453416&alat=33.22836488&w=1&ref=A&Lon=-117.2142639974&Lat=33.228288466
>
> And then this is just the raw stuff - to use all this data, it has to be
> indexed and summarized and backed up - which could increase the actual
> storage foot print by a factors of 10 or more.
>
> And the thing is is that this is just what we can see now. Storage
> prices will continue to plummet, and new applications will be developed
> to take advantage of this fact - the iPod for instance. We don't even
> know what the devices of the future will require.
You know what else is plummeting? Use of error correction. Bad data!
Bad! Here comes the rolled up reuseable newspaper!
>
> >
> > You *sure* it wasn't just so the marketing department could say theirs was
> > bigger than IBM's??!
>
> Nope - we will see single figure petabyte databases very soon (they are
> being built as we speak), and within 10 years we will see 100+ petabyte
> databases and some early exabyte environments. And it's not just
> marketing - Jim Gray has an interesting presentation on this - see
> http://www.research.microsoft.com/~Gray/talks/Gray%20IIST%20Personal%20Petabyte%20Enterprise%20Exabyte.ppt
Ahh, thank you for that. Don't blame Rich, my memory was off by a
factor of 10 and the wrong UC campus:
http://www.sims.berkeley.edu/research/projects/how-much-info-2003/execsum.htm#summary
jg
--
@home.com is bogus.
"A libertarian is a conservative who still gets high." - Drew Carey
Joel Garry
Ironically, the patterns they look for tend to be like "right-handed
spirals emanating from a certain area," which is, of course, the
essence of visualization - human visual pattern recognition is a very
parallelized operation. Whether some analogy of that (like neural
computing) or something else will be useful to sort through the data
remains to be, uh, seen.
jg
--
@home.com is bogus.
And so, my undergraduate degree and Oracle will merge sometime after
my lifespan.
Niall Litchfield
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@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@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@NL1010>CREATE TABLE TBIG
2 TABLESPACE TEST
3 AS SELECT * FROM ALL_OBJECTS;
Table created.
!6-SEP-2004 10:05@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@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@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@NL1010>CREATE TABLE TEST2 TABLESPACE USERS
2 AS SELECT * FROM ALL_OBJECTS;
Table created.
16-SEP-2004 10:10@NL1010>CREATE INDEX IDX_TEST2 ON TEST2(OBJECT_NAME)
2 ;
Index created.
16-SEP-2004 10:10@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@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
Howard J. Rogers
> 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.
Whilst I mull over the detail of your post, just a quick 'thank you' for
that answer. I should have done the test myself a while ago...
So: I can have an 8 Exabyte tablespace, comprised of one single 8 Exabyte
data file. Right?
I guess I'm still confused: can I still have 64,000-ish such tablespaces?
So that my total database storage is whatever 64,000 times a stupidly large
number is?
That doesn't seem to square with the discussion about 'there will be
*peta*byte-sized databases real soon now', because it would imply Oracle is
actually and already ready for the Yentabyte database...
I suppose I will have to stop being lazy and see for myself... Now where did
I put that 60 Yentabyte Seagate USB hard drive...
It all still seems like just so much navel-gazing to me!
Regards
HJR
Niall Litchfield
I said I didn't want to do the maths - its all your fault, and bear in
mind this is maths from an economist.
the max size of a datafile is 2^32 blocks. (bigfile)
the max no of datafiles is 65533 (lets take a short cut cos its absurd
anyway and say its 64k = 2^16 files.
the max block size is 32k = 2^15 bytes
so I reckon that gives the maximum size of an oracle database as
(2^15) * (2^16) * (2^32) = 2^63 bytes = 8 Exabytes
So I reckon (just under) 8 Exabytes is the largest Database you can
have and it would be composed of 64k tablespaces each composed of one
128 terabyte file.
As far as peta-byte sized databases go I suspect there are several,
even non intelligence ones, mostly associated with highly impressive
and highly expensive science. (I'm thinking particle physics and not
molecular biology though).
Niall
p.s. I'm happy to conclude that we don't need to bother about physical
database size limits (other than what hardware and os can we run for
it) for the supported lifetime of Oracle 10g, and to be honest probably
longer - where are you going to get a grid that can adequately manage
all that data.
Mark Townsend
>>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.
>
>
> So: I can have an 8 Exabyte tablespace, comprised of one single 8 Exabyte
> data file. Right?
>
At 32k data blocks, a bigfile tablespace can contain a single 128
Terabyte datafile. Times 64,000 and you have your 8 exabytes. Note that
smaller block sizes result in smaller databases.