oracle的表空间

来源：互联网发布：西西网站软件下载编辑：程序博客网时间：2024/05/16 16:06

创建表空间

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

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

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

CREATE TABLESPACE SAMPLE LOGGING DATAFILE 'D:\11.ora' SIZE 5M,'D:\22.ora' 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;