oracle 删除表空间及数据文件方法

oracle 删除表空间及数据文件方法

 

-删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

 

 

以system用户登录，查找需要删除的用户：

--查找用户
select *　from dba_users;
--查找工作空间的路径
select * from dba_data_files; 
--删除用户
drop user 用户名称 cascade;
--删除表空间
drop tablespace 表空间名称 including contents and datafiles cascade constraint;

例如：删除用户名成为ABC，表空间名称为ABC

--删除用户，及级联关系也删除掉
drop user ABC cascade;
--删除表空间，及对应的表空间文件也删除掉
drop tablespace ABC including contents and datafiles cascade constraint;

 

删除无任何数据对象的表空间：
首先使用PL/SQL界面化工具，或者使用oracle自带的SQL PLUS工具，连接需要删除的表空间的oracle数据局库。
确认当前用户是否有删除表空间的权限，如果没有 drop tablespace,请先用更高级的用户(如sys)给予授权或者直接用更高级的用户。
用drop tablespace xxx ，删除需要删除的表空间。
删除有任何数据对象的表空间
使用drop tablespace xxx including contents and datafiles;来删除表空间。
注意事项：
如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示ora-01911错误

 

1、以system用户登录查找需要删除的用户（普通用户没有删除权限）

select * from dba_users;

2、查询需要删除用户对应的表空间

select * from dba_data_files;

3、删除用户和表空间

drop user usernamecascade;

drop tablespace tablespacename including contents and datafiles cascade constraint;

在删除用户时可能会碰到无法删除当前连接的用户，这是由于还有数据库连接到该用户，有会话存在，需要先删除会话。

最暴力的做法是直接shutdown数据库，然后重启即可。。。

一般的操作是通过查询SessionID，手动杀掉会话再删除用户：

1）查询连接情况：select username,sid,serial# from v$session;

2）找到要删除用户的sid和serial并删除：alter system kill session 'sid,serial';

再执行删除用户的操作，如果还是无法删除说明还有连接的会话，继续执行删除会话的操作。
---------------------

Oracle数据库中删除了表空间物理文件XXX.ora后导致用drop tablespace删除表空间失败，解决方法如下：

　用sqlplus /nolog命令进入oracle数据库执行如下命令:
　　sql>conn /as sysdba;
　　sql>startup;（如果数据库已启动则不需要此命令）
　　sql>alter database datafile ''/home/oracle/XXX.ora'' offline drop；（/home/oracle/XXX.ora为表空间文件的物理路径）
　　sql>drop tablespace XXX；

执行完后，重启数据库即可。