| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Max datafilesze in ORACLE8i
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C018B9.6FF79810
Content-Type: text/plain;
charset="iso-8859-1"
Hi List,
I am not sure whether this question is put or not in the list.
Is there any limit oracle imposes on the size of a datafile.Somebody said it
is 2GB.
Can any one of you tell me the max datafile size possible and also the
oracle recommended size.It is for Oracle 8.1.5.
Thanx
Uma Shankar
> ----------
> From: Davide Bellesi[SMTP:davide.bellesi_at_visto.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Thursday, September 07, 2000 5:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ORA-4031
>
> Hi, (.. i want to apologize for my BAD english)
> have you cheked the fragmentation of your shared pool ??
> Are there hundreds of concurrent users not using bind variables ??
>
> :)
>
> I think you've already checked that, so :
>
> I faced with this kind of error during a bulk insert on a datawarehouse
> (Oracle 8.1.5, HP-UX )
>
> My "little" INSERT used 300M in the shared pool.
> After doubling the shared pool, my 'little' INSERT used 600 M.
>
> It seemed to be a BUG ..... maybe platform specific
>
> I can ask to Oracle if this fits you requests
>
> Hope this helps
> Davide
>
>
>
>
>
>
>
>
> -----Original Message-----
> Sent: Wed, 06 Sep 2000 06:34:38 -0800
> To: ORACLE-L_at_fatcity.com
>
>
>
> We had a similar error but it was ORA-04030 that pointed to system memory
> being exhausted.
> I am assuming you are on a UNIX OS and that you are running perl there.
> On NT it is much trickier to pinpoint a problem. I don't know of any
> accurate memory diagnostic tools out there for NT
>
>
> I went looking at the ulimit for the user on AIX 4.3.3.0 platform
> The systems admin and I upped the ulimit from 256M for data soft limit to
> 512M for the hard limit which the soft limit can be raised to
> That took care of the immediate need.
>
> Then went to explain plan the code. There was the key. Besides trying to
> take an average on the start and stop date(s), and having 6 million rows
> for the major table being joined there were several small coding tricks
> they didn't take advantage of at the start.
> Once we sent the code through explain plan and saw how much nesting, full
> table scans, and such we then started to tune their code.
>
> My suggestion is to do both memory tuning of the system file on UNIX and
> checking of the code.
> For the system file (if you have one.. on Solaris and HP-UX there is one)
> Make sure shmmax and other shared memory segment parameters are tuned for
> the system.
> I heard that Toad is a good tool to use for the database (tuning and
> maintenance and such)
> I am aiming to give it a whirl in the next few weeks.
> Further aside...
> I tried getting SQL Station to work but it is a) intrusive with requiring
> you to create some sort of repository in the target database b) not user
> install friendly
>
> Good luck.
>
> Heidi
>
>
>
>
>
> Kader Ben
>
> <kaderb_at_yahoo To: Multiple recipients of
> list ORACLE-L
> .com> <ORACLE-L_at_fatcity.com>
>
> Sent by: cc:
>
> root_at_fatcity. Subject: ORA-4031
>
> com
>
>
>
>
>
> 09/05/00
>
> 05:32 PM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
> Hi friends,
> I have a perl script that load file content into a
> table, since this morning I am facing with the
> following error:
>
> FATAL ERROR: (DBD::Oracle::db do failed: ORA-04031:
> unable to allocate 52 bytes of shared memory ("shared
> pool","insert into grille values ('...","sql
> area","strdef : prsstr") (DBD ERROR: OCIStmtExecute)
> at infinit.pm line 123, <FILE> chunk 1.
> )
>
> I have increased the shared_pool-size and parameter in
> init.ora and I still running the same problem. I don't
> know what I can do else ;)
>
> This is fragment from my init.ora:
> shared_pool_size = 36000000
> shared_pool_reserved_size = 160000000
> shared_pool_reserved_min_alloc = 8000000
> sort_area_size = 2000000
>
> Thank you for your help in advance,
>
> Kader
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Author: Kader Ben
> INET: kaderb_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Author:
> INET: Heidi_Schmidt_at_gillette.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> __________________________________________________________________________
> _
> Visit http://www.visto.com/info, your free web-based communications
> center.
> Visto.com. Life on the Dot.
>
> --
> Author: Davide Bellesi
> INET: davide.bellesi_at_visto.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
------_=_NextPart_001_01C018B9.6FF79810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3Dus-ascii">
<TITLE>Max datafilesze in ORACLE8i</TITLE> </HEAD> <BODY>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Hi List,</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I am not sure =
whether this question is put or not in the list.</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Is there any limit =
oracle imposes on the size of a datafile.Somebody said it is =
2GB.</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Can any one of you =
tell me the max datafile size possible and also the oracle recommended =
size.It is for Oracle 8.1.5.</FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Thanx</FONT> <BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Uma Shankar</FONT> </P> <BR> <UL> <P><FONT SIZE=3D1 FACE=3D"MS Sans Serif">----------</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">From:</FONT></B> = <FONT SIZE=3D1 FACE=3D"MS Sans Serif">Davide =Bellesi[SMTP:davide.bellesi_at_visto.com]</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Reply To:</FONT></B> = <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">ORACLE-L_at_fatcity.com</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hi, (.. i want to apologize for my BAD =
english) </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> have you cheked the =
fragmentation of your shared pool ??</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Are there hundreds of concurrent =
users not using bind variables ??</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">:)</FONT> </P>
<P><FONT SIZE=3D2 FACE=3D"Arial">I think you've already checked that, =
so :</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">I faced with this kind of error during = a bulk insert on a datawarehouse (Oracle 8.1.5, HP-UX )</FONT> </P>
<P><FONT SIZE=3D2 FACE=3D"Arial">My "little" INSERT used 300M =
in the shared pool.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">After doubling the shared pool, my =
'little' INSERT used 600 M.</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">It seemed to be a BUG ..... maybe =
platform specific</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">I can ask to Oracle if this fits you =
requests</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hope this helps</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Davide</FONT> </P> <BR> <BR> <BR> <BR> <BR> <BR> <BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: Wed, 06 Sep =
2000 06:34:38 -0800</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To: =
ORACLE-L_at_fatcity.com</FONT>
</P> <BR> <BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">We had a similar error but it was =
ORA-04030 that pointed to system memory</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">being exhausted.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I am assuming you are on a UNIX OS =
and that you are running perl there.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">On NT it is much trickier to pinpoint =
a problem. I don't know of any</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">accurate memory diagnostic tools out =
there for NT</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">I went looking at the ulimit for the = user on AIX 4.3.3.0 platform</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">The systems admin and I upped = the ulimit from 256M for data soft limit to</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">512M for the hard limit which the = soft limit can be raised to</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">That took care of the immediate =need.</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Then went to explain plan the =
code. There was the key. Besides trying to</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">take an average on the start and stop =
date(s), and having 6 million rows</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">for the major table being joined =
there were several small coding tricks</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">they didn't take advantage of at the =
start.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Once we sent the code through explain =
plan and saw how much nesting, full</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">table scans, and such we then started =
to tune their code.</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">My suggestion is to do both memory =
tuning of the system file on UNIX and</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">checking of the code.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">For the system file (if you have =
one.. on Solaris and HP-UX there is one)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Make sure shmmax and other shared =
memory segment parameters are tuned for</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">the system.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I heard that Toad is a good tool to =
use for the database (tuning and</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">maintenance and such)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I am aiming to give it a whirl in the =
next few weeks.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Further aside...</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I tried getting SQL Station to work =
but it is a) intrusive with requiring</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">you to create some sort of repository =
in the target database b) not user</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">install friendly</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Good luck.</FONT> </P>
<P><FONT SIZE=3D2 FACE=3D"Arial"> Heidi</FONT> </P> <BR> <BR>
<P><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; Kader = Ben &nb= sp; &nb= sp; &nb= sp; &nb= sp; =</FONT>
<ORACLE-L_at_fatcity.com> &n= bsp; &n= bsp; </FONT><BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; Sent = by: &nb=sp; =
cc: &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
Subject: = ORA-4031 &nbs= p; &nbs= p; </FONT>
FACE=3D"Arial"> &nb= sp; = com &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
09/05/00 &nbs= p; &nbs= p; &nbs= p; &nbs= p; &nbs=p; </FONT>
PM &nbs= p; &nbs= p; &nbs= p; &nbs= p; &nbs=p; </FONT>
Please = = = = = </FONT>
to &nbs= p; &nbs= p; &nbs= p; &nbs= p; </FONT><BR><FONT SIZE=3D2 =
ORACLE-L &nbs= p; &nbs= p; &nbs= p; &nbs= p; &nbs=p; </FONT>
FACE=3D"Arial"> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT><BR><FONT SIZE=3D2 =
FACE=3D"Arial"> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> </P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hi friends,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> I have a perl script that load =
file content into a</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">table, since this morning I am facing =
with the</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">following error:</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">FATAL ERROR: (DBD::Oracle::db do =
failed: ORA-04031:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">unable to allocate 52 bytes of shared =
memory ("shared</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">pool","insert into grille =
values ('...","sql</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">area","strdef : =
prsstr") (DBD ERROR: OCIStmtExecute)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">at infinit.pm line 123, <FILE> =
chunk 1.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">)</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">I have increased the shared_pool-size =
and parameter in</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">init.ora and I still running the same =
problem. I don't</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">know what I can do else ;)</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">This is fragment from my =
init.ora:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">shared_pool_size =3D 36000000</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">shared_pool_reserved_size =3D =
160000000</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">shared_pool_reserved_min_alloc =3D =
8000000</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">sort_area_size =3D 2000000</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Thank you for your help in =
advance,</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Kader</FONT> </P> <BR> <BR>
<P><FONT SIZE=3D2 =
FACE=3D"Arial">__________________________________________________</FONT>=
<BR><FONT SIZE=3D2 FACE=3D"Arial">Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Yahoo! Mail - Free email you can =
access from anywhere!</FONT>
<BR><U><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial"><A =
HREF=3D"http://mail.yahoo.com/" =
TARGET=3D"_blank">http://mail.yahoo.com/</A></FONT></U>
<BR><FONT SIZE=3D2 FACE=3D"Arial">--</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Kader Ben</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: kaderb_at_yahoo.com</FONT> </P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=-----------</FONT>
</P> <BR> <BR> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT><BR><FONT SIZE=3D2 FACE=3D"Arial">Author: </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: = Heidi_Schmidt_at_gillette.com</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=-----------</FONT>
</P> <BR> <BR>
<P><FONT SIZE=3D2 =
FACE=3D"Arial">_________________________________________________________= __________________</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Davide Bellesi</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: =davide.bellesi_at_visto.com</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=Received on Thu Sep 07 2000 - 05:50:41 CDT
 |
 |