RE: Indentifying Redundant Indexes
Mark, The obvious redundant indexes are the ones the n columns of which happen to be, and in the same order, the nth first columns of another index. There is not much which can be said besides. First of all, I would question your definition of redundant as never used by Oracle. Some indexes are sometimes used which in truth should not if you care a bit about performance; being used by Oracle is not a guarantee that they speed up queries. If I were you, I would try first to narrow the scope. well-known tuneDisk space costs nothing these days/well-known tune. I do not fully agree, especially as backing up your Terabytes has a cost, in time if nothing else. But let's put this aside. Where your indices hurt, it's quite obviously with DML (for updates, only if the updated columns are indexed), first because you have of course additional memory scanning/writing and I/Os, and second because since indices are by nature more compact than tables, they are more susceptible of content! ion, with all transactions fighthing over the same small amount of bytes (ladies please skipthe coming January sales could be a good image/ladies please skip). If you concentrate on those of your tables which are most heavily inserted and deleted and try to get a good picture of the queries against them, I believe that you will probably address 90% of issues. HTH, SF - Original Message - From: Mark Richard [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 22 Dec 2002 20:53:36 Dear List, Firstly - Merry Christmas to those who have not already departed for the holiday season. I'm currently doing some investigation based around indexes and would like everyone's opinion: What is everyone's preferred approach to identify redundant (as in never used by Oracle) indexes? I believe Oracle 9 might have a feature to set a flag on objects and then check back later to see if they have been accessed however we're still stuck on 8.1.7.4 (Solaris). Some of my thoughts include: * Can query for physical disk i/o at a tablespace level easily, however more difficult to go to an object level. * Could create a trace file and then inspect explain plans for existence of index accesses, however trace file probably not practical to capture for a long period of time. * Ideal statistic would be something along the lines of index x used y times in last 24 hours, however a simple index x was used in the last 24 hours would be ok. Obviously we are searching for indexes to remove and identifying those which aren't queried over a set period of time would be good candidates for a starting point. Any advice you might have would be greatly appreciated. Regards, Mark. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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: echo `date`
eh? Try the key next to '1' (to the left)...all depends on how your keyboard is setup, nationality (of the os, not you :O). -Original Message- Sent: 23 December 2002 06:44 To: Multiple recipients of list ORACLE-L Dear List, I have Korn shell Script that show the date of execution. echo `date` - Start shutdown of oracle DB. Could any body tell me how to display this char ` the quotation for date. I mean which key I have to press in Unix to get this quotation character. I'm working with Unixware7.1 Regards, Ashraf __ 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: Ashraf Salaymeh 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: Thomas, Kevin 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: TNSNAMES.ora to LDAP/OiD conversion
Hello Stephen We are in the process of converting the use of tnsnames to oid. Just yesterday, Deborah from Oracle Israel was here and created replication environment, with failsafe between two oid servers. You can contact me directly if you want more details. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 21, 2002 2:35 AM Has anyone seen or does anyone have any whitepapers on converting to OiD (Oracle Internet Directory) solely for name resolution (i.e. to replace tnsnames.ora)? I am most interested in something that would discuss the various thoughts that need to go into designing an LDAP tree and what the advantages and disadvantages of each are. If anyone has gone through this and would be willing to discuss it with me sometime, please send me contact information off-list. TIA Stephen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert 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: Yechiel Adar 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: Indentifying Redundant Indexes
There is also the issue of keeping an index that is not used in any explain plan, but is required to prevent a foreign key locking problem. hth connor --- Stephane Faroult [EMAIL PROTECTED] wrote: Mark, The obvious redundant indexes are the ones the n columns of which happen to be, and in the same order, the nth first columns of another index. There is not much which can be said besides. First of all, I would question your definition of redundant as never used by Oracle. Some indexes are sometimes used which in truth should not if you care a bit about performance; being used by Oracle is not a guarantee that they speed up queries. If I were you, I would try first to narrow the scope. well-known tuneDisk space costs nothing these days/well-known tune. I do not fully agree, especially as backing up your Terabytes has a cost, in time if nothing else. But let's put this aside. Where your indices hurt, it's quite obviously with DML (for updates, only if the updated columns are indexed), first because you have of course additional memory scanning/writing and I/Os, and second because since indices are by nature more compact than tables, they are more susceptible of content! ion, with all transactions fighthing over the same small amount of bytes (ladies please skipthe coming January sales could be a good image/ladies please skip). If you concentrate on those of your tables which are most heavily inserted and deleted and try to get a good picture of the queries against them, I believe that you will probably address 90% of issues. HTH, SF - Original Message - From: Mark Richard [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 22 Dec 2002 20:53:36 Dear List, Firstly - Merry Christmas to those who have not already departed for the holiday season. I'm currently doing some investigation based around indexes and would like everyone's opinion: What is everyone's preferred approach to identify redundant (as in never used by Oracle) indexes? I believe Oracle 9 might have a feature to set a flag on objects and then check back later to see if they have been accessed however we're still stuck on 8.1.7.4 (Solaris). Some of my thoughts include: * Can query for physical disk i/o at a tablespace level easily, however more difficult to go to an object level. * Could create a trace file and then inspect explain plans for existence of index accesses, however trace file probably not practical to capture for a long period of time. * Ideal statistic would be something along the lines of index x used y times in last 24 hours, however a simple index x was used in the last 24 hours would be ok. Obviously we are searching for indexes to remove and identifying those which aren't queried over a set period of time would be good candidates for a starting point. Any advice you might have would be greatly appreciated. Regards, Mark. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ 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.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: db block Size for Indexes Tablespaces in 9.2 ?
Hi Jonathan Would like to have Tests done for BOTH Small Big DB_BLOCK_SIZE , if possible , as mentioned below Thanks -Original Message- Sent: Wednesday, December 18, 2002 4:50 PM To: Multiple recipients of list ORACLE-L I think there are too many generic arguments available for picking the 'right' block size for your indexes. The one that is most appropriate is likely to depend on the nature of the activity (load vs. query), nature of the index (unique, nearly unique, far from unique), data clustering, order of data arrival, frequency of data arrival, pattern of data deletion/update, stability of volume, nature of queries (big or small range scans), potential of modifying number of branches, buffering effects, and whether or not you are using a filesystem with or without direct i/o. Given another 10 minutes I might come up with a few more ideas. Your strategy should be to identify the extreme, and critical, characteristics of your system and play to them - small block size may be appropriate, reverse indexes may be appropriate, getting rid of the synthetic key that is likely to cause a problem may be appropriate. But don't assume that anything as trivial as tweaking a block size is a driving feature of making your index work well. Which test case would you like to see - the one I did for company X that showed they needed a small block size, or the one I did for company Y that showed they needed a large block size ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 18 December 2002 05:59 Hi Arup , List Your point is Correct about High buffer busy wait Contention During Large OLTP Insert /Updates. High buffer busy wait on Corresponding INDEX during INSERT Operations was Observed during our previous benchmark which overcame by Converting to REVERSE Index as the Field Value was Sequentially Increasing . Following Article advocates HIGHER Block Size for Index , Strangely :- http://www.tusc.com/oracle/download/author.html#loneyk What may be the Commonly followed Best practice for DB_BLOCK_SIZE for Index Tablespaces in 9.2 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: 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).
RE: RE: echo `date`
Needless to say, it's on another key on my own keyboard ... Ashraf, you may locate more easily that universal sign, $, and parentheses. In Korn shell, you can also write echo $(date) - Start shutdown of oracle DB. HTH, SF - Original Message - From: Thomas, Kevin [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 23 Dec 2002 00:48:53 eh? Try the key next to '1' (to the left)...all depends on how your keyboard is setup, nationality (of the os, not you :O). -Original Message- Sent: 23 December 2002 06:44 To: Multiple recipients of list ORACLE-L Dear List, I have Korn shell Script that show the date of execution. echo `date` - Start shutdown of oracle DB. Could any body tell me how to display this char ` the quotation for date. I mean which key I have to press in Unix to get this quotation character. I'm working with Unixware7.1 Regards, Ashraf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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).
Java to Database Basics
More More people are Accessing the Oracle Database Thru Java Calls , programs etc. What basics , related Database performance issues etc. are part of this Java game with respect to the Database ? How can a structured learning be done on this ? Total Novice on Java here though familiar with Oracle Databases (DBA) -- 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).
RE: Indentifying Redundant Indexes
Cary, I assume that using stored outlines will achieve No 2 in your list. Would that not be an easier approach? If you altered the system to have CREATE_STORED_OUTLINES=true and ran for a period when all scripts are likely to be run, say a month so that all month-end processing was completed, and then check the OUTLN.OL$HINTS.HINT_TEXT column with a query such as the following. 1 select hint_text from outln.ol$hints 2* where substr(hint_text,1,5) = 'INDEX' SQL / HINT_TEXT INDEX(SIMPLE_PK_5M SIMPLE_PK) HTH John -Original Message- Sent: 23 December 2002 05:59 To: Multiple recipients of list ORACLE-L Mark, The way we do it is by what you might call extreme brute force: 1. Capture all SQL from the applications (not just the stuff you've run in the past month, but the stuff you'll run in the future too, like period-end close processes, and so on). 2. Generate execution plans in production for all this SQL. Store the plans. 3. In a structural replica system (that is, a full-schema test instance; you don't need real application data, but you do need schema and db statistics imported from the production system), drop the index. 4. Generate execution plans on the replica system for all the SQL. Store the plans. 5. Compare the two sets of plans from steps 2 and 4. 6. Decide whether the different in 4 that are different from the plans in 2 are better or worse than the plans in 2. Like I said, it's a big hammer method, but it has its reliability advantages, and the only step that we haven't automated is #6. (I'm assuming that you already have a valid test system as described in #3.) The tool we use that does steps 2, 4, and 5 is called Project Laredo (www.hotsos.com/products/laredo). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Richard Sent: Sunday, December 22, 2002 10:54 PM To: Multiple recipients of list ORACLE-L Dear List, Firstly - Merry Christmas to those who have not already departed for the holiday season. I'm currently doing some investigation based around indexes and would like everyone's opinion: What is everyone's preferred approach to identify redundant (as in never used by Oracle) indexes? I believe Oracle 9 might have a feature to set a flag on objects and then check back later to see if they have been accessed however we're still stuck on 8.1.7.4 (Solaris). Some of my thoughts include: * Can query for physical disk i/o at a tablespace level easily, however more difficult to go to an object level. * Could create a trace file and then inspect explain plans for existence of index accesses, however trace file probably not practical to capture for a long period of time. * Ideal statistic would be something along the lines of index x used y times in last 24 hours, however a simple index x was used in the last 24 hours would be ok. Obviously we are searching for indexes to remove and identifying those which aren't queried over a set period of time would be good candidates for a starting point. Any advice you might have would be greatly appreciated. Regards, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
Re: corrupted database
John. Our current problem has been narrowed down to two possabile troubles. 1 is the PERC controller card. The sysadmin missed the message that the battery needed to be reconditioned a week or so ago. They reconditioned the battery and the errror messages stopped and the connect time improved some what to about 10 - 15 seconds. The other possability is that our connections have been increasing lately and there is a memory problem that only show up when there is larger than normal connections. will keep you posted. Ron [EMAIL PROTECTED] 12/20/02 03:44PM If you ever find out the problem please post your findings - I'm going thru a similar issue (9.2.0.2) [EMAIL PROTECTED] 12/20/02 01:50PM Lindon, We have had a controller card go intermittently hay-wire and corrupted the database. It even caused corruption to read-only tablespaces. It is quite possible to corrupt Oracle read-only files with an os or hardware problem. A good backup procedure was the saving grace for that one. Currently I am attempting to diagnose slowdowns in logging into the database. Normal connect time is 3 seconds. During slow downs connect 60 seconds. All indication so far point to memory problems or controller problems. Difficult to trouble shoot without tools built for the os. (Novell). It takes a lot of paging through the os displays before and after the trouble occurs. A reboot of the system clears the trouble for a week or so. Ron [EMAIL PROTECTED] 12/20/02 01:55PM Yes. Twice. First time - bad controller. It wrote CRAP in random spots throughout the database. We had to recover after the hardware was replaced. Second time - no idea. Oracle Support said they'd need mounds of info from the o/s and even then they may not be able to explain it. Since I didn't have the time to gather all the necessary info we let it go. What a lovely thought for a Friday afternoon. At least my databases aren't corrupted. Lisa Koivu Oracle Datababy Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- Sent: Friday, December 20, 2002 1:35 PM To: Multiple recipients of list ORACLE-L connecti The reason I ask is because people around me always blame Oracle when things stop working. Anyways, the problem was traced to a corrupted Oracle database (as to whether the tables or the data file got corrupted. how and why - a $$$ consultant is trying to find out). How could an Oracle database get corruppted in the first place? Anyone here with an experience of their Oracle database getting corrupted and what caused it and what was done to fix it? -- Lyndon Tiu Quoting DENNIS WILLIAMS [EMAIL PROTECTED]: Lyndon I would look at the wait statistics to see what is happening inside Oracle. I would also look at the O.S. performance statistics to see what is happening underneath Oracle. Don't make any rash assumptions. Also, are any error messages or trace files generated? For me the funny story was a misunderstanding of the Unix nice value for an unfamiliar platform. Long story short, we wound up with batch running at a higher priority than interactive users. New users were shut out. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 9:45 AM To: Multiple recipients of list ORACLE-L connections Hello, Just in time for the Holidays. Oracle stops accepting connections. I am wondering if people here can give me their horror stories when Oracle stops accepting new connections or stops accepting connections altogether? Scalability problems when you've got around 5,000 concurrent connections? MTS/Shared server configurations enabled or disabled? It could be an Oracle problem or the application that's trying to connect's fault. Any tips and insights into what caused your horror story and how it was fixed. Thanks. Have a Happy Holidays everybody. -- Lyndon Tiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Happy Holidays
Title: RE: Happy Holidays Some gifts are best left unwrapped ... you know what I mean ... (NO !!, you Sir ARE naughty indeed ...) Happy Holidays 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- From: Arn Klammer [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 10:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Happy Holidays But how long did it take to wrap??? :-) This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Happy Holidays!!
Hi Stephen - you wrote - I'm not orawoman, so I won't plan on join a girls night out, but I hope there will be a list-wide gettogether with no gender discrimination. I'm sure they will. And anyway, I wouldn't object to men at orawoman night, I just think it would be fun to have us (orawomen) get together. After all, they let men in at ladies night at clubs, so why not at orawoman night at IOUG? No gender discrimination intended. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lisa Corell Auerbach 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: Row cache locks on INSERTs with a sequence
Title: RE: Row cache locks on INSERTs with a sequence If I stated dc_segments in my original post, I apologize, I *did* mean to say dc_sequences. At any rate, as usual, the problem was poor application code. The row cache lock no longer shows up as one of the top 5 wait events per statspack. And of course, the programmers never apologized, or even deigned to acknowledge appreciation in improving their application, they are simply acting as offended cats will do, as though the DBA team doesn't exist. -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 11:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Row cache locks on INSERTs with a sequence You are nice person. I still think the problem was mainly a sequence problem. regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/02 9:43 PM And I was unlucky :( Cache id 13 is dc_sequences on 8i and 9i versions of oracle, so I guessed shared pool shortage, if latch waits did not point to SQ enqueue... - Kirti -Original Message- Sent: Friday, December 20, 2002 3:39 PM To: Multiple recipients of list ORACLE-L It matches to the cache# in v$rowcache. I have to say that I seem to have got to a suitable conclusion by mistake. The OP quoted cache id 13, and said it references dc_segments - hence my suggestion about small extents. In fact cache id = 2 is dc_segments, and cache id = 13 is dc_sequences (at least on my 9.2 system - the values may vary across version). I based my comments on dc_segments - not on the cache id number - still, I got lucky ! Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 20 December 2002 21:11 What does it mean cache id 13 ? Regards, Waleed -Original Message- Sent: Friday, December 20, 2002 2:29 PM To: Multiple recipients of list ORACLE-L If the wait times on the latch were significant, I think I'd check that the inserts were high volume inserts into tables with a very small extent sizes and lots of indexes, also with very small extents. I wouldn't have thought it was anything to do with sequences. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Khedr, Waleed 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: VB Objectives needed please !
Not 100% what you mean, but I have worked on several projects without VB and .Net if you can give me some examples I'll see if I can help. The biggest issues we have been facing with VB is dealing with dates and numbers where the application and web server hate none IEEE formats. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 9:28 PM I am facing lot of difficulties with out VB objective questions and Answers and oracle objective questions and Answers.Can any one help me by sending it Its my humble request Regards, [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: guess who 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: Jay 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).
RMAN and cron
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server. When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
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: VB Objectives needed please !
I also amnot sure what you mean. I am currently on a VB project. One thing that we have developed as a standard is to only perform database updates using Oracle PL/SQL stored packages and procedures. VB does not perform any updates at all. Queries are all performed using PL/SQL as well, returning reference cursors through ADO to provide XML strings for the ASP pages. Working well for us. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, December 23, 2002 8:19 AM To: Multiple recipients of list ORACLE-L Not 100% what you mean, but I have worked on several projects without VB and .Net if you can give me some examples I'll see if I can help. The biggest issues we have been facing with VB is dealing with dates and numbers where the application and web server hate none IEEE formats. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 9:28 PM I am facing lot of difficulties with out VB objective questions and Answers and oracle objective questions and Answers.Can any one help me by sending it Its my humble request Regards, [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: guess who 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: Jay 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).
Java Database Connection
All, Given that connections to SYS have to be done as sysdba, does anyone know what the JAVA syntax looks like to connect to the sys user? I'm writing a procedure for compiling any invalid objects and, while a system is ok for 99% of the problems, it doesn't have permissions for fixing sys objects. Admittedly this is only a problem in my test case (revoking select on dual does a wonderful job of making invalid objects), but I would like my code to work if something did go invalid in sys for some reason. Thanks, John John P Weatherman Oracle Database Administrator Replacements, Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN and cron
Are you executing (or running) the env variable script; then running the
backup script? If so, then the env script sets the variables OK ... in the
sub-shell that ran it! When that sub-shell finishes the env script, then
your variables exit with the sub-shell. If this is what you have going on,
then put the variable in the same script as the backup, or dot the env
script.
. env_script
/execute/the/backup
-Original Message-
From: Ron Rogers [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 23, 2002 8:24 AM
To: Multiple recipients of list ORACLE-L
Subject: RMAN and cron
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server.
When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle
variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
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: Stephen Lee
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: RMAN and cron
Ron,
my guess is that your profile file is not being executed by the cron process
properly.
try changing your test.sh script to explicitly point to your profile file.
paths are not set properly by cron because the cron job is executed by a
system account, not the oracle account. secondly, try placing debug
statements (like echo $ORACLE_HOME /alphaprd/rman_test.log
in your test.sh script after you call your profile job to see what is
happening there. I'll bet you a powerball ticket that I'm right.
hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, December 23, 2002 9:24 AM
To: Multiple recipients of list ORACLE-L
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server. When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
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).
Mailing lists for MS Windows NT/2000/XP Administration
Hello, I am looking for mailing lists for MS Windows NT/2000/XP Administration which is similar to ORACLE-L from fatcity.com. Thanks in advance. Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN and cron
Ron
try altering test.sh to read
. /alphaprd/profile [ notice the extra space ]
That should set the environment in your current shell which isn't happenijng
at the moment.
Regards,
Mike Hately
Oracle DBA
-Original Message-
Sent: 23 December 2002 14:24
To: Multiple recipients of list ORACLE-L
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server. When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
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: Hately, Mike (NESL-IT)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Indentifying Redundant Indexes
We assessed this and discarded the option, and now I can't remember why (I'll get back to you after I ask Jeff Holt, who did the study). At best, using stored outlines is a replacement only for steps 2 and 4. The really hard part is step 1. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- [EMAIL PROTECTED] Sent: Monday, December 23, 2002 6:14 AM To: Multiple recipients of list ORACLE-L Cary, I assume that using stored outlines will achieve No 2 in your list. Would that not be an easier approach? If you altered the system to have CREATE_STORED_OUTLINES=true and ran for a period when all scripts are likely to be run, say a month so that all month-end processing was completed, and then check the OUTLN.OL$HINTS.HINT_TEXT column with a query such as the following. 1 select hint_text from outln.ol$hints 2* where substr(hint_text,1,5) = 'INDEX' SQL / HINT_TEXT INDEX(SIMPLE_PK_5M SIMPLE_PK) HTH John -Original Message- Sent: 23 December 2002 05:59 To: Multiple recipients of list ORACLE-L Mark, The way we do it is by what you might call extreme brute force: 1. Capture all SQL from the applications (not just the stuff you've run in the past month, but the stuff you'll run in the future too, like period-end close processes, and so on). 2. Generate execution plans in production for all this SQL. Store the plans. 3. In a structural replica system (that is, a full-schema test instance; you don't need real application data, but you do need schema and db statistics imported from the production system), drop the index. 4. Generate execution plans on the replica system for all the SQL. Store the plans. 5. Compare the two sets of plans from steps 2 and 4. 6. Decide whether the different in 4 that are different from the plans in 2 are better or worse than the plans in 2. Like I said, it's a big hammer method, but it has its reliability advantages, and the only step that we haven't automated is #6. (I'm assuming that you already have a valid test system as described in #3.) The tool we use that does steps 2, 4, and 5 is called Project Laredo (www.hotsos.com/products/laredo). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Richard Sent: Sunday, December 22, 2002 10:54 PM To: Multiple recipients of list ORACLE-L Dear List, Firstly - Merry Christmas to those who have not already departed for the holiday season. I'm currently doing some investigation based around indexes and would like everyone's opinion: What is everyone's preferred approach to identify redundant (as in never used by Oracle) indexes? I believe Oracle 9 might have a feature to set a flag on objects and then check back later to see if they have been accessed however we're still stuck on 8.1.7.4 (Solaris). Some of my thoughts include: * Can query for physical disk i/o at a tablespace level easily, however more difficult to go to an object level. * Could create a trace file and then inspect explain plans for existence of index accesses, however trace file probably not practical to capture for a long period of time. * Ideal statistic would be something along the lines of index x used y times in last 24 hours, however a simple index x was used in the last 24 hours would be ok. Obviously we are searching for indexes to remove and identifying those which aren't queried over a set period of time would be good candidates for a starting point. Any advice you might have would be greatly appreciated. Regards, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: RMAN and cron
Ron,
Try setting your environment variables in your test.sh script. I'm not a unix guru,
so I can't tell you why your profile isn't being used when the script is run from
cron, but my guess is that is what is happening.
Jay
[EMAIL PROTECTED] 12/23/02 09:23AM
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server. When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am running it
as an Oracle cron and the target and catalog are on a different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
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: RMAN and cron
I suppose I should add that you can also have the backup script dot the
env script.
For example:
- backup_script.ksh -
#!/usr/bin/ksh
. /path/to/env_script
etc.
etc.
etc.
---
-Original Message-
From: Stephen Lee [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 23, 2002 8:49 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: RMAN and cron
Are you executing (or running) the env variable script; then
running the
backup script? If so, then the env script sets the variables
OK ... in the
sub-shell that ran it! When that sub-shell finishes the env
script, then
your variables exit with the sub-shell. If this is what you
have going on,
then put the variable in the same script as the backup, or
dot the env
script.
. env_script
/execute/the/backup
-Original Message-
From: Ron Rogers [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 23, 2002 8:24 AM
To: Multiple recipients of list ORACLE-L
Subject: RMAN and cron
list,
Cron server os RedHat 7.2
Database server os OpenVMS 7.3-1
Database Oracle 8.1.7 rel 3
Rman server os OpenVMS 7.3-1
Rman Oracle 8.1.7 rel 3
On the Cron server I have created a script that will backup the
database server and catalog the action on the rman server.
When I try to
have cron perform the backup sctipt I get
RMAN-571 and LEM-00031, and LEM-00033 error message. According to
Metalink it is an environmental error that the oracle
variables are not
set. I have set up my environment as follows:
file: /alphaprd/profile
ORACLE_HOME = cron server ORACLE_HOME
ORACLE_BASE =cron server ORACLE_BASE
LD_LIBRARY_PATH = cron server LD_LIBRARY_PATH
TNS_ADMIN = cron server TNS_ADMIN
export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH TNS_ADMIN
Script to backup the database controlfile: test.sh
./alphaprd/profile
echo start backup: /alphaprd/rman_test.log
/home/oracle/OraHome1/bin/rman EOF /alphaprd/rman_test.log
connect target sys/password@database server
connect catalog rman/rman@rman server
run {
allocate channel di type disk;
backup format
'orabck:[backups]control_%U.bus'
(current controlfile);
}
EOF
Crontab entry as ROOT:
10 01 * * 1 su oracle -c /alphaprd/test.sh -u
OR
Crontab entry as ORACLE
10 01 * * 1 /alphaprd/test.sh
The script functions properly if I run it from the command line as
oracle but fails with
RMAN-571 and LEM-00031 and LEM-00033 errors when run from cron.
I don't see how it could be Oracle environmentals when I am
running it
as an Oracle cron and the target and catalog are on a
different server
that the cron server.
Can you point me in the direction where I can get this resovled?
Thanks,
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
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: Stephen Lee
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: Stephen Lee
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: Java to Database Basics
First do you know how to program? Really program; not talk about how to program but actually type in code and get what you want. If you do then learning 85% of java or any language won't take too long. I sugest you download some JDBC examples and try them out. Change stuff then try again. On Mon, 23 Dec 2002, VIVEK_SHARMA wrote: More More people are Accessing the Oracle Database Thru Java Calls , programs etc. What basics , related Database performance issues etc. are part of this Java game with respect to the Database ? How can a structured learning be done on this ? Total Novice on Java here though familiar with Oracle Databases (DBA) -- 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: Alex 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).
Right()
Please don't laugh. What is the equivalent of a Right() function in Oracle? I want to be able to sort a column numerically whose string contents takes the format v1, v2, v3, v4. I was after something like: ORDER BY Cast(Right(lt_tk_id, Length(lt_tk_id - 1)) as int) but it's not playing nice. Order by Right(lt_tk_id, 1) gives the same error, so I assume that's where it's falling down. I've searched everywhere I can think of for right function, Oracle online docs, Enterprise Manager docs, Google, but nothing seems to come close. BTW, is it my imagination or do the docs leave a lot to be desired? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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: Mailing lists for MS Windows NT/2000/XP Administration
http://peach.ease.lsoft.com/archives/winnt-l.html Hello, I am looking for mailing lists for MS Windows NT/2000/XP Administration which is similar to ORACLE-L from fatcity.com. Thanks in advance. Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky 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: Happy Holidays!!
Ruth G is definitely not a male Guy H? who was a contributor on the Oracle_l list used to sign as Guy Ruth H? is think. I don't remember his last name, but I think it began with H. Ruth g - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:38 PM I have never met RUTH and I apologize in advance if I am wrong but I thought I remember a post long ago indicating Ruth G is male? Again a big oops if incorrect. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc: ahoo.comSubject: RE: Happy Holidays!! Sent by: [EMAIL PROTECTED] om 12/20/2002 02:28 PM Please respond to ORACLE-L from this list: Ruth Gramolini and April Wells are going, they are presenting. I don't know if I am presenting or not, I was accepted as an alternate and today is the cutoff for the speakers to confirm they are going. But I'll be there whether or not I present --- [EMAIL PROTECTED] wrote: No offense guys, but having only small boy children (whom I love dearly), working in a 90% male IT support organization (cause I like men) - can't help feeling like what I really need now is some good woman friends - preferably ones that both work and are raising children. Are any of them going to IOUG? -Original Message- Sent: Friday, December 20, 2002 11:40 AM To: Multiple recipients of list ORACLE-L from what Jared tells me, in the first 50 names on the list (alphabetical?) he found 11 out of 50 names that were obviously female first names. not the best statistic (1/5 of an admittedly small sample). but better than it was a few years ago. What I really like seeing is that more and more women are answering questions here as well --- Lisa Corell Auerbach [EMAIL PROTECTED] wrote: Hi Rachel - you wrote - I'm more interested in the numbers to see if this field is less male-centric than others. I don't post very often, but I'm another in the orawoman category. Lisa (now the DBA at Henrico County Public Schools) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lisa Corell Auerbach 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). __ 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing
RE: Java to Database Basics
Title: RE: Java to Database Basics From: Alex [mailto:[EMAIL PROTECTED]] First do you know how to program? Really program; not talk about how to program but actually type in code and get what you want. If you do then learning 85% of java or any language won't take too long. I sugest you download some JDBC examples and try them out. Change stuff then try again. ... and when you get done with that, start futzing around with pooled connections on Weblogic or Websphere.
Fwd: Oracle Security Product Management Issues Security Alert
list, Received this today. Ron [EMAIL PROTECTED] 12/21/02 06:08PM Oracle Security Product Management Issues Security Alert #47 This e-mail contains a critical, technical alert which is being sent as a service to all Oracle MetaLink users. Oracle Security Product Management team has issued the following alert on MetaLink: Oracle Security Alert #47: Security Vulnerabilities in Oracle9i Application Server Doc ID:224215.1 You may view this article by logging on MetaLink at http://metalink.oracle.com and clicking on the Security Alert document #47 in the News Notes section on the My Headlines page. Thank you for using MetaLink. Oracle Support Services -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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: Row cache locks on INSERTs with a sequence
Just as a side-line observation - when I realised that the problem should have been with the sequence, I set up a small test on a multi-CPU box to run multiple concurrent copies of: begin for i in 1..10 loop insert into t1 values (test_seq.nextval); end loop; end; I couldn't get a single row cache lock wait. This was using 8.1.7.4 on HPUX 11. So I wonder if the waits you were seeing were a side-effect of another issue, or highly version dependent. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 23 December 2002 14:40 If I stated dc_segments in my original post, I apologize, I *did* mean to say dc_sequences. At any rate, as usual, the problem was poor application code. The row cache lock no longer shows up as one of the top 5 wait events per statspack. And of course, the programmers never apologized, or even deigned to acknowledge appreciation in improving their application, they are simply acting as offended cats will do, as though the DBA team doesn't exist. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Right()
is this what you're after? Steve tsoadm(14)@SMP l 1 select substr ( 'left or right', -5) right 2 , substr ( 'left or right', 1, 4) left 3* from dual tsoadm(14)@SMP / RIGHT LEFT right left 1 row selected. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 23, 2002 9:49 AM Please don't laugh. What is the equivalent of a Right() function in Oracle? I want to be able to sort a column numerically whose string contents takes the format v1, v2, v3, v4. I was after something like: ORDER BY Cast(Right(lt_tk_id, Length(lt_tk_id - 1)) as int) but it's not playing nice. Order by Right(lt_tk_id, 1) gives the same error, so I assume that's where it's falling down. I've searched everywhere I can think of for right function, Oracle online docs, Enterprise Manager docs, Google, but nothing seems to come close. BTW, is it my imagination or do the docs leave a lot to be desired? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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: Steve Perry 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: db block Size for Indexes Tablespaces in 9.2 ?
Sorry, It was a rhetorical question. Detailed results come under the heading of company confidential - generic results come under the heading of repetition. You just have to list the set of point (see my earlier point) about why smaller or larger blocks MIGHT make a difference that you could expect to notice, then figure out if any of those points are relevant to your system, then devise a realistic test to find out if any hypothetical benefit turns into a real benefit. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 23 December 2002 10:24 Hi Jonathan Would like to have Tests done for BOTH Small Big DB_BLOCK_SIZE , if possible , as mentioned below Thanks -Original Message- Sent: Wednesday, December 18, 2002 4:50 PM To: Multiple recipients of list ORACLE-L I think there are too many generic arguments available for picking the 'right' block size for your indexes. The one that is most appropriate is likely to depend on the nature of the activity (load vs. query), nature of the index (unique, nearly unique, far from unique), data clustering, order of data arrival, frequency of data arrival, pattern of data deletion/update, stability of volume, nature of queries (big or small range scans), potential of modifying number of branches, buffering effects, and whether or not you are using a filesystem with or without direct i/o. Given another 10 minutes I might come up with a few more ideas. Your strategy should be to identify the extreme, and critical, characteristics of your system and play to them - small block size may be appropriate, reverse indexes may be appropriate, getting rid of the synthetic key that is likely to cause a problem may be appropriate. But don't assume that anything as trivial as tweaking a block size is a driving feature of making your index work well. Which test case would you like to see - the one I did for company X that showed they needed a small block size, or the one I did for company Y that showed they needed a large block size ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Happy Holidays!!
Come-on Ruth, you had the operation. we know it. you escaped to the dark side. -Original Message- Sent: Monday, December 23, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Ruth G is definitely not a male Guy H? who was a contributor on the Oracle_l list used to sign as Guy Ruth H? is think. I don't remember his last name, but I think it began with H. Ruth g - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:38 PM I have never met RUTH and I apologize in advance if I am wrong but I thought I remember a post long ago indicating Ruth G is male? Again a big oops if incorrect. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc: ahoo.comSubject: RE: Happy Holidays!! Sent by: [EMAIL PROTECTED] om 12/20/2002 02:28 PM Please respond to ORACLE-L from this list: Ruth Gramolini and April Wells are going, they are presenting. I don't know if I am presenting or not, I was accepted as an alternate and today is the cutoff for the speakers to confirm they are going. But I'll be there whether or not I present --- [EMAIL PROTECTED] wrote: No offense guys, but having only small boy children (whom I love dearly), working in a 90% male IT support organization (cause I like men) - can't help feeling like what I really need now is some good woman friends - preferably ones that both work and are raising children. Are any of them going to IOUG? -Original Message- Sent: Friday, December 20, 2002 11:40 AM To: Multiple recipients of list ORACLE-L from what Jared tells me, in the first 50 names on the list (alphabetical?) he found 11 out of 50 names that were obviously female first names. not the best statistic (1/5 of an admittedly small sample). but better than it was a few years ago. What I really like seeing is that more and more women are answering questions here as well --- Lisa Corell Auerbach [EMAIL PROTECTED] wrote: Hi Rachel - you wrote - I'm more interested in the numbers to see if this field is less male-centric than others. I don't post very often, but I'm another in the orawoman category. Lisa (now the DBA at Henrico County Public Schools) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lisa Corell Auerbach 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). __ 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
Re: Indentifying Redundant Indexes
Step 6 should be fun - It is possible to come up with code where plan A is better than plan B in low concurrency systems, but plan B is better than plan A in high concurrency systems. Similarly, plan X is better than plan Y if no updates are taking place concurrently, and plan Y is better than plan X is you have to compete with updates and the consequent read-consistent costs. There's also the issue of row-level-security (rls / vpd / fgac) which may increase the scale of the investigation quite significantly. It may also have been the problem point that Jeff came up with - outlines store only one plan, but the same (or apparently identical) piece of SQL could have conflicting plans because of a hidden rls predicate. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 23 December 2002 16:31 We assessed this and discarded the option, and now I can't remember why (I'll get back to you after I ask Jeff Holt, who did the study). At best, using stored outlines is a replacement only for steps 2 and 4. The really hard part is step 1. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Right()
SUBSTR(char,m[,n]) A subtring of char beginning at byte m, n bytes long (if n omitted m to end of char) i.e. If a = 'ABCDEFGHI' substr(a,7,3) = 'GHI' substr(a,7) = 'GHI' -Original Message- Sent: Monday, December 23, 2002 10:49 AM To: Multiple recipients of list ORACLE-L Please don't laugh. What is the equivalent of a Right() function in Oracle? I want to be able to sort a column numerically whose string contents takes the format v1, v2, v3, v4. I was after something like: ORDER BY Cast(Right(lt_tk_id, Length(lt_tk_id - 1)) as int) but it's not playing nice. Order by Right(lt_tk_id, 1) gives the same error, so I assume that's where it's falling down. I've searched everywhere I can think of for right function, Oracle online docs, Enterprise Manager docs, Google, but nothing seems to come close. BTW, is it my imagination or do the docs leave a lot to be desired? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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: Wolfgang Bogacz 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:Right ()
Laugh at you? Never, well once in a while. As for your problem, first thing I'd like to know is the version of Oracle your using. 9i I know has added several functions that do not exist earlier. Since 8.1.7.4 in my latest instance the 'right' function does not exist, so I'd suggest using substr with possibly a to_number. Something like: order by to_number(substr(lt_tk_id,length(lt_tk_id)-1)) Dick Goulet Reply Separator Author: Aidan Whitehall [EMAIL PROTECTED] Date: 12/23/2002 7:49 AM Please don't laugh. What is the equivalent of a Right() function in Oracle? I want to be able to sort a column numerically whose string contents takes the format v1, v2, v3, v4. I was after something like: ORDER BY Cast(Right(lt_tk_id, Length(lt_tk_id - 1)) as int) but it's not playing nice. Order by Right(lt_tk_id, 1) gives the same error, so I assume that's where it's falling down. I've searched everywhere I can think of for right function, Oracle online docs, Enterprise Manager docs, Google, but nothing seems to come close. BTW, is it my imagination or do the docs leave a lot to be desired? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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).
RE: Right()
The equivalent will be substr in one of it's many forms SUBSTR(char, m [, n]) Returns a portion of Char, beginning at character M, N characters long. If M is positive, Oracle counts from the beginning of Char to find the first character. if M is negative, Oracle counts backwards from the end of Char. If N is omitted, Oracle returns all characters to the end of Char. -Original Message- Sent: 23 December 2002 15:49 To: Multiple recipients of list ORACLE-L Please don't laugh. What is the equivalent of a Right() function in Oracle? I want to be able to sort a column numerically whose string contents takes the format v1, v2, v3, v4. I was after something like: ORDER BY Cast(Right(lt_tk_id, Length(lt_tk_id - 1)) as int) but it's not playing nice. Order by Right(lt_tk_id, 1) gives the same error, so I assume that's where it's falling down. I've searched everywhere I can think of for right function, Oracle online docs, Enterprise Manager docs, Google, but nothing seems to come close. BTW, is it my imagination or do the docs leave a lot to be desired? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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: Nicoll, Iain 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: Happy Holidays!!
Guy Ruth Hammond. who as it turns out is a friend of someone I now work with here, so I do get to hear of him on occasion. I miss his recipes! --- Ruth Gramolini [EMAIL PROTECTED] wrote: Ruth G is definitely not a male Guy H? who was a contributor on the Oracle_l list used to sign as Guy Ruth H? is think. I don't remember his last name, but I think it began with H. Ruth g - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:38 PM I have never met RUTH and I apologize in advance if I am wrong but I thought I remember a post long ago indicating Ruth G is male? Again a big oops if incorrect. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc: ahoo.comSubject: RE: Happy Holidays!! Sent by: [EMAIL PROTECTED] om 12/20/2002 02:28 PM Please respond to ORACLE-L from this list: Ruth Gramolini and April Wells are going, they are presenting. I don't know if I am presenting or not, I was accepted as an alternate and today is the cutoff for the speakers to confirm they are going. But I'll be there whether or not I present --- [EMAIL PROTECTED] wrote: No offense guys, but having only small boy children (whom I love dearly), working in a 90% male IT support organization (cause I like men) - can't help feeling like what I really need now is some good woman friends - preferably ones that both work and are raising children. Are any of them going to IOUG? -Original Message- Sent: Friday, December 20, 2002 11:40 AM To: Multiple recipients of list ORACLE-L from what Jared tells me, in the first 50 names on the list (alphabetical?) he found 11 out of 50 names that were obviously female first names. not the best statistic (1/5 of an admittedly small sample). but better than it was a few years ago. What I really like seeing is that more and more women are answering questions here as well --- Lisa Corell Auerbach [EMAIL PROTECTED] wrote: Hi Rachel - you wrote - I'm more interested in the numbers to see if this field is less male-centric than others. I don't post very often, but I'm another in the orawoman category. Lisa (now the DBA at Henrico County Public Schools) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lisa Corell Auerbach 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). __ 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
RE: Right ()
order by to_number(substr(lt_tk_id,length(lt_tk_id)-1)) Brilliant. Thanks to everyone that mentioned substr. -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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:RE: Right ()
OK, NOW can we laugh with you!! Dick Goulet Reply Separator Author: Aidan Whitehall [EMAIL PROTECTED] Date: 12/23/2002 9:44 AM order by to_number(substr(lt_tk_id,length(lt_tk_id)-1)) Brilliant. Thanks to everyone that mentioned substr. -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall 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).
1998 Openworld CD
I believe 1998 was the last year they provided CDs with papers and presentations. Does anyone still have the 1998 CD and is willing to share it? Please mail me directly off list: [EMAIL PROTECTED] Thanks, -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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: Row cache locks on INSERTs with a sequence
Title: RE: Row cache locks on INSERTs with a sequence Jonathan, The inserts were into a staging table. After the staging table was loaded into the live tables, they were using delete and not truncate to flush the staging table. So of course the table and index were acquiring an excessive # of extents over time. More than likely the excessive extents were the real problem, and not the sequence per se, the sequence was just a victim. From what I read on Metalink, the shared pool could be an issue with the row cache locks, but my thinking was that if so, there should be other symptoms pointing to a need to increase the size of the shared pool, and we weren't seeing them (or at least that's what we thought). I had them fix the code by removing the sequence (it was not needed as the column using the sequence was some kind of internal counter only needed for the duration of the load process and not part of any primary key) and to perform a truncate instead of a delete. BTW, The PL/SQL routine performing the inserts is, according to Tim Gorman's TOP script, one of the top two heaviest resource abusers. Per my statspack reports, the row cache lock is no longer one of the top 5 wait events. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row cache locks on INSERTs with a sequence Just as a side-line observation - when I realised that the problem should have been with the sequence, I set up a small test on a multi-CPU box to run multiple concurrent copies of: begin for i in 1..10 loop insert into t1 values (test_seq.nextval); end loop; end; I couldn't get a single row cache lock wait. This was using 8.1.7.4 on HPUX 11. So I wonder if the waits you were seeing were a side-effect of another issue, or highly version dependent. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 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: 23 December 2002 14:40 If I stated dc_segments in my original post, I apologize, I *did* mean to say dc_sequences. At any rate, as usual, the problem was poor application code. The row cache lock no longer shows up as one of the top 5 wait events per statspack. And of course, the programmers never apologized, or even deigned to acknowledge appreciation in improving their application, they are simply acting as offended cats will do, as though the DBA team doesn't exist. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
FW: Identifying Redundant Indexes
Ah, yes, Jeff has restored my memory (below). If your application does not use bind variables, then you have many more distinct SQL statements than you actually *should* have. ...Which is another reason that collecting the SQL is such an important step. If you do it with too simple of a matching algorithm, then you're accidentally giving yourself (and your system) way too much work. For example, SELECT ID FROM T WHERE LNAME='Smith' and SELECT ID FROM T WHERE LNAME='Jones' should really be regarded as one distinct statement. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Sent: Monday, December 23, 2002 1:06 PM To: 'Cary Millsap' That approach would work as long as you can predict the number of distinct statements over the observation interval. If you can predict the number of distinct statements, then you can attempt to size the tablespace. If you cannot predict the volume, then you risk filling the tablespace where the outlines are stored. The most common cause for not being able to predict is an application's lack of use of bind variables in its statements. I haven't tested the scenario of create_stored_outlines=true and the outline cannot be stored because of some error such as 'table cannot be extended'. I would hope that it would ignore the insert error but without a test it would be somewhat risky. Especially since the creations would be most likely be executed on a production instance. Jeff Holt - An O2 bottle, an O2 bottle! My password for an O2 bottle! Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events at http://www.hotsos.com/events/index.html -Original Message- Sent: Mon, Dec 23, 2002 09:40 To: [EMAIL PROTECTED] We assessed this and discarded the option, and now I can't remember why (I'll get back to you after I ask Jeff Holt, who did the study). At best, using stored outlines is a replacement only for steps 2 and 4. The really hard part is step 1. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- [EMAIL PROTECTED] Sent: Monday, December 23, 2002 6:14 AM To: Multiple recipients of list ORACLE-L Cary, I assume that using stored outlines will achieve No 2 in your list. Would that not be an easier approach? If you altered the system to have CREATE_STORED_OUTLINES=true and ran for a period when all scripts are likely to be run, say a month so that all month-end processing was completed, and then check the OUTLN.OL$HINTS.HINT_TEXT column with a query such as the following. 1 select hint_text from outln.ol$hints 2* where substr(hint_text,1,5) = 'INDEX' SQL / HINT_TEXT INDEX(SIMPLE_PK_5M SIMPLE_PK) HTH John -Original Message- Sent: 23 December 2002 05:59 To: Multiple recipients of list ORACLE-L Mark, The way we do it is by what you might call extreme brute force: 1. Capture all SQL from the applications (not just the stuff you've run in the past month, but the stuff you'll run in the future too, like period-end close processes, and so on). 2. Generate execution plans in production for all this SQL. Store the plans. 3. In a structural replica system (that is, a full-schema test instance; you don't need real application data, but you do need schema and db statistics imported from the production system), drop the index. 4. Generate execution plans on the replica system for all the SQL. Store the plans. 5. Compare the two sets of plans from steps 2 and 4. 6. Decide whether the different in 4 that are different from the plans in 2 are better or worse than the plans in 2. Like I said, it's a big hammer method, but it has its reliability advantages, and the only step that we haven't automated is #6. (I'm assuming that you already have a valid test system as described in #3.) The tool we use that does steps 2, 4, and 5 is called Project Laredo (www.hotsos.com/products/laredo). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Richard Sent: Sunday, December 22, 2002 10:54 PM To: Multiple recipients of list ORACLE-L Dear List, Firstly - Merry Christmas to those who have not already departed for the holiday season. I'm currently doing some investigation based around indexes and would like everyone's opinion: What is everyone's preferred approach to identify redundant (as in never used by Oracle) indexes? I believe Oracle 9 might have a feature to set a flag on objects and then check
RE: Happy Holidays!!
Yeah - I remember that Guy used to churn out _very_ yummy (and chocalately) recipes once a week. So where is he now Rachel? John -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: Happy Holidays!! Guy Ruth Hammond. who as it turns out is a friend of someone I now work with here, so I do get to hear of him on occasion. I miss his recipes! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Object relational features and performance
Dennis - 1+2;) The current project will be build with the classic relational data modell. On top of it sits a OR-Mapper (not a full-blown-vapor-ware-application server though ;) and the software will be developed in java. The software development team is VERY experienced with object oriented software development (smalltalk, c++, java, you name it, they've done it). I thought about going with objects (in the database that is) in the beginning but I didn't do it. The main reason for not going with that approach is the fact that things might get mixed up. For one thing, I don't really get stuff like inheritance or interfaces, which are quite important. On the other hand, the business logic would be found partially in the database and partially in the application server. So what would be left except for the encapsulation and abstraction ? Afterall, we used an abstraction layer which is the or-mapper. Right now, I'm just curious if anybody has experience with object relational oracle features and wether it might be a recommandable approach or not. In theory and on my little playground aka laptop it always looks good, but in real life, ... I don't know. Regards, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 5:39 PM Stefan - I believe the general consensus had emerged that usually object features aren't worth the effort. Often there are few benefits, and if you don't do it correctly you may see bad performance. Two questions: 1. Are your developers/management enamored with the concept of object, or is this just your own curiosity? 2. Is there something about your application that leads you to believe that it might derive significant benefit from the object features? For general business applications it is hard to beat the flexibility of the good old traditional relational data modeling. The lack of discussion may provide part of the answer to your question. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 6:35 AM To: Multiple recipients of list ORACLE-L Hi everybody I'm not quite sure wether this has been discussed in deep before, but I couldn't find anything satisfieing (hope the spelling is correct ;)) things in the archive. Anyway: Due to my lack of experience with any real life scenarios with Oracle's object relational features, I never tried to recommend the usage of these and always kept to a normal relational approach. Does anybody have any experience with Types / Nested Tables and the like in a (preferrably big) production system of any kind ? What's recommendable, where are the pitfalls ? Any input deeply appreciated, TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke 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: DENNIS WILLIAMS 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: Stefan Jahnke 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: Object relational features and performance
Hi I hope not, but I'm having more and more difficulties to find valid points for a good old database centric approach except for data integrity ... which, after looking at how a lot of people act, might not be so important after all ;) Regards, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 6:04 PM Is this the future ??? I know one big bank where the development is object oriented and the database (DB2 UDB in this case) is used as a big flat file. The development is using java, j2ee, bea weblogic. --- DENNIS WILLIAMS [EMAIL PROTECTED] a écrit : Stefan - I believe the general consensus had emerged that usually object features aren't worth the effort. Often there are few benefits, and if you don't do it correctly you may see bad performance. Two questions: 1. Are your developers/management enamored with the concept of object, or is this just your own curiosity? 2. Is there something about your application that leads you to believe that it might derive significant benefit from the object features? For general business applications it is hard to beat the flexibility of the good old traditional relational data modeling. The lack of discussion may provide part of the answer to your question. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 6:35 AM To: Multiple recipients of list ORACLE-L Hi everybody I'm not quite sure wether this has been discussed in deep before, but I couldn't find anything satisfieing (hope the spelling is correct ;)) things in the archive. Anyway: Due to my lack of experience with any real life scenarios with Oracle's object relational features, I never tried to recommend the usage of these and always kept to a normal relational approach. Does anybody have any experience with Types / Nested Tables and the like in a (preferrably big) production system of any kind ? What's recommendable, where are the pitfalls ? Any input deeply appreciated, TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke 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: DENNIS WILLIAMS 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Stefan Jahnke 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
RE: Java to Database Basics
and when you're done with that, start improving the JBoss code. and when you're done with that, have yourself shipped off to the asylum ;). -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/23/2002 5:38 PM From: Alex [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] First do you know how to program? Really program; not talk about how to program but actually type in code and get what you want. If you do then learning 85% of java or any language won't take too long. I sugest you download some JDBC examples and try them out. Change stuff then try again. ... and when you get done with that, start futzing around with pooled connections on Weblogic or Websphere. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke 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: Out of topic -Unx question
Title: RE: Out of topic -Unx question The cheap easy way Is to have a cron job that runs at midnight and echo's today's date to a file called today.date But before that it copies that file to yesterday.date Then all you have to do is cat yesterday.date to get the date. It may not be the prettiest way but it works. -Original Message- From: Richard Ji [mailto:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 1:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Out of topic -Unx question Search the archive of this list. This has been discussed before. You can use a trick such as this: yesterday=$(TZ=EST26EDT date +%a) but beaware that this is not a portable solution. You can write your own script. E-mail me off line and I can send you mine yesterday script. -Original Message- Sent: Monday, December 23, 2002 12:09 AM To: Multiple recipients of list ORACLE-L Not easily done in Unix. However, if you've got an Oracle db running on the same server you might want to get yesterday's date from Oracle (within a shell script). ... ... YESTERDAY=`sqlplus -s / -EOF set head off feed off pages 0 select trunc(sysdate - 1) from dual; exit EOF` echo YESTERDAY is $YESTERDAY ... ... hth, Ross -Original Message- Sent: Saturday, 21 December 2002 7:51 To: Multiple recipients of list ORACLE-L Hi All, I want to get previous date from today's date.Is there any cmd for this. Thanks Manoj -- 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: Ross Collado 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: Richard Ji 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: Object relational features and performance
Hi Glenn Now that's some interesting information. It looks like a lean-mixture (isn't that what we all run on ?;), respectively sql/xml, xml-db is the way oracle is headed at. wasn't there an xml-db feature in the latest oracle mag issue ? looks like I have to take a look at it. happy holidays, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 7:14 PM You have a need for them? Or just want to try them out? Oracle pretty much admits (at least according to the presenter at a conference I attended) that few customers use this facility. However, the object relational stuff is a core componet of things like XML DB. Glenn On Fri, 2002-12-20 at 07:34, Stefan Jahnke wrote: Hi everybody I'm not quite sure wether this has been discussed in deep before, but I couldn't find anything satisfieing (hope the spelling is correct ;)) things in the archive. Anyway: Due to my lack of experience with any real life scenarios with Oracle's object relational features, I never tried to recommend the usage of these and always kept to a normal relational approach. Does anybody have any experience with Types / Nested Tables and the like in a (preferrably big) production system of any kind ? What's recommendable, where are the pitfalls ? Any input deeply appreciated, TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Glenn Stauffer [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer 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: Stefan Jahnke 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: Object relational features and performance
Phew, finally I found something meaningful to do over the holidays. And I was already afraid that I might have to spend time with family ;). No seriously, thanks, I also think I have a copy of his book at my office, so I'll start there. happy holidays, Stefan -Original Message- To: [EMAIL PROTECTED] Cc: Stefan Jahnke Sent: 12/20/2002 8:11 PM One-on-One Oracle Expert by Tom Kyte contains some stuff that you will find useful. Head on over to asktom.oracle.com, and search his site. You won't be dissapointed. Please let us know if you find some interesting application. Jared Stefan Jahnke [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/20/2002 04:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Object relational features and performance Hi everybody I'm not quite sure wether this has been discussed in deep before, but I couldn't find anything satisfieing (hope the spelling is correct ;)) things in the archive. Anyway: Due to my lack of experience with any real life scenarios with Oracle's object relational features, I never tried to recommend the usage of these and always kept to a normal relational approach. Does anybody have any experience with Types / Nested Tables and the like in a (preferrably big) production system of any kind ? What's recommendable, where are the pitfalls ? Any input deeply appreciated, TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke 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: Stefan Jahnke 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: Object relational features and performance
Here is a book I put beneath my pillow: Prentice Hall, SUN Series, Core J2EE Patterns (esp. from page 388 on: Integration tier patterns) by Alur/Crupi/Malks, ISBN-0-13-064884-1 Also alot of specs and papers from the sun java site, like EJB, JTA/JTS (Transactions), JMS (Messaging), JDO (Java Data Objects), JDBC and everything that looks like it might have anything to do with data access, which is most of the J2EE stuff. Enjoy your holidays, though ;). Regards, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 8:38 PM Jeremy - Our developers have received a lot of Java training, and if I understand what you are saying, we are planning to do exactly what you describe -- normalized data structures in Oracle and an abstraction layer for the Java programmers. Would you mind to send a UML diagram that describes this EB + session facade pattern? Are you doing the entire abstraction on the Java side, or are you calling Oracle stored procedures? On a more general note to everyone, does anyone know of a book that would be helpful for an Oracle DBA that is trying to master what is needed to support Java programmers or make decisions in the area of Java and Oracle? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 12:25 PM To: Multiple recipients of list ORACLE-L From: Stephane Paquette [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Is this the future ??? I know one big bank where the development is object oriented and the database (DB2 UDB in this case) is used as a big flat file. The development is using java, j2ee, bea weblogic. Here's another thought. Take a strong look at the J2EE architecture. The concept of Entity Beans + Session Facade pattern is a strong means of maintaining the 2 important concepts of OO and relational data. For quite a while I worked to develop a strong abstraction layer to maintain normalized data in the db, but give the Java developers a true OO API. Now with Entity Beans and intelligent design elements I've got the best of both worlds. Transactional data is most effeciently stored in most cases in normalized form. The issue is to not force OO developers to make the leap in their code. Entity Beans are not strictly OO (since you must reference them by a PK), but are close enough to meet the needs of at least 90% of the enterprise development projects, IMHO. If the data access is minimal, I suppose the above solution would be fine. I'd hate to try to roll out an app with a significant amount of transactions with that structure, though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Stefan Jahnke 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: Object relational features and performance
Forgot something: For a general understanding, I like the books by Peter Coad and Bruce Eckel. These guys really know how to explain stuff and how to think. regards, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 8:38 PM Jeremy - Our developers have received a lot of Java training, and if I understand what you are saying, we are planning to do exactly what you describe -- normalized data structures in Oracle and an abstraction layer for the Java programmers. Would you mind to send a UML diagram that describes this EB + session facade pattern? Are you doing the entire abstraction on the Java side, or are you calling Oracle stored procedures? On a more general note to everyone, does anyone know of a book that would be helpful for an Oracle DBA that is trying to master what is needed to support Java programmers or make decisions in the area of Java and Oracle? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 12:25 PM To: Multiple recipients of list ORACLE-L From: Stephane Paquette [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Is this the future ??? I know one big bank where the development is object oriented and the database (DB2 UDB in this case) is used as a big flat file. The development is using java, j2ee, bea weblogic. Here's another thought. Take a strong look at the J2EE architecture. The concept of Entity Beans + Session Facade pattern is a strong means of maintaining the 2 important concepts of OO and relational data. For quite a while I worked to develop a strong abstraction layer to maintain normalized data in the db, but give the Java developers a true OO API. Now with Entity Beans and intelligent design elements I've got the best of both worlds. Transactional data is most effeciently stored in most cases in normalized form. The issue is to not force OO developers to make the leap in their code. Entity Beans are not strictly OO (since you must reference them by a PK), but are close enough to meet the needs of at least 90% of the enterprise development projects, IMHO. If the data access is minimal, I suppose the above solution would be fine. I'd hate to try to roll out an app with a significant amount of transactions with that structure, though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Stefan Jahnke 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).
1998 IOUG or Openworld session listings/programs
If anyone has a conference program or other documentation with session listings from 1998, could you please contact me off the list. Really interested in the who's who of Oracle 7.3 tuning! Thanks! -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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: Object relational features and performance
On the other hand, the business logic would be found partially in the database and partially in the application server. This sounds dangerous and non-performant/scalable. Dangerous because data could easily be changed outside of the application and be unaffected by your carefully crafted business rules. Non-performant/scalable because the data has to be outside the database to be verified by the business rules. Jared Stefan Jahnke [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/23/2002 01:34 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Object relational features and performance Dennis - 1+2;) The current project will be build with the classic relational data modell. On top of it sits a OR-Mapper (not a full-blown-vapor-ware-application server though ;) and the software will be developed in java. The software development team is VERY experienced with object oriented software development (smalltalk, c++, java, you name it, they've done it). I thought about going with objects (in the database that is) in the beginning but I didn't do it. The main reason for not going with that approach is the fact that things might get mixed up. For one thing, I don't really get stuff like inheritance or interfaces, which are quite important. On the other hand, the business logic would be found partially in the database and partially in the application server. So what would be left except for the encapsulation and abstraction ? Afterall, we used an abstraction layer which is the or-mapper. Right now, I'm just curious if anybody has experience with object relational oracle features and wether it might be a recommandable approach or not. In theory and on my little playground aka laptop it always looks good, but in real life, ... I don't know. Regards, Stefan -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/2002 5:39 PM Stefan - I believe the general consensus had emerged that usually object features aren't worth the effort. Often there are few benefits, and if you don't do it correctly you may see bad performance. Two questions: 1. Are your developers/management enamored with the concept of object, or is this just your own curiosity? 2. Is there something about your application that leads you to believe that it might derive significant benefit from the object features? For general business applications it is hard to beat the flexibility of the good old traditional relational data modeling. The lack of discussion may provide part of the answer to your question. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 20, 2002 6:35 AM To: Multiple recipients of list ORACLE-L Hi everybody I'm not quite sure wether this has been discussed in deep before, but I couldn't find anything satisfieing (hope the spelling is correct ;)) things in the archive. Anyway: Due to my lack of experience with any real life scenarios with Oracle's object relational features, I never tried to recommend the usage of these and always kept to a normal relational approach. Does anybody have any experience with Types / Nested Tables and the like in a (preferrably big) production system of any kind ? What's recommendable, where are the pitfalls ? Any input deeply appreciated, TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke 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: DENNIS WILLIAMS 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: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: Happy Holidays!!
he's still in England, just busy and no time for the list these days --- John Kanagaraj [EMAIL PROTECTED] wrote: Yeah - I remember that Guy used to churn out _very_ yummy (and chocalately) recipes once a week. So where is he now Rachel? John -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: Happy Holidays!! Guy Ruth Hammond. who as it turns out is a friend of someone I now work with here, so I do get to hear of him on occasion. I miss his recipes! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
Re: Out of topic -Unx question
my unix sysadmin was kind enough to give me following few monts ago. Works great and can be used for creating any date in dd-mon-yy format DAY=`perl -e 'use POSIX qw(strftime); print strftime(%d-%b-%y, localtime(time() - 86400)) . \n ;'` HTH Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 12:50 PM Hi All, I want to get previous date from today's date.Is there any cmd for this. Thanks Manoj -- 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: Shaleen 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: echo `date`
Thank you... --- Thomas, Kevin [EMAIL PROTECTED] wrote: eh? Try the key next to '1' (to the left)...all depends on how your keyboard is setup, nationality (of the os, not you :O). -Original Message- Sent: 23 December 2002 06:44 To: Multiple recipients of list ORACLE-L Dear List, I have Korn shell Script that show the date of execution. echo `date` - Start shutdown of oracle DB. Could any body tell me how to display this char ` the quotation for date. I mean which key I have to press in Unix to get this quotation character. I'm working with Unixware7.1 Regards, Ashraf __ 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: Ashraf Salaymeh 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: Thomas, Kevin 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: Ashraf Salaymeh 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: Happy Holidays!!
Title: RE: Happy Holidays!! Indeed is Vey mutual . Like Fems too (more the merrier) . ha ha -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Saturday, December 21, 2002 12:16 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Happy Holidays!! No offense guys, but having only small boy children (whom I love dearly), working in a 90% male IT support organization (cause I like men) - can't help feeling like what I really need now is some good woman friends - preferably ones that both work and are raising children. Are any of them going to IOUG? -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 11:40 AM To: Multiple recipients of list ORACLE-L Subject: Re: Happy Holidays!! from what Jared tells me, in the first 50 names on the list (alphabetical?) he found 11 out of 50 names that were obviously female first names. not the best statistic (1/5 of an admittedly small sample). but better than it was a few years ago. What I really like seeing is that more and more women are answering questions here as well --- Lisa Corell Auerbach [EMAIL PROTECTED] wrote: Hi Rachel - you wrote -I'm more interested in the numbers to see if this field is less male-centric than others. I don't post very often, but I'm another in the orawoman category. Lisa (now the DBA at Henrico County Public Schools) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lisa Corell Auerbach 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).
Migrate table data to CSV file - Urgent help !!!
Guys,
i want to export table data into a CSV file.
i am using the script below to do the same.
--
set wrap off
set linesize 2000
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE 1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY ','
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
fromuser_tab_columns
where table_name = upper('1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(44)||'
fromuser_tab_columns
where table_name = upper('1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('1'))
order by column_id
/
select lower(column_name)
fromuser_tab_columns
where table_name = upper('1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('1'))
order by column_id
/
prompt from1
prompt /
spool off
set termout on
@ytmpy.sql
exit
-
things work fine.
for example , i have a record as below (fields seperated by , ) :
AAA,B,C,D ,,GGG
the at the end of D is new line character .hope so.
when this record gets written to CSV file , it is like this :
AAA,B,C,D
you can see the data after is truncated.
and the records after this without are written properly.
problem occurs when there is in a record.
how to get rid of this ?
kindly help me plzzz. This is quite urgent.
TIA.
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: oraora oraora
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Oracle Application Server Installation
Hi Listers, Could someone give me simple explaination how to installed 'Oracle Application Server', I have Oracle 9.i database on my server and I wonder why it always failed when I connect into Oracle after I installed Application server, do I make any mistakes in setting or configuring it, how the best solution to solve this problems. Rgrds, Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto 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).
