oracle的表空间

创建表空间

1：创建单个文件的表空间

CREATE TABLESPACE SAMPLE
 LOGGING
 DATAFILE 'D:\11.dbf' SIZE 5M
 REUSE 
 EXTENT MANAGEMENT LOCAL
 UNIFORM SEGMENT SPACE MANAGEMENT
 AUTO

 

2：创建多个文件的表空间

CREATE TABLESPACE SAMPLE
 LOGGING
 DATAFILE 'D:\11.dbf' SIZE 5M,
'D:\22.dbf' SIZE 5M
 REUSE 
 EXTENT MANAGEMENT LOCAL
 UNIFORM SEGMENT SPACE MANAGEMENT
 AUTO

 

 

删除表空间

1：删除表空间

drop tablespace sample

 

2：连同表空间的文件一同删除

drop tablespace sample including contents and datafiles;

 

 

 drop tablespace sample including contents and datafiles;

3：删除表空间 表之间有关联，关联的表在另一个表空间的话，则级联删除

 drop tablespace sample including contents and datafiles cascade constraints

 

查询表空间

1：查询所有表空间

Select * From Dba_Tablespaces

 

2：查询oracle用户的默认表空间和临时表空间

select default_tablespace, temporary_tablespace, d.username from dba_users d where d.username='用户名' group by  default_tablespace, temporary_tablespace, d.username

3：查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size   from dba_tablespaces t, dba_data_files d   where t.tablespace_name = d.tablespace_name   group by t.tablespace_name;