ORA-14450 GTT alter table issue

Question

Iam facing a issue while altering the GTT table.

I need to find the active users on that table, Can someone help in query regarding how to find the active users on the specific object in oracle database

Answer 1

As far as I can tell, you didn't explain what problem you're trying to solve, but rather what you think that might help you solve it. So, let me try to guess.

Here's a global temporary table (GTT):

SQL> create global temporary table gtt (id number) on commit preserve rows;

Table created.

SQL> insert into gtt values (1);

1 row created.

SQL> commit;

Commit complete.

You're trying to alter it, but - it fails:

SQL> alter table gtt add name varchar2(10);
alter table gtt add name varchar2(10)
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

OK, so - delete row(s) and commit, then try again:

SQL> delete from gtt;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter table gtt add name varchar2(10);
alter table gtt add name varchar2(10)
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

Still no luck. But, if you truncate the table, then it works:

SQL> truncate table gtt;

Table truncated.

SQL> alter table gtt add name varchar2(10);

Table altered.

SQL>

---

If that's not what you're after, then see if this answers your question: connect as a privileged user (such as SYS, if you don't have any other) and run such a query: it shows who owns the GTT, its name, who locked it (which Oracle user) and which operating system user is it (that might help you to contact those people):

SQL> select b.owner,
  2         b.object_name,
  3         a.oracle_username,
  4         a.os_user_name
  5    from v$locked_object a, dba_objects b
  6   where a.object_id = b.object_id;

OWNER           OBJECT_NAM ORACLE_USERNAME OS_USER_NAME
--------------- ---------- --------------- ------------------------------
SCOTT           GTT        SCOTT           littlefoot

SQL>