How to delete users and tablespaces in Oracle

Oracle how to delete users and tablespaces, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Oracle deletes users and tablespaces

Oracle has been used for a long time, adding a lot of user and tablespace. Needs to be cleaned up.

For individual user and tablespace, this can be done using the following command.

Step 1: delete user

Drop user × × cascade

Note: if user is deleted, only the schema objects under the user is deleted, and the corresponding tablespace will not be deleted.

Step 2: delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES

However, because it is a db for the development environment, there is a lot of user and table space to clean up.

Train of thought:

Export lists all the user and tablespace in the DB, filters out the systematic and useful tablespace, and load the useful information into a table. Then write a routine loop to delete the tablespace that is not in the useful table.

1. Select username,default_tablespace from dba_users

two。

Create table MTUSEFULSPACE (ID Number (4) NOT NULL PRIMARY KEY, USERNAME varchar2 (30), TABLESPACENAME varchar2 (60), OWNERNAME varchar2 (30))

3.

Declare icount number (2); tempspace varchar2 (60); begin for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users) loop tempspace: = curTable.alltblspace; dbms_output.put_line (tempspace); select count (TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace; if icount=0 then DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES; end if; commit; end loop;end

The following error will be reported after execution

ORA-06550: line 10, column 5: PLS-00103: the symbol "DROP" appears at one of the following times: begin case declare exit for goto if loop mod null pragma raise return select update while with