Steps for changing the temporary tablespace of oracle to full disk space

OracleTo solve the problem, follow these steps:

At the beginning, I planned to shrink the data file in the temporary tablespace.

Run:

SQL> alter database tempfile

2'/oracle/oms/oradata/temp/temp01.dbf' resize 10240 M;

The database reports an error. The size of the reset space cannot meet the requirements.

It seems that a new temporary tablespace needs to be created to replace the current tablespace.

1. First, check the default tablespace of the current database:

SQL> select * from database_properties

Where property_name = 'default _ TEMP_TABLESPACE ';

Check that the current temporary tablespace is TEMP.

2. view the current temporary tablespace size:

SQL> select file_name, tablespace_name, bytes/1024/1024 "MB", autoextensible from dba_temp_files;

3. Create a new temporary tablespace: Borrow space from other disk spaces first)

SQL> create temporary tablespace temp02

2 tempfile '/oracle/oms/oradata/undo/temp02.dbf'

3 size 512 M;

4. Change the new temporary tablespace to the default temporary tablespace of the database.

SQL> alter database default temporary tablespace temp02;

5. Check the default temporary tablespace of the current database.

SQL> select * from database_properties

Where property_name = 'default _ TEMP_TABLESPACE ';

6. before deleting the temp temporary tablespace, kill the SQL statement that runs in the temp temporary tablespace. Such SQL statements are mostly sorted statements.

SQL> Select se. username, se. sid, se. serial #, su. extents, su. blocks * to_number (rtrim (p. value) as Space,

Tablespace, segtype, SQL _text

From v $ sort_usage su, v $ parameter p, v $ session se, v $ SQL s

Where p. name = 'db _ block_size 'and su. session_addr = se. saddr and s. hashvalue = su. sqlhash

And s. address = su. sqladdr

Order by se. username, se. sid;

After the query, kill these SQL statements:

SQL> alter system kill session '123'; (if the SID of a running SQL statement is 524,778, serial # Is 524)

After confirming that no SQL statement is run in the temp temporary tablespace, you can delete the temp temporary tablespace data file.

7. Delete temp temporary tablespace

SQL> drop tablespace temp including contents and datafiles;

In this way, the temporary tablespace data files can be deleted quickly.

8. Currently, temporary tablespace of temp02 occupies the disk space of others. You need to re-set the temporary tablespace to the original location and recreate the temporary tablespace of temp.

SQL> create temporary tablespace temp

2 tempfile '/oracle/oms/oradata/temp/temp01.dbf'

3 size 512 M autoextend on maxsize 15G;

Create a new M auto-extended temporary tablespace with a maximum of 15 GB.

Check whether the new temp temporary tablespace is correct:

SQL> select file_name, tablespace_name, bytes/1024/1024, maxbytes/1024/1024, autoextensible from dba_temp_files;

9. Change the new temp temporary tablespace to the default temporary tablespace of the database.

SQL> alter database default temporary tablespace temp;

10. Check the default temporary tablespace of the current database.

SQL> select * from database_properties

Where property_name = 'default _ TEMP_TABLESPACE ';

Check that temp is the default tablespace of the current database.

11. Currently, the original temp temporary tablespace is changed to 512 MB, and the remaining disk space is empty. The temporary tablespace of temp02 is useless, and the temporary tablespace of temp02 is deleted.

SQL> drop tablespace temp02 including contents and datafiles;

Using the method described above, the problem that the temporary tablespace in oracle is full of disk space can be easily solved, and it also avoids the trouble of writing the temporary tablespace in oracle to full disk space, I hope that you can learn from the above.