Oracle表空间的创建与监控（持续更新）

表空间的组成

表空间和段是逻辑上的概念，一个表空间(tablespace)由一个或者多个段(segment)组成；而表空间所对应的物理对象是数据文件(datafile)。oracle中，段和表空间可以自动增长，但是不能自动收缩；要想收缩必须要手动完成。

系统自带表空间说明

SYSTEM      #系统表空间，是永久系统表空间，用于存储SYS用户的表、视图、存储过程对象。
UNDOTBS1    #存储撤销信息的undo表空间
SYSAUX      #辅助表空间
TEMP        #临时表空间，用户存储SQL语句处理的表示索引信息
USERS       #永久表空间，存储数据库用户创建的数据库对象

创建表空间并且指定给用户使用

--创建表空间
create tablespace SDA DATAFILE 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10M;
--修改
alter database datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' autoextend on;
--创建用户
create user c##SDA identified by SDA default tablespace SDA;
--修改权限
grant dba to c##SDA;
grant connect to c##SDA;
grant resource to c##SDA;

--删除用户
--drop user c##SDA cascade;
--删除表空间
--drop tablespace SDA including contents and datafiles;

查看表空间的使用情况

select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc
--考虑到自增长
SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
       ROUND(D.AVAILB_BYTES, 2) AS "表空间大小(G)",
       ROUND(D.MAX_BYTES, 2) AS "最终表空间大小(G)",
       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空间(G)",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99') AS "使用比",
       ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",
       F.MAX_BYTES AS "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /
                     (1024 * 1024 * 1024),
                     6) MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;
--简单sql
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
       ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "TOTAL(G)",
       ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "USED(G)",
       ROUND(C.BYTES / (1024 * 1024 * 1024), 2) AS "FREE(G)",
       ROUND((B.BYTES * 100) / A.BYTES, 2) AS "% USED",
       ROUND((C.BYTES * 100) / A.BYTES, 2) AS "% FREE"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

表空间碎片查看

select a.tablespace_name,
       sqrt(max(a.blocks) / sum(a.blocks)) *
       (100 / sqrt(sqrt(count(a.blocks)))) FSFI
  from dba_free_space a, dba_tablespaces b
 where a.tablespace_name = b.tablespace_name
   and b.contents not in ('TEMPORARY', 'UNDO')
 group by a.tablespace_name
 order by 2;

数字越小，表空间碎片较多，当小于30%的时候说明碎片程度很可观了。

查看表空间对应数据文件的相关信息

select file_name,
       tablespace_name,
       bytes / 1024 / 1024 "bytes MB",
       maxbytes / 1024 / 1024 "maxbytes MB",
       autoextensible,
       increment_by
  from dba_data_files
 where tablespace_name = 'SDA';

查询创建表空间的相关SQL

select dbms_metadata.get_ddl('TABLESPACE', 'SDA') from dual;

返回一个clob字段，参考信息如下：

  CREATE TABLESPACE "SDA" DATAFILE 
  'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10485760
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE 
  'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' RESIZE 17571184640

在磁盘空间充足的情况下增加数据文件

--增加数据文件
alter tablespace SDA add 
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
size 10M;
--增加数据文件，并且自增长
alter tablespace SDA add 
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
size 10M autoextend on next 5M maxsize 1G;
--使原来的数据文件自增长
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
autoextend on next 5M maxsize 1G;
--调整原来数据文件的大小
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
RESIZE 1G;

注意：ORACLE支持的数据文件大小是由它的db_block_size和db_block的数量决定的。
     其中db_block(ORACLE块)的数量是一个定值2**22-1(4194303).
     数据文件大小容量=块数量*块大小

验证是否正确增加

select file_name,
       tablespace_name,
       bytes / 1024 / 1024 "bytes MB",
       maxbytes / 1024 / 1024 "maxbytes MB",
       autoextensible,
       increment_by
  from dba_data_files
 where tablespace_name = 'SDA';

删除表空间的数据文件

alter tablespace SDA drop datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 

处理临时表空间和undo表空间

--（1）创建一个新的小空间的undo tablespace  
create undo tablespace undotBS2 datafile 'D:\oracle\oradata\orcl\UNDOTBS02.DBF' size 5m;  
--修改
alter database datafile 'D:\oracle\oradata\orcl\UNDOTBS02.DBF' autoextend on;
--（2）设置新的表空间为系统undo_tablespace  
alter system set undo_tablespace=undotBS2;  
--（3）删除旧的表空间  
drop tablespace undotbs1 including contents; 
--(4)修改临时表空间文件的大小
alter DATABASE tempfile 'D:\oracle\oradata\orcl\TEMP01.DBF' RESIZE 10M;

缩小表空间

在表空间没有把自动扩展的区域使用完成之前可以使用resize完成缩小操作

alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
RESIZE 1G;

但是当空间使用充分时，需要先完成段的碎片整理，完成段的未使用区域的回收才可以在使用resize来缩小空间。

--压缩碎片
alter table ttt shrink space;        
--收回段中崭新的没有使用的部分（也就是高水位以上的部分）
alter table ttt deallocate unused;  
--调整大小
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
RESIZE 1G;

表碎片相关语句

--打开行移动
alter table t1 enable row movement;
--压缩表及相关数据段并下调HWM
alter table t1 shrink space cascade;
--只压缩不下调HWM
alter table t1 shrink space compact;
--下调HWM
alter table t1 shrink space ;
--关闭行移动
alter table t1 disable row movement;

数据文件迁移

• 使用sqlplus登陆：sqlplus sys/passwd as sysdba;
• 关闭数据库:shutdown immediate；
• 移动数据文件
• 开启数据库挂载：startup mount;
• 修改数据文件名称：alter database rename file ‘*’ to ‘*‘;
• 恢复数据文件:recover datafile ‘**‘;
• 开启数据库：alter database open;

windows

linux

参考资料

ORACLE表空间和表碎片分析及整理方法