一、ORACLE 数据库基础信息
1.统计 ORACLE 数据库基础信息
1)统计页大小
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME ='DB_BLOCK_SIZE';
2)查询编码格式
SELECT
*
FROM
V$NLS_PARAMETERS A
WHERE
A.PARAMETER='NLS_CHARACTERSET';
2.统计 ORACLE 数据中的对象以及表数据量
1)根据指定用户统计用户下的各对象类型和数目
SELECT
OBJECT_TYPE,
COUNT(*)
FROM
ALL_OBJECTS
WHERE
OWNER='OWNERNAME'
GROUP BY
OBJECT_TYPE;
2)创建移植辅助表, 统计指定用户下所有的对象并插入到辅助表中
CREATE
TABLE ORACLE_OBJECTS
(
OBJ_OWNER VARCHAR(100),
OBJ_NAME VARCHAR(100),
OBJ_TYPE VARCHAR(50)
);
INSERT INTO ORACLE_OBJECTS
SELECT
OWNER ,
OBJECT_NAME,
OBJECT_TYPE
FROM
ALL_OBJECTS
WHERE
OWNER='OWNERNAME';
SELECT * FROM ORACLE_OBJECTS;
3)创建移植辅助表, 统计每个表的数据量并插入到移植辅助表中
CREATE
TABLE ORACLE_TABLES
(
TAB_OWNER VARCHAR(100),
TAB_NAMEVARCHAR(100) ,
TAB_COUNT INT
);
BEGIN
FOR REC IN
(
SELECT
OWNER,
OBJECT_NAME
FROM
ALL_OBJECTS
WHERE
OWNER ='OWNERNAME'
AND OBJECT_TYPE='TABLE'
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ORACLE_TABLES SELECT '''|| REC.OWNER ||''','''|| REC.OBJECT_NAME ||''',COUNT(*) FROM '|| REC.OWNER || '.' || REC.OBJECT_NAME;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUTLINE
(
REC.OWNER || '.' || REC.OBJECT_NAME || 'GETCOUNT ERROR'
)
;
END;
END LOOP;
END;
SELECT * FROM ORACLE_TABLES;
二、达梦数据基础信息
1.统计达梦数据基础信息
1)统计页大小
SELECT PAGE;
2)通过编码格式
SELECT UNICODE;
3)统计大小写敏感参数
SELECT CASE_SENSITIVE;
2.统计达梦数据中的对象以及表数据量
1)根据指定用户统计用户下的各对象类型和数目
SELECT
OBJECT_TYPE,
COUNT(*)
FROM
ALL_OBJECTS
WHERE
OWNER='OWNERNAME'
GROUP BY
OBJECT_TYPE;
2)统计指定用户下所有的对象,并记录到新的记录表中
CREATE
TABLE DM_OBJECTS
(
OBJ_OWNER VARCHAR(100),
OBJ_NAME VARCHAR(100),
OBJ_TYPE VARCHAR(50)
);
INSERT INTO DM_OBJECTS
SELECT
OWNER ,
OBJECT_NAME,
OBJECT_TYPE
FROM
ALL_OBJECTS
WHERE
OWNER='OWNERNAME';
3)统计每个表的数据量到表数据记录表 1.
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 ='OWNERNAME'
AND OBJECT_TYPE='TABLE'
)
LOOP
declare
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO DM_TABLES SELECT '''|| REC.OWNER ||''','''|| REC.OBJECT_NAME ||''',COUNT(*) FROM '|| REC.OWNER || '.' || REC.OBJECT_NAME;
EXCEPTION
WHEN OTHERS THEN
PRINT REC.OWNER || '.' || REC.OBJECT_NAME || 'GET COUNT ERROR';
END;
END LOOP;
END;
SELECT * FROM DM_TABLES;
三、对比达梦数据库中对象和 ORACLE 库中对象以及数据量差异
1.比对对象,找出没有迁移的对象
SELECT
*
FROM
ORACLE_OBJECTS
WHERE
(
OBJ_OWNER, OBJ_NAME
)
NOT IN
(
SELECT OBJ_OWNER, OBJ_NAME FROM DM_OBJECTS
) --AND OBJ_TYPE='TABLE'
;
2.比对表数据量,找出数据量不相等的表
SELECT
A.TAB_OWNER,
A.TAB_NAME ,
A.TAB_COUNT-B.TAB_COUNT
FROM
ORACLE_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