RE: Isolation Level to get Phantom Read

Thanks Ganesh. I'm getting most of the answers from only you. Thanks you so much. Let me read Tom's article. Regards Sami Ganesh Raja

dbms_stats via dbms_job - syntax question

Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true); but trying to get that into an

RE: dbms_stats via dbms_job - syntax question

Title: RE: dbms_stats via dbms_job - syntax question you don't need quotes around cascade=true ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal.

RE: dbms_stats via dbms_job - syntax question

John, I think the easiest way to do this is to create a stored procedure that calls dbms_stats for you. you could then simply run your stored procedure from dbms_jobs. create or replace procedure run_stats is begin dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10

RE: dbms_stats via dbms_job - syntax question

This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing

Re: dbms_stats via dbms_job - syntax question

Why not submit it exactly the same as you do it interactively: declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(ownname=''RPT_3G_MASTER'',estimate_percent=10,cascade=true);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / All you need to do is replace the single

median function

Title: median function I'm attempting to write a query to calculate the median of a column of numbers. The first solution I came across was Select avg(col1) MEDIAN from ( select rownum row1, col1 from a where col1 in (select col1 from a )) a where a.row1 in ( select floor(count(*)/2 +.5)

Re: How to put a TOP 1 in a select

Thanks!! First let me explain more about the query I have a table ICTRANS that have all the inventary movements. I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the

RE: StoredProc SQL statistics

Mr Shakir: Can you tell me the URL for the Tim Gorman website, and Steve Adams website? RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly

RE: How to put a TOP 1 in a select

