Re: Increase size of data files and rollback segments

Hello Jeremiah

I did some research on metalink and it says:

1. Do not use datafiles more then 4GB on NT systems.
2. There is a bug on NT, W2K that if you resize datafile (direct command or autoextend) to 4GB boundary, i.e 4,8,12, then there are two conditions: 2.1) No archive log - database crashes but you can start again and resize the datafiles. 2.2) Archive log - restore and regenerate to a point in time prior before the resize. This bug was reported also on 9.2.

The work around is to resize to 4.1, 8.1 GB datafiles.

Bug number 1668488.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 11, 2002 12:54 PM

> 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/~jwilton
>
> > ----- 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).
>

-- 
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).