Oracle deletes users and tablespaces. oracle deletes users and tablespaces.

Oracle deletes users and tablespaces. oracle deletes users and tablespaces.

Oracle deletes users and tablespaces

Oracle has been in use for a long time, and many new users and tablespace are added. You need to clear them.

For a single user and tablespace, you can use the following command.

Step 1: delete a user

drop user ×× cascade

Note: If a user is deleted, the schema objects under the user is deleted, and the corresponding tablespace is not deleted.

Step 2: delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

However, because the database is used by the development environment, many user and table spaces need to be cleared.

Ideas:

Export all the user and tablespace in the database, filter out the system and useful tablespace, and load useful information to a table. Then write a routine loop to delete the tablespace that is not in use in the 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;

The following error will be reported after execution

ORA-06550: 10th rows, 5th columns: PLS-00103: The symbol "DROP" appears when you need one of the following: 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 pipe06550. 00000-"line % s, column % s: \ n % s "* Cause: Usually a PL/SQL compilation error. * Action:

It seems that it is locked ..

No way. If the routines cannot be written, they can only group out statements for execution.

Export the user and tablespace to Excel. Use the CONCATENATE group to output the SQL statement.

Paste it to SQLdevelop for batch execution.

The entire deletion takes about 12 hours. It takes more than 100 users.

To find the specific location of 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 tablespace, but does not contain physical files

drop tablespace tablespace_name;

-- Delete non-empty tablespace, but does 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 tablespace, including physical files

drop tablespace tablespace_name including contents and datafiles;

-- If the tables in other tablespaces have foreign keys and other CONSTRAINTS associated with the table fields in the current tablespace, cascade constraints must be added.

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!