Shrink Temporary Tablespace and Move temp file to another location

==============

General

==============

=====================

Shrink Temporary Tablespace

=====================

SELECT file_name,

       tablespace_name, 

       ROUND(SUM(bytes)/1024/1024) TOTAL_MB 

  FROM DBA_TEMP_FILES 

 GROUP BY file_name,tablespace_name;

FILE_NAME                                     TABLESPACE_NAME    TOTAL_MB

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

D:\ORACLE_DB\DB1\DB_IGT\ORA_TEMPORARY_01.DBF  TEMPORARY              4400

ALTER TABLESPACE TEMPORARY SHRINK SPACE KEEP 200M;

Tablespace altered;

SELECT file_name,

       tablespace_name, 

       ROUND(SUM(bytes)/1024/1024) TOTAL_MB 

  FROM DBA_TEMP_FILES 

GROUP BY file_name,tablespace_name;

FILE_NAME                                     TABLESPACE_NAME    TOTAL_MB

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

D:\ORACLE_DB\DB1\DB_IGT\ORA_TEMPORARY_01.DBF  TEMPORARY               201

=====================

Move temp file to another location

=====================

Now: temporary table space has one file:

/oracle_db/db1/db_igt/ora_temporary_01.dbf

Need to move it to /oracle_db/db2/db_igt/datafile location

==============

Steps

==============

sqlplus / as sysdba

SET LINESIZE 120

COL NAME FOR A60

COL FILE_NAME FOR A60

SELECT name, status FROM V$TEMPFILE;

NAME                                       STATUS

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

/oracle_db/db1/db_igt/ora_temporary_01.dbf ONLINE

SELECT file_id, tablespace_name, file_name, status, ROUND(bytes/1024/1024) as used_md, ROUND(maxbytes/1024/1204) as total_mb

FROM DBA_TEMP_FILES;

FILE_ID TABLESPACE_NAME FILE_NAME                                  STATUS   USED_MD    TOTAL_MB

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

1       TEMPORARY       /oracle_db/db1/db_igt/ora_temporary_01.dbf ONLINE   6000       5103

!mkdir /oracle_db/db2/db_igt

!mkdir /oracle_db/db2/db_igt/datafile

ALTER TABLESPACE TEMPORARY ADD TEMPFILE '/oracle_db/db2/db_igt/datafile/ora_temporary_01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 8000M;

Tablespace altered.

SELECT name, status FROM V$TEMPFILE;

NAME                                                  STATUS

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

/oracle_db/db1/db_igt/ora_temporary_01.dbf            ONLINE

/oracle_db/db2/db_igt/datafile/ora_temporary_01.dbf   ONLINE

ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' OFFLINE;

Database altered.

SELECT name, status FROM V$TEMPFILE;

NAME                                                  STATUS

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

/oracle_db/db1/db_igt/ora_temporary_01.dbf            OFFLINE

/oracle_db/db2/db_igt/datafile/ora_temporary_01.dbf   ONLINE

ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' DROP INCLUDING DATAFILES;

ERROR at line 1:

ORA-25152: TEMPFILE cannot be dropped at this time

There are active sessions that are using the temp files:

SELECT B.tablespace, B.segfile#, A.sid, A.serial#,

 A.username, A.osuser, A.status

 FROM V$SESSION A,

      V$SORT_USAGE B

 WHERE A.saddr = B.session_addr;

Kill the active sessions:

ALTER SYSTEM DISCONNECT SESSION '590,39359' IMMEDIATE;

ALTER SYSTEM DISCONNECT SESSION '970,6840' IMMEDIATE;

ALTER SYSTEM DISCONNECT SESSION '22,42942' IMMEDIATE;

ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' DROP INCLUDING DATAFILES;

Database altered.

SELECT name, status FROM V$TEMPFILE;

NAME                                                  STATUS

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

/oracle_db/db2/db_igt/datafile/ora_temporary_01.dbf   ONLINE