Solved:strange data format error: ora-01821

DENNIS WILLIAMS,

	hi, I finally solved it by recreate a new database and export the old data and import it into the new database.They are on the same machine with same os and same oracle version and same configuration.
	I just failed to solve the question and tried create a new database and found it work!
	Thanks,i think i do not want to dig into it any more, just get the work done.		

Regards
zhu chao
Eachnet DBA
86-21-32174588-667
chao_ping_at_vip.163.com

• 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_at_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.
>dwilliams_at_lifetouch.com
>
>
>-----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_at_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_at_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_at_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
>chao_ping_at_vip.163.com
>
>
>======= 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.
>>dwilliams_at_lifetouch.com
>>
>>
>>-----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: cemail2_at_sprintmail.com
>>
>>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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM
>>
>>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: ListGuru_at_fatcity.com (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
>¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡chaospku_at_163.net
>¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2002-08-28
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: zhu chao
> INET: chaospku_at_163.net
>
>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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM
>
>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: ListGuru_at_fatcity.com (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
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡chaospku_at_163.net
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2002-08-29

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: zhu chao
  INET: chaospku_at_163.net

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: ListGuru_at_fatcity.com (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).