|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 8i / 9i
OS Details :
Sun Solaris9
It
is always recommended to use locally managed tablespaces but if you
are still using dictionary managed tablespaces then there
is a known problem in oracle when tables are dropped dynamically from
within PL/SQL. Oracle holds the segments till the completion of the
PL/SQL block or package or procedure even though it has been marked as
dropped. The fix for it is as follows :
-- Get the tablespace
details where the tables that are being dropped dynamically from
within PLSQL resides. Include it at the very beginning outside the
loop since it has to be done only once: (step 1)
select ts#+1 into
ts_variable
from ts$ where name='<tablespace_name>';
-- Execute the follow statement using 'EXECUTE
IMMEDIATE'
(should be included right after the drop
statement):
--
ts_variable has its value populated earlier (step 1)
alter session set events
''immediate trace name drop_segments level
ts_variable'; |
