RE: Oracle-L at UKOUG
Yes please. I am flying down from Edinburgh on the Monday morning and I have never been to this before so any guidance about what to do would be appreciated. Beer / food sounds good, though. Heather -Original Message- Sent: 19 November 2002 20:19 To: Multiple recipients of list ORACLE-L Anyone interested in an Oracle-L get together at UKOUG for those of us poor souls who were not able to attend Oracle World? OW attendees welcome also! Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Storing of number datatype in table
Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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-L at UKOUG
Hi Heather! Gerry is coming along as well, so I'm sure he can will give you guidance if you need it. I'll get him to email his mobile number over to you just in case. Anybody else that has been before have any ideas on a place to eat? I know of the couple of cafe restaurants at the back in Brindley Place, and of course places like Hard Rock Cafe, TGI's and a whole host of other kinds of food down Broad Street.. Anybody have any preferences? -Original Message- Heather Sent: 22 November 2002 10:49 To: Multiple recipients of list ORACLE-L Yes please. I am flying down from Edinburgh on the Monday morning and I have never been to this before so any guidance about what to do would be appreciated. Beer / food sounds good, though. Heather -Original Message- Sent: 19 November 2002 20:19 To: Multiple recipients of list ORACLE-L Anyone interested in an Oracle-L get together at UKOUG for those of us poor souls who were not able to attend Oracle World? OW attendees welcome also! Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
I believe that one position is used for positive/negative value. So it wouldn't fit otherwise -Original Message- [mailto:[EMAIL PROTECTED]] Sent: vrijdag 22 november 2002 12:24 To: Multiple recipients of list ORACLE-L Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
its not stored that way internally its just displayed that way. do a set numwidth 20 joe [EMAIL PROTECTED] wrote: Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
I think the number is still being stored as 11, but only being displayed in E notation. When I try it I get: scott select * from trnid; TRN_ID -- 1 1 11 11 Elapsed: 00:00:00.00 scott I entered both as a number 1 and a character string '1', like you did. Have you specified a format for the trn_id column? -- Paul -Original Message- [EMAIL PROTECTED] Sent: Friday, November 22, 2002 6:24 AM To: Multiple recipients of list ORACLE-L Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Paul Heely INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
Try this before your select: set numwidth 38 Then is should work. The reason is that sqlplus has a default length value for showing number fields. Before setting it to something else, try and type: show numwidth that should tell you the default. Thanks, KidRac [EMAIL PROTECTED] wrote: Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morten Egan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
partitioning questions
Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
The number is more than the numwidth specified. Try this SQL set numwidth 13 SQL select trn_id from trnid; Your numwidth is perhpas defined as 9; so anything of more precision is displayed as exponetial notation; internally all numbers are stored the same. HTH Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 6:23 AM Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Ltrim?
Hallo, Anyone whom could help me how to write in cron when scheduling the start of a unixprogram. I would like that the unix script will run every monday on 6 am. I have tried but it fails. Any suggestions, please help Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: partitioning questions
[EMAIL PROTECTED] wrote: Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? I do think so. 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? I doubt it, because the main benefit of partitioning is clipping - trying to limit searches to a few partitions. In other words, you should partition on a criterion you have input (I mean something which appears as WHERE PARTITION_KEY = constant or (better) bind variable in your queries). If charge_id is just use for joins, it means that in a way it is derived from something else (condition on dates) and therefore using it as a partition key would be useless. 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? No for the same reason as above. It would be also interesting to check whether you should rather have a LOCAL or GLOBAL index on charge_id in this case. 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? With PQO probably less expensive, but I have not tested it specifically. -- Regards, 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Ltrim?
0 6 * * 1 script_name -Original Message- Sent: vrijdag 22 november 2002 15:19 To: Multiple recipients of list ORACLE-L Hallo, Anyone whom could help me how to write in cron when scheduling the start of a unixprogram. I would like that the unix script will run every monday on 6 am. I have tried but it fails. Any suggestions, please help Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
find the system process for an oracle session
Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Ltrim?
The cron part is easy but you have to make sure your Unix script works. Next time, before you post a question ... please try looking up the answer for yourself on any of the available sources. In this case, the man function of unix tells you exactly what you need to know.You should have done a man on crontab. 00 06 * * 5 your_unix_script minute (0-59), 00 = 0 minutes hour (0-23), 06 = 6 am day of the month (1-31), month of the year (1-12), day of the week (0-6 with 0=Sunday). 5 = Friday command name -Original Message- Sent: Friday, November 22, 2002 8:19 AM To: Multiple recipients of list ORACLE-L Hallo, Anyone whom could help me how to write in cron when scheduling the start of a unixprogram. I would like that the unix script will run every monday on 6 am. I have tried but it fails. Any suggestions, please help Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Ltrim?
Title: RE: Ltrim? Should be something like this 0 6 * * 1 ksh myscript.ksh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: Ltrim? Hallo, Anyone whom could help me how to write in cron when scheduling the start of a unixprogram. I would like that the unix script will run every monday on 6 am. I have tried but it fails. Any suggestions, please help Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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 system process for an oracle session
Ruth, If you mean the operating system process id, it's in v$session field PROCESS. the following will tel you the oracle server process select spid from v$process p, v$session s where s.sid = your sid from v$session and p.addr = s.paddr HTH Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 9:53 AM Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Thanks! I had a senior moment there. Guess I'll get some coffee to wash down my Ginko Biloba. Ruth - Original Message - To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, November 22, 2002 10:28 AM select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Ruth, select vp.spid from v$process vp, v$session vs where vp.addr=vs.paddr and vs.sid=1 regards, Mike Hately -Original Message- Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: partitioning questions
That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Ginko Biloba? Isn't he a Brazilian footballer? Pete -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 15:51 To: MOORE, Peter Rbh; [EMAIL PROTECTED] Subject: Re: find the system process for an oracle session Thanks! I had a senior moment there. Guess I'll get some coffee to wash down my Ginko Biloba. Ruth - Original Message - From: MOORE, Peter Rbh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, November 22, 2002 10:28 AM Subject: RE: find the system process for an oracle session select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
select s.username,s.sid,s.osuser,p.spid from v$session s,v$process p where p.addr=s.paddr and s.sid=sid -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 9:54 AM To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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[2]: Ltrim?
Roland, By now you've seen several messages showing you the cron syntax to get you task to start at the time you want. There is characteristic of cron that seems to catch first time users -- the environment settings. As a shell users you'll be used to having your .profile or .login (or similar script depending on what shell you use) run and set up your environment. And when you run at or batch jobs your current environment is passed on to those scripts. Job's that start from your crontab on the other hand, have a stripped down environment. Try running a crontab command that just does a set and compare the email output to the environment that you have when you are at a shell prompt and the differences will be obvious. You'll probably see major differences in $PATH and $ORACLE_HOME, either of which could keep your script from executing properly. The solution is to set your own environment every time a crontab task starts. I keep a stripped down version of my .profile that I name .cron_profile that has the environment I need to run sqlplus scripts. Then my crontab entries look like: 0 6 * * 1 . $HOME/.cron_profile; script_name -rje J 0 6 * * 1 script_name R Hallo, R Anyone whom could help me how to write in cron when R scheduling the start of a unixprogram. R I would like that the unix script will run every monday on 6 am. R I have tried but it fails. Any suggestions, please help R Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Ruth Gramolini wrote: Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth Ruth, It depends on whether you want the client process (eg the sqlplus process) or the shadow process identifier. You find the first one as column PROCESS in V$SESSION, and identify the row either by the SID (session identifier) or by saying AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID') to identify the currently running session. The second one is column SPID in V$PROCESS, and you can join V$SESSION to V$PROCESS by saying V$SESSION.PADDR = V$PROCESS.ADDR. -- 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
There is a column 'PADDR' in v$session , join this with the column 'ADDR' of v$process and look for column value 'SPID' from v$process. Now write a query. :)- -Original Message- Sent: Friday, November 22, 2002 8:54 AM To: Multiple recipients of list ORACLE-L Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
error 472
I have cloned an 8.1.7.0 database from another instance (clone doc from metalink for Apps) on AIX 4.3.3. I don't THINK this is connected to the clone or (directly) to apps. Since I brought the database back up after the clone successfully, it keeps crashing with pick a process terminating instance due to error 472 It comes back when I start it. The interval between startup and crash appears arbitrary. What can be accomplished while the database is up is logical stuff... I can run apps jobs, I can update and select from tables, I can do ddl, I can manually log switch. Metalink has BOAT LOADS of docs on this happening, but I haven't found anything that I am permitted to see that has a resolution. I have an Itar, but that is not yet yielding any information. Has anyone seen similar problems, and (if so) IS there a resolution? Thanks in advance for any advice ajw April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(-O;6UU;FEC871I M;VXL(EN8VQU9EN9R!A='1A8VAM96YTRP@:7,@W1R:6-T;'D@8V]N9FED M96YT:6%L(%N9!F;W(@=AE(EN=5N95D('5S92!O9B!T:4@861DF5S MV5E(]N;'D[(ET(UA2!A;'-O(-O;G1A:6X@')O')I971AGDL('!R M:6-E('-E;G-I=EV92P@;W(@;5G86QL2!PFEV:6QE9V5D(EN9F]R;6%T M:6]N+B!.;W1I8V4@:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-U MF4L(1IW1R:6)U=EO;BP@9ESV5M:6YA=EO;BP@=7-E+!OB!C;W!Y M:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA;B!T M:4@:6YT96YD960@F5C:7!I96YT(ES('-TFEC=QY('!R;VAI8FET960@ M86YD(UA2!B92!I;QE9V%L+B!)9B!Y;W4@:%V92!R96-E:79E9!T:ES M(-O;6UU;FEC871I;VX@:6X@97)R;W(L('!L96%S92!N;W1I9GD@=AE('-E M;F1EB!I;6UE9EA=5L2!B2!R97!L2!E+6UA:6PL(1E;5T92!T:ES M(-O;6UU;FEC871I;VXL(%N9!D97-TF]Y(%L;!C;W!I97,N( T*#0I# M;W)P;W)A=4@4WES=5MRP@26YC+B!H87,@=%K96X@F5AV]N86)L92!P MF5C875T:6]NR!T;R!E;G-UF4@=AA=!A;GD@871T86-H;65N=!T;R!T M:ES(4M;6%I;!H87,@8F5E;B!S=V5P=!F;W(@=FER=7-ERX@5V4@W!E M8VEF:6-A;QY(1IV-L86EM(%L;!L:6%B:6QI='D@86YD('=I;P@86-C M97!T(YO(')EW!O;G-I8FEL:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE M9!AR!A(')EW5L=!O9B!S;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@ M6]U('1O(-AG)Y(]U=!Y;W5R(]W;B!V:7)UR!C:5C:W,@8F5F;W)E (]P96YI;F@86YY(%T=%C:UE;G0N#0H-@ end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
This probably gives you more than you asked for, but it works. select substr(s.sid,1,6) SID, s.serial#, p.spid OSPROCESS, substr(p.program,1,25) OSPROG, substr(s.osuser,1,20) OSUSER, status,substr(s.schemaname,1,20) SCHEMANAME, substr(s.machine,1,15) MACHINE, s.terminal, s.program from v$session s, v$process p where s.paddr = p.addr order by s.terminal, s.schemaname, s.osuser; On Friday 22 November 2002 08:53, Ruth Gramolini wrote: Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Rodd Holman Enterprise Data Systems Engineer LodgeNet Entertainment Corporation [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Ruth, Try this query...Will give you both PID and SPID. If you want to have it for all uesers connected with Database then remove vs.status = 'ACTIVE'. set linesize 120 select substr(vs.username,1,10)username, vs.osuser, vs.sid, vs.serial#, vs.LOGON_TIME, substr(vs.machine,1,15)machine, vs.process, vp.spid, vs.last_call_et from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username is not null and vs.status = 'ACTIVE' / Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 22 Nov 2002 08:24:44 -0800 There is a column 'PADDR' in v$session , join this with the column 'ADDR' of v$process and look for column value 'SPID' from v$process. Now write a query. :)- -Original Message- Sent: Friday, November 22, 2002 8:54 AM To: Multiple recipients of list ORACLE-L Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: partitioning questions
Mladen, are you sure, partitioning is included with oracle 9? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 11:08 AM That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Help me read my 10053 trace file
Shao: I think your program has become a victim of the new costing model for bitmap indexes which was introduced in 8.0.6. Basically the new costing model assumes the 80% of the rows are costed in the old model and 20% of the rows are costed as they are spread across ALL blocks. Let us assume you have a table with 100 blocks and each block contains 10 rows. In total you have 1000 rows and if your result set finds 100 blocks, the cost is calculated as 100/10=10 blocks. So while costing the bit map access cost, it just adds the cost for 10 blocks with the bitmap index access cost. In the new costing model, it assumes the 80% of the blocks are accessed in the older model and the rest of the 20% blocks are spread across the __ENTIRE__ table (with the absence of the partition knowledge) and costs the table access costs accordingly. You can use the event 10170 to tell the CBO to use the old costing model which works best for partition tables. (In the above said example the new costing mode will say the table access cost as 28 blocks instead 10 blocks) Please correct me (Esp:Jonathan Lewis) if I am wrong !!! Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Chunning Sent: Thursday, November 21, 2002 8:59 AM To: Multiple recipients of list ORACLE-L I have a query in which the predictate is like this: WHERE ln.metro_id = i_metro_id AND l.metro_id = :b1 ANd eld.metro_id(+) = :b1 AND ln.status_desc = 'ACTIVE' AND ln.daset_id = ld.daset_id (+) AND ln.sect_id = ld.sect_id (+) AND ln.life_id = ld.life_id (+) AND ln.version_id = ld.version_id (+) AND ln.daset_id = l.daset_id AND ln.sect_id = l.sect_id AND ln.life_id = l.life_id AND ln.version_id = l.version_id AND l.status_desc = 'ACTIVE' AND l.ramp = 'Y' AND l.daset_id = eld.daset_id (+) AND l.sect_id = eld.sect_id (+) AND l.life_id = eld.life_id (+) AND l.version_id = eld.version_id (+) AND eld.life_id IS NULL If the table life is analyzed, the bitmap index is not used, and the optimizer choose a full table scan, and it take hours to run. But if the table life is not analyzed, bitmap index is used, and the query is takes only 1 minutes. Here is some related info from event 10053 With table life ANALYZED *** Table statsTable: LIFE Alias: L (Using composite stats) TOTAL :: CDN: 5485935 NBLKS: 7839 TABLE_SCAN_CST: 1190 AVG_ROW_LEN: 182 -- Index stats INDEX#: 3906609 COL#: 6 16 USING COMPOSITE STATS TOTAL :: LVLS: 1 #LB: 268 #DK: 2 LB/K: 10 DB/K: 11 CLUF: 293 INDEX#: 3905350 COL#: 1 2 3 4 5 USING COMPOSITE STATS TOTAL :: LVLS: 2 #LB: 23411 #DK: 5485935 LB/K: 1 DB/K: 1 CLUF: 950571 INDEX#: 3905296 COL#: USING COMPOSITE STATS TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800 INDEX#: 3905332 COL#: USING COMPOSITE STATS TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800 *** *** SINGLE TABLE ACCESS PATH Column: METRO_ID Col#: 5 Table: LIFE Alias: L NDV: 15NULLS: 0 DENS: 6.6667e-02 LO: 1 HI: 48 Column: STATUS_DES Col#: 16 Table: LIFE Alias: L NDV: 1 NULLS: 0 DENS: 1.e+00 Column: RAMP Col#: 6 Table: LIFE Alias: L NDV: 2 NULLS: 0 DENS: 5.e-01 TABLE: LIFE ORIG CDN: 5485935 CMPTD CDN: 182865 Access path: tsc Resc: 1190 Resp: 1190 Access path: index (equal) INDEX#: 3906609 TABLE: LIFE CST: 134 IXSEL: 5.e-01 TBSEL: 5.e-01 Bitmap access path rejected Cost: 5223 Selectivity: 0 Not believed to be index-only. BEST_CST: 1190.00 PATH: 2 Degree: 1 *** With table LIFE not analyzed *** Table statsTable: LIFE Alias: L (Averaging) PARTITION [0]CDN: 798014 NBLKS: 9770 TABLE_SCAN_CST: 1483 AVG_ROW_LEN: 100 PARTITION [1]CDN: 1673215 NBLKS: 20485 TABLE_SCAN_CST: 3110 AVG_ROW_LEN: 100 PARTITION [2]CDN: 590384 NBLKS: 7228 TABLE_SCAN_CST: 1098 AVG_ROW_LEN: 100 PARTITION [3]CDN: 494246 NBLKS: 6051 TABLE_SCAN_CST: 919 AVG_ROW_LEN: 100 PARTITION [4]CDN: 577478 NBLKS: 7070 TABLE_SCAN_CST: 1074 AVG_ROW_LEN: 100 PARTITION [5]CDN: 1160673 NBLKS: 14210 TABLE_SCAN_CST: 2157 AVG_ROW_LEN: 100 PARTITION [6]CDN: 1365282 NBLKS: 16715 TABLE_SCAN_CST: 2538 AVG_ROW_LEN: 100 PARTITION [7]CDN: 654747 NBLKS: 8016 TABLE_SCAN_CST: 1217 AVG_ROW_LEN: 100 PARTITION [8]CDN: 614969 NBLKS: 7529 TABLE_SCAN_CST: 1143 AVG_ROW_LEN: 100 PARTITION [9]CDN: 427595 NBLKS: 5235 TABLE_SCAN_CST: 795 AVG_ROW_LEN: 100 PARTITION [10]
RE: partitioning questions
Partitioning is still a separately licensed product. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -Original Message- Sent: Friday, November 22, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Mladen, are you sure, partitioning is included with oracle 9? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 11:08 AM That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Nope. Brazillians do not have football, only soccer. I've never seen a brazillian team going to the superbowl. I believe that Denver and Packers will be going there this year. None of those two teams is from Brazil. -Original Message- From: MOORE, Peter Rbh [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: find the system process for an oracle session Ginko Biloba? Isn't he a Brazilian footballer? Pete -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 15:51 To: MOORE, Peter Rbh; [EMAIL PROTECTED] Subject: Re: find the system process for an oracle session Thanks! I had a senior moment there. Guess I'll get some coffee to wash down my Ginko Biloba. Ruth - Original Message - From: MOORE, Peter Rbh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, November 22, 2002 10:28 AM Subject: RE: find the system process for an oracle session select p.spid, s.sid, s.username, s.program from v$session s, v$process p where p.addr = s.paddr p.spid shows you the o/s process ID for the session. -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: 22 November 2002 14:54 To: Multiple recipients of list ORACLE-L Subject: find the system process for an oracle session Good morning, Can anyone send my the sql to find the system process being used by an oracle session. I know this has been out here before, but I can't find it in my saved stuff. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
process question
Thanks to all who answered. I have it now and I killed the dam@ process that was the problem. Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: error 472
Did you look in the PMON trace file? Does anything appear there that might shed some light on the problem? RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -Original Message- Sent: Friday, November 22, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I have cloned an 8.1.7.0 database from another instance (clone doc from metalink for Apps) on AIX 4.3.3. I don't THINK this is connected to the clone or (directly) to apps. Since I brought the database back up after the clone successfully, it keeps crashing with pick a process terminating instance due to error 472 It comes back when I start it. The interval between startup and crash appears arbitrary. What can be accomplished while the database is up is logical stuff... I can run apps jobs, I can update and select from tables, I can do ddl, I can manually log switch. Metalink has BOAT LOADS of docs on this happening, but I haven't found anything that I am permitted to see that has a resolution. I have an Itar, but that is not yet yielding any information. Has anyone seen similar problems, and (if so) IS there a resolution? Thanks in advance for any advice ajw April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(-O;6UU;FEC871I M;VXL(EN8VQU9EN9R!A='1A8VAM96YTRP@:7,@W1R:6-T;'D@8V]N9FED M96YT:6%L(%N9!F;W(@=AE(EN=5N95D('5S92!O9B!T:4@861DF5S MV5E(]N;'D[(ET(UA2!A;'-O(-O;G1A:6X@')O')I971AGDL('!R M:6-E('-E;G-I=EV92P@;W(@;5G86QL2!PFEV:6QE9V5D(EN9F]R;6%T M:6]N+B!.;W1I8V4@:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-U MF4L(1IW1R:6)U=EO;BP@9ESV5M:6YA=EO;BP@=7-E+!OB!C;W!Y M:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA;B!T M:4@:6YT96YD960@F5C:7!I96YT(ES('-TFEC=QY('!R;VAI8FET960@ M86YD(UA2!B92!I;QE9V%L+B!)9B!Y;W4@:%V92!R96-E:79E9!T:ES M(-O;6UU;FEC871I;VX@:6X@97)R;W(L('!L96%S92!N;W1I9GD@=AE('-E M;F1EB!I;6UE9EA=5L2!B2!R97!L2!E+6UA:6PL(1E;5T92!T:ES M(-O;6UU;FEC871I;VXL(%N9!D97-TF]Y(%L;!C;W!I97,N( T*#0I# M;W)P;W)A=4@4WES=5MRP@26YC+B!H87,@=%K96X@F5AV]N86)L92!P MF5C875T:6]NR!T;R!E;G-UF4@=AA=!A;GD@871T86-H;65N=!T;R!T M:ES(4M;6%I;!H87,@8F5E;B!S=V5P=!F;W(@=FER=7-ERX@5V4@W!E M8VEF:6-A;QY(1IV-L86EM(%L;!L:6%B:6QI='D@86YD('=I;P@86-C M97!T(YO(')EW!O;G-I8FEL:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE M9!AR!A(')EW5L=!O9B!S;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@ M6]U('1O(-AG)Y(]U=!Y;W5R(]W;B!V:7)UR!C:5C:W,@8F5F;W)E (]P96YI;F@86YY(%T=%C:UE;G0N#0H-@ end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
It must have converted the date to metric. -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: partitioning questions
MyViews below... Regards, Viral Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? -- NO, they have to be equi-partitioned and you have to specify atleast the leading keys in the join for both tables. 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? -- SEEMS like a good choice since you always limit your query on charge_id, however data distribution in that column also plays a role. 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do jointhe tables by charge_id still benefit? - Dont think that would help. However, you could have a global index on charge_id on both tables. If you insist topartition the data as mentioned in #3, then for the benefit of your queries you may want the exclusive globalindex on charge_id. (As there are pros, there are cons for this too) Again depending on the type of the data contents/value of the columns, you could have 2 bitmap indexes (one on the date and another on charge_id, but this is not always advisable) 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? -- I think it is same as non-partioned tables. From: "Gogala, Mladen" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: partitioning questions Date: Fri, 22 Nov 2002 08:08:55 -0800 That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services--
Identifying indexes
I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
I think this is because during this time Pope Gregory removed 10 days from the calendar to align with solar calendar... check this out... http://webexhibits.org/calendars/timeline.html From: "Freeman, Robert" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Oracle is a time machine!! Date: Fri, 22 Nov 2002 09:55:21 -0800 Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). STOP MORE SPAM with the new MSN 8 and get 2 months FREE* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Very simple explanation. That day does not exist. Prior to 1582, every year divisible by 4 was a leap year. Since a year contains only 365.242199 days (slightly less than 365.25 days), an error of ten days accumulated over the centuries. To compensate for this error, Pope Gregory XIII (after whom the Gregorian Calendar is named) decreed that the ten days between October 5, 1582 and October 14, 1582 would be eliminated from the calendar. This made October 1582 the shortest month, with only 21 days. After 1582, years divisible by 100 are not leap years unless they are also divisible by 400. Thus, 1900 is not a leap year, but 2000 is from http://www.wiskit.com/calendar.html -Original Message- Sent: Friday, November 22, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Nothing unusual here. Ten days were skipped in October, 1582. It was called the Gregorian Calendar Reform. Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- Sent: Friday, November 22, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject:Oracle is a time machine!! Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Stahlke, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Does it have any relation to year 1752 adjustment for leap year? Try following on unix .. cal 9 1752 September 1752 S M Tu W Th F S 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -Rachna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 12:55 PM Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: error 472
There is nothing vaguely resembling anything helpful in either the core dump or the PMON file. That is where the boat load of metalink docs say to start, but there is nothing in there that is in any way indicative of what is going on. Driving me nuts! There is no real commonality in when it crashes. April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein -Original Message- Sent: Friday, November 22, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Did you look in the PMON trace file? Does anything appear there that might shed some light on the problem? RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -Original Message- Sent: Friday, November 22, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I have cloned an 8.1.7.0 database from another instance (clone doc from metalink for Apps) on AIX 4.3.3. I don't THINK this is connected to the clone or (directly) to apps. Since I brought the database back up after the clone successfully, it keeps crashing with pick a process terminating instance due to error 472 It comes back when I start it. The interval between startup and crash appears arbitrary. What can be accomplished while the database is up is logical stuff... I can run apps jobs, I can update and select from tables, I can do ddl, I can manually log switch. Metalink has BOAT LOADS of docs on this happening, but I haven't found anything that I am permitted to see that has a resolution. I have an Itar, but that is not yet yielding any information. Has anyone seen similar problems, and (if so) IS there a resolution? Thanks in advance for any advice ajw April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(-O;6UU;FEC871I M;VXL(EN8VQU9EN9R!A='1A8VAM96YTRP@:7,@W1R:6-T;'D@8V]N9FED M96YT:6%L(%N9!F;W(@=AE(EN=5N95D('5S92!O9B!T:4@861DF5S MV5E(]N;'D[(ET(UA2!A;'-O(-O;G1A:6X@')O')I971AGDL('!R M:6-E('-E;G-I=EV92P@;W(@;5G86QL2!PFEV:6QE9V5D(EN9F]R;6%T M:6]N+B!.;W1I8V4@:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-U MF4L(1IW1R:6)U=EO;BP@9ESV5M:6YA=EO;BP@=7-E+!OB!C;W!Y M:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA;B!T M:4@:6YT96YD960@F5C:7!I96YT(ES('-TFEC=QY('!R;VAI8FET960@ M86YD(UA2!B92!I;QE9V%L+B!)9B!Y;W4@:%V92!R96-E:79E9!T:ES M(-O;6UU;FEC871I;VX@:6X@97)R;W(L('!L96%S92!N;W1I9GD@=AE('-E M;F1EB!I;6UE9EA=5L2!B2!R97!L2!E+6UA:6PL(1E;5T92!T:ES M(-O;6UU;FEC871I;VXL(%N9!D97-TF]Y(%L;!C;W!I97,N( T*#0I# M;W)P;W)A=4@4WES=5MRP@26YC+B!H87,@=%K96X@F5AV]N86)L92!P MF5C875T:6]NR!T;R!E;G-UF4@=AA=!A;GD@871T86-H;65N=!T;R!T M:ES(4M;6%I;!H87,@8F5E;B!S=V5P=!F;W(@=FER=7-ERX@5V4@W!E M8VEF:6-A;QY(1IV-L86EM(%L;!L:6%B:6QI='D@86YD('=I;P@86-C M97!T(YO(')EW!O;G-I8FEL:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE M9!AR!A(')EW5L=!O9B!S;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@ M6]U('1O(-AG)Y(]U=!Y;W5R(]W;B!V:7)UR!C:5C:W,@8F5F;W)E M(]P96YI;F@86YY(%T=%C:UE;G0N#0H-E1H92!I;F9OFUA=EO;B!C M;VYT86EN960@:6X@=AIR!C;VUM=6YI8V%T:6]N+!I;F-L=61I;F@871T M86-H;65N=',L(ES('-TFEC=QY(-O;F9I95N=EA;!A;F0@9F]R('1H M92!I;G1E;F1E9!UV4@;V8@=AE(%D9')EW-E92!O;FQY.R!I=!M87D@ M86QS;R!C;VYT86EN('!R;W!R:65T87)Y+!PFEC92!S96YS:71I=F4L(]R M(QE9V%L;'D@')I=FEL96=E9!I;F9OFUA=EO;BX@3F]T:6-E(ES(AE MF5B2!G:79E;B!T:%T(%N2!D:7-C;]S=7)E+!D:7-TFEB=71I;VXL M(1IW-E;6EN871I;VXL('5S92P@;W(@8V]P6EN9R!O9B!T:4@:6YF;W)M M871I;VX@8GD@86YY;VYE(]T:5R('1H86X@=AE(EN=5N95D(')E8VEP M:65N=!IR!S=')I8W1L2!PF]H:6)I=5D(%N9!M87D@8F4@:6QL96=A M;X@268@6]U(AA=F4@F5C96EV960@=AIR!C;VUM=6YI8V%T:6]N(EN M(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I871E;'D@ M8GD@F5P;'D@92UM86EL+!D96QE=4@=AIR!C;VUM=6YI8V%T:6]N+!A M;F0@95S=')O2!A;P@8V]P:65S+B -@T*0V]R]R871E(%-YW1E;7,L M($EN8RX@:%S('1A:V5N(')E87-O;F%B;4@')E8V%U=EO;G,@=\@96YS M=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S()E M96X@W=E'0@9F]R('9IG5S97,N(%=E('-P96-I9FEC86QL2!D:7-C;%I M;2!A;P@;EA8FEL:71Y(%N9!W:6QL(%C8V5P=!N;R!R97-P;VYS:6)I M;ET2!F;W(@86YY(1A;6%G92!S=7-T86EN960@87,@82!R97-U;'0@;V8@ MV]F='=AF4@=FER=7-ER!A;F0@861V:7-E('EO=2!T;R!C87)R2!O=70@ M6]UB!O=VX@=FER=7,@8VAE8VMS()E9F]R92!O5N:6YG(%N2!A='1A -8VAM96YT+@T*#0H-@ end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Re: Oracle is a time machine!!
strange, I get TO_CHAR(TH -- 10/15/1582 and not 10/05/1582 mo --- Freeman, Robert [EMAIL PROTECTED] wrote: Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you.  -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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! Mail Plus – Powerful. Affordable. Sign up now. http://mailplus.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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 system process for an oracle session
Gogala, Mladen wrote: Nope. Brazillians do not have football, only soccer. No, futbol. -- Regards, 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
I get the same date for Oct 5 - 15 (10/15/02). Good thing I don't deal with dates that far back. ;^) -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 11/22/2002 12:55 PM, Freeman, Robert [EMAIL PROTECTED] wrote: Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
The only indexes that enforce a constaint are primary key and unique key indexes. You can easily spot them using SELECT INDEX_NAME, OWNER FROM DBA_INDEXES WHERE UNIQUENESS = 'NONUNIQUE' However, beware, there may be some unique indexes that may have been created for performance reasons only, even though a secondary intent may be to enforce a unique constraint. For instance Social Security Numbers are probably unique, guranteed pretty much by the application but an index defined on them will be defined as unique as unique indexes perform better. Therefore the intent here was not to enforce the constraint but rather to design a better index. HTH Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 1:39 PM I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
TO_CHAR(TH -- 10/15/1582 -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Gregorian calendar input before 15th October 1582 or after 30th January 4247 is not valid. There was wide variation in the date of adoption of the Gregorian calendar. For example, in Great Britain and colonies, the Julian date Wednesday 2nd September 1752 Old Style was followed the next day by the Gregorian date Thursday 14th September 1752. Oracle is taking the calendar change (Julian-Gregorian) into account. -Original Message- From: Hayes, Scott [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle is a time machine!! It must have converted the date to metric. -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Seems like the machine is frozen in time . . . try the next day, too! insert into test values (to_date('10-06-1582','mm-dd-') ) -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
May be it was called Gregorian Saving Time. Imagine ripping off ten days off your desk calendar; And while you are at it, please check the smoke alarm battery, check your basement for leakage... Thank God they decided not to do it a every year affair. Then again, imagine this, when you get to bed tonight, turn your calendar and watch and sleep more - an hour longer, oh no, 10 days longer ... Holy cow! I am all for it. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 1:53 PM Nothing unusual here. Ten days were skipped in October, 1582. It was called the Gregorian Calendar Reform. Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- Sent: Friday, November 22, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Oracle is a time machine!! Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Stahlke, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
This may not do all you need but it will list out all the indexes in dba_indexes that do not have a corresponding entry in the dba_constraints table. You might have to put a few other criteria on it ... but this might get you in the right direction. select a.owner, a.index_name, a.table_owner, a.table_name from dba_indexes a where not exists ( select '' from dba_constraints b where a.owner = b.owner and a.index_name = b.constraint_name) -Original Message- Sent: Friday, November 22, 2002 12:40 PM To: Multiple recipients of list ORACLE-L I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Very tricky Robert. ;^) Spoiler Alert below!!! After some time to think about this, I did a quick search on Google and realized that this is when the calendar changed from Julian to Gregorian. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 11/22/2002 12:55 PM, Freeman, Robert [EMAIL PROTECTED] wrote: Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
For what I can recall, there was an adjustment in 1582 in the calendar to make it fit with the reality. They introduced the leap years and the cut 10 days in october 1582. I think they went from october 4 to october 15. At 10:19 2002-11-22 -0800, you wrote: It must have converted the date to metric. -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Louis Brouillette Analyste en informatique (DBA) Universite du Quebec a Trois-Rivieres Tel: (819) 376-5011 ext. 2435 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Louis BROUILLETTE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
okay, time to nip this discussion in the bud... First of all, there are/were 2 calendar systems. The gregorian and the juilan calendars. They both got out of synch with the seasons (due to no leap year or something). They were both adjusted forward a number of days to synch back up the real world.. One of them (the one Oracle uses) just so happened to jump forward in October 1582. The other one (used by UNIX) on a different date. Read about it here...http://serendipity.magnet.ch/hermetic/cal_stud.htm -Original Message- Sent: Friday, November 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L It must have converted the date to metric. -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
OEM Config Assistant
Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get invalid credentials. Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
ROLLBACK SEGMENT Tuning
Hi I noticed few of rollback segment in one of Database are having few waits.Is it necessary to create rollback segment with new size? Can u please some one send some infor on rollback segment tunning? Thx -Seema _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
Arup Nanda wrote: The only indexes that enforce a constaint are primary key and unique key indexes. You can easily spot them using SELECT INDEX_NAME, OWNER FROM DBA_INDEXES WHERE UNIQUENESS = 'NONUNIQUE' However, beware, there may be some unique indexes that may have been created for performance reasons only, even though a secondary intent may be to enforce a unique constraint. For instance Social Security Numbers are probably unique, guranteed pretty much by the application but an index defined on them will be defined as unique as unique indexes perform better. Therefore the intent here was not to enforce the constraint but rather to design a better index. HTH Arup Nanda There may also be another problem, with indexes created on FKs to avoid deadlocks - not quite 'performance index' as usually accepted even if they cannot fully be considered as 'constraint indexes'. If you want to detect them, you have to check in DBA_IND_COLUMNS and DBA_CONS_COLUMNS - and I feel too lazy to write the query now. -- Regards, 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Actually, I knew that part of it, I was thinking about that last night and wondered how Oracle would handle a date in that range (which in my mind is an invalid date). RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -Original Message- Sent: Friday, November 22, 2002 1:50 PM To: Multiple recipients of list ORACLE-L I think this is because during this time Pope Gregory removed 10 days from the calendar to align with solar calendar... check this out... http://webexhibits.org/calendars/timeline.html http://webexhibits.org/calendars/timeline.html From: Freeman, Robert Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Subject: Oracle is a time machine!! Date: Fri, 22 Nov 2002 09:55:21 -0800 Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). _ STOP MORE SPAM with the new MSN 8 http://g.msn.com/8HMDEN/2015 and get 2 months FREE* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Perhaps this Gregorian thing is just a government cover-up for a giant alien ship having stopped time for 14 days back then?? I think I've been watching to much X-Files. :-) RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -Original Message- Sent: Friday, November 22, 2002 2:34 PM To: Multiple recipients of list ORACLE-L okay, time to nip this discussion in the bud... First of all, there are/were 2 calendar systems. The gregorian and the juilan calendars. They both got out of synch with the seasons (due to no leap year or something). They were both adjusted forward a number of days to synch back up the real world.. One of them (the one Oracle uses) just so happened to jump forward in October 1582. The other one (used by UNIX) on a different date. Read about it here...http://serendipity.magnet.ch/hermetic/cal_stud.htm -Original Message- Sent: Friday, November 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L It must have converted the date to metric. -Original Message- Sent: Friday, November 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: OEM Config Assistant
You need to log in as sysman/oem_temp through the gui. The userid/passwd you defined are for the database, not the management server. John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Friday, November 22, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get invalid credentials. Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
I bet Goulet is old enough to remember when this happened. He's probably still pissed off at missing a paycheck. --Walt Weaver Bozeman, Montana -Original Message- Sent: Friday, November 22, 2002 11:19 AM To: Multiple recipients of list ORACLE-L I get the same date for Oct 5 - 15 (10/15/02). Good thing I don't deal with dates that far back. ;^) -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
I wanted to see what was actually stored. So I inserted the following dates in order 10/4/1582, 10/5, 10/15, 10/16. The result is: TO_CHAR(TH DUMP_DATE -- 10-04-1582 Typ=12 Len=7: 115,182,10,4,1,1,1 10-15-1582 Typ=12 Len=7: 115,182,10,15,1,1,1 10-15-1582 Typ=12 Len=7: 115,182,10,15,1,1,1 10-16-1582 Typ=12 Len=7: 115,182,10,16,1,1,1 As you can see, the date is stored as 10/15/1582, so the conversion occurs on the to_date function for the insert... Interesting... DF -Original Message- Sent: Friday, November 22, 2002 1:10 PM To: Multiple recipients of list ORACLE-L Actually, I knew that part of it, I was thinking about that last night and wondered how Oracle would handle a date in that range (which in my mind is an invalid date). RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -Original Message- Sent: Friday, November 22, 2002 1:50 PM To: Multiple recipients of list ORACLE-L I think this is because during this time Pope Gregory removed 10 days from the calendar to align with solar calendar... check this out... http://webexhibits.org/calendars/timeline.html http://webexhibits.org/calendars/timeline.html From: Freeman, Robert Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Subject: Oracle is a time machine!! Date: Fri, 22 Nov 2002 09:55:21 -0800 Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). _ STOP MORE SPAM with the new MSN 8 http://g.msn.com/8HMDEN/2015 and get 2 months FREE* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
The following will give you all the indexes that belongs to a constraint select distinct t.owner as table_owner, i.table_name, o.owner as index_owner, o.object_name as index_name,s.name as constraint_name,c.constraint_type from sys.cdef$ cd,dba_objects o,sys.con$ s,dba_constraints c,dba_indexes i, dba_tables t Where t.Table_Name = i.Table_Name and i.owner=o.owner and i.index_name=o.object_name and c.constraint_name=s.name and cd.Enabled = o.object_id and cd.con# = s.con# and o.owner not in ('SYS','SYSTEM') do a minus will get what you want. -Original Message- Sent: Friday, November 22, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Shao, Chunning INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 is a time machine!!
Yes. This is where it gets interesting. England and the colonies didn't adopt the Gregorian Calendar Reform until September, 1752. October, 1582 had its normal 31 days in the British Empire and September, 1752 had its normal 30 days outside the British Empire. So what does all this mean? Technically, Oracle's date routines are correct in some parts of the world but not others. Happy Friday, Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- Sent: Friday, November 22, 2002 11:40 AM To: Multiple recipients of list ORACLE-L Subject:Re: Oracle is a time machine!! Does it have any relation to year 1752 adjustment for leap year? Try following on unix .. cal 9 1752 September 1752 S M Tu W Th F S 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -Rachna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 12:55 PM Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Stahlke, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
Title: RE: Identifying indexes -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? This query will show all indexes that do not enforce a constraint. select owner, object_name from dba_objects where object_type = 'INDEX' and object_id not in (select enabled from sys.cdef$ where type# in (2, 3) and enabled is not null)
RE: Oracle is a time machine!!
I wonder if this will be fixed in 10i (11i in some parts of the world, but not others) ;) -Original Message- Sent: Friday, November 22, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Yes. This is where it gets interesting. England and the colonies didn't adopt the Gregorian Calendar Reform until September, 1752. October, 1582 had its normal 31 days in the British Empire and September, 1752 had its normal 30 days outside the British Empire. So what does all this mean? Technically, Oracle's date routines are correct in some parts of the world but not others. Happy Friday, Mark Stahlke Oracle DuhBA Denver Newspaper Agency -Original Message- Sent: Friday, November 22, 2002 11:40 AM To: Multiple recipients of list ORACLE-L Subject:Re: Oracle is a time machine!! Does it have any relation to year 1752 adjustment for leap year? Try following on unix .. cal 9 1752 September 1752 S M Tu W Th F S 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -Rachna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 12:55 PM Don't believe me?? Try this: create table test(the_date date); insert into test values (to_date('10-05-1582','mm-dd-') ); select to_char(the_date, 'mm/dd/') from test; What do you get? :-)) Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Stahlke, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
I don't get it. Do a minus? Mr. Shao could you explain that please. RWB Shao, Chunning [EMAIL PROTECTED]@fatcity.com on 11/22/2002 02:49:15 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: The following will give you all the indexes that belongs to a constraint select distinct t.owner as table_owner, i.table_name, o.owner as index_owner, o.object_name as index_name,s.name as constraint_name,c.constraint_type from sys.cdef$ cd,dba_objects o,sys.con $ s,dba_constraints c,dba_indexes i, dba_tables t Where t.Table_Name = i.Table_Name and i.owner=o.owner and i.index_name=o.object_name and c.constraint_name=s.name and cd.Enabled = o.object_id and cd.con# = s.con# and o.owner not in ('SYS','SYSTEM') do a minus will get what you want. -Original Message- Sent: Friday, November 22, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Shao, Chunning INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: OEM Config Assistant
I believe the OEM ca sets up the username/password of the owner of the management server repository. That is the uname/password used to start the management server. The username password you use to login to OEM defaults to sysman/sysman. That should get you in, but do remember to change the password ASAP, as it is a well known security loophole. Steve McClure -Original Message- Sardina Sent: Friday, November 22, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get invalid credentials. Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
A MINUS operator in SQL will find the differences between two queries. To find indexes that are not associated with constraints (PK, UK, or FK), you could you the following: select table_owner, table_name, column_name, column_position from dba_ind_columns minus select owner, table_name, column_name, position from dba_cons_columns; Of course, it wouldn't give you the name of the index, so you can make this an inline view for a larger query to hit DBA_IND_COLUMNS again would... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 3:03 PM I don't get it. Do a minus? Mr. Shao could you explain that please. RWB Shao, Chunning [EMAIL PROTECTED]@fatcity.com on 11/22/2002 02:49:15 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: The following will give you all the indexes that belongs to a constraint select distinct t.owner as table_owner, i.table_name, o.owner as index_owner, o.object_name as index_name,s.name as constraint_name,c.constraint_type from sys.cdef$ cd,dba_objects o,sys.con $ s,dba_constraints c,dba_indexes i, dba_tables t Where t.Table_Name = i.Table_Name and i.owner=o.owner and i.index_name=o.object_name and c.constraint_name=s.name and cd.Enabled = o.object_id and cd.con# = s.con# and o.owner not in ('SYS','SYSTEM') do a minus will get what you want. -Original Message- Sent: Friday, November 22, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Shao, Chunning INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Identifying indexes
1. Since DBA_CONSTRAINTS is already connecting to cons$ and cdef, why are you using them in your join ? 2. Why are you joining to dba_objects ?? What purpose does it fill ? 3. Why not just join dba_indexes to dba_constraints and rule out the kind of constraints you are not interested in ... i.e. Check Constraints ? -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 4:04 PM To: Multiple recipients of list ORACLE-L I don't get it. Do a minus? Mr. Shao could you explain that please. RWB Shao, Chunning [EMAIL PROTECTED]@fatcity.com on 11/22/2002 02:49:15 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: The following will give you all the indexes that belongs to a constraint select distinct t.owner as table_owner, i.table_name, o.owner as index_owner, o.object_name as index_name,s.name as constraint_name,c.constraint_type from sys.cdef$ cd,dba_objects o,sys.con $ s,dba_constraints c,dba_indexes i, dba_tables t Where t.Table_Name = i.Table_Name and i.owner=o.owner and i.index_name=o.object_name and c.constraint_name=s.name and cd.Enabled = o.object_id and cd.con# = s.con# and o.owner not in ('SYS','SYSTEM') do a minus will get what you want. -Original Message- Sent: Friday, November 22, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I am trying to find out what indexes are in the database only for performance reasons and do not enforce a constraint. What would be the query to do that? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Shao, Chunning INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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-2083,ora-1256 Urgent
Hi All, I have a down production system on Win NT4,Oracle 8.1.6. I just started getting ora-2083 database name has illegal character '-'. When I try to connect via svrmgrl after setting oracle_sid I get tns-12560 tns protocol adapter error. The other 2 instances work fine. Nothing on MetaLink has been helpful thusfar. Any ideas on how to resolve. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: OEM Config Assistant
The username password you use to login to OEM defaults to sysman/sysman. I just reread my response, and saw I botched the default password. The password is actually oem_temp. Steve McClure -Original Message- Sardina Sent: Friday, November 22, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get invalid credentials. Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 segments
Sorry it took so long to reply... The first direct-path mechanism in Oracle (i.e. Oracle v6.0 FASTLOAD on MVS platforms, followed by Oracle7 v7.0 SQL*Loader DIRECT=TRUE) did not create a separate segment. It merely made use of space in the existing table segment above the HWM and performed loads there. If the operation succeeded, then the HWM was simply moved to include the populated blocks. If the load failed, then the HWM would stay where it was -- no need for a rollback! The blocks would presumably just get eaten over time as the HWM advanced naturally... Starting in v7.1 and continuing today, all new direct-path (now referred to as APPEND) features create one or more separate TEMPORARY segment(s). When the load completes successfully, the TEMPORARY segment is merged into the table or index. If the operation fails, the TEMPORARY segment is left in place to be eventually cleaned up by SMON... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 4:13 AM Okay, it's early here, I'm fuzzy. I think I meant the same thing (I'd had one of those user calls in the middle of the night) what I think I meant to say was that any operation that would end up adding blocks above the HWM if it COMPLETED would do it by creating TEMP segments that are converted to data blocks on commit of the operation. basically, temp segments are created for any operation that does not touch existing blocks or am I still fuzzy? --- Tim Gorman [EMAIL PROTECTED] wrote: No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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! Mail Plus -
RE: partitioning questions
As of when? It's still listed as a costly option on the Oracle Store web page. The perpetual license is $10,000.00 per CPU for the U.S. market. Oracle 9i comes with lots of options many of which cost extra. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 22, 2002 8:09 AM To: Multiple recipients of list ORACLE-L That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
tns-12500 unable to start dedicated server process.
I have 3 instances on Oracle 8.1.6,Nt 4. They are all started but on one instance I connect via the listener. The only connection is at the server not using service name. I have the usual plenty of memory,swap,etc. It has been working fine until today. I even stopped the other 2 instances just to give more memory but still fails. The listener.ora has not changed. I have rebooted the server multiple times. Oracle gives me the same old answer increase processes parameter,add more memory,add more swap file,etc. Anyone have any fresh ideas on how to resolve. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 segments
thanks! there was no rush on the reply, I know some people on this list work on occasion (of course I'm not one of them yeah right but...) :) I think I've got it now. And I think I had it straight in my head but got it confused when it came out my fingertips. --- Tim Gorman [EMAIL PROTECTED] wrote: Sorry it took so long to reply... The first direct-path mechanism in Oracle (i.e. Oracle v6.0 FASTLOAD on MVS platforms, followed by Oracle7 v7.0 SQL*Loader DIRECT=TRUE) did not create a separate segment. It merely made use of space in the existing table segment above the HWM and performed loads there. If the operation succeeded, then the HWM was simply moved to include the populated blocks. If the load failed, then the HWM would stay where it was -- no need for a rollback! The blocks would presumably just get eaten over time as the HWM advanced naturally... Starting in v7.1 and continuing today, all new direct-path (now referred to as APPEND) features create one or more separate TEMPORARY segment(s). When the load completes successfully, the TEMPORARY segment is merged into the table or index. If the operation fails, the TEMPORARY segment is left in place to be eventually cleaned up by SMON... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 4:13 AM Okay, it's early here, I'm fuzzy. I think I meant the same thing (I'd had one of those user calls in the middle of the night) what I think I meant to say was that any operation that would end up adding blocks above the HWM if it COMPLETED would do it by creating TEMP segments that are converted to data blocks on commit of the operation. basically, temp segments are created for any operation that does not touch existing blocks or am I still fuzzy? --- Tim Gorman [EMAIL PROTECTED] wrote: No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: OEM Config Assistant
some rights has to granted to the user n for setting up the repository u may need to modify ur register entry for the mgmt svr. check out the installation notes to the set the values of key in the registry. Regards, Sathyanarayanan |+ || Mike Sardina| || cemail2@sprin| || tmail.com| ||| || 23/11/2002| || 01:29 | || Please respond| || to ORACLE-L | ||| |+ --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: OEM Config Assistant | --| Using OEM Configuration Assistant 2.2, I tried to set up a Management Server/repository, etc. and through the gui I created a user with a password, etc. Now when I try to log into the Management Server with that username/password, I get invalid credentials. Through sqlplus I can log into that username/password though. Any ideas on what the problem could be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Storing of number datatype in table
Thank u list for ur imm resp. It was the prob with numwidth. Now I have set the num width to 25 and s working fine. But whenever i stat the sql the default is set to 9. how do i change the def numwidth?? Regards, Sathyanarayanan |+--- || Arup Nanda | || arupnanda@ho| || tmail.com | || | || 22/11/2002 | || 19:38| || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: Re: Storing of number datatype in | | table | --| The number is more than the numwidth specified. Try this SQL set numwidth 13 SQL select trn_id from trnid; Your numwidth is perhpas defined as 9; so anything of more precision is displayed as exponetial notation; internally all numbers are stored the same. HTH Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 6:23 AM Dear List Have a look at the sql !!! SQL create table trnid 2 ( trn_id number(10)); Table created. SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/ 1 row created. SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/ 1 row created. SQL commit; Commit complete. SQL select trn_id from trnid; TRN_ID - 1.111E+09 1 can anyone tell why the number(10) is stored in exp format Regards, Sathyanarayanan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).