Backups in a DW Environment
Have a question on backups in a DW environment. Our DW is somewhat small at the moment but projected to grow. I seem to be having a hard time trying to convince the sys admin that I don't want archive logging turned on. To me, it does'nt make much sense. He's proposed using EMC BCV's which I've agreed to (and also sounds like a good idea) but also wants to turn on archiving. My thinking is why turn on archiving if I can restore my DB from last night's BCV's and then bring it up to date by re-loading any data that was loaded after the BCV split. Our system is not 24x7 so we can shutdown before the BCV split. Also, it's not directly accessed by users for ad-hoc queries. Automated processes access the database and build cubes using Cognos tools. Users access these and not the DB directly. So, again I don't see the need for archive logging. Any thoughts? mohammed __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backups in a DW Environment
Hi Dennis, On average, we load data weekly. The load time is no more 40 minutes to an hour. Like I said, we're small at the moment. We're at about 70GB which includes temp and undo and growing at the rate of about 2GB a month. Consequence of a failure has been discussed with the developers and users. Developers say that they can live without the DW for one business day. The users don't access the database directly so they would not be affected. As far as critical data being lost, well if we loose the database and we have BCVs in place, we can just reload any data that is missing from the flat files, so no biggie there. Also, I take an export of the entire database after a load. As far as how much the sys admin knows about Oracle well... knows enough from a sys admin perspective that we can converse intelligently but I suspect still holds to the old myths about Oracle that have been discussed on this list. Appreciate your input. mohammed --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Mohammed - When is this database updated? Once/week? Daily? Continuously? If there is a failure, what is the consequence of returning to the last backup? How much critical data will be lost? How will recovery times be affected with/without archive logging? How much does your sys admin know about Oracle? We have a data warehouse that gets updated weekly. The day after the load we perform a cold backup. We don't use archive logging. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, January 09, 2004 3:25 PM To: Multiple recipients of list ORACLE-L Have a question on backups in a DW environment. Our DW is somewhat small at the moment but projected to grow. I seem to be having a hard time trying to convince the sys admin that I don't want archive logging turned on. To me, it does'nt make much sense. He's proposed using EMC BCV's which I've agreed to (and also sounds like a good idea) but also wants to turn on archiving. My thinking is why turn on archiving if I can restore my DB from last night's BCV's and then bring it up to date by re-loading any data that was loaded after the BCV split. Our system is not 24x7 so we can shutdown before the BCV split. Also, it's not directly accessed by users for ad-hoc queries. Automated processes access the database and build cubes using Cognos tools. Users access these and not the DB directly. So, again I don't see the need for archive logging. Any thoughts? mohammed __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Backups in a DW Environment
Yes but... The developers use Cognos tools for all their development. Nobody writes any PL/SQL, triggers etc. So again, all that the developers might lose is data that they loaded which can be easily recovered by re-running the ETL process. What I'm trying to say is that the environment from the database perpective is fairly static except when data is loaded. No users accessing directly, deveopers using third party tools for development and data changing slowly. Thanks for the input. mohammed --- Mladen Gogala [EMAIL PROTECTED] wrote: Well, recovery might be just a wee bit faster then re-loading few gigs of data using SQL. Also, developers on that DW might lose any work that they haven't done the night before. This is a production database, which means that it absolutely must be in archive log mode. One of the big reasons is that you'll have to answer the question why isn't the production DW in the archive log mode whenever you encounter an oracle consultant. Our DW is somewhat small at the moment but projected to grow. I seem to be having a hard time trying to convince the sys admin that I don't want archive logging turned on. To me, it does'nt make much sense. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backups in a DW Environment
Let's assume RMAN is not an option since we don't have a license or busget to use a third party backup tool like Legato or Veritas with RMAN (used in a previous life with Legato NetWorker. Loved it!!) So now I'm left with archive log mode. Archive logs backed up nightly and a full backup once a week. I have to set aside at least as much disk space for the data files as the size of the physical db which will later be copied to tape. Plus, I also need disk space for my BCVs'. I can't have both (budgetry constraints). I'm leaning towards BCV's. Wouldn't it be just as quick to restore the entire BCV as to do an Oracle recovery from tape? Also Gene, you mention that while loading data, you turn off archiving. So if you lost that dbf during a load, how would you recover the db? Restore the dbf, apply the logs and restart the load, right? In the same scenario in my environment I'd just restore the entire BCV set and re-start the load. Not an expert on EMC's BCV technology but my sysadmin says it can be done and yes, I'll test before I sign off on it. True, I'd be nice to have archive logging aswell. But is it a necassity or have we all been programmed into believing that ALL PRODUCTION DATABASES MUST BE IN ARCHIVE LOG REGARDLESS. Should we not be progressing beyond this like we did with hit ratios and one large extents etc...? mohammed - jumping into flame proof suit --- Gene Sais [EMAIL PROTECTED] wrote: I put all databases in archive mode, i.e. dev, test, and production. I can use test db's to test backup/recovery scenario's. The only time they are not in archive mode is when I am doing a major load (import,sqlload,etc). After I am done loading data, I put them back into archive mode. What does it cost you, a few archives? Ha, well worth it :). Gene PS. On a side note, Robert Freeman, your book is a must have using RMAN. Thanks for writing it! [EMAIL PROTECTED] 01/09/04 04:54PM My personal opinion is all production databases should be in archivelog mode. Period. End of story. Less down time, more recovery optionsit's all good. Having said that, given a specific business case, with a specific set of requirements, one could argue for noarchivelog mode, and you might even convince me...but I doubt it...;-) -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent: Friday, January 09, 2004 4:25 PM To: Multiple recipients of list ORACLE-L Have a question on backups in a DW environment. Our DW is somewhat small at the moment but projected to grow. I seem to be having a hard time trying to convince the sys admin that I don't want archive logging turned on. To me, it does'nt make much sense. He's proposed using EMC BCV's which I've agreed to (and also sounds like a good idea) but also wants to turn on archiving. My thinking is why turn on archiving if I can restore my DB from last night's BCV's and then bring it up to date by re-loading any data that was loaded after the BCV split. Our system is not 24x7 so we can shutdown before the BCV split. Also, it's not directly accessed by users for ad-hoc queries. Automated processes access the database and build cubes using Cognos tools. Users access these and not the DB directly. So, again I don't see the need for archive logging. Any thoughts? mohammed __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also
Re: Backups in a DW Environment
Hi Ryan, Not for RMAN. I meant a license for Veritas or Legato. See Mladen's reply re: BCV (basically EMC takes a snapshot of the mount points onto corresponding mount points i.e. a 1-to-1 mapping for each mount point onto a BCV mount point) Hope that clears up the confusion. mohammed --- Ryan [EMAIL PROTECTED] wrote: I never heard about the required license from veritas and legato. Can someone else confirm that this is necessary? They actually charge you more money to do use another product with veriftas and legato? What is a 'BCV'? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 7:19 PM Let's assume RMAN is not an option since we don't have a license or busget to use a third party backup tool like Legato or Veritas with RMAN (used in a previous life with Legato NetWorker. Loved it!!) So now I'm left with archive log mode. Archive logs backed up nightly and a full backup once a week. I have to set aside at least as much disk space for the data files as the size of the physical db which will later be copied to tape. Plus, I also need disk space for my BCVs'. I can't have both (budgetry constraints). I'm leaning towards BCV's. Wouldn't it be just as quick to restore the entire BCV as to do an Oracle recovery from tape? Also Gene, you mention that while loading data, you turn off archiving. So if you lost that dbf during a load, how would you recover the db? Restore the dbf, apply the logs and restart the load, right? In the same scenario in my environment I'd just restore the entire BCV set and re-start the load. Not an expert on EMC's BCV technology but my sysadmin says it can be done and yes, I'll test before I sign off on it. True, I'd be nice to have archive logging aswell. But is it a necassity or have we all been programmed into believing that ALL PRODUCTION DATABASES MUST BE IN ARCHIVE LOG REGARDLESS. Should we not be progressing beyond this like we did with hit ratios and one large extents etc...? mohammed - jumping into flame proof suit --- Gene Sais [EMAIL PROTECTED] wrote: I put all databases in archive mode, i.e. dev, test, and production. I can use test db's to test backup/recovery scenario's. The only time they are not in archive mode is when I am doing a major load (import,sqlload,etc). After I am done loading data, I put them back into archive mode. What does it cost you, a few archives? Ha, well worth it :). Gene PS. On a side note, Robert Freeman, your book is a must have using RMAN. Thanks for writing it! [EMAIL PROTECTED] 01/09/04 04:54PM My personal opinion is all production databases should be in archivelog mode. Period. End of story. Less down time, more recovery optionsit's all good. Having said that, given a specific business case, with a specific set of requirements, one could argue for noarchivelog mode, and you might even convince me...but I doubt it...;-) -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent: Friday, January 09, 2004 4:25 PM To: Multiple recipients of list ORACLE-L Have a question on backups in a DW environment. Our DW is somewhat small at the moment but projected to grow. I seem to be having a hard time trying to convince the sys admin that I don't want archive logging turned on. To me, it does'nt make much sense. He's proposed using EMC BCV's which I've agreed to (and also sounds like a good idea) but also wants to turn on archiving. My thinking is why turn on archiving if I can restore my DB from last night's BCV's and then bring it up to date by re-loading any data that was loaded after the BCV split. Our system is not 24x7 so we can shutdown before the BCV split. Also, it's not directly accessed by users for ad-hoc queries. Automated processes access the database and build cubes using Cognos tools. Users access these and not the DB directly. So, again I don't see the need for archive logging. Any thoughts? mohammed __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Tomcat
Exactly per your last point - licensing is a big Goose Egg. We've just completed a whole suite of Cognos tool installs and Cognos are pushing Tomcat. They're app server is now standardized on Tomcat. I talked to one of the analyts onsite here and he mentioned that they are moving away from IIS and towards Tomcat because of the very same reason mentioned by Stephen - low cost. mohammed --- Karniotis, Stephen [EMAIL PROTECTED] wrote: Does not really fit into their open source initiative. Anytime we mention Tomcat as a supported product for our tools, Oracle's prod. Management team cringes. They are not seeing between the lines that the app server has more stuff than most organizations desire; tomcat solves most requirements. However, for complete scalability, Oracle does win over Tomcat. Yet, the OpenSource community is adding features to Tomcat for that purpose. For product vendors, Tomcat is great because the cost for licensing is a big Goose Egg. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (313) 227-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Thursday, October 30, 2003 10:09 AM To: Multiple recipients of list ORACLE-L Subject: Tomcat I have the impression that Oracle isn't keen on supporting Tomcat with iAS. Instead, OC4J is supposedly more compliant with J2EE and is faster. How popular is Tomcat? Tomcat is being mentioned more and more often at our site, I am wondering whether we are headed for a collision at some time in the future. How does this all fit in with Oracle's Open Source and PHP initiative? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: OT -- Boston Globe job listings
I'd also like to point out that this is least likely to happen if you are a DBA in a federal or state government position (outsourcing to India etc not likely to happen). mohammed --- Stephane Faroult [EMAIL PROTECTED] wrote: Ken - Since you've recently changed jobs, your upbeat attitude is encouraging. I think you've made a good point that jobs aren't always advertised. Another point is that when there are more jobs than available candidates, companies have to advertise strongly to fill their positions. When there are more candidates than available jobs, companies often find that people are seeking out the opening before they post it. Patrice - Look at what happened over the previous years. In 1999 corporations spent wildly on I.T. (naturally when the catastrophe didn't occur because of the tireless efforts of I.T. people, the senior executives felt the money was wasted). Then when spending would have naturally declined, the dot-com madness stuck and things went wild. I think we are just about to come out of the natural down cycle due to the extravagant dot-com spending. But now I keep seeing articles about how much development work is being sent overseas. Has anyone seen that affect Oracle DBA work yet? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Dennis, Concerning your last question, a young indian DBA friend of mine in Bangalore was complaining about the night shifts and all the donkey work Indians have to perform to keep a 24x7 watch on US databases ... I would personally tend to use timezones to get senior DBAs from all around the world ready to help at normal business hours but I guess that this will have to wait until costs in India raise to sufficient levels - which in the end will happen (take a look at Hong Kong and Singapore). I indeed believe that the market for DBAs is going to shrink somewhat. As someone pointed out, big, pharaonic projects are much less common today than they were a few years back. A 'mature' database running stable applications and that you don't want to upgrade hardly requires on a daily or weekly basis anything to do that you cannot put in a crontab file. Moreover, the official Oracle gospel is of course that new versions require less and less administration - a claim which provokes more sarcastic comments on this list than in the upper management levels. However, the amount of data which people are willing to store seems to be joyfully outpacing Moore's law, and I don't see the trend losing momentum anytime soon. Expect more work related to architecture, replication (the days of exp backups have long been over) and of course performance tuning. It's probably the junior part of the market which is going to bear the brunt of the slow-down. Till the pendulum swings back and makes outsourcing out of fashion, by which time I hope that India and China will locally provide enough work for their IT people, which is more than likely. My 0.02 EUR. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: RedHat AS Dev edition
Just musing but... Are'nt Oracle and Red Hat missing a great opportunity here on building more momentum for Oracle on Linux especially RAC? Why charge anything for the Dev of AS edtition? Missed opportunities... mohammed --- Kulev, Milen [EMAIL PROTECTED] wrote: Thanks for the answer Ron, the problem is that I don't see Dev Edition listed anymore ;(. I dont't know whether RH will repeat this great offer- I didn't expected this offer to be pulled so quickly ;( That is why I am searching a way to get this software. Best Regards Milen Kulev -Ursprüngliche Nachricht- Von: Ron Rogers [mailto:[EMAIL PROTECTED] Gesendet: Montag, 16. Juni 2003 18:20 An: Multiple recipients of list ORACLE-L Betreff: Re: RedHat AS Dev edition Melin, Talking to the REdHat support people and the Dev edition was pulled last week some time. If on the buy it page you see the RedHat AS Developers edition you can still purchase it and download the ISO's with a login and password. Ron [EMAIL PROTECTED] 06/13/03 05:09PM Hallo list, I would like to play a little bit with RAC and decided to download RedHat AdvacedServer Dev Edition (in February there was a thread about this topic in the list ). The problem is that the link http://www.redhat.com/software/advancedserver/developer/ doesn't offer this promotion (60 USD)anymore. Is this offer really gone or am I missing sth ? Where could I find RH AS Dev Edition ? Any help will be appreciated. Milen Kulev -- 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: Kulev, Milen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Hash Tables and PL/SQL (Ora 9.2)
I've been trying to create a hash table using PL/SQL but I seem to be running into some trouble. Hoping someone can point me in the right direction. I've been using PL/SQL Users Guide and Reference Ch 5 as a guide. I have the following piece of code: declare cursor c1_cur is select * from load_tab; type rdt_rec_type is table of varchar2(30) index by varchar2(30); rdt_type rdt_rec_type; begin open c1_cur; loop fetch c1_cur into c1_rec; exit when c1_cur%notfound; if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; end loop; end; / My goal is to have only those values in the hash table (rdt values) that are not dups. I was hoping that object.exists(value) would work, but apparently I seem to be getting everything in my hash. Any ideas how I can code this? thanks mohammed __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hash Tables and PL/SQL (Ora 9.2)
Ok, I found the bug. Just re-read ch 5 page 5-4 Understanding Associative Arrays (Index-By Tables). The problem was here: if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; This should have been: if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(c1_rec.rdt) := c1_rec.rdt; end if; Now my hash works and has unique values. mohammed --- mkb [EMAIL PROTECTED] wrote: I've been trying to create a hash table using PL/SQL but I seem to be running into some trouble. Hoping someone can point me in the right direction. I've been using PL/SQL Users Guide and Reference Ch 5 as a guide. I have the following piece of code: declare cursor c1_cur is select * from load_tab; type rdt_rec_type is table of varchar2(30) index by varchar2(30); rdt_type rdt_rec_type; begin open c1_cur; loop fetch c1_cur into c1_rec; exit when c1_cur%notfound; if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; end loop; end; / My goal is to have only those values in the hash table (rdt values) that are not dups. I was hoping that object.exists(value) would work, but apparently I seem to be getting everything in my hash. Any ideas how I can code this? thanks mohammed __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HP-Sun Cross Platform Migration - Exp/Imp, CTAS over dblink or ..
Wondering out aloud here. Could you export/imp using pipes. I remember doing this on a 200gb database going from 7.3 to 8 on Solaris, granted on the same server. Took about 4-6 hours if I remember correctly. Anyway to create a remote pipe on another server to listen for inputs from another server so that the above can be accomplisher or am I Way off the mark here. mohammed --- Goulet, Dick [EMAIL PROTECTED] wrote: Exp/imp of a 200GB database is possible, but I'd think the time required would be the long pole in the tent. I'd say your looking at at least a 4 day weekend at best and only if you used direct mode. Someone has hinted that you can simply move the datafiles from one box to the other. Well I'd not loose the original system before you prove that. My experience with database file from Solaris to HP-UX has been a 100% loss of data. Granted that was on a much earlier version of Oracle (6.0.x). Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Saturday, June 07, 2003 7:29 AM To: Multiple recipients of list ORACLE-L .. Hi all: We are considering migration of an Oracle eBusiness Suite 11.5.8 from HP-UX 11.0 to Solaris9. I'd like to know if anyone has done this and how daunting the task is. Are there any 3rd Party tools which can help out? Coming to the conventional approaches: 1. What do you think of Export/Import of a 200 GB database? 1a. How much time will it take? 1b. Any strategies for cutting that down? 2. How does CTAS over dblink compare to Export/Import? 3. Is there any tool that converts Oracle datafiles on HP-UX 11.0 to Oracle datafiles on Solaris 9. If so we would just need to recreate the control files on the target database and we are done. Any suggestions, pointers, words of wisdom are greatly appreciated... Thanks Regards, Sashi -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sashidhar Kondareddy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Hi Kirti, Just a clarification: PK on col1, col2 but you have duplicates C,D and E,F. If the dups are removed, is the porblem still valid? mohammed --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- 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). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Assuming dups can be deleted, here's my humble attempt: select col1, col2 from t order by col1, col2; Col1 Col2 -- AB BA CD EF GH HG 6 rows selected. select col1, col2 from t union select col2, col1 from t ; Col1 Col2 -- AB BA CD DC EF FE GH HG 8 rows selected. mohammed --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- 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). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Metalink Oracle Support Improving.......
Yep, had the same experience last week with Cognos support. Pretty cool. Tool is called Webex I believe. Support can see what you do but can't take over your workstation. It's like they have a looking glass on your workstation but can only see open items on your desktop. mohammed --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Kirti, Unfortunately for us, our security experts won't allow that ... so we *walk* them through and give them reproducible test cases. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- Sent: Monday, March 03, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Today I found out that the OWS personnel can use 'Oracle Direct Connect' to view your actions on the Client PC. One of our Developers resolved his problem, with PreCompilers, when the support analyst 'saw' his actions via this tool. There is some information at http://metalink.oracle.com/odc/east (or west)... - Kirti -- 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). 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 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Send Mail in Unix
You mean: mailx -m -s Test Message [EMAIL PROTECTED] EOF `ux2dos /home/report/tbsp.lst | uuencode /home/report/tbsp.lst` This is from an HP-UX system hth mohammed --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: All, I'm trying to send an email attachment (Oracle Tablespace Report) from a Sun Unix box to myself when the batch job runs. Anybody been able to do this? I can send the text of the file, but what I really want to do is to send the file (it's an Excel Spreadsheet). thanks in advance. Tom Mercadante Oracle Certified Professional -- 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). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was Unix time conversion function
- because it would be fun to write your own app - sense of accomplishment - you'd get a better handle on the language knowing it's strength and weaknesses etc... Not saying that you should go and write your own dbms or word processor or OS. But sometimes writing a little utility from scratch is more rewarding than downloading a pre-packaged app. mohammed --- [EMAIL PROTECTED] wrote: Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Why? Look up OraC and OracleTool on google. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Perl - Was Unix time conversion function For me, it was either Perl or an icky bass-ackward pipe-laden awk/sed/regex unmaintainable bastion. OK, I couldn't get rid of the regex. While I'll not be entering the Obfuscated Perl contest anytime soon, I think Perl is much easier to understand for a traditional programmer (Assembly, BASIC, COBOL, FORTRAN, and a little C). I bought O'Reilly's Learning Perl, and most of what I needed to do was in the book as an example. Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). __ 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: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: create tablespace script
Helmut,
I have the following in perl. It handles multiple
datafiles for a tablespace. See the in-line comments
below. You can modify this logic for PL/SQL. I think
I also have a script in PL/SQL (somewhere) that I
wrote and converted to perl. Let me know if you'd
like to take a look at that.
hth
mohammed
#!/usr/local/bin/perl
###
#
# Get tablespace DDL from Oracle databases (8x, 8i)
# Create 2001/10/01 - MKB
# Modification History
# 2001/10/11 - MKB - Pass command line opts to program
###
use strict;
use DBI;
my ($username, $passwd, $connstrg, $filename);
my $usage = usage: username password connect_string
filename \n;
$usage = $usage . Example: scott tiger remt_db
tablespace.log \n;
if ($#ARGV != 3) { die($usage) }
else { $username = $ARGV[0];
$passwd = $ARGV[1];
$connstrg = $ARGV[2];
$filename = $ARGV[3];
};
# Declare variables to hold col names, col typs and
col lengths
my ($ts_name, $prev_val, $fl_name, $size);
my ($init_ext, $nxt_ext, $min_ext, $max_ext,
$pct_incr, $bytes);
###
# This variable is set to 1 indicating more
# than one datafile per tablespace
###
my $dup = 0;
# CREATE TABLESPACE string
my ($create_ts, $datafile, $def_strg1, $def_strg2,
$def_strg3, $alt_ts);
my $dbh = DBI - connect (dbi:Oracle:$connstrg,
$username, $passwd)
|| die Database connection not made:
$DBI::errstr;
# open file to write out tablespace info
open my $fh, $filename or die
Can't create $!;
my $sql = qq{ select t.tablespace_name,
t.initial_extent,
t.next_extent, t.min_extents,
t.max_extents,
t.pct_increase, df.bytes, df.file_name,
df.relative_fno
from dba_data_files df, dba_tablespaces t
where t.tablespace_name =
df.tablespace_name
order by t.tablespace_name,
df.relative_fno };
my $sth = $dbh - prepare( $sql );
$sth - execute();
my ($tablespace_name, $initial_extent, $next_extent,
$min_extents, $max_extents, $pct_increase, $bytes,
$file_name, $relative_fno);
$sth - bind_columns(\$tablespace_name,
\$initial_extent, \$next_extent,
\$min_extents, \$max_extents,
\$pct_increase, \$bytes,
\$file_name, \$relative_fno);
while( $sth - fetch() ) {
$ts_name = $tablespace_name;
###
# Here is where I check if there are more
# than one datafile per tablespace
###
if ( $prev_val eq $ts_name ) {
$dup = 1;
$alt_ts = ALTER TABLESPACE . $ts_name;
}
else {
$dup = 0;
$prev_val = $ts_name;
}
$fl_name = $file_name;
$init_ext = $initial_extent;
$nxt_ext = $next_extent;
$min_ext = $min_extents;
$max_ext = $max_extents;
$pct_incr = $pct_increase;
$size = $bytes;
###
# if $dup is 0 than I only have one datafile
# per tablespace else I set this to 1 which
# I have more than one datafile per
# tablespace so I use an ALTER statement
# add the extra datafile to the tablespace
###
if ( $dup == 0 ) {
$alt_ts = CREATE TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
else {
$alt_ts = ALTER TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
if ( $dup == 0 ) {
$datafile = DATAFILE ' . $fl_name . ' SIZE
. $size;
print $fh $datafile . \n;
$def_strg1 = DEFAULT STORAGE (\n\tINITIAL .
$init_ext;
$def_strg1 = $def_strg1 . \n\tNEXT .
$nxt_ext;
$def_strg2 = \n\tMINEXTETNTS . $min_ext .
\n\tMAXEXTENTS . $max_ext;
$def_strg3 = \n\tPCTINCREASE . $pct_incr .
);;
print $fh $def_strg1, $def_strg2, $def_strg3 .
\n . \n;
}
else {
$datafile = ADD DATAFILE . $fl_name . SIZE
. $size . ;;
print $fh $datafile . \n . \n;
}
}
$sth - finish();
$dbh - disconnect();
$dbh - disconnect();
close $fh;
--- Daiminger, Helmut
[EMAIL PROTECTED] wrote:
Hi!
I want to write a create tablespace script that
creates all create
tablespace statements for a database.
I got this script working if each tablesspace has
only one datafile. But how
would I handle it if a tablespace consists of 2
datafiles, e.g. datafile 5
and 87 from dba_data_files...
Is there an id for the datafiles within the
tablespace???
Any ideas?
Thanks,
Helmut
__
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: mkb
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
Re: To Anyone involved in Web apps
Hi Regina, I'll my 2 cents here. We are creating a single Oracle user for each connection. Our app is using IIS/ASP and Oracle as the DB. We looked into using a single app user and controling security from the app. Since our is designed for a secure site, we wanted to keep as much control of security within the database as possible and leave as little to the IIS/ASP comboniation as we could. The security layer is built into the database and we only use the front end to authenticate to the database. We have also turned on autiditing so that we know who has logged on and what they are doing - again, a requriment for the project. Granted, we could have done this via the front end application but we felt much more comfortable putting the security into the hands of the database layer even though this requried the creation of a database user per connection. This is handled via stored procs called from the front end by a security officer so there is very little DBA intervention in managing database users. The disadvantage is obviously we can't use application connection pooling but we can use MTS; although on NT this seems to work not too well. We seem to see a lot of latency. Advantage is from the security perpective i.e. we let the datbase handle all the security, we know who, when and from where each user logged in and we can easliy control access by modifying roles and privs and they take effect immediately. hth mohammed --- Regina Harter [EMAIL PROTECTED] wrote: Hi I have a question for any of you involved in Web applications. I would like to know how many of you go for the single Oracle user for everyone approach, and how many of you create Oracle schemas for each user, and if you can, what was the major reason for choosing that approach. Any opinions you wish to contribute will be helpful. Thank you, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: create tablespace script
Jared,
Is that a perl module? downloadable from CPAN?
thanks
mohammed
--- [EMAIL PROTECTED] wrote:
Since you're using Perl already, give DDL::Oracle a
try.
Takes a lot less code.
Jared
mkb [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/21/2003 01:09 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: create tablespace script
Helmut,
I have the following in perl. It handles multiple
datafiles for a tablespace. See the in-line
comments
below. You can modify this logic for PL/SQL. I
think
I also have a script in PL/SQL (somewhere) that I
wrote and converted to perl. Let me know if you'd
like to take a look at that.
hth
mohammed
#!/usr/local/bin/perl
###
#
# Get tablespace DDL from Oracle databases (8x, 8i)
# Create 2001/10/01 - MKB
# Modification History
# 2001/10/11 - MKB - Pass command line opts to
program
###
use strict;
use DBI;
my ($username, $passwd, $connstrg, $filename);
my $usage = usage: username password connect_string
filename \n;
$usage = $usage . Example: scott tiger remt_db
tablespace.log \n;
if ($#ARGV != 3) { die($usage) }
else { $username = $ARGV[0];
$passwd = $ARGV[1];
$connstrg = $ARGV[2];
$filename = $ARGV[3];
};
# Declare variables to hold col names, col typs and
col lengths
my ($ts_name, $prev_val, $fl_name, $size);
my ($init_ext, $nxt_ext, $min_ext, $max_ext,
$pct_incr, $bytes);
###
# This variable is set to 1 indicating more
# than one datafile per tablespace
###
my $dup = 0;
# CREATE TABLESPACE string
my ($create_ts, $datafile, $def_strg1, $def_strg2,
$def_strg3, $alt_ts);
my $dbh = DBI - connect (dbi:Oracle:$connstrg,
$username, $passwd)
|| die Database connection not made:
$DBI::errstr;
# open file to write out tablespace info
open my $fh, $filename or die
Can't create $!;
my $sql = qq{ select t.tablespace_name,
t.initial_extent,
t.next_extent, t.min_extents,
t.max_extents,
t.pct_increase, df.bytes,
df.file_name,
df.relative_fno
from dba_data_files df, dba_tablespaces
t
where t.tablespace_name =
df.tablespace_name
order by t.tablespace_name,
df.relative_fno };
my $sth = $dbh - prepare( $sql );
$sth - execute();
my ($tablespace_name, $initial_extent, $next_extent,
$min_extents, $max_extents, $pct_increase,
$bytes,
$file_name, $relative_fno);
$sth - bind_columns(\$tablespace_name,
\$initial_extent, \$next_extent,
\$min_extents, \$max_extents,
\$pct_increase, \$bytes,
\$file_name, \$relative_fno);
while( $sth - fetch() ) {
$ts_name = $tablespace_name;
###
# Here is where I check if there are more
# than one datafile per tablespace
###
if ( $prev_val eq $ts_name ) {
$dup = 1;
$alt_ts = ALTER TABLESPACE . $ts_name;
}
else {
$dup = 0;
$prev_val = $ts_name;
}
$fl_name = $file_name;
$init_ext = $initial_extent;
$nxt_ext = $next_extent;
$min_ext = $min_extents;
$max_ext = $max_extents;
$pct_incr = $pct_increase;
$size = $bytes;
###
# if $dup is 0 than I only have one datafile
# per tablespace else I set this to 1 which
# I have more than one datafile per
# tablespace so I use an ALTER statement
# add the extra datafile to the tablespace
###
if ( $dup == 0 ) {
$alt_ts = CREATE TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
else {
$alt_ts = ALTER TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
if ( $dup == 0 ) {
$datafile = DATAFILE ' . $fl_name . ' SIZE
. $size;
print $fh $datafile . \n;
$def_strg1 = DEFAULT STORAGE (\n\tINITIAL .
$init_ext;
$def_strg1 = $def_strg1 . \n\tNEXT .
$nxt_ext;
$def_strg2 = \n\tMINEXTETNTS . $min_ext .
\n\tMAXEXTENTS . $max_ext;
$def_strg3 = \n\tPCTINCREASE . $pct_incr .
);;
print $fh $def_strg1, $def_strg2, $def_strg3 .
\n . \n;
}
else {
$datafile = ADD DATAFILE . $fl_name .
SIZE
. $size . ;;
print $fh $datafile . \n . \n;
}
}
$sth - finish();
$dbh - disconnect();
$dbh - disconnect();
close $fh;
--- Daiminger, Helmut
[EMAIL PROTECTED] wrote:
Hi!
I want to write a create tablespace script that
creates all create
tablespace statements for a database.
=== message truncated ===
__
Do you
Re: create tablespace script
Jared,
In answer to my question re is it on cpan etc...
duh! should have checked before. Yes it is.
Thanks, looks good. I'll download it and play with it
some.
mohammed
--- [EMAIL PROTECTED] wrote:
Since you're using Perl already, give DDL::Oracle a
try.
Takes a lot less code.
Jared
mkb [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/21/2003 01:09 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: create tablespace script
Helmut,
I have the following in perl. It handles multiple
datafiles for a tablespace. See the in-line
comments
below. You can modify this logic for PL/SQL. I
think
I also have a script in PL/SQL (somewhere) that I
wrote and converted to perl. Let me know if you'd
like to take a look at that.
hth
mohammed
#!/usr/local/bin/perl
###
#
# Get tablespace DDL from Oracle databases (8x, 8i)
# Create 2001/10/01 - MKB
# Modification History
# 2001/10/11 - MKB - Pass command line opts to
program
###
use strict;
use DBI;
my ($username, $passwd, $connstrg, $filename);
my $usage = usage: username password connect_string
filename \n;
$usage = $usage . Example: scott tiger remt_db
tablespace.log \n;
if ($#ARGV != 3) { die($usage) }
else { $username = $ARGV[0];
$passwd = $ARGV[1];
$connstrg = $ARGV[2];
$filename = $ARGV[3];
};
# Declare variables to hold col names, col typs and
col lengths
my ($ts_name, $prev_val, $fl_name, $size);
my ($init_ext, $nxt_ext, $min_ext, $max_ext,
$pct_incr, $bytes);
###
# This variable is set to 1 indicating more
# than one datafile per tablespace
###
my $dup = 0;
# CREATE TABLESPACE string
my ($create_ts, $datafile, $def_strg1, $def_strg2,
$def_strg3, $alt_ts);
my $dbh = DBI - connect (dbi:Oracle:$connstrg,
$username, $passwd)
|| die Database connection not made:
$DBI::errstr;
# open file to write out tablespace info
open my $fh, $filename or die
Can't create $!;
my $sql = qq{ select t.tablespace_name,
t.initial_extent,
t.next_extent, t.min_extents,
t.max_extents,
t.pct_increase, df.bytes,
df.file_name,
df.relative_fno
from dba_data_files df, dba_tablespaces
t
where t.tablespace_name =
df.tablespace_name
order by t.tablespace_name,
df.relative_fno };
my $sth = $dbh - prepare( $sql );
$sth - execute();
my ($tablespace_name, $initial_extent, $next_extent,
$min_extents, $max_extents, $pct_increase,
$bytes,
$file_name, $relative_fno);
$sth - bind_columns(\$tablespace_name,
\$initial_extent, \$next_extent,
\$min_extents, \$max_extents,
\$pct_increase, \$bytes,
\$file_name, \$relative_fno);
while( $sth - fetch() ) {
$ts_name = $tablespace_name;
###
# Here is where I check if there are more
# than one datafile per tablespace
###
if ( $prev_val eq $ts_name ) {
$dup = 1;
$alt_ts = ALTER TABLESPACE . $ts_name;
}
else {
$dup = 0;
$prev_val = $ts_name;
}
$fl_name = $file_name;
$init_ext = $initial_extent;
$nxt_ext = $next_extent;
$min_ext = $min_extents;
$max_ext = $max_extents;
$pct_incr = $pct_increase;
$size = $bytes;
###
# if $dup is 0 than I only have one datafile
# per tablespace else I set this to 1 which
# I have more than one datafile per
# tablespace so I use an ALTER statement
# add the extra datafile to the tablespace
###
if ( $dup == 0 ) {
$alt_ts = CREATE TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
else {
$alt_ts = ALTER TABLESPACE . $ts_name;
print $fh $alt_ts . \n;
}
if ( $dup == 0 ) {
$datafile = DATAFILE ' . $fl_name . ' SIZE
. $size;
print $fh $datafile . \n;
$def_strg1 = DEFAULT STORAGE (\n\tINITIAL .
$init_ext;
$def_strg1 = $def_strg1 . \n\tNEXT .
$nxt_ext;
$def_strg2 = \n\tMINEXTETNTS . $min_ext .
\n\tMAXEXTENTS . $max_ext;
$def_strg3 = \n\tPCTINCREASE . $pct_incr .
);;
print $fh $def_strg1, $def_strg2, $def_strg3 .
\n . \n;
}
else {
$datafile = ADD DATAFILE . $fl_name .
SIZE
. $size . ;;
print $fh $datafile . \n . \n;
}
}
$sth - finish();
$dbh - disconnect();
$dbh - disconnect();
close $fh;
--- Daiminger, Helmut
[EMAIL PROTECTED] wrote:
Hi!
I want to write a create tablespace script that
creates all create
tablespace statements
Re: iAS 903 902 install
Hi Barb, Don't know if the following helps any but I went through an install of this beast a few months back. Here are my notes: hth mohammed -Original Message- From: mkb [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, July 17, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: A DBA looks at OAS No books or tips. Just my recent experience *trying* to install 9iAS R2 (I hope that's what you meant when you wrote OAS). I downloaded 9iAS J2EE and Web Cache for Solaris and HP-UX. Oracle recommends about 1GB ram, 1GB swap and lots of free disk space. Anyway, my target Solaris box had 500MB swap, 256MB ram and a 400 MhZ SparcII CPU. Not the ideal platform. On the HP-UX box, I had 3GB ram, a 2 CPU L class machine, lots of swap and lots of disk. In any case, what I learned is that root privs are vital. Had them on the Solaris box but not on the HP-UX machine. The installs in both cases where fairly standard. I had ran through them quite a few times on both servers. On Solaris becuase of resource issues and HP-UX because of root permission issues. There are two types of installs. A mid-tier (less config, easier, fewer components) and an infrastructure (more config, more components, needs a database repoistory). I did the mid-tier install in both cases. Make sure you have JDK 1.3 or later installed. Before the install for mid-tier in particular, export ORACLE_SID=iasdb even if you do not intend to use a repository or have a database. I created a separate ORACLE_HOME for my install. Also, Oracle recommends that you use hostnames, so naming methods should reflect hostname.com instead of 123.45.67.8. During the install, you will be asked for a password for the Eterprise Manager website. NOTE IT DOWN!!! You'll need it to start and stop the EM website. Oracle recommends that you start and stop services via the EM website and not the command line and I'll go along with this since I had trouble shutting down services via the command line (sometime it worked and sometime it did'nt). Also, during the install when prompted to run the root.sh script, run as root since this script starts the Apache httpd daemons. These need to be started as root. It does a bunch of other config things aswell. See root.sh. This is vital since after the install is complete, the installer then configures the components such web cache, OC4J components, Apache config etc. This is the problem I was having on HP-UX, late in the day, govt client, sysadmin has left the building. Ok, after the install has completed and started all the services (hoepfully), you need to apply all relevant patches. For the mid tier install, install the patch in the following order: 9.0.1.3 patch set RDBMS bundled patch Oracle Internet Directory path Oracle HTTP server patch You'll see this in the install notes for the patch. Note that the RDBMS bundled patch is slightly different on HP-UX versus Solaris. Just read the instructions carefully if you are on HP-UX. Solaris was a little easier. After the patch, you can login to the EM website at http://myhostname.com:1810. If the website does not come up, you can start it from the prompt using emctl start|stop|status. Stopping requires password which was entered earlier during install. Password can also be changed using emctl set password pwd. Using the website, you can/start stop other services such as web cache, BC4J, OC4J containers etc. The default website can be accessed (hopefully) at http://myhostname.com: Also, you can start|stop the httpd daemons from the command line from $ORACLE_HOME/dcm/bin/dcmctl start|stop -ct ohs if the EM website is inaccessible for some reason. Again, Oracle recommends that you do all admin through EM the website. Similarly, web cache can be started/stopped from the prompt by webcachectl start|stop|status. Finally, just a couple days ago, we seemed to have trouble starting 9iAS. Seems like some log files had their ownership changed. Don't know how this happened. My guess is some sort of bug. The way I tracked this is tailing the logs while trying to start the server. Since I could'nt get the EM website up, I had to use $ORACLE_HOME/dcm/bin/dcmctl start -ct ohs. Useful logs were: $ORACLE_HOME/opmn/logs/ons.log and ipm.log $ORACLE_HOME/dcm/logs/emd_logs/ and dcmctl_logs/ and of cource $ORACLE_HOME/Apache/Apache/logs/error_log and access_log --- Jeff Herrick [EMAIL PROTECTED] wrote: Barb, I've been deploying forms over the web since OAS 4.07 and I had Oracle WebServer experience going back to version 2. What I found was that knowing all of that stuff made it worse for trying to figure out 9IAS Rel 2 (9.03). What further complicates it (web forms that is) is that if you read the documents on Metalink regarding the forms listener 'Servlet' implementation then you will be lost
RE: Automatic backup on Oracle 9i -- For Jared
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: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Was [RE: Automatic backup on Oracle 9i -- For Jared]
Actually Raj, I appreciate the fact that you brought this up. It needed to be said. mohammed --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Sorry everyone ... I didn't mean to open a can of worms. Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, January 02, 2003 1:42 PM To: Multiple recipients of list ORACLE-L Lighten up Frances -Original Message- Sent: Thursday, January 02, 2003 10:46 AM To: Multiple recipients of list ORACLE-L 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 __ 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: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle is a time machine!!
strange, I get
TO_CHAR(TH
--
10/15/1582
and not 10/05/1582
mo
--- Freeman, Robert [EMAIL PROTECTED] wrote:
Don't believe me?? Try this:
create table test(the_date date);
insert into test values
(to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on
Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in
the same package. How
efficient of you.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and web
hosting services
-
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do you Yahoo!?
Yahoo! Mail Plus Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re:Queues - does anyone use them
Bruce, We used to use queues in 8.1.6 on Solaris. Don't remember much about them except that they were, at that time, unreliable. We'd have push/pulls jobs that would run extract and transformation routines from multiple databases into a reporting DW. We finally switched over to Informatica because of the unreliabilty of the queues. As for seperate queue tables, don't remember. I believe the queues were owned by the application schema since I remember looking for stopped jobs in user_jobs as the application owner. mkb --- [EMAIL PROTECTED] wrote: Bruce, No we don't use advanced queuing here. Don't have the time to figure out how to make it work. Dick Goulet Reply Separator Author: Reardon; Bruce (CALBBAY) [EMAIL PROTECTED] Date: 10/29/2002 10:58 PM Hi, I've sent a couple of questions on queues and got no answers - that's fine and I understand we're all busy. What I'm wondering though is whether anyone is actually using Oracle queues at all? Any feedback would be appreciated. For anyone out there who does use Advanced queues: one of our developers read that Creating a queue table in a tablespace will disable that particular tablespace for point-in-time recovery. - Do you normally put your AQ tables in a separate tablespace (we're currently looking at doing just that)? - Who normally owns the queues and queue tables - system or the application schema. Thanks, Bruce Reardon mailto:bruce.reardon;comalco.riotinto.com.au -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Logon Trigger
Sorry in advance, but I didn't think I'd need this. There was a discussion about 4 weeks ago if memory serves correct, about denying users logging on to Oracle directly either through SQL*Plus or other tools such as TOAD. Tried searching the archives but getting too many hits. Someone posted trigger code that did this. Anyone have a copy of this? Thanks mkb __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Performance Tuning (Book)
Any follks have reviews on the book Oracle Performance Tuning written by Edward Whalen Mitchell Schroter and published by Addison-Wesley? Saw a blurb in the recent issue of Ora Mag. Thanks mkb __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Reduce parse call for stored procedure?
How about using bind variables in your execute immediate statement. Please see doc id 34433.1 and 366753.999 on Metalink as I followed 34433.1 for what I tested below. 366753.999 is a forum question which relates very well to doc id 34433.1. create table t (col1 number, col2 varchar2(10)); create or replace procedure sp_test ( p_col1 in number, p_col2 in varchar2, p_errcd out number, p_errmsg out varchar2) as begin execute IMMEDIATE 'insert into t (col1, Col2) values (:b1, :b2)' USING p_col1, p_col2; p_errcd := SQLCODE; p_errmsg := SQLERRM; EXCEPTION WHEN others THEN ROLLBACK; p_errcd := SQLCODE; p_errmsg := SQLERRM; end; / Then did this: alter system flush shated_pool=true; alter session set sql_trace=true; var errcd number; var errmsg varchar2(2000); -- exec the following about 5 times exec sp_test(1,'A',:errcd,:errmsg); alter session set sql_trace=false; --Now check the following: select sql_text, loads, executions, PARSE_CALLS from v$sql where sql_text like 'insert into t%col1%' ; LOADS EXECUTIONS PARSE_CALLS -- -- --- 1 5 5 1 0 0 Check sql_trace output, I get the following for each of the 5 executions (note that mis=0 indicating that it is not a hard parse): PARSING IN CURSOR #1 len=45 dep=0 uid=67 oct=47 lid=67 tim=2182033968 hv=1348535850 ad='79547da4' BEGIN sp_test(1,'A', :errcd, :errmsg); END; END OF STMT PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2182033968 = PARSING IN CURSOR #2 len=65 dep=1 uid=67 oct=2 lid=67 tim=2182033968 hv=2052728044 ad='79d476b0' insert into t (col1, Col2) values (:b1, :b2) END OF STMT PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2182033968 EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=2182049968 EXEC #1:c=15625,e=16000,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=4,tim=2182049968 So, in conclusion, by using bind var in my proc, I have reduced the hard parse count. hth mkb (Hoping that if I have misstated anything, someone will correct me.) --- chao_ping [EMAIL PROTECTED] wrote: Chuan Zhang, Since you are using execute immediate, you use dynamic sql. If you want to reduce the parse, can u try not using the dynamic sql? Keep it won't help at all. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-10-22 23:43:00 ,you wrote£º=== Hi, DBA Guru, I have a stored procedure of a package which is called with execute immediate in a loop with runtime input parameters. I found that no. of parse calls(451983) is equal to no. of executions (451982). Is there any way such as set cursor_sharing=force or keep this stored procedure into shared pool to reduce the parse call down to one or some no.? TIA, Chuan = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Staspack Grapher/Viewer ?
And if you don't want to use MS Graph, you can use
Perl's built-in graphing facility (see Programming
Perl) or gnuplot.
I had developed something similar at a previous gig
but I used gnuplot.
mkb
--- Gesler, Rich [EMAIL PROTECTED] wrote:
Here is something I am playing around with. The
idea came from Burleson's Statspack book. It
requires Active Perl with DBD/DBI installed. Also
MS Graph is used. This is still a work in progress.
#!C:\Perl\bin\perl.exe -w
#
# rpt_avg_bbw_dy.pl
# Report Average Buffer Busy Wait by Day
# This perl script will produce a graphical
# Signature of information.
# This information is obtained from statspack tables
# and takes advantage of perl's Win32 OLE interface
# to Microsoft Graph.
#
use DBI;
use Win32::OLE qw( with in );
use Win32::OLE::Const Microsoft Graph;
#
# Set Oracle User and Password Information
#
$name= perfstat;
$passwd = x;
$ora_sid = PROD;
# 1 makes creation process visible. 0 is faster.
my $VISIBLE = 1;
my $iIndex = 0;
#
# Make connection to Database
#
$dbh = DBI-connect(dbi:Oracle:$ora_sid, $name,
$passwd)
or die Cannot connect : $DBI::errstr;
#
# Prepare Statement to query database
#
$sth = $dbh-prepare(select
to_char(snap_time,'day') day,
avg(new.buffer_busy_wait-old.buffer_busy_wait) bbw
from
perfstat.stats\$buffer_pool_statistics old,
perfstat.stats\$buffer_pool_statistics new,
perfstat.stats\$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
group by
to_char(snap_time,'day') )
||die Can't prepare statement: $DBI::errstr;
$sth-execute ()
||die Can't execute statement: $DBI::errstr;
while (($day, $bbw) = $sth-fetchrow_array)
{ # loop thru, retrieving data
$Data[$iIndex] = [$day, $bbw];
$iIndex = $iIndex + 1;
}
my %ChartOptions = (
width = 640,
height = 400,
haslegend = 0,
type = xl3DLine,
perspective = 30,
rotation = 20,
autoscaling = 1,
rightangleaxes = 1,
title = Buffer Busy Wait Signature by Day,
);
my( @CELLS ) = ( 'a'..'zz' );
my $File = C:\\temp\\bbw_day.gif;
# BEGIN CALLOUT A
# new() method creates an instance of MS Graph's
Application object.
# To have a remote machine create the chart (DCOM)
then change MSGraph.Application
# parameter to an anonymous array
[appserver.mydomain.com,MSGraph.Application]
my $ChartApp = new Win32::OLE(
MSGraph.Application, Quit ) ||
die Cannot create object\n;
# END CALLOUT A
$ChartApp-{Visible} = $VISIBLE;
# BEGIN CALLOUT B
my $DataSheet = $ChartApp-DataSheet();
my $Chart = $ChartApp-Chart();
# END CALLOUT B
foreach my $Option ( keys( %ChartOptions ) )
{
$Chart-{$Option} = $ChartOptions{$Option};
}
# BEGIN CALLOUT C
my $iTotal = $#Data;
foreach my $iIndex ( 0 .. $iTotal)
{
my $iday = $Data[$iIndex][0];
my $ibbw = $Data[$iIndex][1];
$DataSheet-Range( $CELLS[$iIndex]0 )-{Value} =
$iday;
$DataSheet-Range( $CELLS[$iIndex]1 )-{Value} =
$ibbw;
}
# END CALLOUT C
print \n;
# Configure the X axis.
if( my $Axis = $Chart-Axes( xlCategory ) )
{
$Axis-{HasMajorGridlines} = 0;
$Axis-{TickLabels}-{orientation} = xlUpward;
with( $Axis-{TickLabels}-{Font},
Name = Tahoma,
Bold = 0,
Italic = 0
);
}
# Configure the Y axis.
if( my $Axis = $Chart-Axes( xlValue ) )
{
$Axis-{HasMajorGridlines} = 1;
$Axis-{MajorGridlines}-{Border}-{Weight} = 1;
$Axis-{MajorGridlines}-{Border}-{ColorIndex} =
48;
$Axis-{MajorGridlines}-{Border}-{LineStyle} =
xlContinuous;
with( $Chart-Axes( xlValue
)-{TickLabels}-{Font},
Name = Tahoma,
Bold = 0,
Italic = 0
);
}
# BEGIN CALLOUT D
# Configure data-point labels.
$Chart-SeriesCollection( 1 )-{HasDataLabels} = 1;
if( my $Labels =
$Chart-SeriesCollection(1)-DataLabels() )
{
with( $Labels,
NumberFormat = #.0,
Type = xlDataLabelsShowValue
);
with( $Labels-{Font},
Name = Tahoma,
Bold = 0,
Italic = 0,
);
}
if( defined $ChartOptions{title} )
{
$Chart-{HasTitle} = 1;
$Chart-{ChartTitle}-{Text} =
$ChartOptions{title};
$Chart-{ChartTitle}-{Font}-{Name} = Tahoma;
$Chart-{ChartTitle}-{Font}-{Size} = 18;
}
# Remove consecutive redundant data-point labels.
$iTotal = $Chart-SeriesCollection( 1
)-Points()-{Count};
$iIndex = 0;
my $PrevText = ;
foreach my $Point (in( $Chart-SeriesCollection( 1
)-Points()))
{
my $Percent = int( ++$iIndex * 100 / $iTotal );
my $Text = $Point-{DataLabel}-{Text};
$Point-{MarkerStyle} = xlMarkerStyleDot;
$Point-{DataLabel}-{Font}-{Background} =
xlBackgroundOpaque;
$Point-{DataLabel}-{Top} -= 12;
$Point
RE: Ioug meeting or HOTSOS Seminar
Are we taking votes? From what Cary/Gaja/Anjo/Tom etc have contributed so far on list, I'd say Hotsos. Hey Cary, when are you planning to present in the Washington DC area? mkb --- [EMAIL PROTECTED] wrote: Since performance tuning is just one aspect (although extremely important) of my DBA role I would choose IOUG thinking that I would get performance info. as well as other aspects of my role covered and could pick/choose. Although a conference with Cary Millsap would always be great - the variety would be nice too. -Original Message- Sent: Tuesday, September 10, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Hello List I got a message about Hotsos Tuning seminar in Dallas. Scheduled in February 2003. The lecturers list include among others: Tom, Anjo, Gaja. http://www.hotsos.com/events/symposium/ I also know that there is an IOUG Live meeting on May 2003, and many of you recommended going. http://bneo15.sba.com/ew/ioug/index3.cfm?clientsess_id=07332332nextpage=cal lpapersconference_id=71 MAYBE (not shouting but a big maybe) I will convince management to spring for one of the two. Which one you recommend? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: bind variables
Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other
Re:bind variables
John, I'm in exactly the same predicament. I'm also trying to find some examples. I have an older version of Feuerstein book which does talk about using DBMS_SQL package to bind variables. Unfortunately it looks a little messy. I'm now looking at the following link: http://gethelp.devx.com/techtips/oracle_pro/10min/10min1000.asp which seems to provide a couple examples. This is for 8i and above. If I get anything to work, I'll pass along what I have. hth mkb --- Rachel Carmichael [EMAIL PROTECTED] wrote: O'Reilly and PL/SQl Programming almost ALWAYS means the author is Steven Feuerstein --- [EMAIL PROTECTED] wrote: John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1
Re: Oracle on windows vs Redhat
ora ora Here it is once again: http://www.cuug.ab.ca/~leblancj/nt_to_unix.html hth mkb --- Ron Rogers [EMAIL PROTECTED] wrote: oraoraora, Yesterday there was a posting of a URL that pointed you to a research paper that supplied just the answers you want. Check the archives for the email message. Ron ROR mª¿ªm [EMAIL PROTECTED] 09/04/02 03:43AM Guys, I have heard from people in the forum that Oracle performs well on Linux/Solaris than Windows.Can someone give me docs/papers which proves the same.I need this to convince my manager. Our DB is on Win2K now.we thought of moving to Redhat. TIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Unix/Linux/NT Whitepaper
Came across this very slanted view of Unix/Linux vs Windows. Hope it comes in handy for anyone doing research on this particular topic. http://www.cuug.ab.ca/~leblancj/nt_to_unix.html mkb __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FW: PL/Sql question
um...just a thought but how about setting marketingcode to char(3) in the PL/SQL code snippet. I ran into this similar problem a couple days ago. Had a var as varchar2 in PL/SQL but in the table it was char. Changed my PL/SQL var to char, cursor in my code worked with ltrim and rtrim functions whereas before it wasn't. hth mkb --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS POSITION(208:208) CHAR ) == All tables have the marketingcode field defined as varchar2(3) (none are char(3)) Bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also
Re: FW: PL/Sql question
Geez, after re-reading my post, it seems that it didn't make much sense to me, so to clarify... I had a cursor in my procedure that took as an IN param a varchar2 variable. The cursor failed to return any rows because in my where clause I was comparing a char field against a varchar2 variable. I then decided to create a local variable of type char and assigned my IN varchar2 variable to the local char variable. Using this in my cursors where clause I was then able to get rows back. There, sounds much better. mkb --- mkb [EMAIL PROTECTED] wrote: um...just a thought but how about setting marketingcode to char(3) in the PL/SQL code snippet. I ran into this similar problem a couple days ago. Had a var as varchar2 in PL/SQL but in the table it was char. Changed my PL/SQL var to char, cursor in my code worked with ltrim and rtrim functions whereas before it wasn't. hth mkb --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS POSITION(208:208) CHAR
RE: Slightly OT: Chart generation tool for db monitoring scripts
Didn't catch the rest of the thread. If it's not been mentioned, I've used gnuplot using files that stored data points to create pretty pictures. hth --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Thanks Ron, Beth, That is the last option. Do we have anything that can be used without a supporting web server? I'd like to run this off my file system. TIA Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, July 29, 2002 2:49 PM To: Multiple recipients of list ORACLE-L scripts out Do a google search for RRD Tool *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2 __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rant
Ok, I need to vent a little. Last week, I was asked to do some tech interviews over the phones for a mid level DBA position. Someone with about 2-3 years experience. I don't consider myself a real smart DBA, nor do I think that I ask particularly tough questions. The questions that I ask potential candidates are soley based on what is on the resume. So I figure if someone has, say, hot backups or SQL tuning on their resumes, I'd expect them to be able to hold a fairly intelligent conversation about these topics. No such luck! What really frustrated me, and what I really want to get out of my system, is that nobody that I talked to, had a real good concept of hot backups. Forget about recovery. I asked each and every candidate who claimed to have done hot backups, just give me a high level overview of how you do a hot backup. Don't care about syntax, just give me the mechanics. The answers I got were completely off base, baffling and frustrating. Some of these folks claimed to have 5 years experience!!! 'Well, we use scripts to do these, so I'm not sure how these are done...' (But it says on your resume you've done this???) 'Oh, I take the tablespace offline, and copy the datafile to tape...' (Unless I'm mistaken, that's not how a hot backup is done, right?) 'Well, I use the export utility, and as the backup starts, it is written to the dump file.' (Huh? What?) 'During this time, everything is written to the redo logs and not to the tablespace...' (You've been reading one of those books, haven't you?) I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. I dunno, perhaps I'm spoilt by being a member of this list? Perhaps I expect every candidate to be as knowledgeable as you guys? Perhaps I'm asking too much? Rant over. Thanks for listening. mkb __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rant
uh...raid. Oh yes, I have a script for that. --- Alan Davey [EMAIL PROTECTED] wrote: So no one responded with, We use raid xx. We don't have to worry about backup/recovery. ;^) -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 7/22/02, mkb [EMAIL PROTECTED] wrote: Ok, I need to vent a little. Last week, I was asked to do some tech interviews over the phones for a mid level DBA position. Someone with about 2-3 years experience. I don't consider myself a real smart DBA, nor do I think that I ask particularly tough questions. The questions that I ask potential candidates are soley based on what is on the resume. So I figure if someone has, say, hot backups or SQL tuning on their resumes, I'd expect them to be able to hold a fairly intelligent conversation about these topics. No such luck! What really frustrated me, and what I really want to get out of my system, is that nobody that I talked to, had a real good concept of hot backups. Forget about recovery. I asked each and every candidate who claimed to have done hot backups, just give me a high level overview of how you do a hot backup. Don't care about syntax, just give me the mechanics. The answers I got were completely off base, baffling and frustrating. Some of these folks claimed to have 5 years experience!!! 'Well, we use scripts to do these, so I'm not sure how these are done...' (But it says on your resume you've done this???) 'Oh, I take the tablespace offline, and copy the datafile to tape...' (Unless I'm mistaken, that's not how a hot backup is done, right?) 'Well, I use the export utility, and as the backup starts, it is written to the dump file.' (Huh? What?) 'During this time, everything is written to the redo logs and not to the tablespace...' (You've been reading one of those books, haven't you?) I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. I dunno, perhaps I'm spoilt by being a member of this list? Perhaps I expect every candidate to be as knowledgeable as you guys? Perhaps I'm asking too much? Rant over. Thanks for listening. mkb __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Rant
Wait, the best one yet I heard last week: 'the redo log in 9i is going away...' (Surely you mean that undo tbsp can be used instead of rollback segment tablespace, right???) Yes I see your point, but thankfully, I don't do this very often. --- [EMAIL PROTECTED] wrote: mkb, Your surprised? Over the last 6 years I've interviewed many a candidate while we added two DBA's to the group. I've gotten a lot of answers like this: Question: How do you create a table? Answer: The developer sends me a script. I run script. Question: How do you shutdown a database? Answer: Turn off the power to the computer. Question: How do you change the block size of a database. Answer: Change it in init.ora, restart database. Question: How do you add a datafile to a tablespace? Answer: You can't. Question: What are archived redo logs? Answer: There is no such thing. BTW: these folks had an OCP certificate. Best answer to a question I've asked: Question: You have a database crash at 6AM, what do you do. Answer: Get a cup of coffee first, then look in recovery manual. We hired the guy, he's still here 2 years later and just recently got his OCP. Dick Goulet Reply Separator Subject:Rant Author: mkb [EMAIL PROTECTED] Date: 7/22/2002 6:58 AM Ok, I need to vent a little. Last week, I was asked to do some tech interviews over the phones for a mid level DBA position. Someone with about 2-3 years experience. I don't consider myself a real smart DBA, nor do I think that I ask particularly tough questions. The questions that I ask potential candidates are soley based on what is on the resume. So I figure if someone has, say, hot backups or SQL tuning on their resumes, I'd expect them to be able to hold a fairly intelligent conversation about these topics. No such luck! What really frustrated me, and what I really want to get out of my system, is that nobody that I talked to, had a real good concept of hot backups. Forget about recovery. I asked each and every candidate who claimed to have done hot backups, just give me a high level overview of how you do a hot backup. Don't care about syntax, just give me the mechanics. The answers I got were completely off base, baffling and frustrating. Some of these folks claimed to have 5 years experience!!! 'Well, we use scripts to do these, so I'm not sure how these are done...' (But it says on your resume you've done this???) 'Oh, I take the tablespace offline, and copy the datafile to tape...' (Unless I'm mistaken, that's not how a hot backup is done, right?) 'Well, I use the export utility, and as the backup starts, it is written to the dump file.' (Huh? What?) 'During this time, everything is written to the redo logs and not to the tablespace...' (You've been reading one of those books, haven't you?) I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. I dunno, perhaps I'm spoilt by being a member of this list? Perhaps I expect every candidate to be as knowledgeable as you guys? Perhaps I'm asking too much? Rant over. Thanks for listening. mkb __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: Rant
Checking the FM (Oracle 9i Backup and Recovery Concepts). Page 4-16 gives a high level overview of the principles and Oracle 9i User Managed Backup and Recovery, chapter 4 gives a detailed description. Scenario - Loss of one or more datafiles (NOT SYSTEM) In archivelog mode Database status open Recovery - Datafiles are taken offline (automaitcally by Oracle if it can't read/write to them). Take affected tablespace(s) offline normal. Restore datafile(s) from most recent backups along with necessary archivelogs. Recover using recover tablespace tbsp1, tbsp2... Bring affected tablespace(s) online alter tablespace tbsp1 online etc. hth Correct me if I'm wrong. mkb --- Vergara, Michael (TEM) [EMAIL PROTECTED] wrote: I read your rant, and I agree with you. But I do have one little itsy bitsy question... I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. How DO you do an online recovery of a datafile while the database is still in use? I've had to do recoveries before, but never this scenario. Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rant
). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A DBA looks at OAS
No books or tips. Just my recent experience *trying* to install 9iAS R2 (I hope that's what you meant when you wrote OAS). I downloaded 9iAS J2EE and Web Cache for Solaris and HP-UX. Oracle recommends about 1GB ram, 1GB swap and lots of free disk space. Anyway, my target Solaris box had 500MB swap, 256MB ram and a 400 MhZ SparcII CPU. Not the ideal platform. On the HP-UX box, I had 3GB ram, a 2 CPU L class machine, lots of swap and lots of disk. In any case, what I learned is that root privs are vital. Had them on the Solaris box but not on the HP-UX machine. The installs in both cases where fairly standard. I had ran through them quite a few times on both servers. On Solaris becuase of resource issues and HP-UX because of root permission issues. There are two types of installs. A mid-tier (less config, easier, fewer components) and an infrastructure (more config, more components, needs a database repoistory). I did the mid-tier install in both cases. Make sure you have JDK 1.3 or later installed. Before the install for mid-tier in particular, export ORACLE_SID=iasdb even if you do not intend to use a repository or have a database. I created a separate ORACLE_HOME for my install. Also, Oracle recommends that you use hostnames, so naming methods should reflect hostname.com instead of 123.45.67.8. During the install, you will be asked for a password for the Eterprise Manager website. NOTE IT DOWN!!! You'll need it to start and stop the EM website. Oracle recommends that you start and stop services via the EM website and not the command line and I'll go along with this since I had trouble shutting down services via the command line (sometime it worked and sometime it did'nt). Also, during the install when prompted to run the root.sh script, run as root since this script starts the Apache httpd daemons. These need to be started as root. It does a bunch of other config things aswell. See root.sh. This is vital since after the install is complete, the installer then configures the components such web cache, OC4J components, Apache config etc. This is the problem I was having on HP-UX, late in the day, govt client, sysadmin has left the building. Ok, after the install has completed and started all the services (hoepfully), you need to apply all relevant patches. For the mid tier install, install the patch in the following order: 9.0.1.3 patch set RDBMS bundled patch Oracle Internet Directory path Oracle HTTP server patch You'll see this in the install notes for the patch. Note that the RDBMS bundled patch is slightly different on HP-UX versus Solaris. Just read the instructions carefully if you are on HP-UX. Solaris was a little easier. After the patch, you can login to the EM website at http://myhostname.com:1810. If the website does not come up, you can start it from the prompt using emctl start|stop|status. Stopping requires password which was entered earlier during install. Password can also be changed using emctl set password pwd. Using the website, you can/start stop other services such as web cache, BC4J, OC4J containers etc. The default website can be accessed (hopefully) at http://myhostname.com: Also, you can start|stop the httpd daemons from the command line from $ORACLE_HOME/dcm/bin/dcmctl start|stop -ct ohs if the EM website is inaccessible for some reason. Again, Oracle recommends that you do all admin through EM the website. Similarly, web cache can be started/stopped from the prompt by webcachectl start|stop|status. Finally, just a couple days ago, we seemed to have trouble starting 9iAS. Seems like some log files had their ownership changed. Don't know how this happened. My guess is some sort of bug. The way I tracked this is tailing the logs while trying to start the server. Since I could'nt get the EM website up, I had to use $ORACLE_HOME/dcm/bin/dcmctl start -ct ohs. Useful logs were: $ORACLE_HOME/opmn/logs/ons.log and ipm.log $ORACLE_HOME/dcm/logs/emd_logs/ and dcmctl_logs/ and of cource $ORACLE_HOME/Apache/Apache/logs/error_log and access_log hth mkb --- Freeman, Robert [EMAIL PROTECTED] wrote: Folks, I'm a DBA who is really an old developer at heart. I know a little Java, a little Asp (enough, as they say, to be really dangerous!), I used to do C code that now makes my head swim to look at. (I think to myself, what the devil was I doing??) Anyway, I've finally found some time and a machine here at work to start playing with OAS just to see what it's all about. Anyone have any good suggestions with regards to: 1. Sites, books, white papers and the like with good install tips, hints, warnings and the like. 2. Sites, books, white papers and the like Quick getting started tips. 3. Your own getting started experiences of things to do or not to do. 5. Anything I should do before I mess with OAS. Optimistically hopeful that this will all just go really smooth and I'll have a cool web page that I
RE: A DBA looks at OAS
Just checked my current config again on Solaris. ps -ef shows me two httpd processes owned by root and 10 that I assume are spawned via these as oracle. Also see 2 dwhttpd running under daemon. I assume it is under port 80 since I have not changed the httpd.conf file. Yes, I guess you are right, these should run under nobody, but these are my first steps into setting this up and so I installed it as-is. No tweaks. mkb --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The httpd daemons need to be started as root? Since when? Is it because you're using the standard HTTP PORT OF 80? Starting these daemons under the nobody account is much safer. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 17, 2002 10:49 AM To: Multiple recipients of list ORACLE-L No books or tips. Just my recent experience *trying* to install 9iAS R2 (I hope that's what you meant when you wrote OAS). I downloaded 9iAS J2EE and Web Cache for Solaris and HP-UX. Oracle recommends about 1GB ram, 1GB swap and lots of free disk space. Anyway, my target Solaris box had 500MB swap, 256MB ram and a 400 MhZ SparcII CPU. Not the ideal platform. On the HP-UX box, I had 3GB ram, a 2 CPU L class machine, lots of swap and lots of disk. In any case, what I learned is that root privs are vital. Had them on the Solaris box but not on the HP-UX machine. The installs in both cases where fairly standard. I had ran through them quite a few times on both servers. On Solaris becuase of resource issues and HP-UX because of root permission issues. There are two types of installs. A mid-tier (less config, easier, fewer components) and an infrastructure (more config, more components, needs a database repoistory). I did the mid-tier install in both cases. Make sure you have JDK 1.3 or later installed. Before the install for mid-tier in particular, export ORACLE_SID=iasdb even if you do not intend to use a repository or have a database. I created a separate ORACLE_HOME for my install. Also, Oracle recommends that you use hostnames, so naming methods should reflect hostname.com instead of 123.45.67.8. During the install, you will be asked for a password for the Eterprise Manager website. NOTE IT DOWN!!! You'll need it to start and stop the EM website. Oracle recommends that you start and stop services via the EM website and not the command line and I'll go along with this since I had trouble shutting down services via the command line (sometime it worked and sometime it did'nt). Also, during the install when prompted to run the root.sh script, run as root since this script starts the Apache httpd daemons. These need to be started as root. It does a bunch of other config things aswell. See root.sh. This is vital since after the install is complete, the installer then configures the components such web cache, OC4J components, Apache config etc. This is the problem I was having on HP-UX, late in the day, govt client, sysadmin has left the building. Ok, after the install has completed and started all the services (hoepfully), you need to apply all relevant patches. For the mid tier install, install the patch in the following order: 9.0.1.3 patch set RDBMS bundled patch Oracle Internet Directory path Oracle HTTP server patch You'll see this in the install notes for the patch. Note that the RDBMS bundled patch is slightly different on HP-UX versus Solaris. Just read the instructions carefully if you are on HP-UX. Solaris was a little easier. After the patch, you can login to the EM website at http://myhostname.com:1810. If the website does not come up, you can start it from the prompt using emctl start|stop|status. Stopping requires password which was entered earlier during install. Password can also be changed using emctl set password pwd. Using the website, you can/start stop other services such as web cache, BC4J, OC4J containers etc. The default website can be accessed (hopefully) at http://myhostname.com: Also, you can start|stop the httpd daemons from the command line from $ORACLE_HOME/dcm/bin/dcmctl start|stop -ct ohs if the EM website is inaccessible for some reason. Again, Oracle recommends that you do all admin through EM the website. Similarly, web cache can be started/stopped from the prompt by webcachectl start|stop|status. Finally, just a couple days ago, we seemed to have trouble starting 9iAS. Seems like some log files had their ownership changed. Don't know how this happened. My guess is some sort of bug. The way I tracked this is tailing the logs while trying to start the server. Since I could'nt get the EM website up, I had to use $ORACLE_HOME/dcm/bin/dcmctl start -ct ohs. Useful logs were: $ORACLE_HOME/opmn/logs/ons.log and ipm.log $ORACLE_HOME/dcm/logs/emd_logs/ and dcmctl_logs
RE: How do I check whether a rollback activity is going on
I was wondering where a list of kill signals (kill -1, kill -9 etc) can be found. I've always used kill -9 but I'd rather try something less drastic first, even though I've never run into problems using kill -9 pid before. man pages don't seem to list the signals (Solaris 7). thanks mkb (hmmmcan you say mutating thread...) --- [EMAIL PROTECTED] wrote: Because 'kill' and 'kill -15' don't seem to work consistently. kill -9 says 'kill with extreme prejudice, take no prisoners!', whereas your garden variety kill allows a process to call for a priest, eat a last meal and smoke a cigarette, all of which take too much time. Jared BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 09:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: How do I check whether a rollback activity is going on Jared, I always kill the Oracle session first. So could you elaborate why you use 'kill -9'. TIA Prakash -Original Message- Sent: Monday, July 15, 2002 8:38 PM To: Multiple recipients of list ORACLE-L It could be that pmon is rolling back the transaction. It could also be that pmon is never going to clean it up, which sometimes happens when killing an active transaction. I am a strong advocate of using 'orakill' on NT/Win2k and 'kill -9' on Unix to kill the process, rather than killing the session in Oracle. If your session has still not been cleaned up, you'll understand my position, as you will now have to bounce your database. Check to see if the session is holding a lock, if so, and an unreasonable amount of time has passed for pmon to do its cleanup, then you likely need to bounce the database. Jared Gurelei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/15/2002 02:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How do I check whether a rollback activity is going on Hi. Here is my situation. I have killed an ORacle transaction. Oracle came back with session marked for kill and the status of the session is now Killed. I presume that the reason is that Oracle needs to roll back the changes made. Can I confirm whether this is indeed the case via some system tables? thakns gene __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from
RE: How do I check whether a rollback activity is going on
Thanks. That clarifies things. Time to look into this some more. mkb --- [EMAIL PROTECTED] wrote: try 'kill -l' where the -l is an ell as in -list. Jared mkb [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 11:41 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: How do I check whether a rollback activity is going on I was wondering where a list of kill signals (kill -1, kill -9 etc) can be found. I've always used kill -9 but I'd rather try something less drastic first, even though I've never run into problems using kill -9 pid before. man pages don't seem to list the signals (Solaris 7). thanks mkb (hmmmcan you say mutating thread...) --- [EMAIL PROTECTED] wrote: Because 'kill' and 'kill -15' don't seem to work consistently. kill -9 says 'kill with extreme prejudice, take no prisoners!', whereas your garden variety kill allows a process to call for a priest, eat a last meal and smoke a cigarette, all of which take too much time. Jared BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 09:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: How do I check whether a rollback activity is going on Jared, I always kill the Oracle session first. So could you elaborate why you use 'kill -9'. TIA Prakash -Original Message- Sent: Monday, July 15, 2002 8:38 PM To: Multiple recipients of list ORACLE-L It could be that pmon is rolling back the transaction. It could also be that pmon is never going to clean it up, which sometimes happens when killing an active transaction. I am a strong advocate of using 'orakill' on NT/Win2k and 'kill -9' on Unix to kill the process, rather than killing the session in Oracle. If your session has still not been cleaned up, you'll understand my position, as you will now have to bounce your database. Check to see if the session is holding a lock, if so, and an unreasonable amount of time has passed for pmon to do its cleanup, then you likely need to bounce the database. Jared Gurelei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/15/2002 02:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How do I check whether a rollback activity is going on Hi. Here is my situation. I have killed an ORacle transaction. Oracle came back with session marked for kill and the status of the session is now Killed. I presume that the reason is that Oracle needs to roll back the changes made. Can I confirm whether this is indeed the case via some system tables? thakns gene __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed === message truncated === __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from
RE: Oracle DBA with TS SCI Clearance Needed in Dayton, Ohio..
Have to add in my 2 cents worth. I last held a Top Secret clearance with the State Dept and it expired sometime in 99 when I left the company that placed me with the State Dept. Basic requirements are that you have to be a US citizen and have a 'clean' background. The TS investigation goes back 7 years and they want you to list positions held, places lived, foreign countries visited, relative information (mother, father...) etc. A TS can only be obtained if you are working directly for the federal government, in which case they sponsor your background check or, a company is willing to hire you, place you in a federal agency, such as the State Deparment, and then sponsors your background check. All background checks are conducted by DISA I believe (Defensive Investigative ??? Agency). During the investigation period, you are given an interim clearance which allows you to enter the federal facility and work on non-classified systems. Back in '97 when I applied for this via the company that I worked for, it took about 6-9 months. I hear it takes at least a year or more nowdays. So, in short, no you can't apply as an individual but only if a company sponsors your or if you are directly hired by a federeal agency. Also note that the company that sponsors you must also have some form of clearance with the government in case you were thinking of opening up a company yourself. hth mkb --- Michael Cupp [EMAIL PROTECTED] wrote: But can they be pursued by a civilian, or do they have to be gov't sponsored? -Original Message- From: KENNETH JANUSZ [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 27, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle DBA with TS SCI Clearance Needed in Dayton, Ohio.. If your background is clean these clearances should be no big deal. When I was driving submarines for a living I had TS, Crypto and COMSUBPAC Special Intelligence clearances. SI is above TS. Ken Janusz, CPIM Former U.S.N. Submariner - Original Message - From: Khedr, Waleed mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Thursday, June 27, 2002 2:30 PM Subject: RE: Oracle DBA with TS SCI Clearance Needed in Dayton, Ohio.. Another certification! -Original Message- From: Jesse W. Asher [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 27, 2002 3:15 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle DBA with TS SCI Clearance Needed in Dayton, Ohio.. Very good question! I've wondered the same thing myself! Michael Cupp wrote: How would one begin the process of getting one? Or does it have to be government sponsored? -Original Message- Sent: Thursday, June 27, 2002 2:35 PM To: Multiple recipients of list ORACLE-L It is my understanding that it currently takes 1.5 to 2 years to get this clearence mainly due to events of 9/11. So, if you have one, it behooves you to keep it current. They used to estimate these clearences were worth an additional $10K a year for salary. Im thinking at least $25K now.Alot of my friends that have these clearances , whether they be DBAs, programmers, Sys. Ads. etc, have standing offers for work in many places around the world due to the fact these clearances are very hard to get. Also, the companies will not foot the bill for this delay. The government usually has to approve all hires where the individual is not cleared. From what I have seen the government is in no mood to do this as of late. FWIW ! Mike -Original Message- Sent: Wednesday, June 26, 2002 4:54 PM
Re: Difference Between DBMS/RDBMS
Nicolai, Thank you very much. Very interesting paper. mkb --- Nicolai Tufar [EMAIL PROTECTED] wrote: Oracle was the first commercial Realtional Database Management System. And it was relational from day one (version two :), and it was built with relational theory in mind. IBM was the first to implement RDBMS though. It was called System R, or something, later it became known as DB2. Take a look at this paper: http://www.mcjones.org/System_R/SQL_Reunion_95/index.html Very fascinating reading. They tell how Larry was trying to get tle list of DB2 error codes so that Oracle would be compatible with it. Also a bit about Larry luring IBM engeneers promising that they would become millionares with Oracle. He was right. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicolai Tufar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: GNU or [G]NU (Pronunciation of...)
Ok, how is this pronounced? Is the G in GNU slient or not? mkb __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WAITS
That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WAITS
Thanks Rajesh. Looks to be a very interesting tool. mkb --- [EMAIL PROTECTED] wrote: Check out the statspack viewer tool at http://www.geocities.com/alexdabr/ mkb mkb125@yahooTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: WAITS root@fatcity. com May 13, 2002 10:03 AM Please respond to ORACLE-L That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: pl/sql is INTERPRETED?
Yes indeed. Have often wondered why Perl is'nt considered cross-platform. After all, is'nt it true to say that it probably runs on way more platforms than Java, can be programmed either straight or OOP, is fast and relatively easy to learn. Did I mention it's free. Gotta love those open source folks. --- [EMAIL PROTECTED] wrote: It's been a year since I took the class, and I *much* prefer Perl. It can run circles around Java for most stuff. Jared Alex [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/02/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql is INTERPRETED? It took you a week to learn it? Then you obviously do not know it. Syntax is one thing design is another. I would love to know what you learned in that week. On Thu, 2 May 2002, Jared Still wrote: Hold on Lisa! Java is not complex. It's a very simple language actually. It took me a week to learn it, though I'm not using it now: I much prefer Perl. Getting a handle on all of the libraries and API's is another story, but Java as a language is pretty simple. Jared On Tuesday 30 April 2002 11:14, Koivu, Lisa wrote: You have a point Chris, but pl/sql is nowhere near as complex as an OO language like java or C++, IMHO. I agree with Tom that pl/sql can be learned fairly easily in comparison to the many other choices out there. However, it takes a bit of database savvy to do it correctly. (Not much tho) I was amazed in my database class in college that the same people failing the simple entity-relationship modeling portion of the class that had aced the Op Systems and networking classes we took. I nearly failed both classes, they were so complex. I was the teacher's pet in the db class because I asked him questions that made him think, and he sometimes couldn't answer. (And I had to wear a skirt - night student, straight from work.) What's easy for who is dependent on the person's strengths. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 1:14 PM To:Multiple recipients of list ORACLE-L Subject: RE: pl/sql is INTERPRETED? IMHO, I don't believe that you can properly learn PL/SQL in a very short period of time, or for that matter, any other language. I attended Steve Feuerstein's presentation at MAOP-AOTC conference, and he tore into many real-life examples of PL/SQL. Supposedly, these were written by developers that knew what they were doing. Granted, if a smart developer sits down and reads Feuerstein's Learning PL/SQL and Best Practices books, then perhaps they will be good. But who the hell has free time? There is no free time on any project or effort that I know of!! I'm struggling with trying to improve my Oracle DBA skills, plus some developers skills so I can speak their language when they blow out OPEN_CURSORS or something. My head is swimming in the stupid technical alphabet soup, XML, XDK, XSQL, XSLT, XPath, SOAP, ASP, ADO, EJB, BC4J, JDBC, SQLJ, PSP, JVM, JSP, J2EE, EAD, RMI, CORBA, IIOP...and don't ask me what all those mean, because I can't keep them straight. But I do keep hearing that XML is going to put me out of a job, so I guess I should learn that...whatever that is. Isn't XML an add-on, or extension, or something to DML??? Now where the heck did I hide that bottle... -Original Message- Sent: Tuesday, April 30, 2002 12:15 PM To: Multiple recipients of list ORACLE-L Lisa, You are right about the debate between PL/SQL Java (or anything else outside of the db). In my mind, the deciding factor (and something that is *never* mentioned) is what programming langauage the organization is satisfied with/settled upon. In my little opinion, *any* programmer can learn PL/SQL in a very short period of time. This means that development and maintenance costs are relatively low. If an IT shop is stronger in Java, then they should probably program in Java, or Cobol, or Ada, or whatever the flavor of the decade happens to be (lets bring back APL!). === message truncated === __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services
Re: No DBAs needed on AS400
It's been a while since I actually worked on a AS400 but from my understanding, DB2 is integrated into OS400, the operating system for the AS400. In the System/36 and System/38 worlds, you used RPG and a bunch of other utilities to create your programs and files. The files were flat files and I think when AS400 came out, this concept was still in place except now the files were stored in a database - DB2. I have seen one application on an AS400. There was no RI, no procs or triggers. The application had its roots in a System/36 system and had been re-written for the AS400. All the logic was in the app and DB2 just stored the data. Yes, you can run a utility (strsql I think) that brings up a screen and you can query the tables stored in the database using plain SQL. You can also bypass the app and directly insert/update/delete data aswell. hth mkb --- Jay Hostetter [EMAIL PROTECTED] wrote: We are going through a merger, and management is looking to eliminate positions. Here is a brief summary of my discussion with the new director of IT: Director: Back when I we were using an AS400, we didn't need a DBA. Me: Then you probably were just using files. Director: No, it was a database. Me: Could you issue SQL commands? Director: Yes. But we didn't need a DBA. I guess it was just one of those mysteries of life. My thoughts are that he is using the term database in the generic sense of the word (our files are our database), or he was using some proprietary database that doesn't even begin to compare to Oracle. For those of you who know AS400s, I would appreciate some insight that would demonstrate why he needs to keep me as a DBA. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: # of datafiles per tablespace
I had the opportunity to work with a very good sys admin. We used raw on an EMC Sym and managed it all with Veritas. We both decided to keep our datafiles no bigger than 1GB regardless of TS size and at least 4 datafiles per TS. We used 36GB drives in our Sym, each divided into 9GB LUNs. Our rational was that we could isolate IO hotspots and move 1GB chunks around quickly and that we'd have a much better chance of finding a LUN available with at least 1GB free. Ofcourse, our DB was much smaller (300GB vs 1TB) but I believe our stratgey worked quite well. I guess you should go with whatever works best for you in your environment. mkb --- Gene Sais [EMAIL PROTECTED] wrote: I use 10gb datafiles for a 1tb db and also back up using Legato. Thinking about using Rman :) [EMAIL PROTECTED] 03/05/02 03:18AM We use 4Gb datafiles here as the norm without any problems at all and those datafiles are all backed up with Legato. No problems whatsoever. Lee -Original Message- Sent: 05 March 2002 03:33 To: Multiple recipients of list ORACLE-L That being said is there anything wrong with having one 4G data file for a tablespace. I personally cannot think of any. There were the days when 2G was the limit but that sure isn't the case anymore. The only thing I can think of is for backups. However, I am always going to backup on at least the tablespace level so if I have one file or multiple files I still need to get them all. I don't know if RMAN has some special feature that turns out it makes sense to backup just one data file of a tablespace that has multiple data files but I sure can't think of any good reason. I just randomly picked RBS but I am seeing the same case on data tablespaces as well. -Original Message- Carmichael Sent: Monday, March 04, 2002 6:29 PM To: Multiple recipients of list ORACLE-L no reason. I can see creating multiple files under those conditions only because you want to keep files to a specific size. Now, I did once find that the rollback datafiles were a bottleneck on a system I had. So we built TWO rollback tablespaces, with datafiles on different mount points etc and the rollback segments divided between the two tablespaces. cleared up that bottleneck like a dream other than that though.. why? --- Kimberly Smith [EMAIL PROTECTED] wrote: OK, I know we had the debate already but lets have another go at it. Say you got a tablespace, lets call it RBS and its for rollbacks. Now, for what reason would you create a 500M file and 4 50M files for this puppy as opposed to just one file. I just cannot see the reasoning for this at all. None. Natta. Zilch. So educate me please if someone out there knows a legit reason they would do this. Lets assume for the sake of argument that disk size and mount point size is not a limitation. Space available to me on any one mount point is unlimited. ___ Kimberly Smith Portland, OR [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list
