This content has been marked as final. Show 26 replies
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
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
Riga if you ran ALL the statements in both yourFirstly I suggest you a bit to scan geographical map of Europe and find out that Riga is a city.
sessions you would error with a ORA-00955
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...
> 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.
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 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.
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
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 ;Well said.
drop table tmp_junk ;
--- more work in current session
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.
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