表空间管理
   练习表空间的创建删除等管理操作


1) 创建永久表空间 TOOLS，数据文件初始大小 40M，自动扩展至 4G，固定区大小 4M，段空间手动管理；

SYS@PROD> create tablespace tools

  2  datafile '/u01/app/oracle/oradata/PROD/tools01.dbf'

  3  size 40M autoextend on maxsize 4G

  4  uniform size 4M

  5  segment space management manual;            #设置段空间手动管理，默认为自动管理，参数为auto

Tablespace created.

SYS@PROD>

2) 创建永久表空间 TS_IDX,数据文件大小 40M；

SYS@PROD> create tablespace ts_idx

datafile '/u01/app/oracle/oradata/PROD/ts_idx01.dbf' size 40M;

Tablespace created.

SYS@PROD>

3) 创建默认永久表空间 TS_USERS,数据文件大小 100M，自动扩展至 4G；

#创建表空间ts_users

SYS@PROD> create tablespace ts_users                                           

  2  datafile '/u01/app/oracle/oradata/PROD/ts_users01.dbf'

  3  size 100M autoextend on maxsize 4G;

Tablespace created.

 
#查看当前默认表空间

SYS@PROD> select PROPERTY_VALUE from DATABASE_PROPERTIES where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

----------------------------------------

USERS

 
#切换默认表空间

SYS@PROD> alter database default tablespace ts_users;

Database altered.

 
#再次查看默认表空间

SYS@PROD>  select PROPERTY_VALUE from DATABASE_PROPERTIES where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

----------------------------------------

TS_USERS

SYS@PROD>

4) 修改表空间 TS_IDX，增加数据文件 ts_idx02.dbf，文件大小 40M；

SYS@PROD> alter tablespace ts_idx add datafile

  2  '/u01/app/oracle/oradata/PROD/ts_idx02.dbf' size 40M;

Tablespace altered.

 
#查看表空间文件名称路径和大小

SYS@PROD> select tablespace_name,file_name,bytes/1024/1024 as m from dba_data_files order by 1,2;

TABLESPACE_NAME FILE_NAME                                                   M

--------------- -------------------------------------------------- ----------

SYSAUX          /u01/app/oracle/oradata/PROD/sysaux01.dbf                 325

SYSTEM          /u01/app/oracle/oradata/PROD/system01.dbf                 325

TOOLS           /u01/app/oracle/oradata/PROD/tools01.dbf                   40

TS_IDX          /u01/app/oracle/oradata/PROD/ts_idx01.dbf                  40

TS_IDX          /u01/app/oracle/oradata/PROD/ts_idx02.dbf                  40

TS_USERS        /u01/app/oracle/oradata/PROD/ts_users01.dbf               100

UNDOTBS1        /u01/app/oracle/oradata/PROD/undotbs01.dbf                195

USERS           /u01/app/oracle/oradata/PROD/users01.dbf                   50

8 rows selected.

SYS@PROD>

5) 删除 tools 表空间及其数据文件；

 
#查看当前的表空间

SYS@PROD> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

---------------

SYSTEM

SYSAUX

UNDOTBS1

TEMPTS1

USERS

TOOLS

TS_IDX

TS_USERS

8 rows selected.

 
#删除表空间以及数据文件

SYS@PROD> drop tablespace tools including contents and datafiles;

Tablespace dropped.

 
#再次查看

SYS@PROD> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

---------------

SYSTEM

SYSAUX

UNDOTBS1

TEMPTS1

USERS

TS_IDX

TS_USERS

7 rows selected.

SYS@PROD> 

6) 创建临时表空间组 TMP_GRP， 成员包括 temp1、 temp2， 每个成员大小为 10M； 

#查看当前数据库默认临时表空间
SYS@PROD> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';


PROPERTY_VALUE
----------------------------------------
TEMPTS1


#查看当前数据库有哪些类型的表空间
SYS@PROD> select tablespace_name,contents from dba_tablespaces;


TABLESPACE_NAME CONTENTS
--------------- ---------
SYSTEM          PERMANENT
SYSAUX          PERMANENT
UNDOTBS1        UNDO
TEMPTS1         TEMPORARY
USERS           PERMANENT
TS_IDX          PERMANENT
TS_USERS        PERMANENT


7 rows selected.



#创建临时表空间组TMP_GRP以及临时表空间temp1，temp2
SYS@PROD> create temporary tablespace temp1
  2  tempfile '/u01/app/oracle/oradata/PROD/temp_01.dbf' size 10M
  3  tablespace group TMP_GRP;


Tablespace created.


SYS@PROD> create temporary tablespace temp2
  2  tempfile '/u01/app/oracle/oradata/PROD/temp_02.dbf' size 10M
  3  tablespace group TMP_GRP;


Tablespace created.


#查看表空间组情况
SYS@PROD> select tablespace_name,contents from dba_tablespaces;


TABLESPACE_NAME CONTENTS
--------------- ---------
SYSTEM          PERMANENT
SYSAUX          PERMANENT
UNDOTBS1        UNDO
TEMPTS1         TEMPORARY
USERS           PERMANENT
TS_IDX          PERMANENT
TS_USERS        PERMANENT
TEMP1           TEMPORARY
TEMP2           TEMPORARY


9 rows selected.


SYS@PROD> select * from dba_tablespace_groups;


GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
TMP_GRP                        TEMP1
TMP_GRP                        TEMP2


#修改临时表空间组TMP_GRP为数据库默认临时表空间
SYS@PROD> alter database default temporary tablespace TMP_GRP;


Database altered.


#查看当前的默认临时表空间组
SYS@PROD> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';


PROPERTY_VALUE
----------------------------------------
TMP_GRP


SYS@PROD>