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