Re: USER DEFINED FUNCTIONS
"Jamadagni, Rajendra" wrote: > > select to_char(1) from dual; > > to_char is a user defined function (already built for you) by oracle. I am > yet to find someone who says UDF is a bad thing ... > > Raj > __ > Rajendra Jamadagni MIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > > > Name: ESPN_Disclaimer.txt >ESPN_Disclaimer.txtType: Plain Text (text/plain) > Encoding: 7bit I don't consider UDFs to be bad things per se. It's just what developers do out of them. It's just like triggers. A carefully written trigger can add less overhead than a regular index, for instance. That is, unless it executes queries of death. It's exactly the same stuff with UDFs. It all depends on how they are written. The only problem is that when they are used in the SELECT LIST they are called once for each row returned, like say a correlated subquery. In the hands of your average, middle-of-the-bell-curve developer, it can become a lethal weapon. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Do you use RMAN? DB clone problem
I use this technique only as a failover routine, if the production machine
were to crash: Cloning I would use standards methods as it is much faster:
But once u restored the database you can rename it.
But the steps that I take are
1.Install executables on other machine as same owner and group id as
original database(this is very important)(RESTORE using Netbackup rather
than re-install)
2.Update bp.conf in oracle home and /usr/openv/netbackup/
3. set nb_ora_client=Original client
4.startup taget database nomount
5.connect rman catalog
6.Run scripts
Sam
p.s. scripts I use are
run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
alter database mount;
}
--select min(scn) from (select max(next_change#)scn from
v$archived_log
group by thread#); -- get the last scn in svrmgrl
run {
set until scn=100075926; allocate channel ch1 type
'sbt_tape';restore database;recover database;
}
--back in svrmgrl
alter database open resetlogs
--
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).
ORA-23463
Hi gurus, i have this error in the replication process, when execute dbms_offline_og.end_load any idea for this error... i search in the metalink and no found solution .. help me please @L£K Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW > -Mensaje original- > De: Ron Yount [SMTP:[EMAIL PROTECTED]] > Enviado el: Viernes 8 de Marzo de 2002 09:23 PM > Para: Multiple recipients of list ORACLE-L > Asunto: RE: cursor not closing > > In the for what it is worth department, I had similiar issues. After the > developers knocked themselves out looking for code that was not closing > the > cursor, I opened a tar: > > In a nutshell: yet another "feature" that cursors "even though closed by > the > session that opened them" remain available this is touted as a feature > since there may be a session later that could benefit from a previous > cursor... this behavior will continue until max_cursors is reached and > then > you are done...:-( > > You can remedy this situation with the _CLOSE_CACHED_OPEN_CURSORS=true > init > parameter. > > If you are interested in more detail... my tar number is: 1921166.995 > > HTH, > > -Ron- > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ji, Richard > > Sent: Friday, March 08, 2002 6:23 PM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: cursor not closing > > > > > > Identify all the codes where ResultSet, Statement are used and make sure > > they are closed after it's done. > > > > -Original Message- > > Sent: Friday, March 08, 2002 4:48 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi, > > > > We have a Application that user JDBC thin client connecting to Oracle > > database. > > It seems like java code is opening the cursor , running some sql but not > > closing > > the cusrsor. > > Now the number of open_cursor reached 3568 . > > Is there any way we can close cursor from sqlplus. > > > > Thanks > > --Harvinder > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Harvinder 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 > > -- > > Author: Ji, Richard > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ron Yount > 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: Alexander Ordonez 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 o
RE: archivelog mode
You are looking for ...dest_ = "location=" I would recommend that you utilize the new enumerated archive destination locations, due to the inherent benefits... one example: If you use two locations, enabling the first(1) and defer the second(2), then if dest_1 fills up, you can enable dest_2 and allow your database to overflow archive to dest_2, thereby preventing the "lockup" caused if your one and only destination is full. HTH, -Ron- > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Khedr, > Waleed > Sent: Friday, March 08, 2002 6:03 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: archivelog mode > > > Try using: LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1 > If you would like using LOG_ARCHIVE_DEST_1 you have to read the in Oracle > Doc the syntax for it since more keywords are needed like 'location'. > > Waleed > > -Original Message- > Sent: Friday, March 08, 2002 6:09 PM > To: Multiple recipients of list ORACLE-L > > > Hi All > > While enabling automatic archiving on our 8.1.7 database I get the > following error :- > > ORA-00439: feature not enabled: Managed Standby > > ie the database is in archivelog mode and I edit the parameter file to > enable automatic archiving .. I get the error when trying to startup the > database .. these are the parameters that I change - > > # log_archive_start = true > # log_archive_dest_1 = "location=/u03/oradata/arch" > # log_archive_format = arch_%t_%s.arc > > > Due to this I have to start archiving whenever I restart, using 'alter > system archivelog start' > > Any ideas ? > > -- > Sajid Iqbal > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sajid Iqbal > 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: Khedr, Waleed > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Yount 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: cursor not closing
In the for what it is worth department, I had similiar issues. After the developers knocked themselves out looking for code that was not closing the cursor, I opened a tar: In a nutshell: yet another "feature" that cursors "even though closed by the session that opened them" remain available this is touted as a feature since there may be a session later that could benefit from a previous cursor... this behavior will continue until max_cursors is reached and then you are done...:-( You can remedy this situation with the _CLOSE_CACHED_OPEN_CURSORS=true init parameter. If you are interested in more detail... my tar number is: 1921166.995 HTH, -Ron- > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ji, Richard > Sent: Friday, March 08, 2002 6:23 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: cursor not closing > > > Identify all the codes where ResultSet, Statement are used and make sure > they are closed after it's done. > > -Original Message- > Sent: Friday, March 08, 2002 4:48 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > We have a Application that user JDBC thin client connecting to Oracle > database. > It seems like java code is opening the cursor , running some sql but not > closing > the cusrsor. > Now the number of open_cursor reached 3568 . > Is there any way we can close cursor from sqlplus. > > Thanks > --Harvinder > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Harvinder 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 > -- > Author: Ji, Richard > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Yount 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: cursor not closing
Identify all the codes where ResultSet, Statement are used and make sure they are closed after it's done. -Original Message- Sent: Friday, March 08, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Hi, We have a Application that user JDBC thin client connecting to Oracle database. It seems like java code is opening the cursor , running some sql but not closing the cusrsor. Now the number of open_cursor reached 3568 . Is there any way we can close cursor from sqlplus. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder 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 -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UPGRADE TO 8173
DEAR LIST, Simple question for upgrading, Is it ok if i install 817 separatley then patch 8173 and finally import the database from 816 to 8173, isn't it faster in this way the database is a small database and no need to link and all other things, any idea???in this case how can i use the same SID name The other question is , then is there any way to uninstall 816? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: Strangeness
Jared, yes it reminds me of burning peat. But since I am not the one drinking it, I don't care what it tastes like. Me, I'm debating the merits of a Brooklyn Pennant '55 Pale ale or a nice large glass of Glenmorangie.. hm maybe tonight is a Macallan's night? Been that sort of week. Rachel --- [EMAIL PROTECTED] wrote: > > Hmmph. Topics like this on a Friday make me want to dig deeper > into > > my toolbox (the malted compartment of course). > > I'm having similar feelings. Time to break out the Lagavulin > tonight. > > And Rachel, yes, I know it reminds you of burning peat. > > Maybe I *like* burning peat. :) > > Re the rewrite of the OOP App I mentioned:A developer that was > intimately > familiar with the inner workings of the app ( he inherited it ) and > myself > offered > to do the rewrite in 2 months. They spent $1M+ on the app, and > didn't > appreciate > a couple of Oracle hacks telling them it could be rewritten for $25k, > and > several > orders of magnitude faster. > > Jared > > > > > > > Robert Eskridge <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 03/08/02 02:43 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:Re[2]: Strangeness > > > Jared, > > Yeah, they don't want to hear about PL/SQL because then they can't > scale it up on the middle tier where they can have dozens of machines > with the same poorly written app simultaneously pounding the database > thousands of times more intensely than the task requires > > Hmmph. Topics like this on a Friday make me want to dig deeper into > my toolbox (the malted compartment of course). > > -rje > > J> Lee, > > J> I've had similar experiences. > > J> The problem is not PRO*C, but how the program is designed. > > J> Is it by any chance written in C++? I once had the 'privilege' of > J> administering an the databases for an application written in C++. > J> The software featured and award winning design, literaly. The OOP > J> design was honored in some OOP magazine. > > J> When you consider though that this wonderful OOP design treated > every > J> piece of data from the database as atomic, and retrieved them that > way, > J> you can begin to see the problem. > > J> The average SQL*Net packet size was 200 bytes, sub optimal to say > the > J> least. This is because the app preferred to retrieve it's own > information > > J> from the database and do the joins in the software. > > J> In a couple of hours this app could process all of 10k > transactions, > and > J> generate several million TCP/IP packets in the process. > > J> I suggested they move the app to the database server: this > resulted in > J> a 40% decrease in runtime. > > J> We offered to rewrite the whole thing in PL/SQL, but that was a > J> politically incorrect suggestion. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robert Eskridge > 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).
FW: VMS, large database
Sorry - premature send of the last one! This note finished properly. Mike, > I have a large 7.4 db running under VMS... Database has I am assuming 7.3.4 here (unless VMS has 7.4 :) > It runs around 256 I/O per sec and 7,011 logical I/O per sec. > Fetch vs. Scan is 5%.. > They run about 87 db waits per minute and get 176 I/O per wait... I assume you calculated these figures off V$SYSSTAT and V$SESSION_EVENT? What does the OS say? (Long time since I used VMS, but I think you do have a SHOW DEVICE or some other SHOW command that can show iostat-like OS stats? 7011 LIOs for 256 PIO computes to almost 27:1. Do you have optimized SQL? I would look at large amounts of Nested Loops in case you are at Rule... > What might I look at to see if they would benefit to go from > an 8K blocksize to 16K as they migrate from 7.4 to 8.1.7 I think it > is... He's going to build a new database and import. If this > would help, it would be the time to do it. I would seriously question the need for moving from 8k to 16k until you have exhausted all other possibilities. If the problem is large amounts of LIO, it's not going to solve anything. For a database that complex, figuring out the right order of Import and the elapsed time it would take in itself would be a good weapon that could be used against the blocksize change argument. I would look at the 'quick and dirty' set of views, i.e. V$SYSTEM_EVENT, rollup V$SESSION_WAIT, etc. and look at the Top wait events. Keep the blocksize increase for the last. Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: VMS, large database
Mike, > I have a large 7.4 db running under VMS... Database has I am assuming 7.3.4 here (unless VMS has 7.4 :) > It runs around 256 I/O per sec and 7,011 logical I/O per sec. > Fetch vs. Scan is 5%.. > They run about 87 db waits per minute and get 176 I/O per wait... I assume you calculated these figures off V$SYSSTAT and V$SESSION_EVENT? What does the OS say? (Long time since I used VMS, but I think you do have a SHOW DEVICE or some other SHOW command that can show iostat-like OS stats? 7011 LIOs for 256 PIO computes to almost 27:1. Do you have optimized SQL? I would look at large amounts of Nested Loops in case you are at Rule... > What might I look at to see if they would benefit to go from > an 8K blocksize to 16K as they migrate from 7.4 to 8.1.7 I think it > is... He's going to build a new database and import. If this > would help, it would be the time to do it. I would seriously question the need for moving from 8k to 16k until you have exhausted all other possibilities. If the problem is large amounts of LIO, it's not going to solve anything. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: archivelog mode
Try using: LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1 If you would like using LOG_ARCHIVE_DEST_1 you have to read the in Oracle Doc the syntax for it since more keywords are needed like 'location'. Waleed -Original Message- Sent: Friday, March 08, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Hi All While enabling automatic archiving on our 8.1.7 database I get the following error :- ORA-00439: feature not enabled: Managed Standby ie the database is in archivelog mode and I edit the parameter file to enable automatic archiving .. I get the error when trying to startup the database .. these are the parameters that I change - # log_archive_start = true # log_archive_dest_1 = "location=/u03/oradata/arch" # log_archive_format = arch_%t_%s.arc Due to this I have to start archiving whenever I restart, using 'alter system archivelog start' Any ideas ? -- Sajid Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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: Khedr, Waleed 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: Cost vs Rule
Bill, In addition to the many excellent suggestions, may I also suggest generating adequate number of histograms and using them by using literals instead of bind variables (horrors!). You may also want to look at 9i - the CBO therein looks at the value of the bind variables prior to parsing and can thus use histograms. This was one of the drawbacks of Histograms that seems to have been addressed in 9i. (Would any of the Guru's please confirm this? I don't have access to a 9i instance to test out :( John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** > I work in a dev shop - most of the sql is canned and pretty > basic. We've > been running CBO in all of our dev environments, but we have > a few long > txns > that just take forever. At the request of some savvy > developers, I turned > on RBO, and it brought down execution times dramatically. > > I've been analyzing affected tables often (we do a lot of > bulk load/unload > for testing), and have played with partitioning and clustering, > particularly > on one table that's just a dog. CBO will always do a FTS > where RBO uses > the > PK to retrieve data. > > Where to go next? I've been unable to alter the costs > dramatically enough > to make any real difference in execution time. > > thx > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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[2]: Strangeness
> Hmmph. Topics like this on a Friday make me want to dig deeper into > my toolbox (the malted compartment of course). I'm having similar feelings. Time to break out the Lagavulin tonight. And Rachel, yes, I know it reminds you of burning peat. Maybe I *like* burning peat. :) Re the rewrite of the OOP App I mentioned:A developer that was intimately familiar with the inner workings of the app ( he inherited it ) and myself offered to do the rewrite in 2 months. They spent $1M+ on the app, and didn't appreciate a couple of Oracle hacks telling them it could be rewritten for $25k, and several orders of magnitude faster. Jared Robert Eskridge <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 03/08/02 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re[2]: Strangeness Jared, Yeah, they don't want to hear about PL/SQL because then they can't scale it up on the middle tier where they can have dozens of machines with the same poorly written app simultaneously pounding the database thousands of times more intensely than the task requires Hmmph. Topics like this on a Friday make me want to dig deeper into my toolbox (the malted compartment of course). -rje J> Lee, J> I've had similar experiences. J> The problem is not PRO*C, but how the program is designed. J> Is it by any chance written in C++? I once had the 'privilege' of J> administering an the databases for an application written in C++. J> The software featured and award winning design, literaly. The OOP J> design was honored in some OOP magazine. J> When you consider though that this wonderful OOP design treated every J> piece of data from the database as atomic, and retrieved them that way, J> you can begin to see the problem. J> The average SQL*Net packet size was 200 bytes, sub optimal to say the J> least. This is because the app preferred to retrieve it's own information J> from the database and do the joins in the software. J> In a couple of hours this app could process all of 10k transactions, and J> generate several million TCP/IP packets in the process. J> I suggested they move the app to the database server: this resulted in J> a 40% decrease in runtime. J> We offered to rewrite the whole thing in PL/SQL, but that was a J> politically incorrect suggestion. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge 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).
archivelog mode
Hi All While enabling automatic archiving on our 8.1.7 database I get the following error :- ORA-00439: feature not enabled: Managed Standby ie the database is in archivelog mode and I edit the parameter file to enable automatic archiving .. I get the error when trying to startup the database .. these are the parameters that I change - # log_archive_start = true # log_archive_dest_1 = "location=/u03/oradata/arch" # log_archive_format = arch_%t_%s.arc Due to this I have to start archiving whenever I restart, using 'alter system archivelog start' Any ideas ? -- Sajid Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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[2]: Strangeness
Jared, Yeah, they don't want to hear about PL/SQL because then they can't scale it up on the middle tier where they can have dozens of machines with the same poorly written app simultaneously pounding the database thousands of times more intensely than the task requires Hmmph. Topics like this on a Friday make me want to dig deeper into my toolbox (the malted compartment of course). -rje J> Lee, J> I've had similar experiences. J> The problem is not PRO*C, but how the program is designed. J> Is it by any chance written in C++? I once had the 'privilege' of J> administering an the databases for an application written in C++. J> The software featured and award winning design, literaly. The OOP J> design was honored in some OOP magazine. J> When you consider though that this wonderful OOP design treated every J> piece of data from the database as atomic, and retrieved them that way, J> you can begin to see the problem. J> The average SQL*Net packet size was 200 bytes, sub optimal to say the J> least. This is because the app preferred to retrieve it's own information J> from the database and do the joins in the software. J> In a couple of hours this app could process all of 10k transactions, and J> generate several million TCP/IP packets in the process. J> I suggested they move the app to the database server: this resulted in J> a 40% decrease in runtime. J> We offered to rewrite the whole thing in PL/SQL, but that was a J> politically incorrect suggestion. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge 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).
Cannot run job in OEM ???
Hi all, I'm using 9i on Win2000. When I submit job in OEM (9.0.1), I always got "VNI-2015 : Authentication error". I created a user and put that user in administrator group. That user has Log on as a batch job and Log on locally rights. (No deny ... rights were set) In OEM's preferred credentials tab, I assigned this user as the node's credential. And 9i's Agent, SNMPPeerEncapsulator and SNMPPeerMasterAgent sevices are all running. With all these efforts, the job still fails! Any clue? Thank you! Leslie __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu 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: cursor not closing
common developers problem. they forget to close it in powerbuilder, too. -Original Message- Sent: Friday, March 08, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Java surely allows you to close the statement, ask your developers to do that. I told mine, and they are happy with it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, March 08, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Hi, We have a Application that user JDBC thin client connecting to Oracle database. It seems like java code is opening the cursor , running some sql but not closing the cusrsor. Now the number of open_cursor reached 3568 . Is there any way we can close cursor from sqlplus. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder 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 -- Author: Lyuda Hoska 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: cursor not closing
Java surely allows you to close the statement, ask your developers to do that. I told mine, and they are happy with it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, March 08, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Hi, We have a Application that user JDBC thin client connecting to Oracle database. It seems like java code is opening the cursor , running some sql but not closing the cusrsor. Now the number of open_cursor reached 3568 . Is there any way we can close cursor from sqlplus. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder 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). *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
cursor not closing
Hi, We have a Application that user JDBC thin client connecting to Oracle database. It seems like java code is opening the cursor , running some sql but not closing the cusrsor. Now the number of open_cursor reached 3568 . Is there any way we can close cursor from sqlplus. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder 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).
Re: free buffer waits
The puzzle has been solved, a developer was deleted 4 million rows from a 8 million rows table one row at a time! Joel. - Original Message - From: Manytrees To: Multiple recipients of list ORACLE-L Sent: Thursday, March 07, 2002 6:39 PM Subject: free buffer waits Hello all, Does anybody know what parameters I should be tuning/change to try & reduce the number of busy buffer waits. I have a system which at times has over 15+session waiting on "free buffer waits". The explanation that I have been able to find so far is that session are waiting on buffer to free up :) Have a good night all, Joel.
Re: Do you use RMAN? DB clone problem
Does anyone have any suggestions on DB cloning with RMAN.Netbackup, I
keep getting a file not found error:
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle="EML_L0-EML- 455497207-2852-1", parms=""
I'm missing something somewhere???
Origin DB "EML" on dolphin...
Duplicate DB "EMLC" on cobra...
rman
RMAN> connect target sys/@eml
RMAN> connect rcvcat rman/@ds8i
RMAN> connect auxiliary /
RMAN> "the usual file re-definition & redo log file descriptions"
RMAN> run {
RMAN> allocate auxiliary channel t type 'sbt_tape' parms
'ENV=(NB_ORA_CLIENT=dolphin.xx.xxx.xxx)' trace=1;
5> duplicate target database to EMLC
RMAN> }
RTFM no help
Oracle TAR no help
Sun ticket - completely useless (we get our Veritas support from Sun
[ big mistake])
TIA
...JIM...
>>> [EMAIL PROTECTED] 3/6/02 11:53:34 AM >>>
I use RMAN with veritas Netbackup and it is sweet. Recovery is
ridiculously
easy: Even recovering to an alternative host (not as fast as cloning)
is a
doddle and u can apply redo logs to the recovery to bring it up to
current
time: All in all well worth the learning curve to set up
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 06, 2002 9:03 PM
hmmm, the question of the day, a good one! I don't use it now but plan
on
using it. The question is when :)
>>> [EMAIL PROTECTED] 03/06/02 10:48AM >>>
Hi,
I'm in the process of upgrading my database to 9i and I was trying
to
decide whether I wanted to change my backup strategy to use RMAN. Do
most of
you use it? If you use it, what is your opinion of it? If you don't use
it,
why did you decide not to?
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALINF
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: Gene Sais
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: Sam Roberts
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: James Howerton
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: free buffer waits
Sounds like the write latency on your storage is high or you have an abusive. 'free buffer waits' is the db writer failing to flush it's cache fast enough to disk. George On Thursday, March 7, 2002, at 06:39 PM, Manytrees wrote: Hello all, Does anybody know what parameters I should be tuning/change to try & reduce the number of busy buffer waits. I have a system which at times has over 15+session waiting on "free buffer waits". The explanation that I have been able to find so far is that session are waiting on buffer to free up :) Have a good night all, Joel. // George Schlossnagle // Principal Consultant // OmniTI, Inc http://www.omniti.com // (c) 301.343.6422 (e) [EMAIL PROTECTED] // 1024D/1100A5A0 1370 F70A 9365 96C9 2F5E 56C2 B2B9 262F 1100 A5A0
Re: Networker and Legato Question
Your root user should su to oracle and then connect / as sysdba. >>> [EMAIL PROTECTED] 03/08/02 12:48PM >>> We are setting up the Legato Networker Module for Oracle but we are encountering problems. When we schedule a backup script to run through the utility, it fails. I don't know if the problem is because we run it as root or what. Even though, we can submit the same backups as our oracle user, using SBT_TAPE, root fails. At first the following errors occurred: ar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] * maestro1:/home/oracle/backup_db_level_0_tape.pat.run RMAN-04005: error from target database: ORA-01031: insufficient privileges Mar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] * maestro1:/home/oracle/backup_db_level_0_tape.pat.run Mar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] * maestro1:/home/oracle/backup_db_level_0_tape.pat.run Recovery Manager complete. So, we added root to group dba, but now we get read/write errors when RMAN tried to backup the first file. Our backup server is a remote Sun server. Are there special considerations for this that you know of? I'm going through the documentation but I'm hoping, maybe you've seen this before. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais 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: Strangeness
Lee, I've had similar experiences. The problem is not PRO*C, but how the program is designed. Is it by any chance written in C++? I once had the 'privilege' of administering an the databases for an application written in C++. The software featured and award winning design, literaly. The OOP design was honored in some OOP magazine. When you consider though that this wonderful OOP design treated every piece of data from the database as atomic, and retrieved them that way, you can begin to see the problem. The average SQL*Net packet size was 200 bytes, sub optimal to say the least. This is because the app preferred to retrieve it's own information from the database and do the joins in the software. In a couple of hours this app could process all of 10k transactions, and generate several million TCP/IP packets in the process. I suggested they move the app to the database server: this resulted in a 40% decrease in runtime. We offered to rewrite the whole thing in PL/SQL, but that was a politically incorrect suggestion. Jared Robertson Lee - lerobe <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 03/08/02 01:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Strangeness Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To:Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc:Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From:Robertson Lee - lerobe > > Sent:Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject: RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From:Lawlor Michael - mlawlo > > Sent:05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject: RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To:Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc:Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > >> > -Original Message- >> > From: Fremaux Ian - ifrema >> > Sent: Friday, March 01, 2002 12:04 PM >> > To: Robertson Lee - lerobe; Khiroya Asit - akhiro >> > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; >> > Peters Roy - ropete >> > Subject:RE: Roy's extract job >> > >> > Yesterday it was executing the same code but without the >> > UPDATE statement. I'd agree that the p
RE: USER DEFINED FUNCTIONS
select to_char(1) from dual; to_char is a user defined function (already built for you) by oracle. I am yet to find someone who says UDF is a bad thing ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
VMS, large database
I have a large 7.4 db running under VMS... Database has limited objects, 125,148 meg, with 1,131 tables and 1,022 indexes. (Total objects = 3,445) It runs around 256 I/O per sec and 7,011 logical I/O per sec. Fetch vs. Scan is 5%.. They run about 87 db waits per minute and get 176 I/O per wait... What might I look at to see if they would benefit to go from an 8K blocksize to 16K as they migrate from 7.4 to 8.1.7 I think it is... He's going to build a new database and import. If this would help, it would be the time to do it. ThinkSpark - Michael Alan Kline, Sr. Technical Consultant - Richmond, Virginia Office 13308 Thornridge Court; Midlothian, VA 23112, USA. W:804-744-1545 Cell: 804-314-6262 [EMAIL PROTECTED] Pager: [EMAIL PROTECTED] Alpha pager: www.metrocall.com/Page.html ICQ: 1009605, 975313PhoneFree: 1057439 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Kline 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).
USER DEFINED FUNCTIONS
Hi, We are evaluating the usefullness/drawbacks of using UDF's. Is there any case study on any site which shows some scenarios of using UDF's in Queries. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder 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).
Re: Number of Active Users inside the Database
Use this for active users. Remove status clause and see all logged on users... set linesize 132 set pagesize 24 set feedback on select SADDR, SID, SERIAL#, PADDR, substr(USERNAME,1,8) "USER", STATUS , SCHEMA#, OSUSER, PROCESS, LOGON_TIME, last_call_et from v$session where status = 'ACTIVE' and username not like 'SYS' / Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 07 Mar 2002 17:43:19 -0800 Hello List, Just a stupid Question. I would like to know the Number of Active Users from a Query inside the Database. Plus is it possible to know the number of allowed licence for the Oracle Thanks for U'r Time & Interest. Fazal = Abul Fazal Production Support Services - Quantum Leap Standard Charted Bank Singapore HP : 65-94887900 __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abul Fazal 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). MOHAMMAD RAFIQ _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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: Strangeness with PL/SQL and ProC
Lee, Over the years I've developed a very strong appreciation for TCP/IP as a fast, error free communication protocol. At the same time I've also developer a VERY strong appreciation for how slow SQL*Net is. Anything you can do to minimize that part of an application helps 10 fold. BTW, getting onto the object bandwagon a bit. I've developed, over the last three years, a strong appreciation for doing a lot of database access stuff in PL/SQL and hopefully JAVA in the future. Having a package with a defined interface to the external programs modularizes things so nicely it's breathtaking. I've now a wonderful relation ship with duhvelopers as we do that interface definition. They go off and develop their front ends as they want knowing what the interface at the database looks like. I can then go off and create a very nice, efficient, and normalized (with referential integrity) database and code the package body as needed, including modifications and bug fixes without causing them a pile of grief. In the end we get the job done faster, neater, and with less hassle. Guess I'm going to have to start calling them developers pretty soon. :-) Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 3:23 PM Thanks for that Dick, the communication issue was one we had considered, you are confirming this from what you say below. Thanks again for the response. Lee -Original Message- Sent: 08 March 2002 13:53 To: Robertson Lee - lerobe; Multiple recipients of list ORACLE-L Lee, Stop a minute and take a look at what your doing. I assume that when the process was pure PRO*C there must have been a pile of communication between the database and the program. This communication, even if done by IPC takes time. Now when you re-code it in PL/SQL there is no reason for process to database communication, hence it takes less time. Allow me to provide an illustration: We had a OCI program that would load tester data from NT shares every morning into our Unix based database. Normally this process took around 8 to 10 hours to run. When MicroSoft OS/2 died several years ago yours truly ported the program from OS/2 to NT in the process re-coding it in PRO*C. Now since paramaterization of SQL was not the original authors forte, the program started running a little faster, but still 6 to 8 hours. Now comes Y2K, a new server & database evrsion & some normalization takes place resulting in a new database design. When I reviewed the program I note that there were a number of back and forth communication requirements that were in the original. It took something like 5 round trips to the database for queries to decide if we were going to insert a new record or update an existing one. Well, I took all of that code out of PRO*C, re-coded it as a PL/SQL package and today that same program runs in a little over 1 hour with one round trip from the client to the database. Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 2:03 AM > Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public In
Networker and Legato Question
We are setting up the Legato Networker Module for Oracle but we are
encountering problems. When we schedule a backup script to run through
the utility, it fails. I don't know if the problem is because we run it
as root or what. Even
though, we can submit the same backups as our oracle user, using
SBT_TAPE, root fails.
At first the following errors occurred:
ar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] *
maestro1:/home/oracle/backup_db_level_0_tape.pat.run RMAN-04005: error
from target database: ORA-01031: insufficient privileges
Mar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] *
maestro1:/home/oracle/backup_db_level_0_tape.pat.run
Mar 8 10:59:22 gmuu root: [ID 702911 daemon.notice] *
maestro1:/home/oracle/backup_db_level_0_tape.pat.run Recovery Manager
complete.
So, we added root to group dba, but now we get read/write errors when
RMAN tried to backup the first file.
Our backup server is a remote Sun server. Are there special
considerations for this that you know of? I'm going through the
documentation but I'm hoping, maybe you've seen this before. Thanks in
advance.
begin:vcard
n:Taylor;Belinda
tel;fax:(703) 993-3403
tel;work:(703) 993-3346
x-mozilla-html:FALSE
url:http://itu.gmu.edu
org:George Mason University( 'o_ o ) '_. ( ) .;Information Technology Unit
version:2.1
email;internet:[EMAIL PROTECTED]
title:Database Analyst, Database Support ("'-''-/").__..--''"'-.__
adr;quoted-printable:;;Technology Systems Division=0D=0A4400 University Drive=0D=0AMSN 1B5;Fairfax;VA;22030;USA
fn:Belinda Taylor
end:vcard
Partitioning
Hello All, We have an 8.1.7. database and have a partitioned table in it. Table has about 80 million rows and growing fast. Recent changes have forced us to think about sub-partitioning it further. Also we have to upgrade the database to 9i shortly. My question is is there any advantage of first upgrading to 9i and then doing a sub-partitioning. I mean are there any new features etc in 9i which would make the process easier or get us some additional advantage. Satish>>> [EMAIL PROTECTED] 03/08/02 07:03AM >>>Yes, the man is a X$ marvelWhat that I could remember all of the things thathe seems to have at the tip of his emails.RFRobert G. Freeman - Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database AdministrationThe Cigarette Smoking Man: Anyone who can appease a man's conscience cantake his freedom away from him.-Original Message-Sent: Thursday, March 07, 2002 7:18 PMTo: Multiple recipients of list ORACLE-L> To give credit where credit is due, this came from my friend > K Gopalakrishnan...You mean K 'X$' Gopalakrishnan, don't you ;-)John Kanagaraj-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso 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 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: tkprof plan missing rows
It usually means the cursor for that query was not closed before the end of file (e.g. SQL in pl/sql and you didn't do an exit to get out of sql*plus) so Oracle never got around to dumping the STAT lines. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 18:15 | |Oracle 7.3.4, OpenVMS 7.1 | |My tkprof report is missing the row count in the execution plan. Does |anyone know why what I might be missing? |If I autotrace the same query in the same database, I do get cardinality. |The tables have been analyzed. | |If I tkprof another database using the same version and OS, that tkprof DOES |have row counts. |I believe I'm just missing a parameter somewhere, but I don't know where. | |Thanks for any ideas. |Barb | |$ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd |sys=no | |* |*** | | |Rows Execution Plan |--- --- | 0 SELECT STATEMENT GOAL: CHOOSE | 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' | 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) | |* *** | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: Strangeness
sql_trace and tkprof should tell you where the time was spent and what the code is doing! Waleed -Original Message- Sent: Friday, March 08, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From: Robertson Lee - lerobe > > Sent: Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From: Lawlor Michael - mlawlo > > Sent: 05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Friday, March 01, 2002 12:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Yesterday it was executing the same code but without the > > > UPDATE statement. I'd agree that the performance would be > > > affected by having to do the UPDATEs but from the figures you > > > observed it was executing the UPDATE statement over 1000 > > > times per second. It is performing a commit every 10K records > > > although I don't know how long this is taking, but from what > > > I can see in Toad the rate of increase in the number of times > > > the UPDATE is executing indicates that the COMMIT is probably > > > not the problem. > > > > > > -Original Message- > > > From: Robertson Lee - lerobe > > > Sent: Friday, March 01, 2002 11:57 AM > > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Erm...couldn't see the wood for the trees time. Why are > you > > > doing single updates per record. Were you doing this in > > > batches yesterday ? > > > > > > If this is the case and unless I am mistaken, then a > severe > > > degradation in performance is the sort of thing I would > expect > > > > > > > > > > > > -Orig
Almost OT...
It seems this t-shirt would fit most DBAs here. :) http://www.thinkgeek.com/images/products/zoom/no-clue.jpg Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Number of Active Users inside the Database
The problem is what is meant by "active." If you query for v$session(status) = 'ACTIVE' you will only get the sessions that are currently in the middle of having a statement processed. I doubt that is what he is looking for. If Abul wants everyone who is connected but hasn't been idle for an hour or more, I would use the last_call_et column as a predicate instead. select count (*) from v$session where type != 'BACKGROUND' and last_call_et <= 60; You might consider leaving out the SNP sessions too, since they don't get marked as type = 'BACKGROUND'; Anyway, if you are using any kind of web server or middle tier, these aren't real users anyway, but just sessions acting as a shared resource by many users. As for the number of allowed users per your license, I would read the license. It isn't in the database. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 8 Mar 2002, Joan Hsieh wrote: > SELECT s.client_info client,s.username,s.osuser,s.PROGRAM,p.pid,p.spid, > s.sid,s.serial#, to_char(S.LOGON_TIME,'MONDD HH24:MI') "LOGON TIME" > from v$session s, v$process p > where s.status='ACTIVE' and s.type != 'BACKGROUND' > and p.addr=s.paddr > > Abul Fazal wrote: > > > > I would like to know the Number of Active Users from a > > Query inside the Database. Plus is it possible to know > > the number of allowed licence for the Oracle -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Sun Cluster and VCS failover
We have several 2-way VCS clusters with 16 and 10 CPU's per server. We found that the failover time was proportionate with the number of file systems that need to be mounted by the take-over node. We consolidated the file systems containing the datafiles down to 1 and each database takes 2-3 minutes 'till service is fully restored. HTH Tony Aponte -Original Message-From: Nick Wagner [mailto:[EMAIL PROTECTED]]Sent: Monday, March 04, 2002 4:24 PMTo: Multiple recipients of list ORACLE-LSubject: Sun Cluster and VCS failover Situation: Sun Cluster, or VERITAS Cluster. Oracle 8, 8i, or 9i 2 node Sun E6500s w/ 8 CPUs If the primary node fails, how long does it take before a user is able to connect to the secondary node, and continue their activity? I'm sure reality, and marketing times are different... I'm really interested in reality times, but at this point either would be nice. Thanks!! Nick
tkprof plan missing rows
Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same version and OS, that tkprof DOES have row counts. I believe I'm just missing a parameter somewhere, but I don't know where. Thanks for any ideas. Barb $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd sys=no * *** Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) here's a tkprof from a different database: Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 1440212 SORT (GROUP BY) 2785044HASH JOIN 3109095 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WORK_ORDER_DETAILS' 3762491 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE) 5727880 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SLS_OF_REC' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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-Genesis of a DBA Universe
Hilarious Jim. A bit irreverent, but I'm laughing my head off. Thanks for sharing that. Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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: PL/SQL
Possibly :-) but it did reinforce the point to developers out there who insist that using %TYPE for parameters ensures that they cannot pass strings that exceed the length of the corresponding column. Cheers Connor --- "Freeman, Robert " <[EMAIL PROTECTED]> wrote: > So, do you think I'm making a mountain out of a > molehill over the > PL/SQL %type stuff...? > > RF > > Robert G. Freeman - Oracle8i OCP > Oracle DBA Technical Lead > CSX Midtier Database Administration > > The Cigarette Smoking Man: Anyone who can appease a > man's conscience can > take his freedom away from him. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Freeman, Robert > 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Number of Transaction Slots
Yes, the man is a X$ marvel What that I could remember all of the things that he seems to have at the tip of his emails. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Thursday, March 07, 2002 7:18 PM To: Multiple recipients of list ORACLE-L > To give credit where credit is due, this came from my friend > K Gopalakrishnan... You mean K 'X$' Gopalakrishnan, don't you ;-) John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle client 8.0.5 to 8.1.7
Hi everybody, we've recently upgraded our Oracle client from 8.0.5 to 8.1.7 SQL Worksheet does not seem to have any line numbers - So, if you run any code, it'll give you the line number of the error line, but the code above does not have any line numbers displayed, making it very difficult to find the error line within the code. Has anybody come accross this before and found any workarounds, or is it something you just have to get on with?? Thanks for any replies, they're much appreciated. Best Regards, Barry Deevey Applications Developer Tel: 0117 9154253 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Barry Deevey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [oracle-l-OT] Re: Now: IOUG : Was: PocketDBA
Careful! Kirti has the power of Oradebug in his pocket. erso to speak. -Original Message- To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 3/8/2002 6:55 AM the conspiracy continues to unfold. joe PS: moved to the OT list, since now its a conspiracy Rachel Carmichael wrote: > not once we get through with them (Kirti, you distract them, I'll take > the batteries) > > > --- Joseph S Testa <[EMAIL PROTECTED]> wrote: > >>But they're laptops with working batteries :) >> >>joe >> >> >>Deshpande, Kirti wrote: >> >> >>>How about a seat near the power outlets ?? Then, it will be a fun >>> >>experiment >> >>>for Joe & Susan ;) >>> >>>I will be doing my first ever presentation at IOUG-A.. Just a Quick >>> >>Tips >> >>>(Q31) - Wait Events in a Nutshell.. (bring your lunch with you, if >>> >>they >> >>>allow it :) All Quick Tips Sessions are during lunch period... >>> >>>- Kirti >>> >>>-Original Message- >>>Sent: Thursday, March 07, 2002 7:13 AM >>>To: Multiple recipients of list ORACLE-L >>> >>> >>>and this one doesn't really conflict with anything I want to see. >>> >>>gotta get there early to get a seat in the front row so I can heckle >>>better :) >>> >>> >>>--- Joe Testa <[EMAIL PROTECTED]> wrote: >>> >>> yeppers i'll be there and (he crosses his fingers) if all goes well in our testing(Susan and I) for the data guard presentation, we hope >>to >> do a switchover and possibly a switch back in the demo part. We'll have 2 laptops each running linux and 9i connected by a hub. This should be a fun experiment if nothing else. :) joe >> >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>-- >>Author: Joseph S 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). >> > > > __ > Do You Yahoo!? > Try FREE Yahoo! Mail - the world's greatest free email! > http://mail.yahoo.com/ > -- Joe Testa, Oracle DBA Nothing new to put here, hmm Yahoo! Groups Sponsor -~--> Tiny Wireless Camera under $80! Order Now! FREE VCR Commander! Click Here - Only 1 Day Left! http://us.click.yahoo.com/nuyOHD/7.PDAA/yigFAA/o7folB/TM -~-> To talk about oracle database issues: subscribe to [EMAIL PROTECTED] To do the offtopic(OT) thing. post here :) To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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: Number of Active Users inside the Database
SELECT s.client_info client,s.username,s.osuser,s.PROGRAM,p.pid,p.spid, s.sid,s.serial#, to_char(S.LOGON_TIME,'MONDD HH24:MI') "LOGON TIME" from v$session s, v$process p where s.status='ACTIVE' and s.type != 'BACKGROUND' and p.addr=s.paddr / Joan Abul Fazal wrote: > > Hello List, > Just a stupid Question. > I would like to know the Number of Active Users from a > Query inside the Database. Plus is it possible to know > the number of allowed licence for the Oracle > > Thanks for U'r Time & Interest. > > Fazal > > = > Abul Fazal > Production Support Services - Quantum Leap > Standard Charted Bank > Singapore > HP : 65-94887900 > > __ > Do You Yahoo!? > Try FREE Yahoo! Mail - the world's greatest free email! > http://mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Abul Fazal > 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: Joan Hsieh 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: Strangeness
Thanks for the input Jonathan, I will pass the relevant parts on to the development team responsible. Regards Lee -Original Message- Sent: 08 March 2002 11:33 To: Multiple recipients of list ORACLE-L If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible that a minor bug in the Pro*C could mean that each update was updating every single row in the table on every update (don't laugh, I've seen it before), whereas the PL/SQL, being easier to read and write, is coded correctly. Is it possible that the Pro*C uses an 'in-house library' for its updates that generates code to update every column in the table ? Whereas the PL/SQL is hand-coded to update only the changed columns Is the code doing single row commits inside a loop ? I wouldn't expect this to make a factor of 100 difference (correct my arithmetic if it's wrong), but PL/SQL cheats on commits in loops, and the saving can be significant. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 11:21 |Oracle 8.0.5.0.0 |Tru64 4.0f | |We have a process running here and without going into the detail of it we |have a Pro C program that is taking ages to run updates and selects (2 |hours to do 1 records). The program was changed to PL/SQL and we |suddenly were seeing 5 million records processed in 1 hour. | |Is PL/SQL that much faster than Pro C. Can somone more in the know give me |some hints ?? | |TIA | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Strangeness with PL/SQL and ProC
Lee, Stop a minute and take a look at what your doing. I assume that when the process was pure PRO*C there must have been a pile of communication between the database and the program. This communication, even if done by IPC takes time. Now when you re-code it in PL/SQL there is no reason for process to database communication, hence it takes less time. Allow me to provide an illustration: We had a OCI program that would load tester data from NT shares every morning into our Unix based database. Normally this process took around 8 to 10 hours to run. When MicroSoft OS/2 died several years ago yours truly ported the program from OS/2 to NT in the process re-coding it in PRO*C. Now since paramaterization of SQL was not the original authors forte, the program started running a little faster, but still 6 to 8 hours. Now comes Y2K, a new server & database evrsion & some normalization takes place resulting in a new database design. When I reviewed the program I note that there were a number of back and forth communication requirements that were in the original. It took something like 5 round trips to the database for queries to decide if we were going to insert a new record or update an existing one. Well, I took all of that code out of PRO*C, re-coded it as a PL/SQL package and today that same program runs in a little over 1 hour with one round trip from the client to the database. Dick Goulet Reply Separator Author: Robertson Lee - lerobe <[EMAIL PROTECTED]> Date: 3/8/02 2:03 AM > Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Cost vs Rule
This shouldn't be a problem, hints just look like comments to other db's. John [EMAIL PROTECTED] wrote: not much - desire is to keep sql ANSI compliant due to cross-platform issues(want to be able to run the app on multiple db's)-Original Message-Sent: Thu, March 07, 2002 2:44 PMTo: Multiple recipients of list ORACLE-LHow much have you played with Oracle Hints???-Joe--- "Magaliff, Bill" <[EMAIL PROTECTED]> wrote: I work in a dev shop - most of the sql is canned and pretty basic. We'vebeen running CBO in all of our dev environments, but we have a fewlong txnsthat just take forever. At the request of some savvy developers, Iturnedon RBO, and it brought down execution times dramatically.I've been analyzing affected tables often (we do a lot of bulkload/unloadfor testing), and have played with partitioning and clustering,particularlyon one table that's just a dog. CBO will always do a FTS where RBOuses thePK to retrieve data.Where to go next? I've been unable to alter the costs dramaticallyenoughto make any real difference in execution time.thx-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Magaliff, Bill INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / MailingLists To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).__Do You Yahoo!?Try FREE Yahoo! Mail - the world's greatest free email!http://mail.yahoo.com/
RE: Oracle Indexing
Sinardy, 5 DB blocks is the default for INITIAL and NEXT extents, if you don't specify them, not necessarily the recommended size. The extent size of any segment depends more on the size of the segment, but should always be an integer multiple of db_file_multiblock_read_count. The best recommendation is to have one or a few standard extent sizes in locally-managed tablespaces. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Friday, March 08, 2002 1:48 AM To: Multiple recipients of list ORACLE-L Hi all, Oracle said: The best extent size of an index to minimize fragmetation is 5 times of db block size. My question is why 5 times is the recommended size, why not 4 times or 6 times or perhaps 0.5 of your db block size. Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Cost vs Rule
Bill, There are some good notes on Metalink about why CBO avoids using an index when one is available. I'll see if I can find a note number but you might try searching on index and optimizer. Sometimes you need to modify the code in order to get better performance under CBO. Can you test modified code. Harrison has a good book Oracle SQL High-Performance Tuning which is very good. Burleson also has a book Oracle High-Performance SQL Tuning which is very good. If you're not under tremendous schedule pressure, I'd recommend that you tune the poorly performing SQL to run better under CBO. RBO is not getting any new features and will eventually go away. CBO continues to evolve and get better so if this is a new project, it's best to start out with CBO, in my opinion. If you have to, you can set the entire database to CBO and then add rule-based hints to the poorly-performing statements, if there aren't too many of them. That is, if you have the capability to add hints. Cherie Machler Oracle DBA Gelco Information Network "Magaliff, Bill" dware.com> cc: Sent by: Subject: Cost vs Rule [EMAIL PROTECTED] 03/07/02 01:23 PM Please respond to ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed ts
Ayyapps, Use the DBMS_SPACE_ADMIN supplied PL/SQL package. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Friday, March 08, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Hi all Can we able to change the dictionary managed tablespace to locally managed tablespace. if so how? Ayyapps -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Now: IOUG : Was: PocketDBA
the conspiracy continues to unfold. joe PS: moved to the OT list, since now its a conspiracy Rachel Carmichael wrote: > not once we get through with them (Kirti, you distract them, I'll take > the batteries) > > > --- Joseph S Testa <[EMAIL PROTECTED]> wrote: > >>But they're laptops with working batteries :) >> >>joe >> >> >>Deshpande, Kirti wrote: >> >> >>>How about a seat near the power outlets ?? Then, it will be a fun >>> >>experiment >> >>>for Joe & Susan ;) >>> >>>I will be doing my first ever presentation at IOUG-A.. Just a Quick >>> >>Tips >> >>>(Q31) - Wait Events in a Nutshell.. (bring your lunch with you, if >>> >>they >> >>>allow it :) All Quick Tips Sessions are during lunch period... >>> >>>- Kirti >>> >>>-Original Message- >>>Sent: Thursday, March 07, 2002 7:13 AM >>>To: Multiple recipients of list ORACLE-L >>> >>> >>>and this one doesn't really conflict with anything I want to see. >>> >>>gotta get there early to get a seat in the front row so I can heckle >>>better :) >>> >>> >>>--- Joe Testa <[EMAIL PROTECTED]> wrote: >>> >>> yeppers i'll be there and (he crosses his fingers) if all goes well in our testing(Susan and I) for the data guard presentation, we hope >>to >> do a switchover and possibly a switch back in the demo part. We'll have 2 laptops each running linux and 9i connected by a hub. This should be a fun experiment if nothing else. :) joe >> >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.com >>-- >>Author: Joseph S 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). >> > > > __ > Do You Yahoo!? > Try FREE Yahoo! Mail - the world's greatest free email! > http://mail.yahoo.com/ > -- Joe Testa, Oracle DBA Nothing new to put here, hmm -- 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).
Re: Now: IOUG : Was: PocketDBA
Rachel, I don't seem to have a note Marlene's email address, and would like to drop her a note. Could you forward this to her please and ask her to get in touch. Thanks. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 03:14 |Hi Robert | |she'll be at this one but it's her "swan song". She's earned the right |to retire. And besides, she REALLY wants a puppy and can't have one if |she keeps traveling. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: Strangeness
If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible that a minor bug in the Pro*C could mean that each update was updating every single row in the table on every update (don't laugh, I've seen it before), whereas the PL/SQL, being easier to read and write, is coded correctly. Is it possible that the Pro*C uses an 'in-house library' for its updates that generates code to update every column in the table ? Whereas the PL/SQL is hand-coded to update only the changed columns Is the code doing single row commits inside a loop ? I wouldn't expect this to make a factor of 100 difference (correct my arithmetic if it's wrong), but PL/SQL cheats on commits in loops, and the saving can be significant. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 March 2002 11:21 |Oracle 8.0.5.0.0 |Tru64 4.0f | |We have a process running here and without going into the detail of it we |have a Pro C program that is taking ages to run updates and selects (2 |hours to do 1 records). The program was changed to PL/SQL and we |suddenly were seeing 5 million records processed in 1 hour. | |Is PL/SQL that much faster than Pro C. Can somone more in the know give me |some hints ?? | |TIA | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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).
EMC file systems and backups
Where I can find more info concerning EMC disks, setting up the filesystem and Oracle tuning + backups with EMC. Thanks in advance, sepi _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seppo Kaasalainen 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).
Recall: Strangeness
Robertson Lee - lerobe would like to recall the message, "Strangeness". The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Strangeness with PL/SQL and ProC
> Oracle 8.0.5.0.0 > Tru64 4.0f > > We have a process running here and without going into the detail of it we > have a Pro C program that is taking ages to run updates and selects (2 > hours to do 1 records). The program was changed to PL/SQL and we > suddenly were seeing 5 million records processed in 1 hour. > > Is PL/SQL that much faster than Pro C. Can somone more in the know give > me some hints ?? > > TIA > > Lee > > The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Recall: Strangeness
Robertson Lee - lerobe would like to recall the message, "Strangeness". The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Strangeness
Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour. Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ?? TIA Lee > -Original Message- > From: Lawlor Michael - mlawlo > Sent: 05 March 2002 17:17 > To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy - > ropete > Subject: RE: Roy's extract job > > > Dunno, was hoping it might spark an idea. > > From the dark recesses of my mind, do I recall that if you have TWO_TASK, > you always connect through the listener, even from the same box. Is it > possible that it could have a bottleneck of some sort? Maybe that's > impossible - I don't know > > Mick > > > -Original Message- > > From: Robertson Lee - lerobe > > Sent: Tuesday, March 05, 2002 5:17 PM > > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > erm how ?? > > > > -Original Message- > > From: Lawlor Michael - mlawlo > > Sent: 05 March 2002 17:11 > > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya > > Asit - akhiro > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > Peters Roy - ropete > > Subject:RE: Roy's extract job > > > > > > Something to do with the Oracle listener? > > > > M > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Tuesday, March 05, 2002 5:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete; Lawlor Michael - mlawlo > > > Subject: RE: Roy's extract job > > > > > > Chaps, > > > > > > We have some information that may be of interest. We had this > > > problem (discussed below) last week where a Pro-C program was > > > running very slowly but when Lee monitored the database > > > response time (SELECTs & UPDATEs) it was performing very > > > quickly. We converted the code to PL-SQL and ran it yesterday > > > and it flew along and processed 5 million records in 1 hour. > > > This morning we tried the Pro-C version of the program again > > > and after two hours it had processed less than 10K records. > > > So we executed the PL-SQL again this afternoon and it has > > > almost completed the 5 million rows in about 2 hours again. > > > > > > Any thoughts on the implications of this? > > > > > > Regards, Ian. > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: Friday, March 01, 2002 12:04 PM > > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Yesterday it was executing the same code but without the > > > UPDATE statement. I'd agree that the performance would be > > > affected by having to do the UPDATEs but from the figures you > > > observed it was executing the UPDATE statement over 1000 > > > times per second. It is performing a commit every 10K records > > > although I don't know how long this is taking, but from what > > > I can see in Toad the rate of increase in the number of times > > > the UPDATE is executing indicates that the COMMIT is probably > > > not the problem. > > > > > > -Original Message- > > > From: Robertson Lee - lerobe > > > Sent: Friday, March 01, 2002 11:57 AM > > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro > > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; > > > Peters Roy - ropete > > > Subject:RE: Roy's extract job > > > > > > Erm...couldn't see the wood for the trees time. Why are > you > > > doing single updates per record. Were you doing this in > > > batches yesterday ? > > > > > > If this is the case and unless I am mistaken, then a > severe > > > degradation in performance is the sort of thing I would > expect > > > > > > > > > > > > -Original Message- > > > From: Fremaux Ian - ifrema > > > Sent: 01 March 2002 10:00 > > > To: Khiroya Asit - akhiro >
alter table enable table lock hangs..
list, i was playing around with the disable table lock command and disabled the locks on a temp table.. but i'm not able to ENABLE the table lock again !!! the command just hangs... queried from v$session_wait.. .the command is waiting for "library cache handle" eternally..!!! is there any way i can drop this table ??? (it would require enabling the table locks) seems like a bug to me... i'm on rs/6000 with 8.1.5.0.0 Regards Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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).
Locally managed ts
Hi all Can we able to change the dictionary managed tablespace to locally managed tablespace. if so how? Ayyapps This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. Visit us @ www.ssiworldwide.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DB2
Title: Message - Original Message - From: Cunningham, Gerald Does anybody know if there's a list such as this one for DB2? Or, a link to DB2 documentation (maybe something like the Oracle Concepts Guide)? try these: DB2 links from SearchDatabase.com - http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax282900,00.html "Learning the Lingo" - article from DB2 Magazine that maps some Oracle and DB2 concepts - http://www.db2mag.com/db_area/archives/2002/q1/pdfs/Kolluru.pdf DB2 Self-Study course (u can download it for free): http://www-3.ibm.com/software/data/db2/selfstudy/index.html DB2 Manuals - http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main hth, Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. "
Re: ORA-01406
> Denham Eva wrote: > > Hi List, > > Please can anyone give me some pointers on this issue. > We have third party app called Maximo with reports that run from > within it. > We are receiving an error on one of these reports. > The Oracle error that comes out of it is > ORA-01406 fetched column value was truncated. > I am not sure of what exactly is happening as I don't have access to > their source. > However I would like to know if there is a general resolution for this > that can be implemented on the server, > or is it perhaps a bug? > > OS = Win2K SP6 > ORACLE = 8.1.7.0.0 > > Rgds > Denham > It's a bug in their application. That's what you get when, for instance, you fetch a string of 12 characters into a variable which can accomodate only 10. Oracle knows the size of the recipient variable, so doesn't (normally) blows up your memory, but it issues a warning. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Indexing
Hi all, Oracle said: The best extent size of an index to minimize fragmetation is 5 times of db block size. My question is why 5 times is the recommended size, why not 4 times or 6 times or perhaps 0.5 of your db block size. Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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).
