Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

getting error when inserting data from remote table

Posted on 2011-09-02
21
Medium Priority
?
991 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1336 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 74

Expert Comment

by:sdstuber
ID: 36473589
the problem is the underlying sys.aud$ table has CLOB columns
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 74

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 74

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 74

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 74

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
 

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 74

Expert Comment

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

Author Comment

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

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 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 74

Assisted Solution

by:sdstuber
sdstuber earned 664 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 74

Expert Comment

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

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

886 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