Oracle Quick Delete Schema/username

1. Question:

Oracle the schema of many objects, more than 30,000, directly execute the delete user command for a long time, half an hour did not delete complete

2. Pre-removal preparation

The general deletion of the user is to re-import the user data (do not delete the table space), involving the deletion of the schema of the reconstruction, so delete the user before the collection of the schema information

1) View the user's default tablespace and temporary table space
Set Lines 300
Col username for A30
Select Username, default_tablespace,temporary_tablespace from dba_users where username= ' TEST ';

2) View the user's permissions and roles

Select privilege from Dba_sys_privs where grantee= ' SYSADM '
Union
Select privilege from Dba_sys_privs where grantee in (select Granted_role from Dba_role_privs where grantee= ' TEST ');

3) Get a script that grants permissions to the user

Select ' Grant ' | | privilege| | ' to SYSADM, ' from (select privilege from Dba_sys_privs where grantee= ' SYSADM '
Union
Select privilege from Dba_sys_privs where grantee in (select Granted_role from Dba_role_privs where grantee= ' SYSADM '));

3. Quick Delete User method

1) Stop the application service that connects to the database

(Most applications use a reconnection mechanism, and the application will repeatedly connect to the database without shutting down the application service, even if the command that uses the database kill session does not end, the session will persist)

2) Execute script to get script to delete the schema

Test is the schema to delete

Connect Test/test
Spool/home/oracle/del_test.sql;
Prompt--drop Constraint
Select ' ALTER TABLE ' | | table_name| | ' drop constraint ' | | constraint_name| | '; ' from user_constraints where constraint_type= ' R ';

Prompt--truncate table
Select ' Truncate table ' | | table_name | | '; ' from User_tables;

Prompt--drop Tables
Select ' Drop table ' | | table_name | | ' Purge; ' from User_tables;

Prompt--drop Indexes
Select ' Drop index ' | | index_name | | '; ' from user_indexes;

Prompt--drop View
Select ' Drop View ' | | view_name| | '; ' from User_views;

Prompt--drop sequence
Select ' Drop sequence ' | | sequence_name| | '; ' from user_sequences;

Prompt--drop function
Select ' Drop function ' | |  object_name| | '; ' From user_objects where object_type= ' FUNCTION ';

Prompt--drop procedure
Select ' drop procedure ' | | object_name| | '; ' from user_objects where object_type= ' PROCEDURE ';

Prompt--drop Package
Prompt--drop Package body
Select ' Drop Package ' | | object_name| | '; ' from the user_objects where object_type= ' package ';

Prompt--drop Database link
Select ' Drop database link ' | | object_name| | '; ' from user_objects where object_type= ' DATABASE LINK ';

Spool off;

3) Sqlplus connected to the schema, execute the script obtained as above

View the object under the schema before executing, and then view the object under the schema again

Sql> Select Object_type,count (*) from user_objects GROUP by Object_type;

4) Kill the session connected to the database

Select ' Alter system kill session ' | | sid| | ', ' | | serial#| | ' immediate; ' from V$session where username= ' TEST ';

5) Delete the schema

Drop user test cascade;

Oracle Quick Delete Schema/username