Wrong Results Caused By The Query Joined V$TEMP_EXTENT_POOL and DBA_TEMP_FILES
(Doc ID 2598755.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation 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
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>
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 |