3、管理表空间和数据文件

一、表空间查询
1.在DBA_DATA_FILES数据字典中查询

SQL> col file_name for a50;
SQL> select tablespace_name, file_name ,bytes/1024/1024 M from dba_data_files or
der by tablespace_name;
TABLESPACE_NAME  FILE_NAME                                                   M
---------------- -------------------------------------------------- ----------
CWMLITE          D:\ORACLE\ORADATA\C_DB\CWMLITE01.DBF                       20
DRSYS            D:\ORACLE\ORADATA\C_DB\DRSYS01.DBF                         20
EXAMPLE          D:\ORACLE\ORADATA\C_DB\EXAMPLE01.DBF                  149.375
INDX             D:\ORACLE\ORADATA\C_DB\INDX01.DBF                          25
ODM              D:\ORACLE\ORADATA\C_DB\ODM01.DBF                           20
SYSTEM           D:\ORACLE\ORADATA\C_DB\SYSTEM01.DBF                       400
TOOLS            D:\ORACLE\ORADATA\C_DB\TOOLS01.DBF                         10
UNDOTBS1         D:\ORACLE\ORADATA\C_DB\UNDOTBS01.DBF                      200
USERS            D:\ORACLE\ORADATA\C_DB\USERS01.DBF                         25
XDB              D:\ORACLE\ORADATA\C_DB\XDB01.DBF                       38.125

2.SYSTEM 和SYSAUX 表空间
SYSTEM表空间存放数据字典表的结构和数据
SYSTEM 表空间内存放的对象

SQL> select distinct segment_type,owner,tablespace_name from dba_segments where
tablespace_name ='SYSTEM' order by owner ,segment_type;
SEGMENT_TYPE       OWNER                          TABLESPACE_NAME
------------------ ------------------------------ ----------------
INDEX              MDSYS                          SYSTEM
LOBINDEX           MDSYS                          SYSTEM
LOBSEGMENT         MDSYS                          SYSTEM
TABLE              MDSYS                          SYSTEM
INDEX              ORDSYS                         SYSTEM
TABLE              ORDSYS                         SYSTEM
INDEX              OUTLN                          SYSTEM
TABLE              OUTLN                          SYSTEM
INDEX              SCOTT                          SYSTEM
TABLE              SCOTT                          SYSTEM
CACHE              SYS                            SYSTEM

SYSAUX 表空间
SYSAUX是11G新加的表空间，存放各模式的对象数据，如：智能代理用户DBSNMP，数据挖掘用户ODM等。
查询SYSAUX表空间存放的用户对象

SQL> select owner ,count(*) from dba_segments where tablespace_name='SYSAUX' group by owner;

3.UNDO 表空间
UNDO表空间的段被称为撤销段或回退段。
撤销段的目的：
回退事务，独一致性，事务恢复，闪回操作
查看相关参数

--撤销管理方式
SQL> show parameter undo_management;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
undo_management                      string      AUTO
SQL> show parameter undo_tablespace;
--撤销表空间名称
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
undo_tablespace                      string      UNDOTBS1
--实例中配置的撤销保留时间
SQL> show parameter undo_retention;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
undo_retention                       integer     10800
--创建撤销表空间
create undo tablespace undo_tabs02 datafile '/OS_PATH/undo_t.dbf' size 2G;
--切换UNDO表空间
alter system set undo_tablespace = undo_tabs02 ;
注：在RAC（Real application cluster）机构中，不同例程必须使用独立的UNDO表空间，不能共用同一个UNDO表空间。
--检查UNDO表空间的数据
--UNDO统计信息，每10分钟统计一次
SQL> select to_char(begin_time,'HH24:MI:SS') begin_time,to_char(end_time,'HH24:MI:SS') end_time,undoblks from v$undostat;
BEGIN_TI END_TIME   UNDOBLKS
-------- -------- ----------
20:15:42 20:25:42         16
20:05:42 20:15:42         19
19:55:42 20:05:42         16
19:45:42 19:55:42         17
19:35:42 19:45:42         18
19:25:42 19:35:42         17
19:15:42 19:25:42         15
19:05:42 19:15:42         19
18:55:42 19:05:42         19
--显示UNDO段统计数
select a.name undo段的名称 ,b.xacts 包含的活动事务的个数,b.writes 写入的字节数 ,b.extents 段的区个数 
from v$rollname a, v$rollstat b where a.usn =b.usn;
UNDO段的名称                   包含的活动事务的个数 写入的字节数 段的区个数
------------------------------ -------------------- ------------ ----------
SYSTEM                                            0         6900          6
_SYSSMU1$                                         0       279714          6
_SYSSMU2$                                         0       370476          7
--显示活动事务信息
select a.username ,b.name,c.used_ublk from v$session a,v$rollname b ,v$transaction c 
where a.saddr = c.ses_addr and b.usn = c.xidusn and a.username='risk';
--显示UNDO信息区
select extent_id,bytes,status from dba_undo_extents where segment_name='_SYSMU5$';

