Querying Temporary Files in Oracle Database: Step-by-Step

  

#Query to check data files in Oracle database. This will work in mount or open mode. SQL> set lines 300 pages 3000 SQL> col name for a80 SQL> select file#,name,status,ts#,bytes/1024/1024/1024 SIZE_GB from v$tempfile order by 1; FILE# NAME                                                         STATUS  TS#    SIZE_GB ----- ------------------------------------------------------------ ------- --- ----------     1 D:\RUPESH\SETUPS\TEST\TEST\DATAFILE\O1_MF_TEMP_LKM5RWJB_.TMP ONLINE    3 .095703125 #Query to check data files in Oracle database. This will work only if database is in open mode. SQL> col file_name for a80 SQL> col  TABLESPACE_NAME for a14 SQL> select file_id,file_name,status,tablespace_name,bytes/1024/1024/1024 SIZE_GB,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAX_SIZE_GB from dba_temp_files order by 1; FILE_ID FILE_NAME                                                    STATUS  TABLESPACE_NAM    SIZE_GB AUT MAX_SIZE_GB ------- ------------------------------------------------------------ ------- -------------- ---------- --- -----------       1 D:\RUPESH\SETUPS\TEST\TEST\DATAFILE\O1_MF_TEMP_LKM5RWJB_.TMP ONLINE  TEMP           .095703125 YES  31.9999847