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 <an identifier>
  <a double-quoted delimited-identifier> <a bind variable> <<
  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;

##--Delete empty tablespace, including physical files

drop tablespace tablespace_name including datafiles;

--Delete non-empty table spaces, including physical files

drop tablespace tablespace_name including contents and datafiles;

--If tables in other table spaces are associated with foreign keys and other constraints The fields of the table in this table space must be added with CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

Thank you for reading, I hope it can help everyone, thank you for your support of this site!

For more detailed introduction to Oracle deleting users and table spaces, please pay attention to the PHP Chinese website!