RE: partitioning
Partition views, after all, were not that bad! -Original Message- Sent: Wednesday, May 22, 2002 5:21 PM To: Multiple recipients of list ORACLE-L Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- 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). -- 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). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: partitioning
Dennis, Sure, you need to use INSTEAD OF triggers on views. I don't think Oracle8 supports INSTED OF triggers, but Oracle8i does. I was working on a project some time ago where we had used partitioned views and instead of triggers to implement functionality that's somewhat similar to partitioning option! Sure, you don't have all the nice features at your disposal for administration and maintenance that partitioning provides, but when you look at the price difference between the two choices, and all you need is simple and basic partitioning, then partitioning view might work for you, and save you some big bucks :) HTH and GL! Jay -Original Message- Sent: Wednesday, May 22, 2002 7:12 PM To: Multiple recipients of list ORACLE-L Quick memory test - were you able to create partition views such that you could insert rows into view? In other words, could you create multiple tables joined in a view, then be able to insert into the view? If anybody can recall, I would appreciate it. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 22, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Partition views, after all, were not that bad! -Original Message- Sent: Wednesday, May 22, 2002 5:21 PM To: Multiple recipients of list ORACLE-L Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- 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). -- 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). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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
RE: Zero-term'd machine in V$SESSION from Winders
Rich, We do have Win clients, and V$SESSION does show proper values for machine and terminal. Are these sessions for background processes and/or slave processes? Jay -Original Message- Sent: Tuesday, May 14, 2002 4:23 PM To: Multiple recipients of list ORACLE-L So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. 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). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: Prod problem, please help!!!
received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: Using OID
To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: High DML Table - Suggestions??
Walter, Couple of other areas to watch while DMLs are being issues against this table are: LGWR and DBWR statistics and activities. Based on DBWR/LGWR statistics, you may need to tune these parameters. Jay -Original Message- Sent: Monday, August 20, 2001 5:31 PM To: Multiple recipients of list ORACLE-L Hi, I have a table that is going to have a large amount of inserts, updates and deletes performed against it daily. Approximately 1,000,000 transactions per day (some single-record, some multi-record). The table is ~100Mb in size. I'm looking for some suggestions on what I can do to have the most optimal I/O for the table. I've been doing a little reading about buffer pools. Is assigning this table to a KEEP pool a practical approach or is that not going to buy me anything because DML is involved? Does anyone have any other suggestions? Unfortunately, I can't put the table on a dedicated disk and I am stuck with Raid-5 currently. Any suggestions would be appreciated. Thanks in advance! -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K 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). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: CURSOR_SHARING=FORCE
We are also experimenting with CURSOR_SHARING to reduce excessive parsing in the application, and made few observations. It appears that Oracle doesn't replace literals with system generated bind variables if SQL statement has both literals and bind variables, as shown here: SELECT RV_VALUE FROM REF_CODES WHERE RV_DOMAIN = 'YESNO' AND RV_ABBREVIATION = RTRIM(:b1) Jay -Original Message-From: Babette Turner-Underwood [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 24, 2001 5:38 PMTo: Multiple recipients of list ORACLE-LSubject: CURSOR_SHARING=FORCE We have noticed an interesting side "effect" of using CURSOR_SHARING=force.When using SQL (simple INSERT, UPDATE, SELECT...), and you check v$SQLAREAyou see that yes, Oracle indeed replaced hard-coded values with bindvariablesTRY: SELECT DEPTNO, LOC from DEPT where LOC='Boston';select sql_text from v$sqlarea where sql_text like 'SELECT DEPTNO';BUT this does not work for parameters to procedures or functions.TRY:create procedure upd_dept ( in_deptno number, in_loc varchar2) begin update dept set loc = in_loc;end;exec upd_dept ( 20, 'BOSTON');select sql_text from v$sqlarea where sql_text like '%upd_dept%';Thus we need to change calling our table APIs from :upd_dept( 20, 'BOSTON');todefine my_deptno := 20;define my_location := 'BOSTON';upd_dept( :my_deptno, :my_location);To use bind variables. Thus making extensive use of table APIs will havemultiple copiesof SQL in shared pool UNLESS done this way and CURSOR_SHARING has no effect.According to Oracle, it is how it is supposed to work, but we were notexpecting the behaviour.Just another one of those pleasant surprises from Oracle :-)Babette[EMAIL PROTECTED] This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED]
CPU/Parse Time Reported by SQL Trace
While working on application performance issues, I noticed significant discrepancy in time reported by SQL Trace and actual time taken by the application. Total Elapsed time reported by SQL Trace was 180 seconds, but it took 500 seconds to run it. (It was a PL/SQL procedure. I just measured the time to run the PL/SQL procedure.) Parse Elapsed Time reported by SQL Trace is 90 seconds, but V$SESSTAT reported parse time elapsed of only 15 seconds. Parse CPU Time reported by SQL Trace is 60 seconds, but V$SESSTAT reported parse time CPU of only 14 seconds. Any explanations on why such a big discrepancy on reported time? Thanks in advance, Jay This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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).
