oracle 表空间的管理

创建表空间：
create tablespace DATATEST datafile 'D:\SERVER\DATABASE\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATATEST.dbf' size 100M

eg:

create tablespace myspace datafile 'F:\oracle\product\10.2.0\oradata\orcl\myspace' size 20M;

 

创建临时表空间：
create temporary tablespace DATATEST_TEMP tempfile 'D:\SERVER\DATABASE\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATATEST_TEMP.dbf' size 50M autoextend ON next 10M maxsize 100M;

 

删除表空间：

drop database ts_name

alter tablespace ts_name offline;（windows环境下需要解除联机）

drop tablespace ts_name including contents;
drop tablespace ts_name including contents and datafiles;

 

查看表空间下的约束(唯一键约束和主键约束)：

select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';'
  from dba_constraints
  where constraint_type in ('u','p')
   and (index_owner, index_name) in
       (select owner, segment_name from dba_segments where tablespace_name = 'ts_name');