迁移案例11—收缩数据库—关于expdp带复杂条件的数据迁移与清理

2024年4月13日 1315点热度 0人点赞 0条评论

零、操作步骤
一、需求
二、信息查看
三、需要执行的sql语句
- 1、sql语句
- 2、单独查询：
三、expdp实现测试
四、利用上述测试，进行如下配置，二步实现数据的导出
五、备份（原来的备份存在即可）
六、导入数据
- 注：不需要创建新表，直接impdp导入生产即可，可以自动根据源表创建约束
- 1、导入数据到新表
八、导入后操作
- 5、创建新表索引
九、收缩表空间前查看并分析
十、收缩表空间方法
- 1、方法1-导入导出（推荐-快）
- 2、方法2-迁移表空间
十一、业务校验

先上操作步骤，后面见迁移流程。

零、操作步骤

1、备份表1—导出需要的老数据-query02.par

（1）配置parfile文件

cd /backup

vi query02.par

dumpfile=query02.dmp
logfile=query02.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Param_Code in (select sp.param_code from sys_param_group spg inner join sys_param sp on spg.id = sp.param_group_id and spg.param_group_code = 'BOPA_ROLLCHANGE') and ku$.Param_Code <> 'ROLL_ID' and ku$.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')"

（3）导出数据

数量是：35486133，符合之前导出的数据。

expdp mesprd/123 parfile=query02.par directory=backup_mes

2、维护数据库

（1）关闭监听

lsnrtctl stop

（2）重启数据库

shutdown immediate;
startup mount;
alter database open;

（3）查看数据库SCN

select current_scn from v$database;

（4）更改归档位置（原归档位置）

alter system set db_recovery_file_dest='/backup/archivelog';
alter system set db_recovery_file_dest_size=500G;

3、备份表2—导出最新表

（1）配置parfile文件

cd /backup

vi query03.par

dumpfile=query03.dmp
logfile=query03.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')"

（2）导出数据

数据量：3387230,符合上述测试语句的数据量

expdp mesprd/123 parfile=query03.par

4、备份表3—备份用户下数据1（全表）

（1）查看表空间和用户对应

SELECT DISTINCT(OWNER),tablespace_name FROM DBA_SEGMENTS;

（2）分析

经上述查看，表空间MESPRD_TBSP下只有一个mesprd用户，一个MESPRD用户下的数据只在一个表空间MESPRD_TBSP下，所以expdp导出数据时，使用用户mesprd或表空间mesprd_tbsp备份都可以，所以下述都使用shemas(mesprd)进行数据导出。

（3）导出数据

expdp mesprd/123 dumpfile=mesprd_all_%U.dmp logfile=mesprd_all.log directory=backup_mes schemas=mesprd compression=all parallel=4

5、导入数据到新表

（1）导入2024年前数据到新表

impdp mesprd/123 dumpfile=query02.dmp logfile=query02_imp.log directory=backup_mes remap_table=sfc_brookner_param_log:sfc_brookner_param_log_new table_exists_action=append

（2）导入2024年后数据到新表

导入过程会有大量归档产生，注意观察归档目录。

impdp mesprd/123 dumpfile=query03.dmp logfile=query03_imp.log directory=backup_mes remap_table=sfc_brookner_param_log:sfc_brookner_param_log_new table_exists_action=append

（3）注意查看归档情况

导入过程会有大量归档产生。

set line 300
select * from v$flash_recovery_area_usage;

6、对表进行操作

（1）删除原表：

drop table sfc_brookner_param_log purge;

（2）更改新表为原表名

alter table sfc_brookner_param_log_new rename to sfc_brookner_param_log;

（3）更新统计信息并创建索引

begin
   dbms_stats.gather_table_stats(ownname => 'MESPRD',tabname => 'SFC_BROOKNER_PARAM_LOG');
end;
/

（4）查看表信息

select segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where owner='MESPRD' and segment_name = 'SFC_BROOKNER_PARAM_LOG' order by bytes desc;

（5）新表创建索引

--170s
CREATE INDEX "MESPRD"."IDX_SFC_BROOKNER_PARAM_LOG" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("ENTERPRISE_ID", "ORG_ID", "PARAM_CODE", "ACTUAL_VALUE", "BRUCKNER_CODE", "WORKCENTER_CODE"); 
--43s
CREATE INDEX "MESPRD"."IDX_DATATIME_CREATED" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("DATETIME_CREATED");  
--180s
CREATE INDEX "MESPRD"."SFC_BROOKNER_PARAM_LOG_IX2" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("PARAM_CODE", "BRUCKNER_CODE");

