Oracle创建查询删除表空间操作

创建：

create tablespace 名字 
logging  
datafile
'E:\jlyj.dbf'  
size 1024m  
autoextend on 
next 1024m
maxsize 15000m  
extent management local;

 

查询：

1、查共有多少个表空间  select * from Dba_Tablespaces;

2、查某个用户拥有对哪个表空间的哪些操作权限

select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2
where a1.privilege = 'DROP TABLESPACE'
and a1.grantee =a2.granted_role;

3、查询某个用户下面有哪个表空间

select distinct a.tablespace_name  from dba_segments a where a.owner =upper('jlyj');

4、删除表空间

（PS：如果想要删除的干净点直接复制这句话就可以：drop tablespace 表空间名字 including contents and datafiles;）

 

一个用户要删除数据库的表空间,首先该用户要具备drop tablespace的权限

在当前用户下执行以下语句来查询确认

select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2 where a1.privilege = 'DROP TABLESPACE' and a1.grantee =a2.granted_role

SQL> conn xxx/xxx 已连接。 SQL> select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2 2 where a1.privilege = 'DROP TABLESPACE' 3 and a1.grantee =a2.granted_role;

USERNAME                       PRIVILEGE ------------------------------ ---------------------------------------- WULW                           DROP TABLESPACE

如果没有 drop tablespace,请先用更高级的用户(如sys)给予授权

SQL> conn  as sysdba; 已连接。 SQL> grant drop tablespace to wulw   ;

授权成功。

以上前提条件确认完,下面开始实验.

确认已有的表空间

SQL> select a.TS# ,a.NAME , b.NAME from v$tablespace a,v$datafile b 2 where a.TS# = b.TS#;

TS# NAME NAME 0 SYSTEM L:\ORACLE\ORADATA\IBM\SYSTEM01.DBF 1 UNDOTBS1 L:\ORACLE\ORADATA\IBM\UNDOTBS01.DBF 3 CWMLITE L:\ORACLE\ORADATA\IBM\CWMLITE01.DBF 4 DRSYS L:\ORACLE\ORADATA\IBM\DRSYS01.DBF 5 EXAMPLE L:\ORACLE\ORADATA\IBM\EXAMPLE01.DBF 6 INDX L:\ORACLE\ORADATA\IBM\INDX01.DBF 7 ODM L:\ORACLE\ORADATA\IBM\ODM01.DBF 8 TOOLS L:\ORACLE\ORADATA\IBM\TOOLS01.DBF 9 USERS L:\ORACLE\ORADATA\IBM\USERS01.DBF 10 XDB L:\ORACLE\ORADATA\IBM\XDB01.DBF 12 TDATA_01 L:\ORACLE\ORADATA\IBM\TDATA_01.ORA 14 TINDEX_01 L:\ORACLE\ORADATA\IBM\TINDEX_01.ORA

已选择12行。(为看起来直观,上面的显示作了手工格式化)

创建新的测试表空间:

SQL> create tablespace mytbs01 2 logging 3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE ;

表空间已创建。

SQL> CREATE TABLESPACE "MYTBS02" 2      LOGGING 3      DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ;

表空间已创建。

如果表空间里面没有任何数据对象,可以直接删除.

SQL> drop tablespace mytbs01;

表空间已丢弃。

这种删除方式相关的数据文件仍然存在于磁盘上.

(可以进入数据文件所在目录L:\ORACLE\ORADATA\IBM 进行查看 MYTBS01.DBF )

如果表空间里面含有数据对象,那么该表空间就不能像上面那样做直接删除了.

SQL> create tablespace mytbs01 2 logging 3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;

表空间已创建。

SQL> create table test(mobile number(13)) 2 tablespace mytbs01;

表已创建。

SQL> drop tablespace mytbs01; drop tablespace mytbs01 * ERROR 位于第 1 行: ORA-01549: 表空间非空，请使用 INCLUDING CONTENTS 选项

看到了吧,提示ora-01549错误.

如果要删除该表空间,可加上including contents子句.

如: drop tablespace mytbs01 including contents ;

如果想在删除表空间的同时也删除掉对应的数据文件,那就在上面的语句最后加上 and datafiles

成为 drop tablespace mytbs01 including contents and datafiles;

