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.

Thought 1:

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 one of the following is required:
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 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 #;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12