7、备份表4—全表数据（删减表数据后）

expdp mesprd/123 directory=backup_mes dumpfile=tablespace_mesprd_shrink_%U.dmp logfile=tablespace_mesprd_shrink.log schemas=mesprd parallel=4

8、删除原表空间mesprd_tbsp并删除用户mesprd

drop tablespace mesprd_tbsp including contents and datafiles;
drop user mesprd;

9、创建新表空间mesprd_tbsp

（1）创建表空间

create tablespace mesprd_tbsp datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD01.dbf' size 10g autoextend on;
alter  tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD02.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD03.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD04.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD05.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD06.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD07.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD08.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD09.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD10.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD11.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD12.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD13.dbf' size 10g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD14.dbf' size 10g autoextend on;

（2）创建用户

create user mesprd identified by 123 default tablespace mesprd_tbsp;

grant dba to mesprd;

10、导入删减数据到数据库

导入过程会有大量归档产生。

impdp mesprd/123 directory=backup_mes dumpfile=tablespace_mesprd_shrink_%U.dmp logfile=tablespace_mesprd_shrink_imp.log schemas=mesprd parallel=4

11、关于函数、存储过程

导入过程，比如用户mesprd没有删除，仍然使用原来的，可能会有些问题，需要重新编译函数，存储过程等。

12、导入后查看

（1）文件系统空间

（2）log日志

------------------------------------------------------------------------------------实现流程----------------------------------------------------------------------------

一、需求

子公司（KS），数据库某个表的数据量已经达到了将近8亿，需要将2024年之前的部分数据（3000多万）保留，并保留2024年之后的所有数据。

二、信息查看

1、表空间

select * from dba_data_files;

2、查看数据表占用量

select segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where owner='MESPRD' order by bytes desc;

3、数据行查看

select table_name,num_rows from dba_tables where owner='MESPRD' order by num_rows desc;

三、需要执行的sql语句

1、sql语句

select *
  from sfc_brookner_param_log A
 where A.Bruckner_Code like '0%'
   and A.Param_Code in
       (select sp.param_code
          from sys_param_group spg
         inner join sys_param sp
            on spg.id = sp.param_group_id
           and spg.param_group_code = 'BOPA_ROLLCHANGE')
   and A.Param_Code <> 'ROLL_ID'
   and A.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')
union
select *
  from sfc_brookner_param_log A
 where A.Bruckner_Code like '0%'
   and A.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')

2、单独查询：

（1）第1个SQL：内层SQL

数据量：96条

select count(*) from sys_param_group spg inner join sys_param sp on spg.id = sp.param_group_id and spg.param_group_code = 'BOPA_ROLLCHANGE'

select sp.param_code  from sys_param_group spg inner join sys_param sp on spg.id = sp.param_group_id and spg.param_group_code = 'BOPA_ROLLCHANGE'

（2）第2个SQL:中层SQL

数据量：35486133

select count(*) from sfc_brookner_param_log A
where A.Bruckner_Code like '0%' and A.Param_Code in
(select sp.param_code
from sys_param_group spg
inner join sys_param sp
on spg.id = sp.param_group_id
and spg.param_group_code = 'BOPA_ROLLCHANGE')
and A.Param_Code <> 'ROLL_ID'
and A.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')

（3）第3个SQL：最后unionSQL

数据量:3387230

select count(*)
from sfc_brookner_param_log A
where A.Bruckner_Code like '0%'
and A.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')

三、expdp实现测试

1、第1个SQL导出实现

（1）导出语句

 select *
from sfc_brookner_param_log A
where A.Bruckner_Code like '0%'
and A.Param_Code in
(select sp.param_code
from sys_param_group spg
inner join sys_param sp
on spg.id = sp.param_group_id
and spg.param_group_code = 'BOPA_ROLLCHANGE')
and A.Param_Code <> 'ROLL_ID'
and A.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')

（2）配置parfile文件

vi query01.par

dumpfile=query01.dmp
logfile=query01.log
DIRECTORY=backup_mes
tables=sys_param
QUERY=sys_param:"where exists(select ku$.param_code from sys_param_group spg where spg.param_group_code = 'BOPA_ROLLCHANGE' and spg.id = ku$.param_group_id )"

（3）导出数据

