Re: Reading a file# that doesn't exist

Hi,

an excellent point, Tim!

Even when using global temporary tables, there is actually no need to share the block's
data and no reason to place those blocks into the buffer cache.

However (tested on 9.2, sorry for not having 10g/11g handy):

CREATE GLOBAL TEMPORARY TABLE xGTT

   ON COMMIT PRESERVE ROWS
AS

   SELECT * FROM DBA_OBJECTS; SELECT COUNT(*),TS#,CLASS#
FROM V$BH
GROUP BY TS#,CLASS#
ORDER BY TS#; -- in my case ts#=2 is temporary tablespace with the tempfiles ...

81    2     1
1      2     2
1      2     4

IMHO, this is an illustration of (rational) laziness of server development. Otherwise they would need
to create yet another branch of kernel code to cope with blocks in the PGA and to distinguish between the
usual and temp blocks... New bugs, maintenance, etc

• Andrey

Tim Gorman wrote:
> Think about it -- no need to share with other sessions, so why
> involve SGA resources at all? Tempfiles are just an extension to the
> PGA, in essence...

--
http://www.freelists.org/webpage/oracle-l