Detailed introduction to Oracle deleting users and table spaces

Oracle Delete users and tablespaces

Oracle has been used for a long time, and many new users and tablespaces have been added. They need to be cleaned up

For a single user and tablespace, you can Use the following command to complete.

Step 1: Delete user

drop user ×× cascade

Description: Deleting a user only deletes the schema objects under the user, but does not delete the corresponding tablespace.

Step 2: Delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

However, because it is a db for development environment, there are a lot of user and table spaces that need to be cleaned up.

Idea:

Export all users and tablespaces in the DB, filter out system and useful tablespaces, and load 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;

2.

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;

After execution, the following error will be reported

ORA-06550: 第 10 行, 第 5 列: PLS-00103: 出现符号 "DROP"在需要下列之一时： begin case declare exit for goto if loop mod null pragma raise return select update while with    << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

drop tablespace tablespace_name;

drop tablespace tablespace_name including contents;

drop tablespace tablespace_name including datafiles;

drop tablespace tablespace_name including contents and datafiles;

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;