1 2 Previous Next 26 Replies Latest reply: Jan 31, 2013 8:20 AM by 988357 Go to original post Branched to a new discussion. RSS
      • 15. Re: Problem regarding Global Temporary table
        user571876
        Riga if you ran ALL the statements in both your sessions you would error with a ORA-00955

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

        thus you would have seen the need to pick another name

        Of course if another session uses an existing "temp table" is didn't create then you can get a ORA-14452 (as you saw)

        WRT table naming collisions one could use SELECT SYS_CONTEXT('USERENV', 'SESSIONID') FROM DUAL as a portion of the name.

        FYI, your comments were at least funny
        [rant on]
        super mega hyper approch
        If you had tried then you'd found that it is not true.
        one of your colleagues ... earned an entry ... I suspect that you can be next candidate.
        OK I can assume 0.0000000001% possibility
        [rant off]
        • 16. Re: Problem regarding Global Temporary table
          3520
          Riga if you ran ALL the statements in both your
          sessions you would error with a ORA-00955
          Firstly I suggest you a bit to scan geographical map of Europe and find out that Riga is a city.

          Second thing - of course if you run all the statements in both sessions then you'll get an error. Just like with ordinary tables. The only difference is that you should not do that - the concept of temporary tables in Oracle is different than temporary tables in MS SQL or probably some other DBMSes. In Oracle normally you create the definition of temporary table and reuse it in your code, and each session can see only it's data. In MS SQL you create temporary table on the fly and work with it.
          If you use the same approach in Oracle then your code will be at least:
          1) less maintainable because you'll need to use dynamic SQL,
          2) more prone to errors because of the same reason,
          3) less scalable because of dynamic sql as well as doing DDL on the fly.

          You won't see any performance difference of course if you'll have one user with one session but in this case Oracle probably isn't the right choice, MS Access for example most probably will suit your needs better.

          And it all isn't worth a penny if you have a hammer and everything for you looks like a nail...

          Gints Plivna
          http://www.gplivna.eu
          • 17. Re: Problem regarding Global Temporary table
            William Robertson
            > 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

            Sorry but what do you expect when the poster is trying to do something unscalable, resource-intensive and pointless? "Why do you want to do that?" is exactly the right question to ask.

            Poster: I want to rebuild my car from scratch each time I use it but my garage are telling me it can't be done.
            Forum members: Why do you need to do that? You don't need to. Just get in and drive it.
            Poster: But it's my requirement. I want to know why BMW has this limitation. I can do it with my Caterham. And I don't much care for your tone.
            • 18. Re: Problem regarding Global Temporary table
              319729
              You can drop the global temporary table as long as you are using it only in your procedure and not by any other procedure outside the procedure in which you created the global temp table.

              Attempt to truncate the global temp once you completed you work and just before you attemp to drop it.
              THen attempt to drop, which makes sure that no data in the table is useful as you created it as 'ON COMMIT PRESERVE ROWS'.

              Thanks
              • 19. Re: Problem regarding Global Temporary table
                319729
                Also, to add to what the rest of the people are saying, if you are planning to drop the table, why not use a normal table instead a global temporary. And if you say a GT then, why the hell you want to drop it, it is anyway session specific, do not worry about creating and dropping it.

                Thanks
                • 20. Re: Problem regarding Global Temporary table
                  663384
                  To answer your question regarding why people want to create and drop a temporary table. What if people want to alter the columns, datat ype, etc.. They can't do it, so they have to use the last resort. MV
                  • 21. Re: Problem regarding Global Temporary table
                    696818
                    Thanks for answering the question. I do very much care for people who actually answer the question.
                    And it is respectful to assume the persona asking the question may know what they want.

                    I have the same problem.

                    I think i can justify throw away temp tables: (which is deemed bad by some here)
                    I'm writing an archive utility, which is given by our business process that:
                    - no other sessions will login, no data will change during the archive process
                    - the archive utility needs to operate on 1000s of different table structures

                    In this case of generality throw away temp tables may be correct.
                    • 22. Re: Problem regarding Global Temporary table
                      499962
                      I think everyone is worried about the wrong thing. 'on commit preserve rows' is just for debug use, i.e., it allows you to see the temp table contents in an auto-commit environment. w/o row preservation you can't see what you just did. you can turn autocommit off to accomplish the same thing and then you don't need the 'preserve' clause
                      • 23. Re: Problem regarding Global Temporary table
                        788073
                        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"

                        ... --- Solution do a TRUNCATE followed by a DROP
                        >
                        TRUNCATE table tmp_junk ;

                        drop table tmp_junk ;

                        --- more work in current session
                        Well said.
                        • 24. Re: Problem regarding Global Temporary table
                          789774
                          I was having the same issue and adding "cascade constraints" resolved my problem. i.e. DROP TABLE TABLE_NAME CASCADE CONSTRAINTS;
                          • 25. Re: Problem regarding Global Temporary table
                            374812
                            Hi everybody!

                            I'm getting error ORA-14452 when dropping a user and none of the suggested solutions above solve it.

                            I wanted to drop a user that is no longer needed with

                            drop user <username> cascade;

                            and got ORA-14452.

                            I then dropped all the objects the user owned seperately and could drop them all with the exception of one temporary table. v$access shows that no one is accessing the table but still I cannot delete it. Neither truncating the table nor adding 'CASCADE CONSTRAINTS' to the drop command did any good.

                            Any ideas how I can get rid of the table?

                            I'm using Oracle 10.2.0.3.0 under AIX.

                            Regards
                            Michael
                            • 26. Re: Problem regarding Global Temporary table
                              988357
                              you need to check which session is currently holding lock, check using this query and kill that session.

                              --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              select * from v$lock
                              where id1 in (select object_id from dba_objects
                              where owner='user'
                              and object_name='GT_temp_table');

                              --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              Regards,
                              Irfan
                              1 2 Previous Next