3.修改表空间
3.1添加数据文件
SQL> alter tablespace my01
  2  add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01_add.dbf' size 10m;
表空间已更改。

3.2改变数据文件大小
SQL> alter database
  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01.dbf' resize 25m;
数据库已更改。

3.3数据文件自动扩展
SQL> alter database
  2   datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\my01.dbf'
  3   autoextend on next 10m maxsize 100m;
数据库已更改。

4.修改属性状态
SQL>  alter tablespace my01 offline;
表空间已更改。

SQL>  alter tablespace my01 online;
表空间已更改。

SQL>  alter tablespace my01 read only;
表空间已更改。

SQL>  alter tablespace my01 read write;
表空间已更改。

只读只能在联机情况下操作
SQL>  alter tablespace my01 read only;
 alter tablespace my01 read only
*
第 1 行出现错误:
ORA-01539: 表空间 'MY01' 未联机

读写只能在只读后操作
SQL>  alter tablespace my01 read write;
 alter tablespace my01 read write
*
第 1 行出现错误:
ORA-01646: 表空间 'MY01' 不是只读 - 无法设置为读写

5.修改表空间设置
5.1设置默认表空间
SQL> alter database default tablespace my01;
数据库已更改。

5.2设置默认临时表空间
SQL>  alter database default  temporary tablespace TEMP001;
数据库已更改。

6.删除表空间
6.1只删除数据字典定义，不删除数据文件，需要手动去系统删除数据文件
SQL> drop tablespace my01_1 including contents;
表空间已删除。

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM
MYUNDO01                       LOCAL      SYSTEM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
BIG01                          LOCAL      SYSTEM
BIG03                          LOCAL      SYSTEM
已选择13行。

 数据文件名不能加后缀
 SQL> drop tablespace my01_1.dbf including contents;
drop tablespace my01_1.dbf including contents
                      *
第 1 行出现错误:
ORA-02173: 无效的 DROP TABLESPACE 选项
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
MY02                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM
MYUNDO01                       LOCAL      SYSTEM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
BIG01                          LOCAL      SYSTEM
BIG03                          LOCAL      SYSTEM
已选择13行。

6.2连同数据文件一块删除
SQL> drop tablespace my02 including contents and datafiles;
表空间已删除。
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
TABLESPCE_TEST                 LOCAL      SYSTEM
TABLESPCE_TEMP                 LOCAL      UNIFORM
MY01                           LOCAL      UNIFORM
TEMP001                        LOCAL      UNIFORM
MYUNDO01                       LOCAL      SYSTEM
BIG01                          LOCAL      SYSTEM

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
BIG03                          LOCAL      SYSTEM
已选择12行。