1. 数据库信息
   序 服务器信息 操作系统 IP地址 备注
   1 黑名单管理系统 4.19.90-24.4.v2101.ky10.x86_64 198.2.11.47 目标库
   2 黑名单管理系统 红帽7.3 198.1.9.146 源库
   2 黑名单管理系统 红帽7.3 198.1.9.147 源库
2. 需求
   黑名单需要将全部数据迁移到达梦数据库
3. 迁移方案
   3.1. 源库
   3.1.1. 查询用户基本信息
   set pages 500 lines 500
   column username format a25
   column account_status format a20
   col default_tablespace format a20
   col temporary_tablespace format a20
   col created format a20
   alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
   select username,
   account_status,
   default_tablespace,
   temporary_tablespace,
   created
   from dba_users
   order by 2, 5;

3.1.2. 查看字符集
col PARAMETER for a24
col VALUE for a30
select * from NLS_DATABASE_PARAMETERS;

3.1.3. 查询用户大小
col owner for a20
select owner, count(*),sum(bytes)/1024/1024/1024 as GB from dba_segments where owner in ('LIS') group by owner order by GB desc;

3.1.4. 查询迁移用户是否有外部表
select OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME from dba_external_tables;

3.1.5. 查询用户下表的数量
SQL> select count(*) from dba_tables where owner='LIS';

COUNT(*)

368
3.1.6. 查询用户失效对象
col owner format a20
col object_name format a25
col status format a8
col object_type format a15
select owner,object_name,status,object_type from dba_objects where status='INVALID' and owner='LIS';SQL> SQL> SQL> SQL>

no rows selected
3.1.7. 查询job信息
SQL> select * from dba_jobs where schema_user = 'LIS';

no rows selected
3.2. 迁移
删除测试数据
drop user lis cascade;

drop tablespace DATA_BUSI;
drop tablespace DATA_MNG;
drop tablespace INX_MNG;
drop tablespace INX_BUSI;

目标库创建用户及表空间
1、创建表空间
create tablespace DATA_BUSI datafile '/dmdata/DMP/DAMENG/DATA_BUSI_01.dbf' size 128 autoextend on maxsize 51200 CACHE = NORMAL;
create tablespace DATA_MNG datafile '/dmdata/DMP/DAMENG/DATA_MNG_01.dbf' size 128 autoextend on maxsize 30720 CACHE = NORMAL;
create tablespace INX_MNG datafile '/dmdata/DMP/DAMENG/INX_MNG_01.dbf' size 128 autoextend on maxsize 30720 CACHE = NORMAL;
create tablespace INX_BUSI datafile '/dmdata/DMP/DAMENG/INX_BUSI_01.dbf' size 128 autoextend on maxsize 30720 CACHE = NORMAL;

2、创建用户
create user lis identified by "tsxc!2022"
default tablespace DATA_BUSI
default index tablespace DATA_BUSI;

grant "PUBLIC","RESOURCE","SOI","SVI","VTI" to lis;

利用dts迁移工具迁移

3.3. 目标库
3.3.1. 确认用户下表的数量
select count(*) from dba_tables where owner='LIS';

行号 COUNT(*)

1 372
3.3.2. 确认表空间大小
select
a.name 表空间名字,
SUM(b.max_size) 总大小M,
sum(b.total_sizeb.page_size/1024/1024-b.free_sizeb.page_size/1024/1024) 当前使用大小M,
sum(b.max_size) -sum((b.total_sizeb.page_size/1024/1024-b.free_sizeb.page_size/1024/1024)) 当前剩余空间M,
round((1 -cast((sum(b.total_sizeb.page_size/1024/1024)-sum(b.free_sizeb.page_size/1024/1024)) as DEC)/sum(b.max_size))*100, 2)
||'%' 剩余百分比
from
v$tablespace a,
v$datafile b
where
a.id=b.group_id
group by
name;