Oracle – Drop User Cascade Running for Over 12 Hours

oracle

We have a database that has reached it's 12 GB limit. Export of the user went ok with no errors or warnings. Next step is to drop it completely and then do an import.

Trouble is that drop user [name] cascade; never finishes. And when we look at the object count for that user, nothing has been dropped.

When we look at what the session dropping the user is waiting on we get these two a lot: latch: cache buffers chains, resmgr:cpu quantum.

We also get this one: log file switch completion, not so often, but the wait time is long (700+ seconds on the one in wait just now).

Here is the result of a query against the v$session_event for the drop user cascade SID:

SID EVENT                               TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS      CON_ID
--- ----------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------- ------
 61 Disk file operations I/O                     89              0           0            0          0              3741  166678035    1740759767           8 User I/O             3
 61 control file sequential read                654              0           0            0          0              4274 3213517201    4108307767           9 System I/O           3
 61 control file parallel write                  66              0           8 ,12                   0             82283 4078387448    4108307767           9 System I/O           3
 61 latch: cache buffers chains                   3              0           1 ,41                   1             12447 2779959231    3875070507           4 Concurrency          3
 61 write complete waits                         11              0         886         80,5        177           8855414 4229542060    3290255840           2 Configuration        3
 61 log file switch completion                    1              0           0 ,25                   0              2459 3834950329    3290255840           2 Configuration        3
 61 log file sync                                 6              0           1 ,17                   0             10429 1328744198    3386400367           5 Commit               3
 61 db file sequential read                    3264              0          14            0          0            140814 2652584166    1740759767           8 User I/O             3
 61 db file scattered read                      692              0          10 ,01                   0             99381  506183215    1740759767           8 User I/O             3
 61 db file single write                         20              0           2 ,09                   0             17032 1307477558    1740759767           8 User I/O             3
 61 db file parallel read                         6              0           0 ,01                   0               818  834992820    1740759767           8 User I/O             3
SID EVENT                               TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS      CON_ID
--- ----------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------- ------
 61 index (re)build lock or pin object          204              0           0            0          0              1007 3347698104    4166625743           3 Administrative       3
 61 latch: shared pool                           11              0           0 ,01                   0              1369 2696347763    3875070507           4 Concurrency          3
 61 resmgr:cpu quantum                            6              0          19         3,14         18            188682 1452455426    2396326234          10 Scheduler            3
 61 SQL*Net message to client                    17              0           0            0          0                42 2067390145    2000153315           7 Network              3
 61 SQL*Net message from client                  17              0       62533      3678,42      30488         625331207 1421975091    2723168908           6 Idle                 3
 61 events in waitclass Other                   364              5         427         1,17        100           4267757 1736664284    1893977003           0 Other                3

17 rows selected.

Any insights or tips would be great.

Database is Oracle XE 18.04.

Best Answer

'drop user' has to be atomic, which means we need to be able to roll it back, so its potentially a big operation depending on the number of objects and dependencies.

In some circumstances, I've found it faster to drop all the objects first for that user and then drop the user. For example spooling something like this

select 'drop table '||owner||'.'||table_name||' cascade constraints purge;'
from dba_tables
where owner = upper('&&owner')
union all
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('TABLE','INDEX','PACKAGE BODY','TRIGGER','LOB','JOB')
and object_type not like '%LINK%'
and object_type not like '%PARTITION%'
and owner = upper('&&owner')

and running that covers the typical majority of objects. (There can be leftovers, like scheduler jobs etc) but at least at normally gets the number of objects down very low. And then run 'drop user' after that

Related Solutions

Skip running trigger during cascade delete

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place. It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y. Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.

I killed a session and it is still running

From a thread on AskTom, when a session is killed:

The session is killed but it is being cleaned up. When you kill a session -- outstanding work must still be cleaned up.

When you do DML on a table, Oracle may make the changes to the base table almost immediately, even if you don't commit. The other sessions don't see these changes yet thanks to multiversion read consistency but the actual physical block may already be overwritten with your uncommited work.

This is done because Oracle is optimized for commit. Most sessions should rarely rollback so Oracle anticipates by writing preemptively the changes. This is also one of the reasons that allows Oracle to have arbitrarily large transactions (limited only by the size of the undo tablespace). The changes are not delayed (much). This is also why you almost never wait for a commit in Oracle, even for multimillion-row DML.

One of the consequences is that rollback needs to undo the changes. The uncommited transaction is read back from the undo tablespace and each and every change is undone in reverse order. This can take longer than the initial DML for several reasons:

The rollback is not as well optimized, in particular multi-row operations can take a lot longer than regular DML.
Some changes are more expensive in reverse. For instance a delete which is a relatively cheap DML becomes an insert in reverse (which is more expensive).

This explains why rollback can take a lot of time. You have few means to accelerate the process. In any case you should not:

kill the rollback process
shutdown the database in the hope that this will terminate the session

If you shutdown the database normally (or transactional or immediate), Oracle will wait for the process to complete so that the database is left in a consistent state. If you shutdown ABORT, the database will be left in an inconsistent state (uncommited data written to disk) and Oracle will resume the rollback as soon as the database is restarted.

The rollback is handled by smon, I wouldn't try to kill it.

Some session, after being killed, will remain in the v$session view, even after all their changes have been entirely rolled back. This is often because Oracle is waiting for the client to return a relevant error message, such as:

ora-00028 your session has been killed

If the client itself has already left or the network communication has been severed, the killed session may stay indefinitely until you restart the instance. I don't think these sessions take resources.

Best Answer

Related Solutions

Skip running trigger during cascade delete

I killed a session and it is still running

Related Question