MySQL 5.7.27 迁移 DM 8

SELECT COUNT(*) TABLES, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '数据库名称' GROUP BY TABLE_SCHEMA;

SELECT TABLE_SCHEMA,COUNT(*) VIEWS FROM INFORMATION_SCHEMA.VIEWS  
WHERE TABLE_SCHEMA  = '数据库名称'  GROUP BY TABLE_SCHEMA;

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='数据库名称';

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='数据库名称';

SELECT TRIGGER_SCHEMA,TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE TRIGGER_SCHEMA= '数据库名称';

CREATE TABLE MYSQL_TABLES(TAB_OWNER VARCHAR(100),TAB_NAME VARCHAR(100),TAB_COUNT INT);
INSERT INTO MYSQL_TABLES SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '数据库名称' ORDER BY TABLE_ROWS DESC;

-- 创建表空间dbtest，数据文件为DBTEST.DBF。
create tablespace "dbtest" datafile '/data/dmdata/DAMENG/DBTEST.DBF' size 2048 ;

-- 创建用户
create user "DBTEST" identified by "密码"  
-- 指定用户DBTEST表空间为dbtest
default tablespace "dbtest"
-- 指定用户DBTEST索引表空间为dbtest
default index tablespace "dbtest";
-- 授予用户DBTEST常规权限
grant "PUBLIC","RESOURCE","SOI","SVI","VTI" to "DBTEST";

# OWNER 后面要替换成自己的用户名
SELECT OBJECT_TYPE,COUNT(*) FROM ALL_OBJECTS WHERE OWNER='USER_NAME' GROUP BY OBJECT_TYPE;

CREATE TABLE DM_TABLES
(
TAB_OWNER VARCHAR(100),
TAB_NAME  VARCHAR(100),
TAB_COUNT INT
);

DECLARE BEGIN FOR REC IN
(SELECT OWNER,
OBJECT_NAME
FROM ALL_OBJECTS
WHERE OWNER='USER_NAME'
AND OBJECT_TYPE='TABLE'
)
LOOP
EXECUTE IMMEDIATE 'INSERT INTO DM_TABLES SELECT '''|| REC.OWNER ||''','''|| REC.OBJECT_NAME ||''',COUNT(*) FROM '|| REC.OWNER || '.' || REC.OBJECT_NAME;
END LOOP;
END;

# 注意如果没有这张表 MYSQL_TABLES，可以看一下迁移前准备的语句是不是没有执行，
# 其次看一下是不是表名大小写不一致
SELECT A.TAB_OWNER,
       A.TAB_NAME,
       A.TAB_COUNT-B.TAB_COUNT
  FROM MYSQL_TABLES A,
       DM_TABLES B
 WHERE A.TAB_OWNER=B.TAB_OWNER
   AND A.TAB_NAME=B.TAB_NAME 
   AND A.TAB_COUNT-B.TAB_COUNT<>0;

# 找出数据库名中表用到的外键约束
SELECT
    TABLE_NAME,COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = '数据库名'
        AND REFERENCED_TABLE_NAME IS NOT NULL

show create table 表名;

# 类似于下面这一条的就是外键约束，关键字：FOREIGN KEY
CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `job_name`, `job_group`) REFERENCES `qrtz_job_details` (`sched_name`, `job_name`, `job_group`)

# 添加外键约束的命令
alter table "模式"."表" add constraint 约束名 foreign key ("外键列名") references "主表名" ("主键列名");