Re: drop tablespace takes forever

Hi Susan.

Sybrand is spot on regarding why it takes a long time to drop tablespaces. As for dropping users, there was the following script in the oramag archives from 1999 (www.oramag.com), which is said to be a much faster way of dropping users than using "drop user <user> cascade". Note that I haven't tested this, nor timed it to gauge the difference...

------------------cut-------------------------------------
/* This script asks username to be dropped */

set echo off
set head off
set verify off
set linesize 200
set pages 0
set feedback off
set term on

undefine p_user
def p_user = &&p_user

Prompt Generating Script To Drop User

set term off

SPOOL DROP_USER.sql

SELECT 'TRUNCATE TABLE ' || OWNER ||'.'||OBJECT_NAME || ' ;' FROM DBA_OBJECTS
WHERE OWNER = UPPER('&p_user')
AND OBJECT_TYPE = 'TABLE'
UNION
SELECT 'DROP TABLE ' || OWNER ||'.'||OBJECT_NAME || ' CASCADE;' FROM DBA_OBJECTS
WHERE OWNER = UPPER('&p_user')
AND OBJECT_TYPE = 'TABLE'
UNION
SELECT 'DROP ' || OWNER ||'.'|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';' FROM DBA_OBJECTS
WHERE OWNER = UPPER('&p_user')
AND OBJECT_TYPE IN ('PROCEDURE','PACKAGE','PACKAGE BODY', 'FUNCTION', 'SEQUENCE')
order by 1 desc
/

SPOOL OFF set term on

Prompt Dropping User Objects

set term off

START DROP_USER.SQL set term on

Prompt Dropping User

set term off

DROP USER &P_USER CASCADE; set pages 24
set head on
set verify on
set feedback on
undefine p_user
set term on
set echo on
------------------cut-------------------------------------

HTH, Dave.

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 27 2000 - 04:31:19 CST