1 2 Previous Next 26 Replies Latest reply: Jan 31, 2013 8:20 AM by 988357 Branched to a new discussion. RSS

    Problem regarding Global Temporary table

    447396
      Hi,

      I am trying to create a global temporary table inside a procedure and after doing some work I want to drop it. But it is throwing an error like below :
      ORA-14452: attempt to create, alter or drop an index on temporary table already in use
      ORA-06512: at "E2E_TEST.TEST_GLOBAL", line 19
      ORA-06512: at line 2
      I am using the follwoing code :
      CREATE OR REPLACE procedure test_global
      as
      v_num number(10);
      v_smt varchar2(300);
      begin
      execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
      on commit preserve rows
      as
      select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
      where dept.DEPTNO = dept1.DEPTNO (+)';
      v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';
      execute immediate v_smt into v_num;
      dbms_output.put_line(v_num);
      execute immediate 'drop table RECON_TEST cascade constraints purge';
      end;
      /
      But It I write the code like below :
      CREATE OR REPLACE procedure test_global
      as
      v_num number(10);
      v_smt varchar2(300);
      begin
      execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
      as
      select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
      where dept.DEPTNO = dept1.DEPTNO (+)';
      v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';
      execute immediate v_smt into v_num;
      dbms_output.put_line(v_num);
      execute immediate 'drop table RECON_TEST cascade constraints purge';
      end;
      /
      Then it throwing an error :
      NO DATA FOUND.

      Can any one please suggest any work around.

      Regards,
      Koushik
        • 1. Re: Problem regarding Global Temporary table
          Eduardo Legatti
          Question:
          Why you want to drop the temporary table ? You don't need to do this.

          cheers
          • 2. Re: Problem regarding Global Temporary table
            27876
            Why do you want to create table and then drop it like that?
            why not create it once and use it forever? what good is it to perform the create-drop cycle everytime you run the code? is that code run by only one person at a time?

            You do realize that as soon as you do a create temporary table ... as select ...

            the rows are gone since a ddl commit will make the temp table empty, right?

            Just create the table once outside of the code and use it as often as you want, reducing the code and overhead. is that not better?
            • 3. Re: Problem regarding Global Temporary table
              447396
              Yes, there is a requirement that the code may run by more than person at a time.

              Agree with the point that the rows are gone since a ddl commit will make the temp table empty.

              But when I am creating the table using "ON COMMIT PRESERVE ROWS" , then I can't drop the table.

              Error is :
              ORA-14452: attempt to create, alter or drop an index on temporary table already in use
              ORA-06512: at "E2E_TEST.TEST_GLOBAL", line 19
              ORA-06512: at line 2

              Can you please explain why it is so.

              Regards,
              Koushik
              • 4. Re: Problem regarding Global Temporary table
                3520
                Yes, there is a requirement that the code may run by
                more than person at a time.
                And? Have you read documentation and understood what global temporary table really is and how it should be used in Oracle. It isn't like temporary tables in SQL Server for example. It is created but populated only in session time, and one user cannot see what other user has inserted there.

                >
                Agree with the point that the rows are gone since a
                ddl commit will make the temp table empty.

                But when I am creating the table using "ON COMMIT
                PRESERVE ROWS" , then I can't drop the table.
                So don't do that. Do things like it is expected not like you insist to do :)

                Gints Plivna
                http://www.gplivna.eu
                • 5. Re: Problem regarding Global Temporary table
                  447396
                  Thanks for your reply.

                  But I just I want to know what is reason that not allowing to drop me the global temporary table.
                  When I have tried it with normal table it works fine.
                  But with global temporary table it is not working.
                  Please find below the output of v$lock after executing the following code :
                  CREATE OR REPLACE procedure test_global
                  as
                  v_num number(10);
                  v_smt varchar2(300);
                  begin

                  execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
                  on commit preserve rows
                  as
                  select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
                  where dept.DEPTNO = dept1.DEPTNO (+)';

                  /*
                  execute immediate 'CREATE TABLE RECON_TEST
                  as
                  select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
                  where dept.DEPTNO = dept1.DEPTNO (+)';
                  */
                  v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';

                  execute immediate v_smt into v_num;

                  dbms_output.put_line(v_num);
                  --execute immediate 'create table re_test as select * from RECON_TEST';
                  --commit;
                  --execute immediate 'drop table RECON_TEST cascade constraints purge';
                  end;
                  /


                  select * from v_$lock where id1 = 92158;
                  ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
                  C0000000F95366B8 C0000000F95366D8 2145 TO 92158 1 3 0 88 2

                  A lock type 'TO' is there. Can you please give me some details about this lock type.

                  Regards,
                  Koushik
                  • 6. Re: Problem regarding Global Temporary table
                    Eduardo Legatti
                    Look this:

                    SGMS@ORACLE10> alter session set nls_language = american;
                    Session altered.
                    SGMS@ORACLE10> create global temporary table temp (cod number) on commit preserve rows;
                    Table created.
                    SGMS@ORACLE10> insert into temp values (1);
                    1 row created.
                    SGMS@ORACLE10> commit;
                    Commit complete.
                    I'm yet still connected

                    other session
                    SGMS@ORACLE10> drop table temp;
                    drop table temp
                    *
                    ERROR at line 1:
                    ORA-14452: attempt to create, alter or drop an index on temporary table already in use

                    The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
                    In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

                    see: http://www.oracle-base.com/articles/8i/TemporaryTables.php

                    cheers
                    • 7. Re: Problem regarding Global Temporary table
                      447396
                      Many thanks for the reply. A last simple query :
                      "The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction." -- It is valid for any transaction ?

                      As the follwoing code also throws the same error :
                      CREATE OR REPLACE procedure test_global
                      as
                      PRAGMA AUTONOMOUS_TRANSACTION;
                      v_num number(10);
                      v_smt varchar2(300);
                      begin

                      execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
                      on commit preserve rows
                      as
                      select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
                      where dept.DEPTNO = dept1.DEPTNO (+)';
                      end;
                      /


                      CREATE OR REPLACE procedure test_global1
                      as
                      v_num number(10);
                      v_smt varchar2(300);
                      begin
                      test_global;
                      v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';

                      execute immediate v_smt into v_num;

                      dbms_output.put_line(v_num);
                      --execute immediate 'create table re_test as select * from RECON_TEST';
                      --commit;
                      execute immediate 'drop table RECON_TEST cascade constraints purge';
                      end;
                      /


                      begin
                      e2e_test.test_global1;
                      end;
                      /

                      While I have used PRAGMA AUTONOMOUS_TRANSACTION;

                      Regards,
                      Koushik
                      • 8. Re: Problem regarding Global Temporary table
                        Eduardo Legatti
                        >> It is valid for any transaction ?

                        Yes, any transaction for the current session. In fact, the data rows are storage in a temporary tablespace (sort segments) at the end for the session. If exists active sessions using the temporary table then is not possible dropped, but is possible truncated.

                        What is your doubt in this moment ??

                        About AUTONOMOUS_TRANSACTION look this:
                        http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/13_elems002.htm

                        TESTE@teste> select tablespace_name, duration, temporary from user_tables where table_name = 'TEMP'

                        TABLESPACE_NAME DURATION T
                        ------------------------------ --------------- -
                        SYS$SESSION Y


                        cheers
                        • 9. Re: Problem regarding Global Temporary table
                          3520
                          You can simply try from another session - you cannot drop temporary table if someone has bound it to his session.

                          BUT THE QUESTION IS - what do you want to achieve? Create and drop it anyway? Why? Just for scientific reasons? :)

                          Another question - why do you want to use autonomous transactions together with dynamic SQL? Both are pretty powerful means, both should be used only if you cannot get away with stright static sql and stright normal transactions. Both can lead to unexpected results, both make code much harder to test and debug.

                          Have you heard of KISS principle? Keep it simple stupid. Don't use too advanced constructions if you can get along with simple solutions. Less used time, more robust and understandable code, better performance (at least it refers to dynamic sql), easier to test, shorter time to develop, less used person-days, less wasted $$$

                          Gints Plivna
                          http://www.gplivna.eu
                          • 10. Re: Problem regarding Global Temporary table
                            3520
                            A lock type 'TO' is there. Can you please give me
                            some details about this lock type.
                            According to metalink Note:186854.1 "The purpose of the lock (TO) is to prevent DDL from being issued against an object while DML is occurring."

                            Gints Plivna
                            http://www.gplivna.eu
                            • 11. Re: Problem regarding Global Temporary table
                              Eduardo Legatti
                              >> According to metalink Note:186854.1 "The purpose of the lock (TO) is to prevent DDL >> from being issued against an object while DML is occurring."

                              Good answer, Gints
                              thanks
                              • 12. Re: Problem regarding Global Temporary table
                                user571876
                                A lot of responses said you couldn't do what you want, however there is a solution and you are stuck with a "ORA-14452: attempt to create, alter or drop an index on temporary table already" if you try to drop a GLOBAL TEMPORARY TABLE

                                FYI, I am mostly an Informix developer I had a similar issue and I found a simple solution. Note I didn't much care for the tone by others in this thread constantly asking why you wanted to do what you were trying to do, however you might want to reference Oracle documents like "A97248-01"

                                Cheers,

                                Jon Strabala


                                CREATE GLOBAL TEMPORARY TABLE
                                tmp_junk on commit preserve rows as
                                select table_name from ALL_TABLES ;

                                --- other work in current session

                                SELECT count(*) from tmp_junk ;

                                --- other work in current session

                                --- right now a 'DROP table tmp_junk' SQL statement would generate
                                --- the ORA-14452 error because there are rows in the GLOBAL TEMPORARY TABLE

                                --- Solution do a TRUNCATE followed by a DROP

                                TRUNCATE table tmp_junk ;

                                drop table tmp_junk ;

                                --- more work in current session
                                • 13. Re: Problem regarding Global Temporary table
                                  3520
                                  Lot of responses tried to shed some light on your mind how to do things as they are supposed to do.
                                  Unfortunately it seems it is pointless....

                                  <rant on>
                                  Have you tried your super mega hyper approch????

                                  Here was one of your colleagues How to block readers in Oracle? and he even earned an entry in Andrew Clarke's blog
                                  http://radiofreetooting.blogspot.com/2007/04/ask-stupid-question.html

                                  I suspect that you can be next candidate.
                                  <rant off>

                                  If you had tried then you'd found that it is not true.
                                  SQL> select version from v$instance;

                                  VERSION
                                  -----------------
                                  9.2.0.7.0
                                  SQL> CREATE GLOBAL TEMPORARY TABLE
                                    2  tmp_junk on commit preserve rows as
                                    3  select table_name from ALL_TABLES ;

                                  Table created.

                                  SQL> SELECT count(*) from tmp_junk ;

                                    COUNT(*)
                                  ----------
                                        2650

                                  ---------------------
                                  in other session:
                                  SQL> insert into tmp_junk
                                    2  select table_name from ALL_TABLES ;

                                  2651 rows created.

                                  back to the original one:
                                  ---------------------

                                  SQL> truncate table tmp_junk ;

                                  Table truncated.

                                  SQL> drop table tmp_junk ;
                                  drop table tmp_junk
                                             *
                                  ERROR at line 1:
                                  ORA-14452: attempt to create, alter or drop an index on temporary table already in use
                                  OK I can assume 0.0000000001% possibility that it was true in the very very early days of temporary table's but definitely it is not true in 9.2 and 10.2

                                  Try to understand that each DB is different, use its features as it is supposed to use and the life will be easier for you.

                                  Gints Plivna
                                  http://www.gplivna.eu
                                  • 14. Re: Problem regarding Global Temporary table
                                    6363
                                    Note I didn't much care for the tone by others in this thread constantly asking why you wanted to do what you were trying to do
                                    Note I don't much care for the sensibilities of programmers who foist expensive, none scalable, error prone applications that lack transactional integrity on their employers or customers, just because they do not understand the database they are using.

                                    If you create and drop temporary tables in a stored procedure in Oracle, you are doing it wrong, and there is nothing wrong with being told that.
                                    1 2 Previous Next