RE:
Hi I understand that Oracle's transaction mechanism is different from Microsoft's. Can I ask a dumb question ... do you have autocommit set to on by any chance ? If not, I THINK that Oracle starts a transaction by default anyway, and this persists until you execute COMMIT; or ROLLBACK; And if so, each SQL statement will commit itself. HTH GS -Original Message- Sent: Saturday, 22 September 2001 10:40 To: Multiple recipients of list ORACLE-L Hi Gurus, I am facing problem while comminting the transaction through OLEDB. Whenever i begin transaction thru ADO(OLEDB For ORACLE) it doesn't return any error,but whenever i try to either commit/rollback the transaction ,it returns error saying No transaction is active. But the same code works fine with SQL server using OLEDB for SQL. Also it works fine with Oracle 8.1.6 but returns error whenever i run it on oracle 7.1.6 Any Help is appreciated. TIA. __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Soman Manoj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Design Issue - Quick response appreciated
= HP also. Also, he want to see the related records from the child tables. I tried with the idea of sequence number generation but it was failing. Any ideas or suggestions are much appreciated. Thanks, Rao Maheswara Rao, Oracle DBA SunGard Securities -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
obfuscated data gets corrupted
Hi Has anyone else experienced the following ? Oracle 8.1.6.0.0 on Red Hat 6.2 Kernel 2.2.19 Table X in database A contains data encrypted using obfuscation toolkit. Table Y in database B is materialised view of table X. 1. When I decrypt table X using o t I get my data. 2. When I decrypt table Y using o t I get an ORA 28232 - Invalid input length for obfuscation toolkit. 3. When I e.g. select decrypt(col1) from (select * from X@A), I get an ORA 28232 - Invalid input length for obfuscation toolkit. 4. And ... when I export A and import to C and then select decrypt(col1) from A, I get an ORA 28232 - Invalid input length for obfuscation toolkit. For case 1 when I select length(col1), I get 24 for all rows. For cases 2, 3, and 4 when I select length(col1) rather than decrypt(col1), I get a variety of numbers from 2 to 24. Is there any way to access the data without decrypting it on database A first ? Thanks in advance Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WinNT / 8.0.5 / DECODE function affecting Century result in date
!! Please do not post Off Topic to this List !!Hi George
Your code looks fine to me and I have no idea why it wouldn't work. But if
it turns into a pain in the neck, why not write a wee PL/SQL function to do
the century thing for you and also trap nulls ?
There's a reason you might want to do this anyway: presumably you will
sooner or later start receiving dates in the current century, e.g. if your
file contains 000101 don't you want a function that will turn it into
01-JAN-2000 rather than 01-JAN-1900 ?
Cheers
Greg
-Original Message-
Sent: Wednesday, 12 September 2001 01:12
To: Multiple recipients of list ORACLE-L
date
List,
I have a 6-position column, bdate, in a text file that I am trying to
SQL*Load formatted as yymmdd. When I use the following SQL operations I get
the following results:
1. SQL Operation: (bdate POSITION(001:006) CHAR
TO_DATE(:bdate,'YYMMDD'))
Result in the database: Next Century, e.g. '300223' becomes
'23-FEB-2030'
2. SQL Operation (bdate POSITION(001:006) CHAR
DECODE(:bdate,NULL,NULL,TO_DATE('19'||:bdate,'MMDD')))
Result in the database: Next Century, e.g. '300223' becomes
'23-FEB-2030'
but when I take out the DECODE in item 2, I get the correct century. I only
use decode because there are null values in this column. Can somebody
explain to me what I am missing here?
Thanks,
George
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Hofilena
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Solomon
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Data Warehouse/weekly loads
!! Please do not post Off Topic to this List !!Another (really simple) approach to the problem is to avoid loading all the data in one hit, ie load say ten thousand rows, wait say sixty seconds to give the users' queries time to execute, and then load ten thousand more. The load takes longer, but you avoid bringing the database to its knees. Greg -Original Message- Sent: Thursday, 13 September 2001 08:20 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! If this is really a separate database, not just a separate instance (i.e. OPS), then there may not be any really good options. You still have to move the new data to the other database. Replication MIGHT work, but don't take it lightly! Transportable tablespaces, perhaps in conjunction with partitioning, might also work. (And might work really slick with something like EMC BCV's or transportable split disk mirrors to move the datafiles about!) What might work best will depend on some unmentioned factors. Are any of the dynamic tables the same tables that are being loaded weekly? Is there any essential referential integrity between those two sets of tables? How current must the dynamic data be in the database where the weekly loads take place? How large are the dynamic tables? Can they just be totally replaced in the loading database once a week? Are you appending the tables with your weekly loads or replacing them? On the other hand, if it is really only a different instance in an OPS environment, no data movement is necessary. I have done this using a new partition for each of the weekly loads - where the tables being bulk loaded were not also dynamic. That might work well - depending on the implications of rebuilding any global indexes on the partitioned table(s). (I had the luxury of doing the weekly bulk loads and index creation on weekends, during the dead of night, when the users were fast asleep.) -Don Granaman [certifiable Orasaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 12, 2001 11:10 PM What are the options for being able to keep a data warehouse available to users, but still being able to do weekly data loads during regular business hours? Our warehouse is large enough that we are going to have to create a seperate instance on another box to perform the weekly data loads on. However, there are some tables that do hold some dynamic data. After performing the weekly load, how do you synchronize all the changes on both databases into one database so that the users have all the data? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: I/O Performance/bottlenecks on EMC Symmetrix
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Life: is that really OT?
My deepest sympathies to anyone who has a personal connection to the events that have occurred. It's sad that people are celebrating. Perhaps some of them have their own sorrows and horrors, perhaps some are young and stupid, and perhaps some are just sick or evil or both. But I think we've seen right here on Oracle-L that inappropriate responses are not confined to the Middle East. But saddest of all, IMHO, is the mindview expressed so well by the following comment. We need to be cool, calm, convinced and know what we want to hit - and utterly destroy it. Completely. Well, that's probably a very succinct way of expressing the core beliefs of each of the murderers involved in creating this ghastly mess. To anyone who calls for an air strike anywhere as a result of this, I hope that lots and lots of people stand up and reply, THIS WAS AN AIR STRIKE, YOU IDIOT. This is EXACTLY what you're suggesting should be repeated in someone else's country. Innocent people suffering, lots of them. Many of whom will probably sincerely regret the events of 11th September 2001 and feel no sympathies for those responsible, regardless of what their government may be saying at the time. Perhaps you could be cool, calm, convinced and know what you want your country to ACHIEVE ... and presumably the primary goal is that this tragedy is never repeated, anywhere in the world. I suggest that airport security looks like a good place to start, possibly also the lock on the door from the cabin to the cockpit. Please leave the terror to the terrorists. Again, to those suffering our thoughts are with you. Greg Solomon London UK -Original Message- Sent: Wednesday, 12 September 2001 10:35 To: Multiple recipients of list ORACLE-L Hi Listers Just wanted to express it. Sad about the bombings.. Sadder about people rushing to defend it. Sadder about people rushing to own it! Hope you are all safe. Regards Just another Human -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cyril Thankappan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Life: is that really OT?
Well, I spent 6 hours writing my email but I guess I still said something stupid. My apologies for mentioning any specific area of the globe. I only said Middle East because that's where the CNN cameras were in the newsclip that I saw. I hope no-one is offended, I obviously should have thought for a bit longer before I spoke. Greg Solomon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Life: is that really OT?
To those who replied Scott, I am surprised by your statement that I am a misinformed individual, particularly given your implied claim that the Pentagon is not a military target. Lisa, with respect I would suggest that domestic air security would do more than military action to prevent this problem reoccurring and that the chances of your plane being hijacked are so low as to be laughable. To the other two, please can you explain how carpet bombing a country is NOT a cowardly massacre of civilians ? Target practice is an unfortunate phrase Ken, you are talking about killing people. Please don't use euphemisms for this, it's obscene. If you can't understand why I'm saying this, try using your language to describe recent events in the US. I'm not apologising for anything, nor am I advocating forgiveness for the murderers involved. I think they should be brought to justice and I agree that any country sheltering these criminals is effectively declaring war on the US, and that the US has a right to use SUFFICIENT force to get the government of said country to change their minds. Innocent people (i.e. who disagree strongly with the policy of their government) will be hurt, maimed, and killed in the process and I hope that you regret that as much as I (and they) regret the events on the 11th September 2001. Regards Greg Target practice for one of our Trident missile boats would be OK by me. I'm a former submariner. Ken WE SHOULD CARPET BOMB ANYONE AND GOVT WHO SUPPORTS THIS COWARDLY MASSACRE ON CIVILIANS! GIVE ME THE BASTARD's AND I'LL DO IT MYSELF!!! Greg, you have a point. I don't fully agree with killing to retaliate. However, if the US doesn't do anything about this, there's no guarantee this won't happen again. I went home yesterday because I was scared out of my pants, and I'm in Florida. No one anywhere, US or otherwise, is safe if this kind of terrorism can't be prevented. I'm afraid to get on a plane to attend my brother's wedding in a couple of months. Unfortunately I don't think we have a choice. We can't do NOTHING or issue a 'strong statement'. (Especially with the public speaking skills of our president - ugh.) Would you have such a forgiving attitude if this would have happened in London, and you lost your family? Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 Greg, You are a sadly misinformed individual. Please, before spouting apologist garbage like this in a public forum, learn something about what you are discussing. Talk to some of your SAS guys, and pay attention to what they have to say. They know more about the psychology of terror than you could hope to comprehend. The only way to prevent this from occuring again is to ensure that the capability and WILL to do it no longer exists. Terrorists have left no room for negotiation. They do not attack militarily viable targets. They target civilians - children, crippled, men, women, etc. All non-combatants. Terrorists are the lowest form of coward this earth has ever seen. They will not stop until they are forced to. The world is a big school yard. You will be pushed until you push back. Get used to it. These terrorists have made themselves into a military target through their actions. Anyone aiding or sheltering these terrorists (whoever they may be) is no longer a non-combatant. I hope the US government responds with wisdom, accuracy, and ruthless efficiency. Scott Shafer San Antonio, TX Greg Solomon wrote: My deepest sympathies to anyone who has a personal connection to the events that have occurred. It's sad that people are celebrating. Perhaps some of them have their own sorrows and horrors, perhaps some are young and stupid, and perhaps some are just sick or evil or both. But I think we've seen right here on Oracle-L that inappropriate responses are not confined to the Middle East. But saddest of all, IMHO, is the mindview expressed so well by the following comment. We need to be cool, calm, convinced and know what we want to hit - and utterly destroy it. Completely. Well, that's probably a very succinct way of expressing the core beliefs of each of the murderers involved in creating this ghastly mess. To anyone who calls for an air strike anywhere as a result of this, I hope that lots and lots of people stand up and reply, THIS WAS AN AIR STRIKE, YOU IDIOT. This is EXACTLY what you're suggesting should be repeated in someone else's country. Innocent people suffering, lots of them. Many of whom will probably sincerely regret the events of 11th September 2001 and feel no sympathies for those responsible, regardless of what their government may be saying at the time. Perhaps you could be cool, calm, convinced and know what you want your country to ACHIEVE ... and presumably the primary goal is that this tragedy is never repeated, anywhere in the world. I suggest that airport security looks like a good place
Restoring from half a backup
Hi Can someone help me out with a restore question ? Suppose that I have a cold backup of users.dbf as at date X, and all other files as at date Y. All the business data would be in users.dbf, all the indexes would be in the other files. Would it be possible to build a database using resetlogs or something ? Thanks in advance. Cheers Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: checkpoint message
Which as we all know courtesy of Mike Hately, stands for redo trace file manager, now a discontinued feature ... greg -Original Message- Sent: Wednesday, 22 August 2001 03:50 To: Multiple recipients of list ORACLE-L Or just be like me and spend most of the time saying rtfm :) joe Mohan, Ross wrote: smacks of incremental checkpointing...new implemented in 8i i thinkthe RBA is a redo block address. More than that, and you'll have to smoke some DBA crack, make some guru mushroom tea, hack the internals, write a book, and then become highly enigmatic and largely helpful. -Original Message- Sent: Tuesday, August 21, 2001 10:01 PM To: Multiple recipients of list ORACLE-L I get the same kind of message, anyone knows what it means? Saludos, Veronica Levin Enriquez Administrador AIX Compañía Cervecera de Nicaragua -Mensaje original- De: mala singh [mailto:[EMAIL PROTECTED]] Enviado el: Lunes, 23 de Octubre de 2000 03:01 p.m. Para: Multiple recipients of list ORACLE-L Asunto: checkpoint message Hi all I think there is some wait in our checkpointing.please correct me.I received the following message in alert.log file Beginning log switch checkpoint up to RBA [0x55.3.11], SCN: 0x.000505b1 Completed checkpoint up to RBA [0x55.3.11], SCN: 0x.000505b1 Please advice me for correction. Thanks in advance. Mala DBA-USA _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mala singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW: An SQL question , not easy ;-)
oops, should be not
:o)
select a.call_start, count(b.call_start) interruptions
from phone_call a, phone_call b
where a.call_start = b.call_start and a.call_end b.call_start
group by a.call_start
Cheers
Greg
-Original Message-
Sent: 15 August 2001 15:43
To: '[EMAIL PROTECTED]'
Or use a self-join
select a.call_start, count(b.call_start) interruptions
from phone_call a, phone_call b
where a.call_start = b.call_start and a.call_end b.call_start
group by a.call_start
-Original Message-
Sent: 15 August 2001 16:02
To: Multiple recipients of list ORACLE-L
Yes, so the maximum is 3, between 12:25 and 12:30. To explicitly show the
maximum (and a little histogram) you could amend it as follows: -
declare
l_count pls_integer := 0;
l_max_count pls_integer := 0;
begin
for rec in (
select call_start time, 1 incr from table
union all
select call_end time, -1 incr from table
order by 1
) loop
l_count := l_count + rec.incr;
if( l_count l_max_count ) then
l_max_count := l_count;
end if;
dbms_output.put_line( to_char(rec.time) || ' - ' || l_count
|| ' ' || lpad('*',l_count) )
end loop;
dbms_output.put_line( 'Maximum concurrent calls = ' ||
to_char(l_max_count) );
end;
Regards
David Lord
-Original Message-
From: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
- -
01-AUG-2001 12:10 01-AUG-2001 12:40
01-AUG-2001 12:15 01-AUG-2001 12:30
01-AUG-2001 12:25 01-AUG-2001 12:55
01-AUG-2001 12:45 01-AUG-2001 12:47
-
01-AUG-2001 12:10 - 1
01-AUG-2001 12:15 - 2
01-AUG-2001 12:25 - 3
01-AUG-2001 12:30 - 2
01-AUG-2001 12:40 - 1
01-AUG-2001 12:45 - 2
01-AUG-2001 12:47 - 1
01-AUG-2001 12:55 - 0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lord, David - CS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing)...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Solomon
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: An SQL question , not easy ;-)
Or use a self-join
select a.call_start, count(b.call_start) interruptions
from phone_call a, phone_call b
where a.call_start = b.call_start and a.call_end b.call_start
group by a.call_start
-Original Message-
Sent: 15 August 2001 16:02
To: Multiple recipients of list ORACLE-L
Yes, so the maximum is 3, between 12:25 and 12:30. To explicitly show the
maximum (and a little histogram) you could amend it as follows: -
declare
l_count pls_integer := 0;
l_max_count pls_integer := 0;
begin
for rec in (
select call_start time, 1 incr from table
union all
select call_end time, -1 incr from table
order by 1
) loop
l_count := l_count + rec.incr;
if( l_count l_max_count ) then
l_max_count := l_count;
end if;
dbms_output.put_line( to_char(rec.time) || ' - ' || l_count
|| ' ' || lpad('*',l_count) )
end loop;
dbms_output.put_line( 'Maximum concurrent calls = ' ||
to_char(l_max_count) );
end;
Regards
David Lord
-Original Message-
From: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
- -
01-AUG-2001 12:10 01-AUG-2001 12:40
01-AUG-2001 12:15 01-AUG-2001 12:30
01-AUG-2001 12:25 01-AUG-2001 12:55
01-AUG-2001 12:45 01-AUG-2001 12:47
-
01-AUG-2001 12:10 - 1
01-AUG-2001 12:15 - 2
01-AUG-2001 12:25 - 3
01-AUG-2001 12:30 - 2
01-AUG-2001 12:40 - 1
01-AUG-2001 12:45 - 2
01-AUG-2001 12:47 - 1
01-AUG-2001 12:55 - 0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lord, David - CS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing)...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Solomon
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Re: Whatever became of ORAC
I reckon the best scene was when Orac plays a chess game against a pro, and the stake is the ship and Blake's life. The game is a variant with a truly awesome yet simple rule change - you can move whenever you like, rather than waiting for the other guy all the time. Orac in his a-retentive voice is casually making about one move to the other guy's four, and the intonation manages to make it totally clear that he wins the game out of self-respect rather than because he gives a damn about the wager. Greg Solomon Oracle Geezer -Original Message- Sent: Monday, 06 August 2001 17:51 To: Multiple recipients of list ORACLE-L In the UK, ORAC was a computer of the cult TV series Blake's Seven. Orac was the most sophisticated artifical intelligence in the universe. It had an amazing array of technical abilities but it also had some problems. Problems It had a snappy voice and was often impatient with anyone unfortunate enough to ask it a question. It also showed little concern for the rest of the crew who it regarded with scorn. In short, Orac would much rather spend time contemplating the mysteries of the universe than spending time with mere mortals. Sorry for the sideline ! Steve Parker Technical Consultant LIS -- Logistics Internet Systems Ltd. Knaves House, Knaves Beech Business Centre Loudwater, High Wycombe Buckinghamshire , HP10 9QR, United Kingdom Telephone: +44 (0) 1494 540235 Facsimile: +44 (0) 1494 488824 E Mail: [EMAIL PROTECTED] -- Orr, Steve sorr@rightnoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] w.com cc: Sent by: Subject: Whatever became of ORAC root@fatcity. com 06/08/2001 17:21 Please respond to ORACLE-L Do anyone know whatever became of ORAC. It was an attempt to build an open source web-based DBA tool in Perl but it doesn't look like much has happened. Here's a link. http://www.tux.org/orac-dba/news.html Steve Orr -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: completely off-topic question...
) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
My boss tells me that two major projects have come out of Berkeley in the last 50 years. One was BSD; the other was LSD ... go figure. -Original Message- Sent: Tuesday, 10 July 2001 19:46 To: Multiple recipients of list ORACLE-L Registry, services? OH wait must be an NT thing, sorry, i was having an acid flashback that unix was going down the tubes. Sorry, joe Coed-naked Database Administration Find 'em fragmented, leave 'em coalesced. [EMAIL PROTECTED] 07/10/01 01:26PM Hello, I need to recreate a database, basically make a complete copy of one of the existing databases on a new server. My networking people already copied all datafiles, control file, log files, init file, etc to the target server. They also reproduced operating system directory structure. What I need to do is to make the second part of the task work, create the database, services, etc and make it run. I have not done it before and if I was doing it I would probably do export/import type of thing but the higher ups would like to have it done this way. One of the people has an idea suggesting just recreating the registry setting by exporting it from the existing machine to the new one. The other option is to reinstall Oracle but I am not sure how to make it to accept existing physical components(datafiles, control file, etc). If someone has a suggestion I would greatly appreciate it. Thank you in advance. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 12514 Error
Hi Definitely check whether you can ping the server from the client. If not, then it's a network config problem. After that, I once had this problem when a highly creative user (actually, our CTO) had entered a setting for Oracle Names when we were using local names only. I had to delete the setting. If that doesn't help, suggest that you turn on the trace files for a) the client you're trying to set up, and b) another client which is known to work OK. Comparing the trace files should give you a few clues. HTH Cheers Greg -Original Message- Sent: Tuesday, 10 July 2001 16:56 To: Multiple recipients of list ORACLE-L Hi, I had installed oracle 8.1.7 on WINNT and installed oracle client with administrator utility on win2k system. When i want to connect to this client on win2k to server on winnt. But i am getting the error ORA:12514. Can any one slove this for me. The listener.ora and tnsnames.ora files seems to be ok. Thanks in advance Bhanu __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gorthy BhanuPrakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert empty table to a message
Hi Helen Well, to keep it really really really simple ... spool ... SELECTDECODE(COUNT(*), 0, 'Hello there. Today's batch run contains no reports, so that makes your morning easy, doesn't it.If you have nothing else to do, please feel free to drop by for a coffee around elevenish, it would be great to catch up. By the way, did you know that nine out of ten snowmen die in heat related incidents ?') FROM VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID; SELECT RPAD(RTRIM(LTRIM(V$DATABASE.NAME))||'_'||C_REPORT_ID.REP_FILE_NAME, 30),NAME,ADDRESS,FLOOR,ROOMFROM VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID; spool off -Original Message-From: Helen rwulfjeq [mailto:[EMAIL PROTECTED]]Sent: Wednesday, 27 June 2001 23:50To: Multiple recipients of list ORACLE-LSubject: convert empty table to a message Hello, I have a file to contain info about the reports detailed info (stored in a temp table in the DB) we created every night. Such as: report ID, report_name... However, sometimes there is no report generated overnight. In this case, the temp table would be empty. But this file will still spool out anyway for user to review the information about the report request. My question is: When the table is empty, is there a way that I can convert the empty lines in the file like "no report generated". I tried DECODE, but doesnot work because there is no row return from the table. spool ...SELECT RPAD(RTRIM(LTRIM(V$DATABASE.NAME))||'_'||DECODE(C_REPORT_ID.REP_FILE_NAME, NULL, 'NO REPORT CREATED', ' ', 'NO REPORT CREATED'), 30),DECODE (C_REPORT_ID.REPORT_ID, NULL, 'NO REPORT CREATED', ' ', 'NO REPORT CREATED'),NAME,ADDRESS,FLOOR,ROOMFROM VW.C_EXPORT_CYCLE, C_REPORT_ID, V$DATABASEWHERE C_REPORT_ID.REPORT_ID = C_EXPORT_CYCLE.REPORT_ID;spool off Do you have any suggestions? Thanks in advance Helen Do You Yahoo!?Get personalized email addresses from Yahoo! Mail - only $35 a year!http://personal.mail.yahoo.com/
RE: Common Oracle RDBMS Misconceptions
Hi Slightly off-topic ... if you're interested in a dictionary of clear definitions of mystical concepts, may I recommend ... http://www.sucs.swan.ac.uk/~arthur/jargon/html/entry/tail-recursion.html There's also a well-written boil-down of the last 50 years of IT development into one and a half paragraphs on http://www.sucs.swan.ac.uk/~arthur/jargon/html/entry/Infinite-Monkey-Theorem .html Cheers Greg -Original Message- Sent: Thursday, 28 June 2001 14:21 To: Multiple recipients of list ORACLE-L I want pointers to some more articles like that so that I rid myself of the disease called 'Common Oracle RDBMS Misconceptions' enlighten me with clear explanations along with proof coz I am a novice Oracle Certifiable DBBS - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 26, 2001 4:03 PM On Jun 26, 2001 at 01:05:59AM, novicedba wrote: Hi everyone, I visited Jeremiah Wilton's web page http://www.speakeasy.net/~jwilton I was shocked to read Hot backup mode explained If this is true then I may be a victim of a disease called 'Common Oracle RDBMS Misconceptions' . Somebody help me!! (Jim carrey-MASK style) Please help me. If some one has few more articles like this enlighten me What a help do you need? -- Vladimir Begun | The best things in life are for a fee. http://vbegun.net/ | http://vbegun.net/wap/ | [EMAIL PROTECTED]| -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: novicedba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Function slows the select
Hi Try replacing GetSid with (select SYS_GUID() from dual) in the update statement, ie don't use a function. Or use DECLARE vGetSid Varchar2(4000); BEGIN select SYS_GUID() into GetSid from dual; Update UpdateTable--Varosokat updatel Set UpdateTable_Column1= -- (Select distinct 'Some string here'||vGetSid||UpdateTable_Column2||Logo.Logo_Filenev From VarosTorzs,Logo, Link_keszit Where logo_varos_unique_az = link_keszit_varos_az and logo.logo_azonosito1= 'KKEPX' and logo.logo_azonosito2= '20200' and logo_varos_unique_az = varos_unique_azonosito ) Where UpdateTable_Column2 Is Not Null ;END; HTH Greg -Original Message-From: Csillag Zsolt [mailto:[EMAIL PROTECTED]]Sent: Wednesday, 27 June 2001 12:20To: Multiple recipients of list ORACLE-LSubject: Function slows the selectHi,In the following select statement I have a function "GetSid" that slows theupdate for hours even if the Link_keszit updatable table consists of a few rows.Update UpdateTable--Varosokat updatel Set UpdateTable_Column1= -- (Select distinct 'Some string here'||GetSid||UpdateTable_Column2||Logo.Logo_Filenev From VarosTorzs,Logo, Link_keszit Where logo_varos_unique_az = link_keszit_varos_az and logo.logo_azonosito1= 'KKEPX' and logo.logo_azonosito2= '20200' and logo_varos_unique_az = varos_unique_azonosito ) Where UpdateTable_Column2 Is Not Null ;The GetSid function is:CREATE OR REPLACE Function GetSid Return VarChar2 ASBEGIN return(SYS_GUID());END GetSid;So the problem is that _with_ GetSid function it takes many hours instead of a few minutes.Thank you in advanceZsolt Csillag,Hungary
SQL Net Connection fails - bizarre
Hi I have an NT server which successfully does a SQL Net connection to various databases on other boxes. It can ping database X by ip address, but can't do a SQL Net connection (by ip address) to it. I have another box with the same default gateway, subnet mask, and with all other settings in Lan Connections config identical. It CAN connect to the database. tnsnames.ora files are copies of one another. Funny, I've never seen that happen before. :-) Any suggestions ? Cheers Greg
RE: NOLOGGING FEATURE
Hi Raj RTFM is a relational trace file management system. Originally released as an add-on with 8.0.0.6, I think it now comes standard with 9i. Cheers Greg -Original Message- Sent: Monday, 25 June 2001 09:52 To: Greg Solomon Greg I'm curious.. Whats RTFM is?? Raj -Original Message- Sent: 22 June 2001 17:04 To: LazyDBA.com Discussion Hi Regis Delete nologging sounds like a cool feature. I assume that nologging would tell Oracle not to bother keeping an old image of the deleted row in the rollback buffer, because I promise not to do a rollback. I RTFMed but couldn't find it. Did a few quick trials in sqlplus, 816 on Red Hat Linux. Results are below. I have two questions. 1. Nologging seemed to be generating MORE redo than normal delete. !!? 2. Was able to roll back after a delete nologging. !!? Am I being stupid or something ? Oops, that's three questions. Cheers Greg SQL delete greg where b=2; 2 rows deleted. Execution Plan -- 0 DELETE STATEMENT Optimizer=CHOOSE 10 DELETE OF 'GREG' 21 TABLE ACCESS (FULL) OF 'GREG' Statistics -- 0 recursive calls 6 db block gets 1 consistent gets 0 physical reads 544 redo size 646 bytes sent via SQL*Net to client 548 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL rollback; Rollback complete. SQL delete greg nologging where b=2; 2 rows deleted. Execution Plan -- 0 DELETE STATEMENT Optimizer=CHOOSE 10 DELETE OF 'GREG' 21 TABLE ACCESS (FULL) OF 'GREG' Statistics -- 0 recursive calls 8 db block gets 1 consistent gets 0 physical reads 692 redo size 646 bytes sent via SQL*Net to client 558 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL rollback; SQL select * from greg; A B -- -- 1 2 1 2 Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db_file_multiblock_read_count
Title: RE: db_file_multiblock_read_count Hi A year or two back, the suggestion (on solaris at least) was to avoid using vmstat with the first parm set to a value lower than 10, because the act of measuring perf becomes a drain if you do it every 5 seconds(or less). I tend to use vmstat 10 10. Cheers Greg -Original Message-From: Mohan, Ross [mailto:[EMAIL PROTECTED]]Sent: Monday, 25 June 2001 17:37To: Multiple recipients of list ORACLE-LSubject: RE: db_file_multiblock_read_count geez. Load a trial copy of MKS on NT and get back into da swing of tings, gal! :) -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Monday, June 25, 2001 12:23 PMTo: Multiple recipients of list ORACLE-LSubject: RE: db_file_multiblock_read_count Thanks Guy. I don't even have a unix system here to do a man page. Talk about BOREDOM. Lis -Original Message- From: Guy Hammond [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 11:28 AM To: Multiple recipients of list ORACLE-L Subject: RE: db_file_multiblock_read_count Hi Lisa, IIRC, the first line of vmstat gives you cumulative values since system boot. And "2 10" means "every 2 seconds, 10 times" (on Solaris, at any rate). Cheers, g -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Monday, June 25, 2001 3:23 PMTo: Multiple recipients of list ORACLE-LSubject: RE: db_file_multiblock_read_count 2. vmstat - look at the two columns that start with pg. Read the man page and it will explain it to you in detail. Also, when you use vmstat be sure to give it an interval (like vmstat 2 10 - this means display stats once every 2 seconds for a duration of 10 seconds). The first line returned by vmstat will always be garbage (same with iostat).
RE: How to do a bitwise OR from SQL*Plus
Hi Would external procedures be a way of doing this if bitand didn't exist ? Just that there is a lot of very cool C code out there. Have RTFM, but I'm a C compiler dunce and cc turned out to stand for completely confused :-) Has anyone managed to use external procedures ? Is it hard to do ? What's the stability ? And performance ? Cheers GS -Original Message- Sent: Thursday, 21 June 2001 16:01 To: Multiple recipients of list ORACLE-L why do you need to do a bitwise or within sqlplus? Good question... I have a really good engineer who is working with C and Oracle OCI. He's developing an install routine and assures me that he needs bitwise operators from SQL because he can do some really powerful things with them. Then he said SQLServer and MySQL had them so the challenge was on. Turns out I was able to give him what he wanted with a little bit of help from something I found in a google search. There is an undocumented bitand function in oracle and it appears it must be called from another function. (Why?) You can see how this function is used in some of the data dictionary view creation scripts. Here's and example of bitand... SQL select sum(bitand(12,11)) bitand from dual; BITAND -- 8 If you can get a bitand you can do a bitor... SQL select sum(12+11-bitand(12,11)) bitor from dual; BITOR -- 15 The math... 1100 12 1011 11 bitand 1000 8 1100 12 1011 11 bitor 15 Not an alpha geek today, Steve Orr -Original Message- Sent: Thursday, June 21, 2001 7:06 AM To: Multiple recipients of list ORACLE-L but, why do you need to do a bitwise or within sqlplus? just curious. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 20, 2001 5:51 PM To: Multiple recipients of list ORACLE-L OK, for the alpha geek award of the day... Who can tell me how to do a bitwise or from SQLPlus -- NOT PL/SQL? Isn't there an internal undocumented bitand function and how could you use that to implement a bitor function from SQL? Steve Orr -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
timed statistics
Hi Has anyone experienced any problems after setting timed_statistics to true on a prod database (at system level) ? Can anyone give me a guess as to the performance hit of doing this ? Running Oracle 8.1.6, there are fairly predictable periods of high, medium, and low load so I can avoid collecting stats at peak periods if necessary. Cheers Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT: Working from home
Yeah, now it's one line per day. -Original Message- Sent: Monday, 18 June 2001 13:00 To: Multiple recipients of list ORACLE-L Didn't IBM have a standard, something like a good programmer will produce ten lines of code per day? That was in the days before OOP, though. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Christopher Spence [SMTP:[EMAIL PROTECTED]] Sent: Saturday, June 16, 2001 2:05 PM To: Multiple recipients of list ORACLE-L Subject:RE: OT: Working from home My theory is this. A loosely used ratio for performance reviews. (Number of Bugs Resolved x Number of Projects x Number of lines of code - (Bugs introduced in your code x 500)) But use this with a grain of salt as many things are involved in programming and lines of codes, bugs, and number of products are all relative to the current situation. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Saturday, June 16, 2001 3:55 AM To: Multiple recipients of list ORACLE-L On Friday 15 June 2001 06:00, Rachel Carmichael wrote: There was a manager in that same shop who measured her programmers abilities by the number of lines of code they wrote in a day. She also said to me once I don't like to waste time on design Rachel, Remember the Dilbert where the PHB tells the engineers that he'll pay a cash incentive for every bug the find and fix? Wally leave the meaning saying I'm gonna code me a minivan! As for lines of code, one could get even by writing succint obtuse code before leaving. ;) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
OFA Question
Hi All Running Oracle 817 on Red Hat Linux. File layout is as follows. oradata3/prod: control01.ctl indx01.dbf redo01.log system01.dbf tools01.dbf oradata4/prod: control02.ctl redo02.log temp01.dbf oradata6/prod: control03.ctl rbs01.dbf redo03.log oradata7/prod: redo01b.log users01.dbf oradata8/prod: archives redo02b.log oradata9/prod: archives redo03b.log Where archives is a dir containing online dbf backups and archived redo logs. Is there ANY reason at all not to use spare space on oradata 3,4,6,7 to store other files ? They won't be read or written during times when the database is under load. Cheers GS -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Solomon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: querry..
hi well, one way to do it is to create a stored function create or replace package pkg_select is function cmb(p1 varchar2, p2 varchar2) return varchar2; end; create or replace package body pkg_select is function cmb(p1 varchar2, p2 varchar2) return varchar2 is begin if p1 = 'A' and instr(p2, 'A|B|C|D|E') 0 then return 'TRUE'; elsif p1 = 'B' and instr(p2, 'A|B|C|D|E|F|G|H|etc...') 0 then return 'TRUE'; else return 'FALSE'; end if; exception when others then return SQLERRM; end cmb; end pkg_select; then do select col1, col2 from table1 where pkg_select.cmb(col1, col2) = 'TRUE'; This keeps your select nice and simple, also you can create a function-based index to give fast performance. Rgds Greg -Original Message-From: Saurabh Sharma [mailto:[EMAIL PROTECTED]]Sent: Thursday, 31 May 2001 09:50To: Multiple recipients of list ORACLE-LSubject: querry.. hi list, how can i select two columns from a table based on condition that they are selected in specified combinations. let me.. table 1 has 2 columns col1, col2. both cols have values , say, alphabets. a,b,c,d,e,f,... i'want to select like FOR VALUE OF COL1 IN A, col2 must fetch only between A-E for value of col1 in B, col2 must be between A-X -- and so on.. i want to define this combination, so i should get only these pair of values. any suggestions. thanks. saurabh
