Home » SQL & PL/SQL » SQL & PL/SQL » ora-14452 (10g, 9i)
ora-14452 [message #333874] Mon, 14 July 2008 13:14 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
ora-14452 - attempt to create, alter or drop an index on temporary table already in use


just want to ask for some ways if there are any, in case i encounter this error in development, on which views can i look in for me to know which session or user is using the table? thanks.

regards,
Rhani

[Updated on: Mon, 14 July 2008 14:17] by Moderator

Report message to a moderator

Re: ora-14452 [message #333881 is a reply to message #333874] Mon, 14 July 2008 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
We have met the enemy & they is us.

14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause:  An attempt was made to create, alter or drop an index on temporary
//          table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
//          to truncate table and all the transactions using transaction 
//          specific temporary table have to end their transactions.


It is POOR practice to be creating & dropping tables on the fly.
Tables should be created ONCE & used as long as application is supported.

[Updated on: Mon, 14 July 2008 13:58] by Moderator

Report message to a moderator

Re: ora-14452 [message #333885 is a reply to message #333881] Mon, 14 July 2008 14:07 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
yes, i already know the "fix" from that ora-error explanation. this is just a study if there are views i can traced that session or user? or does it require dba priviledges?

regards,
rhani
Re: ora-14452 [message #333897 is a reply to message #333885] Mon, 14 July 2008 15:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SELECT v.oracle_username
FROM v$locked_object v, all_objects a
WHERE v.object_id = a.object_id
AND a.object_name = 'YOUR_TEMP_TABLE_NAME';

Re: ora-14452 [message #333898 is a reply to message #333897] Mon, 14 July 2008 15:04 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
thank you very very much =)

not sure, but i tried creating a temp table on one session, then did an insert into that from another session, then tried the query on the first session, can't seem to see the "locking" of the table listed in it. ill search more, thanks again.

regards,
rhani

[Updated on: Mon, 14 July 2008 15:21]

Report message to a moderator

Re: ora-14452 [message #334008 is a reply to message #333898] Tue, 15 July 2008 03:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this instead:
SELECT s.username
FROM   v$lock      l
      ,dba_objects o
      ,v$session   s
WHERE l.id1 = o.object_id
AND   s.sid = l.sid
AND   o.object_name = '<Your Table Name>';
Re: ora-14452 [message #334116 is a reply to message #334008] Tue, 15 July 2008 08:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It may be due to insufficient privileges. Here is what I get:

-- session 1 as user scott who has dba privileges:
SCOTT@orcl_11g> CREATE GLOBAL TEMPORARY TABLE test_tab AS
  2  SELECT * FROM dept WHERE 1 = 2
  3  /

Table created.

SCOTT@orcl_11g> CREATE USER test IDENTIFIED BY test
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE TO test
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT ALL ON test_tab TO test
  2  /

Grant succeeded.

SCOTT@orcl_11g>


-- session 2 as user test:
TEST@orcl_11g> INSERT INTO scott.test_tab (deptno, dname, loc)
  2  VALUES (1, 'testname', 'testloc')
  3  /

1 row created.

TEST@orcl_11g> 


-- back to user scott:
SCOTT@orcl_11g> DROP TABLE test_tab
  2  /
DROP TABLE test_tab
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SCOTT@orcl_11g> SELECT v.oracle_username
  2  FROM v$locked_object v, all_objects a
  3  WHERE v.object_id = a.object_id
  4  AND a.object_name = 'TEST_TAB'
  5  /

ORACLE_USERNAME
------------------------------
TEST

SCOTT@orcl_11g> SELECT s.username
  2  FROM   v$lock	l
  3  	   ,dba_objects o
  4  	   ,v$session	s
  5  WHERE l.id1 = o.object_id
  6  AND   s.sid = l.sid
  7  AND   o.object_name = 'TEST_TAB'
  8  /

USERNAME
------------------------------
TEST
TEST

SCOTT@orcl_11g>


-- after user test exits the session:
SCOTT@orcl_11g> DROP USER TEST CASCADE;

User dropped.

SCOTT@orcl_11g> DROP TABLE test_tab;

Table dropped.

SCOTT@orcl_11g> 


You may also find some useful information in this link:

http://oraclequirks.blogspot.com/2007/08/ora-14452-attempt-to-create-alter-or.html
Re: ora-14452 [message #334123 is a reply to message #334116] Tue, 15 July 2008 08:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It may be a 10g/11g thing.
I ran an almost identical set of tests on 10g (10.2.0.1) and got no rows returned.

Previous Topic: Error Assoc Arrays Data Type as OUT Formal Parameter
Next Topic: upgradation document
Goto Forum:
  


Current Time: Wed Dec 07 22:28:04 CST 2016

Total time taken to generate the page: 0.06758 seconds