对比查看是96行，和上述查询数据一致，导出有效。

2、第2个SQL导出实现

（1）导出语句

select *
from sfc_brookner_param_log A
where A.Bruckner_Code like '0%'
and A.Param_Code in
(select sp.param_code
from sys_param_group spg
inner join sys_param sp
on spg.id = sp.param_group_id
and spg.param_group_code = 'BOPA_ROLLCHANGE')
and A.Param_Code <> 'ROLL_ID'
and A.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')

（2）根据导出语句，配置parfile文件

vi query02.par

dumpfile=query02.dmp
logfile=query02.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Param_Code in (select sp.param_code from sys_param_group spg inner join sys_param sp on spg.id = sp.param_group_id and spg.param_group_code = 'BOPA_ROLLCHANGE') and ku$.Param_Code <> 'ROLL_ID' and ku$.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')"

（3）导出数据

数量是：35486133，符合之前导出的数据

[oracle@kssy244:/oracle]$expdp mesprd/123 parfile=query02.par

3、第3个SQL导出实现

（1）导出语句

select * from sfc_brookner_param_log A  where A.Bruckner_Code like '0%' and A.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')

（2）根据导出语句，配置parfile文件

vi query03.par

dumpfile=query03.dmp
logfile=query03.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')"

（3）导出数据

数据量：3387230,符合上述测试语句的数据量

[oracle@kssy244:/oracle]$expdp mesprd/123 parfile=query03.par

四、利用上述测试，进行如下配置，二步实现数据的导出

1、历史数据导出

（1）配置parfile文件

vi query02.par

dumpfile=query02.dmp
logfile=query02.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Param_Code in (select sp.param_code from sys_param_group spg inner join sys_param sp on spg.id = sp.param_group_id and spg.param_group_code = 'BOPA_ROLLCHANGE') and ku$.Param_Code <> 'ROLL_ID' and ku$.DATETIME_CREATED < to_date('2024-1-1', 'yyyy-MM-dd')"

（2）导出数据

数量是：35486133，符合之前导出的数据

[oracle@kssy244:/oracle]$expdp mesprd/123 parfile=query02.par

2、停止数据库并启动到只读状态

shutdown immediate;
startup mount;
alter database open read only;

3、最新数据导出（需要停库）

（1）配置parfile文件

vi query03.par

dumpfile=query03.dmp
logfile=query03.log
DIRECTORY=backup_mes
tables=sfc_brookner_param_log
QUERY=sfc_brookner_param_log:"where ku$.Bruckner_Code like '0%' and ku$.Datetime_Created >= to_date('2024-1-1', 'yyyy-MM-dd')"

（2）导出数据

数据量：3387230,符合上述测试语句的数据量

[oracle@kssy244:/oracle]$expdp mesprd/123 parfile=query03.par

五、备份（原来的备份存在即可）

rman+expdp备份+冷备

六、导入数据

注：不需要创建新表，直接impdp导入生产即可，可以自动根据源表创建约束

1、导入数据到新表

（1）导入2024年前数据到新表

impdp mesprd/123 dumpfile=query02.dmp logfile=query02_imp.log directory=backup_mes remap_table=sfc_brookner_param_log:sfc_brookner_param_log_new table_exists_action=append

（2）导入2024年最新数据

impdp mesprd/123 dumpfile=query03.dmp logfile=query03_imp.log directory=backup_mes remap_table=sfc_brookner_param_log:sfc_brookner_param_log_new table_exists_action=append

（3）注意查看归档情况

导入过程会有大量归档产生。

set line 300
select * from v$flash_recovery_area_usage;

八、导入后操作

1、获取原表SFC_BROOKNER_PARAM_LOG建表信息

（1）执行语句

set long 10000;
set pagesize 10000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'SFC_BROOKNER_PARAM_LOG') from dual;

（2）建表信息

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'SFC_BROOKNER_PARAM_LOG') from dual;

DBMS_METADATA.GET_DDL('TABLE','SFC_BROOKNER_PARAM_LOG')
--------------------------------------------------------------------------------

