Oracle It's been used for a long time , Many new ones have been added user and tablespace. It needs to be cleaned up

For single user and tablespace Come on , This can be done using the following command .

  Step 1 :  delete user

drop user ×× cascade

explain : Deleted user, Just deleted the user Under the schema objects, Is not deleted tablespace Of .

Step 2 : delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

however , Because it's for the development environment db, Need to clean up user and table space quite a lot .

thinking :

 Export Out DB All in user and tablespace, Screen out systematic and useful tablespace, Put useful information load Go to a list .

Then write a routine loop , It's not a watch tablespace Delete

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: The first 10 That's ok , The first 5 column :
PLS-00103: Appear symbol "DROP" When one of the following is needed :
 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 looks like it's locked ..

no way out , Routine cannot be written , You can only group statements for execution .

Delete the user, tablespace Export to Excel. use CONCATENATE Group out SQL.

Post to SQLdevelop Batch execution .

The whole deletion will take time , 100 Multiple user.  Yes 12 About an hour .

If you want to find datafile Specific location of , have access to

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

SQL code
-- Delete empty table spaces , But it does not contain physical files
drop tablespace tablespace_name;
-- Delete non empty table spaces , But it does not contain physical files
drop tablespace tablespace_name including contents;
-- Delete empty table space , Include physical files
drop tablespace tablespace_name including datafiles;
-- Delete non empty table spaces , Include physical files
drop tablespace tablespace_name including contents and datafiles;
-- If a table in another table space has a foreign key and other constraints associated with the fields of the table in this table space , It's going to be added CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE
CONSTRAINTS;

Technology
©2019-2020 Toolsou All rights reserved,
In the problem of target detection “ recall Recall”,“ Accuracy Precision”Linux Page replacement algorithm C Language implementation About wechat payment body( Product description ) Problems encountered stay Vue Use in Web Worker Calculate the working days of each month c++ Memory Pointer out of bounds detection mechanism _CrtMemBlockHeadeJavaScript Medium Call and Apply Remember once EventBus Project issues caused by memory leaks Hundreds of millions of locusts rarely collide Locusts want to be self driving Heroes Java Swing JList: List box components