How To: Fix an issue where a table is not visible in the table explorer despite the table being present in the database.
This article demonstrates two scenarios.
- Table Explorer does not list a1_tpcc because it is owned by tpcc schema and hvrhub4 schema does not have select privilege on it
- Table Explorer does not list a1_temp because it is a global temporary table
This section describes in detail the issue and a workaround for the same
- Oracle is the source database
- The schema hvrhub4 is the hub schema
- a1_temp is a global temporary table present in hvrhub4 schema
- a1_tpcc table is owned by tpcc schema
Scenario 1: HVR user hvrhub4 does not have the privilege to select the table a1_tpcc.
Steps: Grant hvrhub4 privilege to select the table a1_tpcc.
Scenario 2: The table a1_temp is a global temporary table
- If the table is visible in the database schema but if it is not visible while adding to the replication it is possible that this table is a temporary table
- You can run the below query to get the DDL of the table a1_temp. The DDL of the table tells if it is a temporary table.
SQL> spool ddl_list.sql; SQL> SET HEADING OFF; SQL> SET ECHO OFF; SQL> SET PAGES 999; SQL> SET LONG 90000; SQL> SPOOL DDL_LIST.SQL ; SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','A1_TEMP','HVRHUB4') FROM DUAL;
Result of the query:
CREATE GLOBAL TEMPORARY TABLE "HVRHUB4"."A1_TEMP" ( "C1" NUMBER(*,0), "C2" VARCHAR2(25 BYTE) ) ON COMMIT DELETE ROWS ;
Steps :There is no resolution to this scenario. HVR does not support global temporary tables because they are not static.