How to delete schema in oracle-Oracle-php.cn

How to delete schema in oracle

WBOY

Release： 2022-05-25 17:47:25

Original

4652 people have browsed it

In Oracle, you can use the drop statement to delete the schema. The syntax is "drop user username cascade;"; the drop statement is used to delete the structure of the table, including the schema. The schema is a collection of database objects and can also be understood as user.

How to delete schema in oracle

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to delete schema in oracle

The syntax for deleting schema is as follows:

drop user username cascade;

Copy after login

How to delete schema in oracle

The example is as follows:

1 )View the user's default table space and temporary table space

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

Copy after login

2)View the user's permissions and roles

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

Copy after login

9 rows have been selected.

3) Get the script to grant user permissions

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

Copy after login

4) Execute the script to get the script to delete the object under the schema mesprd is the schema to be deleted

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;

Copy after login

5) sqlplus Connect to the schema and execute the script obtained above

Check the objects under the schema before execution, and check 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;

Copy after login

6) Kill the connected database 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;;

Copy after login

7) Delete the schema

drop user MESPRD cascade;

Copy after login

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to delete schema in oracle. For more information, please follow other related articles on the PHP Chinese website!