DENNIS WILLIAMS, hi, thanks for your answer. First i appolygize for input so much data in the mail. As i said,the query runs well in sql*plus.But in OWB(Oracle WareHouseBuilder, it just refused to work. As i posted in the trace file: >*** SESSION ID:(8.21) 2002-08-28 12:20:45.494 >*** 2002-08-28 12:20:45.494 >ksedmp: internal or fatal error >ORA-01821: date format not recognized >ORA-02063: preceding line from QQQ >Current SQL statement for this session: >select 1 from dual@qqq >----- Call Stack Trace ----- This is the actual SQL that give out the error message.The dblink name is specified by me when i create it in OWB(i randomly input some char as the name,for i know it won't work at all:(. ) I have been stuck for several days,unable to solve it.I gathered most of the information that maybe useful to find out the reason.
Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] ======= 2002-08-28 06:58:00 ,you wrote£º======= >Zhu - I compliment you on your preparation. You have avoided the normal >pitfall and posted a great deal of information about your issue, which I >hope will allow one of the experts on the list to diagnose your problem. > The query you post is "select 1 from dual@dblink". Is this really >the exact query? If that query is giving an error, then I am no help for you >and look forward to some good responses from others more knowledgeable than >I. > >Dennis Williams >DBA >Lifetouch, Inc. >[EMAIL PROTECTED] > > >-----Original Message----- >Sent: Wednesday, August 28, 2002 3:28 AM >To: Multiple recipients of list ORACLE-L > > >hi, list friends: > I have a oracle 8.1.7.4 database in redhat linux 7.2,which is used >to support a development datawarehouse project.Now i hit some strange error >message about date format through dblink > The error only appear in oracle warehouse builder, it cannot be >reproduced in sqlplus with the same statement.Query like: select 1 from >dual@dblink, and it give out error message of ora-1821.If i mannually create >the dblink and do the query, it is ok in sql*Plus, and if i try to use the >dblink in oracle warehouse builder, it still give out error.I am not >familier with warehouse builder.So i refer to this mailing list for help. > I also tried set event like: alter system set events ' 1821 trace >name errorstack level 5' > >Starting up ORACLE RDBMS Version: 8.1.7.4.0. >System parameters with non-default values: > processes = 150 > timed_statistics = TRUE > event = 1821 trace name errorstack level 5 > shared_pool_size = 81943040 > and there is trace file like: >/oracle/8.1.7/admin/back/udump/ora_1535.trc >Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production >With the Partitioning option >JServer Release 8.1.7.4.0 - Production >ORACLE_HOME = /oracle/8.1.7 >System name: Linux >Node name: dwdb >Release: 2.4.7-10 >Version: #1 Thu Sep 6 17:27:27 EDT 2001 >Machine: i686 >Instance name: back >Redo thread mounted by this instance: 1 >Oracle process number: 9 >Unix process pid: 1535, image: oracle@dwdb (TNS V1-V3) > >*** SESSION ID:(8.21) 2002-08-28 12:20:45.494 >*** 2002-08-28 12:20:45.494 >ksedmp: internal or fatal error >ORA-01821: date format not recognized >ORA-02063: preceding line from QQQ >Current SQL statement for this session: >select 1 from dual@qqq >----- Call Stack Trace ----- >calling call entry argument values in hex > >location type point (? means dubious value) > >-------------------- -------- -------------------- >---------------------------- >Cannot seek to string table section header in /lib/libm.so.6. >Cannot seek to string table section header in /lib/libm.so.6. >Cannot seek to string table section header in /lib/libm.so.6. >Cannot seek to string table section header in /lib/libm.so.6. >ksedmp()+142 CALL ksedst()+0 >ksddoa()+145 CALLr 00000000 81696C4 ? >ksdpcg()+175 CALL ksddoa()+0 >ksdpec()+171 CALL ksdpcg()+0 >ksfpec()+122 CALL ksdpec()+0 >kgesev()+96 CALLr 00000000 80F ? 4 ? >ksesec2()+24 CALL kgesev()+0 >npierr()+1873 CALL ksesec2()+0 >npixfc()+11784 CALL npierr()+0 >k2rlog()+120 CALL npixfc()+0 0 ? 1 ? 1 ? 0 ? 0 ? > BFFFA438 ? BFFF9F30 ? > BFFFA43F ? 970FCC0 ? > BFFF923C ? 456F1EED ? >npicon0()+3496 CALL k2rlog()+0 >ddfnet2Normal()+237 CALL npicon0()+0 > The file /lib/libm.so.6 is a link and it seems ok.I compare it with >the same file on another machine running production datawareshouse, it seems >the same.I also tried to copy it back, but in vain. > > Some additional information : >bash_profile: >ORACLE_SID=back >ORACLE_HOME=/oracle/8.1.7 >export ORACLE_HOME >ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data >ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data >export ORA_NLS33 >LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/home/oracle/product/webcache/webcache >/bin >EDITOR=vi >NLS_LANG=american_america.ZHS16CGB231280 >export ORACLE_SID ORACLE_HOME ORA_NLS LD_LIBRARY_PATH EDITOR PATH NLS_LANG > >NLS_DATE_FORMAT="YYYYMMDD hh24:mi:ss" >export NLS_DATE_FORMAT > >initsid.ora file: >//////// >db_name = "back" >instance_name = back >service_names = back >control_files = ("/oracle/8.1.7/oradata/back/control01.ctl", >"/oracle/8.1.7/oradata/back/control02.ctl", >"/oracle/8.1.7/oradata/back/control03.ctl") >open_cursors = 300 >max_enabled_roles = 30 >db_block_buffers = 80000 >shared_pool_size = 81943040 >large_pool_size = 614400 >java_pool_size = 30000000 >log_checkpoint_interval = 0 >log_checkpoint_timeout = 0 >processes = 150 >log_buffer = 163840 >user_dump_dest = /oracle/8.1.7/admin/back/udump >db_block_size = 8192 >remote_login_passwordfile = exclusive >os_authent_prefix = "" >local_listener = "listener_back" >compatible = "8.1.7" >sort_area_size = 2048576 >sort_area_retained_size = 2048576 > >open_links = 20 >parallel_max_servers = 2 >timed_statistics = true >event = "1821 trace name errorstack level 5" >///////////////////////////// >sys.props$ table: >QL> l > 1* select * from sys.props$ >SQL> / > >NAME VALUE$ >COMMENT$ >---------------------------------------- ------------------------------ >------------------------------ >DICT.BASE 2 >dictionary base tables version > # > >DBTIMEZONE 0:00 DB >time zone >NLS_LANGUAGE AMERICAN >Language >NLS_TERRITORY AMERICA >Territory >NLS_CURRENCY $ >Local currency >NLS_ISO_CURRENCY AMERICA ISO >currency >NLS_NUMERIC_CHARACTERS ., >Numeric characters >NLS_CHARACTERSET ZHS16CGB231280 >Character set >NLS_CALENDAR GREGORIAN >Calendar system > >NAME VALUE$ >COMMENT$ >---------------------------------------- ------------------------------ >------------------------------ >NLS_DATE_FORMAT DD-MON-RR Date >format >NLS_DATE_LANGUAGE AMERICAN Date >language >NLS_SORT BINARY >Linguistic definition >NLS_TIME_FORMAT HH.MI.SSXFF AM Time >format >NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time >stamp format >NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM Time >with timezone format >NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:T >Timestamp with timezone format > ZM > >NLS_DUAL_CURRENCY $ Dual >currency symbol >NLS_COMP BINARY NLS >comparison > >NAME VALUE$ >COMMENT$ >---------------------------------------- ------------------------------ >------------------------------ >NLS_NCHAR_CHARACTERSET ZHS16CGB231280 >NCHAR Character set >GLOBAL_DB_NAME BACK >Global database name >EXPORT_VIEWS_VERSION 8 >Export views revision # >NLS_RDBMS_VERSION 8.1.7.4.0 >RDBMS version for NLS paramete > rs >///////////////////////////////////////////// > > > Thanks for your help:) >Regards >zhu chao >Eachnet DBA >86-21-32174588-667 >[EMAIL PROTECTED] > > >======= 2002-08-27 14:24:00 ,you wrote£º======= > >>Eric >> set long 2000 >> select text from user_views where view_name = 'MYVIEW'; >> >>There are probably more elegant methods, but this works. >> >>Dennis Williams >>DBA >>Lifetouch, Inc. >>[EMAIL PROTECTED] >> >> >>-----Original Message----- >>Sent: Tuesday, August 27, 2002 4:49 PM >>To: Multiple recipients of list ORACLE-L >> >> >>I did an import into a database and have one view that is invalid. How can >>I >>get the definition for the view out of database so that I can drop it and >>recreate it? >> >>Might there be any issues with just dropping and recreating it? How do >>other >>people handle this when then import goes well except for one view being >>invalid? >> >> >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>-- >>Author: Eric Richmon >> INET: [EMAIL PROTECTED] >> >>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >>San Diego, California -- Public Internet access / Mailing Lists >>-------------------------------------------------------------------- >>To REMOVE yourself from this mailing list, send an E-Mail message >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>the message BODY, include a line containing: UNSUB ORACLE-L >>(or the name of mailing list you want to be removed from). You may >>also send the HELP command for other information (like subscribing). >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>-- >>Author: DENNIS WILLIAMS >> INET: [EMAIL PROTECTED] >> >>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >>San Diego, California -- Public Internet access / Mailing Lists >>-------------------------------------------------------------------- >>To REMOVE yourself from this mailing list, send an E-Mail message >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>the message BODY, include a line containing: UNSUB ORACLE-L >>(or the name of mailing list you want to be removed from). You may >>also send the HELP command for other information (like subscribing). > >= = = = = = = = = = = = = = = = = = = = > > > > >¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡zhu chao >¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡[EMAIL PROTECTED] >¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2002-08-28 > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: zhu chao > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡zhu chao ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡[EMAIL PROTECTED] ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2002-08-28 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).