Deleting temporary tablespace ORA-25152 Error

Deleting temporary tablespace ORA-25152 Error

Deleting temporary Oracle tablespaces or shrinking temporary tablespaces often results in table space occupation.
 
Next we will handle this situation,
 
First, find the locked sid:
 
SELECT a. INST_ID, B. TABLESPACE
, B. segfile #
, B. segblk #
, ROUND (B. blocks * p. VALUE)/1024/1024), 2) size_mb
, A. SID
, A. serial #
, A. username
, A. oSUSEr
, A. program
, A. status
FROM gv $ session
, Gv $ sort_usage B
, Gv $ process c
, Gv $ parameter p
WHERE p. NAME = 'db _ block_size'
AND a. saddr = B. session_addr
AND a. paddr = c. addr
Order by B. TABLESPACE
, B. segfile #
, B. segblk #
, B. blocks;

You can find the sid of the temporary tablespace and serial #

Then, it is the same as ALTER system kill session 'sid, serial # 'to delete.

You can delete the temporary tablespace or contract the temporary tablespace.

The following describes a more convenient method.

Select 'alter system kill session ''' | a. sid | ',' | a. serial # | ''' immediate ;'
From
V $ session,
V $ sort_usage B,
V $ process c,
V $ parameter d
Where
D. name = 'db _ block_size'
And
A. saddr = B. session_addr
And
A. paddr = c. addr
And
B. tablespace = 'temp '-- Name of the temporary tablespace to be shrunk or deleted
Order
B. tablespace,
B. segfile #,
B. segblk #,
B. blocks;

You can end the use of temporary tablespace in batches.

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian