Ora-00031 error for Oracle

1. A ora-14452 error occurred while deleting the user.

SQL> drop user ggtransfer2 cascade;

Drop user ggtransfer2 cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-14452: attempt to create, alter or drop an index on temporary table already

In use

 

2. Find the id of the user's object

Select object_id from user_objects where object_name = upper ('ggs _ stick ');

 

3. Locate sid by id,

Select * from v $ lock where id1 = 123623

 

548

 

4. Locate sid and serial Based on sid #

Select * from v $ session where sid = 548

548, 1

5. view the SQL statement of this sid

SELECT SQL _text

FROM v $ sqltext

WHERE a. hash_value = (SELECT SQL _hash_value

FROM v $ session B

WHERE B. SID = '& sid ')

Order by piece ASC

1. If the SQL statement does not affect system operation, kill

SQL> alter system kill session '2017, 1 ';

Alter system kill session '2017, 1'

*

ERROR at line 1:

ORA-00031: session marked for kill

6. The session of this SQL statement cannot be killed normally. Search for the process in the OS system.

Select spid, oSUSEr, s. program

From v $ session s, v $ process p

Where s. paddr = p. addr and s. sid = 548

1 29321 oraoms Oracle @ CQXXDBS01 (Q002)

2. kill the process in the OS system

Kill-9 29321

ORA-00031: session marked for kill

Cause: The session specified in an alter system kill session command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. this means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the alter system kill session command on this session may cause the session to be killed sooner