use ROWNUM = 1 for instance -Original Message-From: Teresita Castro [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: Re: How to put a TOP 1 in a select Thanks!! First let me explain more about the query I have

Rule Based Optimizer

There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i

How to pass string with a ' to PL/SQL

I have procedure that needs to pass a string with a ' . Anyone have any idea how to do this in PL/SQL Here is my example: var a refcursorvar b varchar2(100);begin :b :='and sub_account_no=''864240103'''; execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02

SAME technology question .....

A couple years ago, Juan Louiza(sp) of Oracle Corporation put out a white paper regarding SAME (Stripe and Mirror Everything).I have read the comments from Steve Adams regarding this methodology. I am curious if anyone else is or is not using the SAME methodology and what has been your

RE: median function

Title: median function here is a note from the list fromawhile aback: Since this subject was brought back up, I thought maybe some would be interested in the following. I've never had a need to calculate a median, but, I knew Celko's SQL for Smarties had a few variations and examples from

RE: any intro statspack docs online?

Search MetaLink ... Burleson has a good book "High PerformanceTuning with Statspack" .. Tom Kytes Book "Oneon One" in Chapter 10 also hassome good information.. Look in your $ORACLE_HOME/rdbms/admin and depending on you version their is a sp*.doc or st*.txt document in there to show you

RE: Rule Based Optimizer

RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list

RE: any intro statspack docs online?

check these links http://www.oracle.com/oramag/oracle/00-Mar/o20tun.html http://www.oracle.com/oramag/oracle/00-Sep/o50tun.html http://www.oracle.com/oramag/oracle/00-Nov/o60tun.html http://www.oracle.com/oramag/oracle/00-Nov/o60tun_ol.html Dave -Original Message-From: Johnson,

RE: How to pass string with a ' to PL/SQL

var a refcursor var b varchar2(100); begin :b :='and sub_account_no=' || CHR(39) || '864240103 || CHR(39); execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b); end; / 39 is ASCII code for .

RE: How to pass string with a ' to PL/SQL

Laura, try it this way: :b :='and sub_account_no=' || || '864240103' || ; remember - 4 quotes gets you one. Tom Mercadante Oracle Certified Professional -Original Message-From: laura pena [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 1:25 PMTo: Multiple

RE: How to pass string with a ' to PL/SQL

change this :b :='and sub_account_no=''864240103'''; into this :- :b := ' and sub_account_no = '||||'864240103'||; -Original Message- Sent: Tuesday, June 10, 2003 1:25 PM To: Multiple recipients of list ORACLE-L I have procedure that needs to pass a string with a ' .

RE: How to pass string with a ' to PL/SQL

This does not work when executing my stored procedure: SQL var a refcursorSQL var b varchar2(100);SQL begin 2 :b :='and sub_account_no=' || || '864240103' || ; 3 execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by

RE: SAME technology question .....

I think there's a lot of confusion about SAME. For example, we have a SAN with the disks grouped into 4-disk RAID-5 sets, and volumes striped across all of the sets (well, we have 2 sets of sets). These are presented as LUNs to Veritas, and then built up into a 1 TB logical volume. All of my

Deleting Statspack tables.

Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table

RE: How to put a TOP 1 in a select

This query would give you the total quantity sold in the past two weeks and the date on which maximum number was sold, for each item ... SELECT a.item, a.tras_date AS max_sale_date, b.tot_qty AS tot_sale_qty FROM ICTRANS a, ( SELECT item, SUM(qty) tot_qty,

RE: How to pass string with a ' to PL/SQL

Show us the package body ... not the spec. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

RE: How to pass string with a ' to PL/SQL

Laura, I cant count to 4 -J thats why Im using CHR(39), see my previous message. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of laura pena Sent: Tuesday, June 10, 2003 1:44 PM To: Multiple

Re: Deleting Statspack tables.

I know there are some versions that the sppurge doesn't remove rows from the children tables. I haven't noticed this since I got to 8.1.7.3 on Tru64. You can write a script to delete from the other tables where they don't have a record in the parent table and run it the same time as SPURGE.

Gather stats on 11i

Hi Listers, I'm using concurrent program of 11i (Oracle financials) on 9i RAC to gather schemas stats but the program never finished because generates deadlock on several tables. I have to kill many sessions to allow program to finish. Did same one experienced this problem? Thank you for your

RE: Quest People

I forwarded this to someone in our company. I hope that soon untold riches will be yours! -Original Message- From: Maria Aurora de la Vega [mailto:[EMAIL PROTECTED] I'm looking for listers who happen to work for Quest. I would like to get information as to who/how to contact the

RE: How to pass string with a ' to PL/SQL

I'm curious why I often see an extra string concatenation operation for putting a single-quote in a string. Is it for readability? Why not :b := 'and sub_account_no=''864240103''' ; Note - there are no double quote characters in the string above, only single quote characters. Two single

RE: Benchmarking of ASP.NET with Oracle v/s MSSQL

I *think* you generally mean Visual Studio .Net where you say ASP.NET , If this is the case then the newly released VS.Net 2003 does indeed integrate the microsoft provider for Oracle with the various data tools that come with the VS product. I'd probably recommend the Oracle provider anyway in

RE: How to pass string with a ' to PL/SQL

Here you go... So far I have not gotten it to work... I have tried all suggestions so far. Seems to work if I just do it to a variable but once I call in the stored proc. It fails to work. Thanks -Lizz "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote: Show us the package body ... not the

RE: Deleting Statspack tables.

Reddy - You didn't mention your Oracle version. I am on 8.1.6 and I don't think those scripts are available there (sorry, busy day, no time to research). So I just delete from stats$snapshot. It seems to remove data from the associated tables just fine. First I select the snap_id and snap_time

RE: Rule Based Optimizer

Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB

Oracle 9i vs SQL Server

Title: Oracle 9i vs SQL Server I know this topic has come up before on the list, so I hesitate to kick it again.(I hesitatedI didn't say I wouldn't :) I know there are cost issues, as well as skill set questions (do you have Oracle skills/MS skills) in house. My company has an App that

sql query optimization

Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result

Named Pipes Protocol Adapter

List, Why would someone want to use Named Pipes Protocol Adapter? I have never used this, could someone educated me on this? David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services

World premier performance of the BAARF party logo

Friends, James Morle has done it again. Nobody does it better. For the first public showing of his BAARF animation, please GoTo www.MiracleAS.dk . Then get back to your work or mailing list fast :-). Let me know if you want to become a BAARF party member, and I'll assign you a BAARF party

RE: StoredProc SQL statistics

Surf to http://www.google.com Search for: tim gorman oracle and steve adams oracle Choose the top link for each. Google is your friend. HTH Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI

Re: How to pass string with a ' to PL/SQL

The means of quoting doesn't seem right for the error because it is complaining about not finding anything if I understand what the in the error message means. Therefore, as an off the wall idea try changing :b to b and see what happens. Pat laura

RE: Rule Based Optimizer

RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle

Re: Rule Based Optimizer

there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about

RE: How to pass string with a ' to PL/SQL

Laura, Would you try this? var a refcursorvar b varchar2(100);begin :b :='and sub_account_no=''864240103'''; :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);end;/ Regards Vadim -Original

RE: Deleting Statspack tables.

Version : 8.1.7.3. I am not sure how it is working for you just by deleting from stats$snapshot and we do not have 8.1.6 here. Thanks -Original Message- Sent: Tuesday, June 10, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Reddy - You didn't mention your Oracle version. I

Re: World premier performance of the BAARF party logo

FYI: The BAARF gif is now the background on my desktop at work. Goes well with the hat. ;) Jared Mogens Nørgaard [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 03:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]

RE: How to pass string with a ' to PL/SQL

I think the problem is that once you pass the string to a PL/SQL procedure, and you want to use it in dynamic SQL, you have to escape the single quote again. Perhaps the example below will help. SQL select * from emp ; ID LAST_NAME FIRST_NAME -

Re: Rule Based Optimizer

I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]

RE: How to pass string with a ' to PL/SQL

