Solved

getting error when inserting data from remote table

Posted on 2011-09-02
21
950 Views
Last Modified: 2012-06-21
SYS@db1> insert into bginfo.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
insert into bginfo.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from cdb1
0
Comment
Question by:ajaybelde
  • 10
  • 9
  • 2
21 Comments
 

Author Comment

by:ajaybelde
ID: 36473530
SYS@db1> desc dba_audit_trail
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OS_USERNAME                                        VARCHAR2(255)
 USERNAME                                           VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 TIMESTAMP                                          DATE
 OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)
 ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)
 NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(1)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000

SYS@cdb1> desc info.testing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DB_UNIQUE_NAME                                     VARCHAR2(30)
 OS_USERNAME                                        VARCHAR2(255)
 USERNAME                                           VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 TIMESTAMP                                          DATE
 OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)
 ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)
 NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(1)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000)


0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 334 total points
ID: 36473556
>>ORA-22992: cannot use LOB locators selected from remote tables

Error seems self explanatory.  CLOBs across database links are problematic.

See if any of these solutions will work for you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:950029833940
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36473589
the problem is the underlying sys.aud$ table has CLOB columns
0
 

Author Comment

by:ajaybelde
ID: 36473884
yes sdstuber, i am seeing clob objects in aud$, but i am not clob objects in dba_audit_trail, can u help me how to do this..
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36473958
dba_audit_trail selects from aud$,  so you are pulling the CLOB

but,  dba_audit_trail cuts the CLOB's off at 2000 characters.

you want Oracle to resolve the substr calls before trying to send the data across the link, so you'll have varchar2  results instead of clob results

sometimes you can do that simply by sticking in rownum, because rownum doesn't exist until the results are generated.

this will mean adding an extra column to your testing table though

insert into bginfo.testing@cdb1(select 'db1',rownum, a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36473970
you could also try ordering the data, for the same reason.
ordering doesn't happen until the results are done
this way might be slower but it won't require modifying your table structure


insert into bginfo.testing@cdb1(select 'db1', a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null order by timestamp);
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36473971
you could also try ordering the data, for the same reason.
ordering doesn't happen until the results are done
this way might be slower but it won't require modifying your table structure


insert into bginfo.testing@cdb1(select 'db1', a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null order by timestamp);
0
 

Author Comment

by:ajaybelde
ID: 36474314
SYS@db1> insert into info.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate and timestamp is null order by times
insert into info.testing@cdb1(select 'db1',a.* from dba_audit_trail a where timestamp < sysdate and timestamp is null order by timestamp)
                                                                                                                                                                                                                *
ERROR at line 1:
ORA-00907: missing right parenthesis
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36474437
oh sorry, the order by isn't allowed on the insert,  did you try the rownum method?
0
 

Author Comment

by:ajaybelde
ID: 36474552
no..what will rownum does..? can u explain me once..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ajaybelde
ID: 36474597
SYS@db1> insert into info.testing@cdb1(select 'UTOLHO01',rownum,a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null);
insert into bginfo.testing@uapxho1(select 'UTOLHO01',rownum,a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null)
                   *
ERROR at line 1:
ORA-00913: too many values
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36474614
did you add the extra column ?
0
 

Author Comment

by:ajaybelde
ID: 36475247
no dude..
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36475398
that would do it.


if you include rownum in the select output, you must have a corresponding numeric column in the same position of the table.

or, specify the column order (better option, more robust)  in both the insert and the select
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 36475414
>>no dude..

Like sdstuber suggested here and why I mentioned it in your other question:  You should not use insert into with a select *.

You should specify the individual column on the insert side and the select side.

This way you know with 100% accuracy what goes where.
0
 

Author Comment

by:ajaybelde
ID: 36484795
SQL> /
insert into info.testing@prod(select 'dev',os_username,username,userhost,terminal,timestamp,owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_bind,sql_text  from dba_audit_trail a where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from PROD
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 166 total points
ID: 36486060
same problem mentioned above http:#36473589

you are selecting the lobs from dba_audit_trail because dba_audit_trail selects from aud$


either don't select them  (sql_bind, sql_text)  or you must force the dba_audit_trail to resolve those fields to varchar2 before sending to the remote system.
0
 

Author Comment

by:ajaybelde
ID: 36486180
SQL> /
insert into info.testing@prod(select 'dev',os_username,username,userhost,terminal,timestamp,owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn from dba_audit_trail where timestamp < sysdate or timestamp is null)
                   *
ERROR at line 1:
ORA-00947: not enough values
0
 

Author Comment

by:ajaybelde
ID: 36486266
 1  insert into bginfo.testing@prod(db_unique_name,os_username,username,userhost,terminal,timestamp,
  2  owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,
  3  grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,
  4  comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,
  5  extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind)
  6  (select d.db_unique_name,os_username,username,userhost,terminal,timestamp,
  7  owner,obj_name,action,action_name,new_owner,new_name,obj_privilege,sys_privilege,admin_option,
  8  grantee,audit_option,ses_actions,logoff_time,logoff_lread,logoff_pread,logoff_lwrite,logoff_dlock,
  9  comment_text,sessionid,entryid,statementid,returncode,priv_used,client_id,econtext_id,session_cpu,
 10  extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind from v$database d,
 11  dba_audit_trail
 12* where timestamp < sysdate or timestamp is null)
SQL> /
extended_timestamp,proxy_sessionid,global_uid,instance_number,os_process,transactionid,scn,sql_text,sql_bind from v$database d,
                                                                             *
ERROR at line 10:
ORA-02070: database  does not support  in this context

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36486342
you've changed the query by adding additional table
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36496513
ajaybelde,

are you sure you got your answer?

http:#36473589 answers the question as to why the error occured but it wasn't selected.

Of the other 3 that were selected, none of the suggestions to address the clob issue were followed.

You did switch to named columns vs '*', but that was a peripheral issue, it had nothing to do with the clob problem you asked about.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now