Scenario, in a single connection I do this:
1. Start transaction.
2. Create table.
4. Start transaction.
5. Insert rows.
6. Select using join with other tables.
8. Start transaction.
9. Drop table.
---> ERROR: Object is in use.
I KNOW the table has been accessed only using this
connection and the
transactions listed above. So why is it in use at step 9
and how can I avoid
I need this to temporarily store some data to select data
for a report, and
the required set of columns in the temp table varies, so I
cannot use a GTT
since it has persistent DDL. Is there any other way to
achieve that, i.e. a
local temporary table? For pref I would like to have it
local to the
Waiting a while before step 8-9 (and subsequent commit) is
not an option
because this is done in application code that runs all the
way while the
user is waiting for the report to pop up - I do need to
drop it right away.
(Unless I would always just leave it there and instead
drop it before step 1
next time I run this kind of report, but it would annoy me
to have a useless
temp table lying around.)
What sort of transaction are you using? The select may be
retaining.. Do you
close the query?