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
Daily Recommendation