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).