Shulou(Shulou.com)06/01 Report--

Close the application first

1) View the user's default and temporary tablespaces

Set lines 300

Col username for a30

Select username, default_tablespace,TEMPORARY_TABLESPACE from dba_users where username='MESPRD'

-

USERNAME DEFAULT_TABLESPACE TEMPORAR Y_TABLESPACE

MESPRD HTTBS_MESPRD TEMP

2) View the permissions and roles of the user

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='MESPRD')

-

PRIVILEGE

CREATE CLUSTER

CREATE INDEXTYPE

CREATE OPERATOR

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SESSION

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

Nine rows have been selected.

3) get the script that grants the user permission

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'))

4) execute the script to get the script mesprd that deletes the object under the schema as the schema to be deleted

Connect mesprd/MESPRD

Spool E:\ app\ Administrator\ del_mesprd.sql

Select 'alter table' | | table_name | | 'drop constraint' | | constraint_name | |'; 'from user_constraints where constraint_type='R'

Select 'truncate table' | | table_name | |'; 'from user_tables

Select 'drop table' | | table_name | | 'purge;' from user_tables

Select 'drop index' | | index_name | |'; 'from user_indexes

Select 'drop view' | | view_name | |'; 'from user_views

Select 'drop sequence' | | sequence_name | |'; 'from user_sequences

Select 'drop function' | | object_name | |'; 'from user_objects where object_type='FUNCTION'

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

Select 'drop package' | | object_name | |'; 'from user_objects where object_type='PACKAGE'

Select 'drop database link' | | object_name | |'; 'from user_objects where object_type='DATABASE LINK'

Spool off

5) sqlplus connects to the schema and executes the script obtained above

View the objects under the schema before execution, and then view the objects under the schema again after execution

@? \ e:\ app\ Administrator\ del_mesprd.sql

SQL > select object_type,count (*) from user_objects group by object_type

6) kill drops the session of the database

Select 'alter system kill session''| | sid | |','| | serial# | |''immediate;' from v$session where username='MESPRD'

7) Delete the schema

Drop user MESPRD cascade

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.