Oracle 数据库表空间运维

-- https://blog.csdn.net/hanjianahanjian/article/details/83871567
-- 1、block：是Oracle中存储数据块的最小单位，所以数据最终都是存储在block中。它也被称为逻辑blocks或是页（pages）。每个操作系统都有自己的block size。而这里的block是Oracle自己的，不同2于2、extent：OS的blocks。可以通过设置DB_BLOCK_SIZE设置Oracle的block为OS的block的几倍，从而减少不必要的I/O。不管block中存放的数据是表、索引还是cluster data，block的结构都是一致。
-- extent 是每次分配给一个对象的逻辑最小单位，是由一定数量连续的block组成。一个或多个extent又组成了一个segment。
-- 3、segment：它是extents的集合，它包含了在表空间中所包含的具体逻辑存储机构的所有extents。

-- 查看表空间和对应物理文件名
select b.file_id 物理文件号,
       b.file_name 物理文件名,
       b.tablespace_name 表空间,
       b.bytes / 1024 / 1024 大小M,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 and 利用率 < 50%
 group by b.tablespace_name, b.file_id, b.file_name, b.bytes
 order by b.tablespace_name;
-- 152,153,154,155,156,157,158,159,160,161,162,163 物理文件名/datadb/ZHFCDB/default_tbs**，物理I文件号 152,153,154,155,156,157,158,159,160,161,162,163 几乎未达使用

-- 确认下表空间对应的数据文件是否有在
SELECT *
  FROM dba_extents
 WHERE TABLESPACE_NAME = 'DEFAULT_TBS'
   and extend_id in
       (152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163); -- 为空集，说明数据文件数据块还未写入数据，可以直接删除

-- 数据清理与清空回收站
-- 清除用户回收站
purge recyclebin;
-- 清除全库回收站
purge dba_recyclebin;

-- 整理表空间
alter tablespace tablespace_name coalesce;

-- 清空表数据
truncate table table_name;
-- 释放表空间
alter table table_name deallocate UNUSED KEEP 0; 
-- 注：（1）先清空表数据，此时表依旧存在，Truncate不支持回滚，并且不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除；（2）注意如果不加KEEP 0的话，表空间是不会释放的。

-- 迁移表数据。在出现以下错误时，我们不仅可以通过整理表空间或者清楚回收站进行解决，还可以通过将待释放表空间的表数据全部迁移到其他表空间，将空间释放后再迁移回原表空间。（此次操作无须涉及）
-- 需移动的表数据
select DISTINCT 'alter table '|| owner||'.'||segment_name || ' move tablespace user_test;' from dba_extents where segment_type='TABLE' and file_id=4;
-- 需移动的索引数据
select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild tablespace user_test;' from dba_extents where segment_type='INDEX' and file_id=4;
-- 需移动的分区表数据
select DISTINCT 'alter table '|| owner||'.'|| segment_name || ' move partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='TABLE PARTITION' and file_id=4;
-- 需移动的分区表索引数据
select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='INDEX PARTITION' and file_id=4;
-- 上述代码中file_id可以通过查看表空间进行比对更改，上述代码执行后可以获得对应的自动生成的SQL代码，此时运行对应的SQL代码即可迁移表数据

-- 生成RESIZE代码 （无须操作）
select a.file#,
       a.name,
       a.bytes / 1024 / 1024 CurrentMB,
       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) HWM
          from dba_extents
         where file_id in (select b.file#
                             From v$tablespace a, v$datafile b
                            where a.ts# = b.ts#
                              and a.name = 'tablespace_name')
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * block_size) > 0
 order by 5;


-- 删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
-- 删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
-- 删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
-- 删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

-- 查看表空间数据表，数据块，数据段的使用信息
SELECT * FROM dba_tables a WHERE a.TABLESPACE_NAME = 'DEFAULT_TBS';
SELECT * FROM dba_extents WHERE TABLESPACE_NAME = 'DEFAULT_TBS';
SELECT * FROM dba_segments WHERE TABLESPACE_NAME = 'DEFAULT_TBS';