Re: sql question
Bear in mind though that the original query will only count rows where b.award_number is not null whereas this new query will count all rows in the result set. Regards, Dave [EMAIL PROTECTED] wrote: Can you change it to this query: SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) eric - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 12, 2004 3:04 PM David - Can you post the EXPLAIN PLAN for both? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 12, 2004 1:14 PM To: Multiple recipients of list ORACLE-L Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed users-be more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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).
kill session privilage
My boss want me to give kill session privilage one of the developer here . He doesn't have any dba privilage to see session or anything . Is there any way I can give likited access to him. Thanks, ak
Re: ** OCP for 9i requirements
At 06:14 PM 1/12/2004, Ryan wrote: www.oracle.com do a search for certification. Its all explained there. You can take an online course for $300. If your company is an oracle partner the course is free. My understanding is that you need to take a class that corresponds to one of the four OCP exams, which are all 5-day classes. The in-classroom versions run $2500 and the on-line versions run $1250. Oracle partners get a 35% discount (advantage and certified advantage partners may get a larger discount). http://www.oracle.com/education/certification/index.html?dba9i_ocpcoursereq.html Justin Cave - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, January 12, 2004 7:34 PM Subject: ** OCP for 9i requirements Hi, For taking Oracle9i OCP exam is it necessary to have attended a Oracle course by Oracle University. What is the minimum? Is any small course good enough? Can someone who has gone through this provide details? Thank you Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
RE: test
-test... We have been having mail problems. I've only had female problems. ;o) Dave -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Monday, January 12, 2004 9:29 PMTo: Multiple recipients of list ORACLE-LSubject: test test... We have been having mail problems. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113
Heap
Hi All, I need some basic understanding of heap structure and its operation. Can anybody give me some useful links in this respect? Thank You Regards, B S Pradhan
RE: Should we stop analyzing?
My explanation is. How is network connectivity priced? By bandwidth or latency. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wolfgang Breitling Sent: 12 January 2004 21:35 To: Multiple recipients of list ORACLE-L Subject: RE: Should we stop analyzing? My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Niall Litchfield 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: kill session privilage
Yes. You can write a procedure using execute immediate and grant him the right to execute the procedure. On 2004.01.13 02:44, AK wrote: My boss want me to give kill session privilage one of the developer here He doesn't have any dba privilage to see session or anything . Is there any way I can give likited access to him. Thanks, ak -- 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).
FW: problems with dbms_sql w/ 9.2.0.4???
Title: Message -Original Message-From: Niall Litchfield [mailto:[EMAIL PROTECTED] Sent: 13 January 2004 11:16To: '[EMAIL PROTECTED]'Subject: RE: problems with dbms_sql w/ 9.2.0.4??? There is a bug 3140063which at first glance looks as if it might be relevant to you. Niall -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: 12 January 2004 21:14To: Multiple recipients of list ORACLE-LSubject: RE:problems with dbms_sql w/ 9.2.0.4??? Developer is saying functions are good - all functions referencing dbms_sql bomb??? Help??? problem with dbms_sql Compiling function FUN_CHANGE_PASSWORD... Compilation error on function FUN_CHANGE_PASSWORD: PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 23, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 24, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 25, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 26, column 7 Statement ignored -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Goulet, DickSent: Monday, January 12, 2004 3:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Yep.it's a Monday.. Yeah, it is a Monday. Vendor shows up to "fix" a minor problem my day gets trashed. Oh well, job justification!! Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Bobak, Mark [mailto:[EMAIL PROTECTED]Sent: Monday, January 12, 2004 11:44 AMTo: Multiple recipients of list ORACLE-LSubject: Yep.it's a Monday.. While doing some SAN work, one of our intrepid Sys Admins unplugged the wrong cable, crashing 20+ production databases at the same time..Yeah, it's a Monday.. So, here I sit, waiting, while they scramble around, re-connecting cables and re-booting boxes..sigh.I have a feeling lunch will be a little late today. At least I wasn't the one who caused the crash..;-) 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
RE: Problem with archive log when testing recovery..., urgent
Well, I think I forget the alter system switch logfile before shutdown and after restart. I'll try that n let you all know the result. Regards, Wendry. -Original Message- Sent: Tuesday, January 13, 2004 6:07 PM To: Wendry Nothing major amiss there, that is exactly what I do. However, remember to do an alter system switch logfile, before shutting the DB, then backup, restart, alter system switch logfile, and take all the logfiles upto and including that created on the second logfile switch. I'd suspect that not switching before shutting down may be the cause, but I'm working on the logic as to why. Let me know how it goes. Cheers GJC The fifty dwarves were reduced to eight, before anyone suspected hungry. __ Gary Colbran System/Database Administrator Telkom SA 55 Oak Avenue Centurian South Africa Ph: 012-680 1315. Ph: 082-786 6592. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] **Disclaimer** ** Information contained in this E-MAIL being proprietary to Telkom SA and is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ** * -Original Message- Sent: 12 January 2004 16:09 To: Gary Colbran (GJ); Multiple recipients of list ORACLE-L Thank you for your reply, Yes, you're quite right, I think the archive next to the cold backup is corrupted. The hot backup is always succeeded. But I wonder why is my cold backup is largely end up with internal error or corrupted archive log files. All I do for closed backup is shutdown immediate, copy all controlfiles, datafiles and redo log files to backup destination, and then reopen the database. Is there any steps that I left which cause the archive log to be corrupted? Thank you in advance. Regards, Wendry. -Original Message- Sent: Monday, January 12, 2004 12:36 PM To: Wendry In which order did you perform the backups ? Say you do the cold backup, and the next archive is corrupted. You then do a hot backup. Restore the cold backup and the archive needs to be applied. Restore the hot backup, and it is after the corrupted archive. Does the corruption occur with an archive used by the cold backup, but not the hot backup ? Cheers GJC The fifty dwarves were reduced to eight, before anyone suspected hungry. __ Gary Colbran System/Database Administrator Telkom SA 55 Oak Avenue Centurian South Africa Ph: 012-680 1315. Ph: 082-786 6592. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] **Disclaimer** ** Information contained in this E-MAIL being proprietary to Telkom SA and is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ** * -Original Message- Sent: 16 January 2004 03:16 To: LazyDBA.com Discussion Hi all, I have done closed and open backup on my database. Later on I try to test my backup. So I try the closed backup that I've taken. The backup database can be opened succesfully. But when I try to recover database using backup controlfile, the archive logs giving me internal error (sometimes after applying 1st to 5th archive log), sometimes it gave me notification that the archive logs is corrupted. Strangely when I test my open backup, and recover it using the same set of archive logs, the process went smoothly. So what is the real problem here, I really don't have any idea. Is there somebody have the same experience? Now I'm in doubt of planning my backup schedule, please help... Thanks a lot. Regards, Wendry. Get today's cartoon: http://www.LazyDBA.com Please don't reply to RTFM questions Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry 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
RE: Should we stop analyzing?
We have the occasional network issue from Perth to Port Hedland (both in Western Australia, with Port Hedland being a couple of thousand km's north of Perth). When management phone up, I always reply with: Have you looked at a map? See how far north Port Hedland is...that's all uphill you know! Never fails to amaze me how many will nod in agreement... Cheers Connor --- Wolfgang Breitling [EMAIL PROTECTED] wrote: My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: kill session privilege
Rrright ... I'd go a step forward and make sure that only sessions the developer kills is USER type sessions and the procedure REMAINS in development environment only. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, January 13, 2004 6:54 AM To: Multiple recipients of list ORACLE-L Yes. You can write a procedure using execute immediate and grant him the right to execute the procedure. On 2004.01.13 02:44, AK wrote: My boss want me to give kill session privilage one of the developer here He doesn't have any dba privilage to see session or anything . Is there any way I can give likited access to him. Thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Cold Fusion and Bind Variables
Jared, Try this link. It still worked for me this morning. http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html Scott [EMAIL PROTECTED] 1/12/04 8:49:25 PM Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
RE: Cold Fusion and Bind Variables
Hi Jared, Look at QFQUERYPARM: Usage The CFQUERYPARAM is designed to do the following things: Allows the use of SQL bind parameters. Allows long text fields to be updated from an SQL statement. Improves performance. The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 driverssupport SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters. If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. Suzy -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Monday, January 12, 2004 6:49 PMTo: Multiple recipients of list ORACLE-LSubject: Cold Fusion and Bind VariablesDear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
Re: kill session privilege
Raj, you're right, as usual. On 01/13/2004 08:04:25 AM, Jamadagni, Rajendra wrote: Rrright ... I'd go a step forward and make sure that only sessions the developer kills is USER type sessions and the procedure REMAINS in development environment only. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, January 13, 2004 6:54 AM To: Multiple recipients of list ORACLE-L Yes. You can write a procedure using execute immediate and grant him the right to execute the procedure. On 2004.01.13 02:44, AK wrote: My boss want me to give kill session privilage one of the developer here He doesn't have any dba privilage to see session or anything . Is there any way I can give likited access to him. Thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). -- 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).
RE: problems with dbms_sql w/ 9.2.0.4???
Title: Message You nailed it - you might want to see the info. about it - least it happens to you. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Niall LitchfieldSent: Tuesday, January 13, 2004 7:14 AMTo: Multiple recipients of list ORACLE-LSubject: FW: problems with dbms_sql w/ 9.2.0.4??? -Original Message-From: Niall Litchfield [mailto:[EMAIL PROTECTED] Sent: 13 January 2004 11:16To: '[EMAIL PROTECTED]'Subject: RE: problems with dbms_sql w/ 9.2.0.4??? There is a bug 3140063which at first glance looks as if it might be relevant to you. Niall -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: 12 January 2004 21:14To: Multiple recipients of list ORACLE-LSubject: RE:problems with dbms_sql w/ 9.2.0.4??? Developer is saying functions are good - all functions referencing dbms_sql bomb??? Help??? problem with dbms_sql Compiling function FUN_CHANGE_PASSWORD... Compilation error on function FUN_CHANGE_PASSWORD: PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 23, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 24, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 25, column 7 Statement ignored PL/SQL ERROR 801 at line 1, column 1 internal error [1907] PL/SQL ERROR 0 at line 26, column 7 Statement ignored -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Goulet, DickSent: Monday, January 12, 2004 3:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Yep.it's a Monday.. Yeah, it is a Monday. Vendor shows up to "fix" a minor problem my day gets trashed. Oh well, job justification!! Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Bobak, Mark [mailto:[EMAIL PROTECTED]Sent: Monday, January 12, 2004 11:44 AMTo: Multiple recipients of list ORACLE-LSubject: Yep.it's a Monday.. While doing some SAN work, one of our intrepid Sys Admins unplugged the wrong cable, crashing 20+ production databases at the same time..Yeah, it's a Monday.. So, here I sit, waiting, while they scramble around, re-connecting cables and re-booting boxes..sigh.I have a feeling lunch will be a little late today. At least I wasn't the one who caused the crash..;-) 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
Re: Disk capacity planning
Hi! I don't remember any documents other than Oracle documentation by heart, but would like to make one point here: Disk capacity planning is not only predicting how many Mega/Giga/Terabytes you'll need in certain point of time, planning also includes requires IOPS prediction, also IO throughput requirements for your SAN or storagearray and so on. Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 8:29 AM Subject: Disk capacity planning Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel
Re: ** OCP for 9i requirements
Are you sure that the on-line version qualifies as fulfilling the pre-requisite ? I thought that the pre-requisite is at least one Instructor Led Training. Hemant At 12:04 AM 13-01-04 -0800, you wrote: At 06:14 PM 1/12/2004, Ryan wrote: www.oracle.com do a search for certification. Its all explained there. You can take an online course for $300. If your company is an oracle partner the course is free. My understanding is that you need to take a class that corresponds to one of the four OCP exams, which are all 5-day classes. The in-classroom versions run $2500 and the on-line versions run $1250. Oracle partners get a 35% discount (advantage and certified advantage partners may get a larger discount). http://www.oracle.com/education/certification/index.html?dba9i_ocpcoursereq.html Justin Cave - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, January 12, 2004 7:34 PM Subject: ** OCP for 9i requirements Hi, For taking Oracle9i OCP exam is it necessary to have attended a Oracle course by Oracle University. What is the minimum? Is any small course good enough? Can someone who has gone through this provide details? Thank you Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 05-Jan-04} -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
snmp from Oracle?
A bit off the wall this one... Anyone ever tried to monitor other devices on the network from a pl/sql or java package using snmp? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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).
Table access
I am looking for a script that I can supply a table name and it returns all users that have access to it (either directly, thru system priveleges or thru roles) and what the access is. Does anybody have something like this that I can use? Thanks American Express made the following annotations on 01/13/2004 08:16:14 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
Re: Heap
In last few weeks, there have been several discussions about Oracle's memory management heap structures as well. Search the archives, visit www.ixora.com.au , and read Steve's book. Tanel. - Original Message - From: bhabani s pradhan To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 12:59 PM Subject: Heap Hi All,I need some basic understanding of heap structure and its operation.Can anybody give me some useful links in this respect?Thank YouRegards,B S Pradhan
resend: Re: Disk capacity planning
is the list smart enough to block my posts made after 2 am? Pd --- Paul Drake [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Hi everyone! Can anybody point me to any good documentation regarding disk capacity planning? Sharing your experience or approach will also give me so much help. I'd like to know other people's approach on forecasting the growth of their databases particularly on determining the (growth) rate of disk space usage and on deciding when to add and how many disk to add on an Oracle server. Thanks in advance. Best Regards, Rhojel Hi Rhojel, http://www.baarf.com not organized, just a brain dump. use a UPS and configure it to properly shutdown the instance should it be running out of power. use battery backed-up cache (NVRAM) on your host-based RAID controllers. replace or recharge the battery as perscribed in the manual (or you'll be testing media recovery in an unscheduled fashion). enable write-back caching on your host-based RAID controller (if you have satisfied the above conditions). Base the number of disks on the number of IOPS (independent operations per second) the storage subsystem must handle in steady state, and in peak usage, and on the response time that exists in the service level agreement with your users. It will be very likely that you will not be constrained on space, but on controller channels and physical hard drives (e.g. having 24 x 36 GB hard drives, but only having an 18 GB database - I am not kidding). Spread the disks out across controllers and controller channels such that controller bandwidth is not the limiting factor. Spread controllers across PCI-X bus channels. use dedicated drives for online redo logs. At least have 2 redo log members per group on separate controllers. Provided that one member per group is still accessible, you can take a hit in terms of losing a volume and your instance(s) will still be up. Don't use more than 50% of the space of your RAID volumes for live files. Don't even create filesystems for live files on the remaining 50%, or use them for staging backup sets or for trial recoveries. This will keep your mean seek time down, reducing latency. better yet, don't format them at all. Beware of Network Admins chanting iSCSI that want to store their files on your unused space. If need be, create bogus tablespaces with say 1 GB datafiles to consume the excess space. drop them. when its time to add a datafile, re-use the bogus datafiles. in the meantime, the storage reports from the OS won't turn up loads of free space. If your SysAdmins are crafty and will see through this ploy, create raw volumes on the unused space and label them in a very cryptic fashion, or say that they are for when you are going to partition the large fact tables. use dedicated drives for your archived redo logs. use a stripe size that is a multiple of your operating system max IO size. set your db file multiblock read count accordingly. don't set it too high, as the CBO will think its time to chow down and grab blocks by the 32 pack instead of using that index that the developer intended. have space for cloned databases for testing new application updates, new oracle server software releases, on disk backup sets, uncompressed archived redo logs since your last full backup, logical dumps, copies of binaries and patch sets. All of these can help reduce mean time to recovery in the event that you need to perform media recovery or re-install the database server software. if you intend to use dbms_flashback or select AS OF ... use dedicated drives for your UNDO tablespace. use tape for getting backup sets offsite. recover from local storage. use dedicated drives for your TEMPORARY tablespace's temp files, unless you have so much memory that you don't sort to disk. Remember that global temporary tables and hash joins can write out to TEMP also. segregate files on RAID volumes based upon access methods. If you want an agressive read-ahead algorithm to be used for full table scans, don't store index data files there that are only accessed a block at a time. stripe and mirror everything if you can. even numbers are best, multiples of 2 work well with Oracle block sizes (4 or 8 disk RAID 10 volume). by now, 24 drives looks about halfway there, doesn't it? My point is, that if you have enough drives to keep your CPUs well fed, you likely won't have any space concerns. it is customary to include your server OS and Oracle version. as this is a storage question, you might want to include the storage system manufacturer if you are not going with direct attached storage. Based upon someone's posting about his monday morning having 20 database instances crashed due to a sysadmin pulling a cable, use redundant data paths if possible. If you are connecting via Fibrechannel to
RE: Heap
I know that Steve's site hasn't really been updated since 8i (8.1.6???). Are the Internals still applicable for 9i and 10gee? Steve's book, Oracle8i Internal Services, is great. Helped me understand how to defeat ORA-4031s, even though I really need to go back and refresh... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, January 13, 2004 8:40 AM To: Multiple recipients of list ORACLE-L In last few weeks, there have been several discussions about Oracle's memory management heap structures as well. Search the archives, visit www.ixora.com.au , and read Steve's book. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 12:59 PM Hi All, I need some basic understanding of heap structure and its operation. Can anybody give me some useful links in this respect? Thank You Regards, B S Pradhan -- 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).
Read Only TBS Backup Confirmation
ENV: - AIX 4.3.3 - Oracle 8.1.7 I have one tablespace which is read only.Can I just copy the datafiles from a Read Only tablespace for a valid backup? For the rest of the db, I backup hot or cold. Is anyone copying Read Only Tablespace datafiles for backups? I haven't found this method officially supported by Oracle, but have read that is safe to copy Read Only datafiles (makes sense), just wondering if anyone is doing it? Thanks, Gene
RE: Re: A STRANGE QUERY
At first stab...I would guess that there is something foobarred with the primary key index. I would rebuild the primary key and try again. Brad O. -Original Message- Sent: Monday, January 12, 2004 4:45 PM To: Multiple recipients of list ORACLE-L It is not an expensive query.It runs really fast without the primary key in production but we dont have this problem in the test instance. -- Original Message Date: Mon, 12 Jan 2004 14:04:42 -0800 Even stranger is, that you expect us to solve your problem without knowing what exactly the problem is! Does your query consist of a SQL statement? Does it have an execution plan? Very strange, indeed. Tanel. Dear List, I have a very strange query: The table, data, indexes, constraints are set up exactly same The query was running ok in the test database but paused the production system. It is also running ok in production if the primary key disabled. Any ideas? Any input will be greatly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager 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: Odland, Brad 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: snmp from Oracle?
there are some open source implementations of java snmp you might be able to load into the database and utilize. Google on Java SNMP Package. It looks interesting. Brad O. -Original Message- Sent: Tuesday, January 13, 2004 9:09 AM To: Multiple recipients of list ORACLE-L A bit off the wall this one... Anyone ever tried to monitor other devices on the network from a pl/sql or java package using snmp? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: Odland, Brad 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).
Oracle8i on Linux
Hi, I have 256mb RAM,60GB of hard disk,P3 MACHINE.I'm having installation of oracle 817 on linux 7.2. I got error during oracle relinking.DOes any one have such problem earlier? Let me know if some one had such installation in past.ANy thoughts are most welcome. thx -Seema _ Check out the new MSN 9 Dial-up fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Cold Fusion and Bind Variables
Thanks Suzy, CFQUERYPARM is what he needs. Jared Vordos, Suzy [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/13/2004 05:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cold Fusion and Bind Variables Hi Jared, Look at QFQUERYPARM: Usage The CFQUERYPARAM is designed to do the following things: Allows the use of SQL bind parameters. Allows long text fields to be updated from an SQL statement. Improves performance. The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters. If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. Suzy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED] Sent: Monday, January 12, 2004 6:49 PM To: Multiple recipients of list ORACLE-L Subject: Cold Fusion and Bind Variables Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
Shared Pool fragmentation
Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Re: Disk capacity planning
Hi Rhojel, You've had some responses that go beyond your forecasting question. I'll go the simple-minded route here. Maybe there is some canned software out there that will do it but it seems vendors stay away from forecasting future growth. Maybe this is to avoid being held accountable for faulty results (probably shouldn't trust my answer either...). Anyway, I was taught that the first rule of forecasting is that the forecast will be wrong. This doesn't mean you shouldn't forecast the growth of your database. It just means that you need to follow your forecast on a regular basis and note variances (ie: significant changes to growth rates) and ask questions. A spike in growth can be a development boo-boo, new functionality, growth in business, or something else. A simple approach is to track (at least monthly...depending on your comfort zone) the physical size of tablespaces and actual data and calculate net changes in size between your forecasting periods (eg: Month). Summarize this and calculate the average rate over time (eg: Year). Given the average rate and the last rate, you can forecast your growth using both rates over some horizon (1 year? 2 years?) and compare it to your available diskspace. You should be able to identify when you'll run out...but don't assume this will be correct. Also track significant events that may have driven growth. And if there is a major difference between the average growth rate and the last growth rate...it's analysis time. There are fancier forecasting methods like exponential smoothing and so on but the simple approach might get you started. But you need to keep testing results to see if your situation has changed. The above would be a fairly simple spreadsheet. Kip Bryant |Hi everyone! |Can anybody point me to any good documentation regarding disk capacity |planning? Sharing your experience or approach will also give me so much |help. I'd like to know other people's approach on forecasting the growth |of their databases particularly on determining the (growth) rate of disk |space usage and on deciding when to add and how many disk to add on an |Oracle server. |Thanks in advance. |Best Regards, |Rhojel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Should we stop analyzing?
How do you know they're nodding if they call you on the phone? Distinct rattling sound? :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:59 PM We have the occasional network issue from Perth to Port Hedland (both in Western Australia, with Port Hedland being a couple of thousand km's north of Perth). When management phone up, I always reply with: Have you looked at a map? See how far north Port Hedland is...that's all uphill you know! Never fails to amaze me how many will nod in agreement... Cheers Connor --- Wolfgang Breitling [EMAIL PROTECTED] wrote: My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Josepsson 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: ** OCP for 9i requirements
why dont you just read what is on the oracle website? www.oracle.com do a search for certification. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 9:34 AM Subject: Re: ** OCP for 9i requirements Are you sure that the on-line version qualifies as fulfilling the pre-requisite ? I thought thatthe pre-requisite is "at least one Instructor Led Training".HemantAt 12:04 AM 13-01-04 -0800, you wrote: At 06:14 PM 1/12/2004, Ryan wrote: www.oracle.comdo a search for certification. Its all explained there. You can take an online course for $300. If your company is an oracle partner the course is free. My understanding is that you need to take a class that corresponds to one of the four OCP exams, which are all 5-day classes. The in-classroom versions run $2500 and the on-line versions run $1250. Oracle partners get a 35% discount (advantage and certified advantage partners may get a larger discount).http://www.oracle.com/education/certification/index.html?dba9i_ocpcoursereq.htmlJustin Cave - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, January 12, 2004 7:34 PM Subject: ** OCP for 9i requirements Hi, For taking Oracle9i OCP exam is it necessary to have attended a Oracle course by Oracle University. What is the minimum? Is any small course good enough? Can someone who has gone through this provide details? Thank you Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes Hemant K ChitaleOracle 9i Database Administrator Certified Professionalhttp://hkchital.tripod.com {last updated 05-Jan-04}-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Oracle8i on Linux
Seema, Try this site. While it is about 9i, some of the same issues will appear in the 8.1.x install. http://www.puschitz.com/InstallingOracle9i.shtml Also, go to www.google.com/linux and search for +oracle +error message and look through the responses. HTH Stephen [EMAIL PROTECTED] 01/13/04 10:04AM Hi, I have 256mb RAM,60GB of hard disk,P3 MACHINE.I'm having installation of oracle 817 on linux 7.2. I got error during oracle relinking.DOes any one have such problem earlier? Let me know if some one had such installation in past.ANy thoughts are most welcome. thx -Seema _ Check out the new MSN 9 Dial-up fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Shared Pool fragmentation
On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
SQL*Net bytes per session over time
Friends -- My pesky users want to be able to see how many bytes are flying over the wire by process given a start time and end time... here's what I had that they want to use as a basis going forward... select osuser,username,terminal,program,name,value from ( select osuser,username, terminal,program,name,value from v$session a, v$sesstat b, v$statname c where a.sid=b.sid and b.statistic#=c.statistic# and c.name like '%SQL*N%' and osuser=decode(lower('user'),'all',osuser,lower('user')) and program is not null and value != 0 order by value desc ) where rownum = 10 / Now, given that they want to pass in a start time and end time, seems to me that they want historical information that v$session just isn't going to give me... and, aside from logon_time, v$session isn't going to give me any time at all. Is what they're asking for possible? Is there some table somewhere that tracks SQL*Net activity at this level with timestamps embedded? aTdHvAaNnKcSe! Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi 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).
Background parameter adjustment
Hi DBAs, 2-3 days ago, I decided to play with dynamic modification of SGA parameters on Oracle9.2 I reduced my Shared_pool_size and since then my CKPT background process is taking 50% of my CPUs. In my Statspack report, I see a big wait event called Background parameter adjustment Did that happen to you? Thank you Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- 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).
SQL Query Problem(possilble duplicate send, Sorry!)
Hello all,I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %.Here is the query:select "COUNTRY", count ("MSS") "COUNT_MSS"FROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY)And the output:COUNTRY COUNT_MSSAUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC 1 DENMARK 4 ENGLAND 10 46Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement?As always thanks so much!Viktor Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: Shared Pool fragmentation
dba_object_size.parsed_size Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 1:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Shared Pool fragmentation On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
data file permissions --
Hello All, I have a question on data file permissions. When i add a new data file, it gets created as -rw-r- Umask for oracle user is 022. There is a unix user who wants read access to the data files since they are read by fastunload process (syncsort). When i do a touch on any file in that same directory the permission reads as -rw-r--r-- which coincides with the umask set. Could someone please tell me how the data files get -rw-r and NOT -rw-r--r--. Oracle file permission reads -rwsr-s--x Thank You, Sathish. -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select COUNTRY, count (MSS) COUNT_MSS FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRYCOUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC1 DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor Will this work? (ran a simple test case that worked, although this may not): select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pct from ( select COUNTRY, count (MSS) over (partition by MSS) COUNT_MSS, count(MSS) over () overall_total FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY)) (not sure how it will react to a 'group by rollup..' though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson 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: kill session privilage
First question for the boss, WHY? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 2:44 AMTo: Multiple recipients of list ORACLE-LSubject: kill session privilage My boss want me to give kill session privilage one of the developer here . He doesn't have any dba privilage to see session or anything . Is there any way I can give likited access to him. Thanks, ak
Re: Shared Pool fragmentation
dba_object_size only shows some fixed calculations from stored code (how big is the code segment etc..), but it doesn't show how much memory a loaded object actually uses in shared pool. If you want to know library cache usage, use v$db_object_cache or x$kglob directly. If you want to know shared pool usage, then you have to start taking shared pool heapdumps Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 11:09 PM Subject: RE: Shared Pool fragmentation dba_object_size.parsed_size Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 1:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Shared Pool fragmentation On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Thanks for your reply! Will try it now. Will this work in 8i? Viktor Bricklen Anderson [EMAIL PROTECTED] wrote: Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select "COUNTRY", count ("MSS") "COUNT_MSS" FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID", NVL(a.country,'USA') "COUNTRY" FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id ! and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRY COUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC 1 ! DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor Will this work? (ran a simple test case that worked, although this may not):select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pctfrom (select "COUNTRY",count ("MSS") over (partition by "MSS") "COUNT_MSS",count("MSS") over () overall_totalFROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003'or m.second_return between '01/01/2003 and '12/31/2003'or m.second_return! between '01/01/2003 and '12/31/2003')and ms.journal_id = j.journal_idand M.MSNUMBER_JCO! DE = ms.journal_idand M.MSNUMBER_YRISSUE = ms.yr_of_issueand M.MSNUMBER_MS_SEQNO = ms.ms_sequence_noand M.MSNUMBER_CKCHAR = ms.check_charand m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)and a.addtype_addrstyp in ('m', 'p')and a.addtype_typeno = 1and (a.addr_end_date is nullor a.addr_end_date sysdate)and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY))(not sure how it will react to a 'group by rollup..' though.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bricklen AndersonINET: [EMAIL PROTECTED]Fat C! ity Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: data file permissions --
Oracle data files are read BY ORACLE SERVER PROCESSES ONLY! The only unloader that reads files directly is the notorious DUL. I haven't had any experiences with DUL, but according to what I know, I wouldn't even want to have it. Now there is a system call named chmod and oracle takes care that only oracle RDBMS can access files. Nobody but the DBA can even sniff database files. Please, execute that user. On 01/13/2004 04:19:25 PM, [EMAIL PROTECTED] wrote: Hello All, I have a question on data file permissions. When i add a new data file, it gets created as -rw-r- Umask for oracle user is 022. There is a unix user who wants read access to the data files since they are read by fastunload process (syncsort). When i do a touch on any file in that same directory the permission reads as -rw-r--r-- which coincides with the umask set. Could someone please tell me how the data files get -rw-r and NOT -rw-r--r--. Oracle file permission reads -rwsr-s--x Thank You, Sathish. -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- 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). -- 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).
Re: Table access
Tracy Rahmlow wrote: I am looking for a script that I can supply a table name and it returns all users that have access to it (either directly, thru system priveleges or thru roles) and what the access is. Does anybody have something like this that I can use? Thanks American Express made the following annotations on 01/13/2004 08:16:14 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == Not exactly what you request, but pretty close ... rem = rem remwhocan.sql rem rem Copyright (C) Oriole Software, 2003 rem rem Downloaded from http://www.oriolecorp.com rem rem This script for Oracle database administration is free software; you rem can redistribute it and/or modify it under the terms of the GNU General rem Public License as published by the Free Software Foundation; either rem version 2 of the License, or any later version. rem rem This script is distributed in the hope that it will be useful, rem but WITHOUT ANY WARRANTY; without even the implied warranty of rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the rem GNU General Public License for more details. rem rem You should have received a copy of the GNU General Public License rem along with this program; if not, write to the Free Software rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. rem rem = -- -- This scripts allows you to check who can either SEE (i.e. SELECT from) -- or MODIFY (INSERT, UPDATE or DELETE) a given table or view. -- This is fairly easy to check when the right was directly granted, much -- less when rights are inherited through roles. -- -- Check your sensitive data ... -- -- Usage : @whocan SEE|MODIFY [owner.]tablename -- -- Example : @whocan see scott.emp -- -- No row returned means that the object doesn't exist (SYS and SYSTEM -- could otherwise access it). If no owner is specified, it defaults -- to the current schema. -- -- For DBAs only, as usual. -- -- - -- set verify off select u.name USERNAME from sys.user$ u where u.type# = 1 and (exists (select null from sys.sysauth$ where privilege# in (select privilege from sys.system_privilege_map where (upper('1') = 'SEE' and name = 'SELECT ANY TABLE') or (upper('1') = 'MODIFY' and name in ('INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE'))) connect by grantee# = prior privilege# start with grantee# = u.user# union all select null from sys.objauth$ where privilege# in (select privilege from sys.table_privilege_map where (upper('1') = 'SEE' and name = 'SELECT') or (upper('1') = 'MODIFY' and name in ('INSERT', 'UPDATE', 'DELETE'))) and obj# = (select o.obj# from sys.obj$ o, sys.user$ u where o.owner# = u.user# and u.name = decode(instr('2', '.'), 0, sys_context('USERENV', 'CURRENT_SCHEMA'), upper(substr('2', 1, instr('2','.')-1))) and o.name = decode(instr('2', '.'), 0, upper('2'),
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote: Thanks for your reply! Will try it now. Will this work in 8i? Viktor If it works at all, then it should work in both 8i and 9i, although I don't have a version of 8i handy right now to try this on. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson 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: Shared Pool fragmentation
Rick, I think the best answer is 'know thy application'. And in this, knowledge of bind var vs hardcoded value usage, looking at V$SQL and V$SQLAREA, the ratio (!!) of 'parse count (hard)' to 'parse count (total)', pinning of packages/sequences, etc., can help... You cannot actually 'catch' a 4031 before it occurs, but you can always straighten things out before it occurs. I have found that a combination of pinning Packages/Sequences followed by judicious (once in a while) use of shared pool flush helps. Of course, the shared pool has to be correctly sized - too much and you waste time latching and memory, too little and you _might_ run into 4031. Sizing shared pool is an art that has a little science behind it - science that involves understanding and using values from X$KGLOB and X$KSMSP and your application OTOH, I have seen good results with a flush shared pool during quiet times for non-bind hungry 3rd party apps... See below (script courtersy Steve!) - the number of chunks has dropped dramatically freeing up largish globs of shared pool that would otherwise have to be freed up when a largish object (in this case 15456 bytes) has to load. As well, you will see that the number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down drastically as the system frees up 'freeable' chunks ahead of time, reducing the chance of 4031s My (very limited) understanding is that when a package/cursor has to load and a large-enough chunk of shared pool memory is not free, then the kernel will try and flush out the 'freeable' (not in use) memory and merge adjacent free chunks. If this still does not staisfy the memory requirements, then a 4031 is signalled/ The 'alter system flush shared pool' performs a manual flush instead, ahead of time and could (possibly) prevent a 4031 ... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** 08:35:00 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 01089784 23488 46 76 1 3941364656 84140 2 6812843678 185268 3 315504 875 360524 449019527300 671 1036 561588964099 1502 2060 655465161966 2821 4048 71125720 263 4280 7624 8 989584 101 9797 15456 9 rows selected. 08:35:29 SQL alter system flush shared_pool; System altered. 08:36:32 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 0 14364 330 43 76 1 6528 76 85140 6 3964 1 3964 3964 9 29580 129580 29580 105028636 10348821 65436 11 13860744 15092404 130872 12 32192980 173 186086 261016 13 64490864 172 374946 522764 14 83609184 112 7465101048432 15 79829220 57 14005122068384 16 38149220 14 27249443705320 11 rows selected. -Original Message- Sent: Tuesday, January 13, 2004 9:34 AM To: Multiple recipients of list ORACLE-L Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL
RE: Shared Pool fragmentation
Rick, I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. John -Original Message- From: John Kanagaraj [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Shared Pool fragmentation Rick, I think the best answer is 'know thy application'. And in this, knowledge of bind var vs hardcoded value usage, looking at V$SQL and V$SQLAREA, the ratio (!!) of 'parse count (hard)' to 'parse count (total)', pinning of packages/sequences, etc., can help... You cannot actually 'catch' a 4031 before it occurs, but you can always straighten things out before it occurs. I have found that a combination of pinning Packages/Sequences followed by judicious (once in a while) use of shared pool flush helps. Of course, the shared pool has to be correctly sized - too much and you waste time latching and memory, too little and you _might_ run into 4031. Sizing shared pool is an art that has a little science behind it - science that involves understanding and using values from X$KGLOB and X$KSMSP and your application OTOH, I have seen good results with a flush shared pool during quiet times for non-bind hungry 3rd party apps... See below (script courtersy Steve!) - the number of chunks has dropped dramatically freeing up largish globs of shared pool that would otherwise have to be freed up when a largish object (in this case 15456 bytes) has to load. As well, you will see that the number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down drastically as the system frees up 'freeable' chunks ahead of time, reducing the chance of 4031s My (very limited) understanding is that when a package/cursor has to load and a large-enough chunk of shared pool memory is not free, then the kernel will try and flush out the 'freeable' (not in use) memory and merge adjacent free chunks. If this still does not staisfy the memory requirements, then a 4031 is signalled/ The 'alter system flush shared pool' performs a manual flush instead, ahead of time and could (possibly) prevent a 4031 ... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** 08:35:00 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 01089784 23488 46 76 1 3941364656 84140 2 6812843678 185268 3 315504 875 360524 449019527300 671 1036 561588964099 1502 2060 655465161966 2821 4048 71125720 263 4280 7624 8 989584 101 9797 15456 9 rows selected. 08:35:29 SQL alter system flush shared_pool; System altered. 08:36:32 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 0 14364 330 43 76 1 6528 76 85140 6 3964 1 3964 3964 9 29580 129580 29580 105028636 10348821 65436 11 13860744 15092404 130872 12 32192980 173 186086 261016 13 64490864 172 374946 522764 14 83609184 112 7465101048432 15 79829220 57 14005122068384 16 38149220 14 27249443705320 11 rows selected. -Original Message- Sent: Tuesday, January 13, 2004 9:34 AM To: Multiple recipients of list ORACLE-L Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received
RE: oracle client on PC's
Jeffrey, I've probably missed something, but if the application uses Merant's ODBC driver - which I believe is a wire-protocol driver, how come you need to install the client at all? Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 7 January 2004 7:14 AM The application is a third-party application using Merant's ODBC driver. How do people normally install the client. Do you do an install to every workstation??? [EMAIL PROTECTED] 1/6/04 2:54:34 PM At 11:39 AM 1/6/2004, Jeffrey Beckstrom wrote: Rather than installing the Oracle client on every client PC, we have been: - installing client on 1 PC - copying directory to a network server - extract the registry for oracle key - fix registry that was extracted to reference the network drive - load registry on client PCs - add the network pc as a search drive to the client pc. We are now experiencing problems over the WAN and looking at ways to eliminate the Oracle dll overhead. Short of installing Oracle on every client PC, what are our options? There is a reason that Oracle doesn't support configurations like this. There is a fair amount of chatter between an application and the Oracle client DLL's. When this chatter starts flying over the network rather than merely going to a local DLL, you start to get performance problems. How are your application(s) designed? It's probably possible to tweak an OCI application to make fewer OCI calls. If you're using ODBC, and have the budget, you could purchase one of the wire-protocol ODBC drivers. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
Import foibles
My apologies if there are multiple copies of this. Something funky going on with email from work. First, the basics: System Configuration: Sun Microsystems sun4u SUN Enterprise 420R (2 X UltraSPARC-II 450MHz) System clock frequency: 113 MHz Memory size: 1024 Megabytes = CPUs = Run Ecache CPUCPU Brd CPU Module MHz MBImpl. Mask --- --- --- - -- -- 0 2 2 450 4.0 US-II10.0 0 3 3 450 4.0 US-II10.0 The CPU's are about 95% idle. Not much memory paging activity. The disk is (gasp!) a single RAID 5 volume. As this is a mostly read system, it (usually) doesn't matter. Oracle is 8.1.7.2 Doing an import into the database with the following script: imp userid=$USERNAME/[EMAIL PROTECTED] \ file=/u03/tmp/${OWNER}_dv01.dmp \ buffer=10485760 \ fromuser=$OWNER \ touser=$OWNER \ ignore=y \ commit=y \ constraints=n \ indexes=n \ grants=n \ log=imp_${OWNER}.log Notice that the buffer is 10m and commit=y. This job is running very slowly. Querying v$session_event reveals many and long waits for log file sync. TIMEAVG TOTAL TOTAL WAITED WAIT USERNAME SID EVENT WAITS TIMEOUTS SECONDS 100ths -- -- --- -- JKSTILL12 latch free63 49 0 1 log buffer space4818 2 641 13 log file switch completion 9 0 3 37 log file sync 628432 6 212193 34 db file sequential read 27 0 3 10 file open 3 0 0 0 SQL*Net message to client1257012 0 3 0 SQL*Net message from client 1257012 0 211774 17 SQL*Net more data from client 118572 0 9 0 9 rows selected. Notice that the value for log file sync seems a bit high for a session that has been connected for a little over 2 hours. Even so, it does accumulate rapidly. 10 seconds of activity garners 8 seconds of log file sync waits. This is not a terribly fast system, but it should not be this slow. The following query shows that the average blocks per commit is about 4.5. select blocks_changed, user_commits, blocks_changed / user_commits blocks_per_commit from ( select stat.value blocks_changed from v$sesstat stat, v$statname name, v$session sess where stat.sid = sess.sid and stat.statistic# = name.statistic# and name.name = 'db block changes' and stat.sid = 12 ) r1, ( select stat.value user_commits from v$sesstat stat, v$statname name, v$session sess where stat.sid = sess.sid and stat.statistic# = name.statistic# and name.name = 'user commits' and stat.sid = 12 ) r2 With an 8k block, that is about 36k per commit. Somewhat less than the 10m per commit I expected. Suspecting that the LONG datatype in some of the tables may be the culprit, a quick perusal of TFM reveals the following regarding the use of the LONG datatype with the imp utility: The integer specified for BUFFER is the size, in bytes, of the buffer through which data rows are transferred. BUFFER determines the number of rows in the array inserted by Import. The following formula gives an approximation of the buffer size that inserts a given array of rows: buffer_size = rows_in_array * maximum_row_size For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, or DATE columns, rows are inserted individually. The size of the buffer must be large enough to contain the entire row, except for LOB and LONG columns. If the buffer cannot hold the longest row in a table, Import attempts to allocate a larger buffer. So, the buffer parameter has no effect on tables containing columns of the type long, lob, bfile, ref, rowid, urowid or date. This seems rather limiting for such an important utility. This applies to versions 8.1.7 and 9.2.0 I ran a test to load 90k rows into 2 different tables, the only difference being that one used a long column for text and the other used a varchar2(4000). The long table took 90 seconds to load with imp and committed every 4.5 blocks. The varchar2 table took 9 seconds to load and committed every 1000 blocks. This is know doubt old hat to many of you, but it's the first time I can recall encountering this. Don't really use imp too much. The fact that writes on this
Re: oracle client on PC's
I guess Merant ODBC driver depends on OCI library which is only available after installed Oracle client. We use DataAnalyst from AgileInfoSoftware, we use Microsoft ODBC Driver for Oracle and Oracle ODBC Driver, both need Oracle client library be installed. Eric. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 21:24 Jeffrey, I've probably missed something, but if the application uses Merant's ODBC driver - which I believe is a wire-protocol driver, how come you need to install the client at all? Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 7 January 2004 7:14 AM The application is a third-party application using Merant's ODBC driver. How do people normally install the client. Do you do an install to every workstation??? [EMAIL PROTECTED] 1/6/04 2:54:34 PM At 11:39 AM 1/6/2004, Jeffrey Beckstrom wrote: Rather than installing the Oracle client on every client PC, we have been: - installing client on 1 PC - copying directory to a network server - extract the registry for oracle key - fix registry that was extracted to reference the network drive - load registry on client PCs - add the network pc as a search drive to the client pc. We are now experiencing problems over the WAN and looking at ways to eliminate the Oracle dll overhead. Short of installing Oracle on every client PC, what are our options? There is a reason that Oracle doesn't support configurations like this. There is a fair amount of chatter between an application and the Oracle client DLL's. When this chatter starts flying over the network rather than merely going to a local DLL, you start to get performance problems. How are your application(s) designed? It's probably possible to tweak an OCI application to make fewer OCI calls. If you're using ODBC, and have the budget, you could purchase one of the wire-protocol ODBC drivers. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Eric King 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: SQL Query Problem(possilble duplicate send, Sorry!)
hi, There is a function RATIO_BY_PERCENT or something very similar to this in SQL just find it out this gives u individual contributions as compared to the whole thing. Regds, Rohan From: Viktor [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: SQL Query Problem(possilble duplicate send, Sorry!) Date: Tue, 13 Jan 2004 12:34:35 -0800 Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select COUNTRY, count (MSS) COUNT_MSS FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCODE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRYCOUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC1 DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes _ Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag Only on www.shaadi.com. Register now! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rohan Karanjawala 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: oaktable people
Lies, lies and viscious rumors. It was only the loft of the Garage, and the idea was to create a new, exciting space for the Oracle Museum, complete with webcams. I had planned the Miracle Master Class Teambuilding Exercise as follows: 1. On Sunday evening we would (slowly!) move the stuff from the loft downstairs and stack it carefully. 2. On Monday evening we would put in the new flooring. 3. On Tuesday evening we would put the stacked stuff back up on the loft. As it turned out, it ended up a bit different from the original plan: 1. On Sunday it took the Oakies about 42 minutes to remove everything from the loft. Most of it was thrown out, and Peter Gram even had to rent a new trailer for the junk. 2. Lex and Carel-Jan and Gary Goodman and James Morle and Jon (from Miracle Iceland) were un-stopable and made 80% of the flooring on Sunday. 3. On Monday Lex got the bright idea of doing some heavy changes to the whole construction of the Garage. Which he and Carel-Jan and helpers then did. 4. On Tuesday evening nobody did anything except participate in the Gala Dinner and visit the famous hotdog stand Bjarne's Poelser. 5. I don't know when the stuff is going up there again. I'm afraid. Mogens PS: The Oakies rock. Nothing beats having 18 of them in your house. Gudmundur Josepsson wrote: Onkel Mogens wrote: All to stay in my house (except Gaja - don't know what he's up to). Rock'n'roll. And none of them know what I meant when I asked them to bring some old clothes for some unusual teambuilding... You're not having them do construction work on your house again, are you? Gaja is probably the smart one, he knows what you're up to! My guess is that 'teambuilding' is Danish for 'dig me a 12 x 25 m swimming pool in my back yard and paint my house while you're at it.' Gummi -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Oracle8i on Linux
Hi, I experienced that same problem before. I found an article that suggested downgrading your gcc to a previous version. I lost the link to the article, sorry. You may try downgrading your gcc to a version before what is included in the RH7.3 distro. Good luck, Seema Singh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2004 01:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Oracle8i on Linux Hi, I have 256mb RAM,60GB of hard disk,P3 MACHINE.I'm having installation of oracle 817 on linux 7.2. I got error during oracle relinking.DOes any one have such problem earlier? Let me know if some one had such installation in past.ANy thoughts are most welcome. thx -Seema _ Check out the new MSN 9 Dial-up fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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).
read-only simple snapshot/materialised view refresh
Hello everyone, We've got read-only primary key snapshots in our 8.1.7.4 databases. 1 master. 1 slave. master and slave are on different servers. Snapshots are refreshed by the FAST method using dbms_refresh.refresh. However, do to the extremely high transaction rates on our database, we're getting ORA-1555 when trying to refresh the snapshots. The mlog$ tables builds up and the slave just keeps on falling behind. From what I can see, snapshots are refreshed as a single large transaction. So if there are 500K rows in the mlog$ table, all 500K will be processed in one go. There are no intermediate commits. So my question is: how do you specify a commit point with snapshots? I'm looking for parameters similar to that of the exp and sqlldr utility where you can specify commit points. I've logged an iTAR with Oracle Support and there answer is that it's not possible. ARGH!! Here's another crazy question is - has anyone updated the dbms_refresh package to add a commit point? Or, have you tried to interogate the mlog$ and write a PL/SQL procedure to process the rows in there, thereby having your own commit points? mlog$ provides the primary keys and the DML type. So surely it's just a matter of going through each one of the row and applying it to the slave? TIA, Leng, -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng 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: Disk capacity planning
Thanks for the time answering my question guys... :-) Tanel, I've just inherited the database that I'm handling right now and I was not part of the real capacity planning. I really lack experience on this part of the job and I would appreciate your thoughts on any good documentation regarding IOPS prediction and determining IO throughput requirements. Thanks again guys and best regards, Rhojel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2004 01:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Rhojel Echano/Manila/PH/SGS) Subject:Re: Disk capacity planning Hi Rhojel, You've had some responses that go beyond your forecasting question. I'll go the simple-minded route here. Maybe there is some canned software out there that will do it but it seems vendors stay away from forecasting future growth. Maybe this is to avoid being held accountable for faulty results (probably shouldn't trust my answer either...). Anyway, I was taught that the first rule of forecasting is that the forecast will be wrong. This doesn't mean you shouldn't forecast the growth of your database. It just means that you need to follow your forecast on a regular basis and note variances (ie: significant changes to growth rates) and ask questions. A spike in growth can be a development boo-boo, new functionality, growth in business, or something else. A simple approach is to track (at least monthly...depending on your comfort zone) the physical size of tablespaces and actual data and calculate net changes in size between your forecasting periods (eg: Month). Summarize this and calculate the average rate over time (eg: Year). Given the average rate and the last rate, you can forecast your growth using both rates over some horizon (1 year? 2 years?) and compare it to your available diskspace. You should be able to identify when you'll run out...but don't assume this will be correct. Also track significant events that may have driven growth. And if there is a major difference between the average growth rate and the last growth rate...it's analysis time. There are fancier forecasting methods like exponential smoothing and so on but the simple approach might get you started. But you need to keep testing results to see if your situation has changed. The above would be a fairly simple spreadsheet. Kip Bryant |Hi everyone! |Can anybody point me to any good documentation regarding disk capacity |planning? Sharing your experience or approach will also give me so much |help. I'd like to know other people's approach on forecasting the growth |of their databases particularly on determining the (growth) rate of disk |space usage and on deciding when to add and how many disk to add on an |Oracle server. |Thanks in advance. |Best Regards, |Rhojel -- 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).