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:
It seems to be locked. .
There is no way, the routine cannot be written, so we can only formulate statements and execute them.
Export the user and tablespace that need to be deleted to Excel. Use CONCATENATE to group the SQL.
Post it to SQLdevelop for batch execution.
The entire deletion will be time-consuming. It took about 12 hours for more than 100 users.
If you want to find the specific location of the datafile, you can use
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;
SQL code
- -Delete empty tablespaces, but do not contain physical files
drop tablespace tablespace_name;
--Delete non-empty tablespaces, but do not contain physical files
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;