Posted by: baskardba | June 13, 2010

What I Learned Today

ORA-14450: attempt to access a transactional temp table already in use

Hi, Its a GTT table with oncommit delete rows..

SQL> ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
ADD
(
TOP_COLOR             VARCHAR2(50),
BOTTOM_COLOR          VARCHAR2(50)
);  2    3    4    5    6
ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
SQL>
SQL> select owner,object_id from dba_objects where owner=’DEVELOPER’ and object_name=’YYJSW_VASIND_TOTAL_DESP’;

OWNER                           OBJECT_ID
—————————— ———-
DEVELOPER                          770078

SQL> select * from v$locked_object where object_id = 770078;

no rows selected

SQL> select * from v$Lock where id1 = 770078;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE
—————- —————- ———- — ———- ———- ———-
   REQUEST      CTIME      BLOCK
———- ———- ———-
000000021F9BF7A8 000000021F9BF7C8        518 TO     770078          1          3
         0     106617          0
SQL> @sid
Enter value for sid: 518

old   3: where sid = ‘&sid’
new   3: where sid = ‘518’

       SID    SERIAL# OSUSER
———- ———- ——————————
USERNAME                       STATUS   PROCESS      PADDR
—————————— ——– ———— —————-
SPID         MODULE
———— ————————————————
       518       8872 Abhishek?Pandey
APPS                           INACTIVE 3744:2300    000000021F61FE60
6374         T.O.A.D.
SQL> SQL>
SQL>
SQL> @kill
Enter value for sid: 518
Enter value for sno: 8872
old   1: alter system kill session ‘&sid,&sno’
new   1: alter system kill session ‘518,8872’

System altered.

SQL> ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
ADD
(
TOP_COLOR             VARCHAR2(50),
BOTTOM_COLOR          VARCHAR2(50)
);  2    3    4    5    6

Table altered.

Thanks,

baskar.l

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: