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”
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
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