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,
Non preemptive static priority scheduling algorithm for operating system (C language )Go Language learning notes (GUI programming )XCTF Attack and defense world web Advanced practice _ 2_lottery What's the difference between computer major and training background ?python realization vlookup_ Dry goods I : Why python It's inside vlookup Bubble sort primary springboot2 Separation of front and rear platforms ,token Put in header Pit for verification Python Case conversion of letters ( Two methods )javascript event ( Detailed explanation of zero basis )Unity2019 UIElement note ( ten ) Simple exercise 2