Diagnosing and Resolving USER_TAB_COLUMNS View Inconsistencies Between Oracle and OceanBase

The author, a MySQL DBA, encountered a fault where stored procedures that query user_tab_columns returned incomplete SQL text in an OB Oracle tenant, while the same logic worked correctly in a regular Oracle environment.

Background

The procedure fetches table names from user_tab_columns and concatenates SQL strings. The article aims to reproduce and verify the issue.

Problem Reproduction – Oracle

-- 创建测试用户并赋权
[root@localhost ~]# sqlplus / as sysdba
SQL> create user u1 identified by u1;
SQL> create user u2 identified by u2;
SQL> grant connect,resource to u1;
SQL> grant create procedure to u1;
SQL> grant connect,resource to u2;
SQL> grant create synonym to u2;
SQL> grant select any table to u2;

-- 创建测试表并赋权
SQL> conn u1/u1
SQL> create table t1(id int);
SQL> insert into t1(id) values(1);
SQL> create synonym t1 for u1.t1;

-- 创建存储过程并赋权
SQL> conn u1/u1
SQL> create or replace procedure proc_case1 as
    v_str varchar2(10);
  begin
    select table_name into v_str from user_tab_columns where table_name='T1';
    dbms_output.put_line(v_str);
  end;
/
SQL> grant execute on proc_case1 to u2;

-- 创建存储过程同义词
SQL> conn u2/u2
SQL> create synonym proc_case1 for u1.proc_case1;

-- 验证
SQL> conn u1/u1
SQL> set serveroutput on;
SQL> call proc_case1();
T1
SQL> conn u2/u2
SQL> set serveroutput on;
SQL> call proc_case1();
T1

In Oracle, both users u1 and u2 obtain the correct table name, confirming expected behavior.

Problem Reproduction – OB Oracle

-- 创建测试用户并赋权 (same steps as above under OB environment)
-- 创建测试表并赋权
SYS[U1]> create table t1(id int);
SYS[U1]> insert into t1(id) values(1);
SYS[U1]> commit;
-- 创建表的同义词
SYS[U2]> create synonym t1 for u1.t1;
-- 创建存储过程并赋权 (uses user_tab_columns)
SYS[U1]> create or replace procedure proc_case1 as
    v_str varchar2(10);
  begin
    select table_name into v_str from user_tab_columns where table_name='T1';
    dbms_output.put_line(v_str);
  end;
/
SYS[U1]> grant execute on proc_case1 to u2;
-- 创建存储过程同义词
SYS[U2]> create synonym proc_case1 for u1.proc_case1;
-- 验证
SYS[U1]> set serveroutput on;
SYS[U1]> call proc_case1();
T1
SYS[U2]> set serveroutput on;
SYS[U2]> call proc_case1();
-- No output – query returns empty

In OB, user_tab_columns returns only objects owned by SYS, so the procedure cannot see tables created by user u1, leading to empty results.

Analysis of PL Objects and System Views

The author queries dba_source to list PL objects that reference user_tab_columns , and inspects various data dictionary views ( dba_objects , dba_views , dba_source ) to locate dependent objects.

select count(*), type from dba_source group by type;
-- Result shows many PROCEDURE, PACKAGE, FUNCTION entries.

select owner, object_name, object_type from dba_objects where owner='SYS' and (object_name like 'USER_PART_%' or object_name like 'USER_T%');

select owner, view_name, text from dba_views where owner not in ('SYS','SYSTEM',... ) and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%');

Because dba_views.text is of type LONG, direct LIKE queries fail (ORA‑00932). The workaround is to copy the view definition into a temporary table using to_lob to convert LONG to CLOB.

Workarounds

Replace user_tab_columns with all_tab_columns in stored procedures (requires code changes and SELECT ANY TABLE privilege).

Create synonyms for the target objects, but they also need SELECT ANY TABLE.

Copy dba_views into an intermediate table with CLOB conversion and query that table.

Create a materialized view that stores the converted definitions and refresh it on demand.

-- Create intermediate table
create table my_views as select owner, view_name, to_lob(text) text from dba_views;

-- Query intermediate table
select owner, view_name, text from my_views where text like '%USER_TAB_COLUMNS%';

-- Create materialized view
create materialized view my_mviews refresh force on demand as
  select owner, view_name, to_lob(text) text from dba_views;

-- Refresh materialized view
exec dbms_mview.refresh('my_mviews');

Solution

The recommended permanent fix is to replace references to user_tab_columns with all_tab_columns in the application code, ensuring the correct owner is specified, or to obtain a hot‑fix patch from OceanBase that aligns the view semantics with Oracle.

Problem Summary

In OceanBase, the permission logic for USER_TAB_COLUMNS and other USER_* views differs from Oracle, causing missing rows for non‑SYS users. The issue also affects related views such as USER_SYNONYMS and USER_TABLES . Until the vendor patch is applied, developers should audit code for these view usages and apply the temporary workarounds described above.