4.临时表空间
临时表空间是一个磁盘空间，当内存排序不够使用时必须将数据写入的那个磁盘空间。排序操作完成后，Oracle系统自动的释放。
常见的临时表空间操作：
SELECT DISTINCT
UNION
INTERSECT
MINUS
ANALYZE
连接2个没有索引的表
第一、创建临时表空间：

CREATE [DEFAULT] --默认的临时表空间
TEMPORARY TABLESPACE TEMP_TBS 
TEMPFILE '/OS_PATH/TEMP1.DBF' SIZE 2G;
在v$tempfiles中查看临时表空间信息
SQL> select file_name ,bytes/1024/1024 M ,tablespace_name from dba_temp_files;
FILE_NAME                            M             TABLESPACE_NAME
D:\ORACLE\ORADATA\C_DB\TEMP01.DBF    40            TEMP

第二、临时表空间组
在11g中可以创建多个临时表空间，并且将他们组成一个临时表空间组，这样在排序时，使用组里的多个临时表空间。如果删除组中的全部临时表空间，那么组也被删除。
好处：
避免临时表空间不足时引起的磁盘排序问题
当一个用户同时有多个会话时，可以使用不同的临时表空间。

--1.创建临时表空间组
CREATE TEMPORARY TABLESPACE TEMPTS1 GEMPFILE '/OS_PATH/TEMP01.DBF'
SIZE 20M TABLESPACE GROUP GROUP_1;
--2.查看
SELECT * FROM DBA_TABLESPACE_GROUPS;
--3.将临时表空间换组
ALTER TABLESPACE TEMPTS1 TABLESPACE GROUP GROUP_N;
--4.为用户指定临时表空间
ALTER USER scott TEMPORARY TABLESPACE GROUP_N;
--5.修改数据库的临时表空间组
ALTER DATABASE c_db DEFAULT TEMPROARY TABLESPACE GROUP_N;

5.创建表空间

CREATE [SMALLFILE/BIGFILE] --大文件或小文件表空间
TABLESPACE tabspace_name   --名称
DATAFILE '/OS_PATH/file_name' SIZE INTEGER [K/M] --设置物理路径和大小
         REUSE   --如果文件存在，清除后重新建；否则直接建立
         [,'/OS_PATH/file_name' SIZE INTEGER [K/M] REUSE]
