RE: Sparky auth file
This was solved offline by examining the setup at one of my customers' site which has the same configuration of VMS, Oracle and Perl as Barb listed. Barb changed the setting of user_dump_dest to point to the actual directory that was defined in the hotsos.auth file. For some reason the logical was not resolved correctly. I am not proficient enough in VMS to explain that behaviour further. To save any future users of Sparky and VMS a few minutes of frustration I'd like to point out that you must remember to quit your session before Sparky can pull the trace file over to your PC. VMS does not release the file lock on the trace file even if you stop tracing. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barbara Baker Sent: 11. júlí 2003 23:35 To: Multiple recipients of list ORACLE-L Subject: Sparky auth file Ok, this is a REAL reach. But does anyone out there know how to specify the dump directory in the auth file for sparky ... wait ... I'm not there yet on an OpenVMS system? OpenVMS 7.2-1 Oracle 8.1.7.4 Perl 5.6.1 The background dump desitnation from v$parameter is ORA_DUMP. The system logical ORA_DUMP equates to disk$dbtest11:[db_amdev.trace]. Sparky wants to append the trace name file to the end of this. I've tried both ORA_DUMP: and disk$dbtest11:[db_amdev.trace] in the aut file. They don't work. if I use diskname: Authorized directory 'disk$dbtest11:[DB_AMDEV.TRACE]': 16 of 16 '*.trc' files are readable Fri Jul 11 16:20:48 2003 REQ 10.39.8.253 version Fri Jul 11 16:20:48 2003 ACK 10.39.8.253 0 3.04 Fri Jul 11 16:21:30 2003 REQ 10.39.8.253 trcname 'ORA_DUMP', '20c02d0a', 'AMDEV', '16' Fri Jul 11 16:21:30 2003 ACK 10.39.8.253 1 not authorized to read 'ORA_DUMP' (trcfiled.pl v3.04 line 536) if I use ORA_DUMP Fri Jul 11 16:18:34 2003 INFO spid('[.ORA_DUMP]drax11_amdev_fg_oracle_016.trc')='-1' Fri Jul 11 16:18:34 2003 WSVR can't read '[.ORA_DUMP]drax11_amdev_fg_oracle_017.trc' (no such file or directory) I get the error message trace file server daemon has denied your request. Check the trcfiled logfile on 'DRAX11' (ip) at port 2297 for details. I'd be most grateful for any ideas. Thanks!! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni 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).
OT: unix shell script question
Hi List, The requirement is as follows: DBNAME=PROD ( 'DBNAME' variable contains value 'PROD' ) LINE_PROD=100 ( 'LINE_PROD' variable contains value 100 ) Now I want to echo the LINE_PROD variable using DBNAME variable. e.g echo ${LINE_${DBNAME}} should return 100. Is this possible and if yes, how ? I tried some ways but couldn't find any way. Regards, ~Dilip Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dilip 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).
Object Types in PL/SQL
Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at line 4: ORA-06550: line 4, column 15: PLS-00307: too many declarations of 'TVNR' match this call ORA-06550: line 4, column 3: PL/SQL: Statement ignored Looks to me like the PL/SQL enginge isn't able to distinguish the default constructor from the overridden (my) version, since they have the same signature (of course). Any input ? I couldn't find ANY descenct hints in the fine manual or the Feuerstein book :(. TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Good document / Presentation on Oracle wait events meaning , expl
Dear All, Please suggest me some URL for the above subject ( Good document / Presentation on Oracle wait events meaning , explanation , action to be taken .). Please do send me documents if any. Thanks a lot. Regards Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi 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: Good document / Presentation on Oracle wait events meaning , expl
Raju, just have a look at http://ixora.com.au/ Jp. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Veeraraju_Mareddi Sent: Tuesday, July 15, 2003 7:59 PM To: Multiple recipients of list ORACLE-L Subject: Good document / Presentation on Oracle wait events meaning , expl Dear All, Please suggest me some URL for the above subject ( Good document / Presentation on Oracle wait events meaning , explanation , action to be taken .). Please do send me documents if any. Thanks a lot. Regards Rajuveera -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle last
Title: RE: oracle last select name, sal from (select rownum rnum, name, sal from emp order by sal asc) where rownum 4 / ??? 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: Daniel Fink [mailto:[EMAIL PROTECTED]] Sent: Monday, July 14, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: oracle last Depending upon the version, you may be able to use the analytical functions (8.1.6+) to achieve what you want. I don't have a ready example, but the Oracle docs are pretty good. If you want a less elegant solution, use an inline query, sort by the reverse order (asc or desc depends on your requirements) and take the top 3. You can find examples of the Top-N approach on www.optimaldba.com/library.html. Either the Top-N Row or SQL Scripting Sorcery paper/presentation will have enough info to solve the problem. pfeffer wrote: hi is there a statement like last in mysql ? last returns a specified number of rows from a select. means if i do a select and i want only the last 3 lines. thx martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Object Types in PL/SQL
if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in your database? How efficient do you find that? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 06:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Object Types in PL/SQL Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at line 4: ORA-06550: line 4, column 15: PLS-00307: too many declarations of 'TVNR' match this call ORA-06550: line 4, column 3: PL/SQL: Statement ignored Looks to me like the PL/SQL enginge isn't able to distinguish the default constructor from the overridden (my) version, since they have the same signature (of course). Any input ? I couldn't find ANY descenct hints in the fine manual or the Feuerstein book :(. TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Performance problems VMS 8i
Hi! One of first thing I'd check when migrating from 7 to 8i, is settings for optimizer_index_* parameters. And of course, your tablesindexes should be analyzed (if not still explicitly using RBO). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:49 PM Hi Barbara After an upgrade from Oracle 7.3.4 to 8.1.7.4 on OpenVMS, some (but not all) of our batch jobs are suffering severe performance degradation. One of our critical jobs went from 3 hours to 9 hours elapsed time. The reason is obvious. The solution is not. One of our jobs increased from 45 minutes to 1 hr 30 min. The direct i/o for this job increased from 480 to 1,046,938. (Identical everything. Only difference 7.3.4 versus 8.1.7.4) This direct i/o number is from the parent process - the process that is communicating with the detached process actually running the oracle code via a mailbox (using the bequeath adapter). The jobs causing trouble are batch jobs running on the server, and are using bequeath. This sounds like a piece of SQL has hit upon a different execution plan. I'd recommend the following course of action 1. get up to date statistics as Jared says (compute them if you can). 2. modify the job so that it does the following alter session set events '10046 trace name context forever, level 12'; your job alter session set events '10046 trace name context off'; 3. run the job. You will get a trace file in the udump directory with waits and elapsed time in it, you can run that thru tkprof. Look for large values of elapsed time. Chances are excellent (better than 90%) there will be 1 (or at an outside 2) statements that take up more than an hour of your hour and a half. Chances are pretty good ( better than 75%) that faced with those statements you can tune them to take less than 20 minutes - a good index, a rewritten statement. If you don't like all this set events stuff connect internal to the db and run @?/rdbms/admin/dbmssupp and replace the trace stuff with exec sys.dbms_support.start_trace(true,true); your job exec dbms_support.stop_trace(); It does the same thing. If in the unlikely event the above does not hold true, well you will see what you spend your time waiting on. If it is network stuff then maybe you can think about raising a tar with more info. I honestly expect it to be the sql. Do feel free to post the results of the above, if only to show how wrong I am. Good luck. Niall -- 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). -- 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).
AW: Object Types in PL/SQL
Hi The anonnymous block at the bottom of my email is just a little test driver. Basically, I don't use subtyping here. I just override the default constructor. If I don't implement a constructor at all, I would look like this: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; And I would get a hidden, system provided default constructor. That would allow me to do the following (tested it): declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; And I would get the output: 12345678901 Unfortunately, that doesn't give me any control regarding the initialization of vVNR. Explanation: VNR = Versichertennummer, which is kind of the Swiss analog to the US Social Security ID, only less unique ;). We don't use Object Relational features IN the database. Just plain relational tables. What we would like to use are Object Types to encapsulate certain things like the above shown VNR to ensure data integrity during data conversion for PL/SQL programs using these types. It's more of a guideline where the developers are constrained to use the types for certain stuff. Enjoy your day, Stefan Jahnke Consultant BOV Aktiengesellschaft Tel.: +49 201/45 13-289 mailto:[EMAIL PROTECTED] http://www.bov.de Abonnieren Sie unseren Newsletter: http://www.bov.de/enews Kosten senken - strategische IT-Ziele erreichen! BOV Microsoft Day am 24.07.03 in Essen. Anmeldung unter http://www.bov.de/microsoft-day oder mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Object Types in PL/SQL if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in your database? How efficient do you find that? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 06:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Object Types in PL/SQL Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at line 4: ORA-06550: line 4, column 15: PLS-00307: too many declarations of 'TVNR' match this call ORA-06550: line 4, column 3: PL/SQL: Statement ignored Looks to me like the PL/SQL enginge isn't able to distinguish the default constructor from the overridden (my) version, since they have the same signature (of course). Any input ? I couldn't find ANY descenct hints in the fine manual or the Feuerstein book :(. TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
Re: OEM Repository Problem
This is a shot in the dark, but try and delete (or rename) Oracle_Home\sysman\config\omsconfig.properties At 10:14 PM 7/14/2003 -0800, you wrote: Hi Listers, I had OEM repository on my database installed on my PC. Accidentally I dropped the database. Now I have recreated the database. But an not able to use this database to create New OEM repository as the OEM config assistant says that the database already had a repository installed. If I try to drop the repository it gives me error as the repository is not actually present. Please tell me if anyone of u knows how to solve this problem. Do I have to reinstall the Oracle Software again. Wolfgang Breitling 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).
Re: AW: Object Types in PL/SQL
ok then i missed it. where is the overriding taking place? I saw a base constructor.. where was the 'override'? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 08:30:30 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: AW: Object Types in PL/SQL Hi The anonnymous block at the bottom of my email is just a little test driver. Basically, I don't use subtyping here. I just override the default constructor. If I don't implement a constructor at all, I would look like this: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; And I would get a hidden, system provided default constructor. That would allow me to do the following (tested it): declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; And I would get the output: 12345678901 Unfortunately, that doesn't give me any control regarding the initialization of vVNR. Explanation: VNR = Versichertennummer, which is kind of the Swiss analog to the US Social Security ID, only less unique ;). We don't use Object Relational features IN the database. Just plain relational tables. What we would like to use are Object Types to encapsulate certain things like the above shown VNR to ensure data integrity during data conversion for PL/SQL programs using these types. It's more of a guideline where the developers are constrained to use the types for certain stuff. Enjoy your day, Stefan Jahnke Consultant BOV Aktiengesellschaft Tel.: +49 201/45 13-289 mailto:[EMAIL PROTECTED] http://www.bov.de Abonnieren Sie unseren Newsletter: http://www.bov.de/enews Kosten senken - strategische IT-Ziele erreichen! BOV Microsoft Day am 24.07.03 in Essen. Anmeldung unter http://www.bov.de/microsoft-day oder mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Object Types in PL/SQL if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in your database? How efficient do you find that? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 06:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Object Types in PL/SQL Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at line 4: ORA-06550: line 4, column 15: PLS-00307: too many declarations of 'TVNR' match this call ORA-06550: line 4, column 3: PL/SQL: Statement ignored Looks to me like the PL/SQL enginge isn't able to distinguish the default constructor from the overridden (my) version, since they have the same signature (of course). Any input ? I couldn't find ANY descenct hints in the fine manual or the Feuerstein book :(. TIA,
AW: AW: Object Types in PL/SQL
Hi The default constructor is always there, even if you don't provide one. So, if you declare and implement a constructor that has the same signature as the default constructor (simply all the attributes as IN parameters, returning SELF), you automatically override the default constructor. That is what I want to do. The object type compiles, but if I use it, PL/SQL tells me that it finds more than one constructors with that signature, which are the hidden default constructor and the one I implemented (I think). Now, that leaves me clueless, since it should be possible (manual says so) to override the default constructor from 9.2 on. Any more ideas ? Stefan Jahnke Consultant BOV Aktiengesellschaft Tel.: +49 201/45 13-289 mailto:[EMAIL PROTECTED] http://www.bov.de Abonnieren Sie unseren Newsletter: http://www.bov.de/enews Kosten senken - strategische IT-Ziele erreichen! BOV Microsoft Day am 24.07.03 in Essen. Anmeldung unter http://www.bov.de/microsoft-day oder mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:44 An: Multiple recipients of list ORACLE-L Betreff: Re: AW: Object Types in PL/SQL ok then i missed it. where is the overriding taking place? I saw a base constructor.. where was the 'override'? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 08:30:30 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: AW: Object Types in PL/SQL Hi The anonnymous block at the bottom of my email is just a little test driver. Basically, I don't use subtyping here. I just override the default constructor. If I don't implement a constructor at all, I would look like this: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; And I would get a hidden, system provided default constructor. That would allow me to do the following (tested it): declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; And I would get the output: 12345678901 Unfortunately, that doesn't give me any control regarding the initialization of vVNR. Explanation: VNR = Versichertennummer, which is kind of the Swiss analog to the US Social Security ID, only less unique ;). We don't use Object Relational features IN the database. Just plain relational tables. What we would like to use are Object Types to encapsulate certain things like the above shown VNR to ensure data integrity during data conversion for PL/SQL programs using these types. It's more of a guideline where the developers are constrained to use the types for certain stuff. Enjoy your day, Stefan Jahnke Consultant BOV Aktiengesellschaft Tel.: +49 201/45 13-289 mailto:[EMAIL PROTECTED] http://www.bov.de Abonnieren Sie unseren Newsletter: http://www.bov.de/enews Kosten senken - strategische IT-Ziele erreichen! BOV Microsoft Day am 24.07.03 in Essen. Anmeldung unter http://www.bov.de/microsoft-day oder mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Object Types in PL/SQL if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in
Re: Oracle security question
Ron, I have urgent question. Because I want to shutdown my database, I login as oracle and execute dbshut. But later I found that the process is very slow so I realize that I should type shutdown immediate. Then, I use control-D to stop shutdown command. And I re-execute shutdown immediate. Now the shutdown command seems to take longer time to finish. Is there something wrong or normal? Any comments are appreciated! Thanks! Don Ron Rogers wrote: Don, The users need acces to the data that is in the database or what is the purpose of the database? I would change the privileges of the users to CREATE SESSION only and revoke all others. Then I would use ROLES that have select privileges on the tables that they need acces to. By creating roles and granting the role to a user the user can select from the tables. If different groups need acces to different tables you can create different roles and grant them as needed. Roles are an easy method of controlling acces to table data and if changes are needed then you change the role's privileges and all users of the role are effected. Ron [EMAIL PROTECTED] 07/11/03 03:44PM Hi, I have a security question about Oracle database. Recently I have taken full control an Oracle database in my department. Now I would like to make sure that no other people except myself can update data in that database. Can somebody tell me what it is necessary steps to do that? Any comments are highly appreciated. Thanks! Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: Object Types in PL/SQL
- Original Message - That is what I want to do. The object type compiles, but if I use it, PL/SQL tells me that it finds more than one constructors with that signature, which are the hidden default constructor and the one I implemented (I think). Now, that leaves me clueless, since it should be possible (manual says so) to override the default constructor from 9.2 on. Any more ideas ? I think the problem is that both the default constructor and your custom one have the same signature. Ie, they both have the same parameters, which is the way Oracle PL/SQL distinguishes between overloaded function and procedure calls. What you need is to add a fake variable to your constructor so that it looks like this: CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2, my_fake NUMBER) and then ignore the darn thing (do nothing with it). That will make tVNR() constructor differ sufficiently from the default one so Oracle considers it an overload for the default. Don't forget that Oracle's default constructor is STILL available when you define your own. The only way it gets distinguished is by the parameter list (name is the same or else it ain't a constructor for the type!). BTW this works also in 8i, it's not just 9i. Cheers Nuno Souto [EMAIL PROTECTED] Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:44 An: Multiple recipients of list ORACLE-L Betreff: Re: AW: Object Types in PL/SQL ok then i missed it. where is the overriding taking place? I saw a base constructor.. where was the 'override'? From: Stefan Jahnke [EMAIL PROTECTED] Hi The anonnymous block at the bottom of my email is just a little test driver. Basically, I don't use subtyping here. I just override the default constructor. If I don't implement a constructor at all, I would look like this: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; And I would get a hidden, system provided default constructor. That would allow me to do the following (tested it): declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; And I would get the output: 12345678901 Unfortunately, that doesn't give me any control regarding the initialization of vVNR. Explanation: VNR = Versichertennummer, which is kind of the Swiss analog to the US Social Security ID, only less unique ;). We don't use Object Relational features IN the database. Just plain relational tables. What we would like to use are Object Types to encapsulate certain things like the above shown VNR to ensure data integrity during data conversion for PL/SQL programs using these types. It's more of a guideline where the developers are constrained to use the types for certain stuff. Enjoy your day, Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Object Types in PL/SQL if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in your database? How efficient do you find that? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 06:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Object Types in PL/SQL Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at
v$session question
Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank 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: OEM Repository Problem
Thanks Wolfgang, The Solution provided by u was great and has worked. Regards Munish Bajaj -Original Message- Sent: Tuesday, July 15, 2003 18:00 To: Multiple recipients of list ORACLE-L This is a shot in the dark, but try and delete (or rename) Oracle_Home\sysman\config\omsconfig.properties At 10:14 PM 7/14/2003 -0800, you wrote: Hi Listers, I had OEM repository on my database installed on my PC. Accidentally I dropped the database. Now I have recreated the database. But an not able to use this database to create New OEM repository as the OEM config assistant says that the database already had a repository installed. If I try to drop the repository it gives me error as the repository is not actually present. Please tell me if anyone of u knows how to solve this problem. Do I have to reinstall the Oracle Software again. Wolfgang Breitling 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: Munish Bajaj 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: OT: unix shell script question
Try DBNAME=PROD LINE_PROD=100 CMD=echo \$LINE_$DBNAME echo $CMD eval $CMD Dilip [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 15/07/03 11:34 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:OT: unix shell script question Hi List, The requirement is as follows: DBNAME=PROD ( 'DBNAME' variable contains value 'PROD' ) LINE_PROD=100 ( 'LINE_PROD' variable contains value 100 ) Now I want to echo the LINE_PROD variable using DBNAME variable. e.g echo ${LINE_${DBNAME}} should return 100. Is this possible and if yes, how ? I tried some ways but couldn't find any way. Regards, ~Dilip CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: v$session question
Title: RE: v$session question grant select on sys.v_$session to xyuser / 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: Foelz.Frank [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 15, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL : http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Sie diese E-Mail irrtümlich erhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
v$session question
sorry, cut/copy/paste mismatch. I took the wrong error description. again.. anyone able to tell me, how to grant v$session select privileges to a user ? --- grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views --- ORA-02030 can only select from fixed tables/views Cause: An operation other than SELECT on a fixed dynamic performance table or view was attempted. It is only possible to select from fixed tables or views. Action: Remove the fixed table or view name from the SELECT statement. --- But I must know, what user is doing things to a definite table ! Frank --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: AW: Object Types in PL/SQL
then the guy is correct. that is a weakness in oracle's object oriented model. in c++ and java, you can override signatures. its different then overloading. having to add an additional flag can be tedious in a large project. you might want to open a TAR on this? Id like to see what the tech support guys say. If you do, please post what they say. From: Nuno Souto [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 09:49:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: AW: Object Types in PL/SQL - Original Message - That is what I want to do. The object type compiles, but if I use it, PL/SQL tells me that it finds more than one constructors with that signature, which are the hidden default constructor and the one I implemented (I think). Now, that leaves me clueless, since it should be possible (manual says so) to override the default constructor from 9.2 on. Any more ideas ? I think the problem is that both the default constructor and your custom one have the same signature. Ie, they both have the same parameters, which is the way Oracle PL/SQL distinguishes between overloaded function and procedure calls. What you need is to add a fake variable to your constructor so that it looks like this: CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2, my_fake NUMBER) and then ignore the darn thing (do nothing with it). That will make tVNR() constructor differ sufficiently from the default one so Oracle considers it an overload for the default. Don't forget that Oracle's default constructor is STILL available when you define your own. The only way it gets distinguished is by the parameter list (name is the same or else it ain't a constructor for the type!). BTW this works also in 8i, it's not just 9i. Cheers Nuno Souto [EMAIL PROTECTED] Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:44 An: Multiple recipients of list ORACLE-L Betreff: Re: AW: Object Types in PL/SQL ok then i missed it. where is the overriding taking place? I saw a base constructor.. where was the 'override'? From: Stefan Jahnke [EMAIL PROTECTED] Hi The anonnymous block at the bottom of my email is just a little test driver. Basically, I don't use subtyping here. I just override the default constructor. If I don't implement a constructor at all, I would look like this: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; And I would get a hidden, system provided default constructor. That would allow me to do the following (tested it): declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; And I would get the output: 12345678901 Unfortunately, that doesn't give me any control regarding the initialization of vVNR. Explanation: VNR = Versichertennummer, which is kind of the Swiss analog to the US Social Security ID, only less unique ;). We don't use Object Relational features IN the database. Just plain relational tables. What we would like to use are Object Types to encapsulate certain things like the above shown VNR to ensure data integrity during data conversion for PL/SQL programs using these types. It's more of a guideline where the developers are constrained to use the types for certain stuff. Enjoy your day, Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Object Types in PL/SQL if im reading your code right... looks like your overriding in an anonymous block. in most OO languages overriding is done by a child class. i dont see any subclassing here? didnt they add sub-types and 'extends' to 9.2? btw, are you using object oriented design in your database? How efficient do you find that? From: Stefan Jahnke [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 06:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Object Types in PL/SQL Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE
Re: v$session question
v$session is a synonym. all the v$ are really v_$. go to dba_views... see for yourself. so grant on v_$. everyone gets nabbed by that one atleast once. From: Foelz.Frank [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 09:59:31 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank 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: [EMAIL PROTECTED] 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: unix shell script question
Hi, This works in ksh: DBNAME=PROD LINE_PROD=500 OTHER=\${LINE_${DBNAME}} eval echo $OTHER Regards Pete [END] -Original Message- Sent: 15 July 2003 11:35 To: Multiple recipients of list ORACLE-L Hi List, The requirement is as follows: DBNAME=PROD ( 'DBNAME' variable contains value 'PROD' ) LINE_PROD=100 ( 'LINE_PROD' variable contains value 100 ) Now I want to echo the LINE_PROD variable using DBNAME variable. e.g echo ${LINE_${DBNAME}} should return 100. Is this possible and if yes, how ? I tried some ways but couldn't find any way. Regards, ~Dilip Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dilip INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com __ The information contained in this email is confidential and intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. Thomson Scientific will accept no responsibility or liability in respect to this email other than to the addressee. If you have received this communication in error, please notify us immediately via email: [EMAIL PROTECTED] __ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hitchman, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: v$session question
GRANT SELECT ON V_$SESSION TO user; Regards Nigel Bishop Snr. Oracle DBA ioko Tel DDI: +44 (0) 1904 435 458 Mobile: +44 (0) 7881 624 386 Fax: +44 (0) 1904 435 450 Email:[EMAIL PROTECTED] www.ioko.com -Original Message- Sent: 15 July 2003 15:00 To: Multiple recipients of list ORACLE-L Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank 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: Nigel Bishop 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).
AW: v$session question
that helped, tnx a lot ! Frank -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet am: Dienstag, 15. Juli 2003 16:15 An: Multiple recipients of list ORACLE-L Betreff: Re: v$session question v$session is a synonym. all the v$ are really v_$. go to dba_views... see for yourself. so grant on v_$. everyone gets nabbed by that one atleast once. From: Foelz.Frank [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 09:59:31 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl Question
Jared, You came to the same conclusion I did. Since this is the first perl script I have written for production I still have a lot to learn. I was thinking that I had missed something painfully obvious. Thanks for the help. Hope all is well by you. Pete --- [EMAIL PROTECTED] wrote: Pete, Even as a rabid Perl fanatic, I still use KSH to install statspack. I just modified the stock scripts a bit so they will run without user input. You can do it in Perl, but ksh is probably the tool for this job. Jared Peter Barnett [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Perl Question I am mucking through my first real perl script. When it is finished it will install statspack on all of the database throughout the company. That's a gob of databases! To run in batch mode statspack requires two define statements: define default_tablespace = tablespace_name define temporary_tablespace = temp_name Does anyone know the syntax to get these two statements to work? If not, I suppose the individual scripts can be called individually after the perfstat user is created. Either that or chicken out of the perl stuff and go back to tried and true shell scripting. = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle security question
Don, A normal shutdown will wait for all of the users to complete there work and shutdown when the users are off the system. A shutdown immediate wait for the current transactions to complete and not allow any more to start before shutting down. A shutdown abort does just that, I stops all transactions and shutdown the database. A shutdown abort is best followed by a startup restricted and then a shutdown immediate to clear up the database. If you check your alert log you will find that the database does a recovery to clean it's self up after a shutdown abort. When you do an non normal action such as a CTRL-D when shutting down Oracle might be cleaning it's self up or clearing the temp that takes time to accomplish. Take a look at the actions that are going on in the database with temp. Perhaps that is why the shutdown is taking time. A user could be performing a long transaction that has to complete or rollback before Oracle shuts down. There are many possibilities for the lengthy time to shut down. Select count(*) from DBA_EXTENTS where segment_type ='temporary'; will give you the number of extents that are being shrunk if temp is one of your problems. The number will decrease as SMON does it's work. I would also edit the dbshut command to shutdown immediate rather than the plain shutdown. Check the OS to see what processes are consuming the time. That can lead you to the time comsumption. Remember time is relative to the viewer. Ron [EMAIL PROTECTED] 07/15/03 09:29AM Ron, I have urgent question. Because I want to shutdown my database, I login as oracle and execute dbshut. But later I found that the process is very slow so I realize that I should type shutdown immediate. Then, I use control-D to stop shutdown command. And I re-execute shutdown immediate. Now the shutdown command seems to take longer time to finish. Is there something wrong or normal? Any comments are appreciated! Thanks! Don Ron Rogers wrote: Don, The users need acces to the data that is in the database or what is the purpose of the database? I would change the privileges of the users to CREATE SESSION only and revoke all others. Then I would use ROLES that have select privileges on the tables that they need acces to. By creating roles and granting the role to a user the user can select from the tables. If different groups need acces to different tables you can create different roles and grant them as needed. Roles are an easy method of controlling acces to table data and if changes are needed then you change the role's privileges and all users of the role are effected. Ron [EMAIL PROTECTED] 07/11/03 03:44PM Hi, I have a security question about Oracle database. Recently I have taken full control an Oracle database in my department. Now I would like to make sure that no other people except myself can update data in that database. Can somebody tell me what it is necessary steps to do that? Any comments are highly appreciated. Thanks! Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu 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
should you seperate indexes from tables in seperate datafiles?
There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Object Types in PL/SQL
This doesn't answer your question directly, but if you aren't able to override the constructor explicitly, you can probably create a static function that creates, initializes returns a tVNR as you would like it. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 15, 2003 3:49 AM To: Multiple recipients of list ORACLE-L Hi list I have a problem regardint PL/SQL Object Types. According to the fine manual, it should be possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did that, Object Type compiles without complaints: CREATE OR REPLACE TYPE tVNR AS OBJECT ( vVNR VARCHAR2(14), CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT, MEMBER FUNCTION getVNR RETURN VARCHAR2 ) INSTANTIABLE FINAL; CREATE OR REPLACE TYPE BODY tVNR AS CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2) RETURN SELF AS RESULT IS BEGIN IF (LENGTH(piVNR)=11) THEN SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' || SUBSTR(piVNR,9,3); ELSE SELF.vVNR := 'invalid'; END IF; RETURN; END; MEMBER FUNCTION getVNR RETURN VARCHAR2 IS BEGIN RETURN SELF.vVNR; END; END; Now, everytime I want to create an object like this: declare vVNR tVNR; begin vVNR := new tVNR('12345678901'); dbms_output.put_line(vVNR.getVNR()); end; I get the following error message: ERROR at line 4: ORA-06550: line 4, column 15: PLS-00307: too many declarations of 'TVNR' match this call ORA-06550: line 4, column 3: PL/SQL: Statement ignored Looks to me like the PL/SQL enginge isn't able to distinguish the default constructor from the overridden (my) version, since they have the same signature (of course). Any input ? I couldn't find ANY descenct hints in the fine manual or the Feuerstein book :(. TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle security question
I'm not Ron; but I'll take a stab at this. The shutdown immediate is hanging probably because there are several transactions that are rolling back. The database is not going to close till all have rolled back completely. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:29 AM Ron, I have urgent question. Because I want to shutdown my database, I login as oracle and execute dbshut. But later I found that the process is very slow so I realize that I should type shutdown immediate. Then, I use control-D to stop shutdown command. And I re-execute shutdown immediate. Now the shutdown command seems to take longer time to finish. Is there something wrong or normal? Any comments are appreciated! Thanks! Don Ron Rogers wrote: Don, The users need acces to the data that is in the database or what is the purpose of the database? I would change the privileges of the users to CREATE SESSION only and revoke all others. Then I would use ROLES that have select privileges on the tables that they need acces to. By creating roles and granting the role to a user the user can select from the tables. If different groups need acces to different tables you can create different roles and grant them as needed. Roles are an easy method of controlling acces to table data and if changes are needed then you change the role's privileges and all users of the role are effected. Ron [EMAIL PROTECTED] 07/11/03 03:44PM Hi, I have a security question about Oracle database. Recently I have taken full control an Oracle database in my department. Now I would like to make sure that no other people except myself can update data in that database. Can somebody tell me what it is necessary steps to do that? Any comments are highly appreciated. Thanks! Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don Yu 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: Arup Nanda 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 you seperate indexes from tables in seperate datafiles?
I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. --- [EMAIL PROTECTED] wrote: There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: should you seperate indexes from tables in seperate datafiles
R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unix shell script question
Something else for people who are bored: If I may ASS-U-ME that your ultimate goal is to have variables like: LINE_THIS LINE_THAT LINE_THE_OTHER_THING and you want to be able to change what is after the LINE part, if you have genuine ksh93, and you like playing around with scripting stuff, then you can try playing around with associative arrays. For example: LINE=([THIS]='ABC' [THAT]='XYZ' [THE_OTHER_THING]='HELLO WORLD') X='THE_OTHER_THING' echo ${LINE[$X]} HELLO WORLD X='THIS' echo ${LINE[$X]} ABC X='THAT' echo ${LINE[$X]} XYZ I think you must define the associative array as a single command; i.e. you can't do LINE=([THIS]='ABC') LINE=([THAT]='XYZ') because you will only have ${LINE[THAT]} Note that, as far as I know, this stuff works only with ksh93. Ksh88 is probably much more predominant on non-Linux, and I think pdksh (public domain ksh) -- which is useless for scripting -- shows up on Linux. But you can download and build ksh93. Make sure you put the (){}[] characters in the right places. -Original Message- Hi List, The requirement is as follows: DBNAME=PROD ( 'DBNAME' variable contains value 'PROD' ) LINE_PROD=100 ( 'LINE_PROD' variable contains value 100 ) Now I want to echo the LINE_PROD variable using DBNAME variable. e.g echo ${LINE_${DBNAME}} should return 100. Is this possible and if yes, how ? I tried some ways but couldn't find any way. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Bye all
Dear Friends, I am going to unsubscribe from this list. It was a good journey towards learning. Thanks Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle pricing question
never mind, 4 seconds after I sent the e-mail I saw the items listed on a web page... sigh. Patrice. -Original Message- Sent: Tuesday, July 15, 2003 11:15 AM To: '[EMAIL PROTECTED]' Oracle used to sell databases options as add-ons to Oracle EE. I went to the Oracle Store web site, can't find any options listed anywhere. Do Spatial, Advanced Security, and Transparent Gateways all come with the EE license now? That would be nice. : ) Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle pricing question
Oracle used to sell databases options as add-ons to Oracle EE. I went to the Oracle Store web site, can't find any options listed anywhere. Do Spatial, Advanced Security, and Transparent Gateways all come with the EE license now? That would be nice. : ) Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: should you seperate indexes from tables in seperate datafiles
Actually, there is a performance benefit, but is almost negligible. Performance benefit comes from the fact that indexes are usually read by using db_file_sequential_read, which is, as I was told by 3 or 4 wise men without any gifts, a single block read. Having vast majority of I/O being short allows, at least in theory, the controller to better optimize the incoming I/O requests, thus achieving better service times. I must say that I haven't actually seen the benefits myself but my faith is rock solid and I'll continue to separate data from indexes. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 11:04 AM To: Multiple recipients of list ORACLE-L datafiles? I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. --- [EMAIL PROTECTED] wrote: There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen 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 you seperate indexes from tables in seperate datafiles?
I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes. Rachel Carmichael wrote: I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well.begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
Re: RE: should you seperate indexes from tables in seperate datafiles
does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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 you seperate indexes from tables in seperate datafiles?
It's hot here. I wish I was at the beach and I feel like a rant. oracle actually accesses indexes and tables serially Is it just me or is this blindingly obvious? You cannot access the table data until you have completed accessing the index data because the index data contains the location of the table data. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. That is the argument for what it is worth. Real life is of course vastly more complex than this and we are swimming in very muddy waters, which is why there is so much argument on the subject (raid salesmen - spit). Thanks for the vent Garry Gillies [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 15/07/03 15:49 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:should you seperate indexes from tables in seperate datafiles? There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies 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 you seperate indexes from tables in seperate datafiles
Steroids, weight lifting, and a flattop hair cut (orange or green). After two years of this, try talking to the storage guys while holding a beer in one hand and a Polish sausage in the other. If you can manage a good belch during the conversation, even better. (Are you a visual person?) -Original Message- get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: should you seperate indexes from tables in seperate datafiles
I disagree with the concept of recovery not including some indexes because they can be rebuilt later. To me, that's like going to a gas station and only filling the tank half-way because I can get more gas later. You are saving small amounts of time up front, but will pay for it later on. I prefer to restore a totally whole database (when needed), and not having to rely on my memory to rebuild some indexes that we purposly chose not to back up. Just seems silly to me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L datafiles? I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes. Rachel Carmichael wrote: I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle has out-smarted me again.
Interim Patch: 2878462 DATE: May 7, 2003 Platform Patch for : Microsoft Windows NT Microsoft Windows 2000 Microsoft Windows XP Product Version # : 2.2.0.18.0 Product Patched : UNIVERSAL INSTALLER Patch Installation Instructions : ( this assumes that you have already unzipped the file you downloaded from metalink): 1. Extract the contents of the file oui220180_nt.jar into a newly created directory using C: jar -xvf oui220180_nt.jar or C: unzip oui220180_nt.jar or You can use the winzip graphical utility as well. 2. Change directory to Disk1\install\win32 as : cd Disk1\install\win32 1. Run setup.exe from Disk1\install\win32 directory as C: setup.exe:. This will launch the Install session for installing OUI 2.2.0.18.0 to the current Oracle environment. After the installation completes, you can verify that the OUI version is 2.2.0.18.0 by invoking Oracle Universal Installer from Windows Program Menu and clicking on the About Oracle Universal Installer button . * Having R'ed the old FM to a fare-thee-well, how is one supposed to make the above instructions work? Following them slavishly will re-install the current version of the OUI, since the products.jar hasn't changed. OUI doesn't recognize oui220180_nt.jar as a substitute for products.jar. Anyone who has had any success in installing OUI 2.2.0.18.0 on Win2K, could you share how that happened? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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).
failed to archive
I see a lot of "failed to archive " messages in alert.log . But finally the log gets archived . Is it due overloaded archiver ? this is from alert log. ARC0: Beginning to archive log# 3 seq# 87568ARC0: Failed to archive log# 3 seq# 87568ARC0: Beginning to archive log# 2 seq# 87569Mon Jul 14 11:36:23 2003ARC4: Completed archiving log# 3 seq# 87568 -ak ocp dba 8i
RE: Oracle pricing question
That's a big sale, something like your friendly neighborhood Dodge sale. You can get Oracle Ram, with world's most powerful V8 engine and $3000 cashback with 0.7 APR. All bells and whistles like the Spatial Option, Transparent Gateways and Advanced Networking are included. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 11:15 AM To: Multiple recipients of list ORACLE-L Oracle used to sell databases options as add-ons to Oracle EE. I went to the Oracle Store web site, can't find any options listed anywhere. Do Spatial, Advanced Security, and Transparent Gateways all come with the EE license now? That would be nice. : ) Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: should you seperate indexes from tables in seperate dataf
For a given statements execution, running in serial, you will not find an index and a table being accessed in parallel. Thus, there is no contention, for a given statement, between the tables and indexes accessed by that statement. Thus, one could argue the merits of not needing to seperate indexes and data in a low concurrency environment. OTOH, high concurrency leads to hot blocks, which often leads to a requirement of distributing IO. In this case, seperating indexes and data may be one step in that direction. RAID technologies, if properly configured, can eliminate some of the risk from co-location of data of course but in the end there is no substitute for carefully laid out data. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/15/2003 10:29 AM datafiles does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services
Re: RE: should you seperate indexes from tables in seperate datafiles
if you keep a repository of all your indexes or have the create index statements on files in the file system.. that would solve the memory problem. oracle designer and erwin have these capabilities. in some cases would it be better to recovery without indexes first so the users will have some access to data, then build the indexes while the instance is online? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:39:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles I disagree with the concept of recovery not including some indexes because they can be rebuilt later. To me, that's like going to a gas station and only filling the tank half-way because I can get more gas later. You are saving small amounts of time up front, but will pay for it later on. I prefer to restore a totally whole database (when needed), and not having to rely on my memory to rebuild some indexes that we purposly chose not to back up. Just seems silly to me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L datafiles? I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes. Rachel Carmichael wrote: I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle pricing question
Patrice, The listing I have 09-06-2002, has separate prices for the options you listed. Prices are for named users license and Processor license. There might be a newer price list out but I haven't found it yet. The prices I show are EE 800/40,000 Spatial 200/10,000 Advanced Security 200/10,000 Label Security 200/10, Data Mining 400/20,000 OLAP 400/20,000 Partitionong 200/10,000 RAC 400/20,000 Ron [EMAIL PROTECTED] 07/15/03 11:14AM Oracle used to sell databases options as add-ons to Oracle EE. I went to the Oracle Store web site, can't find any options listed anywhere. Do Spatial, Advanced Security, and Transparent Gateways all come with the EE license now? That would be nice. : ) Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: should you seperate indexes from tables in seperate datafiles
Tom, I prefer to backup the whole database, but in some situations that I have encountered, it is not possible. If you look at the ratio of backups to recoveries, the savings can be substantial. As part of the backup/recovery documentation, the scripts to rebuild (actually the physical location of said scripts) the indexes are included as part of the process. At one client, the monthly load window for a data warehouse was insufficient to be able to perform a full backup. By skipping the performance indexes, we were able to make the window. At another, the backup processes and dedicated disk were too small due to a lack of proper planning. It took less than a week to move and rebuild the indexes and we were able to backup the data and constraint indexes. Obviously, neither of these situations was ideal. In fact, the backup modification was purely a band-aid solution, but it allowed us to focus on backing up the critical components successfully. Daniel Fink Mercadante, Thomas F wrote: I disagree with the concept of recovery not including some indexes because they can be rebuilt later. To me, that's like going to a gas station and only filling the tank half-way because I can get more gas later. You are saving small amounts of time up front, but will pay for it later on. I prefer to restore a totally whole database (when needed), and not having to rely on my memory to rebuild some indexes that we purposly chose not to back up. Just seems silly to me. Tom Mercadante Oracle Certified Professionalbegin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: RE: should you separate indexes from tables in separate dataf
R, My personal theory on where this started was benchmarking. Before the Oracle Wait Interface was developed, about the only hard-core information you could get on tuning came as a result of people running benchmarks. If you benchmark a batch program by itself that uses an index to access a table, you will get much better performance if you place the index and table on separate devices. Now, as has been pointed out, in a busy multi-user system with many users, many tables and indexes, you shouldn't just put the indexes on separate devices and call it Miller time. You should observe the actual usage at a time when performance is critical (Cary Millsap does a nice job of describing this, although he focuses on finding critical applications), look for devices that are most heavily used, and move data files accordingly to spread the load. If you sill feel you must keep the table and indexes separate, you can criss-cross, i.e. put table A on device A and index A on device B, then table B on device B and index B on device A. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 10:29 AM To: Multiple recipients of list ORACLE-L datafiles does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: should you seperate indexes from tables in seperate datafiles
Hi! In some environments you just have to get some functions of database back online ASAP and deal with other issues (like reporting performance or not critical end users) later on. OTOH, my experience with OLTP environments has shown that if you when you lose performance indexes and try to use your database in full power, it just gets too damn slow anyway, so it's better to do the recovery. Not to mention, that if you got huge indexes, it's usually cheaper to restore recover the datafiles than build the index from scratch. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 6:39 PM I disagree with the concept of recovery not including some indexes because they can be rebuilt later. To me, that's like going to a gas station and only filling the tank half-way because I can get more gas later. You are saving small amounts of time up front, but will pay for it later on. I prefer to restore a totally whole database (when needed), and not having to rely on my memory to rebuild some indexes that we purposly chose not to back up. Just seems silly to me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L datafiles? I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes. Rachel Carmichael wrote: I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Re: should you seperate indexes from tables in seperate datafiles
- Original Message - I must say that I haven't actually seen the benefits myself but my faith is rock solid and I'll continue to separate data from indexes. Don't want to debate faith... However, the technical side I can. ;) The practice of separating indexes and tables into individual tablespaces comes from very early in the history of databases, as many here know. It made sense then in terms of performance, when databases were relatively small and the number of objects and users in a database was also small. Besides, in those days disks were discrete and controllers controlled each disk separately. It was easy to separate loads, for those who could be bothered and knew the arcane arts of disk partitioning and file system creation. Nowadays with databases of thousands of tables and indexes, with disk farms and multiple disk strings and monster caches and disk arrays and logical volume managers, it makes no sense whatsoever to separate tables from indexes *FROM THE PURE PERFORMANCE POINT OF VIEW*. However from the maintenance and management point of view, it makes a lot of sense. If you ever want to separate indexes to a different disk, it's infinitely easier to just move a set of datafiles making up a tablespace than to have to create tablespaces, move indexes, blah blah. In addition indexes tend to have different allocation patterns than tables, even in LMT. It makes sense to separate them from that point of view. There is another consideration which you brushed on: You normally don't want to mix the type of I/O for indexes (small, discrete blocks) from the potential stringed reads of a full table scan. They have different characteristics and cause all sorts of different I/O prioritization at controller and cache level. As such, they should not reside in the same logical device. This means in effect: not the same tablespace. Having said that, all sorts of tricks are possible at LVM level to avoid this even in single tablespace or single file system. But once again: this is all very relative and highly dependent on the type of database and its use, type of hardware, etc. Bottom line: separate if you feel comfortable doing so. You do NOT have to move the separate tablespaces into different disk devices: it all depends on I/O patterns, configuration, load management and so on. Analyze I/O patterns and loads, find the bottleneck (if there is one!) and solve it. Do not do things just because someone says they should/should not be done. And that includes the above. ;) Try and apply a one size fits all policy to your work and sooner or later you'll be replaced by a program or outsourced. You are a DBA: think. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: should you seperate indexes from tables in seperate datafiles?
Hi! There's not just head movement involved, there is disk rotational latency as well, and you have to cope with it whether even if your disk reading heads are in the same place. But more importantly, as Rachel already stated, that if you got multi-user environment (as a usual Oracle environment is), then you won't get no real benefit from separating data indexes on different disks, because disk heads will never be there where you leave them, there's always someone else who wants to read or write something. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 6:39 PM It's hot here. I wish I was at the beach and I feel like a rant. oracle actually accesses indexes and tables serially Is it just me or is this blindingly obvious? You cannot access the table data until you have completed accessing the index data because the index data contains the location of the table data. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. That is the argument for what it is worth. Real life is of course vastly more complex than this and we are swimming in very muddy waters, which is why there is so much argument on the subject (raid salesmen - spit). Thanks for the vent Garry Gillies [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 15/07/03 15:49 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:should you seperate indexes from tables in seperate datafiles? There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Bye all
Rajuveera, I hope that you find your continuing journey as a learning experince as well. Live long and prosper. Ron mª¿ªm [EMAIL PROTECTED] 07/15/03 11:10AM Dear Friends, I am going to unsubscribe from this list. It was a good journey towards learning. Thanks Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: should you seperate indexes from tables in seperate datafiles?
It's hot here. I wish I was at the beach and I feel like an ant. Hmmm. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. Assuming you get 4 rows returned :) If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. I would understand this if I had one index and one table. On a busy system with many users wouldn't the heads move, anyway, to serve queries that used other indexes and tables? Gudmundur -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni 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: RE: should you seperate indexes from tables in seperate dataf
thanks for the dis :) My experience with these types of recovery scenarios has been that the application *needs* the indexes that are being rebuilt. So user access is s slow that I should not have allowed the users back until all the indexes were rebuilt. The end result being that the recovery really is not complete until everything is done. Obviously, a better evaluation of indexes could have been performed. But my conclusion was to backup and restore everything - including indexes. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 11:54 AM To: Multiple recipients of list ORACLE-L datafiles if you keep a repository of all your indexes or have the create index statements on files in the file system.. that would solve the memory problem. oracle designer and erwin have these capabilities. in some cases would it be better to recovery without indexes first so the users will have some access to data, then build the indexes while the instance is online? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:39:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles I disagree with the concept of recovery not including some indexes because they can be rebuilt later. To me, that's like going to a gas station and only filling the tank half-way because I can get more gas later. You are saving small amounts of time up front, but will pay for it later on. I prefer to restore a totally whole database (when needed), and not having to rely on my memory to rebuild some indexes that we purposly chose not to back up. Just seems silly to me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L datafiles? I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes. Rachel Carmichael wrote: I separate indexes and tables into different tablespaces for maintenance purposes, not for performance, as there really is no performance benefit if you are on a system with multiple users. At any given time, many users will be doing queries that read the indexes and many users will be doing queries that read the tables. Besides, I don't get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) Maintenance: if I lose an index tablespace datafile, I can just offline/drop the tablespace and recreate it and the indexes within it rather than do recovery. My indexes and my tables tend to have different extent size requirements (most of my indexes are NOT comprised of all columns in the table) so I separate them for extent size purposes as well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
how do I test a restore of a raw device?
Hi all: I'm testing a restore of a database file (tools TS). The datafile is a raw device and I have link to that raw evice in my links directory. Im running Oracle 9203 and UNIX 5l. And the problem I ran into is as follows. If I drop the logical link , the rman restores the data as a datafile. If I remove the link and recreate it pointing to another raw device, the restore fails bacause the file exist. How do I force rman to restore the data in the raw device form? IS there a way to do it? thanks gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei 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 you seperate indexes from tables in seperate datafiles?
I may be way off base here, so any gurus please correct me with a gentle slap to the back of the head... Index and table access is not as simple as index entry..table row..index entry..table row..etc. I just ran a quick test (which may not be represntative and is using the primary key which can be understood as the row number in physical order of the data blocks) and I found (using the sequence of wait events) that there was substantial access to the index datafiles initially, followed by substantial access to the data datafiles. Then another single access to index, multiple access to data, single access to index, multiple access to data. It seems to me that this pattern is read several index blocks, then access several data blocks, read several index blocks, access several data blocks. This may be due to the sequential nature of the pk in the data blocks. It seems that the most efficient algorithm is to read enough index blocks to set up a list of data blocks to read, then go get them. Since you have the index block pinned, don't waste any resource in releasing the pin to pin the data blocks, then repin the index block. The other issue is that indexes can be accessed using multiblock reads (index fast full scan) and tables can be indexed using single block reads (table access by rowid). Garry Gillies wrote: It's hot here. I wish I was at the beach and I feel like a rant. oracle actually accesses indexes and tables serially Is it just me or is this blindingly obvious? You cannot access the table data until you have completed accessing the index data because the index data contains the location of the table data. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. That is the argument for what it is worth. Real life is of course vastly more complex than this and we are swimming in very muddy waters, which is why there is so much argument on the subject (raid salesmen - spit). Thanks for the vent Garry Gilliesbegin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: failed to archive
It's due to Oracle using multiple archivers. For every one that fails, you will find an another ARCX process that was already working on it. Using sequence number 87568 from below as an example, grep for that number in the alert log and see for yourself. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED]"Doing linear scans over an associative array is liketrying to club someone to death with a loaded Uzi." - Larry Wall (creator of Perl) -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 15, 2003 11:45 AMTo: Multiple recipients of list ORACLE-LSubject: failed to archive I see a lot of "failed to archive " messages in alert.log . But finally the log gets archived . Is it due overloaded archiver ? this is from alert log. ARC0: Beginning to archive log# 3 seq# 87568ARC0: Failed to archive log# 3 seq# 87568ARC0: Beginning to archive log# 2 seq# 87569Mon Jul 14 11:36:23 2003ARC4: Completed archiving log# 3 seq# 87568 -ak ocp dba 8i
Datafiles on SAN?
Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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: MicroSlop DTC
Unfortunately, it's not just a problem with this product. Some Oracle products like OEM can incorrectly populate TNSNAMES.ORA for you because of course that's always the best thing to do (and isn't there another Oracle product that requires it or am I confusing that with the semi-Intelligent Agent's requirement of a LISTENER.ORA?). And Quest's QCO will largely not work correctly in v2.4 (and to some extent in 2.5) without a correctly populated TNSNAMES.ORA (sorry Jacques!). I went around and around with Quest Support as to why this is incorrect and why I refuse to manually populate a TNSNAMES.ORA on some or all clients. Supposedly, it'll be fixed in v3. Desperately trying to get rid of all TNSNAMES.ORAs on all non-DBA boxes... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Yes, as well as SQL*Plus and ODBCTST. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hum, does tnsping resolve the service correctly? Ron Thomas 9.2.0.1.0 On Win 2K. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-L I've seen this happen with older versions of the sqlnet client (Different application, same symptom). What version of the client are you using? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To All, especially any WEB developers out there. We've a WEB based application that uses MS DTC. OK, so we turned on XA in the database, but the web servers do not want to play with our normal ONmase setup. Instead they only want to work with a TNSNAMES.ORA file in the appriopriate place. I've been all over MicroSlop Technet and Metalink as well as several other IIS sites with no results. Therefore anyone know why this is?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- 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).
Re: should you seperate indexes from tables in seperate datafiles?
Hi! During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. Actually, if you think a little more, then you see that physical IO doesn't occur like you described. If you got non-unique index range scan first time for example, you got index-access for index header block, then access to branch block in different location on disk (first branch block can be in completely different location on disk, dependent on extent sizes, number of datafiles in tablespace, datafile sizes and extensibility, even cardinality of index + other factors). Then next level branch block is probably in different location on disk again etc..) So, when you eventually get to leaf block (another physical IO), you have a number of entries (with rowids) there which match your where condition, and a physical IO has to be done for each block where we got matching rows (here comes the clustering factor into play - if matching rows are spread all over different blocks, we got lots of IOs to do). So, it's more like index-index-index-index IO + table-table-table-table IO. Next time there's hopefully no physical IO needed for index root block and maybe some other blocks as well, dependion on IO rate and buffer cache size etc.. And I believe, when index range scan is done, Oracle can optimize it's IO to batches, not having to initiate separate IO requests for every single row scanned but other wiser people might want to comment on it.. Tanel. -- 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).
RE: Sparky auth file
Yes and no. If I remember correctly (it's been a few months now), the lock on the trace file is probably a shareable write lock (RMS). So you can read/copy the file, but not open it for write/append. Changing the requested access on the open of the file may solve this. Someone correct me if I'm wrong... Yes, some of do miss VMS, Mladen! :) zipping up flame-proof suit Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Gudmundur Bjarni Josepsson [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 5:35 AM To: Multiple recipients of list ORACLE-L Subject: RE: Sparky auth file This was solved offline by examining the setup at one of my customers' site which has the same configuration of VMS, Oracle and Perl as Barb listed. Barb changed the setting of user_dump_dest to point to the actual directory that was defined in the hotsos.auth file. For some reason the logical was not resolved correctly. I am not proficient enough in VMS to explain that behaviour further. To save any future users of Sparky and VMS a few minutes of frustration I'd like to point out that you must remember to quit your session before Sparky can pull the trace file over to your PC. VMS does not release the file lock on the trace file even if you stop tracing. Gudmundur -- 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).
SGA question
hi is it possible to have a sga bigger than the rela memory available? suppose i have a 1gb ram can i start an instance with sga 2gb. does virtual memeory play a part in this memory allocation? thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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: SGA question
Don't do it. You should try to avoid paging. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Sai Selvaganesan Sent: Tuesday, July 15, 2003 11:49 AM To: Multiple recipients of list ORACLE-L hi is it possible to have a sga bigger than the rela memory available? suppose i have a 1gb ram can i start an instance with sga 2gb. does virtual memeory play a part in this memory allocation? thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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: Igor Neyman 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: Datafiles on SAN?
Yes. Yes. Not yet. We're VERY I/O bound on our 6-way HP K570 and an AutoRAID (see BAARF) for our ERP system running a hybrid (OLTP/reporting) 8.1.7.4 DB. We've tested an IBM FastT900, a smallish box in the SAN world, with wonderful preliminary results for us. I'm sure that the perf tuners here on the list have already predicted what we've seen. While our I/O bandwidth went up, that caused the CPUs to spin more to generate all those I/O requests. And while our tests showed a marked improvement in total response time, individual wait events are still high in some cases. I take this to possibly be evidence that if we were able to redesign our apps (purchased and homegrown) to reduce total I/O, maybe we wouldn't need new hardware. So what's the bottom line of this incoherent babble? We're going SAN. Our I/Os went from 400-500 peak on the AutoRAID to 22000 peak (cached) with 5000 sustained (uncached) on the SAN, with a mitigated 2GB cache (1GB mirrored, flushed). Hopefully, we'll be able to also upgrade the DB server next year to compensate for the inevitable CPU bottleneck. And while this is going on, we're attempting to change our design methods to better allow for scalability. Not that it'll help our purchased apps, though... One more note: because of time constraints, we were not able to test the SAN with anything close to our production DB/app K570 server (test box was a dual 2.4Ghz Windohs PC that was a pain to setup). The purpose of the test was to see what kind of raw and buffered I/O we could get from the FastT900 and a copy of our 30GB production DB. We will hopefully be testing the SAN on a K-box soon to see exactly what kind of CPU it's going to take to feed the I/O. HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Tim Levatich [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator -- 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).
RE: RE: should you separate indexes from tables in separate dataf
so there are benefits of splitting indexes and tables on different mount points in an instance used for batch loads? such as a data publication model where you ingest deltas? any data on this? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/07/15 Tue PM 12:04:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: should you separate indexes from tables in separate dataf R, My personal theory on where this started was benchmarking. Before the Oracle Wait Interface was developed, about the only hard-core information you could get on tuning came as a result of people running benchmarks. If you benchmark a batch program by itself that uses an index to access a table, you will get much better performance if you place the index and table on separate devices. Now, as has been pointed out, in a busy multi-user system with many users, many tables and indexes, you shouldn't just put the indexes on separate devices and call it Miller time. You should observe the actual usage at a time when performance is critical (Cary Millsap does a nice job of describing this, although he focuses on finding critical applications), look for devices that are most heavily used, and move data files accordingly to spread the load. If you sill feel you must keep the table and indexes separate, you can criss-cross, i.e. put table A on device A and index A on device B, then table B on device B and index B on device A. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 10:29 AM To: Multiple recipients of list ORACLE-L datafiles does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: should you seperate indexes from tables in seperate datafiles
Hrrr - as a wine-drinking, vegetarian, non-weightlifting new yawk city boy, this explains why I never fit in with the storage crowd However, to address the original idea about striping across lots of disks, etc., you have to be very careful about how you configure your storage volumes depending on your storage arrays. The intelligence that is built-in to high-end frames can be outsmarted (for better or worse) by certain storage configurations. Case in point - you have an EMC array that exposes 9 GB RAID-1 volumes that you use Veritas to create stripe sets across. You make a 10-volume RAID-0 stripe and following the match the filesystem block size to the oracle block size principle you make the stripe depth 8k. This makes a certain degree of sense - linear reads and writes getting distributed among a number of physical spindles, helps mitigate hotspots, etc. However, on a Symmetrix, this will yield poor(er) performance results. This is because of two factors - one, regardless of the I/O on the host side, the Symm will always do backend I/O and cache allocation in 32k objects and two, the symmetrix readahead won't kick in until it sees two or three sequential tracks being requested within a certain minimum amount of time. So, the small stripe size ends up unnecessarily placing objects in cache and negates the readahead that can provide large performance enhancements. There's a whole host of oddities like these that are present in all of the major storage vendors, so you have to be aware of what's going to happen. The moral of the story is, of course, the more expensive your storage array, the more you benefit by knowing the hows and whys of what your storage array does. Also try not to be too smart about how you set up your storage unless you have a very deep understanding of the intelligence behind the storage - it'll help keep you from shooting yourself in the foot. I've seen too many oracle DBAs spend hours creating a highly tuned storage configuration based on faulty or lacking information on how the storage array actually works and then they complain about how slow the array is Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Lee Sent: Tuesday, July 15, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: should you seperate indexes from tables in seperate datafiles Steroids, weight lifting, and a flattop hair cut (orange or green). After two years of this, try talking to the storage guys while holding a beer in one hand and a Polish sausage in the other. If you can manage a good belch during the conversation, even better. (Are you a visual person?) -Original Message- get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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: Datafiles on SAN?
I have them... one of the things that I can tell you is that... sometimes (at least here at work) the ports switches fail and you lost your connectivity to your filesystems... it does not mean that your database goes down... just have to reassign your LUNs to another SP processor and that's it... anyway, in my case I lost about 30 mins (the time to detect/reassign/correct the problem)... fortunately was a test database... I would like to help/comment you more, but, that's what we have seem until now.! HTH JL --- Tim Levatich [EMAIL PROTECTED] wrote: Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.edu http://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: SGA question
you will end up doing swap/paging . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:49 AM hi is it possible to have a sga bigger than the rela memory available? suppose i have a 1gb ram can i start an instance with sga 2gb. does virtual memeory play a part in this memory allocation? thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: should you seperate indexes from tables in seperate datafiles
why is it useful to seperate different i/o pattersn? such as multi-block reads and single block reads? From: Nuno Souto [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:59:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: should you seperate indexes from tables in seperate datafiles - Original Message - I must say that I haven't actually seen the benefits myself but my faith is rock solid and I'll continue to separate data from indexes. Don't want to debate faith... However, the technical side I can. ;) The practice of separating indexes and tables into individual tablespaces comes from very early in the history of databases, as many here know. It made sense then in terms of performance, when databases were relatively small and the number of objects and users in a database was also small. Besides, in those days disks were discrete and controllers controlled each disk separately. It was easy to separate loads, for those who could be bothered and knew the arcane arts of disk partitioning and file system creation. Nowadays with databases of thousands of tables and indexes, with disk farms and multiple disk strings and monster caches and disk arrays and logical volume managers, it makes no sense whatsoever to separate tables from indexes *FROM THE PURE PERFORMANCE POINT OF VIEW*. However from the maintenance and management point of view, it makes a lot of sense. If you ever want to separate indexes to a different disk, it's infinitely easier to just move a set of datafiles making up a tablespace than to have to create tablespaces, move indexes, blah blah. In addition indexes tend to have different allocation patterns than tables, even in LMT. It makes sense to separate them from that point of view. There is another consideration which you brushed on: You normally don't want to mix the type of I/O for indexes (small, discrete blocks) from the potential stringed reads of a full table scan. They have different characteristics and cause all sorts of different I/O prioritization at controller and cache level. As such, they should not reside in the same logical device. This means in effect: not the same tablespace. Having said that, all sorts of tricks are possible at LVM level to avoid this even in single tablespace or single file system. But once again: this is all very relative and highly dependent on the type of database and its use, type of hardware, etc. Bottom line: separate if you feel comfortable doing so. You do NOT have to move the separate tablespaces into different disk devices: it all depends on I/O patterns, configuration, load management and so on. Analyze I/O patterns and loads, find the bottleneck (if there is one!) and solve it. Do not do things just because someone says they should/should not be done. And that includes the above. ;) Try and apply a one size fits all policy to your work and sooner or later you'll be replaced by a program or outsourced. You are a DBA: think. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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 you seperate indexes from tables in seperate datafiles?
The thing that occurred to me a few years ago (as a result of a test designed by Craig Shallahamer) is that what disks do gets very, very complicated when you add users. On any system busy enough to have a performance problem, the odds are usually slim that a disk is just sitting there waiting for your next I/O call. On a busy system, someone else's I/O call is almost always going to intercede between two of *your* I/O calls. As has been said many times, many ways... - DO separate tables and indexes into different tablespaces. There are lots of reasons you should do this. - DON'T necessarily feel that you have to put the index and data tablespaces on different devices. One decision criterion is performance: don't ever put two files on the same device if the sum of their I/O-per-second rates exceeds the I/O-per-second capacity of the device. Another decision criterion is availability: don't ever put more data on a device than you can recover in your acceptable downtime window. The list goes on... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Daniel Fink Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L I may be way off base here, so any gurus please correct me with a gentle slap to the back of the head... Index and table access is not as simple as index entry..table row..index entry..table row..etc. I just ran a quick test (which may not be represntative and is using the primary key which can be understood as the row number in physical order of the data blocks) and I found (using the sequence of wait events) that there was substantial access to the index datafiles initially, followed by substantial access to the data datafiles. Then another single access to index, multiple access to data, single access to index, multiple access to data. It seems to me that this pattern is read several index blocks, then access several data blocks, read several index blocks, access several data blocks. This may be due to the sequential nature of the pk in the data blocks. It seems that the most efficient algorithm is to read enough index blocks to set up a list of data blocks to read, then go get them. Since you have the index block pinned, don't waste any resource in releasing the pin to pin the data blocks, then repin the index block. The other issue is that indexes can be accessed using multiblock reads (index fast full scan) and tables can be indexed using single block reads (table access by rowid). Garry Gillies wrote: It's hot here. I wish I was at the beach and I feel like a rant. oracle actually accesses indexes and tables serially Is it just me or is this blindingly obvious? You cannot access the table data until you have completed accessing the index data because the index data contains the location of the table data. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. That is the argument for what it is worth. Real life is of course vastly more complex than this and we are swimming in very muddy waters, which is why there is so much argument on the subject (raid salesmen - spit). Thanks for the vent Garry Gillies -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: should you separate indexes from tables in separate dataf
R, Just to avoid confusion, that wasn't what I meant by batch program benchmarking. Are you rebuilding indexes? I've seen some strong benefits when rebuilding indexes of having the index and table on separate devices. But then that is really easy for you to test. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 12:14 PM To: Multiple recipients of list ORACLE-L dataf so there are benefits of splitting indexes and tables on different mount points in an instance used for batch loads? such as a data publication model where you ingest deltas? any data on this? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/07/15 Tue PM 12:04:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: should you separate indexes from tables in separate dataf R, My personal theory on where this started was benchmarking. Before the Oracle Wait Interface was developed, about the only hard-core information you could get on tuning came as a result of people running benchmarks. If you benchmark a batch program by itself that uses an index to access a table, you will get much better performance if you place the index and table on separate devices. Now, as has been pointed out, in a busy multi-user system with many users, many tables and indexes, you shouldn't just put the indexes on separate devices and call it Miller time. You should observe the actual usage at a time when performance is critical (Cary Millsap does a nice job of describing this, although he focuses on finding critical applications), look for devices that are most heavily used, and move data files accordingly to spread the load. If you sill feel you must keep the table and indexes separate, you can criss-cross, i.e. put table A on device A and index A on device B, then table B on device B and index B on device A. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 10:29 AM To: Multiple recipients of list ORACLE-L datafiles does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). --
a script to list all privileges for a user heirarchically
Hi everyone I just answered a post on the server newsgroup from someone who wanted to know if a privilege had been granted to a particular user including mining through all of the roles granted hierarchically to roles etc. I posted a reference to a PL/SQL script I have knocked up as an answer there. I thought people here might be find it useful as well, its at http://www .petefinnigan.com/tools.htm, and its called find_all_privs.sql. A sample run is here: SQL @find_all_privs get user input NAME OF USER TO CHECK [ORCL]: PETE OUTPUT METHOD [S/F]: S FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [/tmp]: old 162:lv_file_or_screen:='output_method'; new 162:lv_file_or_screen:='S'; old 164:open_file('file_name','output_dir'); new 164:open_file('priv.lst','/tmp'); old 166:get_privs('user_to_find',lv_tabs); new 166:get_privs('PETE',lv_tabs); ...USER = PETE has ROLE CONNECT which contains = ..SYS PRIV =ALTER SESSION grantable = NO ..SYS PRIV =CREATE CLUSTER grantable = NO ..SYS PRIV =CREATE DATABASE LINK grantable = NO ..SYS PRIV =CREATE SEQUENCE grantable = NO ..SYS PRIV =CREATE SESSION grantable = NO ..SYS PRIV =CREATE SYNONYM grantable = NO ..SYS PRIV =CREATE TABLE grantable = NO ..SYS PRIV =CREATE VIEW grantable = NO ...USER = PETE has ROLE RESOURCE which contains = ..SYS PRIV =CREATE CLUSTER grantable = NO ..SYS PRIV =CREATE INDEXTYPE grantable = NO ..SYS PRIV =CREATE OPERATOR grantable = NO ..SYS PRIV =CREATE PROCEDURE grantable = NO ..SYS PRIV =CREATE SEQUENCE grantable = NO ..SYS PRIV =CREATE TABLE grantable = NO ..SYS PRIV =CREATE TRIGGER grantable = NO ..SYS PRIV =CREATE TYPE grantable = NO ...USER = PETE has ROLE UNIX_ADMIN which contains = ..USER = UNIX_ADMIN has ROLE ADMIN which contains = .SYS PRIV =ALTER USER grantable = NO .SYS PRIV =CREATE USER grantable = NO ..SYS PRIV =CREATE CLUSTER grantable = NO ...SYS PRIV =CREATE DATABASE LINK grantable = NO ...SYS PRIV =CREATE SESSION grantable = NO ...SYS PRIV =UNLIMITED TABLESPACE grantable = NO ...TABLE PRIV =SELECT table_name = V_$SESSION grantable = NO PL/SQL procedure successfully completed. SQL you can choose to either send output to the screen via dbms_output or to a file via utl_file. choose 'S' or 'F' at run time and if you choose 'F' specify a file name and directory. Anyway its there if anyone would find it useful. kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: Datafiles on SAN?
Hundreds, nay, thousands put their datafiles on SAN. All love it. All would trade their children for more SAN storage. None have ever had a problem. :) Seriously, though, some huge percentage of storage being configured today is SAN and a big chunk of that is database storage. It by and large works fine, in that its just as good as SCSI-attached, only generally faster and you can put the array farther away from the host :) The gotchas tend to come up in more complex environments with things like combining multiple san vendors, different operating systems, remote replication, snapshots, etc. etc. But just hooking up hosts to fibre channel storage and sending commands tends to go off flawlessly. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Levatich Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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: Matthew Zito 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).
AIX 5.2 Or AIX 5.1 for Oracle 9.2 database server?
Hi, friends: There is a project that migrate a 9.0.1.3 database on linux server to 9.2.0.3 on aix/p670 server, and I have some question about this project, hope friends in the list can share your idears. 1.Shall I use AIX 5.1 or AIX 5.2? According to pagev http://www-1.ibm.com/servers/aix/os/52features.html, the most attractive of 5.2 is the jfs2 and large page support,cpu-guard and virtual IP Address. But not sure whether Oracle 9.2 will support large page or not(9.2 on linux does support large page of 2MB).Multipath IO is also avaliable for aix5.2. But 5.2 is even newer than 5.1 and I am not sure whether it is stable or not? Does anyone here have experience running 9.2 on aix 5.2? What is the current ML level? Do I need to apply any special patch for database to intall and run smoothly? 2.If using AIX 5.2, we should use 64bit kernel, and according to document, by default it will use jfs2.In jfs2, there is a feature called concurrent IO.Concurrent IO provide much better performance while it is a rather new feature, shall we use it for better performance? Have anyone used it? 3.If using multipathing, will the same disk be recognized by aix as different hdiskN? If yes,How do i solve this problem? Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: should you seperate indexes from tables in seperate datafiles
if I can still find it. :) I know Kevin did say different disks because I remember coming home from that presentation and trying to explain to my data center people why I needed something like 17 disks for my database. my apologies over the years, people have made the assumption that different tablespaces=different disks --- Cary Millsap [EMAIL PROTECTED] wrote: :) Look carefully at the OFA paper. It does *not* say to put indexes and data on different *disks*. It says to put them in different *tablespaces*. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Rachel Carmichael Sent: Tuesday, July 15, 2003 10:44 AM To: Multiple recipients of list ORACLE-L datafiles yes, it's in the DBA Handbook but I believe I did see a paper (with, I think, Cary's name on it. Sorry Cary!) way back when that discussed OFA and how to set up your disks and it said to put the indexes in a separate tablespace on a different disk back before the days of wait event tuning that is --- [EMAIL PROTECTED] wrote: does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network
Re: Datafiles on SAN?
Hi, JL: I think you need the multipathing solution for your platform. In a SAN environment, there is more devices, so more chance that device fail. So you need multiple path from host to storage. That means multipathing IO support, like Veritas DMP or Sun AP or HP pvlinks or storage specific solutions. Good luck. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 1:19 AM I have them... one of the things that I can tell you is that... sometimes (at least here at work) the ports switches fail and you lost your connectivity to your filesystems... it does not mean that your database goes down... just have to reassign your LUNs to another SP processor and that's it... anyway, in my case I lost about 30 mins (the time to detect/reassign/correct the problem)... fortunately was a test database... I would like to help/comment you more, but, that's what we have seem until now.! HTH JL --- Tim Levatich [EMAIL PROTECTED] wrote: Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.edu http://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: zhu chao 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: Datafiles on SAN?
Its all fine unless some jacka$$ starts pulling fiber cables w/o paying attention, then the paths die, databases crash, etc. joe Matthew Zito wrote: Hundreds, nay, thousands put their datafiles on SAN. All love it. All would trade their children for more SAN storage. None have ever had a problem. :) Seriously, though, some huge percentage of storage being configured today is SAN and a big chunk of that is database storage. It by and large works fine, in that its just as good as SCSI-attached, only generally faster and you can put the array farther away from the host :) The gotchas tend to come up in more complex environments with things like combining multiple san vendors, different operating systems, remote replication, snapshots, etc. etc. But just hooking up hosts to fibre channel storage and sending commands tends to go off flawlessly. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Levatich Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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). -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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 you seperate indexes from tables in seperate datafiles?
I LOVE THIS LIST! A few more personalities (GAJA, ARI, IAN .) opinions added to this discussion and we could publish a paper on this thread alone. Thank you GURUS You are the ones that make monitoring this list worth more than anything ! Luis -Original Message- Sent: Tuesday, July 15, 2003 11:20 AM To: Multiple recipients of list ORACLE-L datafiles? The thing that occurred to me a few years ago (as a result of a test designed by Craig Shallahamer) is that what disks do gets very, very complicated when you add users. On any system busy enough to have a performance problem, the odds are usually slim that a disk is just sitting there waiting for your next I/O call. On a busy system, someone else's I/O call is almost always going to intercede between two of *your* I/O calls. As has been said many times, many ways... - DO separate tables and indexes into different tablespaces. There are lots of reasons you should do this. - DON'T necessarily feel that you have to put the index and data tablespaces on different devices. One decision criterion is performance: don't ever put two files on the same device if the sum of their I/O-per-second rates exceeds the I/O-per-second capacity of the device. Another decision criterion is availability: don't ever put more data on a device than you can recover in your acceptable downtime window. The list goes on... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Daniel Fink Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L I may be way off base here, so any gurus please correct me with a gentle slap to the back of the head... Index and table access is not as simple as index entry..table row..index entry..table row..etc. I just ran a quick test (which may not be represntative and is using the primary key which can be understood as the row number in physical order of the data blocks) and I found (using the sequence of wait events) that there was substantial access to the index datafiles initially, followed by substantial access to the data datafiles. Then another single access to index, multiple access to data, single access to index, multiple access to data. It seems to me that this pattern is read several index blocks, then access several data blocks, read several index blocks, access several data blocks. This may be due to the sequential nature of the pk in the data blocks. It seems that the most efficient algorithm is to read enough index blocks to set up a list of data blocks to read, then go get them. Since you have the index block pinned, don't waste any resource in releasing the pin to pin the data blocks, then repin the index block. The other issue is that indexes can be accessed using multiblock reads (index fast full scan) and tables can be indexed using single block reads (table access by rowid). Garry Gillies wrote: It's hot here. I wish I was at the beach and I feel like a rant. oracle actually accesses indexes and tables serially Is it just me or is this blindingly obvious? You cannot access the table data until you have completed accessing the index data because the index data contains the location of the table data. During an indexed query on a single table the index will be accessed, then the table, then the index,then the table, then the index,then the table then the index,then the table. If the index and the table are on the same disk then a lot of time will be taken up by head seek movement. If they are on the different disks then the index heads can locate their data and stay there - and the data heads can locate their data and stay there. Less head movement, less wasted time. That is the argument for what it is worth. Real life is of course vastly more complex than this and we are swimming in very muddy waters, which is why there is so much argument on the subject (raid salesmen - spit). Thanks for the vent Garry Gillies -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, 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: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: should you seperate indexes from tables in seperate datafiles
Matt Thanks so much for your posting. I especially appreciated your comment try not to be too smart. Would you consider writing a book on the topic of I/O Devices for the Oracle DBA? I would like to learn more, but don't know where to begin. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 15, 2003 12:15 PM To: Multiple recipients of list ORACLE-L datafiles Hrrr - as a wine-drinking, vegetarian, non-weightlifting new yawk city boy, this explains why I never fit in with the storage crowd However, to address the original idea about striping across lots of disks, etc., you have to be very careful about how you configure your storage volumes depending on your storage arrays. The intelligence that is built-in to high-end frames can be outsmarted (for better or worse) by certain storage configurations. Case in point - you have an EMC array that exposes 9 GB RAID-1 volumes that you use Veritas to create stripe sets across. You make a 10-volume RAID-0 stripe and following the match the filesystem block size to the oracle block size principle you make the stripe depth 8k. This makes a certain degree of sense - linear reads and writes getting distributed among a number of physical spindles, helps mitigate hotspots, etc. However, on a Symmetrix, this will yield poor(er) performance results. This is because of two factors - one, regardless of the I/O on the host side, the Symm will always do backend I/O and cache allocation in 32k objects and two, the symmetrix readahead won't kick in until it sees two or three sequential tracks being requested within a certain minimum amount of time. So, the small stripe size ends up unnecessarily placing objects in cache and negates the readahead that can provide large performance enhancements. There's a whole host of oddities like these that are present in all of the major storage vendors, so you have to be aware of what's going to happen. The moral of the story is, of course, the more expensive your storage array, the more you benefit by knowing the hows and whys of what your storage array does. Also try not to be too smart about how you set up your storage unless you have a very deep understanding of the intelligence behind the storage - it'll help keep you from shooting yourself in the foot. I've seen too many oracle DBAs spend hours creating a highly tuned storage configuration based on faulty or lacking information on how the storage array actually works and then they complain about how slow the array is Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Lee Sent: Tuesday, July 15, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: should you seperate indexes from tables in seperate datafiles Steroids, weight lifting, and a flattop hair cut (orange or green). After two years of this, try talking to the storage guys while holding a beer in one hand and a Polish sausage in the other. If you can manage a good belch during the conversation, even better. (Are you a visual person?) -Original Message- get to control how my disks are set up (part of that now now little girl, don't you worry your pretty little head about how the disks are set up, you just leave that sort of stuff to us big male data center operations people crap I get) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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
Re: a script to list all privileges for a user heirarchically
nice! and very usable, as I'm trying to mine through privileges and tighten security here. thanks much! --- Pete Finnigan [EMAIL PROTECTED] wrote: Hi everyone I just answered a post on the server newsgroup from someone who wanted to know if a privilege had been granted to a particular user including mining through all of the roles granted hierarchically to roles etc. I posted a reference to a PL/SQL script I have knocked up as an answer there. I thought people here might be find it useful as well, its at http://www .petefinnigan.com/tools.htm, and its called find_all_privs.sql. A sample run is here: SQL @find_all_privs get user input NAME OF USER TO CHECK [ORCL]: PETE OUTPUT METHOD [S/F]: S FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [/tmp]: old 162:lv_file_or_screen:='output_method'; new 162:lv_file_or_screen:='S'; old 164:open_file('file_name','output_dir'); new 164:open_file('priv.lst','/tmp'); old 166:get_privs('user_to_find',lv_tabs); new 166:get_privs('PETE',lv_tabs); ...USER = PETE has ROLE CONNECT which contains = ..SYS PRIV =ALTER SESSION grantable = NO ..SYS PRIV =CREATE CLUSTER grantable = NO ..SYS PRIV =CREATE DATABASE LINK grantable = NO ..SYS PRIV =CREATE SEQUENCE grantable = NO ..SYS PRIV =CREATE SESSION grantable = NO ..SYS PRIV =CREATE SYNONYM grantable = NO ..SYS PRIV =CREATE TABLE grantable = NO ..SYS PRIV =CREATE VIEW grantable = NO ...USER = PETE has ROLE RESOURCE which contains = ..SYS PRIV =CREATE CLUSTER grantable = NO ..SYS PRIV =CREATE INDEXTYPE grantable = NO ..SYS PRIV =CREATE OPERATOR grantable = NO ..SYS PRIV =CREATE PROCEDURE grantable = NO ..SYS PRIV =CREATE SEQUENCE grantable = NO ..SYS PRIV =CREATE TABLE grantable = NO ..SYS PRIV =CREATE TRIGGER grantable = NO ..SYS PRIV =CREATE TYPE grantable = NO ...USER = PETE has ROLE UNIX_ADMIN which contains = ..USER = UNIX_ADMIN has ROLE ADMIN which contains = .SYS PRIV =ALTER USER grantable = NO .SYS PRIV =CREATE USER grantable = NO ..SYS PRIV =CREATE CLUSTER grantable = NO ...SYS PRIV =CREATE DATABASE LINK grantable = NO ...SYS PRIV =CREATE SESSION grantable = NO ...SYS PRIV =UNLIMITED TABLESPACE grantable = NO ...TABLE PRIV =SELECT table_name = V_$SESSION grantable = NO PL/SQL procedure successfully completed. SQL you can choose to either send output to the screen via dbms_output or to a file via utl_file. choose 'S' or 'F' at run time and if you choose 'F' specify a file name and directory. Anyway its there if anyone would find it useful. kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Datafiles on SAN?
Has any rolled their own SAN? We've got a bunch of stuff on EMC but now we're looking to build our own fibre channel SAN and replace EMC NFS with clustered file systems. (Of course Oracle is not on NFS.) Disk may be cheap but vendor SAN boxes are not. Steve Orr Bozeman, MT -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Hundreds, nay, thousands put their datafiles on SAN. All love it. All would trade their children for more SAN storage. None have ever had a problem. :) Seriously, though, some huge percentage of storage being configured today is SAN and a big chunk of that is database storage. It by and large works fine, in that its just as good as SCSI-attached, only generally faster and you can put the array farther away from the host :) The gotchas tend to come up in more complex environments with things like combining multiple san vendors, different operating systems, remote replication, snapshots, etc. etc. But just hooking up hosts to fibre channel storage and sending commands tends to go off flawlessly. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Levatich Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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: Matthew Zito 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: Orr, Steve 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: MicroSlop DTC
Jesse, You could always get around this problem by obtaining an upto date TNSNAMES.ORA on any Names-aware client using the 'namesctl dump_tnsnames' command that will create/update the tnsnames.ora with the entries from Names servers. I would suggest renaming the original tnsnames.ora _just_ before doing this, and having some script check the sizes of the created file (just in case). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Unfortunately, it's not just a problem with this product. Some Oracle products like OEM can incorrectly populate TNSNAMES.ORA for you because of course that's always the best thing to do (and isn't there another Oracle product that requires it or am I confusing that with the semi-Intelligent Agent's requirement of a LISTENER.ORA?). And Quest's QCO will largely not work correctly in v2.4 (and to some extent in 2.5) without a correctly populated TNSNAMES.ORA (sorry Jacques!). I went around and around with Quest Support as to why this is incorrect and why I refuse to manually populate a TNSNAMES.ORA on some or all clients. Supposedly, it'll be fixed in v3. Desperately trying to get rid of all TNSNAMES.ORAs on all non-DBA boxes... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Yes, as well as SQL*Plus and ODBCTST. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hum, does tnsping resolve the service correctly? Ron Thomas 9.2.0.1.0 On Win 2K. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-L I've seen this happen with older versions of the sqlnet client (Different application, same symptom). What version of the client are you using? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To All, especially any WEB developers out there. We've a WEB based application that uses MS DTC. OK, so we turned on XA in the database, but the web servers do not want to play with our normal ONmase setup. Instead they only want to work with a TNSNAMES.ORA file in the appriopriate place. I've been all over MicroSlop Technet and Metalink as well as several other IIS sites with no results. Therefore anyone know why this is?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SGA question
surely i will not do it. but my question is whether it is possible at all to do it. will oracle when allocating shared memory space take virtual memory into consideration or only real memory into consideration. thanks sai --- AK [EMAIL PROTECTED] wrote: you will end up doing swap/paging . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:49 AM hi is it possible to have a sga bigger than the rela memory available? suppose i have a 1gb ram can i start an instance with sga 2gb. does virtual memeory play a part in this memory allocation? thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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: AK 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: Sai Selvaganesan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: should you seperate indexes from tables in seperate datafiles
If you can't find it, you can download it again from http://www.hotsos.com/catalog. I *promise* I haven't cheated and edited the document since the date that's published on its cover. Lots of people at the time (~1992) were preaching to separate indexes and data on different disks. The OFA gave you the *ability* to separate indexes from data on disk (if your I/O rates were high enough to motivate it), but I tried to avoid saying anything in that document that either wasn't true to begin with, or that was likely change as technology evolved. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Rachel Carmichael Sent: Tuesday, July 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L datafiles if I can still find it. :) I know Kevin did say different disks because I remember coming home from that presentation and trying to explain to my data center people why I needed something like 17 disks for my database. my apologies over the years, people have made the assumption that different tablespaces=different disks --- Cary Millsap [EMAIL PROTECTED] wrote: :) Look carefully at the OFA paper. It does *not* say to put indexes and data on different *disks*. It says to put them in different *tablespaces*. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Rachel Carmichael Sent: Tuesday, July 15, 2003 10:44 AM To: Multiple recipients of list ORACLE-L datafiles yes, it's in the DBA Handbook but I believe I did see a paper (with, I think, Cary's name on it. Sorry Cary!) way back when that discussed OFA and how to set up your disks and it said to put the indexes in a separate tablespace on a different disk back before the days of wait event tuning that is --- [EMAIL PROTECTED] wrote: does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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
System
John Kanagaraj [EMAIL PROTECTED] wrote: Jesse,You could always get around this problem by obtaining an upto dateTNSNAMES.ORA on any Names-aware client using the 'namesctl dump_tnsnames'command that will create/update the tnsnames.ora with the entries from Namesservers. I would suggest renaming the original tnsnames.ora _just_ beforedoing this, and having some script check the sizes of the created file (justin case). John KanagarajOracle Applications DBADBSoft Inc(W): 408-970-7002What would you see if you were allowed to look back at your life at the endof your journey in this earth?** The opinions and statements above are entirely my own and not those of myemployer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:25 AM To: Multiple reci! pients of list ORACLE-L Subject: RE: MicroSlop DTC Unfortunately, it's not just a problem with this product. Some Oracle products like OEM can incorrectly populate TNSNAMES.ORA "for you" because of course that's always the best thing to do (and isn't there another Oracle product that requires it or am I confusing that with the semi-Intelligent Agent's requirement of a LISTENER.ORA?). And Quest's QCO will largely not work correctly in v2.4 (and to some extent in 2.5) without a correctly populated TNSNAMES.ORA (sorry Jacques!). I went around and around with Quest Support as to why this is incorrect and why I refuse to manually populate a TNSNAMES.ORA on some or all clients. Supposedly, it'll be fixed in v3. Desperately trying to get rid of all TNSNAMES.ORAs on all non-DBA boxes... &! gt; Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Yes, as well as SQL*Plus and ODBCTST.Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 2:59 PM To: Multiple recipients of list ORACLE-LHum, does tnsping resolve the service correctly?Ron Thomas 9.2.0.1.0 On Win 2K.Dick Goulet Senior Oracle DBA! t; Oracle Certified 8i DBA-Original Message- Sent: Monday, July 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-LI've seen this happen with older versions of the sqlnet client (Different application, same symptom). What version of the client are you using?Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan[EMAIL PROTECTED]To All, especially any WEB developers out there.We've a WEB based application that uses MS DTC. OK, so we turned on XA in the database, but the web servers do not want to play with our normal ONmase setup. Instead ! they only want to work with a TNSNAMES.ORA file in the appriopriate place. I've been all over MicroSlop Technet and Metalink as well as several other IIS sites with no results. Therefore anyone know why this is??Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L! (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: John KanagarajINET: [EMAIL PROTECTED]Fat City 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!? The New Yahoo! Search - Faster. Easier. Bingo.
Interview Questions for a Unix Solaris System Admin
I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia M Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
RE: Datafiles on SAN?
Gadzoox!! www.gadzoox.com -Original Message- Sent: Tuesday, July 15, 2003 11:19 AM To: Multiple recipients of list ORACLE-L I have them... one of the things that I can tell you is that... sometimes (at least here at work) the ports switches fail and you lost your connectivity to your filesystems... it does not mean that your database goes down... just have to reassign your LUNs to another SP processor and that's it... anyway, in my case I lost about 30 mins (the time to detect/reassign/correct the problem)... fortunately was a test database... I would like to help/comment you more, but, that's what we have seem until now.! HTH JL --- Tim Levatich [EMAIL PROTECTED] wrote: Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.edu http://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Orr, Steve 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: Interview Questions for a Unix Solaris System Admin
question #1: Do you realize that your DBA is a God, and you will obey his/her edicts without question? question #2: Are you aware of the daily offering of food/beer required to keep in your God's (DBA's) good graces? etc... Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject: Interview Questions for a Unix Solaris System Admin I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia M _ Do you Yahoo!? The New Yahoo! Search http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com - Faster. Easier. Bingo. -- 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: RE: should you seperate indexes from tables in seperate datafiles
Oh I'm sure I have a copy at home, somewhere. I'm a packrat. I was an almost brand-new DBA at the time, so I was on information overload then, trying to learn Oracle and as as much as I could, all at once (you try becoming the Oracle DBA without ever having seen Oracle before!) and yes, you do try (and as far as I can tell, succeed) not to say things that won't stay true. Blame my faulty memory on advancing old age :) --- Cary Millsap [EMAIL PROTECTED] wrote: If you can't find it, you can download it again from http://www.hotsos.com/catalog. I *promise* I haven't cheated and edited the document since the date that's published on its cover. Lots of people at the time (~1992) were preaching to separate indexes and data on different disks. The OFA gave you the *ability* to separate indexes from data on disk (if your I/O rates were high enough to motivate it), but I tried to avoid saying anything in that document that either wasn't true to begin with, or that was likely change as technology evolved. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Rachel Carmichael Sent: Tuesday, July 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L datafiles if I can still find it. :) I know Kevin did say different disks because I remember coming home from that presentation and trying to explain to my data center people why I needed something like 17 disks for my database. my apologies over the years, people have made the assumption that different tablespaces=different disks --- Cary Millsap [EMAIL PROTECTED] wrote: :) Look carefully at the OFA paper. It does *not* say to put indexes and data on different *disks*. It says to put them in different *tablespaces*. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Rachel Carmichael Sent: Tuesday, July 15, 2003 10:44 AM To: Multiple recipients of list ORACLE-L datafiles yes, it's in the DBA Handbook but I believe I did see a paper (with, I think, Cary's name on it. Sorry Cary!) way back when that discussed OFA and how to set up your disks and it said to put the indexes in a separate tablespace on a different disk back before the days of wait event tuning that is --- [EMAIL PROTECTED] wrote: does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a different text? From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 11:10:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: should you seperate indexes from tables in seperate datafiles R, Some of it depends on the disk storage. I have always followed the time-proven method of organizing disks and placing indexes away from the tables they belong to. Our warehouse is using EMC external disk. What the warehouse architect did was to stripe the EMC disks in such a way that all mount points (Sun system) are spread across all the EMC disks. What this does is to spread all files in the database across all the EMC drives. And with 4 Gig of EMC cache available, it further disproves the theory that separing indexes from data are required. The end result, in my case, is almost like one big RAM disk - where all disk IO is spread across all disk. If you do not have this arrangement, then I would still try and keep indexes and data away from each other. But let's face it, we *never* have enough disk mount points, so we end up merging things together somewhat anyway. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 15, 2003 10:49 AM To: Multiple recipients of list ORACLE-L There has been alot of literature stating that you will recieve performance improvements by seperating indexes and tables across multiple I/O points. Ie... you have a tables tablespace and an index tablespace. If you put them on seperate hard drives, you will have less I/O contention. Now Im seeing some articles stating that this is not true. That oracle actually accesses indexes and tables serially. Now it might be useful seperate indexes from tables for maintenance purposes but this wont lower I/O contention. Can anyone chime in on this? Curious to see where the evidence is leading? -- Please see the official ORACLE-L FAQ:
RE: MicroSlop DTC
John, True, but the whole purpose is to get rid of those damned TNSNAMES.ora files in the first place. I don't know about you, but the fun to tracing why a end user can't get to the database because he has his own alias to the database buried in a TNSNAMES.ora file is a real PAIN. BTW: I tried the TNS_ADMIN setting in the registry only to have it ignored. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 15, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Jesse, You could always get around this problem by obtaining an upto date TNSNAMES.ORA on any Names-aware client using the 'namesctl dump_tnsnames' command that will create/update the tnsnames.ora with the entries from Names servers. I would suggest renaming the original tnsnames.ora _just_ before doing this, and having some script check the sizes of the created file (just in case). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Unfortunately, it's not just a problem with this product. Some Oracle products like OEM can incorrectly populate TNSNAMES.ORA for you because of course that's always the best thing to do (and isn't there another Oracle product that requires it or am I confusing that with the semi-Intelligent Agent's requirement of a LISTENER.ORA?). And Quest's QCO will largely not work correctly in v2.4 (and to some extent in 2.5) without a correctly populated TNSNAMES.ORA (sorry Jacques!). I went around and around with Quest Support as to why this is incorrect and why I refuse to manually populate a TNSNAMES.ORA on some or all clients. Supposedly, it'll be fixed in v3. Desperately trying to get rid of all TNSNAMES.ORAs on all non-DBA boxes... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Yes, as well as SQL*Plus and ODBCTST. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hum, does tnsping resolve the service correctly? Ron Thomas 9.2.0.1.0 On Win 2K. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-L I've seen this happen with older versions of the sqlnet client (Different application, same symptom). What version of the client are you using? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To All, especially any WEB developers out there. We've a WEB based application that uses MS DTC. OK, so we turned on XA in the database, but the web servers do not want to play with our normal ONmase setup. Instead they only want to work with a TNSNAMES.ORA file in the appriopriate place. I've been all over MicroSlop Technet and Metalink as well as several other IIS sites with no results. Therefore anyone know why this is?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
Re: SGA question
If it take only real mem in consideration why would pageing happen at all ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:19 AM surely i will not do it. but my question is whether it is possible at all to do it. will oracle when allocating shared memory space take virtual memory into consideration or only real memory into consideration. thanks sai --- AK [EMAIL PROTECTED] wrote: you will end up doing swap/paging . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:49 AM hi is it possible to have a sga bigger than the rela memory available? suppose i have a 1gb ram can i start an instance with sga 2gb. does virtual memeory play a part in this memory allocation? thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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: AK 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: Sai Selvaganesan 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: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle listener (auto increment value for PK)
Hi Markus! Here's a recipe that might help you (assuming your table is called MYTABLE: 1. create a sequence: CREATE SEQUENCE MYTABLE_Sequence start with 1 increment by 1 nocycle nocache; 2. create an insert trigger CREATE OR REPLACE TRIGGER MYTABLE_Insert_Trigger BEFORE INSERT ON MYTABLE FOR EACH ROW DECLARE n PLS_INTEGER; BEGIN -- get the next auto increment value SELECT MYTABLE_Sequence.NEXTVAL INTO n FROM DUAL; -- assign the value to the ID of the row that is being inserted :NEW.ID:=n; END; / HTH Servus! Andreas -Original Message- Sent: Monday, July 14, 2003 10:39 PM To: Multiple recipients of list ORACLE-L hi i run oracle8 on w2000 advanced server. my question is how to create a auto increment primary key for this table: id number(5) name varchar(12) id should auto increment after inserting a new datarow must i create a trigger ? excuse my bad english thx martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: pfeffer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interview Questions for a Unix Solaris System Admin
I've been in the same situation, I had to interview the company's sysadmin, though I am not one. Here you have a few points to start your list. Regards Gabriel The candidate must provide knolwedge about (how to's): 1. Start and stop a solaris system, including several levels of boot (1,2,3, etc). 2. Patch installation. 3. App's installation and configuration (kernel and semaphores). 4. Users creation. 5. Partitions creation. 6. Mount and unmount FileSystems. 7. DNS, IP configuration. 8. Monitoring and performance tuning (process, priorities, memory, disk, etc). 9. Services configuration SMTP, FTP, Internet, secure conections (HTTP, HTTPs). 10. Scripts programming. 11. Establish backup/recovery strategies. Plus: - Define Sun equipment architecture. --- M.Godlewski [EMAIL PROTECTED] wrote: I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia M - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle listener (auto increment value for PK)
Sorry I meant Hi Martin -Original Message- Sent: Tuesday, July 15, 2003 8:59 PM To: Multiple recipients of list ORACLE-L Hi Markus! Here's a recipe that might help you (assuming your table is called MYTABLE: 1. create a sequence: CREATE SEQUENCE MYTABLE_Sequence start with 1 increment by 1 nocycle nocache; 2. create an insert trigger CREATE OR REPLACE TRIGGER MYTABLE_Insert_Trigger BEFORE INSERT ON MYTABLE FOR EACH ROW DECLARE n PLS_INTEGER; BEGIN -- get the next auto increment value SELECT MYTABLE_Sequence.NEXTVAL INTO n FROM DUAL; -- assign the value to the ID of the row that is being inserted :NEW.ID:=n; END; / -- 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: Interview Questions for a Unix Solaris System Admin
Okay, here are my favorites for senior candidates (I'm giving all my secrets away...): 1) What is an inode? Bonus: What important piece of file information is NOT stored in the inode? 2) What is priority paging and how does it work? (mildly dated, but useful if they claim to have been around for a while) 3) What does sr stand for in vmstat output? 4) How would I configure the gigabit ethernet interface to force it to be full duplex? 5) How does RAID-5 work? Bonus question: how does raid-4 work? Extra-extra bonus question: how does raid-3 work? 6) What's the difference between the passwd and the shadow files? 7) What's the difference between the dsk and rdsk devices in /dev? Bonus question: what's the difference between a block and a character device? 8) How do journaling filesystems work? 9) What's the difference between ssh and telnet? Why is one preferable over the other? 10) What's the difference between the e4000 and the e4500 (or e6000 and e6500, etc. - also a bit dated, but there's still a million of the things out there) 11) What happens on an E6500 when I add boards in the bottom two slots? (I won't ask this if the person has never touched an E6500) 12) On an Sbus e-class I/O tray, what performance considerations do I have to keep in mind when I'm installing Sbus cards? 13) Why is NIS bad? 14) What's the difference between TCP and UDP? 15) How does DNS work? Bonus question: is DNS TCP or UDP? Then I usually throw in some amorphous questions: tell me about a performance problem you tracked down and solved, how do you normally secure a freshly installed Solaris server, etc. Then I follow up with product specific questions - oracle, sun cluster, veritas volume manager, storage, etc. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Interview Questions for a Unix Solaris System Admin question #1: Do you realize that your DBA is a God, and you will obey his/her edicts without question? question #2: Are you aware of the daily offering of food/beer required to keep in your God's (DBA's) good graces? etc... Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject:Interview Questions for a Unix Solaris System Admin I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia M _ Do you Yahoo!? The New Yahoo! Search http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com - Faster. Easier. Bingo. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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: Datafiles on SAN?
Eh - same problem with SCSI, except SCSI cables have the neat little screws to make that harder. It's a good point, though - a SAN is a network. For proper redundancy, you need two separate fabrics (read: redundant paths from storage to host that pass through two different switches, with the switches NOT being cross-connected) and some sort of software such as Veritas DMP to handle multi-path and failover. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joe Testa Sent: Tuesday, July 15, 2003 12:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: Datafiles on SAN? Its all fine unless some jacka$$ starts pulling fiber cables w/o paying attention, then the paths die, databases crash, etc. joe Matthew Zito wrote: Hundreds, nay, thousands put their datafiles on SAN. All love it. All would trade their children for more SAN storage. None have ever had a problem. :) Seriously, though, some huge percentage of storage being configured today is SAN and a big chunk of that is database storage. It by and large works fine, in that its just as good as SCSI-attached, only generally faster and you can put the array farther away from the host :) The gotchas tend to come up in more complex environments with things like combining multiple san vendors, different operating systems, remote replication, snapshots, etc. etc. But just hooking up hosts to fibre channel storage and sending commands tends to go off flawlessly. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Levatich Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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). -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: Matthew Zito 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: Interview Questions for a Unix Solaris System Admin
These are nice questions. I'm not sure how I would identify if they are technical enough to handle the job though.[EMAIL PROTECTED] wrote: question #1: Do you realize that your DBA is a God, and you will obeyhis/her edicts without question?question #2: Are you aware of the daily offering of food/beer required tokeep in your God's (DBA's) good graces?etc...Scott ShaferSan Antonio, TX210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject: Interview Questions for a Unix Solaris System Admin I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some?tia ! M _ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.-- 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.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!? The New Yahoo! Search - Faster. Easier. Bingo.
RE: Datafiles on SAN?
Well, you can run Oracle over Netapp NFS, which is far superior to EMC's Celerra (their NFS product), except in a few niche features. By the way, Netapp just released their FAS250 low-end filer - up to 1TB usable in 3U, pretty speedy, and damn cheap. Rolling your own SAN is certainly doable, but Fibre Channel is fraught with implementation and interop problems. If you're set on doing it, get someone who's done SAN implementations before to oversee it, and get _written_ signoff from each vendor you're using that they'll guarantee interop. Then test the heck out of it. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Orr, Steve Sent: Tuesday, July 15, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Datafiles on SAN? Has any rolled their own SAN? We've got a bunch of stuff on EMC but now we're looking to build our own fibre channel SAN and replace EMC NFS with clustered file systems. (Of course Oracle is not on NFS.) Disk may be cheap but vendor SAN boxes are not. Steve Orr Bozeman, MT -Original Message- Sent: Tuesday, July 15, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Hundreds, nay, thousands put their datafiles on SAN. All love it. All would trade their children for more SAN storage. None have ever had a problem. :) Seriously, though, some huge percentage of storage being configured today is SAN and a big chunk of that is database storage. It by and large works fine, in that its just as good as SCSI-attached, only generally faster and you can put the array farther away from the host :) The gotchas tend to come up in more complex environments with things like combining multiple san vendors, different operating systems, remote replication, snapshots, etc. etc. But just hooking up hosts to fibre channel storage and sending commands tends to go off flawlessly. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Levatich Sent: Tuesday, July 15, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: Datafiles on SAN? Is anyone putting datafiles on SAN storage? Success? Horror?Tell me a story. ~ Tim Levatich, Database Administrator Cornell Laboratory of Ornithology, 159 Sapsucker Woods Road, Ithaca, New York 14850 [EMAIL PROTECTED]phone 607-254-2113fax 607-254-2415 http://birds.cornell.eduhttp://birdsource.cornell.edu ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Levatich 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: Matthew Zito 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: Orr, Steve 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: Interview Questions for a Unix Solaris System Admin
You only spend time on the technical stuff if they pass this round. Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Interview Questions for a Unix Solaris System Admin These are nice questions. I'm not sure how I would identify if they are technical enough to handle the job though. [EMAIL PROTECTED] wrote: question #1: Do you realize that your DBA is a God, and you will obey his/her edicts without question? question #2: Are you aware of the daily offering of food/beer required to keep in your God's (DBA's) good graces? etc... Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject: Interview Questions for a Unix Solaris System Admin I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia ! M _ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do you Yahoo!? The New Yahoo! Search http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com - Faster. Easier. Bingo. -- 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).