RE: Urgent: Temporary Tablespaces
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <bunjibry@xxxxxxxxx>
- Date: Mon, 17 Jan 2005 20:57:17 +0100
Bryan,
did you search MetaLink for bugs?
Did you try creating synonyms as a workaround?
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bryan Wells
Sent: Monday, January 17, 2005 20:42
Cc: Oracle-L
Subject: Re: Urgent: Temporary Tablespaces
After further research, we're determined that the 9.2.0.5 patch set
appears to drop support for ANSI SQL JOIN syntax (JOIN...ON...) when the
tables or views being joined reside in different schemas. Changing to the
old theta syntax (as shown below) fixes the issue:
select * a.table1, a.table2, a.table3
where a.table1 = a.table2
and a.table2 = a.table3;
Complex JOINS using the JOIN...TO syntax over tables and views in the same
schemas DO run successfully - but, as soon as we add a table/view in another
schema, we get the ORA03113:End-of-file on Communication Channel.
Unfortunately, we have many, many reports, views, etc. that use the
JOIN...ON syntax over multiple schemas. Is there another patch or
configuration parameter we can modify to fix the issue (aside from modifying
our reports/views/queries)?
On Mon, 17 Jan 2005 19:21:06 +0000, stephen booth
<stephenbooth.uk@xxxxxxxxx> wrote:
> On Mon, 17 Jan 2005 10:07:35 -0700, Bryan Wells <bunjibry@xxxxxxxxx>
wrote:
> > okay... another Newbie (dumb) question:
> >
> > how do I clear out temporary table space?
>
> You don't. Oracle handles it. The only time you might have to do
> anything is if you have users creating non-temporary segments in there
> (can happen if it wasn't created correctly and your users are
> particularly dangerous, i.e. developers). In that case you'll have to
> drop or move the objects to a different tablespace, change your dba
> passwords and then find something heavy to chastise the users with.
> :-)
>
> > Whats the difference beteween temp space and temporary tablespace?
>
> Depends what you mean by temp space (and your OS).
>
> The temporary tablespace is a tablepace where Oracle creates temporary
> segments. Everything is handled 'under the hood', you might need to
> think about it for tuning purposes but other than that, barring bugs
> and the amove mentioned users, probably not.
>
> If by tempspace you mean operating system temp it depends somewhat on
> your operating system. In general it's a dumping ground for any
> temporary files tha the OS uses or needs for the duration of a
> process. For example under most versions of UNIX the vi text editor
> will keep undo and recovery information (not to be confused with
> Oracle UNDO (i.e. rollback) and recovery (i.e. redo) infomation) about
> the file that you're working on in the /tmp filesystem, under Windows
> most verson of MSIE will download a file to c:\temp or c:\windows\temp
> and then copy it to the location you designate (which is why you often
> get a disk space error when downloading large files even if the drive
> you're downloading to has more than enough space, there's not enough
> space on the drive your temp directory is on). Under some OSes
> (Solaris being an example) the tempspace is also used for swapping and
> paging when memory gets full.
>
> Stephen
>
> --
> It's better to ask a silly question than to make a silly assumption.
>
--
Bryan S Wells - DBA Newbie
bunjibry@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
