Oracle表空间相关命令

    从08年的压力测试开始，零星整理了很多Oracle的命令，东一坨西一坨的也不知道放到了那里，懒惰之人必有可恨之处啊。


-- 查询表空间的使用率
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY F.TABLESPACE_NAME;


-- 查看其他表空间的数据文件
select a.TABLESPACE_NAME "表空间名",
       a.FILE_NAME "物理文件",
       a.BYTES / 1024 / 1024 "表空间大小(M)",
       a.AUTOEXTENSIBLE "自动扩展"
  from DBA_DATA_FILES a
 order by a.TABLESPACE_NAME;
-- 创建表空间
create tablespace CRM_APP logging datafile 'd:\oradata\crm_app_1.dbf' size 1024m autoextend on next 50m maxsize 5120m extent management local; 
-- 给表空间添加数据文件
alter tablespace CRM_APP add datafile 'd:\oradata\crm_app_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整表空间数据文件大小
-- ps:调整的值不能小于实际使用值，否则会报【ORA-03297: 文件包含在请求的RESIZE 值以外使用的数据……】的错误
alter database datafile 'd:\oradata\crm_app_2.dbf' resize 2048M;


-- 查看临时表空间的数据文件
select tablespace_name "表空间名",
       file_name "物理文件",
       bytes / 1024 / 1024 "表空间大小(M)",
       autoextensible "自动扩展"
  from dba_temp_files
 order by tablespace_name;
-- 创建临时表空间
create temporary tablespace CRM_TEMP tempfile 'D:\oradata\crm_temp_0.dbf' size 1024m autoextend on next 50m maxsize 2048m extent management local;  
-- 给临时表空间添加数据文件
alter tablespace CRM_TEMP add tempfile 'D:\oradata\crm_temp_1.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整临时表空间数据文件大小
alter database tempfile 'D:\oradata\crm_temp_1.dbf' resize 2048M;


【缩小临时表空间的方法】
-- 1.给临时表空间增加一个数据文件，大小自己定义【命令见上面！】
-- 2.将之前的临时表空间数据文件脱机
alter database tempfile 'D:\oradata\crm_temp_0.dbf' offline;
-- 3.删除脱机的数据文件，达到缩小临时表空间的效果
alter database tempfile 'D:\oradata\crm_temp_0.dbf' drop including datafiles;


【UNDO表空间爆满的处理方法】
-- 1.创建新的UNDO表空间
create undo tablespace undotbs2 datafile 'C:\app\Administrator\oradata\orcl\undo2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m; 
-- 2.给UNDO表空间添加数据文件
alter tablespace undotbs2 add datafile 'C:\app\Administrator\oradata\orcl\undo2_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 3.设置Oracle的默认UNDO表空间为新建的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both; 
-- 4.做一个备份文件【可略过】
create pfile from spfile;
-- 5.删除原有的UNDO表空间及数据文件
drop tablespace undotbs1 including contents; -- 只删除表空间
drop tablespace undotbs1 including contents and datafiles; -- 删除表空间及数据文件
-- 6.删除物理文件【如果数据文件没有删除的话，手动做一下】




-- 查看表占用的物理空间
select segment_name "表名", bytes / 1024 / 1024 "大小(M)"
  from dba_segments
 where segment_type = 'TABLE'
   and owner = 'CRMT'
 order by segment_name;


-- 查看索引占用的物理空间【索引有时候占用的物理空间比数据表还大，第一次做数据库规划的时候差点被害死】
select segment_name "索引名", bytes / 1024 / 1024 "大小(M)"
  from dba_segments a
 where segment_type = 'INDEX'
   and owner = 'CRMT'
 order by segment_name;


-- 查看数据文件的使用率
select a.name "物理路径",
       a.bytes / 1024 / 1024 "文件大小(M)",
       ceil(HWM * a.block_size) / 1024 / 1024 "已使用(M)",
       (a.bytes - HWM * a.block_size) / 1024 / 1024 "可释放(M)"
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) HWM
          from dba_extents
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * block_size) > 0
 order by a.name;


    一个用户最好使用独立的用户表空间和用户临时表空间，索引需要部署在独立的表空间上，数据表也可以根据用途划分部署在不同的表空间。 


    windows环境下的数据库不建议创建太大的表空间数据文件（10G），反正多建几个数据文件也不是什么麻烦事。