RE: Inserts are taking time !
Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 2:29 AM > Good question, Jared! Perhaps 'overflow' is technically not the correct > term to use to decribe this scenario but it seemed to fit the bill > sufficiently to mail off a quick one-liner solution without going into great > depth. Some of us have work to do, you know ;) > > To redeem myself I probably should have mentioned that this table sounds > pretty volatile. Consequently the index(es) are likely to end up fairly > disorganized, especially if the 350k records are being inserted in > ascending order. Once you start adding levels to the index things start to > slow down > > Chris > > -Original Message- > Sent: 04 September 2002 16:50 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > It sounds to me like the indexes are going into overflow - this will cause > > What do you mean by 'overflow'? > > Jared > > > the insert time to increase. I would suggest batching up the inserts, > > dropping the indexes, running the inserts and re-creating the indexes. > > > > Chris > > > > -Original Message- > > Sent: 04 September 2002 07:53 > > To: Multiple recipients of list ORACLE-L > > > > > > Hi All, > > > > We have a table which can contain more than half a million records. When > we > > try to insert some 10k records in the empty table it get inserted in 10 > > min. but as the size increases time taken to insert also increases. After > > 350,000 records it takes around an hour to insert 10k records. > > There are around 15 columns in it out of which 11 are indexed. There is > one > > concatenated function-based index on two columns of Varchar type and two > > separate index for the same two columns. > > > > I have checked the free space for the tablespaces to which the table and > > indexes are attached to. They are in two separate tbs. > > > > Any clues why this is happenning. > > > > TIA > > Marul. > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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: Marul Mehta 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: Naveen Nahata 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).
Re: Inserts are taking time !
Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 2:29 AM > Good question, Jared! Perhaps 'overflow' is technically not the correct > term to use to decribe this scenario but it seemed to fit the bill > sufficiently to mail off a quick one-liner solution without going into great > depth. Some of us have work to do, you know ;) > > To redeem myself I probably should have mentioned that this table sounds > pretty volatile. Consequently the index(es) are likely to end up fairly > disorganized, especially if the 350k records are being inserted in > ascending order. Once you start adding levels to the index things start to > slow down > > Chris > > -Original Message- > Sent: 04 September 2002 16:50 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > It sounds to me like the indexes are going into overflow - this will cause > > What do you mean by 'overflow'? > > Jared > > > the insert time to increase. I would suggest batching up the inserts, > > dropping the indexes, running the inserts and re-creating the indexes. > > > > Chris > > > > -Original Message- > > Sent: 04 September 2002 07:53 > > To: Multiple recipients of list ORACLE-L > > > > > > Hi All, > > > > We have a table which can contain more than half a million records. When > we > > try to insert some 10k records in the empty table it get inserted in 10 > > min. but as the size increases time taken to insert also increases. After > > 350,000 records it takes around an hour to insert 10k records. > > There are around 15 columns in it out of which 11 are indexed. There is > one > > concatenated function-based index on two columns of Varchar type and two > > separate index for the same two columns. > > > > I have checked the free space for the tablespaces to which the table and > > indexes are attached to. They are in two separate tbs. > > > > Any clues why this is happenning. > > > > TIA > > Marul. > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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: Marul Mehta 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).
Sampling V$SESSTAT
I want to start sampling this table, however collecting data on the 200+ statistics for each session would produce a prohibitively large result. I'm trying to pare the 225 statistics to something more reasonable, but I cannot decide which ones to discard and which to record. Does anyone have a listing of the most useful statistics that they would like to share? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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).
Re: Inserts are taking time !
No there is not a single bitmap indexes. We had previously but than removed all and converted to normal b-tree indexes. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 4:58 PM > Marul, > > Are there any bitmapped indexes on the table > > Iain Nicoll > > -Original Message- > Sent: Wednesday, September 04, 2002 11:28 AM > To: Multiple recipients of list ORACLE-L > > > Marul, > > 10k records in 1 hour(3600 seconds) > > 1 record in 3600/1 => approx 0.36 seconds > > If your application is OLTP you'll be inserting records 1 by 1 rather than > in > bulk. Which means the effect will hardly be noticed. > > If you are going to insert record in bulk you can DROP and then recreate the > indexes after load. > > Check what takes more time. > > See if there is any scope of partitioning the table, to use local > partitioned > indexes. > > For bulk load, disabling the constraints is also an option. > > Naveen > > -Original Message- > Sent: Wednesday, September 04, 2002 3:13 PM > To: Multiple recipients of list ORACLE-L > > > Thanks for the immediate reply > But my requirement is such that I cannot reduce the indexes. There are lots > of selects happeneing on this table based on these indexed columns. Our > entire application is about to move in the production environment and we > cant change our DB design at this time. > > Please suggest > > TIA, > Marul. > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 1:33 PM > > > > Yep and you have given the answer yourself. It is the number of indexes. I > > think that if the number of records increase the number of levels increase > > and slowly but surely you need to update more and more blocks. I have done > > sone tests (an oher people I am sure) that show that there is an expontial > > increase in the amount of undo and redo generated for every index that > gets > > added into the mix. > > > > You will probably see an increase in CPU time (assuming that you are the > only > > process/session on the system). > > > > Anjo. > > > > > > On Wednesday 04 September 2002 08:53, you wrote: > > > Hi All, > > > > > > We have a table which can contain more than half a million records. When > we > > > try to insert some 10k records in the empty table it get inserted in 10 > > > min. but as the size increases time taken to insert also increases. > After > > > 350,000 records it takes around an hour to insert 10k records. There are > > > around 15 columns in it out of which 11 are indexed. There is one > > > concatenated function-based index on two columns of Varchar type and two > > > separate index for the same two columns. > > > > > > I have checked the free space for the tablespaces to which the table and > > > indexes are attached to. They are in two separate tbs. > > > > > > Any clues why this is happenning. > > > > > > > > > TIA > > > Marul. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Anjo Kolk > > 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: Marul Mehta > 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: Naveen Nahata > 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 fro
Re: Any woraround for this ....?
Title: Message Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL> exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
Runing logmnr always 3113 and core dump!
hi, list friends: I am trying to analyze the redo log in oracle 8172 64bit on solaris.Whatever i tried , it always core dump after fetching about 60k records from the v$logmnr_contents. I tried to analyze the logfile on 8172(64bit), 901 64bit on solairs, but in vain. Here is the example output: 04-SEP-02 update "EACHPAY"."UL_USERLOGIN 04-SEP-02 update "ACCOUNT"."USERSTATISIN 04-SEP-02 update "BIDDER"."SHOPCATALOG" ERROR: ORA-03113: end-of-file on communication channel 66600 rows selected. and in the background trace file, there is alert like: Errors in file /export/home/oracle9i/admin/test9i/udump/test9i_ora_4534.trc: ORA-07445: exception encountered: core dump [7F790794] [SIGSEGV] [Address not mapped to object] [0x000D8] [] [] Count(*)from v$logmnr_contents also give out such error. I tried to enlarge the shared_pool, shared_pool_reserved to SQL> show parameter shared_pool NAME TYPEVALUE --- -- shared_pool_reserved_sizebig integer 2000 shared_pool_size big integer 234881024 Still get that error. Is there someone also ever hit similiar error ? Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] ¡¡ -- 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).
Off-topic Informix
Hello all, I have a problem in Informix... I wrote a program which will select count(*) from a table. Select count(*) into $var from ibtab; If there are no rows also, the variable var is showing as 1 and if there are 1 records, it shows as 2.. and another program which also uses the same select shows the correct result.. What may be the reason ?? Thanks and regards,Santosh Varma
RE: Trace file with tkprof
Title: RE: Trace file with tkprof
> -Original Message-
> From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
>
> Is there any way to get the same execution statistics between
> the finished sql statement and interrupted sql statement?
>
> Supposed table A have ten million rows.
>
> If select A.a, A.b from A where ..., in sqlplus session,
> actually the returned could be millions. I could not wait for
> all the selected rows coming out. I have to interrupte it in
> the process. Could I still get the same execution statistics
> in trace file?
>
> The same happened to "set autotrace on" in sqlplus session. I
> could only see the execution plan at the end of execution.
Would "set autotrace traceonly explain" or "set autotrace traceonly explain statistics" do what you need?
From the SQL*Plus manual, SET command:
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). The report can include execution statistics and the query execution path.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle9i SQL Reference manual.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
Information about Execution Plans and the statistics is documented in the Oracle9i Performance Guide and Reference manual.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
Trace file with tkprof
Hi DBAs, Is there any way to get the same execution statistics between the finished sql statement and interrupted sql statement? Supposed table A have ten million rows. If select A.a, A.b from A where ..., in sqlplus session, actually the returned could be millions. I could not wait for all the selected rows coming out. I have to interrupte it in the process. Could I still get the same execution statistics in trace file? The same happened to "set autotrace on" in sqlplus session. I could only see the execution plan at the end of execution. Thanks in advance, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Re: Configuring 734 and 817 on same machine.
When configuring two versions of ORacle on a single machine , whether it is NT or UNIX, always use the Listener of the most recent version. It is backwards compatible. RWB Denham Eva <[EMAIL PROTECTED]>@fatcity.com on 08/30/2002 03:28:19 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hello Gurus, Does anyone have a link to a resource which describes the Installation and configuration of Oracle 734 and Oracle 817 on the same Windows 2000 machine. The configuration of the listeners is my main concern. Any help on this is very much appreciated. Regards Denham Eva DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Contract Forwarding, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Replication question
When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called $RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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).
RE: Find the dependent objects? <-- Attachment History Removed
Thanks Jack very much. It works very well.
CHuan
-Original Message-
Sent: Wednesday, 4 September 2002 6:28 PM
To: Multiple recipients of list ORACLE-L
break on "Type" skip 1 nodup
set verify off
set pages 100
col "Owner" for a30
col "Object name" for a32
Col "Type" for a12
select
type "Type"
, owner "Owner"
, name "Object name"
from
dba_dependencies
where
referenced_owner||'.'||referenced_name=upper('&1')
order by
"Type"
, "Owner"
/
clear breaks
clear columns
call this script from sqlplus like @
RE: hash_value and address
We see one such collision about every four years in the application sites who share their data with us. The "hash_value" is definitely not a unique identifier. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- Faroult Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Pablo Rodriguez wrote: > > I see 2 different addreses for the same hash_value in > v$sqlarea. > Why do I see this? > > Here's an example: > > hash_value address > -- --- > 3749804 4064082C > 3749804 4192941C > > I wonder if someone can shed some light on this > > Txs > My understanding is that to quickly find an already parsed statement in memory, Oracle uses a hash function, which, like all hash functions is not surjective (which means that you can have x <>y and f(x)= f(y), in which case you say you have a collision). Of course once the hash value has been computed values are checked, and all strings hashing to the same value are linked - and stored at different addresses. You can also have the case in which statements are strictly identical, but, for session-related reasons, cannot be considered the same cursor (because of synonyms pointing to different objects, of similarly named tables in different schemas, or because optimizer settings have been altered at the session level, typically). In that case it's normal to have the same hash result for the same string - but you nevertheless have a collision of a sort. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Cary Millsap 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).
Re: waits vs. logons
Could be "SQL*Net message from client" Query v$session_event to see what else a session has waited on: select sess.username, sess.sid, se.event, se.total_waits, se.total_timeouts, se.time_waited/100 time_waited, se.average_wait from v$session_event se, v$session sess where event like '%' and sess.sid = se.sid and sess.username is not null order by username, sid; Jared "Terrian, Tom" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:waits vs. logons I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) b where a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS -- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 144943 1303 24 595844 588441 261733 1728 275308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719507 56 14507 8706 592269221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: waits vs. logons
Title: Message First, you’re missing user-mode CPU time consumed by the Oracle session. The statistic that is supposed to hold this number is ‘CPU used by this session’ from v$sesstat. But there are bugs in that statistic. Second, some of the time that an Oracle process spends sleeping (because of context switches imposed by a timesharing operating system) isn’t counted either. See www.hotsos.com/dnloads/1/kevents/unaccounted-for.html for a start. Our Hotsos Clinic explains in complete detail. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9–12 Dallas - Next event: Miracle Database Forum, Sep 20–22 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Terrian, Tom Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Subject: waits vs. logons I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) b where a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS -- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 14 4943 1303 24 595844 588441 26 1733 1728 27 5308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719 507 56 14507 8706 59 2269 221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom
Re: waits vs. logons
yep, look at CPU used by this session in V$sesstat. CPU + WAIT should get close to the logon_secs. There are a number of reasons why it can't and shouldn't but for simplicity go with it. Anjo. On Wednesday 04 September 2002 22:59, you wrote: > I run the following query to compare the total waits for a session verses > the seconds logged on: > > select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs > from > (select sid, (sysdate - logon_time)*24*60*60 ontime >from v$session ) a, > (select sid, sum(time_waited)/100 waittime >from v$session_event >group by sid) b > where a.sid = b.sid; > > I get the following results: > >SID LONGON_SECS WAIT_SECS > -- --- -- > 1 595995 595989 > 2 595994 471204 > 3 595994 595585 > 4 595994 594580 > 5 595994 595492 > 6 595994 593639 > 9 595993 577157 > 144943 1303 > 24 595844 588441 > 261733 1728 > 275308 2478 > 29 517269 0 > 32 53 0 > 35 415158 13 > 50 140478 140371 > 51 719507 > 56 14507 8706 > 592269221 > > I am very much a novice when it comes to wait statistics. When a session, > for example #51, has been logged on for 719 seconds but has experienced > only 507 wait seconds, what did it do the rest of the time? I am assuming > some kind of work? Any way to determine what? > > Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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).
RE: Temp problem
Peter, If you just change next size to some thing else E.G from 64 to 32 then back it to previous value it will be decreased. -Original Message- Sent: Wednesday, September 04, 2002 2:28 PM To: Multiple recipients of list ORACLE-L Hi friends, My temp tablespace is showing full!! I try to wakeup smon thru oradebug wakeup 6; but getting error..I also used alter tablespace temp storage(pctincrease 0);..But it could't release space...any ideas to avoid bouncing the database??? tia peter. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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).
Re: Temp problem
Version and platform please? Early version of this ( 7.3) didn't always work properly. Jared "Peter R" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/04/2002 02:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Temp problem Hi friends, My temp tablespace is showing full!! I try to wakeup smon thru oradebug wakeup 6; but getting error..I also used alter tablespace temp storage(pctincrease 0);..But it could't release space...any ideas to avoid bouncing the database??? tia peter. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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: 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).
RE: Temp problem
Peter; Just a side question is your Temp space defined as a Temp tablespace ?? or just a Tablespace named temp that is defined Permanant ?? -Original Message- Sent: Wednesday, September 04, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Hi friends, My temp tablespace is showing full!! I try to wakeup smon thru oradebug wakeup 6; but getting error..I also used alter tablespace temp storage(pctincrease 0);..But it could't release space...any ideas to avoid bouncing the database??? tia peter. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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: Kevin Lange 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).
Re: waits vs. logons
Well, let's hope the session does some work! ;-) You can check v$sesstat for time spent on various operations, for example, 'CPU used by this session'. That certainly is to be added to the total wait time. But now that you mention it, I don't know which stats should add up to equal time logged on. __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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).
RE: automatic segment space management
We didn't implement a "Large" tablespace on any of our OLTP databases for that very reason. The tables that didn't fit into "Small" or "Medium" got their own tablespaces. There are only a few big tables on the OLTP databases and on the Data Warehouse we've moved toward giving the really big partitioned tables one tablespace/partition. It's made storage management a bit easier. -Original Message- Sent: Tuesday, September 03, 2002 6:39 PM To: Multiple recipients of list ORACLE-L So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a "Papa Bear". Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 03, 2002 3:04 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: automatic segment space management > > > Rich - Good point. Yes, I create all the tables here, at least in > production, and I probably wouldn't use autoextend if the > situation were > otherwise. The other thing to consider is if you are using > uniform extents, > by definition you have bought into the philosophy that you > can have many > extents and your database will not do a Linda Blair Exorcist > imitation on > you. If we use the guideline that the number of extents > should be not many > more than 1,000, then the 128K extent will get you 128M, > which is good for > most tables. >While we are on the subject, anyone considering switching > to LMTs should > carefully read "How to Stop Defragmenting and Start Living" > by Juan Loaiza, > Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on > http://www.hotsos.com. Trying to implement a philosophy without fully > understanding it is a recipe for failure. > > Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Miller, Jay 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).
Re: Coercion issue
Tom, I have to disagree, because, the following works: SQLWKS> select table_name, partition_name from dba_tab_partitions 2> where table_name = 'IPN_CYCLE'; TABLE_NAME PARTITION_NAME -- -- IPN_CYCLE P200203 IPN_CYCLE P200204 IPN_CYCLE P200205 IPN_CYCLE P200206 IPN_CYCLE P200207 IPN_CYCLE P200208 IPN_CYCLE P200209 IPN_CYCLE P200210 8 rows selected. SQLWKS> select table_name, partition_name from dba_tab_partitions 2> where table_name = 'IPN_CYCLE' and to_number(substr(partition_name, 6, 2)) = 9; TABLE_NAME PARTITION_NAME -- -- IPN_CYCLE P200209 1 row selected. Now, on my other databse, where LogMiner istalled creating partitioned tables: SQL af_dba@AF> select table_name, partition_name, table_owner from dba_tab_partitions; TABLE_NAME PARTITION_NAME TABLE_OWNER -- -- - - LOGSTDBY$APPLY_PROGRESSP0 SYSTEM LOGMNR_DICTSTATE$ P_LESSTHAN100 SYSTEM LOGMNR_DICTIONARY$ P_LESSTHAN100 SYSTEM LOGMNR_OBJ$P_LESSTHAN100 SYSTEM LOGMNR_USER$ P_LESSTHAN100 SYSTEM LOGMNRC_GTLO P_LESSTHAN100 SYSTEM LOGMNRC_GTCS P_LESSTHAN100 SYSTEM LOGMNRC_GSII P_LESSTHAN100 SYSTEM LOGMNR_TAB$P_LESSTHAN100 SYSTEM LOGMNR_COL$P_LESSTHAN100 SYSTEM LOGMNR_ATTRCOL$P_LESSTHAN100 SYSTEM LOGMNR_TS$ P_LESSTHAN100 SYSTEM LOGMNR_IND$P_LESSTHAN100 SYSTEM LOGMNR_TABPART$P_LESSTHAN100 SYSTEM LOGMNR_TABSUBPART$ P_LESSTHAN100 SYSTEM LOGMNR_TABCOMPART$ P_LESSTHAN100 SYSTEM LOGMNR_TYPE$ P_LESSTHAN100 SYSTEM LOGMNR_COLTYPE$P_LESSTHAN100 SYSTEM LOGMNR_ATTRIBUTE$ P_LESSTHAN100 SYSTEM LOGMNR_LOB$P_LESSTHAN100 SYSTEM LOGMNR_CDEF$ P_LESSTHAN100 SYSTEM LOGMNR_CCOL$ P_LESSTHAN100 SYSTEM LOGMNR_ICOL$ P_LESSTHAN100 SYSTEM LOGMNR_LOBFRAG$P_LESSTHAN100 SYSTEM LOGMNR_INDPART$P_LESSTHAN100 SYSTEM LOGMNR_INDSUBPART$ P_LESSTHAN100 SYSTEM LOGMNR_INDCOMPART$ P_LESSTHAN100 SYSTEM GP_CYCLE P200208BOB GP_CYCLE P200209BOB GP_CYCLE P200210BOB GP_CYCLE P200211BOB GP_CYCLE_PART_ID P200208BOB GP_CYCLE_PART_ID P200209BOB GP_CYCLE_PART_ID P200210BOB GP_CYCLE_PART_ID P200211BOB similar statement does not work: SQL af_dba@AF> select table_name, partition_name from dba_tab_partitions 2 where table_name = 'GP_CYCLE' and to_number(substr(partition_name, 6, 2)) = 9; where table_name = 'GP_CYCLE' and to_number(substr(partition_name, 6, 2)) = 9 * ERROR at line 2: ORA-01722: invalid number because there are rows with partition_name (i.e. 'P_LESSTHAN100') not convertible, when using "to_number(substr(partition_name, 6, 2))". Even when I select from user_tab_partitions, which does not show LogMiner tables/partitions, I get the same error, because user_tab_partitions view is based on the table, which contains rows with non-convertible values in partition_name column. So, the conclusion is: the auther of original e-mail has non-convertible values in partition_name column, like 'TYPEA01', which will cause an error, when doing "to_number(substr(partition_name,5,2))". Igor Neyman, OCP DBA [EMAIL PROTECTED] > > - Original Message - > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 1:14 PM > Subject: RE: Coercion issue > > > > Igor, > > > > If the buy_price_pkg.cnv_bpt_to_bp_id function ret
Re: Oracle 9i and memory required
I have Oracle 9.2 running on 256MB under XP. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 7:18 AM > Oracle 9i installation document says it requires a minimum of 512MB of > memory. Anyone got it installed and running on 256MB? > > John > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Dunn > 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: Igor Neyman 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).
RE: Oracle to publish pricing guide on Sept. 3
Not there yet - promises, promises: Oracle Software Investment Guide -- Available September 6, 2002 Visit http://www.oracle.com/corporate/pricing on Friday, September 6th to see the Oracle Software Investment Guide. Shall we start a pool of what the likely date will be? Maybe a delay of another week and it's Friday Sept 13th > -Original Message- > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 29, 2002 4:03 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Oracle to publish pricing guide on Sept. 3 > > > Oracle has moved the release date from Aug. 28 to Sept. 3. All will be > revealed . . . > > http://www.eweek.com/article2/0,3959,491399,00.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret 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).
dbbackup_begin Velpuri script
If anyone has the dbbackup_begin script from the Velpuri book/website that they are currently using and it is working properly, can you please send me a copy? I would really appreciate seeing an example that is definitely working. I realize that RMAN is available, but the person that I am trying to help doesn't want to use RMAN. -- 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).
waits vs. logons
Title: Message I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secsfrom (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) bwhere a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS-- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 14 4943 1303 24 595844 588441 26 1733 1728 27 5308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719 507 56 14507 8706 59 2269 221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom
RE: Coercion issue
Title: RE: Coercion issue It's because of the order in which conditions are evaluated in two expressions joined with AND. Example: SQL> select v from t ; V -- A 1 AA 11 SQL> select v from t where to_number (v) > 0 and v like '1%' ; V -- 1 11 SQL> select v from t where v like '1%' and to_number (v) > 0 ; select v from t where v like '1%' and to_number (v) > 0 * ERREUR à la ligne 1 : ORA-01722: Nombre non valide -Original Message- From: John Weatherman [mailto:[EMAIL PROTECTED]] I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) - - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) * ERROR at line 5: ORA-01722: invalid number
RE: Inserts are taking time !
> Some of us have work to do, you know ;) Not me, I'm independently wealthy. I just stay up late to do this stuff for fun. Jared ;) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Inserts are taking time ! Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared > the insert time to increase. I would suggest batching up the inserts, > dropping the indexes, running the inserts and re-creating the indexes. > > Chris > > -Original Message- > Sent: 04 September 2002 07:53 > To: Multiple recipients of list ORACLE-L > > > Hi All, > > We have a table which can contain more than half a million records. When we > try to insert some 10k records in the empty table it get inserted in 10 > min. but as the size increases time taken to insert also increases. After > 350,000 records it takes around an hour to insert 10k records. > There are around 15 columns in it out of which 11 are indexed. There is one > concatenated function-based index on two columns of Varchar type and two > separate index for the same two columns. > > I have checked the free space for the tablespaces to which the table and > indexes are attached to. They are in two separate tbs. > > Any clues why this is happenning. > > TIA > Marul. Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: 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).
RE: Coercion issue
Title: RE: Coercion issue It's because of the order that the conditions are checked in the joining of two comparisons with AND. Example: -Original Message- From: John Weatherman [mailto:[EMAIL PROTECTED]] Sent: mercredi, 4. septembre 2002 09:44 To: Multiple recipients of list ORACLE-L Subject: Coercion issue Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) - - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) * ERROR at line 5: ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd.
Re: hash_value and address
Pablo Rodriguez wrote: > > I see 2 different addreses for the same hash_value in > v$sqlarea. > Why do I see this? > > Here's an example: > > hash_value address > -- --- > 3749804 4064082C > 3749804 4192941C > > I wonder if someone can shed some light on this > > Txs > My understanding is that to quickly find an already parsed statement in memory, Oracle uses a hash function, which, like all hash functions is not surjective (which means that you can have x <>y and f(x)= f(y), in which case you say you have a collision). Of course once the hash value has been computed values are checked, and all strings hashing to the same value are linked - and stored at different addresses. You can also have the case in which statements are strictly identical, but, for session-related reasons, cannot be considered the same cursor (because of synonyms pointing to different objects, of similarly named tables in different schemas, or because optimizer settings have been altered at the session level, typically). In that case it's normal to have the same hash result for the same string - but you nevertheless have a collision of a sort. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: Inserts are taking time !
Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared > the insert time to increase. I would suggest batching up the inserts, > dropping the indexes, running the inserts and re-creating the indexes. > > Chris > > -Original Message- > Sent: 04 September 2002 07:53 > To: Multiple recipients of list ORACLE-L > > > Hi All, > > We have a table which can contain more than half a million records. When we > try to insert some 10k records in the empty table it get inserted in 10 > min. but as the size increases time taken to insert also increases. After > 350,000 records it takes around an hour to insert 10k records. > There are around 15 columns in it out of which 11 are indexed. There is one > concatenated function-based index on two columns of Varchar type and two > separate index for the same two columns. > > I have checked the free space for the tablespaces to which the table and > indexes are attached to. They are in two separate tbs. > > Any clues why this is happenning. > > TIA > Marul. Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Temp problem
You could offline then online the tablespace... Peter R wrote: > > Hi friends, > > My temp tablespace is showing full!! I try to wakeup smon thru > oradebug wakeup 6; but getting error..I also used > alter tablespace temp storage(pctincrease 0);..But it could't release > space...any ideas to avoid bouncing the database??? > > tia > peter. > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Peter R > 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: Suzy Vordos 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).
Re: Coercion issue
John, Maybe the buy_price_pkg.cnv_bpt_to_bp_id(5) function is returning a string? From what you provided, it looks like its returning null or maybe a blank character. Null shouldn't be a problem, but a blank string Judging by the position of the * in the error message(?) ,it seems that the problem is in converting the function result to a number. Chaim John Weatherman <[EMAIL PROTECTED]>@fatcity.com on 09/04/2002 12:43:30 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) - - 9 10 11 12 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) ERROR at line 5: ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Temp problem
add space to the temp tablespace. ALTER TABLESPACE... Scott Shafer San Antonio, TX 210.581.6217 > -Original Message- > From: Peter R [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, September 04, 2002 4:28 PM > To: Multiple recipients of list ORACLE-L > Subject: Temp problem > > > Hi friends, > > My temp tablespace is showing full!! I try to wakeup smon thru > oradebug wakeup 6; but getting error..I also used > alter tablespace temp storage(pctincrease 0);..But it could't release > space...any ideas to avoid bouncing the database??? > > > tia > peter. > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Peter R > 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: 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).
RE: Oracle to publish pricing guide on Sept. 3
Just checked this, and it is supposed to be out now on Sept 6th. Jared DENNIS WILLIAMS <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/29/2002 01:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Oracle to publish pricing guide on Sept. 3 Oracle has moved the release date from Aug. 28 to Sept. 3. All will be revealed . . . http://www.eweek.com/article2/0,3959,491399,00.asp -Original Message- Sent: Wednesday, August 28, 2002 2:02 PM To: '[EMAIL PROTECTED]' Has anyone seen this pricing guide yet? http://www.infoworld.com/articles/hn/xml/02/08/16/020816hnoraguide.xml?0816f ram -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Coercion issue
John, Oracle will apply both "where" clauses: table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) to each row at the same time. If you want to force oracle to do it sequencially (first - table_name, then - the other one), use in-line view: SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5)FROM (select partition_name from all_tab_partitions WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' and partition_name != 'TYPE01')WHERE to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) This should do it for you. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 4:59 PM Subject: RE: Coercion issue There are partitions with names that don't have the 5th and 6th characters equal to numbers, but not for this table: SQL> select partition_name from all_tab_partitions 2 where table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY'; PARTITION_NAME--TYPE01TYPE09TYPE10TYPE11TYPE12 The query should be restricting things to only those partition names for the one table. John P Weatherman Database Administrator Replacements Ltd. -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 1:14 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Coercion issue John, Do you have other partitions with such names, that 5th and 6th characters are not convertible into numbers? Like: 'TYPEA1'? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 12:43 PM Subject: Coercion issue Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01'SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)- - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5)SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) *ERROR at line 5:ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd.
RE: PCTUSED - when is block added to freelist?
The MetaLink note didn't seem to make it clear, at least not to me.
I ran the following test which initially filled 7 blocks. Adding one
more row as seen in the test, causes additional blocks to be
added with the current PCTFREE and PCTUSED settings.
set echo on
drop table fb;
-- create in SYSTEM tablespace, as it is
-- the only dictionary managed TBS in the DB
create table fb(
mydata varchar2(100)
)
tablespace system
pctused 20
pctfree 80
storage( initial 8k next 8k pctincrease 0 )
/
begin
-- maximum rows that will fit in initial blocks
for r in 1..1029
loop
insert into fb(mydata)
values(rpad('',100,'X'));
end loop;
commit;
end;
/
analyze table fb compute statistics;
select blocks, empty_blocks
from user_tables
where table_name = 'FB'
/
insert into fb(mydata)
values(rpad('',100,'X'));
commit;
analyze table fb compute statistics;
select blocks, empty_blocks
from user_tables
where table_name = 'FB'
/
set echo off
Here are the results:
=
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> drop table fb;
Table dropped.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> -- create in SYSTEM
tablespace, as it is
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> -- the only dictionary
managed TBS in the DB
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> create table fb(
09:32:23 2 mydata varchar2(100)
09:32:23 3 )
09:32:23 4 tablespace system
09:32:23 5 pctused 20
09:32:23 6 pctfree 80
09:32:23 7 storage( initial 8k next 8k pctincrease 0 )
09:32:23 8 /
Table created.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> begin
09:32:23 2 -- maximum rows that will fit in initial blocks
09:32:23 3 for r in 1..1029
09:32:23 4 loop
09:32:23 5 insert into fb(mydata)
09:32:23 6 values(rpad('',100,'X'));
09:32:23 7 end loop;
09:32:23 8 commit;
09:32:23 9 end;
09:32:23 10 /
PL/SQL procedure successfully completed.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> analyze table fb
compute statistics;
Table analyzed.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> select blocks,
empty_blocks
09:32:23 2 from user_tables
09:32:23 3 where table_name = 'FB'
09:32:23 4 /
BLOCKS EMPTY BLOCKS
---
70
1 row selected.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> insert into fb(mydata)
09:32:23 2 values(rpad('',100,'X'));
1 row created.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> commit;
Commit complete.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> analyze table fb
compute statistics;
Table analyzed.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> select blocks,
empty_blocks
09:32:23 2 from user_tables
09:32:23 3 where table_name = 'FB'
09:32:23 4 /
BLOCKS EMPTY BLOCKS
---
123
1 row selected.
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:32:23 rsysdevdb.radisys.com - jkstill@dv01 SQL> set echo off
=
I next added the following line just before the final row is inserted:
alter table fb pctused 80 pctfree 20;
No new blocks were added this time, suggesting that the blocks were put on
the
freelist at the time of INSERT. A more thorough explanation requires
running a
trace, but this was enough for me.
=
09:34:49 rsysdevdb.radisys.com - jkstill@dv01 SQL> @fb2
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> drop table fb;
Table dropped.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> -- create in SYSTEM
tablespace, as it is
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> -- the only dictionary
managed TBS in the DB
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> create table fb(
09:34:50 2 mydata varchar2(100)
09:34:50 3 )
09:34:50 4 tablespace system
09:34:50 5 pctused 20
09:34:50 6 pctfree 80
09:34:50 7 storage( initial 8k next 8k pctincrease 0 )
09:34:50 8 /
Table created.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> begin
09:34:50 2 -- maximum rows that will fit
RE: Coercion issue
Sory Igor - I misread who sent the original email. John, If the buy_price_pkg.cnv_bpt_to_bp_id function returns a number, this makes perfect sense. Oracle is attempting to convert the partition_name column to a number before your instr function gets to do its magic. Try doing the following: SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and substr(partition_name,5,2) = to_char(buy_price_pkg.cnv_bpt_to_bp_id(5)) / I bet you a dollar it works. Remember, when it comes to comparisons, Oracle will convert the database column to match the literal data type. In your case, the character values in the 'partition_name' column will not convert to a number. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, September 04, 2002 1:43 PM To: Multiple recipients of list ORACLE-L Under 8.1.5 partition_name is varchar2(30), as most of the names in data dictionary. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:03 PM > Unless my memory is failing (more than likely) I thought you couldn't use > the partition_name like that in the where clause of patitioned tables as it > is a LONG ?? > > Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i. > I ran into the same problem a while ago. > > HTH > > -Original Message- > Sent: 04 September 2002 17:44 > To: Multiple recipients of list ORACLE-L > > > Hi all, > > I'm writing a package to manipulate a partitioned table for the duhvelopers > and have run into > a weird query that I can't figure out. I can convert a substring to a > number in a select clause, > but as soon as I try to use that same number in the where clause, the thing > chokes. Has > anyone else seen anything like this? > > I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query > is a custom > function that returns a number corresponding with which partition is > current, old, next, > etc. > > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4*and partition_name != 'TYPE01' > SQL> / > > TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) > - - > 9 9 >10 9 >11 9 >12 9 > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4 and partition_name != 'TYPE01' > 5* and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > SQL> / > and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > * > ERROR at line 5: > ORA-01722: invalid number > > TIA, > > John P Weatherman > Database Administrator > Replacements Ltd. > > > > * > > The information contained in this communication is > confidential, is intended only for the use of the recipient > named above, and may be legally privileged. > If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, > distribution, or copying of this communication is strictly > prohibited. > If you have received this communication in error, > please re-send this communication to the sender and > delete the original message or any copy of it from your > computer system. Thank You. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robertson Lee - lerobe > 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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, Califor
RE: Coercion issue
Igor, If the buy_price_pkg.cnv_bpt_to_bp_id function returns a number, this makes perfect sense. Oracle is attempting to convert the partition_name column to a number before your instr function gets to do its magic. Try doing the following: SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and substr(partition_name,5,2) = to_char(buy_price_pkg.cnv_bpt_to_bp_id(5)) / I bet you a dollar it works. Remember, when it comes to comparisons, Oracle will convert the database column to match the literal data type. In your case, the character values in the 'partition_name' column will not convert to a number. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, September 04, 2002 1:43 PM To: Multiple recipients of list ORACLE-L Under 8.1.5 partition_name is varchar2(30), as most of the names in data dictionary. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:03 PM > Unless my memory is failing (more than likely) I thought you couldn't use > the partition_name like that in the where clause of patitioned tables as it > is a LONG ?? > > Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i. > I ran into the same problem a while ago. > > HTH > > -Original Message- > Sent: 04 September 2002 17:44 > To: Multiple recipients of list ORACLE-L > > > Hi all, > > I'm writing a package to manipulate a partitioned table for the duhvelopers > and have run into > a weird query that I can't figure out. I can convert a substring to a > number in a select clause, > but as soon as I try to use that same number in the where clause, the thing > chokes. Has > anyone else seen anything like this? > > I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query > is a custom > function that returns a number corresponding with which partition is > current, old, next, > etc. > > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4*and partition_name != 'TYPE01' > SQL> / > > TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) > - - > 9 9 >10 9 >11 9 >12 9 > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4 and partition_name != 'TYPE01' > 5* and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > SQL> / > and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > * > ERROR at line 5: > ORA-01722: invalid number > > TIA, > > John P Weatherman > Database Administrator > Replacements Ltd. > > > > * > > The information contained in this communication is > confidential, is intended only for the use of the recipient > named above, and may be legally privileged. > If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, > distribution, or copying of this communication is strictly > prohibited. > If you have received this communication in error, > please re-send this communication to the sender and > delete the original message or any copy of it from your > computer system. Thank You. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robertson Lee - lerobe > 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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Temp problem
Hi friends, My temp tablespace is showing full!! I try to wakeup smon thru oradebug wakeup 6; but getting error..I also used alter tablespace temp storage(pctincrease 0);..But it could't release space...any ideas to avoid bouncing the database??? tia peter. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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).
RE: Any woraround for this ....?
Well, I did say that this was the 'heart' of the program.
A full fledged one with online help is about 420 lines.
It also creates sqlloader files for each of the tables dumped.
If you download the PDBA toolkit from:
http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/
you will find the sqlunldr.pl script in the toolkit.
If you also want detailed instructions on setting up the
toolkit, there is a book available at:
http://www.oreilly.com/catalog/oracleperl/
:)
Jared
"Robson, Peter" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/04/2002 10:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Any woraround for this ?
Hee hee hee! I just LOVE that phrase 'That''s all there is to it'! (notice
my correct SQL syntax here...)
Ummm, errr, well, not quite all. Just a tiny bit of in-line documentation
would a) help those of us thinking about embarking on Perl, and b) further
enhance your evangelical status, Jared! (Yes yes, it could probably be all
worked out, but lets save some time here...)
peter
edinburgh
> -Original Message-
> From: Jared Still [mailto:[EMAIL PROTECTED]]
> Sent: 04 September 2002 17:14
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Any woraround for this ?
>
>
>
> Muths,
>
> I hope I'm not beginning to sound like a nag, but PL/SQL is
> not really a great tool for what you're trying to do.
>
> I *like* PL/SQL, but it does have its limits. This is one of them.
>
> What you're trying to do in PL/SQL can be done rather simply
> in Perl. The heart of a program to do this in Perl looks something
> like this:
>
> ...
> my $tabsql = q{
>select table_name
>from dba_tables
>where owner = 'SCOTT'
> };
>
> my $sth = $dbh->prepare($tabsql);
> $sth->execute;
>
> while ( my @array = $sth->fetchrow_array ) {
>my $tableName = $array[0];
>my $file = lc($tableName) . '.txt';
>open(DUMP, ">$file" ) || die "could not open $file\n";
>my $dumpsql = qq{
> select *
> from scott.$tableName
>};
>my $dumpsth = $dbh->prepare($dumpsql);
>$dumpsth->execute;
>while ( my $aref = $dumpsth->fetchrow_arrayref ) {
> print DUMP q{'}, join(q{','},@{$aref}),q{'},qq{\n};
>}
> }
>
> That's all there is to it. You will write considerably more code to
> do that in PL/SQL, and it won't be nearly as fast.
>
> Jared - OCP and Part Time Perl Evangelist ;)
>
>
> On Wednesday 04 September 2002 03:48, Muthaiah, VSNL wrote:
> > Hi,
> >
> > I'm writing a procedure/script for extract the data of all
> the tables in a
> > schema. When I am trying to spool/write into a file, I am
> getting the
> > following error.
> >
> > ORA-20001: -2ORA-2: ORU-10028: line length
> overflow, limit of 255
> > bytes per line ORA-06512: at "SCOTT.EXTRACT", line 115
> > ORA-06512: at line 1
> > First I tried to spool to a file.But got the error line
> length overflow. I
> > have tried using the UTL_FILE option also. But getting the
> same error. Can
> > anyone in the list has any work around for this?
> >
> > Thanks in Advance,
> >
> >
> > Muths
>
>
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: quoted-printable
> Content-Description:
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> 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).
>
*
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you have received it in error
and any copying, distribution or other use of any part of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent those of the British
Geological Survey. The security of e-mail communication cannot be
guaranteed and the BGS accepts no liability for claims arising as a
result of the use of this medium to transmit messages from or to the
BGS. The BGS cannot accept any responsibility for viruses, so please
scan all attachments.http://www.bgs.ac.uk
*
RE: Coercion issue
There are partitions with names that don't have the 5th and 6th characters equal to numbers, but not for this table: SQL> select partition_name from all_tab_partitions 2 where table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY'; PARTITION_NAME--TYPE01TYPE09TYPE10TYPE11TYPE12 The query should be restricting things to only those partition names for the one table. John P Weatherman Database Administrator Replacements Ltd. -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 1:14 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Coercion issue John, Do you have other partitions with such names, that 5th and 6th characters are not convertible into numbers? Like: 'TYPEA1'? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 12:43 PM Subject: Coercion issue Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01'SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)- - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5)SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) *ERROR at line 5:ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd.
Re: ssh client for Windoze
Pete, The one that hasn't been mentioned yet is command line ssh as provided by Cygwin: http://sources.redhat.com/cygwin/ Install cygwin, get a unix like environment on your PC, complete with ksh command window. Command line ssh works just fine, be sure to set your TERM to cygwin. If not already installed on your AIX/Solaris boxes, let me know, and I'll send the cygwin terminfo to you. Jared Peter Barnett <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/04/2002 09:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:ssh client for Windoze We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: ssh client for Windoze
You can try PuTTY http://www.chiark.greenend.org.uk/~sgtatham/putty/ It uses multiple protocols for telnet or ssh. I connect with around 50 different UNIX servers over our vpn. It handles Sun, HP, IBM, Linux with no problems. It's interface is configurable. You can customize connection information, pass parameters for execution, set up custom foreground/background colors for different sessions. It allows multiple sessions in different windows and the clipboard is active between windows, UNIX, and between sessions. Finally IT'S FREE Check it out Rodd Holman On Wed, 2002-09-04 at 11:48, Peter Barnett wrote: We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett 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).
Re: Data mining data access
On Wed, Sep 04, 2002 at 09:03:29AM -0800, Tim Gorman wrote: > Definitely this is true of SAS and SPSS, but don't worry about those tools > not getting used. Pretty sure sas can read db directly with one of their plugins: http://www.sas.com/products/access/index.html though I haven't messed with it in years, it definitely worked to whatever db I was using at the time. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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).
RE: ssh client for Windoze
SecureCRT is good. You can also check out putty (free) as your SSH client. I am happy with both. -Original Message- Sent: Wednesday, September 04, 2002 1:24 PM To: Multiple recipients of list ORACLE-L > Has anyone used an Windows ssh client in a large environment (50 +/- > Unix servers, 300+ instances) that they would recommend? SecureCRT at http://www.vandyke.com. $50.00/license for 200 to 499 users. Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers 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: Ji, Richard 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).
Re: ssh client for Windoze
We are using Putty and WinSCP2 http://winscp.vse.cz/eng/index.php JP On Wednesday 04 September 2002 18:48, you wrote: > We are looking for an ssh client for our desktops. > Our early testing has been with a product from > F-Secure which has some major configuration issues > when used in an environment connecting to multiple > Unix servers and multiple instances. > > Open source is out of the question. > > Has anyone used an Windows ssh client in a large > environment (50 +/- Unix servers, 300+ instances) that > they would recommend? > > > > = > Pete Barnett > Lead Database Administrator > The Regence Group > [EMAIL PROTECTED] > > __ > Do You Yahoo!? > Yahoo! Finance - Get real-time stock quotes > http://finance.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
RE: Any woraround for this ....?
Hee hee hee! I just LOVE that phrase 'That''s all there is to it'! (notice
my correct SQL syntax here...)
Ummm, errr, well, not quite all. Just a tiny bit of in-line documentation
would a) help those of us thinking about embarking on Perl, and b) further
enhance your evangelical status, Jared! (Yes yes, it could probably be all
worked out, but lets save some time here...)
peter
edinburgh
> -Original Message-
> From: Jared Still [mailto:[EMAIL PROTECTED]]
> Sent: 04 September 2002 17:14
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Any woraround for this ?
>
>
>
> Muths,
>
> I hope I'm not beginning to sound like a nag, but PL/SQL is
> not really a great tool for what you're trying to do.
>
> I *like* PL/SQL, but it does have its limits. This is one of them.
>
> What you're trying to do in PL/SQL can be done rather simply
> in Perl. The heart of a program to do this in Perl looks something
> like this:
>
> ...
> my $tabsql = q{
>select table_name
>from dba_tables
>where owner = 'SCOTT'
> };
>
> my $sth = $dbh->prepare($tabsql);
> $sth->execute;
>
> while ( my @array = $sth->fetchrow_array ) {
>my $tableName = $array[0];
>my $file = lc($tableName) . '.txt';
>open(DUMP, ">$file" ) || die "could not open $file\n";
>my $dumpsql = qq{
> select *
> from scott.$tableName
>};
>my $dumpsth = $dbh->prepare($dumpsql);
>$dumpsth->execute;
>while ( my $aref = $dumpsth->fetchrow_arrayref ) {
> print DUMP q{'}, join(q{','},@{$aref}),q{'},qq{\n};
>}
> }
>
> That's all there is to it. You will write considerably more code to
> do that in PL/SQL, and it won't be nearly as fast.
>
> Jared - OCP and Part Time Perl Evangelist ;)
>
>
> On Wednesday 04 September 2002 03:48, Muthaiah, VSNL wrote:
> > Hi,
> >
> > I'm writing a procedure/script for extract the data of all
> the tables in a
> > schema. When I am trying to spool/write into a file, I am
> getting the
> > following error.
> >
> > ORA-20001: -2ORA-2: ORU-10028: line length
> overflow, limit of 255
> > bytes per line ORA-06512: at "SCOTT.EXTRACT", line 115
> > ORA-06512: at line 1
> > First I tried to spool to a file.But got the error line
> length overflow. I
> > have tried using the UTL_FILE option also. But getting the
> same error. Can
> > anyone in the list has any work around for this?
> >
> > Thanks in Advance,
> >
> >
> > Muths
>
>
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: quoted-printable
> Content-Description:
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> 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).
>
*
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you have received it in error
and any copying, distribution or other use of any part of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent those of the British
Geological Survey. The security of e-mail communication cannot be
guaranteed and the BGS accepts no liability for claims arising as a
result of the use of this medium to transmit messages from or to the
BGS. The BGS cannot accept any responsibility for viruses, so please
scan all attachments.http://www.bgs.ac.uk
*
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robson, Peter
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).
ORA-01918: user 'SYSTEM' does not exist
Title: Message Hello, Oracle 8.1.7.4 on HP-UX 11.0 A colleague of mine upgraded this database from 8.1.6 to 8.1.7.4. Now when I tried to change system's password, I get the above error. To add to it, when I try to do a full export, I get 'Abort(coredump)' I can login as sys but not as system. V$version says that the database is on 8.1.7.4 TIA Prakash
Re: Coercion issue
Under 8.1.5 partition_name is varchar2(30), as most of the names in data dictionary. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:03 PM > Unless my memory is failing (more than likely) I thought you couldn't use > the partition_name like that in the where clause of patitioned tables as it > is a LONG ?? > > Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i. > I ran into the same problem a while ago. > > HTH > > -Original Message- > Sent: 04 September 2002 17:44 > To: Multiple recipients of list ORACLE-L > > > Hi all, > > I'm writing a package to manipulate a partitioned table for the duhvelopers > and have run into > a weird query that I can't figure out. I can convert a substring to a > number in a select clause, > but as soon as I try to use that same number in the where clause, the thing > chokes. Has > anyone else seen anything like this? > > I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query > is a custom > function that returns a number corresponding with which partition is > current, old, next, > etc. > > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4*and partition_name != 'TYPE01' > SQL> / > > TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) > - - > 9 9 >10 9 >11 9 >12 9 > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4 and partition_name != 'TYPE01' > 5* and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > SQL> / > and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > * > ERROR at line 5: > ORA-01722: invalid number > > TIA, > > John P Weatherman > Database Administrator > Replacements Ltd. > > > > * > > The information contained in this communication is > confidential, is intended only for the use of the recipient > named above, and may be legally privileged. > If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, > distribution, or copying of this communication is strictly > prohibited. > If you have received this communication in error, > please re-send this communication to the sender and > delete the original message or any copy of it from your > computer system. Thank You. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robertson Lee - lerobe > 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: Igor Neyman 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).
RE: Coercion issue
D'oh I was thinking of the high value column. Apologies Lee -Original Message- Sent: 04 September 2002 18:03 To: Multiple recipients of list ORACLE-L Unless my memory is failing (more than likely) I thought you couldn't use the partition_name like that in the where clause of patitioned tables as it is a LONG ?? Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i. I ran into the same problem a while ago. HTH -Original Message- Sent: 04 September 2002 17:44 To: Multiple recipients of list ORACLE-L Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4*and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) - - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) * ERROR at line 5: ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd. * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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: Robertson Lee - lerobe 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).
Re: Coercion issue
John, Do you have other partitions with such names, that 5th and 6th characters are not convertible into numbers? Like: 'TYPEA1'? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 12:43 PM Subject: Coercion issue Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01'SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)- - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5)SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) *ERROR at line 5:ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd.
RE: ssh client for Windoze
Perhaps PuTTY, which isn't Open source, but is free and includes the source: http://www.chiark.greenend.org.uk/~sgtatham/putty/ Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Peter Barnett [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 04, 2002 11:48 AM > To: Multiple recipients of list ORACLE-L > Subject: ssh client for Windoze > > > We are looking for an ssh client for our desktops. > Our early testing has been with a product from > F-Secure which has some major configuration issues > when used in an environment connecting to multiple > Unix servers and multiple instances. > > Open source is out of the question. > > Has anyone used an Windows ssh client in a large > environment (50 +/- Unix servers, 300+ instances) that > they would recommend? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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).
RE: ssh client for Windoze
TeraTerm (does not support ssh2) Hummingbird Reflection PuTTY (current fave) Scott Shafer San Antonio, TX 210.581.6217 > -Original Message- > From: Peter Barnett [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, September 04, 2002 11:48 AM > To: Multiple recipients of list ORACLE-L > Subject: ssh client for Windoze > > We are looking for an ssh client for our desktops. > Our early testing has been with a product from > F-Secure which has some major configuration issues > when used in an environment connecting to multiple > Unix servers and multiple instances. > > Open source is out of the question. > > Has anyone used an Windows ssh client in a large > environment (50 +/- Unix servers, 300+ instances) that > they would recommend? > > > > = > Pete Barnett > Lead Database Administrator > The Regence Group > [EMAIL PROTECTED] > > __ > Do You Yahoo!? > Yahoo! Finance - Get real-time stock quotes > http://finance.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Peter Barnett > 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: 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).
Re: ssh client for Windoze
> Has anyone used an Windows ssh client in a large environment (50 +/- > Unix servers, 300+ instances) that they would recommend? SecureCRT at http://www.vandyke.com. $50.00/license for 200 to 499 users. Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers 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).
Re: Coercion issue
The 'where' clause may be applied to many more rows than appear in the result set (to which the functions on the selected columns will be applied). Thus one of the rows that is being encountered does convert to a valid number. Basic example follows: SQL> select to_number(x) from 2( select '1' x from dual ); TO_NUMBER(X) 1 SQL> select to_number(x) from 2 ( select '1' x from dual union all select 'X' from dual ) 3 where x = '1' 4 / TO_NUMBER(X) 1 SQL> select to_number(x) from 2 ( select '1' x from dual union all select 'X' from dual ) 3 where x is not null 4 / ERROR: ORA-01722: invalid number no rows selected hth connor --- John Weatherman <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm writing a package to manipulate a partitioned > table for the duhvelopers > and have run into > a weird query that I can't figure out. I can > convert a substring to a > number in a select clause, > but as soon as I try to use that same number in the > where clause, the thing > chokes. Has > anyone else seen anything like this? > > I'm on 9.0.1.3, Solaris 8. The > buy_price_pkg.cnv_bpt_to_bp_id in the query > is a custom > function that returns a number corresponding with > which partition is > current, old, next, > etc. > > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = > 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4*and partition_name != 'TYPE01' > SQL> / > > TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) > BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) > - > - > 9 > 9 >10 > 9 >11 > 9 >12 > 9 > > SQL> SELECT to_number(substr(partition_name,5,2)), > buy_price_pkg.cnv_bpt_to_bp_id(5) > 2 FROM all_tab_partitions > 3 WHERE table_name = > 'BUY_PRICE_PIECE_TYPE_HISTORY' > 4 and partition_name != 'TYPE01' > 5* and to_number(substr(partition_name,5,2)) > = > buy_price_pkg.cnv_bpt_to_bp_id(5) > SQL> / > and to_number(substr(partition_name,5,2)) = > buy_price_pkg.cnv_bpt_to_bp_id(5) > * > ERROR at line 5: > ORA-01722: invalid number > > TIA, > > John P Weatherman > Database Administrator > Replacements Ltd. > > = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
RE: Any woraround for this ....?
You can use the sqlplus parameter "LINESIZE" to specify the size of each line for output. I have used it in the past with lengths of 5000 and more, and it has worked, including spooling to files. The only drawback in this case is that it "adjusts" each line to LINESIZE (adds spaces to the right where the length is less than that of the parameter LINESIZE), but you can get around with this by setting another sqlplus variable "TRIMSPOOL" to ON. HTH, Manav. At 05:48 AM 9/4/2002 -0800, you wrote: Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message- From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AM To: Multiple recipients of list ORACLE-L Subject: Any woraround for this ? Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per line ORA-06512: at "SCOTT.EXTRACT", line 115 ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
Re: Data mining data access
Definitely this is true of SAS and SPSS, but don't worry about those tools not getting used. I've often observed that separating a statistical analyst from SAS/SPSS requires a crow-bar... :-) They'll find a way to get that data, despite incredibly foolish limitations such as 8Kb record-width and 8-char fieldnames... I imagine that Oracle's old Darwin product (purchased from Thinking Machines) could access relational data directly, but that might be an invalid assumption also. Anyway, Oracle has apparently decided to get out of the business of creating DM/OLAP tools and get into providing the APIs to create DM/OLAP applications with the OLAP and Data Mining options. The reasoning is to embed data from data mining right into that sales-call application, so that the salesperson can see the prospects "score" in real-time within the application rather than having to fire up a separate console. Sorry for no help, but that's my $0.02. Looking forward to hearing more on this topic... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 8:53 AM > > I have a question for anyone who has used a data mining tool. My > understanding is that most data mining tools do not access your database > directly, but require you to unload the data and put it into some specific > flat file formats before the data mining tool can search your data. Is this > true? This sounds very time-consuming and inconvenient. I am concerned that > the tool won't get used very much because of the effort involved. Does > anybody know of a data mining tool that can directly read database tables? > If so, is there any standard data model involved? Here are my theories for > why data mining tools don't read database tables directly: > 1. Data warehouse schemas are not standard. Some are simple star-schemas, > but others aren't. > 2. In-memory tables may perform associative look-up faster than database > queries. > > Your ideas are appreciated. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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).
Re: Inserts are taking time !
On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared > the insert time to increase. I would suggest batching up the inserts, > dropping the indexes, running the inserts and re-creating the indexes. > > Chris > > -Original Message- > Sent: 04 September 2002 07:53 > To: Multiple recipients of list ORACLE-L > > > Hi All, > > We have a table which can contain more than half a million records. When we > try to insert some 10k records in the empty table it get inserted in 10 > min. but as the size increases time taken to insert also increases. After > 350,000 records it takes around an hour to insert 10k records. > There are around 15 columns in it out of which 11 are indexed. There is one > concatenated function-based index on two columns of Varchar type and two > separate index for the same two columns. > > I have checked the free space for the tablespaces to which the table and > indexes are attached to. They are in two separate tbs. > > Any clues why this is happenning. > > TIA > Marul. Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
ssh client for Windoze
We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett 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).
Re: OPS Sequences: nocache == order ??
Thinking more about it last night...
Since Oracle's theoretical limit is 16384 commits
per second, I imagine that you could safely make the sequence recycle at
(or 16384 or 9) and limit the number of digits contributed by the
sequence to 4-5...
Also, you can get rid of the "wasteful" query on
DUAL by including either X$DUAL (referencing previous ORACLE-L threads
on DUAL vs X$DUAL plus good related stuff on http://www.optimaldba.com) or just use
centi-second info from V$TIMER instead of X$DUAL. Either way makes for
zero logical reads and (most importantly) zero physical reads thus zero
pings...
- Original Message -
From:
Gogala, Mladen
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 04, 2002 9:03
AM
Subject: RE: OPS Sequences: nocache ==
order ??
Neat
idea. Thanks!
-Original Message-From: Tim Gorman
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002
2:28 AMTo: Multiple recipients of list
ORACLE-LSubject: Re: OPS Sequences: nocache == order
??
Mladen,
Is there any way to have developers/users
access the sequence via a function, instead of accessing the sequence
directly?
If so, then perhaps you could modify the
sequence to add the temporal component, while maintaining the use of a
cached sequence for uniqueness? Such as:
SQL> create or replace function
gen_seqq(in_seq in number)
2 return number 3
as 4 v_return_nbr
number; 5 begin 6
select to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
into v_return_nbr
8 from
dual; 9 return
v_return_nbr; 10* end gen_seqq;SQL> /
Function created.
SQL> create table x (y
number);
Table created.
SQL> create sequence xq;
Sequence created.
SQL> insert into x values
(gen_seqq(xq.nextval));
1 row created.
SQL>
Big and ugly numbers yes, but I think some
folks get a strange thrill out of 20-digit numbers.
It fits the requirement of being temporal (to
the second, at least) and unique. You can throw in HSECS from V$TIMER
if someone gets picky enough to want to go to the centi-second level as
well. Yeah, and you can throw in USERENV('INSTANCEID') too, just for
some real OPS/RAC-ness! Best of all, it fits the DBA-half of your
brain by being fully cacheable and non-pinging...
...of course, you can embed the use of the
SEQUENCE object inside the function; I left it on the "outside" in
this example just to make it more flexible with regard to which sequence
object it uses...
If they don't like the idea of using a stored
function to get the sequence number, then tell 'em that "it's more ANSI
standard that way" and it's "database independent". That gets 'em
every time...
Hope this helps...
-Tim
- Original Message -
From: "Mladen Gogala" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Tuesday, September 03, 2002 5:54
PM
Subject: Re: OPS Sequences: nocache == order
??
> Unfortunately, we have an application dependency and I was
required > to come up with a quick & dirty fix. Thanks for your
reply.> > > On 2002.09.03 19:10 Anjo Kolk
wrote:> > > > If you run OPS and specify order, it works
like no cache. > > > > My question to you: "Why cripple
OPS and your business performance by having > > this requirement
?" Spending a few bucks to get rid of this dependency will > >
improve the performance, until you run in to the next problem ;-)>
> > > Anjo.> > > > > >
> > > > > On Wednesday 04 September 2002 00:00, you
wrote:> > > I'm managing an OPS configuration (4x HP 9000/N,
HP-UX 11/64 , RDBMS> > > 8.1.7.1)> > > and I'm
having an application dependency on a temporal order of sequence>
> > numbers.> > > With OPS that becomes a problem because
each node caches a set of sequence> > > numbers> >
> (20 by default). Oracle has an option, specifically for that
situation,> > > namely "ORDER".> > > My question
is whether ORDER is the same thing as NOCACHE and whether it is> >
> possible> > > to have a NOCACHE sequence which will return
numbers in an incorrect order> > > (larger number> >
> before the smaller one).> > > Please, o OPS gods and
godesses, help me out and I'll sacrifice you a beer> > > when I
see you.> > > Mladen Gogala> > > > >
> --> > Please see the official ORACLE-L FAQ: http://www.orafaq.com> >
--> > Author: Anjo Kolk> > INET: [EMAIL PROTECTED]> >
> > Fat City Network Services -- (858)
538
RE: ssh client for Windoze
we here use product from www.ssh.com works fine for us across AIX, DG-UX, Solaris, Linux ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Coercion issue
Unless my memory is failing (more than likely) I thought you couldn't use the partition_name like that in the where clause of patitioned tables as it is a LONG ?? Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i. I ran into the same problem a while ago. HTH -Original Message- Sent: 04 September 2002 17:44 To: Multiple recipients of list ORACLE-L Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4*and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) - - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) * ERROR at line 5: ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd. * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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).
Re: Any woraround for this ....?
Muths,
I hope I'm not beginning to sound like a nag, but PL/SQL is
not really a great tool for what you're trying to do.
I *like* PL/SQL, but it does have its limits. This is one of them.
What you're trying to do in PL/SQL can be done rather simply
in Perl. The heart of a program to do this in Perl looks something
like this:
...
my $tabsql = q{
select table_name
from dba_tables
where owner = 'SCOTT'
};
my $sth = $dbh->prepare($tabsql);
$sth->execute;
while ( my @array = $sth->fetchrow_array ) {
my $tableName = $array[0];
my $file = lc($tableName) . '.txt';
open(DUMP, ">$file" ) || die "could not open $file\n";
my $dumpsql = qq{
select *
from scott.$tableName
};
my $dumpsth = $dbh->prepare($dumpsql);
$dumpsth->execute;
while ( my $aref = $dumpsth->fetchrow_arrayref ) {
print DUMP q{'}, join(q{','},@{$aref}),q{'},qq{\n};
}
}
That's all there is to it. You will write considerably more code to
do that in PL/SQL, and it won't be nearly as fast.
Jared - OCP and Part Time Perl Evangelist ;)
On Wednesday 04 September 2002 03:48, Muthaiah, VSNL wrote:
> Hi,
>
> I'm writing a procedure/script for extract the data of all the tables in a
> schema. When I am trying to spool/write into a file, I am getting the
> following error.
>
> ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255
> bytes per line ORA-06512: at "SCOTT.EXTRACT", line 115
> ORA-06512: at line 1
> First I tried to spool to a file.But got the error line length overflow. I
> have tried using the UTL_FILE option also. But getting the same error. Can
> anyone in the list has any work around for this?
>
> Thanks in Advance,
>
>
> Muths
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
Re: Oracle on windows vs Redhat
ora ora Here it is once again: http://www.cuug.ab.ca/~leblancj/nt_to_unix.html hth mkb --- Ron Rogers <[EMAIL PROTECTED]> wrote: > oraoraora, > Yesterday there was a posting of a URL that pointed > you to a research > paper that supplied just the answers you want. > Check the archives for the email message. > Ron > ROR mª¿ªm > >>> [EMAIL PROTECTED] 09/04/02 03:43AM >>> > Guys, > > I have heard from people in the forum that Oracle > performs well on > Linux/Solaris than Windows.Can someone give me > docs/papers which > proves the same.I need this to convince my manager. > > Our DB is on Win2K now.we thought of moving to > Redhat. > > TIA. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: oraora oraora > 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: Ron Rogers > 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb 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).
Coercion issue
Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out. I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes. Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8. The buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4* and partition_name != 'TYPE01'SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)- - 9 9 10 9 11 9 12 9 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) 2 FROM all_tab_partitions 3 WHERE table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' 4 and partition_name != 'TYPE01' 5* and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5)SQL> / and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) *ERROR at line 5:ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd.
RE: PCTUSED - when is block added to freelist?
See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick "Miller, Jay" house.com> cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 P
Re: Oracle on Win platforms
9.2.0.1 EE (with OEM and 9iAS) on a Dell PPX Latitude laptop running XP Pro on 500Mhz CPU, 384 Mb RAM, 20Gb disk. Zero problems during full install (including DBCA). Works fine for noodling around, but I get occasional "low on virtual memory" messages... :-) - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 7:38 AM > I have a Dell 8200 running XP Prof. and have installed 9.2 on it. > > My $0.02 worth, > > Ken Janusz, CPIM > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 6:58 AM > > > > I've got 8.1.7 on my PC. One of the other DBAs has put 9i on his. > > > > -Original Message- > > Sent: 04 September 2002 12:44 > > To: Multiple recipients of list ORACLE-L > > > > > > Just a quickie, please, folks - > > > > I have used Win NT4 for years, but have now got Win98SE on a second PC. > > (Don't mention XP...) > > > > Which versions of Oracle will load to 98? Single user only, stand-alone > > machine (no networking). On attempting to load 7.3.4 an 'unsuported' > message > > pops up, which wasn't exactly confidence inspiring... > > > > thanks, > > > > peter > > edinburgh > > > > > > * > > This e-mail message, and any files transmitted with it, are > > confidential and intended solely for the use of the addressee. If > > this message was not addressed to you, you have received it in error > > and any copying, distribution or other use of any part of it is > > strictly prohibited. Any views or opinions presented are solely those > > of the sender and do not necessarily represent those of the British > > Geological Survey. The security of e-mail communication cannot be > > guaranteed and the BGS accepts no liability for claims arising as a > > result of the use of this medium to transmit messages from or to the > > BGS. The BGS cannot accept any responsibility for viruses, so please > > scan all attachments.http://www.bgs.ac.uk > > * > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Robson, Peter > > 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). > > > > > > > > > > The information contained in this communication is > > confidential, is intended only for the use of the recipient > > named above, and may be legally privileged. > > If the reader of this message is not the intended > > recipient, you are hereby notified that any dissemination, > > distribution, or copying of this communication is strictly > > prohibited. > > If you have received this communication in error, > > please re-send this communication to the sender and > > delete the original message or any copy of it from your > > computer system. Thank You. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Robertson Lee - lerobe > > 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: KENNETH JANUSZ > 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 s
RE: OPS Sequences: nocache == order ??
Neat
idea. Thanks!
-Original Message-From: Tim Gorman
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 2:28
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
OPS Sequences: nocache == order ??
Mladen,
Is there any way to have developers/users access
the sequence via a function, instead of accessing the sequence
directly?
If so, then perhaps you could modify the sequence
to add the temporal component, while maintaining the use of a cached
sequence for uniqueness? Such as:
SQL> create or replace function
gen_seqq(in_seq in number)
2 return number 3
as 4 v_return_nbr number;
5 begin 6
select to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
into v_return_nbr
8 from dual;
9 return
v_return_nbr; 10* end gen_seqq;SQL> /
Function created.
SQL> create table x (y
number);
Table created.
SQL> create sequence xq;
Sequence created.
SQL> insert into x values
(gen_seqq(xq.nextval));
1 row created.
SQL>
Big and ugly numbers yes, but I think some folks
get a strange thrill out of 20-digit numbers.
It fits the requirement of being temporal (to the
second, at least) and unique. You can throw in HSECS from V$TIMER if
someone gets picky enough to want to go to the centi-second level as
well. Yeah, and you can throw in USERENV('INSTANCEID') too, just for
some real OPS/RAC-ness! Best of all, it fits the DBA-half of your brain
by being fully cacheable and non-pinging...
...of course, you can embed the use of the
SEQUENCE object inside the function; I left it on the "outside" in this
example just to make it more flexible with regard to which sequence object it
uses...
If they don't like the idea of using a stored
function to get the sequence number, then tell 'em that "it's more ANSI
standard that way" and it's "database independent". That gets 'em every
time...
Hope this helps...
-Tim
- Original Message -
From: "Mladen Gogala" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Tuesday, September 03, 2002 5:54
PM
Subject: Re: OPS Sequences: nocache == order
??
> Unfortunately, we have an application dependency and I was
required > to come up with a quick & dirty fix. Thanks for your
reply.> > > On 2002.09.03 19:10 Anjo Kolk wrote:>
> > > If you run OPS and specify order, it works like no cache.
> > > > My question to you: "Why cripple OPS and your
business performance by having > > this requirement ?" Spending a
few bucks to get rid of this dependency will > > improve the
performance, until you run in to the next problem ;-)> > >
> Anjo.> > > > > > > > >
> > On Wednesday 04 September 2002 00:00, you wrote:> >
> I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 ,
RDBMS> > > 8.1.7.1)> > > and I'm having an
application dependency on a temporal order of sequence> > >
numbers.> > > With OPS that becomes a problem because each node
caches a set of sequence> > > numbers> > > (20 by
default). Oracle has an option, specifically for that situation,> >
> namely "ORDER".> > > My question is whether ORDER is the
same thing as NOCACHE and whether it is> > > possible>
> > to have a NOCACHE sequence which will return numbers in an incorrect
order> > > (larger number> > > before the smaller
one).> > > Please, o OPS gods and godesses, help me out and I'll
sacrifice you a beer> > > when I see you.> > >
Mladen Gogala> > > > > > --> > Please
see the official ORACLE-L FAQ: http://www.orafaq.com> > --> > Author: Anjo Kolk> >
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).> > > > --
> Mladen Gogala> -- > Please see the official ORACLE-L
FAQ: http://www.orafaq.com> --
> Author: Mladen Gogala> 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 '
Calling report
Hi friends
I am using following command to call report from
report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action
Trigger.
But this is not calling the report.
Any solution will be appreciated.
Syed
RE: PCTUSED - when is block added to freelist?
Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but "what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value?" Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: > I have one huge table (takes up about 30% of the total database storage) > which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED > set to the defaults of 10 and 40 respectively. > > I occurred to me that I could probably free up a lot more space by > increasing the PCTUSED so that more blocks would be available to be written > to (since getting more storage for the server is a bureaucratic nightmare > here). > > So my question is, if I just raise the PCTUSED from 40 to, say, 75 would > all blocks that fall into the 40-75 range become available for inserts? Or > is it only after their next update or delete? > > Different sections of the docs seem to imply different things. The docs > say: > > A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE > statements for moving a block to the free list when the block has fallen > below that percentage of usage. > > This seems to imply that it won't be moved to the freelist until a delete > or update is done that affects that block. > > > But they also say: > > A higher PCTUSED increases processing cost during INSERTs and UPDATEs. > > This seems to imply that when it's looking to do the insert it might find > that it can insert to a block. > > > Anyway, why would a lower PCTUSED reduce processing costs during a DELETE > but a higher PCTUSED wouldn't increase processing costs during a DELETE. > That makes no sense. I'm befuddled. > > > TIA, > Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Miller, Jay 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).
RE: How to speed up import
thanks, Kirti . . . that is the most succinct and helpful set of guidelines I have ever seen for doing this. -bill -Original Message- Sent: Wednesday, September 04, 2002 9:39 AM To: Multiple recipients of list ORACLE-L First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance. HTH... - Kirti Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage (if rollback segments are okay, do not use this). 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes and such, I keep the quota on the target tablespace to 0 to make it fail during import (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Deshpande, Kirti 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 subscribi
Re: Oracle on Win platforms
Peter was asking about Win98SE platform, not XP. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 9:38 AM > I have a Dell 8200 running XP Prof. and have installed 9.2 on it. > > My $0.02 worth, > > Ken Janusz, CPIM > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 6:58 AM > > > > I've got 8.1.7 on my PC. One of the other DBAs has put 9i on his. > > > > -Original Message- > > Sent: 04 September 2002 12:44 > > To: Multiple recipients of list ORACLE-L > > > > > > Just a quickie, please, folks - > > > > I have used Win NT4 for years, but have now got Win98SE on a second PC. > > (Don't mention XP...) > > > > Which versions of Oracle will load to 98? Single user only, stand-alone > > machine (no networking). On attempting to load 7.3.4 an 'unsuported' > message > > pops up, which wasn't exactly confidence inspiring... > > > > thanks, > > > > peter > > edinburgh > > > > > > * > > This e-mail message, and any files transmitted with it, are > > confidential and intended solely for the use of the addressee. If > > this message was not addressed to you, you have received it in error > > and any copying, distribution or other use of any part of it is > > strictly prohibited. Any views or opinions presented are solely those > > of the sender and do not necessarily represent those of the British > > Geological Survey. The security of e-mail communication cannot be > > guaranteed and the BGS accepts no liability for claims arising as a > > result of the use of this medium to transmit messages from or to the > > BGS. The BGS cannot accept any responsibility for viruses, so please > > scan all attachments.http://www.bgs.ac.uk > > * > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Robson, Peter > > 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). > > > > > > > > > > The information contained in this communication is > > confidential, is intended only for the use of the recipient > > named above, and may be legally privileged. > > If the reader of this message is not the intended > > recipient, you are hereby notified that any dissemination, > > distribution, or copying of this communication is strictly > > prohibited. > > If you have received this communication in error, > > please re-send this communication to the sender and > > delete the original message or any copy of it from your > > computer system. Thank You. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Robertson Lee - lerobe > > 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: KENNETH JANUSZ > 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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 5
Data mining data access
I have a question for anyone who has used a data mining tool. My understanding is that most data mining tools do not access your database directly, but require you to unload the data and put it into some specific flat file formats before the data mining tool can search your data. Is this true? This sounds very time-consuming and inconvenient. I am concerned that the tool won't get used very much because of the effort involved. Does anybody know of a data mining tool that can directly read database tables? If so, is there any standard data model involved? Here are my theories for why data mining tools don't read database tables directly: 1. Data warehouse schemas are not standard. Some are simple star-schemas, but others aren't. 2. In-memory tables may perform associative look-up faster than database queries. Your ideas are appreciated. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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).
Re: OPS Sequences: nocache == order ??
A day late and a dollar short but here's my $.02 Order will give you the temporal sequencing. Nocache should but it's not certain. Cached numbers are stored in the SYSTEM tablespace and can be retrieved in an atemporal order. I can't give you any specifics, but that's what Oracle says. Nocached numbers are generated at call time but that doesn't mean that they'll be stored in the database in temporal order. Order means that the number will be generated and stored in temporal order. As you can guess, this slows things up a bit. You will almost certainly see an increase in locking with ordered sequences. It can also happen with nocache. We recently went through an exercise of looking at every sequence in our database, about 400 altogether, to see if they needed to be ordered and cached. None of them needed to be ordered. Your requirement is unusual. If the sequence was being hit once an hour or so we decided to nocache it (save churning the SYSTEM tablespace). But our defaults are cache and noorder. HTH "Gogala, Mladen" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: OPS Sequences: nocache == order ?? Sent by: root 09/03/2002 06:00 PM Please respond to ORACLE-L I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day 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).
RE: Any woraround for this ....?
Title: Message Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
Re: Oracle on Win platforms
I have a Dell 8200 running XP Prof. and have installed 9.2 on it. My $0.02 worth, Ken Janusz, CPIM - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 6:58 AM > I've got 8.1.7 on my PC. One of the other DBAs has put 9i on his. > > -Original Message- > Sent: 04 September 2002 12:44 > To: Multiple recipients of list ORACLE-L > > > Just a quickie, please, folks - > > I have used Win NT4 for years, but have now got Win98SE on a second PC. > (Don't mention XP...) > > Which versions of Oracle will load to 98? Single user only, stand-alone > machine (no networking). On attempting to load 7.3.4 an 'unsuported' message > pops up, which wasn't exactly confidence inspiring... > > thanks, > > peter > edinburgh > > > * > This e-mail message, and any files transmitted with it, are > confidential and intended solely for the use of the addressee. If > this message was not addressed to you, you have received it in error > and any copying, distribution or other use of any part of it is > strictly prohibited. Any views or opinions presented are solely those > of the sender and do not necessarily represent those of the British > Geological Survey. The security of e-mail communication cannot be > guaranteed and the BGS accepts no liability for claims arising as a > result of the use of this medium to transmit messages from or to the > BGS. The BGS cannot accept any responsibility for viruses, so please > scan all attachments.http://www.bgs.ac.uk > * > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robson, Peter > 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). > > > > > The information contained in this communication is > confidential, is intended only for the use of the recipient > named above, and may be legally privileged. > If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, > distribution, or copying of this communication is strictly > prohibited. > If you have received this communication in error, > please re-send this communication to the sender and > delete the original message or any copy of it from your > computer system. Thank You. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robertson Lee - lerobe > 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: KENNETH JANUSZ 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).
RE: How to speed up import
First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance. HTH... - Kirti Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage (if rollback segments are okay, do not use this). 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes and such, I keep the quota on the target tablespace to 0 to make it fail during import (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Deshpande, Kirti 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).
RE:
Title: Message
Can
you specify a logfile or errorfile with srw.run_report? Maybe its running
and failing without reporting an error back to you. You can put an
srw.message call right before the srw.run_report to see if its ever actually
getting to that code.
HTH,
Beth
-Original Message-From: sultan
[mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 8:04
AMTo: Multiple recipients of list ORACLE-LSubject:
Hi friends
I am using following command to call report from
report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action
Trigger.
But this is not calling the report.
Any solution will be appreciated.
Syed
Re: Oracle on windows vs Redhat
On 2002.09.04 08:58 Ron Rogers wrote: > oraoraora, > Yesterday there was a posting of a URL that pointed you to a research > paper that supplied just the answers you want. > Check the archives for the email message. > Ron > ROR mª¿ªm > >>> [EMAIL PROTECTED] 09/04/02 03:43AM >>> > Guys, > > I have heard from people in the forum that Oracle performs well on > Linux/Solaris than Windows.Can someone give me docs/papers which > proves the same.I need this to convince my manager. > > Our DB is on Win2K now.we thought of moving to Redhat. > > TIA. > > I am, as someone has nicely put, an elitist Unix bigot, but I must say that I haven't noticed any difference in performance. I'm ashamed to admit but I do have a Win 2k partition on my PC and I did install oracle 9.2 on it. I ran a few query tests and one batch update test, but there wasn't any noticeable difference between SuSE 8.0 and Win 2k. The machine has 70 GB of disk drives (SCSI IIW), 1300 MHZ Athlon, 640MB RAM, NVIDIA VANTA with 32 MB and Creative Labs PCI 128 soundcard. Performance was almost identical. Now, if you ask me which OS I prefer, Linux wins hands down. After all, I am a UNIX elitist SOB. -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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).
RE: automatic segment space management
okay, if I hadn't already been convinced not to use it, this would clinch it... we will have joins of 16+ tables to one of the fact tables and I can't impede performance. This is a very visible system and needs to be as good as it can be --- Connor McDonald <[EMAIL PROTECTED]> wrote: > You need to benchmark ASSM carefully because it may > have impact especially on your smaller tables. To > avoid the concurrency issues, you can end up with > blocks "sprayed" as rows are created. For example, > you might add a single row to an (empty) table and end > up with the table being 10 blocks instead of 1 because > ASSM tries to spread blocks around in this fashion. > > If those tables are targets of joins ( to large > tables) as they often tend to be in DW, you might be > trawling through a much larger amount of blocks then > you need to be... > > hth > connor > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > > Thanks Ian, that was indeed the question, although > > the other > > information is useful as well. > > > > if I understand what you are saying correctly, it > > works but it won't > > really buy me anything and I might do better > > controlling the space > > myself. And while you have had no problems, you've > > heard negative > > things about it. I can't afford to have this > > database not be available > > so I'll manage them on my own > > > > Rachel > > > > --- "MacGregor, Ian A." <[EMAIL PROTECTED]> > > wrote: > > > The question posed was not whether "extent > > management local" should > > > be used, but whether automatic segment space > > management should be > > > used. > > > > > > As this is a data warehouse, I would not expect > > you to have > > > transactions trying to change the same block. > > Assuming you are > > > loading; that is, inserting data and not doing > > updates, wouldn't you > > > try to cram as much data as possible into a block? > > Seems this could > > > be done more easily by controlling these > > parameters yourself > > > > > > I've got one system using automatic segment space > > management without > > > any problems, however when I posed the same > > question on using it a > > > few months ago, the respone which trickled in way > > highly negative > > > concerning its usage. > > > > > > Ian MacGregor > > > Stanford Linear Accelerator Center > > > [EMAIL PROTECTED] > > > > > > -Original Message- > > > Sent: Tuesday, September 03, 2002 8:49 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > time for me to ask the experts again. > > > > > > My data warehouse will be 9.2, with all locally > > managed tablespaces. > > > We > > > will be following what I have taken to calling the > > "Goldilocks" > > > principle -- that of small, medium and large > > tablespace extent sizes, > > > with variations in that we will separate indexes > > and data, and will > > > have even more separation for our fact tables into > > partitioned tables > > > and tablespaces. > > > > > > However, now comes the time for me to work out > > storage clauses. And a > > > quick read through the docs leaves me wondering if > > I should just turn > > > on automatic segment-space management and not > > worry about setting > > > PCTFREE, PCTUSED and FREELIST parameters. I can't > > find any real > > > information or bugs on MetaLink either. > > > > > > Does anyone have any experience, good OR bad, with > > using this > > > feature? > > > If you are doing data warehouse work, what are > > good values for the > > > parameters if I DO use them? One fact table is > > likely to be highly > > > updated (customer info) as we collect more and > > more specific > > > information from customers. The rest will be, as > > you would expect > > > from > > > a DW, mostly inserts. > > > > > > Help? > > > > > > Thanks! > > > > > > Rachel > > > > > > __ > > > Do You Yahoo!? > > > Yahoo! Finance - Get real-time stock quotes > > > http://finance.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > > -- > > > Author: Rachel Carmichael > > > 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: MacGregor, Ian A. > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538
RE: Function-Based Index not working
Try changing optimizer mode to FIRST_ROWS ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Oracle on windows vs Redhat
oraoraora, Yesterday there was a posting of a URL that pointed you to a research paper that supplied just the answers you want. Check the archives for the email message. Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 09/04/02 03:43AM >>> Guys, I have heard from people in the forum that Oracle performs well on Linux/Solaris than Windows.Can someone give me docs/papers which proves the same.I need this to convince my manager. Our DB is on Win2K now.we thought of moving to Redhat. TIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora 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: Ron Rogers 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).
RE: Oracle on Win platforms
Peter, My advice would be to totally forget about running Oracle on Win9x of any kind. The O/S is notoriously bad at managing multi-threaded processes (which is how Oracle runs on the Win32 platform).. If there is no chance of getting up to XP (oh bugger, I still mentioned it ;P ), how about upgrading the machine to at least NT4, or Win2K (a much better option)? Sorry, I can't help on the supported version issue, I've never had the inclination to try it.. ;) Regards Mark -Original Message- Peter Sent: 04 September 2002 12:44 To: Multiple recipients of list ORACLE-L Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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: Mark Leith 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).
RE: Oracle on Win platforms
Only Personal Edition is available on windows 98. Standard and Enterprise editions r available only for NT or Win2k -Original Message- Sent: Wednesday, September 04, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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: Naveen Nahata 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).
ora-4020 deadlock detected while trying to lock object:
Hi All, When a form is run occassionally the users will get "ora-4020 deadlock detected while trying to lock object:" message. How can I determine why the deadlock is happening. I suspect it is a coding issue. I am not familiar with the code but perhaps there are some DD tables that may help. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
[no subject]
Hi friends
I am using following command to call report from
report
srw.run_report ('report=d:\test.rdf destype=file desname=try.out
desformat=dflt batch=yes');
I have tested this using After Report /Before Report/Action Trigger.
But this is not calling the report.
Any solution will be appreciated.
Syed
RE: Oracle on Win platforms
I've got 8.1.7 on my PC. One of the other DBAs has put 9i on his. -Original Message- Sent: 04 September 2002 12:44 To: Multiple recipients of list ORACLE-L Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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).
RE: Oracle 9i and memory required
Hi I got it working with 384MB on redhat 7.2, Dell Optiplex GX1 desktop. (test install). George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 04 September 2002 13:18 PM To: Multiple recipients of list ORACLE-L Oracle 9i installation document says it requires a minimum of 512MB of memory. Anyone got it installed and running on 256MB? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Leonard (ZA) 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).
Re: Changing sysdate[Scanned]
It is better to use ntpdate. JP On Wednesday 04 September 2002 12:28, you wrote: > Hi, > > I am getting the error "newdate does not exist" when I tried $newdate > '09042002 14:48:00' . Is it the command I should use to change the sysdate > in Solaris? > > regards, > Karthik > > -Original Message- > Sent: Wednesday, September 04, 2002 12:53 PM > To: Multiple recipients of list ORACLE-L > > Change the System date, and restart the database. > > -Original Message- > Sent: Wednesday, September 04, 2002 12:18 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > Is it possible to change the sysdate? (Solaris 5.8, Oracle 8.1.7) > TIA. > > K. -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
RE: Oracle 9i and memory required
It will run on even 128MB. Increase the swap size Naveen -Original Message- Sent: Wednesday, September 04, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Oracle 9i installation document says it requires a minimum of 512MB of memory. Anyone got it installed and running on 256MB? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: Naveen Nahata 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).
Oracle on Win platforms
Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
RE: Inserts are taking time !
Marul, Are there any bitmapped indexes on the table Iain Nicoll -Original Message- Sent: Wednesday, September 04, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Marul, 10k records in 1 hour(3600 seconds) 1 record in 3600/1 => approx 0.36 seconds If your application is OLTP you'll be inserting records 1 by 1 rather than in bulk. Which means the effect will hardly be noticed. If you are going to insert record in bulk you can DROP and then recreate the indexes after load. Check what takes more time. See if there is any scope of partitioning the table, to use local partitioned indexes. For bulk load, disabling the constraints is also an option. Naveen -Original Message- Sent: Wednesday, September 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:33 PM > Yep and you have given the answer yourself. It is the number of indexes. I > think that if the number of records increase the number of levels increase > and slowly but surely you need to update more and more blocks. I have done > sone tests (an oher people I am sure) that show that there is an expontial > increase in the amount of undo and redo generated for every index that gets > added into the mix. > > You will probably see an increase in CPU time (assuming that you are the only > process/session on the system). > > Anjo. > > > On Wednesday 04 September 2002 08:53, you wrote: > > Hi All, > > > > We have a table which can contain more than half a million records. When we > > try to insert some 10k records in the empty table it get inserted in 10 > > min. but as the size increases time taken to insert also increases. After > > 350,000 records it takes around an hour to insert 10k records. There are > > around 15 columns in it out of which 11 are indexed. There is one > > concatenated function-based index on two columns of Varchar type and two > > separate index for the same two columns. > > > > I have checked the free space for the tablespaces to which the table and > > indexes are attached to. They are in two separate tbs. > > > > Any clues why this is happenning. > > > > > > TIA > > Marul. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Anjo Kolk > 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: Marul Mehta 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: Naveen Nahata 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: Nicoll, Iain \(Calanais\) 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] (n
Oracle 9i and memory required
Oracle 9i installation document says it requires a minimum of 512MB of memory. Anyone got it installed and running on 256MB? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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).
Re: automatic segment space management
yes I did notice that in the docs (oh my goodness, the docs were CLEAR? ) I think, based on what Ian has said, that I will manage the space myself. I know that updates will be rare, under 5% of the time. I know that deletes will not happen, unless I am pruning partitions, so that will not be affected by PCTFREE/PCTUSED values. I know that the data load will be once daily, a single process (for the time being), with no other users on the system. So I should be able to figure this out :) Rachel --- Tim Gorman <[EMAIL PROTECTED]> wrote: > One note: 9i automatic segment space management does not automate > PCTFREE; > that still functions as before. It does cause PCTUSED, FREELISTS, > and > FREELIST GROUPS to be ignored, however... > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, September 03, 2002 9:48 AM > > > > time for me to ask the experts again. > > > > My data warehouse will be 9.2, with all locally managed > tablespaces. We > > will be following what I have taken to calling the "Goldilocks" > > principle -- that of small, medium and large tablespace extent > sizes, > > with variations in that we will separate indexes and data, and will > > have even more separation for our fact tables into partitioned > tables > > and tablespaces. > > > > However, now comes the time for me to work out storage clauses. And > a > > quick read through the docs leaves me wondering if I should just > turn > > on automatic segment-space management and not worry about setting > > PCTFREE, PCTUSED and FREELIST parameters. I can't find any real > > information or bugs on MetaLink either. > > > > Does anyone have any experience, good OR bad, with using this > feature? > > If you are doing data warehouse work, what are good values for the > > parameters if I DO use them? One fact table is likely to be highly > > updated (customer info) as we collect more and more specific > > information from customers. The rest will be, as you would expect > from > > a DW, mostly inserts. > > > > Help? > > > > Thanks! > > > > Rachel > > > > __ > > Do You Yahoo!? > > Yahoo! Finance - Get real-time stock quotes > > http://finance.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Rachel Carmichael > > 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: Tim Gorman > 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: Inserts are taking time !
Marul, It sounds to me like the indexes are going into overflow - this will cause the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: 04 September 2002 07:53To: Multiple recipients of list ORACLE-LSubject: Inserts are taking time ! Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul.
RE: Changing sysdate[Scanned]
Title: RE: Constraints problem It's date, not newdate. Check "man date". -Original Message-From: ext Karthikeyan S [mailto:[EMAIL PROTECTED]]Sent: 04 September, 2002 13:28To: Multiple recipients of list ORACLE-LSubject: RE: Changing sysdate[Scanned] Hi, I am getting the error "newdate does not exist" when I tried $newdate '09042002 14:48:00' . Is it the command I should use to change the sysdate in Solaris? regards, Karthik -Original Message-From: Naveen Nahata [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 12:53 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Changing sysdate[Scanned] Change the System date, and restart the database. -Original Message-From: Karthikeyan S [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 12:18 PMTo: Multiple recipients of list ORACLE-LSubject: Changing sysdate Hi, Is it possible to change the sysdate? (Solaris 5.8, Oracle 8.1.7) TIA. K.
Oracle*Terminal redefinition of keys in vt220
Hello, OS : HP-UX B.11.11 32 bits RDBMS : 8.1.7.4.0 DEV2000 : 6i patch 8 We are developping in Forms6i in character mode. I'am trying to alter the resource file "fmrcvt220.res" for integrating our own combinations of keys. I want to change for example the "Do" to the "F5". I use "oraterm60" and all seems ok. I have altered the key. But when I am in the window "Key Binding Editor" I can't go upward to generate the resource file. The environment variables are set as follow : ORACLE_TERMINAL=$ORACLE_HOME/forms60/admin/terminal/US FORMS60_TERMINAL=$ORACLE_HOME/forms60/admin/terminal/US ORACLE_TERM=vt220 TREM=vt220 With the tool on Windows, I have the bouton OK or CANCEL to generate the resource file. Any help will by welcome. Thanks. Cordialement, Frédéric Major -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Fr=E9d=E9ric_MAJOR?= 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).
Any woraround for this ....?
Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
RE: Changing sysdate[Scanned]
Title: RE: Constraints problem Hi, I am getting the error "newdate does not exist" when I tried $newdate '09042002 14:48:00' . Is it the command I should use to change the sysdate in Solaris? regards, Karthik -Original Message-From: Naveen Nahata [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 12:53 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Changing sysdate[Scanned] Change the System date, and restart the database. -Original Message-From: Karthikeyan S [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 12:18 PMTo: Multiple recipients of list ORACLE-LSubject: Changing sysdate Hi, Is it possible to change the sysdate? (Solaris 5.8, Oracle 8.1.7) TIA. K.
