SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M; alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value

Удаление / Воссоздание временного табличного пространство

SQL> DROP TABLESPACE temp; Tablespace dropped. SQL> CREATE TEMPORARY TABLESPACE TEMP 2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE 3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Tablespace created.

SQL> DROP TABLESPACE temp; drop tablespace temp * ERROR at line 1: ORA-12906: cannot drop default temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE temp2 2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE 3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Tablespace created. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; Database altered. SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. SQL> CREATE TEMPORARY TABLESPACE temp 2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE 3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Tablespace created. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; Database altered. SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.

Drop Tempfile Command Method - (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with the above method,

this should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop. For this example,

my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp2_01.dbf:

SQL> SELECT tablespace_name, file_name, bytes 2 FROM dba_temp_files WHERE tablespace_name = 'TEMP'; TABLESPACE_NAME FILE_NAME BYTES ----------------- -------------------------------- -------------- TEMP /u02/oradata/TESTDB/temp01.dbf 13,107,200,000

The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment. A bad query, however,

increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space. In the example below,

I simply drop and recreate the tempfile:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES; Database altered. SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m 2 AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED; Tablespace altered.

On some platforms (i.e. Windows 2000), it is possible for the tempfile to be deleted fromDBA_TEMP_FILES but not from the file system.
If this occurs, simply delete the file using regular O/S commands.

If users are currently accessing the tempfile you are attempting to drop, you may receive the following error:
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES; ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time
As for the poor users who were using the tempfile, their transaction will end and will be greeted with the following error message:
SQL> @testTemp.sql join dba_extents c on (b.segment_name = c.segment_name) * ERROR at line 4: ORA-00372: file 601 cannot be modified at this time ORA-01110: data file 601: '/u02/oradata/TESTDB/temp01.dbf' ORA-00372: file 601 cannot be modified at this time ORA-01110: data file 601: '/u02/oradata/TESTDB/temp01.dbf'
If this happens, you should attempt to drop the tempfile again so the operation is successful:
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES; Database altered.