1 说明本需求是从主库带的备库中80个表 前30天的数据通过DBLINK插入到历史表中,备库以T_开头,历史以H_开头。其中CREATE_TIME和
GUID字段都一样。各位表字段不一样可以改。
2 代码是从USER_TAB_COL 为主表关联 USER_SEG表,在主表中查询出表名 以T_BIGTABLE打头的。返回表名,列名,列ID,列名2
并以列ID排序。 列名2在开头追加“T."字符。
3 最外层代码显示表名,表大小,和SYNC_SQL语句。并以大小为降序。
4 SYNC_SQL 第一句是判断同步时间段是否有数据?有进入下一条
5 插入运行日志 SP_RUN_INFO
6 核心MERGE INTO 语句生成。
7 CHR(13)换行,CHR(9) TAB符号 WM_CONCAT函数完成 行转列 比如转成效果 (A,B,C,D,E)
8 SP_RUN_INFO 字段说明 日期,过程名,步骤,说明,消耗时间,影响行数,错误信息
9 变量说明
LV_ERRINFO VARCHAR(4000); --错误信息
LV_ROWNUM
NUMBER; --插入数据
LV_ALL_ROWNUM NUMBER; --总影响行数
LV_SP_NAME VARCHAR2(30); --存储过程名字
LV_BEGINTIME
TIMESTAMP; --步骤开始时间
LV_START_TIME TIMESTAMP; --过程开始时间
LV_ENDTIME TIMESTAMP; --步骤结束时间
LV_DAY_AGO_BEGIN
NUMBER; --多少天以前开始
LV_DAY_AGO_END NUMBER;
--多少天以前结束
LV_SELECT_COUNT NUMBER; --可获得多少笔数据
SQL:
SELECT Z.TABLE_NAME,F.BYTES/1024/1024/1024 AS SIZEGB,
'SELECT COUNT(*)
INTO LV_SELECT_COUNT FROM SHARK.'||TABLE_NAME||'@SHARK_ADGWHERE CREATE_TIME < TRUNC(SYSDATE-LV_DAY_AGO_BEGIN) AND CREATE_TIME >=
TRUNC(SYSDATE-LV_DAY_AGO_END) ;'||CHR(13)
||' IF LV_SELECT_COUNT > 0 THEN
'||CHR(13)
||' INSERT INTO SP_RUN_INFO
VALUES(SYSDATE,LV_SP_NAME,1,UPPER('''||TABLE_NAME||' INSERT
START''),NULL,0,NULL);'||CHR(13)
||' COMMIT;'||CHR(13)
||'
LV_BEGINTIME:=SYSTIMESTAMP;'||CHR(13)
||' MERGE INTO
H_'||SUBSTR(TABLE_NAME,3,LENGTH(TABLE_NAME)-2)||' H'||CHR(13)
||' USING (
'||CHR(13)
||CHR(9)||CHR(9)||'SELECT
'||wm_concat(COLUMN_NAME)||CHR(13)
||CHR(9)||CHR(9)||'FROM'||CHR(9)||'SHARK.'||TABLE_NAME||'@SHARK_ADG'||CHR(13)
||CHR(9)||CHR(9)||'WHERE
CREATE_TIME < TRUNC(SYSDATE-LV_DAY_AGO_BEGIN) AND CREATE_TIME >=
TRUNC(SYSDATE-LV_DAY_AGO_END) '||CHR(13)
||' ) T'||CHR(13)
||'
ON ( T.GUID = H.GUID)'||CHR(13)
||' WHEN NOT MATCHED
THEN'||CHR(13)
||' INSERT
('||WM_CONCAT(COLUMN_NAME)||')'||CHR(13)
||' VALUES
('||WM_CONCAT(T_COLUMN_NAME)||');'||CHR(13)
||'
LV_ENDTIME:=SYSTIMESTAMP;'||CHR(13)
||' LV_ROWNUM:=
SQL%ROWCOUNT;'||CHR(13)
||' INSERT INTO SP_RUN_INFO
VALUES(SYSDATE,LV_SP_NAME,2,UPPER('''||TABLE_NAME||' INSERT
END''),LV_ENDTIME-LV_BEGINTIME,LV_ROWNUM,NULL);'||CHR(13)
||'
COMMIT;'||CHR(13)
||' LV_SELECT_COUNT:=0;'||CHR(13)
||'END IF;
'||CHR(13)
||CHR(13)
AS SYNC_SQL
FROM
(
SELECT
TABLE_NAME,COLUMN_NAME,COLUMN_ID,'T.'||COLUMN_NAME AS T_COLUMN_NAME
FROM
USER_TAB_COLUMNS S
WHERE TABLE_NAME LIKE 'T_BIGTABLE%'
ORDER BY
TABLE_NAME, COLUMN_ID ASC
) Z
LEFT JOIN USER_SEGMENTS F ON
Z.TABLE_NAME=F.SEGMENT_NAME AND F.SEGMENT_TYPE='TABLE'
GROUP BY
Z.TABLE_NAME,F.BYTES
ORDER BY SIZEGB DESC;
10 此脚本在PL/SQL DEVLOPER工具下 SQL查询窗口,
11 查询出来后全选该列,然后放在文本编辑器中 替换掉双引号,然后再放进迁移存储过程中试图编译查错!