--转载自 https://blog.csdn.net/sunny05296/article/details/81126548
--以sysdba用户登录,查找需要删除的用户
conn / as sysdba
--查找用户
select * from dba_users;
select username from dba_users;
select username from dba_users where username='JACK';
select username from all_users where username='JACK';
--查看所有表空间总大小、已使用大小、剩余大小
select a.tablespace_name,
total "Total(M)",
free "Free(M)",
total - free "Used(M)",
round(((total - free) / total) * 100, 2) "Used(%)"
from (select tablespace_name, sum(bytes) / 1024 / 1024 total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
--查找表空间存储文件的路径
select * from dba_data_files;
--查看所有表占用的表空间的大小
select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type;
--查看表所属的用户
select owner,table_name from dba_tables where table_name='TEST_TBL01';
--查看用户所在的表空间(查看当前用户的缺省表空间)
conn JACK
select username,default_tablespace from user_users;
--查看表所属的表空间(查看所有表)
select table_name,tablespace_name from user_tables;
--查看表所属的表空间(查看指定表)
select table_name,tablespace_name from user_tables where table_name=upper('&table_name');
--删除用户
--注意:指定 cascade 会删除用户下的所有对象(包括表、视图、主键、外键、索引等;但不会删除存储过程、函数、包)。如果不指定则仅仅只删除用户,一般建议指定
conn /as sysdba
drop user myusername cascade;
--删除表空间
drop tablespace mytablespace including contents and datafiles cascade constraint;
例如:删除用户JACK及表空间USER_DATA:
--删除用户JACK,级联删除
drop user JACK cascade;
--删除表空间,及对应的表空间文件也删除掉。注意:如果多个用户使用相同的表空间,删除用户时不要删除表空间
drop tablespace USER_DATA including contents and datafiles cascade constraint;
--Tablespace表空间删除
--转自 http://blog.itpub.net/28793776/viewspace-1587612
一、普通表空间删除:
Oracle 11g删除表空间语法描述:
DROP TABLESPACE tablespace_name [ including contents [ and datafiles ] [ CASCADE CONSTRAINT 搜索] ];
无选项 -- 当表空间为空才能删除;
including contents — 删除表空间及对象;
including contents and datafiles — 删除表空间、对象及数据文件;
including contents CASCADE CONSTRAINT — 删除关联;
including contents and datafiles cascade constraint -- 含前两项。
生成脚本:
select 'drop tablespace ' || tablespace_name ||
' including contents and datafiles cascade constraint;'
from dba_data_files
where tablespace_name not in
('SYSTEM', 'SYSAUX', 'USERS', 'EXAMPLE', 'UNDOTBS2', 'UNDOTBS1');
二、分区表空间删除:
select 'alter table ' || owner || '.' || segment_name || ' drop partition ' ||
partition_name || ' ;'
from dba_segments
where segment_name in (select distinct segment_name
from dba_segments
where tablespace_name = 'p1'
and segment_type like '%PART%')
and tablespace_name <> 'p1';
得出:
alter table CP.IDX_CP_HANDLE_BATCH_NO drop partition SYS_P200 ;
alter table CP.IDX_CP_HANDLE_REQUEST_ID drop partition SYS_P200 ;
alter table CP.IDX_CP_PAYMENT_REQUEST_ID drop partition SYS_P201 ;
alter table CP.IDX_CP_PAYMENT_TRAN_NO drop partition SYS_P201 ;
alter table CP.IDX_CP_REQUEST_ID drop partition SYS_P199 ;
alter table CP.IDX_CP_REQUEST_TRAN_NO drop partition SYS_P199 ;
alter table CP.TBL_CP_HANDLE drop partition SYS_P200 ;
alter table CP.TBL_CP_PAYMENT drop partition SYS_P201 ;
alter table CP.TBL_CP_REQUEST drop partition SYS_P199 ;
三、异常处理:
报错有下面几种:
一. ORA-23515
--- ORA-23515: materialized views and/or thei