Re: SQL*Plus with readline
On HPUX the ied utility enables command history. Everything you type will be logged to the HISTORY file unless you specify one. -- Denny Koovakattu Quoting Carel-Jan Engel [EMAIL PROTECTED]: At 14:09 16-12-03 -0800, you wrote: I know there's a lot of folks who grumble about the spartan-ness of SQL*Plus, but the only real feature I wish it had was GNU readline capability for command history and editing. I've looked at some wrappers (yasql, gasql), as well as replacements (henplus, which is quite nice, actually), but today I found something that seems to do exactly what I really want--SQL*Plus behavior, augmented with readline support (So I can still do stuff like output formatting, spooling, etc., etc.,). Yes, I actually dig SQL*Plus, just wish it had readline. So today I found a little utility called uniread: http://sourceforge.net/projects/uniread/ It's a Perl program that adds full readline support (command editing, history, etc.) to any existing interactive command-line program. Common examples are Oracle's sqlplus or jython. uniread will work on any POSIX platform with Perl. I tried it out with SQL*plus, and so far seems to work beautifully. Just thought I'd share. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division Don't know this particular one, but ran into a shell on HP-UX with similar capabilities. I was a developer those days, and the feature I liked most was its capability to unveil sys/system passwords. Just get this shell running and ask the DBA to do something from your terminal. After that, the non-echoed password will be perfectly visible in command-line history (after the DBA left the scene, of course). They never found out how we were able to discover their passwords. I think it's now safe to spread the knowledge around. Does this tool have the same 'functionality'? So, be carefull, or take advantage of it ;-). Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: rebuilding indexes - sure to cause a ruckus
If it's from Oracle, I would believe it, i.e., I would believe somebody did actually say that ;) But it does not make it right. Now only if management knew/believed that. Some more from Oracle, - Oracle writes to one log member and then the other. So you need both log members for recovery. Volunteered to help us use _allow_resetlogs_corruption when we had one intact log member. (Took a lot of effort not to tell him to read the concepts manual. Was from a Sev1 problem that happened a few years ago.) - Increasing hit ratio, OS swap size to 3 times the OS memory and improving data proximity in an index (never really understood this one) among other bizarre ones to improve performance. This from an Oracle consultant who was called onsite by Development Management because we claimed the real reason was because the application was committing after every record to avoid locking issues on a table generating sequences, not to mention running 48 batch jobs on a 8CPU box with all of them committing after every record and using the table to generate keys (Cary would love this one) ;) They wanted to find other reasons and he conveniently ignored the real problem. BTW, I personally don't like having a zillion extents for an object (more so when you have multiple DBA Replacement Tools querying dba_extents constantly and showing flashing red lights) and would expect the development team NOT to give me a deer in the headlights look when asked for table sizing info. Response most often heard is Why do you need that. Oracle will be able to take care of it or can't Oracle take care of it or some variation thereof What I really want to say is if you don't have any idea about your data, then please don't write any SQL. That should take care of most performance issues. Barbara Baker wrote: You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dc_used_extents ,dc_free_extents and dc_histogram_defs
They will think differently after Mogens comes out with the Do you really need 10g presentation ;) -- Denny Koovakattu Quoting Nuno Souto [EMAIL PROTECTED]: The interesting fact of course is that the beta program of 10g was announced in newsgroups AFTER it had closed for all intents and purposes to the general public... And quite frankly, Oracle could do a LOT WORSE than let customers like Mogens definitely join. Just a feeling, mind you. If Oracle thinks the good old days of in-house elites are back, they're dead wrong. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Generally, the announcements are made at events like OracleWorld, through OTN and so forth that the beta program is open. Depending on the release, the program may not even get announced unless it's big enough. IIRC, the beta program for 9.2 was open to only a small number of customers and wasn't announced to the world at large, whereas the 10g program was announced (again IIRC - it's 4 am for me and I haven't had my first coffee yet!) at OracleWorld in San Fran in September? Of course, there are some companies that are almost always invited to join the beta program for the database because of the type of customer they are and the type of work they do - customers like Amazon, for example, may fall into that category. Customers like Mogens definitely don't. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database management techniques and frameworks
Makes me remember the story I was told about damagement running around telling users to log off because the latches are red ;) -- Denny Koovakattu Quoting Thater, William [EMAIL PROTECTED]: Orr, Steve scribbled on the wall in glitter crayon: I'm not assuming such a tool exists... It indeed does exist because the salesman who happened to be selling it said so and it must be of course it does, and they'll install it for you because they can install and tune it EXACTLY THE SAME WAY for every installation. so see you don't need your DBA any more, just install the whiz-bang tool and follow the bouncing prompt and all you troubles will be far away. until it shows red for some reason and nobody can find out why. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] ..one of the strongest motives that lead men to art and science is escape from everyday life with its painful crudity and hopeless dreariness, from the fetters of one's own ever-shifting desires. A finely tempered nature longs to escape from the personal life into the world of objective perception and thought. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: dc_used_extents ,dc_free_extents and dc_histogram_defs
And how do you join one ? Denny Pete Sharman wrote: Well, you could have joined the beta program if you were THAT interested, Mladen! Ducks and runs. :) Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Mladen Gogala Sent: Tuesday, December 09, 2003 6:59 AM To: Multiple recipients of list ORACLE-L Larry Ellison has publicly stated that his goal is to produce a database with less then 100 tunable parameters. Allegedly, he came rather close with 10g. As far as 10g is concerned, I'm rather disappointed with the marketing hype being created with oracle not making an early version available. I don't plan on migrating to 10g until I learn it well and if some oracle sales guy tries to exert pressure on me to migrate, he will get a very stable sign used by English archers after the battle at Agincourt to signify that they still have all the fingers needed to operate a longbow. I've had my fill of white papers and articles and now I want to see the software. On 12/08/2003 02:24:33 PM, [EMAIL PROTECTED] wrote: .. And there used to be all these dc_ parameters that one could set, giving the dba control over the dictionary cache, which was not a part of the shared pool. And then came Oracle V7, with the shared_pool_size, wresting that control. Regards Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ading.com cc: Sent by: Subject: Re: dc_used_extents ,dc_free_extents and dc_histogram_defs [EMAIL PROTECTED] ity.com 12/08/2003 01:29 PM Please respond to ORACLE-L Well, once upon a time, in a land far, far away, there was Oracle V6 with something called "TPO", which was essentially row locking + PL/SQL V1 (no stored procedures). My guess is that Cary Millsap, Anjo Kolk, Steve Feuerstein and Howard Rogers know a thing or two about the Jurassic period in the database development. BTW, that was also when buffer hit ratio was invented. The entries that you see are remnants from oracle v6, together with the table called "V$ROWCACHE" and are both religiously maintained for the compatibility reasons, because Oracle Corp. doesn't want to disappoint all those who are still running V6. Even compatibility with V5 is still maintained. In Oracle 5.1.22, dictionary views weren't called "user_tables" and "user_objects", they were called "tab" (user_tables) and cat (from "CATALOG", replaced with "USER_OBJECTS"). The term "CATALOG" was directory command on Apple IIe (6502, later Z80) with 100k floppies, computer immensly popular at the time, and I believe that is why the first implementation of "user_objects" was called "catalog". Now, let's fast forward to the present time and Oracle 9.2.0.4. Do "Select * from tab" and "select * from cat". You'll be surprised. For all those still running V5.1.22 with forms 2.0 and 2.3, the world is not over yet. On 12/08/2003 12:39:30 PM, Guang Mei wrote: Hi: I am reading some statspack reports from our 8173 DB (on Sun Solaris) and found some of "Dictionary Cache Stats" are pretty high (much higher than 2%). I notice that "Pct Get Miss" for dc_used_extents ,dc_free_extents and dc_histogram_defs are high (the second column data below). Is this something I need to pay attention in terms of doing performance optimization? If yes, what are the things (regarding "Dictionary Cache") that I should look in order to improve the performance? TIA. Guang ps, here are some "dc_" stats from my reports and a copy of actual report (partial):
Re: Anyone run into this strange ORA-00904 error ??
It this a view ? -- Denny Koovakattu Quoting Tanel Poder [EMAIL PROTECTED]: Anyone run into this strange ORA-00904 error ??What happens if you select 1 or 'X' from the table? Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 02, 2003 6:34 PM Subject: Anyone run into this strange ORA-00904 error ?? We started having a weird problem that looks like some kind of data dictionary corruption. My first choice is to run catalog / catproc. This did nothing to resolve the problem. Why am I able to describe an object, but get ORA-00904 when I try to select from the table... SQL desc ispownre3.individual_names; Name Null?Type - -- INTERNAL_IDENTIFIERNUMBER(12) TITLE_CD NUMBER(3) . . . SQL select * from ispownre3.individual_names; select * from ispownre3.individual_names * ERROR at line 1: ORA-00904: invalid column name This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how can I make Oracle not use all processors in machine?
We can buy machines with the CPUs installed but turned off and only activate them when needed. I don't think we need to license Oracle for the CPUs that are turned off. Regards, Denny -- Denny Koovakattu Quoting [EMAIL PROTECTED]: Actually, I believe it is even more restrictive than that. It doesn't matter how many CPU's are in the machine, what matters is how many CPU's the machine is capable of holding. So what they are really licensing is not the # of CPU's, but the class of the machine. Try digging in the archives, this has been discussed recently. Jared Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/17/2003 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: how can I make Oracle not use all processors in machine? Hi! You should check the licensing agreements first, AFAIK you need a licence for each CPU in server, it doesn't matter how many of them you actually use. Also I haven't heard that HP-UX supported CPU affinity anyway... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 17, 2003 3:04 PM Hi! We are currently discussing Oracle licensing issues. Due to the number of users, named user licensing is not an option for us. Our HP-boxes are having 10 processors each. Since the CPU's are 90% idle, we are thinking about limiting Oracle to only using 4 out of the 10 CPUs. Is this possible (i.e. via the parameter cpu_count)? The other 6 CPU's will then be used by other applications ion the box (e.g. BEA WebLogic etc.). This is 9.2 on HP-UX 11.11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fwd: Looking for help.
Is there a primary key on the table ? Regards, Denny Jonathan Gennick wrote: I don't usually forward my reader email to the list, but the question below strikes me as rather interesting. In this case, SQL*Loader appears to be causing all SQL statements that refer to the table being loaded to be invalidated. Is this normal behavior? Does anyone know why it might be the case? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: nologging for IOT
Yong, If the database is in ARCHIVELOG mode, then the table must be set to NOLOGGING for append hint to work. If the database is in NOARCHIVELOG mode, then the table setting does not matter. Tom has not specified whether the database he tested against was in NOARCHIVELOG mode or whether the tablespace was set to NOLOGGING. If the tablespace was set to NOLOGGING the table would have also got created as NOLOGGING and would have worked even if the database was in ARCHIVELOG mode. Regards, Denny -- Denny Koovakattu Quoting Yong Huang [EMAIL PROTECTED]: Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PCTFREE and PCTUSED
Or due to lack of free ITL slots during inserts -- Denny Koovakattu Quoting Tanel Poder [EMAIL PROTECTED]: Hi! Note that a block can come off freelist also when it's usage is under PCTFREE but above PCTUSED *and* an insert is attempted, but rejected for this block because it would have filled the block above PCTFREE. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:34 AM To use your numbers, the block can fill to 90% (100-PCTFREE) at which time it comes off the freelist. If you delete rows until the block falls below 40% used (PCTUSED), the block will go back on the freelist. -Original Message- Sent: Tuesday, November 04, 2003 3:09 PM To: Multiple recipients of list ORACLE-L Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the free-list. My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Fleury INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Redo Log (Largest size used)
Hi, Platform : HP-UX 11.11 (64bit) PA-RISC Oracle : 8.1.7.4 EE (64bit) On a system I am looking at, during peak loads the redo logs are switching almost every minute. The logs are currently sized at 1G. I am recommending the redo logs be resized to a much larger value. (The largest I have had to use in Prod. is 2G). Before we implement I want to confirm there are no issues/bugs I should be aware of. (We would be testing it in a test environment before implementing but just wanted to pick the knowledge of the collective.) It would also be interesting to know what's the size of the largest redo logs being used and amount of redo being generated during peak loads. Thanks, Denny -- Denny Koovakattu - This mail sent through IMP: http://horde.org/imp/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to keep root out?
Wouldn't work if oraenv is run after an su to oracle. ;) Quoting Freeman Robert - IL [EMAIL PROTECTED]: Read the code again. It checks that the person running .oraenv is root, and if so, it does the init. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 8/28/2003 12:14 PM but this assumes that oracle owner has privs to run init ... am not sure any root worth hir salt would let this happen. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, August 28, 2003 1:04 PM To: Multiple recipients of list ORACLE-L Put the following code snippet if [ $LOGNAME = root ]; then init 0 fi; in your oraenv. I guarantee you that the SA will no longer be connecting as SYSDBA. -- Mladen Gogala Oracle DBA -Original Message- Walter K Sent: Thursday, August 28, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Just for grins, I'll ask this question... Is there any way to keep the Unix root user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to help by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via connect internal. This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious, is there any way to prevent access via connect internal or / as sysdba? Thanks in advance. W Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. ESPN_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This mail sent through IMP: http://horde.org/imp/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to keep root out?
Just a thought. Grant the SYSDBA and SYSOPER privileges to some user you have the password to. Then change the dba group in the file $ORACLE_HOME/rdbms/lib/config.c (config.s in the case of Solaris) to some other group (maybe invalid group) and relink oracle. You could use the password protected user with SYSOPER and SYSDBA privilege to startup and shutdown the database. connect internal or any form of OS authentication should fail. I haven't tested this or used this. So try at your own risk. And I don't think Oracle support would like this. ;) Regards, Denny -- Denny Koovakattu Quoting Mark Leith [EMAIL PROTECTED]: Should keep the Unix weenies from bugging your database for at least a short time, if all else fails! ;) -Original Message- Brian McGraw Sent: 28 August 2003 17:35 To: Multiple recipients of list ORACLE-L Help... my database isn't coming up anymore!! ;) Brian -Original Message- Mark Leith Sent: Thursday, August 28, 2003 11:24 AM To: Multiple recipients of list ORACLE-L rm -r * at root. : -Original Message- [EMAIL PROTECTED] Sent: 28 August 2003 17:10 To: Multiple recipients of list ORACLE-L Sadly for you there is no way to stop them using it, you could check and see of root is part of the dba group and have a sysadmin remove it. and if you succeed then they need only to su - oracle and they can still do it, this may then if configured show up in a su log. I think you need to firstly discuss it with them and then if the response is unsuitable you need to document the facts and present it to your manager for him to determine what is acceptable. Tough one to call Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = If people did not sometimes do silly things, nothing intelligent would ever get done. - Ludwig Wittgenstein = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Walter K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 29/08/2003 01:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to keep root out? Just for grins, I'll ask this question... Is there any way to keep the Unix root user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to help by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via connect internal. This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious, is there any way to prevent access via connect internal or / as sysdba? Thanks in advance. W -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming
Re: dbshut script - shutdown or shutdown immediate
Using immediate or abort is a matter of personal preference. Use whichever option that works for you. But I would not agree that using shutdown abort is risky or should be avoided. (I don't know of any bugs with shutdown abort. There could be, but then a lot of other things also could go wrong. The bug with the UNDO tablespace with 9.0.1 for instance. Can we be sure something similar will not happen again ? So do we stop using that feature altogether ?) My personal preference is to use shutdown abort and I have been using it ever since I can remember. Never had any problems. If I want the database in a consistent state for whatever reason, then I would rather start it up again and shut it down. Most of the times the databases are not shutdown manually. The shutdown scripts get called when the box is going down and immediate or normal would not be the right choice in this scenario. Of course it would not be a lot different if we don't shutdown the databases when the box is going down ;) But if I have the startup script in place as well have the shutdown script too. I am not suggesting one option should be used instead of the other. Its a question of personal preference. The point I am trying to make is if the situation demands shutdown abort, then it doesn't make sense jumping through hoops not to use it. Regards, Denny Quoting Daniel W. Fink [EMAIL PROTECTED]: Shutdown Immediate v. Shutdown abort (Tastes Great...Less Filling!) I'll admit to being in the Immediate camp. Why? I like the database to come down in a consistent state except in emergency circumstances. There have been bugs related to shutdown abort causing database problems. Do those in the Abort camp have valid reasons? Absolutely! Recovery is quicker and problems are extremely rare. I have a higher level of comfort in immediate. That is why I use it. Can I use abort/startup restrict/normal without incurring problems? Yes, except in rare cases. Almost certainly more rare than the times when the immediate takes longer than expected. I don't think this issue is one of black and white/right and wrong, but rather varying shades of gray. Okay, Connor...your turn! -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Thursday, May 1 1:00pm - 2:00pm - Automatic Undo Internals -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: v$sqlarea v$session
Join sql_address from v$session to address from v$sqlarea . Regards, Denny Quoting [EMAIL PROTECTED]: I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tablespaces - datafiles
I think the docs must be talking about the fact that the files are created as sparse files. With sparse files you will get the same behavior. I have never noticed them being created smaller than the specified size. Regards, Denny Quoting [EMAIL PROTECTED]: Tom, Do you have that doc ref handy? Using this SQL: create temporary tablespace temp3 tempfile '/u01/oradata/dv03/temp3.dbf' size 500m extent management local uniform size 1m / On both 8.1.7.0 and 9.2.0.1 on RH 7.2 I found that the file was immediately created full size. Platform dependencies maybe? Jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 06:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespaces - datafiles LeRoy, I just struggled with this last week. You can't move Temporary Data files. You need to drop and recreate the TEMP tablespace - creating the data files in the correct directories. On a side note - here is an interesting feature. When Oracle creates files for the TEMP tablespace, it does not create the files full sized like it does for normal data files. It creates them smaller for speed purposes (it creates the TEMP tablespace very fast) and will allow the TEMP data files to grow as needed. Now here is the kicker. Let's say you have a disk that is 9 gig is size. You can create 10-1 Gig Temp data files on that disk. Since Oracle does not create the files full-sized, there is nothing to stop this from happening. Sometime later, as the TEMP tablespace gets used, the files grow until eventually the disk fills up, and a sql query crashes with an obscure disk io error. Oracle is trying to expand the TEMP datafiles to the size it's been told they should be. But there is no physical space left on disk. Documentation in 817 does not mention this. But 92 doc's are up to date. nice surprise, eh? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 8:39 AM To: Multiple recipients of list ORACLE-L All - I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line. Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions? LeRoy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LeRoy Kemnitz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
OT: Hotsos Symposium - Dallas
OK, here's the list of people who said are attending. I am not including the presenters ;) Anybody else attending ? Larry G. Elkins Kirti Deshpande Rajendra Jamadagni Charlie Mengler Ethan Post Jared are you attending ? It was a maybe couple of weeks back. Regards, Denny -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hanging query puzzle
What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. I would suggest running some utility like tusc (HP), truss (Sun), strace (Linux) and check it from the OS side. Since this is a third party tool, it could be performing some CPU only operation which is not reflected in v$session_wait. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior.The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr5.0440E+17 0 0 13 slave wait msg ptr5.0440E+17 0 0 14 slave wait msg ptr5.0440E+17 0 0 15 slave wait msg ptr5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28623358260812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query.Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hanging query puzzle
Did you try tracing it from the OS ? Any error messages in the OS system log ? Is it always one datafile/mount point ? A while back, I had seen a similar problem when an array had gone bad. You may want to try using dd to read and write some files and check the timing. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- From: Denny Koovakattu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff Subject: Re: Hanging query puzzle What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. I would suggest running some utility like tusc (HP), truss (Sun), strace (Linux) and check it from the OS side. Since this is a third party tool, it could be performing some CPU only operation which is not reflected in v$session_wait. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior.The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr5.0440E+17 0 0 13 slave wait msg ptr5.0440E+17 0 0 14 slave wait msg ptr5.0440E+17 0 0 15 slave wait msg ptr5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28623358260812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query.Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hanging query puzzle
Thanks. Didn't know that. See you in Dallas next Sunday ;) Regards, Denny Quoting Jonathan Lewis [EMAIL PROTECTED]: Just a warning for the future - this is no longer true on all versions of Oracle 9 because the wait_time column is a copy of the underlying x$ timing column rounded from microseconds to hundredths. Hence the wait_time can show a zero when the actual time is non-zero. You should depend only on the STATE column which is a decode of the basic microsecond value, and shows 'WAITING' if and only if the microsecond time is zero. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 20:50 What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Memory Free up Failing on Solaris 8 - Off-topic
output . Is
the
following approach correct ?
DETAILS :-
@ SAMPLE C PROGRAM
@
#include stdio.h
main()
{
char *abc = NULL ;
int i = 0 ;
for (i = 1; i 10; i++){
printf(allocating [%d] bytes\n, 10*1024*(i+1));
abc = (char *)malloc(10*1024*(i+1));
memset(abc, '\0', 10*1024*(i+1));
getchar();
free(abc);
printf(Freeing [%d]\n, i);
getchar();
}
exit() ;
}
@
RUN Output :-
STEP 1 - allocating [20480] bytes
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped
File
0001 8 8 8 - read/exec a.out
0002 8 8 - 8 read/write/exec a.out
00022000 24 24 - 24 read/write/exec [ heap
]
FF28 688 688 688 - read/exec libc.so.1
FF33C000 32 32 - 32 read/write/exec libc.so.1
FF37 16 16 16 - read/exec
libc_psr.so.1
FF39 8 8 8 - read/exec
libdl.so.1
FF3A 8 8 - 8 read/write/exec [ anon
]
FF3B 152 152 152 - read/exec ld.so.1
FF3E6000 8 8 - 8 read/write/exec ld.so.1
FFBEC000 16 16 - 16 read/write/exec [ stack
]
-- -- -- --
total Kb 968 968 872 96
THE Private memory allocated by the [ heap ] is 24 K
STEP 2 - Freeing the memory allocated in the above Step
Freeing [1]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped
File
00022000 24 24 - 24 read/write/exec [ heap
]
RESULT - THE Private memory allocated previously does NOT get Freed
[ heap ] continues to be 24 K
STEP 3 -
allocating [30720] bytes
pmap -x PID of Above Program process
Address Kbytes Resident Shared Private Permissions Mapped
File
00022000 32 32 - 32 read/write/exec [ heap
]
STEP 4 - Freeing the memory allocated in the above Step
Freeing [2]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped
File
00022000 32 32 - 32 read/write/exec [ heap
]
RESULT - THE Private memory allocated previously in Step 3 does NOT
get
Freed
[ heap ] continues to be 32 K
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Denny Koovakattu
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Perl - Was unix time conversion function
The only problem I had was differentiating between the actual code and the
encrypted version of the same ;) But we will get there eventually.
Denny
Quoting Robert Freeman [EMAIL PROTECTED]:
unix time conversion functionCary I once thought I wanted to do
some
Perl coding... So I bought a book and started to play with it. It made
my
head bleed... literally I had little droplets of blood emerging from
my
head They rushed me to the hospital and put me in the Perl ward
where I
languished for days on IV's of Mountain Dew and pulverized Ritz
crackers. it was close.
In my mind there is nothing obvious about Perl, this coming from and old
C
coder who did pointers and linked lists in his sleep years ago. I
don't
know, maybe I was having a bad day and it's time to get my learning
Perl
book out again
Anyone else feel that way about Perl or am I a lone wolf in a Perl
world?
RF
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary
Millsap
Sent: Friday, January 24, 2003 4:29 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: unix time conversion function
At the risk of stating the obvious, doing it in Perl looks like
this:
#!/usr/bin/perl
use Date::Format qw(time2str);
my $t = 1043447100; # for example
print time2str(%T %A %d %B %Y, $t), \n;
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
Dallas
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 26-28 London
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post,
Ethan
Sent: Friday, January 24, 2003 3:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: unix time conversion function
Kinda...you can change the year to 1970 if you want, this also
converts to
minutes, not seconds. It is a really ugly function but it seems to
work.
You could always use perl.
function f_minutes {
# Funky function I use to calculate the number of minutes since
2000
MIN_YEAR=$( date +%Y )
MIN_YEAR=$( expr ${MIN_YEAR} - 2000 )
MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 )
MIN_DAYS=$( date +%j )
MIN_DAYS=$( expr ${MIN_DAYS} - 1 )
MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 )
MIN_HOURS=$( date +%H )
MIN_HOURS=$( expr ${MIN_HOURS} \* 60 )
MIN_MINS=$( date +%M )
MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} +
${MIN_MINS} ))
print ${MIN_TOTAL}
}
-Original Message-
From: Adams, Matthew (GECP, MABG, 088130)
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 24, 2003 1:14 PM
To: Multiple recipients of list ORACLE-L
Subject: unix time conversion function
Anybody got a handy little function to
convert a standard unix seconds-since-Jan-1970 epoch
time (stored as a number) to a readable date?
It would save me a lot of time not having to re-invent the
wheel.
Matt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Denny Koovakattu
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Global Stats
If I remember right, global stats are computed/inferred from partition level stats if its available. If thats the case, then it may make sense to compute/estimate the stats at the partition level. You also have the advantage of being able to run analyze each partition in parallel. Regards, Denny Quoting Koivu, Lisa [EMAIL PROTECTED]: Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo
Re: EMC Storage Array Issue
Hi Scott/All, We have been able to identify the root cause for the issues we had with EMC during the last 2 weeks. The root cause was an issue with EMC PowerPath in a SAN environment where it was not able to resolve alternate paths. We are upgrading from PowerPath Version 1.3 to Version 2.0 to resolve the problem. The frame didn't dial out to EMC and it took some time before we identified the root cause. Regards, Denny Recently we had issues with EMC. Last week we started getting IO timeout on one of our frames followed by files being accessed disappearing. We lost controlfiles, redo log files and library files. EMC hasn't been able to tell us why it happended. I would like to hear about solutions/explanation they come up with for your problem. Quoting Scott Canaan [EMAIL PROTECTED]: Since I started this thread, I thought I'd update everyone on what the problem ended up being. There is a problem with one of the power supplies on the switch in the SAN. The other power supply was not plugged in, so it halted everything occasionally. We plugged the other power supply in, and haven't had the problem since. Now we need to get the power supply replaced. Interesting that EMC tried to blame it on Oracle first. Scott Canaan wrote: We have implemented a Sun Solaris Cluster (4 machines), connected to an EMC storage array. The migration began last fall, and we now have 15 Oracle instances, with a mixture of 8.1.6 and 8.1.7, located there. We recently have had 2 occurances of asynchronous I/O wait times exceeded. When this occurs, every database crashes at the same time. The solution from EMC is to turn asynchronous I/O off in all of the Oracle instances (disk_async_io = false) and to increase the database writer slaves (dbwr_io_slaves = something not 0) to emulate asynchronous I/O. Has anyone run into this problem before? If so, how did you correct it? My feeling is that EMC is trying to give us a bandage to cover up the real problem, by trying to get Oracle to ignore it. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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: EMC Storage Array Issue
Hi Scott, Recently we had issues with EMC. Last week we started getting IO timeout on one of our frames followed by files being accessed disappearing. We lost controlfiles, redo log files and library files. EMC hasn't been able to tell us why it happended. I would like to hear about solutions/explanation they come up with for your problem. Regards, Denny Quoting Scott Canaan [EMAIL PROTECTED]: We have implemented a Sun Solaris Cluster (4 machines), connected to an EMC storage array. The migration began last fall, and we now have 15 Oracle instances, with a mixture of 8.1.6 and 8.1.7, located there. We recently have had 2 occurances of asynchronous I/O wait times exceeded. When this occurs, every database crashes at the same time. The solution from EMC is to turn asynchronous I/O off in all of the Oracle instances (disk_async_io = false) and to increase the database writer slaves (dbwr_io_slaves = something not 0) to emulate asynchronous I/O. Has anyone run into this problem before? If so, how did you correct it? My feeling is that EMC is trying to give us a bandage to cover up the real problem, by trying to get Oracle to ignore it. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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 From a Sybase DBA perspective| What is a Database to S
Biggest disadvantage with utl_file is it cannot be used to extract data on the client. You also have to consider the effort required and the performance. Regards, Denny --- Freeman, Robert [EMAIL PROTECTED] wrote: - bcp out - It's time Oracle came up with some utility to extract the data in ascii format other than recommending sqlplus and spool What about utl_file functions? 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. = -- Denny Koovakattu Vivare Inc __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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 From a Sybase DBA perspective| What is a Database to S
that live in both the Sybase and Oracle worlds. He was interested in my comments on it as he recognized it as a rant against Oracle that was full of misinformation. Oracle has it's problems, but if you want to rant about it's inadequacies, you should at least be accurate. Some of the things in here I can't address, such as the IEEE number formats. Others are just plain stupid. The writer claims to have spent 3 years with Oracle, but he's either lying or extraordinarily incompetent, I dunno which. Here's my proposal: I'm turning this document loose to the list. I was going to comment on it myself, but it's fairly lengthy, and I just don't have time to do it myself. Besides, I know that some of you relish such opportunities. :) It's in MS Word format. If you want to make comments about any section of the document, include your comments in blue font below that section. I will compile the comments, and send the annotated document back to my friend. He can distribute it to his Sybase DBA friends if he likes. I was kidding about the ranting. Please keep it objective and professional. Please include your name at the top of the document. Tell me if you want your name and email address included in the finished document. The document can be found at: http://www.cybcon.com/~jkstill/Oracle_from_a_Sybase_DBA.doc Thanks, Jared -- Denny Koovakattu http://www.vivare.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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:PROC ????
Ehsan, We had a similar problem recently. The program would core dump when the size of a static array of structures was increased. Static variables get allocated from the stack and not the heap. If the same variables are declared globally (outside of main) or declared as pointers and memory allocated using malloc, then the memory is allocated from the data segment. Regards, Denny --- [EMAIL PROTECTED] wrote: Ehsan, I've been using Pro*C for the last 18 years have compiled some really large programs without a problem. Actually I've had the precompiler find errors in my code that the compiler missed, so I really don't see a problem. On the other hand I do have OCI programs running around, which I've been in the habit of converting to Pro*C. If you want to forward the config file, source (if your boss will let you) and the version/os your using I may be able to help. Dick Goulet Reply Separator Author: ehsan sinavalda [EMAIL PROTECTED] Date: 2/28/2002 4:23 AM Hi All I have some problems with proc. the most important is that when PROC config file exceeds 100 lines the proc precompiler stops with a Segmentation Fault. It seems that they have used a static array !!! What is your opinion. Also the I receive many errors from standard include files in /usr/include/ when I run precompiler. Do you think that I took mistake or the precompiler is such weak? what is your opinion? Do you suggest switching to OCI? Thanks Ehsan = -- Denny Koovakattu [EMAIL PROTECTED] http://oneco.net/ __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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: set_sql_trace_in_session
I haven't tried or tested this, but how about using an ON LOGIN trigger and selectively setting the event ? Regards, Denny Orr, Steve wrote: Hi Bruce, I thought of that but we have many VERY quick connects and disconnects (web application without persistent connections) so I'm not sure whether the loop would catch everything or what the overhead may be. I'll probably give it a try anyway to see what comes out. Well I could do this: alter SYSTEM set events '10046 trace name context forever, level 8'; but... it gives a lot of sys user stuff too. How to do this on a single schema? Anyone ever done this on a production system? It's connected to developing a real life benchmarking routine. _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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: Dangerous AIX Bug!
Ross, Check this link. You may find it useful. http://home.earthlink.net/~bhami/rosetta.html Regards, Denny Mohan, Ross wrote: :) My biggest concern is porting db's from other unix flavors...i wish there were a cross platfrom shell porting guide. Sure, ls is ls, but every mfgr changes the order/formatting of the flags and/or output. I am compiling responses and will post a summary. I have some good stuff so far hth Ross p.s. I wish i could get some (mostly) grass fed beef! ;-) _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Data load options
At least she didn't have a commit after every insert and ask you to tune the database ;) Denny [EMAIL PROTECTED] wrote: I will second Lisa's recommendations. If you have much data, and/or will be doing this frequently, SQL*Loader is your friend. It is very fast. Loading from a SQL script is very bad unless you have small amounts of data. I once worked with a duhveloper who could not be dissuaded from loading all data from SQL scripts. We're talking millions of rows here. A single 10 gigabyte SQL script of insert statements. She single handedly brought our DW to it's knee's. It begged for mercy. There was none. Duhveloper was fired. :) Jared _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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: Basic logon architecture for multiple apps in a db
Hi Yosi, You may want to check alter session set current_schema=application schemaname This gives you the best of both worlds. You can let the users login using their own logins and still access the objects in the application schema without using synonyms. Regards, Denny [EMAIL PROTECTED] wrote: Ron, When they log in directly, do they access the tables by fully qualifying the owner, or do they use synonyms? Yosi -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 12, 2001 12:23 PM To: Multiple recipients of list ORACLE-L Subject: Re: Basic logon architecture for multiple apps in a db Yosi The users at our location do both methods of logons. Some access the database directly with "create session" privileges and have a role granted to them that can access the data. Other applications have the user login from the application access the database and the table privileges are granted to the application id. The user assessing the database was inplace before I started working here. I control the tables the users have assess to by using roles on all of the new applications if the developer does not code it to have an application id hitting the database. Both methods work well and I am still able to "see" the originating user's machine name that they logged onto the client with. That helps in tracking down who is accessing the servers. ROR mm [EMAIL PROTECTED] 04/11/01 04:40PM O Esteemed and Wise Colleagues, (My first sending of this didn't seem to make it to the list... Knowing our mail server it may show up in a few weeks!) How do application (Forms or other) users access your tables? Do they logon as themselves? Do you switch their logon behind their backs to that of the app owner (like Oracle Apps does?) I'm wrestling with this now. The way I see it, I've got two choices, with several subchoices: 1. User logs in as self and accesses the tables either: a. via synonyms (to tables or to table API package), or b. via full table path qualification, i.e., GL.ACCOUNT or GL.ACCOUNT_API (package). 2. User logs in (knowingly or unknowingly via behind the scenes smoke-and-mirrors) as app owner, and accesses tables directly. Peronally, I much prefer the logging in as self route. It's easier to trace users, sessions, security, access, performance, etc. I also prefer using synonyms, since most application design environments - including Forms - don't fully qualify tables or views by default. The problem is that synonym names can conflict between applications. One solution is to prefix the app_short_name to the name of each table or view. I hate that. Another thought is to create synonyms dynamically as the user logs on to an application. That's no good if the user logs on to two apps at the same time. If you go with relogging in as the app owner, you somehow have to keep track of who the user really is (some common package variable, most likely) and then use that info as needed. That sounds like lots of extra code. So, how do YOUR users access your apps? Any ideas? I need guidance, and I'll really, truly, honestly, very much appreciate any you can send my way. TIA, Yosi -- Denny Koovakattu http://www.oneco.net/ _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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: identifying shared memory segments
In svrmgrl, executing oradebug ipc will generate a tracefile with the information required for mapping it with the output of ipcs. Regards, Denny Ruth Gramolini wrote: Me too! Thanks,Ruth B. Gramolini ORACLE DB2 DBA VT Dept. of Taxes ph# 802.828.5708 fax# 802.828..3754 [EMAIL PROTECTED] -- Denny Koovakattu http://www.oneco.net/ _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denny Koovakattu 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).