CREATE TABLE "MESPRD"."SFC_BROOKNER_PARAM_LOG"
( "ID" VARCHAR2(32) DEFAULT sys_guid() NOT NULL ENABLE,
"DATETIME_CREATED" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"USER_CREATED" VARCHAR2(80) DEFAULT 'SYS' NOT NULL ENABLE,
"DATETIME_MODIFIED" DATE,
"USER_MODIFIED" VARCHAR2(80),
"STATE" CHAR(1) DEFAULT 'A' NOT NULL ENABLE,
"ENTERPRISE_ID" VARCHAR2(32) DEFAULT '*' NOT NULL ENABLE,
"ORG_ID" VARCHAR2(32) NOT NULL ENABLE,
"WORKCENTER_CODE" VARCHAR2(80) NOT NULL ENABLE,
"EQUIPMENT_CODE" VARCHAR2(80) NOT NULL ENABLE,
"PARAM_CODE" VARCHAR2(80) NOT NULL ENABLE,
"PARAM_NAME" VARCHAR2(240) NOT NULL ENABLE,
"PARAM_POINT" VARCHAR2(240) NOT NULL ENABLE,
"ACTUAL_VALUE" VARCHAR2(2000),
"INCIDENT_DESC" VARCHAR2(2000),
"BRUCKNER_CODE" VARCHAR2(80) NOT NULL ENABLE,
"IS_RECOM" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MESPRD_TBSP" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MESPRD_TBSP"

（3）查看索引信息

select index_name from user_indexes where table_name='SFC_BROOKNER_PARAM_LOG_BAK';

INDEX_NAME
------------------------------
IDX_SFC_BROOKNER_PARAM_LOG
SYS_C0073069                 --主键索引，导入表时已经有了，无需再建。
IDX_DATATIME_CREATED
SFC_BROOKNER_PARAM_LOG_IX2

（4）获取创建索引信息

SELECT dbms_metadata.get_ddl('INDEX', 'IDX_SFC_BROOKNER_PARAM_LOG') FROM dual;
SELECT dbms_metadata.get_ddl('INDEX', 'IDX_DATATIME_CREATED') FROM dual;
SELECT dbms_metadata.get_ddl('INDEX', 'SFC_BROOKNER_PARAM_LOG_IX2') FROM dual;

（5）获取到的索引信息（后续需要使用）

CREATE INDEX "MESPRD"."IDX_SFC_BROOKNER_PARAM_LOG" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("ENTERPRISE_ID", "ORG_ID", "PARAM_CODE", "ACTUAL_VALUE", "BRUCKNER_CODE", "WORKCENTER_CODE");
CREATE INDEX "MESPRD"."IDX_DATATIME_CREATED" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("DATETIME_CREATED");
CREATE INDEX "MESPRD"."SFC_BROOKNER_PARAM_LOG_IX2" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("PARAM_CODE", BRUCKNER_CODE");

2、表名更改

（1）原表名sfc_brookner_param_log更改为sfc_brookner_param_log_bak

alter table sfc_brookner_param_log rename to sfc_brookner_param_log_bak;

（2）sfc_brookner_param_log_new更改为sfc_brookner_param_log

alter table sfc_brookner_param_log_new rename to sfc_brookner_param_log;

3、更新统计信息并创建索引

begin
   dbms_stats.gather_table_stats(ownname => 'MESPRD',tabname => 'SFC_BROOKNER_PARAM_LOG');
end;
/

4、查看表信息

select segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where owner='MESPRD' and segment_name = 'SFC_BROOKNER_PARAM_LOG' order by bytes desc;

5、创建新表索引

根据1中获取到的创建索引名称执行：

CREATE INDEX "MESPRD"."IDX_SFC_BROOKNER_PARAM_LOG_2" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("ENTERPRISE_ID", "ORG_ID", "PARAM_CODE", "ACTUAL_VALUE", "BRUCKNER_CODE", "WORKCENTER_CODE"); --170s
CREATE INDEX "MESPRD"."IDX_DATATIME_CREATED_2" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("DATETIME_CREATED");  --43s
CREATE INDEX "MESPRD"."SFC_BROOKNER_PARAM_LOG_IX2_2" ON "MESPRD"."SFC_BROOKNER_PARAM_LOG" ("PARAM_CODE", "BRUCKNER_CODE");  --180s

九、收缩表空间前查看并分析

1、运行几天看看

查看数据库是否稳定。

2、删除原表前查看段信息

（1）查看表占用空间

select segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where owner='MESPRD' order by bytes desc;

（2）查看段占用空间

select segment_type,sum(bytes/1024/1024/1024) from dba_segments where owner='MESPRD' group by segment_type order by 2 desc;

（3）表数据查看

select table_name,num_rows from dba_tables where owner='MESPRD' order by num_rows desc;

2、删除原表（索引会自动删除）

（1）删除表

drop table sfc_brookner_param_log_bak purge;

（2）查看空间占用

3、表空间空闲查看

 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

4、收缩表空间前查看

（1）查询表空间可收缩

select a.tablespace_name,
a.file_name,
a.totalsize as totalsize_MB,
b.freesize as freesize_MB,
'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' ||
round((a.totalsize - b.freesize) + 200) || 'M;' as "alter datafile"
from (select a.file_name,
a.file_id,
a.tablespace_name,
a.bytes / 1024 / 1024 as totalsize
from dba_data_files a) a,
(select b.tablespace_name,
b.file_id,
sum(b.bytes / 1024 / 1024) as freesize
from dba_free_space b
group by b.tablespace_name, b.file_id) b
where a.file_id = b.file_id
and b.freesize > 100
and a.tablespace_name in ('MESPRD_TBSP')
order by a.tablespace_name

（2）获取到相关语句

ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/kssyorclprd/MESPRD_01.dbf' RESIZE 27780M;
ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/kssyorclprd/MESPRD_05.dbf' RESIZE 8434M;
ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/kssyorclprd/MESPRD_13.dbf' RESIZE 4512M;

注：因为表空间中个数据文件末尾有很多数据块被占用，所以中间的空闲数据无法被收缩。

十、收缩表空间方法

1、方法1-导入导出（推荐-快）

（1）导出表空间mesprd_tbsp数据

expdp mesprd/123 directory=backup_mes dumpfile=tablespace_mesprd_%U.dmp logfile=tablespace_mesprd.log tablespaces=mesprd_tbsp parallel=4

（2）删除原表空间

SQL> drop tablespace mesprd_tbsp including contents and datafiles;

（3）创建新表空间mesprd_tbsp

create tablespace mesprd_tbsp datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD01.dbf' size 1g autoextend on;
alter  tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD02.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD03.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD04.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD05.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD06.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD07.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD08.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD09.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD10.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp add datafile '/oracle/app/oracle/oradata/kssyorclprd/MESPRD11.dbf' size 1g autoextend on;

（4）导入数据到新表空间

impdp mesprd/123 directory=backup_mes dumpfile=tablespace_mesprd_%U.dmp logfile=tablespace_mesprd_imp.log tablespaces=mesprd_tbsp parallel=4

2小时22小时。

2、方法2-迁移表空间

（1）查看当前表空间占用空间（mesprd_tbsp）

 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

（2）创建新的表空间

根据上述的mesprd_tbsp使用容量，进行创建；

create tablespace mesprd_tbsp_2 datafile '/oracle/oradata/nmesprd001.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd002.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd003.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd004.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd005.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd006.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd007.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd008.dbf' size 1g autoextend on;
alter tablespace mesprd_tbsp_2 add datafile '/oracle/oradata/nmesprd009.dbf' size 1g autoextend on;

（3）获取所有表信息并移动表到新表空间

SELECT distinct (segment_name),
segment_type,
'alter table ' || segment_name ||
' move tablespace "MESPRD_TBSP_2"; '
FROM dba_extents
WHERE tablespace_name = 'MESPRD_TBSP' and segment_type='TABLE' order by segment_name;

（4）获取所有索引信息，并重建索引到新表空间

SELECT distinct (segment_name),
segment_type,
'alter index ' || segment_name ||
' rebuild tablespace "MESPRD_TBSP_2 online parallel 4"; '
FROM dba_extents
WHERE tablespace_name = 'MESPRD_TBSP' and segment_type='INDEX' order by segment_name;

（5）获取所有lob信息，并迁移到新表空间

迁移比较慢，内部包含图片

select 'alter table '||table_name||' move lob('||column_name||') store as (tablespace MESPRD_TBSP_2)  parallel 16;' from dba_lobs 
where segment_name in(select segment_name
from dba_extents
where tablespace_name ='MESPRD_TBSP' and segment_type like 'LOB%');

（6）根据3获取到的信息，整理到move_table.sql中

SQL>@move_table.sql

（7）根据4获取到的信息，整理到move_index.sql中

SQL>@move_index.sql

（8）根据5获取到的信息，整理到move_lob.sql中

SQL>@move_lob.sql

（9）最后查看mesprd_tbsp下的数据

select * from dba_extents where tablespace_name='MESPRD_TBSP';

十一、业务校验

本作品采用知识共享署名 4.0 国际许可协议进行许可