How to find Locked object in Oracle Database.

Hello dear, Using this example you can find out your locked object list from your database. You must to have DBA prevailed.
 

SQL> CREATE TABLE AAA(ID NUMBER);

Table created.

SQL> INSERT INTO  AAA VALUES(10);

1 row created.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OS_USER_NAME                   TERMINAL          OBJECT_ID
------------------------------ ---------------- ----------
OBJECT_NAME
--------------------------------------------------------------------------------
       195          7 RND
SPFTL\rajib.pradhan            N1020                215166
AAA

SQL> commit;

Commit complete.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

no rows selected

SQL>

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

Cause :- This error occur when you are try to drop Global Temporary Table which are locked.

Solution :- To solve this error you need to truncate table / kill this session / or exit form session and login again.


SQL> create global temporary table temp_data(id number, name varchar2(200)) on commit  PRESERVE ROWS;

Table created.

SQL> insert into temp_data values(10, 'Rajib');

1 row created.

SQL> select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


SQL>  commit;

Commit complete.

SQL>  select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


SQL>  drop table temp_data;
 drop table temp_data
            *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

       SID
----------
       195

SQL> TRUNCATE TABLE temp_data;

Table truncated.

SQL>  drop table temp_data;

Table dropped.

SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

no rows selected

SQL>

ORA-00972: identifier is too long

SQL> Select * from identifier_too_long_cause_extends_30;
Select * from identifier_too_long_cause_extends_30
              *
ERROR at line 1:
ORA-00972: identifier is too long


Cause : Oracle identifiers are not allowed to exceed 30 characters in length.

Solution : Please use Table name (object name) between 1 to 30 character long.