Link to home
Start Free TrialLog in
Avatar of ajaybelde
ajaybeldeFlag for United States of America

asked on

getting error when inserting data from remote table

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
Avatar of ajaybelde
ajaybelde
Flag of United States of America image

ASKER

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)


ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

the problem is the underlying sys.aud$ table has CLOB columns
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..
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);
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);
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);
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
oh sorry, the order by isn't allowed on the insert,  did you try the rownum method?
no..what will rownum does..? can u explain me once..
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
did you add the extra column ?
no dude..
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
 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

you've changed the query by adding additional table
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.