任务：表空间管理
1)创建永久表空间TOOLS，数据文件初始大小40M，自动扩展至4G，固定区大小4M，段空间手动管理
2)创建永久表空间TS_IDX,数据文件大小40M
3)创建默认永久表空间TS_USERS,数据文件大小100M，自动扩展至4G
4)修改表空间TS_IDX，增加数据文件ts_idx02.dbf，文件大小40M
5)删除tools表空间及其数据文件
--1)创建永久表空间TOOLS，数据文件初始大小40M，自动扩展至4G，固定区大小4M，段空间手动管理
SQL> create tablespace tools datafile '/u01/app/oracle/oradata/ORA11GR2/tool01.dbf' size 40m autoextend on maxsize 4G uniform size 4m segment space management manual;
Tablespace created.
--2)创建永久表空间TS_IDX,数据文件大小40M
SQL> create tablespace ts_idx datafile '/u01/app/oracle/oradata/ORA11GR2/ts_idx.dbf' size 40m;
Tablespace created.
--3)创建默认永久表空间TS_USERS,数据文件大小100M，自动扩展至4G
SQL> create tablespace ts_users datafile '/u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf' size 100m autoextend on maxsize 4G;
Tablespace created.
——查询系统默认的永久表空间：
SQL> desc database_properties
Name Null? Type
----------------------------------------- -------- ----------------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
SQL> select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS
——修改系统默认的永久表空间：
SQL> alter database default tablespace ts_users;
Database altered.
——验证：
SQL> select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TS_USERS
--4)修改表空间TS_IDX，增加数据文件ts_idx02.dbf，文件大小40M
SQL> alter tablespace ts_idx add datafile '/u01/app/oracle/oradata/ORA11GR2/ts_idx02.dbf' size 40m;
Tablespace altered.
——查看验证：
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select TABLESPACE_NAME,BYTES/1024/1024 m,FILE_NAME from dba_data_files order by 1,3;
TABLESPACE_NAME M FILE_NAME
--------------- ------ ------------------------------------------------------------
EXAMPLE 346 /u01/app/oracle/oradata/ORA11GR2/example01.dbf
SYSAUX 580 /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
SYSTEM 760 /u01/app/oracle/oradata/ORA11GR2/system01.dbf
TOOLS 40 /u01/app/oracle/oradata/ORA11GR2/tool01.dbf
TS_IDX 40 /u01/app/oracle/oradata/ORA11GR2/ts_idx.dbf
TS_IDX 40 /u01/app/oracle/oradata/ORA11GR2/ts_idx02.dbf
TS_USERS 100 /u01/app/oracle/oradata/ORA11GR2/ts_users01.dbf
UNDOTBS1 90 /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
USERS 5 /u01/app/oracle/oradata/ORA11GR2/users01.dbf
9 rows selected.
--5)删除tools表空间及其数据文件
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMP1
TEMP2
TOOLS
TS_IDX
TS_USERS
11 rows selected.
SQL> drop tablespace tools including contents and datafiles;
（将表空间内的方案和操作系统层面的对应的物理数据文件一并删除）
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMP1
TEMP2
TS_IDX
TS_USERS
10 rows selected.
——验证是否还有tools表空间的物理数据文件：
[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/
[oracle@wang ORA11GR2]$ ls
control01.ctl redo4_a.log redo7_a.log temp_02.dbf
control02.ctl redo4_b.log redo7_b.log ts_idx02.dbf
example01.dbf redo5_a.log sysaux01.dbf ts_idx.dbf
redo01.log redo5_b.log system01.dbf ts_users01.dbf
redo02.log redo6_a.log temp_01.dbf undotbs01.dbf
redo03.log redo6_b.log temp01.dbf users01.dbf
[oracle@wang ORA11GR2]$
发现没有tool01.dbf数据文件了！