靓靓小知识-Oracle数据库新建索引，重建索引和大表，收缩表，降低表空间使用率和磁盘使用空间

Oracle数据库新建索引，重建索引和大表，收缩表，降低表空间使用率和磁盘使用空间

1. Oracle建索引

--对于新表，直接新建即可
create  index 客户报价表_IX1 on TAB_客户报价表 (客户编号) tablespace E3_INDX; 

--对于已经有数据的表，尤其是大表，选择在线并行，新建索引
-- Create/Recreate indexes 
create  index 客户报价表_IX1 on TAB_客户报价表 (客户编号) online parallel 4 nologging tablespace E3_INDX; --在线建索引
------关闭并行。
alter index 客户报价表_IX1 noparallel;

2. 重建索引

在实际生产环境中，由于磁盘空间是有限的，数据文件不可能无限增加，经常遇到数据库表空间不足，磁盘空间不足的情况，此时一般可以这样处理，通过删除数据，然后通过重建索引的方式来降低表空间使用率。如果没有删除过数据，此方法效果很小！
当然如果不能删除数据，大概率只能增加磁盘！
下面是重建索引的方式：

2.1 先查询数据库中表和索引的大小

--查询当前用户下，表的大小
select sEGMENT_name, bytES / 1024 / 1024 / 1024
  FROM User_Segments
 where segment_type = 'TABLE'
 order by  bytES / 1024 / 1024 / 1024 desc

--查询当前用户下，索引的大小
select sEGMENT_name, bytES / 1024 / 1024 / 1024
  FROM User_Segments
 where segment_type = 'INDEX'
 order by bytES / 1024 / 1024 / 1024 desc

2.2 重建索引查询

----重建索引
select 'alter index '||segment_name ||
       ' rebuild online parallel 4  tablespace e3_indx;'
  from user_segments
 where tablespace_name = 'E3_DATA'
 and segment_type='INDEX'
   and bytes/1024/1024 >1000 
   and bytes/1024/1024 <5000
-- 此sql是将目前在e3_data表空间大于1G小于5G的索引,重建到e3_indx，此方法会降低e3_data使用率，增加e3_indx的使用率。
-- 还有其他几种方式，例如将e3_indx表空间的索引重建到e3_indx,此方法需要根据实际情况自行调整。

重建索引前，务必确认剩余空间是否足够，另外如果索引特别大，需要关注undotbs表空间和临时表空间的大小

2.3 实际执行语句

--开启并行重建
alter index 客户报价表_IX1 rebuild online parallel 4  tablespace e3_indx;
--关闭并行
alter index 客户报价表_IX1 noparallel;

2.4 查看索引重建进度，仅供参考

select sid,                 /* Session ID */
       opname as 操作内容,              /*  操作内容   */
       target as 相关表,
       round(sofar/totalwork*100) as 完成百分比, /*+ 完成百分比 */
       elapsed_seconds  as 已执行时间,     /*已经执行的时间*/
       time_remaining as 剩余时间      /* 预计剩余时间 */
from v$session_longops
where time_remaining !=0

3.重建大表

此方法是替换大表，将原表数据导出后，删除原表，新建表的方式，可大幅降低表空间使用率
务必先导出备份，不到万不得已，不使用

alter table E3_LOG rename to E3_LOG_BAK;
alter table E3_LOG1 rename to E3_LOG;
drop  table E3_LOG_BAK;

4. 收缩表

此方法在删除数据后，效果很明显，对于大表，需要关注undotbs表空间和临时表空间的大小

alter table E3_LOG  enable row movement; --启动行移动功能
alter table E3_LOG  shrink space cascade;--收缩表，降低高水位线，并且相关索引也要收缩一下
alter table E3_LOG  disable row movement;--关闭行移动

5.重建索引后，查看磁盘空间是否可调整

此方法是重新调整数据文件的大小，来调整磁盘空间的使用率。请根据实际情况调整！

-- 先查询可调整的数据文件
SELECT
   a.file_id,
   a.file_name
   file_name,
   CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,
   CEIL( blocks * blksize / 1024 / 1024 ) currsize,
   CEIL( blocks * blksize / 1024 / 1024 ) -
   CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,
   'alter database datafile ''' || file_name || ''' resize ' ||
   CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 )  || 'm;' cmd
FROM
   DBA_DATA_FILES a,
   (
      SELECT   file_id, MAX( block_id + blocks - 1 ) hwm
      FROM     DBA_EXTENTS
      GROUP BY file_id
   ) b,
   (
      SELECT  TO_NUMBER( value ) blksize
      FROM    V$PARAMETER
      WHERE   name = 'db_block_size'
   )
WHERE
   a.file_id = b.file_id(+)
AND
   CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0

ORDER BY 5 desc

alter database datafile '/u02/oradata/orcl/e3_data93.dbf' resize 1793m;