在 ORACLE 迁移到 MogDB 的过程中，经常遇到一些 ORACLE 系统视图的改造工作。记录如下：

MogDB 官方出了一个兼容性系统视图的插件

https://gitee.com/enmotech/compat-tools/tree/master

compat-tools

介绍

本项目是一个兼容工具集合，旨在为从其他异构数据库，迁移到 openGauss 之后的系统，创建必要的函数，以及系统视图的兼容。为后续的系统运维与应用改造提供便利。

脚本中带有版本控制，直接运行脚本时，会根据如下三种情况进行处理：

若待创建对象在目标数据库中不存在，则直接进行创建
若待创建对象版本高于目标数据库中的对象版本，则进行升级重建
若待创建对象版本不高于目标数据库中的对象版本，则跳过创建

目前看了下，代码较多、复杂，比如在改造过程中遇到的分区表查询的视图 user_tab_partitions，通过 giteee 来查看 ALL_TAB_PARTITIONS 发现代码较长，不光查询系统视图还用到了自定义的函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- =========================================================================
    -- DBA_TAB_PARTITIONS
    -- ALL_TAB_PARTITIONS
    -- USER_TAB_PARTITIONS
    -- =========================================================================
    -- 2022-01-26  [1.0 -> 1.1]    增加段管理字段 SEGMENT_MANAGED，移除 AVG_ROW_LEN (只能记录到父表，不能精确到分区)
    -- 2024-03-29  [2.1]           增加 interval 字段
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_PARTITIONS', '2.1')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_PARTITIONS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS TABLE_OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS PARTITION_NAME
                 , coalesce(array_to_string(p.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
                 , length(array_to_string(p.boundaries, ',')) as HIGH_VALUE_LENGTH
                 , row_number() over (partition by p.parentid order by p.boundaries) as PARTITION_POSITION
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , case when arraycontains(p.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as COMPRESSION
                 , case when arraycontains(p.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
                 , p.reltuples::bigint as NUM_ROWS
                 , p.relpages::bigint as BLOCKS
                 , coalesce(pg_stat_get_last_analyze_time(p.parentid), pg_stat_get_last_autoanalyze_time(p.parentid)) as LAST_ANALYZED
                 , case when pp.partstrategy = 'i' then 'YES' else 'NO' end as interval
              FROM pg_catalog.pg_partition p
              join pg_catalog.pg_class c on p.parentid = c.oid
              join pg_catalog.pg_partition pp on p.parentid = pp.parentid and pp.parttype = 'r'
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid
              left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid
             WHERE p.parttype = 'p';

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_PARTITIONS AS SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name, logging, compression, segment_managed, num_rows, blocks, last_analyzed, interval FROM compat_tools.DBA_TAB_PARTITIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_PARTITIONS for compat_tools.USER_TAB_PARTITIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_PARTITIONS: %', SQLERRM;
            rollback;
    end;
1
select count(*) from user_tab_partitions where partition_name = 'xxx_partition'

其实在日常改造过程当中，虽然 pg 是以扩展来号称期方便的特性，其实没必要，国产化改造过程中，要尽可能的简单，满足业务需求，对于上面遇到的，虽然是查询 user_tab_partitions 视图，但是他主要是查询分区表是否存在，MogDB的 pg_partition 就可以满足要求。

1
select count(*) from pg_partition p where parttype=‘p’ and p.relname = 'xxx_partition‘

还有 查询 USER_ALL_TABLES ，来判断表是否存在的。

1
2
SELECT COUNT(*) into result FROM USER_ALL_TABLES
WHERE TABLE_NAME=upper(p_tablename);

可以通过以下来改造：

1
2
SELECT COUNT(*) into result FROM information_schema.tables
WHERE TABLE_NAME=lower(p_tablename);

当然如果要通过插件的方式来解决，MogDB可以通过以下三个兼容性的插件来实现，等以后体验安装。

  1. whale（extension)
    https://docs.mogdb.io/zh/mogdb/v3.0/whale
  2. **orafce（extension)
    https://docs.mogdb.io/zh/mogdb/v3.0/orafce
  3. compat-tools (sql_script)
    https://gitee.com/enmotech/compat-tools

更新：20240603

user_tab_columns

1
select column_name from user_tab_columns where table_name='''||tab_name||''' and column_id='||col_id;

改造后：

1
select column_name from information_schema.columns where table_name'''||tab_name||''' and ordinal_position='||col_id;'

pg/MogDB 中使用 information_schema.columns 替换 user_tab_columns ，ordinal_position 替换 column_id

这个也可以加上 table_schema 的限制，只限制在当前 schema下面，table_schema=current_schema.

同样 user_tables 用 informat_schema.tables 改造。

1
select count(1) from user_tables where table_name=xxx;

改造如下：

1
select count(1) from information_schema.tables  where table_name='xxx' and table_schema=current_schema;

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/83bfca47/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议