| 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: Increase size of data files and rollback segments
On Wed, 11 Dec 2002, Yechiel Adar wrote:
> Do not allow your datafile to autoextend across 4GB boundary.
> There is a bug that cause this datafile to be unusable.
I think the 4Gb limit is confined to a handfull of older operating system versions or older Oracle versions. For about the last five years I have been accustomed to creating 16Gb datafiles with no problem.
Imagine trying to build a 5 terabyte data warehouse out of 1900Mb datafiles! It would require 2760 datafiles!
Can anyone confirm that this is no longer a problem after certain versions of O/S and Oracle?
Note another mean bug (8.1.6.2 / HP-UX 64-bit) where Oracle lets you specify no size for a datafile, then adds it to the controlfile /data dictionary in a way that makes it look like it has a ton (like a terabyte) of free space. The datafile can't be resized or offline dropped, and the tablespace must be dropped and recreated (unless you get the patch). Let one segment extend into there and watch the ORA-600s.
-- Jeremiah Wilton http://www.speakeasy.net/~jwiltonReceived on Wed Dec 11 2002 - 04:54:39 CST
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, December 11, 2002 2:46 AM
>
>
> > As a start, look up the following commands in the SQL Reference guide:
> >
> > alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' autoextend on
> maxsize 20480M;
> > Read up on the implications of autoextend and whether you want it
> >
> > alter database datafile 'C:\ORADATA\fred\DAT_LARGE_01.dbf' resize 10240M;
> >
> > For rollback datafile - same as any other datafile, eg:
> > alter database datafile 'C:\ORADATA\fred\ROLLBACK1.DBF' autoextend on
> maxsize 10240M;
> >
> > You may then need to add another rollback segment or increase the max
> extents of an existing one. eg to add another rollback segment:
> > CREATE PUBLIC ROLLBACK SEGMENT r09_big
> > TABLESPACE rollback
> > STORAGE
> > ( minextents 20
> > INITIAL 10M
> > NEXT 10M
> > MAXEXTENTS UNLIMITED)
> >
> > (you would then need to bring this online - eg "alter rollback segment
> r09_big online;")
> >
> >
> > For tempfile (ie LMT temporary tablespaces):
> > alter database tempfile 'C:\ORADATA\fred\TEMP1.dbf' autoextend on maxsize
> 10240M;
> >
> > Remember - don't use the exact sizes I've shown - alter to suit your case
> (these were part of a huge load into a new test system)
> >
> >
> > Hope this helps (and willing to learn if some of the above could be
> improved).
> >
> > Regards,
> > Bruce
> >
> > -----Original Message-----
> > Sent: Wednesday, 11 December 2002 8:15 AM
> >
> > > From: Nguyen, David M [mailto:david.m.nguyen_at_xo.com]
> > >
> > > Alright guys, just because you know Jeremiah better than me
> > > so you can take
> > > his joke.
> > I have no idea who Jeremiah is from Adam, and I got the joke. Hell, I
> nearly flamed you myself, and I'm just a lowly developer.
> > > My apology to him as I did not recognize it was a
> > > joke, however
> > > when I am asking for help from the group, I am in a situation
> > > to resolve
> > > problem ASAP and hope to find a answer not a joke.
> > The problem is that if enough people like you refuse to RTFM, then no one
> will want to answer questions because it'll be duller'n your resume.
> > > If you were in my situation, you would understand.
> > I've been in your situation, and been damned ashamed of myself for not
> having researched the question first. It ain't all that hard...
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Reardon, Bruce (CALBBAY)
> > INET: Bruce.Reardon_at_comalco.riotinto.com.au
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Yechiel Adar
> INET: adar76_at_inter.net.il
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: jwilton_at_speakeasy.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
 |
 |