Sorry about that. Looking at your example more closely it seems like you may be doing it right. In your package body I see this: IF ( in_whereclause IS NOT NULL ) THEN v_sqlbeg := v_sqlbeg || in_orderbyclause ||' '; END IF; shouldn't that be in_whereclause in the second line?

RE: How to put a TOP 1 in a select

Your query returns the maximum quantity (and associated date) for a single ICTRANS entry. If there are multiple entries per day then the logic is a lot more complex - but certainly achievable. Since we don't know how data is stored in the table though the query below MAY be valid.

RE: StoredProc SQL statistics

http://www.evdbt.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jesse, Rich Sent: Tuesday, June 10, 2003 4:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: StoredProc SQL statistics Surf to http://www.google.com Search for:

Re: sql query optimization

This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd probably spend some time looking at the following to see if they might help you out: 1) Look at the sub-select with the connect by clause... Try executing that query on it's own

Re: Oracle 9i vs SQL Server

The bitter person in me suggests the answer will depend on which list you ask. Asking an Oracle list may provide a resounding No - it will choke and die, whilst asking a SQL Server list may be slightly more positive. I don't work with SQL Server myself but from what I've heard lately the

RE: Rule Based Optimizer

I am 99% confident that I remember the CBO existing on VAX/VMS in 7.1.5, and for sure in 7.1.6 (on VAX/VMS) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for

RE: How to put a TOP 1 in a select

This is an example of the information. I forgot to mention that in each company we havesupermarkets called in the system Locations. So when I do this query I have to return per item the total of sales in the las two week, the day that we sale more per supermarket Item trans_date Quantity

Re: sql query optimization

All very good suggestions, but given the low elapsed time and cost figures, I suspect most of the time is taken up jumping around buffer cache locating and pinning blocks. What would help a lot is to eliminate the table access by index rowid by including all query columns in your indexes. There

Re: Rule Based Optimizer

CBO came in with Oracle7, so that means 7.0.x onwards. The marketing literature said so :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 11:45 AM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in

Re: sql query optimization

Hi: Thanks for your help and suggestions, Mark. I have done some of the things you suggested already. Please see my text below. On Tue, 10 Jun 2003, Mark Richard wrote: This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd

Re: Deleting Statspack tables.

Um, if I understand correctly, you're just trying to keep the volume down in your statspack tables. I use Tim Gorman's sppurpkg.sql package (on several different versions across several different operating systems). (www.evdbt.com) I have it set up to keep 14 days of data, but you can change

Re: World premier performance of the BAARF party logo

Do NOT believe the liars and infidels. We have no BAARF in our computer room, nor will we allow an invasion of BAARF. The BAARF party will have its own equivalent of the Iraqi Information Minister to deliver key notes at their conventions --bb --- [EMAIL PROTECTED] wrote: FYI: The BAARF

Re: Rule Based Optimizer

Thanks for the confidence, Jared. It was introduced in 7.0 and the world hasn't been the same since. Mogens Jacques Kilchoer wrote: I am 99% confident that I remember the CBO existing on VAX/VMS in 7.1.5, and for sure in 7.1.6 (on VAX/VMS) -Original Message- From: [EMAIL PROTECTED]

RE: How to put a TOP 1 in a select

There may be different ways to write this query - otherwise you need to look at tuning this query. Four minutes for a single product in a 5 million row table doesn't sound really good but I guess it depends on hardware. Have a look at the explain plan for the query - you have a lot of

Re: sql query optimization

Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the

Database Auditing

Hi All, How do we turn on statement level auditing (DB and OS Level) to audit the DML/DDL's on the database.Is this possible with out triggers? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network

Re: Rule Based Optimizer

Yep. Here's the official statement from Oracle about RBO in 10i: The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an unsupported feature and will be removed in a

Re: Database Auditing

Hi, It depends. You can use triggers to do it (much easier with system triggers in 8i and onwards). You can use FGA (Fine-Grained Audit) from 9i onwards. If you just want to know how much DML is taking place aginst which tables you can use the monitoring facility in 8i onwards, which will

RE: Rule Based Optimizer

Hi RWB: Check the Note 189702.1 in Metalink. Its official from Oracle. ;) Best Regards, K Gopalakrishnan -Original Message- [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Mr. Gopalakrishnan: How do you know this? Can anyone confirm

Re: World premier performance of the BAARF party logo

Dear Barbare, You have just been made a Bold Member of the Party. Your Party number is 20. Mogens Barbara Baker wrote: Do NOT believe the liars and infidels. We have no BAARF in our computer room, nor will we allow an invasion of BAARF. "The BAARF party will have its own equivalent

Re: Database Auditing

Ravindra, I'm not sure I understand the question correctly. Statement level auditing? Are you referring to recording that a particular statement was executed on a specific table? If so, the plain vanilla AUDIT statement is for you. There is no need for any triggers to be set up. If you want more