背景
在一次Oracle迁移Dm的过程中，源库&目的库大小写均敏感，执行客户提供的SQL脚本的过程中发现，表ip_address被系统默认成了表IP_ADDRESS。
经过分析，客户提供的SQL没有使用双引号，来确保Oracle和Dm数据库按照指定的大小写来识别表名，因此，做以下改写。
源SQL
需要用目的用户去执行
declare num number;
beginselect count(1) into num from user_tables where table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';if num > 0 thenexecute immediate 'drop table TMP_GET_ACTURAL_TABLE_COUNT';end if;
end;
/
create table TMP_GET_ACTURAL_TABLE_COUNT ( table_name varchar(50), table_cnt int );/
CREATE OR REPLACE
PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun integer) AUTHID CURRENT_USER
ISsqlstr varchar2(4000);
beginfor cursor_sql in ( select 'insert into TMP_GET_ACTURAL_TABLE_COUNT(table_name,table_cnt) select '''||table_name||''' as table_name,count(1) as table_cnt from '||table_name as sqlstr1 from user_tables where table_name not like 'SREF_CON_%') loopexecute immediate (cursor_sql.sqlstr1);commit;end loop;
end;
/
truncate table TMP_GET_ACTURAL_TABLE_COUNT;/
call GET_ACTURAL_TABLE_COUNT(1);
/
commit;
/select *from TMP_GET_ACTURAL_TABLE_COUNT order by upper(table_name);/
改写后SQL
需要用目的用户去执行
DECLAREnum NUMBER;
BEGINSELECT COUNT(1)INTOnumFROM user_tablesWHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';IF num > 0 THENEXECUTE IMMEDIATE 'DROP TABLE TMP_GET_ACTURAL_TABLE_COUNT';END IF;EXECUTE IMMEDIATE 'CREATE TABLE TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';
END;
/
-- 创建存储过程
CREATE OR REPLACE
PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun INTEGER) AUTHID CURRENT_USER
ISsqlstr VARCHAR2(4000);v_table_name USER_TABLES.TABLE_NAME%TYPE;
BEGIN-- 遍历所有用户表（排除以'SREF_CON_'开头的表）FOR rec IN (SELECT table_name FROM user_tables WHERE table_name NOT LIKE 'SREF_CON_%')LOOP-- 构建并执行SQL语句sqlstr := 'INSERT INTO TMP_GET_ACTURAL_TABLE_COUNT(table_name, table_cnt) SELECT ''' || rec.table_name || ''' AS table_name, COUNT(1) AS table_cnt FROM "' || rec.table_name || '"';EXECUTE IMMEDIATE sqlstr;END LOOP;-- 提交事务（在循环外提交）COMMIT;
END;
/
-- 调用存储过程
--BEGIN
-- GET_ACTURAL_TABLE_COUNT(1);
--END;
--/
-- 截断临时表（如果需要重新填充）
TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;/
-- 调用存储过程（如果需要）
BEGINGET_ACTURAL_TABLE_COUNT(1);
END;
/
-- 查询临时表SELECT * FROM TMP_GET_ACTURAL_TABLE_COUNT
ORDER BY UPPER(table_name);/
通用SQL-（暂时未成功）
思路：使用DBA用户可以执行任何模式下的表信息的统计
-- 删除并重新创建临时表
DECLARE num NUMBER; target_schema VARCHAR2(50) := 'ECOLOGY_TARGET'; --填写目标模式，保证大小写正确
BEGIN SELECT COUNT(1) INTO num FROM dba_tables WHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT' AND owner = target_schema; IF num > 0 THEN EXECUTE IMMEDIATE 'DROP TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';
END;
/CREATE OR REPLACE PROCEDURE GET_ACTURAL_TABLE_COUNT(isrun INTEGER)
IS sqlstr VARCHAR2(4000); v_table_name USER_TABLES.TABLE_NAME%TYPE; target_schema VARCHAR2(50) := 'ECOLOGY_TARGET'; --填写目标模式，保证大小写正确
BEGIN -- 遍历用户表（排除以'SREF_CON_'开头的表） FOR rec IN (SELECT table_name FROM dba_tables WHERE owner = 'target_schema' AND table_name NOT LIKE 'SREF_CON_%') LOOP -- 构建并执行SQL语句 sqlstr := 'INSERT INTO ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT(table_name, table_cnt) SELECT "' || rec.table_name || '" AS table_name, COUNT(1) AS table_cnt FROM "' || rec.table_name || '"'; EXECUTE IMMEDIATE sqlstr; END LOOP; -- 提交事务（在循环外提交） COMMIT;
END;
/-- 截断临时表（如果需要重新填充）
TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;/
-- 调用存储过程（如果需要）
BEGINGET_ACTURAL_TABLE_COUNT(1);
END;
/
-- 查询临时表SELECT *FROM TMP_GET_ACTURAL_TABLE_COUNT
ORDER BY UPPER(table_name);/