[AUTOEXTEND [OFF/ON] NEXT INTEGER[K/M] --是否自动扩展，ON需要设置NEXT 大小
[MAXSIZE[UNLIMITED /INTEGER [K/M]]]]  --最大表空间大小
[MINIMUM EXTENT INTEGER[K/M]]   
[DEFAULT STORAGE storage]    --指定以后要创建表，索引及簇的存储参数
[ONLINE/OFFLINE]    
[LOGGING/NOLOGGING]      --是否产生日志
[PERMANENT/TEMPORARY]    --永久或临时 ，默认是永久表空间
[EXTENT MANAGEMENT   --扩展段得管理      
[DICTIONARY/LOCAL    --数据字典管理还是本地管理，建议本地管理 
[AUTOALLOCATE|UNIFORM SIZE INTEGER[K/M]]]]
--例子---------------------------
create tablespace myspace datafile 'D:\ORACLE\ORADATA\C_DB\myspace01.DBF'
size 256M reuse
autoextend on next 20M maxsize unlimited
logging online
permanent 
-- blocksize 16384 加上这句，表示建立非标准块的表空间
extent management local autoallocate
segment space management auto;
--上面的表空间建立中，有很多参数可以是系统默认的。
--修改表空间大小的2种方法
alter database datafile 'D:\ORACLE\ORADATA\C_DB\myspace01.DBF' resize 1G; 
alter tablespace myspace resize 10G;
--改变表空间的读写状态
--条件：表空间处于ONLINE，没有任何回滚段，（归档模式或数据发行中不能改为只读）
alter tablespace myspace read only;
alter tablespace myspace read write;
------------------------------------------------------------
--表空间的操作  
------------------------------------------------------------
--1.修改表空间的名字
--先查询
SELECT TABLESPACE_NAME ,FILE_NAME FROM DBA_DATA_FILES;
ALTER TABLESPACE OLD_NAME TO NEW_NAME;
--修改后，旧表空间存放的对象被自动的更名为新表空间
--2.设置默认表空间
alter database default tablespace myspace;
select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
--3.删除表空间
--语法
-----------------------------------------
DROP TABLESPACE TABLESPACE_NAME   --表空间名称
[INCLUDING CONTENTS]              --删除表空间以及里面的数据 
[CASCADE CONSTRAINS]              --删除相关的完整性约束（主键，索引等）
[color=red]注：如果使用了分区【partition】的表空间，要先使用alter table ...remove partition将表空间的数据移到别的表空间去，然后才能drop[/color]
drop tablespace my_tabs including contents cascade constraints;
--4.新增数据文件到表空间
alter tablespace my_tabs add datafile '/OS_PATH/xxx.dbf' size 300MB;
--5.删除表空间中无数据的数据文件
--在11gR2后才能删除数据文件
alter tablespace my_tabs drop datafile '/OS_PATH/xxx.dbf' ;
--6.在数据文件中设置自动扩展属性
ALTER DATABASE/TABLESPACE [FILE_PATH]   --file_path表示路径+文件名
AUTOEXTEND ON NEXT[INCREMENT_SIZE]      --自动扩展的大小
MAXSIZE[MAX_SIZE/UNLIMITED]             --最大值或无限大小UNLIMITED
--查询数据文件，并改为自动扩展
SQL> select file_name ,tablespace_name,bytes/1024/1024 M,autoextensible from dba_data_files order by tablespace_name;
FILE_NAME                                  TABLESPACE          M AUT
------------------------------------------ ---------- ---------- ---
D:\ORACLE\ORADATA\C_DB\CWMLITE01.DBF       CWMLITE            20 YES
D:\ORACLE\ORADATA\C_DB\DRSYS01.DBF         DRSYS              20 YES
D:\ORACLE\ORADATA\C_DB\EXAMPLE01.DBF       EXAMPLE       149.375 YES
D:\ORACLE\ORADATA\C_DB\INDX01.DBF          INDX               25 YES
D:\ORACLE\ORADATA\C_DB\ODM01.DBF           ODM                20 YES
D:\ORACLE\ORADATA\C_DB\SYSTEM01.DBF        SYSTEM            400 YES
D:\ORACLE\ORADATA\C_DB\TOOLS01.DBF         TOOLS              10 YES
D:\ORACLE\ORADATA\C_DB\UNDOTBS01.DBF       UNDOTBS1          200 YES
D:\ORACLE\ORADATA\C_DB\USERS01.DBF         USERS              25 YES
D:\ORACLE\ORADATA\C_DB\my_tabs.DBF         MY_TABS        38.125 NO
alter database datafile 'D:\ORACLE\ORADATA\C_DB\my_tabs.DBF'
autoextend on next 200M maxsize 5000M;