Oracle

Dropping tempfile while inuse – 10.2.0.4

Posted Anand

I was asked by one of my friend, what would happen if we drop the tempfile (from multiple tempfiles in temp tablespace)while it is in-use.Inuse, in the sense,that some sessions are performing huge sorting and the temp tablespace is being used.With my little knowledge of Oracle, i replied “The tempfile must not get dropped,it must say something like “file not empty” or “currently file is in use”.
So, then it was time to test

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

User anand’s session – referred as session1

D:\>sqlplus anand
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 20 10:05:01 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >
10:05:03 ANAND at matrix >exec dbms_random.seed(0)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
10:05:08 ANAND at matrix >create table test_main
10:05:17   2   as
10:05:17   3   select
10:05:17   4   sys.dbms_random.string('U',2000 ) as object_name
10:05:17   5   from
10:05:17   6   dual
10:05:17   7   connect by
10:05:17   8   rownum <= 90000;
Table created.
Elapsed: 00:06:32.00

From SYS session – referred as session2

10:59:37 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                    60 AVAILABLE       YES
+DATA1/matrix/tempfile/temp.268.732884287          TEMP                    20 AVAILABLE       NO
Elapsed: 00:00:00.21

The default temporary tablespace allocated to user ANAND is also TEMP.From session1 started gathering the statistic for the table using method_opt => ‘FOR ALL COLUMNS SIZE 254’

10:59:42 ANAND at matrix >exec dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254');

Checking the session2, shows

11:01:14 SYS at matrix >@sess_temp
USERNAME            SID    EXTENTS      SPACE TABLESPACE   SEGTYPE
------------ ---------- ---------- ---------- ------------ ---------
ANAND               145         23   24117248 TEMP         SORT
ANAND               145         15   15728640 TEMP         DATA
Elapsed: 00:00:00.20
11:01:17 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                    60 AVAILABLE       YES
+DATA1/matrix/tempfile/temp.268.732884287          TEMP                    20 AVAILABLE       NO
Elapsed: 00:00:00.21
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        TEMP
Elapsed: 00:00:00.04
11:01:21 SYS at matrix >@sess_temp
USERNAME            SID    EXTENTS      SPACE TABLESPACE   SEGTYPE
------------ ---------- ---------- ---------- ------------ ---------
ANAND               145        122  127926272 TEMP         SORT
ANAND               145         15   15728640 TEMP         DATA
Elapsed: 00:00:00.34
11:01:34 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                   140 AVAILABLE       YES
+DATA1/matrix/tempfile/temp.268.732884287          TEMP                    20 AVAILABLE       NO
Elapsed: 00:00:00.28
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        TEMP

The above confirms that the temp tablespace is being used by session1 and the tempfile size has also increased.
Lets try to drop one of the tempfile

11:01:46 SYS at matrix >ALTER DATABASE TEMPFILE 3  DROP including datafiles;
ALTER DATABASE TEMPFILE 3  DROP including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

Does the above confirm that my tempfile can’t be dropped, does it confirm my user’s session wouldn’t get any error??
What has the session1 to say

10:59:42 ANAND at matrix >exec dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254');
BEGIN dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254'); END;
*
ERROR at line 1:
ORA-01135: file 203 accessed for DML/query is offline
ORA-01110: data file 203: '+DATA1/matrix/tempfile/temp.268.732884287'
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1
Elapsed: 00:01:58.00
11:02:24 ANAND at matrix >

Session2

11:03:08 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                   220 AVAILABLE       YES
+DATA1/matrix/tempfile/temp.268.732884287          TEMP                       AVAILABLE
Elapsed: 00:00:00.26

Though i got “TEMPFILE cannot be dropped” , the size_in_mb and autoextensible column shows null, but the status column shows “AVAILABLE”
The tempfile still exists on the location ‘+DATA1/matrix/tempfile’.To permanently drop and delete the file from the location, once again fire the same command

11:03:08 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                   220 AVAILABLE       YES
+DATA1/matrix/tempfile/temp.268.732884287          TEMP                       AVAILABLE
Elapsed: 00:00:00.06
11:03:13 SYS at matrix >ALTER DATABASE TEMPFILE 3  DROP including datafiles;
Database altered.
Elapsed: 00:00:00.11
11:03:20 SYS at matrix >@temp_info
FILE_NAME                                          TABLESPACE_NAME SIZE_IN_MB STATUS          AUT
-------------------------------------------------- --------------- ---------- --------------- ---
+DATA1/matrix/tempfile/temp.264.732884425          TEMP                   220 AVAILABLE       YES
Elapsed: 00:00:00.03

Just for fun, lets try to drop the tempfile using “alter tablespace temp drop tempfile ;” and see what happens.
Session1 – again started the stats gathering

11:05:42 ANAND at matrix >exec dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254');

From session2 – Added one tempfile to the temp tablespace.

11:07:02 SYS at matrix >@sess_temp
USERNAME            SID    EXTENTS      SPACE TABLESPACE   SEGTYPE
------------ ---------- ---------- ---------- ------------ ---------
ANAND               145        194  203423744 TEMP         SORT
ANAND               145         15   15728640 TEMP         DATA
Elapsed: 00:00:00.21

Now, lets try to drop the tempfile
Session2

11:07:47 SYS at matrix >alter tablespace temp drop tempfile 1;
Tablespace altered.
Elapsed: 00:00:00.32

What has the session1 to say this time, whats the ORA error, this time
Session1

11:05:42 ANAND at matrix >exec dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254');
BEGIN dbms_stats.gather_table_stats(null,'TEST_MAIN',method_opt => 'FOR ALL COLUMNS SIZE 254'); END;
*
ERROR at line 1:
ORA-01115: IO error reading block from file %s (block # %s)
Elapsed: 00:02:14.23
11:07:58 ANAND at matrix >

Ohhh, this time the error is “ORA-01115: IO error reading block from file %s (block # %s)”
Interestingly if you try to execute some query after the above error (ORA-01115), you will get “ORA-03114: not connected to ORACLE“, and you need to re-connect to perform any SQL,which was not the case in previous drop command.

11:08:00 ANAND at matrix >exec dbms_stats.gather_table_stats(null, 'TEST_TEMP');
ERROR:
ORA-03114: not connected to ORACLE

Reference http://oracle-randolf.blogspot.com/2009/06/temporary-tablespace-groups.html

2 thoughts on “Dropping tempfile while inuse – 10.2.0.4”

  1. Hi Anand,
    Thanks for sharing this, however I have a doubt, in the first scenario where you use alter database drop tempfile, after this command is fired, will the user session rollback??or will it start making use of the other tempfile in the temp tablespace??
    Regards,
    Ajinkya

    Reply

    1. Suppose user is performing select query with “order by” leading to sorting on disk,means it starts using its allocated temp tablespace.Meanwhile, someone drop the tempfile 2 from the temp tablespace.So the user running the select query will get ORA-01110 or ORA-01115 and has to start the sql all over again and that time the sql will use the left over tempfile for sorting.
      Hope this clears your doubt. 🙂
      Regards,
      Anand

      Reply

Leave a Reply