Why Oracle DBA_TEMP_FILES view Bytes Column is NULL: How to Drop, Shrink Offline / Online Temp File

advertisements

_____________________________________________________________________________________________________________________

Some Facts on Oracle Tempfiles:

If the database tempfile is in offline status then the bytes column of the dba_temp_files will be null. If the file system of the temporary file is having some problem then the corresponding tempfile status will move to offline

status. You can make the temporary file to offline status manually also.

You cannot make temporary tablespace offline instead you can make tempfile offline. But in case the tempfile is in offline status that particular file size will be null in the dictionary views.

Make the tempfile to offline.

SQL> alter database tempfile '+DATA/DEVQA1/tempfile/temp.1141.798273527' offline;

Database altered.

In below query the bytes column is null

FILE_NAME                                   TABLESPACE_NAME    BYTES/1024/1024/1024 STATUS

------------------------------------------- ------------------ -------------------- -------

+DATA/DEVQA1/tempfile/temp.1141.787253875   TEMP                                    OFFLINE

+DATA/DEVQA1/tempfile/temp.563.758899679    TEMP               31.9990234           ONLINE

SQL> alter database tempfile '+DATA/DEVQA1/tempfile/temp.1141.787253875' online;

Database altered.

SQL>  select FILE_NAME,TABLESPACE_NAME, BYTES/1024/1024/1024 ,STATUS from dba_temp_files;

FILE_NAME                                   TABLESPACE_NAME    BYTES/1024/1024/1024 STATUS

------------------------------------------- ------------------ -------------------- -------

+DATA/DEVQA1/tempfile/temp.1141.787253875   TEMP               31.9990234           ONLINE

+DATA/DEVQA1/tempfile/temp.563.758899679    TEMP               31.9990234           ONLINE

In case you do not want a huge tempfile to the database, you can either reduce the tempfile size to smaller size or drop the tempfile.

A.  SQL> alter database tempfile '+DATA/DEVQA1/tempfile/temp.1141.787253875' resize 1g;

Database altered.

B.  SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/DEVQA1/tempfile/temp.563.758899679';

Tablespace altered.

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES/1024/1024/1024 ,STATUS from dba_temp_files;

FILE_NAME                                   TABLESPACE_NAME    BYTES/1024/1024/1024 STATUS

------------------------------------------- ------------------ -------------------- ------+DATA/DEVQA1/tempfile/temp.1141.787253875   TEMP               1                    ONLINE

+DATA/DEVQA1/tempfile/temp.563.758899679    TEMP               31.9990234           ONLINE

There are two options to drop a temporary file from the database.

A.  SQL> alter tablespace temp drop tempfile '+DATA/DEVQA1/tempfile/temp.1141.798273405';

Tablespace altered.

B.  SQL> ALTER DATABASE TEMPFILE '+DATA/DEVQA1/tempfile/temp.1141.787253875' drop;

Database altered.

INCLUDING DATAFILES Option will remove the physical temporary files also.

ALTER DATABASE TEMPFILE '+DATA/DEVQA1/tempfile/temp.1141.787253875' drop including datafiles;

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES/1024/1024/1024 ,STATUS from dba_temp_files;

FILE_NAME                                   TABLESPACE_NAME    BYTES/1024/1024/1024 STATUS

------------------------------------------- ------------------ -------------------- -------

+DATA/DEVQA1/tempfile/temp.563.758899679    TEMP               31.9990234           ONLINE

_____________________________________________________________________________________________________________________

Website Stats