oracle怎么删除schema-Oracle-PHP中文网

oracle怎么删除schema

WBOY

发布： 2022-05-25 17:47:25

原创

4426人浏览过

在oracle中，可以利用drop语句删除schema，语法为“drop user username cascade;”；drop语句用于删除表的结构，包括schema，schema是数据库对象的集合，也可以理解为用户。

oracle怎么删除schema

本教程操作环境：Windows10系统、Oracle 11g版、Dell G3电脑。

oracle怎么删除schema

删除schema语法如下：

drop user username cascade;

登录后复制

示例如下：

1)查看用户的默认表空间及临时表空间

set lines 300
col username for a30
select username ,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username=&#39;MESPRD&#39;;
-----------------------------------
USERNAME   DEFAULT_TABLESPACE     TEMPORAR    Y_TABLESPACE
MESPRD    HTTBS_MESPRD                        TEMP

登录后复制

2)查看该用户的权限和角色

select privilege from dba_sys_privs where grantee=&#39;SYSADM&#39;
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=&#39;MESPRD&#39; );
-----------------------------------
PRIVILEGE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

登录后复制

已选择9行。

3)获取获得授予用户权限的脚本

select &#39;grant &#39;||privilege||&#39; to SYSADM;&#39; from (select privilege from dba_sys_privs where grantee=&#39;SYSADM&#39;
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=&#39;SYSADM&#39; ));

登录后复制

4)执行脚本获得删除该schema下对象的脚本 mesprd为要删除的schema

connect mesprd/MESPRD
spool E:\app\Administrator\del_mesprd.sql;
select &#39;alter table &#39;||table_name||&#39; drop constraint &#39;||constraint_name||&#39; ;&#39; from user_constraints where constraint_type=&#39;R&#39;;
select &#39;truncate table &#39;||table_name ||&#39;;&#39; from user_tables;
select &#39;drop table &#39;||table_name ||&#39; purge;&#39; from user_tables;
select &#39;drop index &#39;||index_name ||&#39;;&#39; from user_indexes;
select &#39;drop view &#39; ||view_name||&#39;;&#39; from user_views;
select &#39;drop sequence &#39; ||sequence_name||&#39;;&#39; from user_sequences;
select &#39;drop function &#39; ||object_name||&#39;;&#39;  from user_objects  where object_type=&#39;FUNCTION&#39;;
select &#39;drop procedure &#39;||object_name||&#39;;&#39; from user_objects  where object_type=&#39;PROCEDURE&#39;;
select &#39;drop package &#39;|| object_name||&#39;;&#39; from user_objects  where object_type=&#39;PACKAGE&#39;;
select &#39;drop database link &#39;|| object_name||&#39;;&#39; from user_objects  where object_type=&#39;DATABASE LINK&#39;;
spool off;

登录后复制

5)sqlplus连接到该schema下，执行如上获得的脚本

执行前查看下该schema下的对象，执行后再次查看下该schema下的对象

@？\E:\app\Administrator\del_mesprd.sql;
SQL> select object_type,count(*) from user_objects group by object_type;

登录后复制

6)kill掉连接数据库的session

select &#39;alter system kill session &#39;&#39;&#39;||sid||&#39;,&#39;||serial#||&#39;&#39;&#39; immediate;&#39; from v$session where username=&#39;MESPRD&#39;;

登录后复制

7)删除该schema