| Dropping tables takes a 15 seconds per table [message #55237] |
Mon, 13 January 2003 06:49  |
Elmer
Messages: 1
Registered: January 2003
|
Junior Member |
|
|
We have a little problem. I have a 5 gig script that is dropping several thousand tables. The script seems to stop at the 'drop table XXXX' line. We did a little searching and came up with the following sql statement that it seems to stop at on the Oracle side:
SELECT c.owner#, c.name, c.con#
FROM con$ c, cdef$ d
WHERE d.obj# = :1
AND d.con# = c.con#
This is happening with zero row tables. We thought about updating statistics on the system tables, however Oracle does not recommend this. Any ideas on how to 'speed' up a drop table command?
|
|
|
|
| Re: Dropping tables takes a 15 seconds per table [message #55260 is a reply to message #55237] |
Tue, 14 January 2003 14:41  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
When you drop a table it drops the table itself, indexes, constraints etc. If your table or indexes have gone into many extents (could be thousands) then all those Dictionary rows in the internal tables need to be deleted too which takes time. Identify the number of indexes, constraint and extents involved where the dropping is slow. If this table once held a lot of data (pushing the extents up) then you have probably found the cause.
select segment_name, segment_type, count(*)
from user_extents
group by segment_name, segment_type
order by 3 desc;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
