Solutions to ORA-01948 and ORA-04052 errors

Solutions to ORA-01948 and ORA-04052 errors

1. background:

The business personnel reported the following error when creating a Materialized View:

SQL>

CREATE MATERIALIZED VIEW MV_RETAIL_wbmout_R

 TABLESPACE SAPDATA

PCTUSED 0

PCTFREE 10

 INITRANS 2

 MAXTRANS 255

STORAGE (

 INITIAL 64 K

NEXT 1 M

 MINEXTENTS 1

 MAXEXTENTS UNLIMITED

PCTINCREASE 0

 BUFFER_POOL DEFAULT

 FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD DEFERRED

REFRESH COMPLETE ON DEMAND

WITH ROWID

SELECT "record_code" FROM "wbm_store_out_record" @ MYSQL

Create materialized view MV_RETAIL_wbmout_R

*

Row 3 has an error:

ORA-04052: An error occurred while searching for remote object datachk. wbm_store_out_record @ OTO

ORA-01948: The Name Length of the identifier (31) exceeds the maximum length (30)
 

"Wbm_store_out_record" @ MYSQL is a table on the remote mysql database and accessed through a transparent gateway;

 

2. Search for metalink documents and organize them as follows:

2.1 symptoms

Line # = 0 Column # = 0 Error Text = ORA-04052: error occurred when looking up remote object Oracle. V_STG_GENDGEN @ DBLINK

ORA-01948: identifier's name length (31) exceeds maximum (30)

This occurs even though the SQL Server table name is less than 30 characters.

 

A query of the remote table via SQLPlus does not produce the error. However, running an anonymous block from SQLPlus does produce the error:

 

Error starting at line 1 in command:

Declare

V_count integer;

Begin

Select count (*) into v_count from ORACLE. V_STG_GENDGEN @ DBLINK;

End;

Error report:

ORA-04052: error occurred when looking up remote object ORACLE. V_STG_GENDGEN @ RAY

ORA-01948: identifier's name length (31) exceeds maximum (30)

04052. 00000-"error occurred when looking up remote object % s"

* Cause: An error has occurred when trying to look up a remote object.

* Action: Fix the error. Make sure the remote database system has run

KGLR. SQL to create necessary views used for querying/looking up

Objects stored in the database.

You can select a table through transparent gateway. However, when creating a materialized view, an error is reported for the column length;

2.2 reason

When you access a remote ms or mysql database through transparent gateway, this error is reported when any column in these tables has a length greater than 30 characters;

2.3 Solution

Method 1: based on the error message, an error is reported if the column length is greater than 30 characters. Modify the column length to be less than 30 characters. (when the system is online, some fields may be referenced by the application. Therefore, it is risky to modify the column name at this time. We do not recommend that you use this function)

Method 2: Create a view where the names of all columns cannot exceed 30 characters (recommended)

Because our system has been launched, we can directly create a view to avoid unnecessary problems;

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle RAC 11.2 (12C) correctly closes the order