My Oracle Support Banner

Wrong Results Caused By The Query Joined V$TEMP_EXTENT_POOL and DBA_TEMP_FILES (Doc ID 2598755.1)

Last updated on OCTOBER 29, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

No rows selected after joined V$TEMP_EXTENT_POOL and DBA_TEMP_FILES that should select at least 1 row.

SQL>  SELECT A.*, B.*
FROM DBA_TEMP_FILES A
INNER JOIN V$TEMP_EXTENT_POOL B
ON A.FILE_ID = B.FILE_ID
AND A.TABLESPACE_NAME = B.TABLESPACE_NAME;
 2    3    4    5
no rows selected



Correct results can return after set set "_simple_view_merging" = false.

SQL> alter session set "_simple_view_merging" =false;

Session altered.

SQL>  SELECT A.*, B.*
FROM DBA_TEMP_FILES A
INNER JOIN V$TEMP_EXTENT_POOL B
ON A.FILE_ID = B.FILE_ID
AND A.TABLESPACE_NAME = B.TABLESPACE_NAME;
 2    3    4    5
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS SHARED           INST_ID
---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- ----------- ------------- ----------
TABLESPACE_NAME                   FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO     CON_ID
------------------------------ ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------ ----------
+DATA/ORCL/TEMPFILE/temp.271.1010963443
        1 TEMP                             48234496       5888 ONLINE             1 YES 3.4360E+10    4194302           80   47185920        5760 SHARED
TEMP                                    1             14            5          1792         640     14680064    5242880            1          0


SQL>



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.