Re: Trivia question - file date.

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA702FAE3F5_at_lnewton.leeds.lfs.co.uk>...
> Hi Richard,
>
> you had to get one in about the cricket didn't you :o)
> Niall will have something to say on the matter - and he'll probably be
> very scathing towards a certain Mr Bowie as well.
>
> Yep the checkpointing of temp files isn't much good - I eventually
> figured that one out. I must be getting old.

The real issue here is explained in metalink NOTE: 160426.1. When sorting, Oracle only actually goes to the sort file if it needs to. So in the situation of 2 large locally managed temp files, the second file may never be used. So the checkpointing is irrelevant, and while it is true you may not care about recovering this second file, Oracle may have something to say about it after you rm it without dropping it, and then happen to have a very large sort like the one that originally prompted you to add it (mayhaps, a year-end thingee...).

So now I'm wondering if Oracle checks for this situation on startup, and I'm guessing it doesn't, since all the transportable ts and such rely on plugins... ORA-25153 doesn't quite seem to cover it... sounds like an easy bomb to overlook.

I noticed also in that note they seem to be very clear about the difference between an tempfile and a datafile, until the example about dropping a temporary datafile in 9i... :-)

SQL> alter database tempfile '/oradata/V901/temp_temp01.dbf' drop including datafiles;
Database altered.

>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: Richard Foote [mailto:richard.foote_at_bigpond.com]
> Posted At: Wednesday, November 27, 2002 11:48 AM
> Posted To: server
> Conversation: Trivia question - file date.
> Subject: Re: Trivia question - file date.
>
>
> Hi Norman, Joel and all cricket loving DBAs ;)
>
> Remember that the purpose of a checkpoint is to have a point from which
> to
> commence instance recoveries. However, if you had an instance failure,
> what
> precisely within a temp file would be worth recovering. Not a lot.
> Therefore
> if Oracle is not going to concern itself with recovering a temp file,
> then
> there's really no point in checkpointing it either.

jg

--
@home is bogus.
I hate it when crickets make all that noise on a hot night.