ajaybelde
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
insert into bginfo.testing@cdb1(select
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from cdb1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the problem is the underlying sys.aud$ table has CLOB columns
ASKER
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);
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
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);
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
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);
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
ASKER
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
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?
ASKER
no..what will rownum does..? can u explain me once..
ASKER
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(sel ect 'UTOLHO01',rownum,a.* from dba_audit_trail a where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-00913: too many values
insert into bginfo.testing@uapxho1(sel
*
ERROR at line 1:
ORA-00913: too many values
did you add the extra column ?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQL> /
insert into info.testing@prod(select 'dev',os_username,username ,userhost, terminal,t imestamp,o wner,obj_n ame,action ,action_na me,new_own er,new_nam e,obj_priv ilege,sys_ privilege, admin_opti on,grantee ,audit_opt ion,ses_ac tions,logo ff_time,lo goff_lread ,logoff_pr ead,logoff _lwrite,lo goff_dlock ,comment_t ext,sessio nid,entryi d,statemen tid,return code,priv_ used,clien t_id,econt ext_id,ses sion_cpu,e xtended_ti mestamp,pr oxy_sessio nid,global _uid,insta nce_number ,os_proces s,transact ionid,scn, sql_bind,s ql_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
insert into info.testing@prod(select 'dev',os_username,username
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from PROD
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQL> /
insert into info.testing@prod(select 'dev',os_username,username ,userhost, terminal,t imestamp,o wner,obj_n ame,action ,action_na me,new_own er,new_nam e,obj_priv ilege,sys_ privilege, admin_opti on,grantee ,audit_opt ion,ses_ac tions,logo ff_time,lo goff_lread ,logoff_pr ead,logoff _lwrite,lo goff_dlock ,comment_t ext,sessio nid,entryi d,statemen tid,return code,priv_ used,clien t_id,econt ext_id,ses sion_cpu,e xtended_ti mestamp,pr oxy_sessio nid,global _uid,insta nce_number ,os_proces s,transact ionid,scn from dba_audit_trail where timestamp < sysdate or timestamp is null)
*
ERROR at line 1:
ORA-00947: not enough values
insert into info.testing@prod(select 'dev',os_username,username
*
ERROR at line 1:
ORA-00947: not enough values
ASKER
1 insert into bginfo.testing@prod(db_uni que_name,o s_username ,username, userhost,t erminal,ti mestamp,
2 owner,obj_name,action,acti on_name,ne w_owner,ne w_name,obj _privilege ,sys_privi lege,admin _option,
3 grantee,audit_option,ses_a ctions,log off_time,l ogoff_lrea d,logoff_p read,logof f_lwrite,l ogoff_dloc k,
4 comment_text,sessionid,ent ryid,state mentid,ret urncode,pr iv_used,cl ient_id,ec ontext_id, session_cp u,
5 extended_timestamp,proxy_s essionid,g lobal_uid, instance_n umber,os_p rocess,tra nsactionid ,scn,sql_t ext,sql_bi nd)
6 (select d.db_unique_name,os_userna me,usernam e,userhost ,terminal, timestamp,
7 owner,obj_name,action,acti on_name,ne w_owner,ne w_name,obj _privilege ,sys_privi lege,admin _option,
8 grantee,audit_option,ses_a ctions,log off_time,l ogoff_lrea d,logoff_p read,logof f_lwrite,l ogoff_dloc k,
9 comment_text,sessionid,ent ryid,state mentid,ret urncode,pr iv_used,cl ient_id,ec ontext_id, session_cp u,
10 extended_timestamp,proxy_s essionid,g lobal_uid, instance_n umber,os_p rocess,tra nsactionid ,scn,sql_t ext,sql_bi nd from v$database d,
11 dba_audit_trail
12* where timestamp < sysdate or timestamp is null)
SQL> /
extended_timestamp,proxy_s essionid,g lobal_uid, instance_n umber,os_p rocess,tra nsactionid ,scn,sql_t ext,sql_bi nd from v$database d,
*
ERROR at line 10:
ORA-02070: database does not support in this context
2 owner,obj_name,action,acti
3 grantee,audit_option,ses_a
4 comment_text,sessionid,ent
5 extended_timestamp,proxy_s
6 (select d.db_unique_name,os_userna
7 owner,obj_name,action,acti
8 grantee,audit_option,ses_a
9 comment_text,sessionid,ent
10 extended_timestamp,proxy_s
11 dba_audit_trail
12* where timestamp < sysdate or timestamp is null)
SQL> /
extended_timestamp,proxy_s
*
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.
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.
ASKER
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)