SQL> drop tablespace mytbs01 2 including contents and datafiles;

表空间已丢弃。

要注意的一点是,如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示ora-01911错误:

SQL> drop tablespace mytbs02 2 including datafiles; including datafiles            * ERROR 位于第 2 行: ORA-01911: 需要 CONTENTS 关键字

接下来的实验是:

如果表空间A中有一个表ta,表空间B中有一个表tb,而ta与tb有着某种关系,那么是否可以按上面的方法直接干掉表空间A和表空间B呢?

看试验过程:

SQL> create tablespace mytbs01 2 logging 3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;

表空间已创建。

SQL> CREATE TABLESPACE "MYTBS02" 2      LOGGING 3      DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ; CREATE TABLESPACE "MYTBS02" * ERROR 位于第 1 行: ORA-01543: 表空间 'MYTBS02' 已经存在

SQL> create table test(mobile number(13)) 2 tablespace mytbs01;

表已创建。

SQL> create table test2(mobile number(13)) 2 tablespace mytbs02;

表已创建。

SQL> alter table TEST2 2    add primary key (mobile);

表已更改。

SQL> ALTER   TABLE   test    2   ADD   CONSTRAINT   FOREIGN con_mobile    REFERENCES   test2(mobile);

表已更改。

以上的意思是:在表空间mytbs01上创建表test,在表空间mytbs02上创建表test2,两个表test和test2以外键相关联,test2为主表,test为从表.

现在尝试能否用上面的语句直接干掉mytbs02:

SQL> drop tablespace mytbs02; drop tablespace mytbs02 * ERROR 位于第 1 行: ORA-01549: 表空间非空，请使用 INCLUDING CONTENTS 选项

SQL> drop tablespace mytbs02 2 including contents; drop tablespace mytbs02 * ERROR 位于第 1 行: ORA-02449: 表中的唯一/主键被外部关键字引用

SQL> drop tablespace mytbs02 2 including contents and datafiles; drop tablespace mytbs02 * ERROR 位于第 1 行: ORA-02449: 表中的唯一/主键被外部关键字引用

可见主表所在表空间因为表与其他空间上的表有联系,所以没办法直接删掉.

那mytbs01表空间能不能干掉?请看:

SQL> drop tablespace mytbs01 2 including contents and datafiles;

表空间已丢弃。

SQL> drop tablespace mytbs02 2 including contents and datafiles ;

表空间已丢弃。

嘿嘿,从表test所在的表空间mytbs01能直接干掉,而且从表的表空间干掉后,主表test2所在的表空间mytbs02也能干掉了!

那么,如果我只想干掉主表所在的表空间,又不想干掉从表所在的表空间那怎么办?

很简单,最笨的一招就是想把两个表的关联关系给灭了,(在上面的两个表中,就是把那个外键给删了)再把主表表所在的表空间删了.但这种方法可不太现实,如果一个表空间里有成百上千个对象与别的表空间里的对象有联系,总不能一个一个去"解铃"吧? 即使你本人就是"系铃"人,估计你也要"解"到郁闷死!

一个更简单的方法当然是级联删除了！

drop tablespace mytbs02 including contents and datafiles cascade constraints

试验如下：

SQL> create tablespace mytbs01 2 logging 3 datafile 'L:\ORACLE\ORADATA\IBM\mytbs01.dbf' size 10m REUSE;

表空间已创建。

SQL> CREATE TABLESPACE "MYTBS02" 2      LOGGING 3      DATAFILE 'L:\ORACLE\ORADATA\IBM\MYTBS02.dbf' SIZE 10M REUSE ;

表空间已创建。

SQL> create table test(mobile number(13)) 2 tablespace mytbs01;

表已创建。

SQL> create table test2(mobile number(13)) 2 tablespace mytbs02;

表已创建。

SQL> alter table TEST2 2    add primary key (mobile);

表已更改。

SQL> ALTER   TABLE   test    2   ADD   CONSTRAINT   FOREIGN con_mobile    REFERENCES   test2(mobile);

表已更改。

SQL> drop tablespace mytbs02 2 including contents and datafiles cascade constraints;

表空间已丢弃。

转载于:https://www.cnblogs.com/mxqh/p/3658563.html