RE: Function based indexes
Sergey, I did a quick test which indicates that no special path is mentioned when using a FBI, instead the execution plan will show that an index is being used, which it would not do if a FBI had not been set up. The example below shows what I mean HTH John SQL create index john_idx1 on john(spid_type); SQL select spid_type from john where upper(substr(spid_type,2,1)) = 'Y' SQL / SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'JOHN' SQL drop index john_idx1; Index dropped. SQL create index john_idx1 on john(upper(substr(spid_type,2,1))); Index created. SQL analyze table john compute statistics; Table analyzed. SQL select spid_type from john where upper(substr(spid_type,2,1)) = 'Y'; SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony 8 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=96 Bytes= 1344) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'JOHN' (Cost=7 Card=96 By *FBI in use tes=1344) 2 1 INDEX (RANGE SCAN) OF 'JOHN_IDX1' (NON-UNIQUE) (Cost=1 C *FBI in use ard=96) -Original Message-From: Babich , Sergey [mailto:[EMAIL PROTECTED]]Sent: 26 February 2002 20:51To: Multiple recipients of list ORACLE-LSubject: Function based indexes Hi, everyone, This may seem very simple to you, but what's the best way to see if a fresh FBI (sorry!) is used during the execution? Are they reported in the same manner to the SQL trace as other ones? Regards, Sergey = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Manager decrees his data warehouse design. Help!
Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 À : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Query Tool
Hello Mike You should try PlSqlDeveloper from AllAroundAutomation. A nice friendly tool that lets you create queries with GUI. No need to know SQL. The cost was, when we bought it, was 750$ for 10 users license. I did not check reporting capabilities but a good tool for queries and updates. See: http://www.allroundautomations.com Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Bond Mike A Contr OC-ALC/TILC [SMTP:[EMAIL PROTECTED]] Sent: Tue, February 26, 2002 9:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: Database Query Tool Dennis, You are absolutely correct. These users know nothing of SQL and will probably never be able (due to lack of interest) to do more than change search parameters in queries that I write and provide to them. They need access to Oracle to validate the transfer of data from IMS on the mainframe (their home planet) to Oracle. We are very sensitive to keeping this thing simple and may wind up creating custom forms for them to use (with one big button). You last comment was interesting though, but I am already on the hook to make the recommendation AND to train them. Thanks, Mike -Original Message- Sent: Tuesday, February 26, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Mike - One critical question - Will you be creating reports for the users or will they be creating the reports by themselves? We have had a lot of trouble where IS would buy a query or reporting tool with the idea that the users would be self-sufficient. Then it turns out that IS uses the tool. Actually SQL was originally created as a user query interface. How many users do you see today creating their own SQL statements? Sorry to be cynical, but we've gotten bitten on that one before. This last time IS stayed out of it, let the users pick their own tool, the users got the training and no IS people were trained. It seems to be working pretty good. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 26, 2002 8:13 AM To: Multiple recipients of list ORACLE-L Mike, There are a lot of tools like that on the market. My favorite for several years was Discoverer or Oracle Browser as it was then known. Recently though I've switched camps to Brio One from Brio Technology. Reason, Discoverer is part parcel with 9IAS Enterprise that product is an unstable pig (CPU Disk). Had it here for a month late last year. Even the Oracle consultant could not get it installed working. Took a long night ton of reading plus help from OTS then we could only get it to work on Netscape. Dick Goulet Reply Separator Author: Bond Mike A Contr OC-ALC/TILC [EMAIL PROTECTED] Date: 2/25/2002 2:48 PM Hi, I was asked to recommend a Query Tool for some of our end users. I have used Q+E aka DataDirect Explorer for years. Now I can't find it on the internet. It has changed owners many times and may not even be available now. Does any one have a recommendation for a COTS product that allows the user with minimum knowledge of SQL to create SQL queries, views tables, store/recall queries, ... Is there an Oracle product that does that? Discoverer seems to be a bit heavy handed. Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bond Mike A Contr OC-ALC/TILC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access /
RE: DBMS_STATS in 8.1.6
We had trouble with analyze during heavy updates. Something to do with SNAPSHOT TOO OLD. The instance froze. It is corrected somewhere in 8.1.7. Oracle will not port the fix to 8.1.6. We schedule the analyze to work at night and no problems. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Jesse, Rich [SMTP:[EMAIL PROTECTED]] Sent: Tue, February 26, 2002 12:39 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_STATS in 8.1.6 Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: moving from unix to NT
Thanks to everyone for their input into this. The only real issue seems to be UTL_FILE on network drives. 1. I was interested in the many references to Perl as an alternative to using UTL_FILE. Could any of you provide more detail. I know nothing about Perl so would be interested in how to replace the use of UTL_FILE in PL/SQL with Perl. We use UTL_FILE quite a lot for reading and writing flat files. 2. With regard to external procedures, On Unix we currently use this to call a C routine that calls the system command to run Unix commands and scripts(Korn Shell). I presume we will need to amend these commands to their NT equivalents(or can I call Windows API directly from PL/SQL? on NT) and re-write the scripts...presumably in Perl? It will probbably be Oracle 9i on NT. John -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: 26 February 2002 21:37 To: Multiple recipients of list ORACLE-L Subject: Re: moving from unix to NT I haven't tried to do this with Oracle, I just knew that you could. My use has been to change the account that is used for some of my monitors that need to see network drives. I've never had a need to make Oracle run as other than System. As for UTL_FILE, I avoid it like the plague. Perl is much cleaner and easier to use. Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 10:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, I was having all kinds of problems, when I was playing with this option, trying to make oracle service on nt to run under other then SYSTEM account. And yes, I granted this account any possible NT privilege (like ability to run/logon as a service), still didn't work. Jared, Could you share some details on this issue, if you still remember how you managed to make this working? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 1:14 PM Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. You can change that if you're so inclined. ( I can't believe I'm defending Windoze. shudder ) Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, you shouldn't:) Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 25, 2002 9:38 PM On UTL_FILE, I've never had much luck using network mounted files. Igor Neyman ineyman To: Multiple recipients of list ORACLE-L @perceptron.c[EMAIL PROTECTED] om cc: Sent by: rootSubject: Re: moving from unix to NT 02/25/2002 12:18 PM Please respond to ORACLE-L For external procedures just follow the rules for creating DLLs on NT, works fine. DBMS_JOB works fine. For UTL_FILE make sure, you are following NT conventions, when specifying file path(use back slash '\', not front slash '/'). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 25, 2002 11:43 AM We are contemplating moving our application from Unix to NT. The main things that concerm me are where our application interfaces with the OS, via a C external procedure, and also via UTL_FILE. Also, can I expect DBMS_JOB to work OK? Anyone got experience of porting this sort of functionality to NT? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: ORA-00600 errors
17090 is a JDBC error. you can find it at : oracle 9i doc\java.901\a90211\ermesap.htm Could not find 17172 anywhere. ORA-17090 operation not allowed Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Mandal, Ashoke [SMTP:[EMAIL PROTECTED]] Sent: Mon, February 25, 2002 7:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-00600 errors Check the content of trace file. -Original Message- Sent: Monday, February 25, 2002 9:56 AM To: Multiple recipients of list ORACLE-L can anyone tell me waht these errors might mean? Errors in file /u01/app/oracle/admin/vaddev/udump/vaddev_ora_6942.trc: ORA-00600: internal error code, arguments: [17172], [0], [], [], [], [], [], [] Thu Feb 21 03:10:56 2002 Errors in file /u01/app/oracle/admin/vaddev/bdump/vaddev_snp2_10163.trc: ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [] John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HUGE numbers is V$SYSTAT
Steve, Do you have the BUG#? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Monday, February 25, 2002 3:48 PM To: Multiple recipients of list ORACLE-L OWS confirmed it's a bug and needs to be back ported for Linux. Sigh... -Original Message- Sent: Friday, February 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Steve, The number 18,446,744,069,414,584,320 is 0x000. So looks like some of the counters are hitting their max values. I clearly suspect this could be a BUG. Have you directly queried $ksusgsta? If your X$KSUSSGTA shows correct values then there is something went wrong during the conversion. Otherwise this could be a BUG. BTW Which version of Oracle? Looks like you are on some 64 Bit Oracle? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Friday, February 15, 2002 4:48 PM To: Multiple recipients of list ORACLE-L I'm finding HUGE numbers in the V$SYSTAT table. For instance for statistic# 1, logons current the number is 18,446,744,069,414,584,320... I don't think so! What's the cause and what's the cure? I used know about this but now I forget. Sigh... Steve Orr Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RETURNING clause
Hi list, I am firing foll query from oracle forms INSERT INTO TABLE_NAME (col1,col2,..) VALUES(val1,val2,..) RETURNING ROWID INTO var_rowid. it displays me follwoing error. ORA-00439:feature not enabled:RETURNING clause from this client type Is there any way to enable this feature from client ? (i know that in Forms ,there is a property of BLOCK 'DML returning value :YES/NO' but my table is not attached to the block ) (same query works fine from sqlplus) (Forms 6i, Orcale 8.1.6) Any help is appreciated Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 À : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@ M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
Re: Re[2]: address parse
To throw a totally different twist into the combination: The new IRS ( yer favorite U.S. institution) form for corporations to report taxable income for individuals requires--- 1.If the address of the individual is in the U.S. use the street1,street1,city,county,state,zip fields. 2.If the address of the individual is outside the U.S. place all the information into 1 field. makes for a nightmare to parse the information. ROR mª¿ªm [EMAIL PROTECTED] 02/26/02 04:12PM John, We're actually messing with you a little bit, having fun at your incomplete question. Not only are there many things that could be considered an address, some of them have several components that could be combined in several different ways. Not only that, but there are different ways that you might choose to represent those components. A common street address example would be that given something like: '1293 Incomplete Drive, Suite 2001, Mail Stop H, Specification City, Oklahoma, 74953-0011' And a common set of fields to parse it into would be: AddressLine1 AddressLine2 City State Zip When stating a parsing problem both the input form and the output form need to specified. Also any peculiar rules. Above you'd need to state things like: -Assume USA address -Comma separated fields -City state and zip are last three fields -First field always AddressLine1 -If 4 fields AddressLine2 left null -If 5 fields then field 2 is AddressLine2 -If 6 or more fields, then fields 2 - (n-3) are concatenated separated by commas in AddressLine2 -State will be stored as 2 character state code -Zip can be either 5 digit or 9 digit (no dash) codes Now given all that, a parse routine could be written. But lacking such a specification, the question is very open for various interpretation, any of which has only a remote chance of meeting your needs. -rje S street address S -Original Message- S Sent: Tuesday, February 26, 2002 10:55 AM S To: Multiple recipients of list ORACLE-L S Anybody already have an address string parser (plsql) already written S that S they would care to share? S Address? IP? Internet mail? USPS? Memory address? URL? -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need some white papers on replication
Orafaw is a dead link for me from here... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 26, 2002 5:16 PM To: Multiple recipients of list ORACLE-L Subject:RE: Need some white papers on replication Bill: Anita Bardeen's Replication DOs and DON'Ts is excellent. All of Lawrence To's White Papers are good Graceful Switchover and Switchback, Oracle Standby Database Oracle8i Standby Database Mission Critical Recovery Within 30 Minutes I believe these are all on Metalink. www.orafaw.com/fawrepl.htm as an Oracle Replication FAQ Sorry, don't know of any good book recommendations. Barb -- From: Bill Conner[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Subject: Need some white papers on replication Hi All, i really need some papers on replication and any book recommendations. TiA!! -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Conner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle learning network v Oracle CBT-Select
We are considering whether or not to invest in Oracles CBT-select computer based training CDs or instead use the Oracle Learning Network(which appears to be free to OPP members) Anyone got any experience of using either or both? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: address parse
Robert must be new to the list. He is MUCH too nice. :) -Original Message- Sent: Tuesday, February 26, 2002 4:12 PM To: Multiple recipients of list ORACLE-L John, We're actually messing with you a little bit, having fun at your incomplete question. Not only are there many things that could be considered an address, some of them have several components that could be combined in several different ways. Not only that, but there are different ways that you might choose to represent those components. A common street address example would be that given something like: '1293 Incomplete Drive, Suite 2001, Mail Stop H, Specification City, Oklahoma, 74953-0011' And a common set of fields to parse it into would be: AddressLine1 AddressLine2 City State Zip When stating a parsing problem both the input form and the output form need to specified. Also any peculiar rules. Above you'd need to state things like: -Assume USA address -Comma separated fields -City state and zip are last three fields -First field always AddressLine1 -If 4 fields AddressLine2 left null -If 5 fields then field 2 is AddressLine2 -If 6 or more fields, then fields 2 - (n-3) are concatenated separated by commas in AddressLine2 -State will be stored as 2 character state code -Zip can be either 5 digit or 9 digit (no dash) codes Now given all that, a parse routine could be written. But lacking such a specification, the question is very open for various interpretation, any of which has only a remote chance of meeting your needs. -rje S street address S -Original Message- S Sent: Tuesday, February 26, 2002 10:55 AM S To: Multiple recipients of list ORACLE-L S Anybody already have an address string parser (plsql) already written S that S they would care to share? S Address? IP? Internet mail? USPS? Memory address? URL? -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Old Chestnut: Tablespace Fragmentation
I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need some white papers on replication
try www.orafaq.com/faqrepl.htm Dave -Original Message- Sent: Wednesday, February 27, 2002 6:38 AM To: Multiple recipients of list ORACLE-L Orafaw is a dead link for me from here... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 26, 2002 5:16 PM To: Multiple recipients of list ORACLE-L Subject:RE: Need some white papers on replication Bill: Anita Bardeen's Replication DOs and DON'Ts is excellent. All of Lawrence To's White Papers are good Graceful Switchover and Switchback, Oracle Standby Database Oracle8i Standby Database Mission Critical Recovery Within 30 Minutes I believe these are all on Metalink. www.orafaw.com/fawrepl.htm as an Oracle Replication FAQ Sorry, don't know of any good book recommendations. Barb -- From: Bill Conner[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Subject: Need some white papers on replication Hi All, i really need some papers on replication and any book recommendations. TiA!! -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Conner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RETURNING clause
I never used 'RETURNING' clause with pseudo-column like ROWID. Usually I'm using it to return a value of the sequence, used to insert the row: INSERT INTO TABLE_NAME (col1,col2,..) VALUES(col1_SEQ.NEXTVAL,val2,..) RETURNING col1 INTO col1_var. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 6:13 AM Hi list, I am firing foll query from oracle forms INSERT INTO TABLE_NAME (col1,col2,..) VALUES(val1,val2,..) RETURNING ROWID INTO var_rowid. it displays me follwoing error. ORA-00439:feature not enabled:RETURNING clause from this client type Is there any way to enable this feature from client ? (i know that in Forms ,there is a property of BLOCK 'DML returning value :YES/NO' but my table is not attached to the block ) (same query works fine from sqlplus) (Forms 6i, Orcale 8.1.6) Any help is appreciated Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Global variable in Pl/SQL
thanks a lot to all who replied ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: 26 February 2002 23:24 To: Multiple recipients of list ORACLE-L Andrey, You can't make a PL/SQL variable value visible to all sessions. You'll have to put the value in a row in a table and commit it. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, February 26, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Dear list ! How can i make a package variable visible to all sessions , please ? I.e. i have a package PPP that has a package variable VVV. There is a stored proc in the package , PPP.SP1 In this procedure i set the package variable VVV to some value. Now , i want all the sessions connected to the instance to be able to see this value of PPP.VVV . Thanks a lot in advance ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Control File locks
Of course,he is Steve Adams. Who else can give me such nice script? He suspect we had application design problem. We had serious performance problem associate with the cf lock. Joan Gogala, Mladen wrote: No, it's not a problem, it's just a matter of scientific interest. Your query looks very good, I like it. Who is Steve that you have mentioned in your post? -Original Message- From: Joan Hsieh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 4:12 PM To: Multiple recipients of list ORACLE-L Subject: Re: Control File locks Last year when I worked at a internet company. We had same problem. CF constantly get locked. Steve asked me to run this query. I am not sure it will help you or not? But at least it give you some idea which sql caused the lock. It was a mystery for us, never get solved. By the way, do you have st lock problem? Joan column resource format a8 column sid format a4 justify right column sql_text format a38 wor break on resource select /*+ rule */ l.type || '-' || l.id1 || '-' || l.id2 RESOURCE, nvl(b.name, lpad(to_char(l.sid), 4)) sid, decode( l.lmode, 1, ' N', 2, ' SS', 3, ' SX', 4, ' S', 5, 'SSX', 6, ' X' ) holding, decode( l.request, 1, ' N', 2, ' SS', 3, ' SX', 4, ' S', 5, 'SSX', 6, ' X' ) wanting, l.ctime seconds, q.sql_text from sys.v_$lock l, sys.v_$session s, sys.v_$bgprocess b, sys.v_$sql q where l.type in ('CF', 'ST') and s.sid = l.sid and b.paddr (+) = s.paddr and q.address (+) = s.sql_address order by l.type || '-' || l.id1 || '-' || l.id2, sign(l.request), l.ctime desc / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need some white papers on replication
I believe he had a typo, I got to http://www.orafaq.com/faqrepl.htm just fine. (Replace w's with q's - ;) I've never heard of orafaw, but have orafaq. -Original Message- Sent: Wednesday, February 27, 2002 7:38 AM To: Multiple recipients of list ORACLE-L Orafaw is a dead link for me from here... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 26, 2002 5:16 PM To: Multiple recipients of list ORACLE-L Subject:RE: Need some white papers on replication Bill: Anita Bardeen's Replication DOs and DON'Ts is excellent. All of Lawrence To's White Papers are good Graceful Switchover and Switchback, Oracle Standby Database Oracle8i Standby Database Mission Critical Recovery Within 30 Minutes I believe these are all on Metalink. www.orafaw.com/fawrepl.htm as an Oracle Replication FAQ Sorry, don't know of any good book recommendations. Barb -- From: Bill Conner[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Subject: Need some white papers on replication Hi All, i really need some papers on replication and any book recommendations. TiA!! -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Conner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Cupp INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[4]: address parse
MTF Robert must be new to the list. He is MUCH too nice. :) Sh! I'm playing good cop/bad cop.. :-) -rje MTF -Original Message- MTF Sent: Tuesday, February 26, 2002 4:12 PM MTF To: Multiple recipients of list ORACLE-L MTF John, MTF We're actually messing with you a little bit, having fun at your MTF incomplete question. Not only are there many things that could be MTF considered an address, some of them have several components that could MTF be combined in several different ways. Not only that, but there are MTF different ways that you might choose to represent those components. MTF A common street address example would be that given something like: MTF '1293 Incomplete Drive, Suite 2001, Mail Stop H, Specification City, MTF Oklahoma, 74953-0011' MTF And a common set of fields to parse it into would be: MTF AddressLine1 MTF AddressLine2 MTF City MTF State MTF Zip MTF When stating a parsing problem both the input form and the output form MTF need to specified. Also any peculiar rules. Above you'd need to MTF state things like: MTF -Assume USA address MTF -Comma separated fields MTF -City state and zip are last three fields MTF -First field always AddressLine1 MTF -If 4 fields AddressLine2 left null MTF -If 5 fields then field 2 is AddressLine2 MTF -If 6 or more fields, then fields 2 - (n-3) are concatenated separated MTF by commas in AddressLine2 MTF -State will be stored as 2 character state code MTF -Zip can be either 5 digit or 9 digit (no dash) codes MTF Now given all that, a parse routine could be written. But lacking MTF such a specification, the question is very open for various MTF interpretation, any of which has only a remote chance of meeting your MTF needs. MTF -rje S street address S -Original Message- S Sent: Tuesday, February 26, 2002 10:55 AM S To: Multiple recipients of list ORACLE-L S Anybody already have an address string parser (plsql) already written S that S they would care to share? S Address? IP? Internet mail? USPS? Memory address? URL? MTF -rje -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: address parse
http://www.runnertechnologies.com/clean_addr_features.html Haven't used their product, I had just received a flyer from them some time ago. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Manager decrees his data warehouse design. Help!
Um, then that's not a data warehouse... -Original Message- Sent: Wednesday, February 27, 2002 1:48 AM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Manager decrees his data warehouse design. Help!
1. Run, don't walk, to monster.com. 2. Update and print resume 3. Enjoy vacation (hopefully brief) Nothing good is going to come of the warehouse Don dondealy @teleport.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Manager decrees his data warehouse design. Help! 02/27/2002 01:48 AM Please respond to ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle learning network v Oracle CBT-Select
OLN is free for OPP members however, you need outside access to Real Player. Because of the many bugs in RealPlayer's Browser, we have closed off RealPlayer and hence OLN to our office. OLN is a nice feature and does offer many courses. However, you are also depending on Oracle's web site to be available at any time. Several times OLN was not available. Good luck. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L Subject:Oracle learning network v Oracle CBT-Select We are considering whether or not to invest in Oracles CBT-select computer based training CDs or instead use the Oracle Learning Network(which appears to be free to OPP members) Anyone got any experience of using either or both? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HUGE numbers is V$SYSTAT
BUG# 1854275. Once again it looks like Oracle is slower to fix bugs on Linux that on Solaris. :-( -Original Message- Sent: Wednesday, February 27, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Steve, Do you have the BUG#? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Monday, February 25, 2002 3:48 PM To: Multiple recipients of list ORACLE-L OWS confirmed it's a bug and needs to be back ported for Linux. Sigh... -Original Message- Sent: Friday, February 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Steve, The number 18,446,744,069,414,584,320 is 0x000. So looks like some of the counters are hitting their max values. I clearly suspect this could be a BUG. Have you directly queried $ksusgsta? If your X$KSUSSGTA shows correct values then there is something went wrong during the conversion. Otherwise this could be a BUG. BTW Which version of Oracle? Looks like you are on some 64 Bit Oracle? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Friday, February 15, 2002 4:48 PM To: Multiple recipients of list ORACLE-L I'm finding HUGE numbers in the V$SYSTAT table. For instance for statistic# 1, logons current the number is 18,446,744,069,414,584,320... I don't think so! What's the cause and what's the cure? I used know about this but now I forget. Sigh... Steve Orr Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need some white papers on replication
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 7:38 AM To: Multiple recipients of list ORACLE-L Orafaw is a dead link for me from here... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 26, 2002 5:16 PM To: Multiple recipients of list ORACLE-L Subject:RE: Need some white papers on replication Bill: Anita Bardeen's Replication DOs and DON'Ts is excellent. All of Lawrence To's White Papers are good Graceful Switchover and Switchback, Oracle Standby Database Oracle8i Standby Database Mission Critical Recovery Within 30 Minutes I believe these are all on Metalink. www.orafaw.com/fawrepl.htm as an Oracle Replication FAQ Sorry, don't know of any good book recommendations. Barb -- From: Bill Conner[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Subject: Need some white papers on replication Hi All, i really need some papers on replication and any book recommendations. TiA!! -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Conner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Your best best is to quantify this mathematically. Take the following example: Case 1: 100GB table, one extent Case 2: 100GB table, 1000 extents Assume: a) track to track seeks are 'free' b) random seeks are 20ms c) Block size is 16KB d) db_file_multiblock_read_count=16 e) multiblock read time=8.6ms (29MB/s conservative for 10k drives) f) total # reads=409600 g) one drive only (a very big one...) Case1: Time for FTS= 409600*8.6ms=3522s (~ 1 hour) Case2: Time for FTS= 3522s (as above) PLUS 1000*20ms= 20s - TOTAL=3542s The difference is minor in this case (0.5% greater elapsed time) and 1000 extents would put each at ~100MB in this case. If you had cue Dr. Evil voice 1 million extents, it would be a different story - about 668% longer... Hope that helps - there's an infinite number of shades of grey, so it's important to do the math! Regards James Bill Buchan wrote: I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of Scaling Oracle8i - Building Highly Scalable OLTP System Architectures -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Morle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
IMHO, yes you're right, but the little bit of extra disk head movement is going to be insignificant because of the overall size of the transaction. In a perfect world, no tables would ever be fragmented. But the trade off is in maintenance. You're going to go through alot of work to keep your one large table always contiguous, keep your data files always contiguous to shave a few millseconds off a long transaction. Beth -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City
Re: Manager decrees his data warehouse design. Help!
Point #6 - I did not know Win98 can run under 64k RAM. Tell your Boss he needs at least 16MB RAM for Win98 : 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HUGE numbers is V$SYSTAT
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 6:03 AM To: Multiple recipients of list ORACLE-L Steve, Do you have the BUG#? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Monday, February 25, 2002 3:48 PM To: Multiple recipients of list ORACLE-L OWS confirmed it's a bug and needs to be back ported for Linux. Sigh... -Original Message- Sent: Friday, February 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Steve, The number 18,446,744,069,414,584,320 is 0x000. So looks like some of the counters are hitting their max values. I clearly suspect this could be a BUG. Have you directly queried $ksusgsta? If your X$KSUSSGTA shows correct values then there is something went wrong during the conversion. Otherwise this could be a BUG. BTW Which version of Oracle? Looks like you are on some 64 Bit Oracle? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Friday, February 15, 2002 4:48 PM To: Multiple recipients of list ORACLE-L I'm finding HUGE numbers in the V$SYSTAT table. For instance for statistic# 1, logons current the number is 18,446,744,069,414,584,320... I don't think so! What's the cause and what's the cure? I used know about this but now I forget. Sigh... Steve Orr Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
RE: Manager decrees his data warehouse design. Help!
When I was consulting at a particular client, I saw the effects of this approach after the fact. Massive amounts of data were loaded into tables that were never accessed except to load data because the users couldn't do anything with it. Later it was thought that to fix this all they had to do was send the users to training on the reporting tool. After they spent lots of money on training nothing changed because the users still didn't understand the data and couldn't do anything with it. Meanwhile, damangement checked off it's accomplishment of an objective on the HR management forms. I suspect that may be what you're dealing with. After all, isn't it more important to report that you did something that to actually do something worthwhile? ;-) The notion that DBA's aren't needed and all you have to do is load data into a relational database and give the end users a point and click GUI tool is foolish but not uncommon with shortsided damagement. Steve Orr -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
Your damager should be committed in the nearest mental institution ASAP. The guy is a moron. -Original Message- Sent: Wednesday, February 27, 2002 8:28 AM To: Multiple recipients of list ORACLE-L 1. Run, don't walk, to monster.com. 2. Update and print resume 3. Enjoy vacation (hopefully brief) Nothing good is going to come of the warehouse Don dondealy @teleport.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Manager decrees his data warehouse design. Help! 02/27/2002 01:48 AM Please respond to ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Manager decrees his data warehouse design. Help!
You have this decree in writing? Okay, once you get that. Do what he wants, making sure everyone knows that this great new database design and application are all his idea (do this with a smile, with enthusiasm if you can manage it) hang on and wait for it all to fall apart. Otherwise, do you have any friends or connections in the user base? or to his manager? Write up, without emotion, what you see as the problems to this approach. Be very logical, with explicit reasons (not this is crap) pass it around. and be prepared to make an enemy --- Don [EMAIL PROTECTED] wrote: I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Bill - My Tech. Service Manager keeps reminding me that disk isn't so simple anymore. You are probably on RAID for the higher read performance. Now your file is broken across several disks. Of course, to get the straight read, the controller can't service anyone else's requests while your scan continues uninterrupted. Just some other thoughts. By the way, yesterday I was able to reduce the full-table scan time on our data warehouse from over 2 minutes to below 10 seconds. I broke the table into 54 partitions so the most common queries were able to scan just the minimum amount of the table they need. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 9:04 AM To: Multiple recipients of list ORACLE-L IMHO, yes you're right, but the little bit of extra disk head movement is going to be insignificant because of the overall size of the transaction. In a perfect world, no tables would ever be fragmented. But the trade off is in maintenance. You're going to go through alot of work to keep your one large table always contiguous, keep your data files always contiguous to shave a few millseconds off a long transaction. Beth -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RETURNING clause
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 6:13 AM To: Multiple recipients of list ORACLE-L Hi list, I am firing foll query from oracle forms INSERT INTO TABLE_NAME (col1,col2,..) VALUES(val1,val2,..) RETURNING ROWID INTO var_rowid. it displays me follwoing error. ORA-00439:feature not enabled:RETURNING clause from this client type Is there any way to enable this feature from client ? (i know that in Forms ,there is a property of BLOCK 'DML returning value :YES/NO' but my table is not attached to the block ) (same query works fine from sqlplus) (Forms 6i, Orcale 8.1.6) Any help is appreciated Thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Bill, It sounds like you are describing an ideal situation. Is this scan being done by only one user at a time? Then you are describing a dedicated database to one user? Lets face it, the above is not even remotely probable in todays world. And furthur, if you decided that the above setup is what you want, then how do you apply new records to the table - a full reload every time? It seems way to much work in hopes that the resulting query might be faster. In my humble opinion, there seems to be *way* too much time devoted to worrying about table extents and disk access. Disk are soo much faster today, that I've decided that it really is not worth considering very much. Of course, I'm not currently working on a high-volume application right now, so my radar is focused on other things. Interesting idea, though. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
We Have the same issue here. Large Tables , loaded nightly But nobody uses them. Management calls it a Datawarehouse, I call it a data repository. Can a cusotmer find out what they need. NOPE Document your findings, to C.Y.A. Good Luck --- Orr, Steve [EMAIL PROTECTED] wrote: When I was consulting at a particular client, I saw the effects of this approach after the fact. Massive amounts of data were loaded into tables that were never accessed except to load data because the users couldn't do anything with it. Later it was thought that to fix this all they had to do was send the users to training on the reporting tool. After they spent lots of money on training nothing changed because the users still didn't understand the data and couldn't do anything with it. Meanwhile, damangement checked off it's accomplishment of an objective on the HR management forms. I suspect that may be what you're dealing with. After all, isn't it more important to report that you did something that to actually do something worthwhile? ;-) The notion that DBA's aren't needed and all you have to do is load data into a relational database and give the end users a point and click GUI tool is foolish but not uncommon with shortsided damagement. Steve Orr -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: address parse
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 7:33 AM To: Multiple recipients of list ORACLE-L To throw a totally different twist into the combination: The new IRS ( yer favorite U.S. institution) form for corporations to report taxable income for individuals requires--- 1.If the address of the individual is in the U.S. use the street1,street1,city,county,state,zip fields. 2.If the address of the individual is outside the U.S. place all the information into 1 field. makes for a nightmare to parse the information. ROR mª¿ªm [EMAIL PROTECTED] 02/26/02 04:12PM John, We're actually messing with you a little bit, having fun at your incomplete question. Not only are there many things that could be considered an address, some of them have several components that could be combined in several different ways. Not only that, but there are different ways that you might choose to represent those components. A common street address example would be that given something like: '1293 Incomplete Drive, Suite 2001, Mail Stop H, Specification City, Oklahoma, 74953-0011' And a common set of fields to parse it into would be: AddressLine1 AddressLine2 City State Zip When stating a parsing problem both the input form and the output form need to specified. Also any peculiar rules. Above you'd need to state things like: -Assume USA address -Comma separated fields -City state and zip are last three fields -First field always AddressLine1 -If 4 fields AddressLine2 left null -If 5 fields then field 2 is AddressLine2 -If 6 or more fields, then fields 2 - (n-3) are concatenated separated by commas in AddressLine2 -State will be stored as 2 character state code -Zip can be either 5 digit or 9 digit (no dash) codes Now given all that, a parse routine could be written. But lacking such a specification, the question is very open for various interpretation, any of which has only a remote chance of meeting your needs. -rje S street address S -Original Message- S Sent: Tuesday, February 26, 2002 10:55 AM S To: Multiple recipients of list ORACLE-L S Anybody already have an address string parser (plsql) already written S that S they would care to share? S Address? IP? Internet mail? USPS? Memory address? URL? -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
The boss is NOT always right but he is always the BOSS. Murphy said: If they want it bad (in 24 hours) they will get it bad. Let the guy have whatever he wants, just be sure to document his requests. Don't you want to get advanced after they fire HIM :-) Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Don [SMTP:[EMAIL PROTECTED]] Sent: Wed, February 27, 2002 8:48 AM To: Multiple recipients of list ORACLE-L Subject: Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle learning network v Oracle CBT-Select
What is OPP? -Original Message- From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 27, 2002 9:13 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle learning network v Oracle CBT-Select OLN is free for OPP members however, you need outside access to Real Player. Because of the many bugs in RealPlayer's Browser, we have closed off RealPlayer and hence OLN to our office. OLN is a nice feature and does offer many courses. However, you are also depending on Oracle's web site to be available at any time. Several times OLN was not available. Good luck. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L Subject: Oracle learning network v Oracle CBT-Select We are considering whether or not to invest in Oracles CBT-select computer based training CDs or instead use the Oracle Learning Network(which appears to be free to OPP members) Anyone got any experience of using either or both? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UPGRADATION
I wanted to do this at my last job. I called WWS and was told it was their best recommendation to put it in a new home. They would not support installing into the old home and if you have problems, you are on your own. Terry -Original Message- Sent: Tuesday, February 26, 2002 3:03 PM To: Multiple recipients of list ORACLE-L Hi I am running oracle 8i standared edition.I want to upgrade to oracle 8i enterprise edition.IS this possible on same oracle home? If not then can I install oracle 8i enterprise edition into another oracle home? Thx _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
Buy him the 'Oracle8i For Dummies' as your departing gift, and suggest that he does this himself... Heck, if he can design it, let him have the privilege of building it as well :) Good Luck... - Kirti -Original Message- Sent: Wednesday, February 27, 2002 1:48 AM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Bug, tuning issue or bad sql?
Oracle 8.1.6.0 NT 4.0 I'm having a problem with a query which is basically just a bunch of UNION ALL's that I want sorted in a certain way. The query runs flawlessly when I limit the result set with a where clause, but when I remove it, the query crashes when sorting (in the order by at the far bottom). If I remove the ORDER BY, the query runs beautifully. The max rows returned by this is about 12,000. I am confident that the values returned are consistant across each of the UNION's. Here's the error returned: trans_demographic td * ERROR at line 257: ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [], [] In other words: 1. Query runs with no ORDER BY. 2. Query runs with ORDER BY, when limiting rows returned. 3. When limiting rows returned, I can use any value in the full range (in this case poe_assoc_id), and everything works as long as the total rows returned are less than around 3,000. I have tested every range of data, and I can use any set of valid values as long as I do it in pieces. So my question is: is the sql bad somehow (I don't think so). Is this a tuning issue on the database itself? Is this a bug in 8.1.6.0? Thanks in advance! Ed P.S. Here's the big ole query: select ae_email_addr, ae_assoc_id, poe_assoc_id, table_type, poe, adds, changes, drops, ae_name from (select a.assoc_id poe_assoc_id, a.poe poe, 'Member' table_type, SUM(DECODE(tm.record_change_type,'A',1,0)) adds, SUM(DECODE(tm.record_change_type,'C',1,0)) + SUM(DECODE(tm.record_change_type,'T',1,0)) changes, SUM(DECODE(tm.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id from(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr from ASSOCIATION a, ASSOCIATION_MEMBER poe_am, MEMBER poe_m, ASSOCIATION_MEMBER ae_am, MEMBER ae_m where a.assoc_type = 'L' anda.assoc_status_cd = 'A' anda.assoc_id = poe_am.assoc_id andpoe_am.relation_type = 'POE' andpoe_am.mem_id = poe_m.mem_id andpoe_m.primary_assoc_id = ae_am.assoc_id andae_am.relation_type = 'EO_ID' andae_am.mem_id = ae_m.mem_id) a, TRANS_MEMBER tm wherea.assoc_id = tm.primary_assoc_id(+) and a.poe = tm.sender_id(+) and tm.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001' GROUP BY a.assoc_id, a.poe, a.ae_email_addr, a.ae_name, a.ae_assoc_id UNION ALL SELECT a.assoc_id poe_assoc_id, a.poe poe, 'Member Supplemental' table_type, SUM(DECODE(tms.record_change_type,'A',1,0)) adds, SUM(DECODE(tms.record_change_type,'C',1,0)) changes, SUM(DECODE(tms.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id FROM(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr from ASSOCIATION a, ASSOCIATION_MEMBER poe_am, MEMBER poe_m, ASSOCIATION_MEMBER ae_am, MEMBER ae_m where a.assoc_type = 'L' anda.assoc_status_cd = 'A' anda.assoc_id = poe_am.assoc_id andpoe_am.relation_type = 'POE' andpoe_am.mem_id = poe_m.mem_id andpoe_m.primary_assoc_id = ae_am.assoc_id andae_am.relation_type = 'EO_ID' andae_am.mem_id = ae_m.mem_id) a, trans_member_supplemental tms WHEREa.assoc_id = tms.assoc_id(+) AND a.poe = tms.sender_id(+) and tms.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001' GROUP BY a.assoc_id, a.poe, a.ae_email_addr, a.ae_name, a.ae_assoc_id UNION ALL SELECT a.assoc_id poe_assoc_id, a.poe poe, 'Office' table_type, SUM(DECODE(tof.record_change_type,'A',1,0)) adds, SUM(DECODE(tof.record_change_type,'C',1,0)) + SUM(DECODE(tof.record_change_type,'T',1,0)) changes, SUM(DECODE(tof.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id FROM(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL
RE: Manager decrees his data warehouse design. Help!
Hmmm ... in an old project we had a manager with similar ideas... his ideas of design were, what should I say? 'revolutionary'? We finally named his design technique as 'Rainfall Design' because the ideas would come down like a heavy rainfall and then drain away immediately when logic was applied. To quote Celine Dion's song ... It's all coming back to me now .. Manager: One who knows more buzzwords than you Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Upgrade directly to 8.1.7.3?
Solaris 2.6 Oracle 8.0.5 -- Oracle 8.1 -- Oracle 8.1.7.3 I have a Solaris box with a test and a production database. I have 2 code trees: 8.0.5, and 8.1.7 patched up to level 8.1.7.2. With Oracle's blessing (really, I opened a tar), I upgraded the test database directly from 8.0.5 to the 8.1.7.2 patch level. Did not pass go. Did not collect $200. OK, here's a surprise. I still have not had an opportunity to upgrade production, which is still at 8.0.5, and now I'd like to upgrade it to patch level 8.1.7.3. However, the 8.1.7.3 patch documentation states that When migrating a database from an earlier release, you must complete the database migration to the 8.1.7 release prior to applying this patch set. If I believe this note, then I believe I must install a new code tree with a vanilla 8.17, then upgrade production from 8.0.5 to 8.1.7, then immediately upgrade again to 8.1.7.3My problem: I don't have an extra gig of space to devote to another code tree. (And obviously I want to thoroughly test the test database with exactly the same version I'll be running in production, i.e., 8.1.7.3.) I don't see a good reason not to go immediately to 8.1.7.3, especially since I was able to go directly to 8.1.7.2 with the test database, which worked nicely. However, this is a critical database, and I'd just as soon not screw it up. Any words of widsom? Thanks for any help. Barb q (Jesse: I'm currently taking classes for the q-impaired. I'm feeling much better now.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
I call it a data repository. I call it a data suppository... Because nobody wants it after you've stuffed in a dark place. :-) -Original Message- Sent: Wednesday, February 27, 2002 9:08 AM To: Multiple recipients of list ORACLE-L We Have the same issue here. Large Tables , loaded nightly But nobody uses them. Management calls it a Datawarehouse, I call it a data repository. Can a cusotmer find out what they need. NOPE Document your findings, to C.Y.A. Good Luck --- Orr, Steve [EMAIL PROTECTED] wrote: When I was consulting at a particular client, I saw the effects of this approach after the fact. Massive amounts of data were loaded into tables that were never accessed except to load data because the users couldn't do anything with it. Later it was thought that to fix this all they had to do was send the users to training on the reporting tool. After they spent lots of money on training nothing changed because the users still didn't understand the data and couldn't do anything with it. Meanwhile, damangement checked off it's accomplishment of an objective on the HR management forms. I suspect that may be what you're dealing with. After all, isn't it more important to report that you did something that to actually do something worthwhile? ;-) The notion that DBA's aren't needed and all you have to do is load data into a relational database and give the end users a point and click GUI tool is foolish but not uncommon with shortsided damagement. Steve Orr -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
RE: Oracle learning network v Oracle CBT-Select
In Canada we call it Ontario Provincial Police. SELECT * FROM users WHERE clue 0 0 rows returned What is OPP? -Original Message- From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 27, 2002 9:13 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle learning network v Oracle CBT-Select OLN is free for OPP members however, you need outside access to Real Player. Because of the many bugs in RealPlayer's Browser, we have closed off RealPlayer and hence OLN to our office. OLN is a nice feature and does offer many courses. However, you are also depending on Oracle's web site to be available at any time. Several times OLN was not available. Good luck. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L Subject: Oracle learning network v Oracle CBT-Select We are considering whether or not to invest in Oracles CBT-select computer based training CDs or instead use the Oracle Learning Network(which appears to be free to OPP members) Anyone got any experience of using either or both? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hayes, Scott INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
I once had a manager who was a paranoid schizophrenic. Very exciting, let me tell you. But, one thing he told me in a rather roundabout paranoid way is that the way you deal with crazy bosses who were out to get you is to make friends with other people at your boss' level in the organization, hopefully who report to the same person your boss does, and let them know in a laughing kind of way what your boss wants you to do. Never be confrontational or speak ill of your boss, because, of course, that will wind up biting you in the ass, too. But, that way, when your boss starts badmouthing you and blaming you for everything that goes wrong that was his fault, you'll be insulated from having anything bad happen to you as your friends will close ranks around you and stop your boss from making your life a living hell. And then he threw his coffee cup across the room, turned bright red and started shrieking about how the VP of RD had always hated him. God, I loved that job. Many, *MANY* wonderful stories came out of that place. Anyway, I never thought to follow this advice, let alone share it, but, it sounds like, in this case, you have a crazy boss, and if you stick around, you're going to need a little safety. Of course, posting your resume isn't a bad approach either. HTH, Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Thanks to everyone for their comments on this. You've convinced me. I'll go away and worry about something else instead now! - Bill. At 04:43 27/02/02 -0800, you wrote: I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Manager decrees his data warehouse design. Help!
I've had a similar, although not identical situation some couple of years ago that never fully corrected itself. The CIO wanted a data warehouse employed a math specialist with some database experience to handle it. My biggest problem was this toad went and got an OCP certificate just from reading the books thought he was the king of the hill. Anyway, he came up with this totally denormalized configuration, although indexed, that was totally dumb. Many of the management types were for his design as the web apps he had also created were 'pretty'. OK, so I asked him to estimate how much disk space he would need. His was about 30GB of disk. I took his demo app calculated average row length, and all of the other stuff + indexes and came up with more like half a terrabyte. When the needed were presented to the CIO there were a lot of questions, recalculating, etc... presented all of which supported my call for an extra EMC cabinet full of disks (about $2.5M at the time). The data structures then made some very dramatic changes, initial implementation was done on 30GB of drives but out grew that in 2 months. We then acquired an additional 30GB of disk, blew through that in 2 months. At that point the individual submitted his resignation we acquired an additional 100GB of disk limited the data retention to 6 months. Completely redesigning the warehouse is a Q4 project this year. So hang in there, reality is one beast that does bite the hand that feeds it. Dick Goulet Reply Separator Author: Rachel Carmichael [EMAIL PROTECTED] Date: 2/27/02 7:48 AM You have this decree in writing? Okay, once you get that. Do what he wants, making sure everyone knows that this great new database design and application are all his idea (do this with a smile, with enthusiasm if you can manage it) hang on and wait for it all to fall apart. Otherwise, do you have any friends or connections in the user base? or to his manager? Write up, without emotion, what you see as the problems to this approach. Be very logical, with explicit reasons (not this is crap) pass it around. and be prepared to make an enemy --- Don [EMAIL PROTECTED] wrote: I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
RE: Bug, tuning issue or bad sql?
Wow. Are you using CURSOR_SHARING=FORCE in your init.ora by any chance? We've had sporadic problems with that while CBO is on. Also, what does the explain plan look like? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, February 27, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.0 NT 4.0 I'm having a problem with a query which is basically just a bunch of UNION ALL's that I want sorted in a certain way. The query runs flawlessly when I limit the result set with a where clause, but when I remove it, the query crashes when sorting (in the order by at the far bottom). If I remove the ORDER BY, the query runs beautifully. The max rows returned by this is about 12,000. I am confident that the values returned are consistant across each of the UNION's. Here's the error returned: trans_demographic td * ERROR at line 257: ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [], [] In other words: 1. Query runs with no ORDER BY. 2. Query runs with ORDER BY, when limiting rows returned. 3. When limiting rows returned, I can use any value in the full range (in this case poe_assoc_id), and everything works as long as the total rows returned are less than around 3,000. I have tested every range of data, and I can use any set of valid values as long as I do it in pieces. So my question is: is the sql bad somehow (I don't think so). Is this a tuning issue on the database itself? Is this a bug in 8.1.6.0? Thanks in advance! Ed P.S. Here's the big ole query: [truncated for brevity] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle learning network v Oracle CBT-Select
[Erik Williams] What is OPP? http://otn.oracle.com/partners/oraclepartnerprogram.html -- James Manning [EMAIL PROTECTED] GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E A0BF B026 EEBB F6E4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Manning INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN with TSM on AIX
Hi Gurus,Any body got any experience usingTSM 4.2 onAIX 4.3.2 with RMAN?Any hints/suggestions will be most welcome.
Re: Bug, tuning issue or bad sql?
Bug. Ed wrote: Oracle 8.1.6.0 NT 4.0 I'm having a problem with a query which is basically just a bunch of UNION ALL's that I want sorted in a certain way. The query runs flawlessly when I limit the result set with a where clause, but when I remove it, the query crashes when sorting (in the order by at the far bottom). If I remove the ORDER BY, the query runs beautifully. The max rows returned by this is about 12,000. I am confident that the values returned are consistant across each of the UNION's. Here's the error returned: trans_demographic td * ERROR at line 257: ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [], [] In other words: 1. Query runs with no ORDER BY. 2. Query runs with ORDER BY, when limiting rows returned. 3. When limiting rows returned, I can use any value in the full range (in this case poe_assoc_id), and everything works as long as the total rows returned are less than around 3,000. I have tested every range of data, and I can use any set of valid values as long as I do it in pieces. So my question is: is the sql bad somehow (I don't think so). Is this a tuning issue on the database itself? Is this a bug in 8.1.6.0? Thanks in advance! Ed P.S. Here's the big ole query: select ae_email_addr, ae_assoc_id, poe_assoc_id, table_type, poe, adds, changes, drops, ae_name from (select a.assoc_id poe_assoc_id, a.poe poe, 'Member' table_type, SUM(DECODE(tm.record_change_type,'A',1,0)) adds, SUM(DECODE(tm.record_change_type,'C',1,0)) + SUM(DECODE(tm.record_change_type,'T',1,0)) changes, SUM(DECODE(tm.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id from(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr from ASSOCIATION a, ASSOCIATION_MEMBER poe_am, MEMBER poe_m, ASSOCIATION_MEMBER ae_am, MEMBER ae_m where a.assoc_type = 'L' anda.assoc_status_cd = 'A' anda.assoc_id = poe_am.assoc_id andpoe_am.relation_type = 'POE' andpoe_am.mem_id = poe_m.mem_id andpoe_m.primary_assoc_id = ae_am.assoc_id andae_am.relation_type = 'EO_ID' andae_am.mem_id = ae_m.mem_id) a, TRANS_MEMBER tm wherea.assoc_id = tm.primary_assoc_id(+) and a.poe = tm.sender_id(+) and tm.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001' GROUP BY a.assoc_id, a.poe, a.ae_email_addr, a.ae_name, a.ae_assoc_id UNION ALL SELECT a.assoc_id poe_assoc_id, a.poe poe, 'Member Supplemental' table_type, SUM(DECODE(tms.record_change_type,'A',1,0)) adds, SUM(DECODE(tms.record_change_type,'C',1,0)) changes, SUM(DECODE(tms.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id FROM(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr from ASSOCIATION a, ASSOCIATION_MEMBER poe_am, MEMBER poe_m, ASSOCIATION_MEMBER ae_am, MEMBER ae_m where a.assoc_type = 'L' anda.assoc_status_cd = 'A' anda.assoc_id = poe_am.assoc_id andpoe_am.relation_type = 'POE' andpoe_am.mem_id = poe_m.mem_id andpoe_m.primary_assoc_id = ae_am.assoc_id andae_am.relation_type = 'EO_ID' andae_am.mem_id = ae_m.mem_id) a, trans_member_supplemental tms WHEREa.assoc_id = tms.assoc_id(+) AND a.poe = tms.sender_id(+) and tms.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001' GROUP BY a.assoc_id, a.poe, a.ae_email_addr, a.ae_name, a.ae_assoc_id UNION ALL SELECT a.assoc_id poe_assoc_id, a.poe poe, 'Office' table_type, SUM(DECODE(tof.record_change_type,'A',1,0)) adds, SUM(DECODE(tof.record_change_type,'C',1,0)) + SUM(DECODE(tof.record_change_type,'T',1,0)) changes, SUM(DECODE(tof.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id FROM(select a.assoc_id, poe_am.mem_id poe,
Re: Bug, tuning issue or bad sql?
Ed - Sounds like you hit bug 1331849. Check Metalink for more info. Dennis Ed mrclark@xnetTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Bug, tuning issue or bad sql? root@fatcity. com 02/27/02 09:23 AM Please respond to ORACLE-L Oracle 8.1.6.0 NT 4.0 I'm having a problem with a query which is basically just a bunch of UNION ALL's that I want sorted in a certain way. The query runs flawlessly when I limit the result set with a where clause, but when I remove it, the query crashes when sorting (in the order by at the far bottom). If I remove the ORDER BY, the query runs beautifully. The max rows returned by this is about 12,000. I am confident that the values returned are consistant across each of the UNION's. Here's the error returned: trans_demographic td * ERROR at line 257: ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [], [] In other words: 1. Query runs with no ORDER BY. 2. Query runs with ORDER BY, when limiting rows returned. 3. When limiting rows returned, I can use any value in the full range (in this case poe_assoc_id), and everything works as long as the total rows returned are less than around 3,000. I have tested every range of data, and I can use any set of valid values as long as I do it in pieces. So my question is: is the sql bad somehow (I don't think so). Is this a tuning issue on the database itself? Is this a bug in 8.1.6.0? Thanks in advance! Ed P.S. Here's the big ole query: select ae_email_addr, ae_assoc_id, poe_assoc_id, table_type, poe, adds, changes, drops, ae_name from (select a.assoc_id poe_assoc_id, a.poe poe, 'Member' table_type, SUM(DECODE(tm.record_change_type,'A',1,0)) adds, SUM(DECODE(tm.record_change_type,'C',1,0)) + SUM(DECODE(tm.record_change_type,'T',1,0)) changes, SUM(DECODE(tm.record_change_type,'D',1,0)) drops, a.ae_email_addr ae_email_addr, a.ae_name ae_name, a.ae_assoc_id ae_assoc_id from(select a.assoc_id, poe_am.mem_id poe, ae_m.first_name||' '||ae_m.last_name ae_name, ae_am.assoc_id ae_assoc_id, NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr from ASSOCIATION a, ASSOCIATION_MEMBER poe_am, MEMBER poe_m, ASSOCIATION_MEMBER ae_am, MEMBER ae_m where a.assoc_type = 'L' anda.assoc_status_cd = 'A' anda.assoc_id = poe_am.assoc_id andpoe_am.relation_type = 'POE' andpoe_am.mem_id = poe_m.mem_id andpoe_m.primary_assoc_id = ae_am.assoc_id andae_am.relation_type = 'EO_ID' andae_am.mem_id = ae_m.mem_id) a, TRANS_MEMBER tm wherea.assoc_id = tm.primary_assoc_id(+) and a.poe = tm.sender_id(+) and tm.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001' GROUP BY a.assoc_id, a.poe, a.ae_email_addr, a.ae_name, a.ae_assoc_id UNION ALL SELECT a.assoc_id poe_assoc_id, a.poe poe, 'Member Supplemental' table_type, SUM(DECODE(tms.record_change_type,'A',1,0)) adds, SUM(DECODE(tms.record_change_type,'C',1,0)) changes,
RE: Manager decrees his data warehouse design. Help!
Oracle Discoverer? Users could poke around with that, without knowing SQL. They won't be very quick about it though. I don't know the context, why did management come up with this scenario, is there a history behind all this? Sounds a bit strange to try to impose an impossible situation that just won't work. Decrees don't make reality. Even when the tools work and the data is there, sometimes users don't use systems because the informatics setup does not dovetail nicely with the way they go about their daily tasks. If it's not natural to them, or it complicates their lives, there will be resistance. From the description though it seems there is more than that to it here. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Wednesday, February 27, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Subject:RE: Manager decrees his data warehouse design. Help! When I was consulting at a particular client, I saw the effects of this approach after the fact. Massive amounts of data were loaded into tables that were never accessed except to load data because the users couldn't do anything with it. Later it was thought that to fix this all they had to do was send the users to training on the reporting tool. After they spent lots of money on training nothing changed because the users still didn't understand the data and couldn't do anything with it. Meanwhile, damangement checked off it's accomplishment of an objective on the HR management forms. I suspect that may be what you're dealing with. After all, isn't it more important to report that you did something that to actually do something worthwhile? ;-) The notion that DBA's aren't needed and all you have to do is load data into a relational database and give the end users a point and click GUI tool is foolish but not uncommon with shortsided damagement. Steve Orr -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Manager decrees his data warehouse design. Help!
Try Win98Lite, I doubt you can make it fit under 64K though. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Wednesday, February 27, 2002 10:53 AM To: Multiple recipients of list ORACLE-L Subject:Re: Manager decrees his data warehouse design. Help! Point #6 - I did not know Win98 can run under 64k RAM. Tell your Boss he needs at least 16MB RAM for Win98 : 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
Hi Bambi, I once had a manager who was a paranoid schizophrenic. BEEN THERE! A former boss from hell got very angry (there's a reason they call it mad) because he tried to fire me and found out he couldn't. Even so, he liked having me around because he needed me. I hung in there because the company was paying for my masters degree at the time. Later, after I got the degree, I left for greener pastures within the company. In the exit interview my former boss accused me of abandonning him, congratulated me on my move, and literally wept at my departure. He confessed he had been trying to get my goat because he was a 20 year military man who managed by intimidation. But he respected me because I was never intimidated, always stood my ground, behaved as a gentleman, and served him faithfully (his words). Two years later I saw my former boss from hell on the 6 o'clock news being put into a police squad car and with yellow crime scene ribbons around his house. Turns out that while he was being laid off he said things which were interpreted as threats on the lives of certain managers. This was taken seriously and the police confiscated all his guns. I guess the lesson is that eventually the truth will come out. The decision remains with us as to whether to put up with the insanity or move on. Do you have a high insanity tolerance level or are you just a masochist? Steve Orr -Original Message- Sent: Wednesday, February 27, 2002 9:23 AM To: Multiple recipients of list ORACLE-L I once had a manager who was a paranoid schizophrenic. Very exciting, let me tell you. But, one thing he told me in a rather roundabout paranoid way is that the way you deal with crazy bosses who were out to get you is to make friends with other people at your boss' level in the organization, hopefully who report to the same person your boss does, and let them know in a laughing kind of way what your boss wants you to do. Never be confrontational or speak ill of your boss, because, of course, that will wind up biting you in the ass, too. But, that way, when your boss starts badmouthing you and blaming you for everything that goes wrong that was his fault, you'll be insulated from having anything bad happen to you as your friends will close ranks around you and stop your boss from making your life a living hell. And then he threw his coffee cup across the room, turned bright red and started shrieking about how the VP of RD had always hated him. God, I loved that job. Many, *MANY* wonderful stories came out of that place. Anyway, I never thought to follow this advice, let alone share it, but, it sounds like, in this case, you have a crazy boss, and if you stick around, you're going to need a little safety. Of course, posting your resume isn't a bad approach either. HTH, Bambi. -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OS authenticated from Outside Domain/Firewall
We have a jsmith account that is OS authenticated in our TEST database. This account also exists in server X which is outside our domain and protected by a firewall. We've set up our firewall to allow Oracle connections to our TEST database to get through. This setup works fine. One day jsmith logs into server X, tries to connect to the TEST database using ODBC and the database let him in without asking for a password! We also did this on a local machine and it came through as well. Obviously, the database wasn't validating the DOMAIN NAME when it evaluated the user and since the username had an OPS$ account it let it through. Is there some setting that I needed to configure to force Oracle to validate both the Domain and User names? Thanks, George -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Hofilena INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import placing data into wrong tablespace
I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OS authenticated from Outside Domain/Firewall: PLS. IGNORE
Please ignore this for now. I think I've found something. George -Original Message- Sent: Wednesday, February 27, 2002 10:15 AM To: LazyDBA.com Discussion We have a jsmith account that is OS authenticated in our TEST database. This account also exists in server X which is outside our domain and protected by a firewall. We've set up our firewall to allow Oracle connections to our TEST database to get through. This setup works fine. One day jsmith logs into server X, tries to connect to the TEST database using ODBC and the database let him in without asking for a password! We also did this on a local machine and it came through as well. Obviously, the database wasn't validating the DOMAIN NAME when it evaluated the user and since the username had an OPS$ account it let it through. Is there some setting that I needed to configure to force Oracle to validate both the Domain and User names? Thanks, George Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Hofilena INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
Both. I was a consultant to this pharmaceutical company at the time and I honestly liked the job that I was doing, so I stuck it out. After I got over the initial shock of having an insane boss, I found the whole thing amusing. Seems he didn't trust the data center with cables for some weird reason, and after the company relieved him of his post (OH so gently), his garage had something like $30K worth of cables in it. They didn't press charges. But, MAN, there were some stories. -Original Message- Sent: Wednesday, February 27, 2002 12:14 PM To: Multiple recipients of list ORACLE-L Hi Bambi, I once had a manager who was a paranoid schizophrenic. BEEN THERE! A former boss from hell got very angry (there's a reason they call it mad) because he tried to fire me and found out he couldn't. Even so, he liked having me around because he needed me. I hung in there because the company was paying for my masters degree at the time. Later, after I got the degree, I left for greener pastures within the company. In the exit interview my former boss accused me of abandonning him, congratulated me on my move, and literally wept at my departure. He confessed he had been trying to get my goat because he was a 20 year military man who managed by intimidation. But he respected me because I was never intimidated, always stood my ground, behaved as a gentleman, and served him faithfully (his words). Two years later I saw my former boss from hell on the 6 o'clock news being put into a police squad car and with yellow crime scene ribbons around his house. Turns out that while he was being laid off he said things which were interpreted as threats on the lives of certain managers. This was taken seriously and the police confiscated all his guns. I guess the lesson is that eventually the truth will come out. The decision remains with us as to whether to put up with the insanity or move on. Do you have a high insanity tolerance level or are you just a masochist? Steve Orr -Original Message- Sent: Wednesday, February 27, 2002 9:23 AM To: Multiple recipients of list ORACLE-L I once had a manager who was a paranoid schizophrenic. Very exciting, let me tell you. But, one thing he told me in a rather roundabout paranoid way is that the way you deal with crazy bosses who were out to get you is to make friends with other people at your boss' level in the organization, hopefully who report to the same person your boss does, and let them know in a laughing kind of way what your boss wants you to do. Never be confrontational or speak ill of your boss, because, of course, that will wind up biting you in the ass, too. But, that way, when your boss starts badmouthing you and blaming you for everything that goes wrong that was his fault, you'll be insulated from having anything bad happen to you as your friends will close ranks around you and stop your boss from making your life a living hell. And then he threw his coffee cup across the room, turned bright red and started shrieking about how the VP of RD had always hated him. God, I loved that job. Many, *MANY* wonderful stories came out of that place. Anyway, I never thought to follow this advice, let alone share it, but, it sounds like, in this case, you have a crazy boss, and if you stick around, you're going to need a little safety. Of course, posting your resume isn't a bad approach either. HTH, Bambi. -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED]
RE: moving from unix to NT
Perl is *much* more flexible than UTL_FILE for flat file operations. There is simply no basis for comparison. The question in your case is this: Can you easily replace the PL/SQL procedures that are using UTL_FILE with a process that runs outside of the database? If so, myself and others on this list can point you in the right direction, as basics in Perl/Oracle/DBI are really not too hard. If your PL/SQL is part of a larger application and not easily removed, you may just have to deal with modifying the PL/SQL. Of course, if you had made this stuff data driven ( meta data, if you will ), this would be a non-issue. :) Jared John Dunn [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 01:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: moving from unix to NT Thanks to everyone for their input into this. The only real issue seems to be UTL_FILE on network drives. 1. I was interested in the many references to Perl as an alternative to using UTL_FILE. Could any of you provide more detail. I know nothing about Perl so would be interested in how to replace the use of UTL_FILE in PL/SQL with Perl. We use UTL_FILE quite a lot for reading and writing flat files. 2. With regard to external procedures, On Unix we currently use this to call a C routine that calls the system command to run Unix commands and scripts(Korn Shell). I presume we will need to amend these commands to their NT equivalents(or can I call Windows API directly from PL/SQL? on NT) and re-write the scripts...presumably in Perl? It will probbably be Oracle 9i on NT. John -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: 26 February 2002 21:37 To:Multiple recipients of list ORACLE-L Subject: Re: moving from unix to NT I haven't tried to do this with Oracle, I just knew that you could. My use has been to change the account that is used for some of my monitors that need to see network drives. I've never had a need to make Oracle run as other than System. As for UTL_FILE, I avoid it like the plague. Perl is much cleaner and easier to use. Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 10:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, I was having all kinds of problems, when I was playing with this option, trying to make oracle service on nt to run under other then SYSTEM account. And yes, I granted this account any possible NT privilege (like ability to run/logon as a service), still didn't work. Jared, Could you share some details on this issue, if you still remember how you managed to make this working? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 1:14 PM Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. You can change that if you're so inclined. ( I can't believe I'm defending Windoze. shudder ) Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, you shouldn't:) Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 25, 2002 9:38 PM On UTL_FILE, I've never had much luck using network mounted files. Igor Neyman ineyman To: Multiple recipients of list ORACLE-L @perceptron.c[EMAIL PROTECTED] om cc: Sent by: rootSubject: Re: moving from unix to NT 02/25/2002 12:18 PM Please respond to ORACLE-L For external procedures just follow the rules for creating DLLs on NT, works fine. DBMS_JOB works fine. For UTL_FILE make sure, you are following NT conventions, when specifying file path(use back slash '\', not front slash '/'). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday,
RE: Manager decrees his data warehouse design. Help!
April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 03:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Manager decrees his data warehouse design. Help! I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 À : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE
RE: Oracle learning network v Oracle CBT-Select
OPP stands for Oracle Partner Program. It is for ISV, System Integrators, Software Vendors, etc. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, February 27, 2002 10:59 AM To: Multiple recipients of list ORACLE-L Subject:RE: Oracle learning network v Oracle CBT-Select What is OPP? -Original Message- From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 27, 2002 9:13 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle learning network v Oracle CBT-Select OLN is free for OPP members however, you need outside access to Real Player. Because of the many bugs in RealPlayer's Browser, we have closed off RealPlayer and hence OLN to our office. OLN is a nice feature and does offer many courses. However, you are also depending on Oracle's web site to be available at any time. Several times OLN was not available. Good luck. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L Subject: Oracle learning network v Oracle CBT-Select We are considering whether or not to invest in Oracles CBT-select computer based training CDs or instead use the Oracle Learning Network(which appears to be free to OPP members) Anyone got any experience of using either or both? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: Manager decrees his data warehouse design. Help!
I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 03:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Manager decrees his data warehouse design. Help! I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 A : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
RE: Import placing data into wrong tablespace
ENVTST with unlimited tablespace, quota on DATA ??? - Kirti -Original Message- Sent: Wednesday, February 27, 2002 12:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. Plse, correct me if I'm wrong. Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 12:18 PM Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and
Re:RE: Manager decrees his data warehouse design. Help!
I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:RE: Manager decrees his data warehouse design. Help! I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 03:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Manager decrees his data warehouse design. Help! I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 A : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this situation? For tomorrow, I've done a CTAS from a materialized view that we created to support one departments known requirements. Don -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet
RE: moving from unix to NT
Jared, please point me in the right direction. I have used Perl for scripting odd procedures on Unix and NT. I would like to use it in Oracle also. Thanks, Dave -Original Message- Sent: Wednesday, February 27, 2002 12:35 PM To: Multiple recipients of list ORACLE-L Perl is *much* more flexible than UTL_FILE for flat file operations. There is simply no basis for comparison. The question in your case is this: Can you easily replace the PL/SQL procedures that are using UTL_FILE with a process that runs outside of the database? If so, myself and others on this list can point you in the right direction, as basics in Perl/Oracle/DBI are really not too hard. If your PL/SQL is part of a larger application and not easily removed, you may just have to deal with modifying the PL/SQL. Of course, if you had made this stuff data driven ( meta data, if you will ), this would be a non-issue. :) Jared John Dunn [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 01:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: moving from unix to NT Thanks to everyone for their input into this. The only real issue seems to be UTL_FILE on network drives. 1. I was interested in the many references to Perl as an alternative to using UTL_FILE. Could any of you provide more detail. I know nothing about Perl so would be interested in how to replace the use of UTL_FILE in PL/SQL with Perl. We use UTL_FILE quite a lot for reading and writing flat files. 2. With regard to external procedures, On Unix we currently use this to call a C routine that calls the system command to run Unix commands and scripts(Korn Shell). I presume we will need to amend these commands to their NT equivalents(or can I call Windows API directly from PL/SQL? on NT) and re-write the scripts...presumably in Perl? It will probbably be Oracle 9i on NT. John -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: 26 February 2002 21:37 To:Multiple recipients of list ORACLE-L Subject: Re: moving from unix to NT I haven't tried to do this with Oracle, I just knew that you could. My use has been to change the account that is used for some of my monitors that need to see network drives. I've never had a need to make Oracle run as other than System. As for UTL_FILE, I avoid it like the plague. Perl is much cleaner and easier to use. Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 10:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, I was having all kinds of problems, when I was playing with this option, trying to make oracle service on nt to run under other then SYSTEM account. And yes, I granted this account any possible NT privilege (like ability to run/logon as a service), still didn't work. Jared, Could you share some details on this issue, if you still remember how you managed to make this working? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 1:14 PM Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. You can change that if you're so inclined. ( I can't believe I'm defending Windoze. shudder ) Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/02 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: moving from unix to NT Well, you shouldn't:) Because Oracle on NT runs (hence executes UTL_FILE) under SYSTEM account, which does not have privileges to access network drives. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 25, 2002 9:38 PM On UTL_FILE, I've never had much luck using network mounted files. Igor Neyman ineyman To: Multiple recipients of list ORACLE-L @perceptron.c[EMAIL PROTECTED] om cc: Sent by: rootSubject: Re: moving from unix to NT 02/25/2002 12:18 PM Please respond to ORACLE-L For external procedures just follow the rules for creating DLLs on NT, works fine. DBMS_JOB works fine. For UTL_FILE make
Re: Users reading from rollback segments
The delayed_logging_block_cleanouts parameter does not force or suppress cleanouts. It just makes any cleanouts that do occur get logged as redo entries. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Wed, 27 Feb 2002, Diego Cutrone wrote: Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. - Original Message - Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people
RE: RE: Manager decrees his data warehouse design. Help!
How much do you charge an hour? They want to build a table with 980 columns, because the queries fly if you index it heavily. It won't load... the indexes won't build from load to load if you drop them... but the QUERIES... they JUST F*L*Y! -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:RE: Manager decrees his data warehouse design. Help! I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 03:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Manager decrees his data warehouse design. Help! I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 A : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k RAM.) 7 - We are not to just copy the legacy transactions. 8 - We are to load into an Oracle table, all legacy transction data because we don't want to limit how or what a user will look at 9 - It is not necessary to talk with the users to see what data they want to look at, or the atomic level. They are smart enough to fighure this out on their own. We just need to provide them the data. 10 - There shall be no long term maintenance required by the dw. Any ideas on how to deal with this
RE: RE: Manager decrees his data warehouse design. Help!
By taking all the joins out I think they mean basically forcing Oracle to store the row data in the same blocks since you changes the rows to columns or some such. I saw a database out there a while back promoted by Joe Celko called KillerDB that does this but the data is still stored in rows. It was used for very large decision making systems. I can't find the site anymore so perhaps just another .com gone bust. - Ethan -Original Message- Sent: Wednesday, February 27, 2002 12:59 PM To: Multiple recipients of list ORACLE-L I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import placing data into wrong tablespace
Bill, The default is to import objects back into the tablespace from which they were exported. Your surest bet is to create empty tables in the appropriate tablespace (by editing the imp/indexfile), then import the date. Mike Hand Polaroid Corp. -Original Message- Sent: Wednesday, February 27, 2002 1:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import placing data into wrong tablespace
ENVTST had the role RESOURCE, which includes UNLIMITED TABLESPACE on all tablespaces. Revoked that and granted unlimited tablespace on ENVTST_DATA and all seems to work. thanks -Original Message- Sent: Wed, February 27, 2002 1:58 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:RE: Import placing data into wrong tablespace ENVTST with unlimited tablespace, quota on DATA ??? - Kirti -Original Message- Sent: Wednesday, February 27, 2002 12:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
Jeremiah : What I meant was that the delayed_logging_block_cleanouts parameter (=FALSE) will make the next reader of the block to cleanout that block. Now, if this parameter's value is TRUE (default in Oracle 7 and 8.0) the next reader will NOT clean out the block (it will read the rollback segment and generate the appropiate block image but it will not clean the block out). The delayed logging block clean out feature delays the redo for the cleanout blocks until it could be logged in combination with another redo for another change to the block. So if you have this parameter set in TRUE the block clean out will be made only when you'll make another change to these blocks. According to what Glenn was saying: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). I think that there's a way you can be sure that noone will need to read some RBS blocks for a cleanout operation. Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 4:38 PM The delayed_logging_block_cleanouts parameter does not force or suppress cleanouts. It just makes any cleanouts that do occur get logged as redo entries. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Wed, 27 Feb 2002, Diego Cutrone wrote: Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. - Original Message - Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading
RE: RE: Manager decrees his data warehouse design. Help!
Also not uncommon when tracking medical data. Bambi. -Original Message- Sent: Wednesday, February 27, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Some of the tables in J.D. Edwards OneWorld have over 200 columns, VARCHAR is not used, only NUMBER and CHAR. Makes for some wide tables. This product was originally some type of flat file database. Ethan -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
www.kx.com -Original Message- Sent: Wednesday, February 27, 2002 2:38 PM To: Multiple recipients of list ORACLE-L By taking all the joins out I think they mean basically forcing Oracle to store the row data in the same blocks since you changes the rows to columns or some such. I saw a database out there a while back promoted by Joe Celko called KillerDB that does this but the data is still stored in rows. It was used for very large decision making systems. I can't find the site anymore so perhaps just another .com gone bust. - Ethan -Original Message- Sent: Wednesday, February 27, 2002 12:59 PM To: Multiple recipients of list ORACLE-L I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrade directly to 8.1.7.3?
Barq, ;) I've been bouncing up and down through the 8.1.7 patches on Tru64, and have just backed out the 8.1.7.3 patch (to 8172(1)) because of bug 2220597, so I have a suggestion to avoid an addtional code tree. Deinstall the 8172 code then reinstall 817, migrate the production database, then apply the patch of your choice. HTH Mike H Polaroid Corp. -Original Message- Solaris 2.6 Oracle 8.0.5 -- Oracle 8.1 -- Oracle 8.1.7.3 I have a Solaris box with a test and a production database. I have 2 code trees: 8.0.5, and 8.1.7 patched up to level 8.1.7.2. With Oracle's blessing (really, I opened a tar), I upgraded the test database directly from 8.0.5 to the 8.1.7.2 patch level. Did not pass go. Did not collect $200. OK, here's a surprise. I still have not had an opportunity to upgrade production, which is still at 8.0.5, and now I'd like to upgrade it to patch level 8.1.7.3. However, the 8.1.7.3 patch documentation states that When migrating a database from an earlier release, you must complete the database migration to the 8.1.7 release prior to applying this patch set. If I believe this note, then I believe I must install a new code tree with a vanilla 8.17, then upgrade production from 8.0.5 to 8.1.7, then immediately upgrade again to 8.1.7.3My problem: I don't have an extra gig of space to devote to another code tree. (And obviously I want to thoroughly test the test database with exactly the same version I'll be running in production, i.e., 8.1.7.3.) I don't see a good reason not to go immediately to 8.1.7.3, especially since I was able to go directly to 8.1.7.2 with the test database, which worked nicely. However, this is a critical database, and I'd just as soon not screw it up. Any words of widsom? Thanks for any help. Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Manager decrees his data warehouse design. Help!
Title: RE: Manager decrees his data warehouse design. Help! wow .. now my place of work seems oh so normal !! kinda boring really ... Nelson Flores Project Manager [EMAIL PROTECTED] - Information Technology Center http://cti.intec.cl Corporación de investigación Tecnológica - Intec http://cti.intec.cl - Avda el condor 844 Ciudad Empresarial Huechuraba Santiago - Chile -Mensaje original- De: Bellows, Bambi [mailto:[EMAIL PROTECTED]] Enviado el: Miércoles, 27 de Febrero de 2002 15:29 Para: Multiple recipients of list ORACLE-L Asunto: RE: Manager decrees his data warehouse design. Help! Both. I was a consultant to this pharmaceutical company at the time and I honestly liked the job that I was doing, so I stuck it out. After I got over the initial shock of having an insane boss, I found the whole thing amusing. Seems he didn't trust the data center with cables for some weird reason, and after the company relieved him of his post (OH so gently), his garage had something like $30K worth of cables in it. They didn't press charges. But, MAN, there were some stories. -Original Message- Sent: Wednesday, February 27, 2002 12:14 PM To: Multiple recipients of list ORACLE-L Hi Bambi, I once had a manager who was a paranoid schizophrenic. BEEN THERE! A former boss from hell got very angry (there's a reason they call it mad) because he tried to fire me and found out he couldn't. Even so, he liked having me around because he needed me. I hung in there because the company was paying for my masters degree at the time. Later, after I got the degree, I left for greener pastures within the company. In the exit interview my former boss accused me of abandonning him, congratulated me on my move, and literally wept at my departure. He confessed he had been trying to get my goat because he was a 20 year military man who managed by intimidation. But he respected me because I was never intimidated, always stood my ground, behaved as a gentleman, and served him faithfully (his words). Two years later I saw my former boss from hell on the 6 o'clock news being put into a police squad car and with yellow crime scene ribbons around his house. Turns out that while he was being laid off he said things which were interpreted as threats on the lives of certain managers. This was taken seriously and the police confiscated all his guns. I guess the lesson is that eventually the truth will come out. The decision remains with us as to whether to put up with the insanity or move on. Do you have a high insanity tolerance level or are you just a masochist? Steve Orr -Original Message- Sent: Wednesday, February 27, 2002 9:23 AM To: Multiple recipients of list ORACLE-L I once had a manager who was a paranoid schizophrenic. Very exciting, let me tell you. But, one thing he told me in a rather roundabout paranoid way is that the way you deal with crazy bosses who were out to get you is to make friends with other people at your boss' level in the organization, hopefully who report to the same person your boss does, and let them know in a laughing kind of way what your boss wants you to do. Never be confrontational or speak ill of your boss, because, of course, that will wind up biting you in the ass, too. But, that way, when your boss starts badmouthing you and blaming you for everything that goes wrong that was his fault, you'll be insulated from having anything bad happen to you as your friends will close ranks around you and stop your boss from making your life a living hell. And then he threw his coffee cup across the room, turned bright red and started shrieking about how the VP of RD had always hated him. God, I loved that job. Many, *MANY* wonderful stories came out of that place. Anyway, I never thought to follow this advice, let alone share it, but, it sounds like, in this case, you have a crazy boss, and if you stick around, you're going to need a little safety. Of course, posting your resume isn't a bad approach either. HTH, Bambi. -Original Message- Sent: Tuesday, February 26, 2002 11:48 PM To: Multiple recipients of list ORACLE-L I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of high power talk. 6 - all users will be trained to use MS Access to get at their data. (These are users that were just converted off from green screen teminals within the last 45-days, to Windows 98 with 64k
RE: Upgrade directly to 8.1.7.3?
Hi Barabara, The wording is a bit clumsy, isn't it? I would think you'd be fine since you're not really applying a patch, you've already got the binaries in place. The 4th digit is just bug fixes, the upgrade scripts (catalog.sql, catproc.sql, catrep.sql, etc. should be the same across all 8.1.7.x installs. Right? Of course I could be completely wrong! - Jerry -Original Message- Sent: Wednesday, February 27, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Solaris 2.6 Oracle 8.0.5 -- Oracle 8.1 -- Oracle 8.1.7.3 I have a Solaris box with a test and a production database. I have 2 code trees: 8.0.5, and 8.1.7 patched up to level 8.1.7.2. With Oracle's blessing (really, I opened a tar), I upgraded the test database directly from 8.0.5 to the 8.1.7.2 patch level. Did not pass go. Did not collect $200. OK, here's a surprise. I still have not had an opportunity to upgrade production, which is still at 8.0.5, and now I'd like to upgrade it to patch level 8.1.7.3. However, the 8.1.7.3 patch documentation states that When migrating a database from an earlier release, you must complete the database migration to the 8.1.7 release prior to applying this patch set. If I believe this note, then I believe I must install a new code tree with a vanilla 8.17, then upgrade production from 8.0.5 to 8.1.7, then immediately upgrade again to 8.1.7.3My problem: I don't have an extra gig of space to devote to another code tree. (And obviously I want to thoroughly test the test database with exactly the same version I'll be running in production, i.e., 8.1.7.3.) I don't see a good reason not to go immediately to 8.1.7.3, especially since I was able to go directly to 8.1.7.2 with the test database, which worked nicely. However, this is a critical database, and I'd just as soon not screw it up. Any words of widsom? Thanks for any help. Barb q (Jesse: I'm currently taking classes for the q-impaired. I'm feeling much better now.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import placing data into wrong tablespace
This may be due to when tables were created originally they were created with tablespace data..Export captures that defination and try to create it in that tablespace while import I have seen this behaviour recently while importing full dump of 8.1.6.3 to 8.1.7.2 and system stuff were creating in TOOLS tbs instead of SYSTEM tablespace for those objects which were originally created using tools tbs although default tablespace for SYSTEM user is SYSTEM... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 27 Feb 2002 10:58:33 -0800 ENVTST with unlimited tablespace, quota on DATA ??? - Kirti -Original Message- Sent: Wednesday, February 27, 2002 12:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import placing data into wrong tablespace
ENVTST doesn't need unlimited tablespace, because SYSTEM has unlimited tablespace --- Deshpande, Kirti [EMAIL PROTECTED] wrote: ENVTST with unlimited tablespace, quota on DATA ??? - Kirti -Original Message- Sent: Wednesday, February 27, 2002 12:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
Star schemas with bitmap indexes are pretty fast. Heck, even a single table with lots of bitmap indexes is pretty fast. That's what I'm doing now as an interims solution for one group until we can come up with a proper DW initiative. But I don't think I would care to rebuild 980 bitmap indexes every time I load a single table. :) As for per hour, I'm not too expensive, but time is a precious commodity for me lately. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 11:43 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Manager decrees his data warehouse design. Help! How much do you charge an hour? They want to build a table with 980 columns, because the queries fly if you index it heavily. It won't load... the indexes won't build from load to load if you drop them... but the QUERIES... they JUST F*L*Y! -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:RE: Manager decrees his data warehouse design. Help! I seem to remember reading somewhere that there can be a maximum of 255 columns in a table. Never created a table with half that many before. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 2/27/2002 10:28 AM April, I sincerely hope you're being facetious with the statement that queries run so much faster if you take all the joins out 1000 columns!? How many rows like that will fit in a block? Your system has to wade through a lot of extraneous data to get a few columns for a query. How do you index it? You can't. It would be most interesting if you share your benchmarks with us. Jared April Wells [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 03:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Manager decrees his data warehouse design. Help! I agree, but at all costs... DOCUMENT EVERYTHING so it proves you made your suggestions and then went by the book on following what he decreed. We are facing similar problems (although not quite to your degree) and we are going to do two proof of concepts... on that denormalizes EVERYTHING into big GIANT tables (very nearly 1000 columns each)... because queries run so much faster if you take all the joins out... and one using a star-flake kind of model because it follows the standard (to the Nth degree)... we will ADOPT something about halfway in between... but we need to waste the time now following protocol to prove what we already know. Good Luck! ajw -Original Message- Sent: Wednesday, February 27, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Don, if as you are saying this guy is v headstrong then use the Chinese approach. 1. Ensure that you have backed up your argument with a design or at least a doc outlining your approach showing that views and associated tables will ensure performance . 2. Send your emails to him and to others so that there is a trace. 3. Then wait and let it blow up. This should not take too long as the spec never included any indexes either. This way you have followed his design to the letter. 4. Let the users kill him when they have to wait 2 hours for the statement to return a value. 4. This means that you will have time to perfect a design using a CASE tool. 5. In the end his table could be used as a staging area Just wait don't get annoyed, smile. Just think you can have his job soon. Kind Regards Peter Lomax (Oracle DBA) Expertise Oracle ORANGE/DSI/SIMBAD/ATP OrangeFrance Bureau: email: [EMAIL PROTECTED] tel:(+33) (0)1 55 22 59 13 fax:(+33) (0)1 55 22 39 69 Simbad sailing through UMTS. -Message d'origine- De : Don [mailto:[EMAIL PROTECTED]] Envoyé : mercredi 27 février 2002 07:48 A : Multiple recipients of list ORACLE-L Objet : Manager decrees his data warehouse design. Help! I've lost patience, my temper, and I'm about to quit a job because the IT manager has decreed that we will have his data warehouse running within 24 hours, and we will use his design. 1 - We are NOT to use any kind of views, not even materailzed views. 2 - we are not to do any computations, summaries or rollups 3 - we are to have everything in one table 4 - the table name and column names will be meaningful to any clerk 5 - we are not to start or snowflake designs. That's just a bunch of
RE: RE: Manager decrees his data warehouse design. Help!
What is the meaning of relational in relational database again? Good grief. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
At one of the Oracle Application group meetings it was stated that it is better to have large tables and forget normalization. Disks are getting faster and you can read a lot more data from one disk reather that getting your data from many disk locations. Also it doesn't really matter the size of the tables if you use the S.A.M.E theory. All disks are treated as one disk farm today. I haven't tried it but it sounded reasonable. ROR mª¿ªm [EMAIL PROTECTED] 02/27/02 03:08PM Also not uncommon when tracking medical data. Bambi. -Original Message- Sent: Wednesday, February 27, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Some of the tables in J.D. Edwards OneWorld have over 200 columns, VARCHAR is not used, only NUMBER and CHAR. Makes for some wide tables. This product was originally some type of flat file database. Ethan -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import placing data into wrong tablespace
The export dump will have the create table definition with the tablespace DATA. You have two ways to go about it: 1. Precreate the tables in the tablespace ENVTST_DATA and then perform the import with the ignore=Y. 2. Ensure user that not have any quota on DATA tablespace, enuf quota on ENVTST_DATA tablespace, and then import with ignore=Y Raj Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@cc: yahoo.com Subject: RE: Import placing data into wrong tablespace Sent by: root@fatcity. com February 27, 2002 03:18 PM Please respond to ORACLE-L ENVTST doesn't need unlimited tablespace, because SYSTEM has unlimited tablespace --- Deshpande, Kirti [EMAIL PROTECTED] wrote: ENVTST with unlimited tablespace, quota on DATA ??? - Kirti -Original Message- Sent: Wednesday, February 27, 2002 12:20 PM To: Multiple recipients of list ORACLE-L I have a user ENVTST with a default tablespace ENVTST_DATA. I have a user ENVTPA with a default tablespace DATA. I export user ENVTPA as SYSTEM, and then try to import into ENVTST schema, also as SYSTEM. Data is going into DATA tablespace, not ENVTST_DATA tablespace. Any ideas? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
SNP0...SNPx Memory Hogs
8.1.7.2.5 under Win2k Server I just discovered that my four SNP processes were sitting around, doing no work, taking up about 500MB of RAM. Is this normal? In Task Manager, the Oracle process was showing to be using about 1.4GB. After I issued Alter System Set Job_Queue_Processes=0 it shrank to about 900MB. I then issued Alter System Set Job_Queue_Processes=4, but the Mem Usage stayed the same. None of those SNP processes had done any work (i.e., run any jobs) for about 10 hours. I would have expected the SNP processes to release memory when their jobs finish. I've been trying to figure out what processes were chewing up RAM, ramping up over the course of 3 or 4 days. Some Java processes that we run were found to be hogs, but after I got the developers to disconnect/reconnect occasionally, those sessions were OK. I finally found the culprits when I was checking sessions' session pga memory. A couple of sessions were using about 200MB each. I checked to see what SQL they'd been running and found it to be dbms_ijob calls, which lead me to suspect the SNP processes - alas, Win2k doesn't let you look at individual Oracle processes like UNIX does. Anyway, I can't find any mention anywhere that dormant SNP processes can be memory hogs. Am I missing something? For now I'll set Job_Queue_Processes to 0 and back to 4 right after the heavy work each night. Any init parameters I'm missing? ...other suggestions? Thanks. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
S.A.M.E.? -Original Message- Sent: Wednesday, February 27, 2002 3:48 PM To: Multiple recipients of list ORACLE-L At one of the Oracle Application group meetings it was stated that it is better to have large tables and forget normalization. Disks are getting faster and you can read a lot more data from one disk reather that getting your data from many disk locations. Also it doesn't really matter the size of the tables if you use the S.A.M.E theory. All disks are treated as one disk farm today. I haven't tried it but it sounded reasonable. ROR mª¿ªm [EMAIL PROTECTED] 02/27/02 03:08PM Also not uncommon when tracking medical data. Bambi. -Original Message- Sent: Wednesday, February 27, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Some of the tables in J.D. Edwards OneWorld have over 200 columns, VARCHAR is not used, only NUMBER and CHAR. Makes for some wide tables. This product was originally some type of flat file database. Ethan -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Cupp INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
Relational. Adjective. Of, or relating to, relatives. Generally pertaining to mandatory dinners or inane conversations regarding politics, religion, sex, money or military service. Of necessity, the tables are denormalized, that is, all semblance to normalcy is rejected, especially when discussing Uncle Vernor's time in Normandy with that goat. HTH, Bambi. -Original Message- Sent: Wednesday, February 27, 2002 2:28 PM To: Multiple recipients of list ORACLE-L What is the meaning of relational in relational database again? Good grief. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
rows and columns... you know... like Excel. -Original Message- Sent: Wednesday, February 27, 2002 2:28 PM To: Multiple recipients of list ORACLE-L What is the meaning of relational in relational database again? Good grief. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@ M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!) M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@ M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE %;G0N#0H end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
Here is the Query you are looking for. set lines 132 set pages 30 col rr heading 'RB Segment' format a18 col os heading 'OS User' format a10 col te heading 'Terminal' format a10 col sid format 9 col spid format 99 select r.name ROLLBACK SEG, s.sid, s.serial#, s.username,osuser,START_TIME from v$session s, v$transaction t, v$rollname r where s.taddr=t.addr and t.xidusn = r.usn order by 1 / Brijesh Gupta Oracle Production DBA Air Liquide Inc. Phone : (713) 438 6259 Fax : (713) 438-6825 Cell : (713) 539-1375 Email : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ~~~ -Original Message- Sent: Tuesday, February 26, 2002 3:03 PM To: Multiple recipients of list ORACLE-L Thanks for the replies. I have all sorts of neat queries (which I can post) which show me gobs of information about my rollback segments (sizes, extents, optimal, shrinks, active transactions, block used by those transcations, ad infinitum...). HOWEVER, I still cannot find an answer to my original question; Is there a way to tell if anyone is reading from the rollback segments? Readers do not open transactions, correct? So they will not show up on the queries most of us are running against v$rollxxx and v$transaction. Where can I find out of someone is using the undo info in the rollbacks for read consistency? In other words, how do I find the readers (from rollback, not from the tables themselves)? I do not want to issue a shrink (and thus risk a ORA-01555) if people are still using the rollback for read consistency. To answer another reply's question: I am shrinking the rollbacks right before I run a large batch job, so as to give the job the maximum amount of space in the rollback tablespace. (I cannot utilize 'set transaction use ...' as this is an Oracle Apps job which actually does many transactions (re: purges)). -Original Message- From: Glenn Travis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Subject: Users reading from rollback segments Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gupta, Brijesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import from a dump which was split and compressed while export.
Hi All, The compressed dump export file size of my database is more than 2 gb and my OS file size limitation is 2 Gb so I compressed and split it into two parts while export. While export I got is export terminated successfully without warning but at the time of import, after successfully importing few tables it gave me the following errors at the end. IMP - 9 uncompress: 0653-059 The input file contains bad data; SIGSEGV signal received. and I could not import the data from it. Script used for Export is as : mknod /home/oracle/exp_pipe p compress /home/oracle/exp_pipe | split -b1023m - /home/oracle/dmps/EXPORT. ___ The script for the import I used was mknod /home/oracle/dkg/imp/imp_pipe p cat /test3/test/EXPORT* | zcat /home/oracle/dkg/imp/imp_pipe sleep 20 imp username/passwd@TEST file=/home/oracle/dkg/imp/imp_pipe log=/home/oracle/imp/imp.log buffer=117376000 recordlength=64000 fromuser=username touser=username ignore=y ___ Kindly suggest where I am wrong. Why the import terminates after successfully impoting of few tables Thanks, Deepender [EMAIL PROTECTED] 914 697 2169(work) 914 671 3113(cell) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
Stripe And Mirror Everything --- Michael Cupp [EMAIL PROTECTED] wrote: S.A.M.E.? -Original Message- Sent: Wednesday, February 27, 2002 3:48 PM To: Multiple recipients of list ORACLE-L At one of the Oracle Application group meetings it was stated that it is better to have large tables and forget normalization. Disks are getting faster and you can read a lot more data from one disk reather that getting your data from many disk locations. Also it doesn't really matter the size of the tables if you use the S.A.M.E theory. All disks are treated as one disk farm today. I haven't tried it but it sounded reasonable. ROR mª¿ªm [EMAIL PROTECTED] 02/27/02 03:08PM Also not uncommon when tracking medical data. Bambi. -Original Message- Sent: Wednesday, February 27, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Some of the tables in J.D. Edwards OneWorld have over 200 columns, VARCHAR is not used, only NUMBER and CHAR. Makes for some wide tables. This product was originally some type of flat file database. Ethan -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Cupp INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Manager decrees his data warehouse design. Help!
Well, you know the saying, SAME is LAME. Let's just forget all this database crap. Use a humongous flat file, get yourself the GNU version of grep with the Boyer-Moore search algorithm, add cut, paste and awk. Who needs an RDBMS anyway? As for disks getting faster, in the words of James Morle, 'do the math' Jared Ron Rogers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/27/02 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Manager decrees his data warehouse design. Help! At one of the Oracle Application group meetings it was stated that it is better to have large tables and forget normalization. Disks are getting faster and you can read a lot more data from one disk reather that getting your data from many disk locations. Also it doesn't really matter the size of the tables if you use the S.A.M.E theory. All disks are treated as one disk farm today. I haven't tried it but it sounded reasonable. ROR mª¿ªm [EMAIL PROTECTED] 02/27/02 03:08PM Also not uncommon when tracking medical data. Bambi. -Original Message- Sent: Wednesday, February 27, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Some of the tables in J.D. Edwards OneWorld have over 200 columns, VARCHAR is not used, only NUMBER and CHAR. Makes for some wide tables. This product was originally some type of flat file database. Ethan -Original Message- Sent: Wednesday, February 27, 2002 1:28 PM To: Multiple recipients of list ORACLE-L I think the column limit is now closer to 1000, but like you, I can't imagine willingly designing a table with a column count exceeding 2 digits. More than 15 or 20 and I start to question the design. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01000: maximum open cursors exceeded
Title: RE: ORA-01000: maximum open cursors exceeded Sam, That sounds like a huge number of cursors, does the Java application really have 7500 cursors open at any one time? I too have found that some Java applications have required a large number of open cursors but this is only because the Java developers had forgotten to close Statement and ResultSet objects. Once I got them to close these, I was able to set the limit to some smaller value. Had I left the max_open_cursors parameter set to some large value we might not have found this defect as soon as we did. Cheers, Craig. -Original Message- From: Sam Roberts [mailto:[EMAIL PROTECTED]] Sent: Saturday, 9 February 2002 1:43 AM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-01000: maximum open cursors exceeded no performance impact whatsoever . I have various Java applications that require huge number of cursors and i have limit set to 7500 without any issues Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, February 08, 2002 5:03 PM We had this error show up the other day. I am wondering if there is a performance limit on how big you should set your OPEN_CURSORS parameter in the SID.init file? Mine currently is set at 300. Are there any guidelines on this setting? Just wondering. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Roberts INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).