oracle数据库大数据量表的复制备份以及表空间的清理

一、问题描述

1. Oracle数据库retina用户表空间利用率频繁出现告警通知，利用率当前值已经不足10%。

二、解决思录

1. 通过SQL语句检查表空间是否溢出

--查询表空间是否溢出
SELECT DBF.TABLESPACE_NAME "表空间",
       DBF.TOTALSPACE "总量(M)",
       DBF.TOTALBLOCKS "总块数",
       DFS.FREESPACE "剩余总量(M)",
       DFS.FREEBLOCKS "剩余块数",
       (DFS.FREESPACE / DBF.TOTALSPACE) * 100 "空闲比例"
  FROM (SELECT T.TABLESPACE_NAME,
               SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
               SUM(T.BLOCKS) TOTALBLOCKS
          FROM DBA_DATA_FILES T
         GROUP BY T.TABLESPACE_NAME) DBF,
       (SELECT TT.TABLESPACE_NAME,
               SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
               SUM(TT.BLOCKS) FREEBLOCKS
          FROM DBA_FREE_SPACE TT
         GROUP BY TT.TABLESPACE_NAME) DFS
 WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME);

我们可以发现retina用户表空间利用率确实已经不足10%，如下图所示：

2. 通过SQL语句检查表占用空间情况：

 SELECT SEGMENT_NAME "表名",
         BYTES / (1024 * 1024) "表大小(M)",
         OWNER "用户"
          FROM DBA_SEGMENTS
         WHERE SEGMENT_TYPE = 'TABLE'
           AND OWNER = 'RETINA'
         ORDER BY BYTES DESC;

我们可以发现在retina表空间中占用存储最多的就是第一张表。统计了一下，该表占用将近50多个G、数据量为159948665(亿级别)。
思路：所以我们现在可以分为两步走：先进行转移备份、避免数据的丢失。然后在清理该表。

3. Oracle大数据量级表备份

（1）现在Oracle数据库有两个用户。一个是我们的retina(表空间：RETINA)，一个是即将将表迁入的gis(GIS_DEFAULT)。
（2）具体备份方法，如下所示：

create table gis.WRP_PM_5G_CEL_H_CEL nologging parallel (DEGREE 16) AS SELECT * FROM retina.WRP_PM_5G_CEL_H_CEL;//16个并行

（3）备份主要目的为日后若有需要该表的所有数据，在原来的retina用户下进行表数据的恢复工作。

4. 删除retina用户下的WRP_PM_5G_CEL_H_CEL表(大数据量建议使用drop语句)

drop table WRP_PM_5G_CEL_H_CEL;

注意：
在进行drop语句操作的时候，突然报以下错误：


经过百度查询，原来是数据库表被锁死：

（1）执行该条SQL语句完成sid和serial#值的查询：

SELECT s.sid,
    s.serial#,
    v.*,
    ao.*
 FROM v$locked_object v,
    all_objects   ao,
    v$session    s
 WHERE v.object_id = ao.object_id
  AND s.sid = v.session_id;

显示结果如图所示：(在这里我发现一个locked_mode字段，我查询了一下原来是Oracle锁的模式字段)
说明：
ORACLE里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

（2）如有记录则表示lock，记录下SID和serial#，将记录下的SID和serial#替换下面的7905，15154，即可解除lock。

ALTER SYSTEM KILL SESSION '7905,15154';

（3）解除锁后在进行原先的drop删除表操作即可。

5. 将gis用户下的备份表进行查询出最近一个月的数据，然后迁回到retina用户下。

create table retina.WRP_PM_5G_CEL_H_CEL nologging parallel (DEGREE 16) AS SELECT * FROM gis.WRP_PM_5G_CEL_H_CEL where day_id > 20200323;

注意：一定要进行两边数据量的统计，避免数据的丢失。

select count(*) from retina.WRP_PM_5G_CEL_H_CEL;
select count(*) from gis.WRP_PM_5G_CEL_H_CEL;

若迁移后的数据与原先的数据量一样，就成功完成数据的迁移了。