bigfile 表空间的缺点

1、resize时会产生行锁
2、备份恢复或者故障恢复慢
3、adg迁移的时候备库也只能是bigfile
4、bigfile有io方面的问题
5、水位线、碎片不好整理
6、迁移的话，只能逻辑迁移，比如导出导入

bigfile迁移至普通表空间的方法

1、逻辑导入导出
2、源库将bigfile表空间的对象move到普通表空间，然后按正常迁移方法迁移。

一、需求

目前环境大部分表空间是bigfile表空间，不方便管理，有如下缺点：
1、resize时会产生行锁
2、备份恢复或者故障恢复慢
3、adg迁移的时候备库也只能是bigfile
4、bigfile有io方面的问题
5、水位线、碎片不好整理
6、迁移的话，只能逻辑迁移，比如导出导入

故，计划将bigfile表空间迁移至普通表空间，目前考虑的方案有如下两个：
1、逻辑导入导出
2、源库将bigfile表空间的对象move到普通表空间，然后按正常迁移方法迁移

下面是具体迁移测试方案

二、方案1：move表空间

1、创建bigfile表空间

CREATE BIGFILE TABLESPACE bftbs datafile size 5G;  
复制

2、向bigfile表空间插入数据

create table bigtable tablespace BFTBS as select * from dba_objects ;  
insert into bigtable select * from bigtable;  
insert into bigtable select * from bigtable;  
insert into bigtable select * from bigtable;  
insert into bigtable select * from bigtable;  
insert into bigtable select * from bigtable;  
insert into bigtable select * from bigtable;  
commit;  
复制

创建索引

create index idx_big on bigtable(object_id) tablespace bftbs ;  
复制

3、创建普通表空间

CREATE TABLESPACE smtbs datafile size 2G;  
复制

4、查询bigfile表空间对象

SELECT TABLESPACE_NAME AS TABLESPACE_NAME  
, SEGMENT_NAME AS SEGMENT_NAME  
, SEGMENT_type AS SEGMENT_type  
, SUM(BYTES)/1024/1024 AS SEGMENT_SIZE  
FROM DBA_SEGMENTS  
WHERE TABLESPACE_NAME='&TABLESPACE_NAME'  
GROUP BY TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_type  
ORDER BY 4  
;  
  
TABLESPACE\_NAME SEGMENT\_NAME SEGMENT\_TYPE SEGMENT\_SIZE  
------------------------------ -------------------- ------------------------------------ ------------  
BFTBS IDX\_BIG INDEX 88  
BFTBS BIGTABLE TABLE 952  
复制

5、检查对象有效性

11:10:14 sys@ xxx>set autotrace traceon  
11:10:21 sys@ xxx>select object\_id from BIGTABLE where object\_id<5;  
  
192 rows selected.  
  
Elapsed: 00:00:00.02  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1152243438  
  
----------------------------------------------------------------------------  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
----------------------------------------------------------------------------  
| 0 | SELECT STATEMENT | | 192 | 2496 | 3 (0)| 00:00:01 |  
|* 1 | INDEX RANGE SCAN| IDX\_BIG | 192 | 2496 | 3 (0)| 00:00:01 |  
----------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
1 - access("OBJECT\_ID"<5)  
  
Note  
-----  
- dynamic statistics used: dynamic sampling (level=2)  
  
  
Statistics  
----------------------------------------------------------  
0 recursive calls  
0 db block gets  
16 consistent gets  
0 physical reads  
0 redo size  
3853 bytes sent via SQL\*Net to client  
542 bytes received via SQL\*Net from client  
14 SQL\*Net roundtrips to/from client  
0 sorts (memory)  
0 sorts (disk)  
192 rows processed  
复制

6、move对象

检查普通表空间大小，move bigfile表空间下表和索引等对象。

alter table bigtable move tablespace SMTBS;  
alter index idx\_big rebuild tablespace SMTBS;  
复制

alter index idx\_big on bigtable move to SMTBS;  
复制

对于索引无法使用move，使用rebuild来重建。

三、方案2：导入导出

1、创建目录

set lines 300  
col DIRECTORY\_NAME for a25  
col DIRECTORY\_PATH for a50  
select \* from dba\_directories;  
  
create or replace directory expdir as '/home/oracle/exp';  
复制

配置pdb的tns

pdb1 =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = 1521))  
(CONNECT\_DATA =  
(SERVER = DEDICATED)  
(SERVICE\_NAME = pdb1)  
)  
)  
复制

2、导出表空间

expdp system/oracle@xxxxxx:1521/pdb1 directory=expdir dumpfile=bigtest.dmp logfile=bigtest.log tablespaces=BFTBS  
复制

select owner, segment_name, segment_type, tablespace_name  
from dba_segments  
where tablespace_name in ('BFTBS', 'SMTBS');  
复制

3、导入普通表空间

alter tablespace BFTBS read only; --避免导入对象重复  
复制

或者可以删除表空间

impdp system/oracle@xxxxxx:1521/pdb1 directory=expdir dumpfile=bigtest.dmp logfile=bigtest.log remap\_tablespace=BFTBS:SMTBS  
复制

4、验证导入的数据

select owner, segment_name, segment_type, tablespace_name  
from dba_segments  
where tablespace_name in ('BFTBS', 'SMTBS');  
  
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  
-------------------- -------------------- ------------------------------------ ------------------------------  
LWB BIGTABLE TABLE SMTBS  
LWB IDX\_BIG INDEX SMTBS  
复制

报错处理

ORA-31655: no data or metadata objects selected for job  
Job "SYSTEM"."SYS\_EXPORT\_TABLESPACE\_01" completed with 1 error(s) at Tue Jul 20 14:20:24 2022 elapsed 0 00:00:09  
复制

解决：
将表和索引创建在普通用户下，不用再sys或者system用户下。