bad SQL day...help please
List, Please excuse the content of this question. I haven't had a breakthrough yet so I'm hoping for some assistance... it may seem trivial to some but for some reason I am SQL-ly challenged today. I have a table which holds historical transaction records. Each PICK or RPCK record should have a corresponding SHIP record with a match on quantity, sku, and order_id. I have to create an exception report where if for any PICK/RPCK record there isn't a corresponding SHIP record, I should be shown the PICK/RPCK record. In other words, each sku has records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2 PICK/RPCK records, then 2 SHIP records. I know what I want in English, but I'm having trouble designing the query in SQL. In the table below, you can see that SKU 117127 has a PICK record but no SHIP record, same case for SKU 701206. Is someone kind enough to offer me some SQL advice? Thanks in advance, Saira OB_OID SKU TRANSACTQTY 50340 115227 RPCK36 50340 115227 SHIP36 50340 115304 RPCK36 50340 115304 SHIP36 50340 174040 RPCK12 50340 174040 SHIP12 50340 177127 PICK36 50340 177144 PICK24 50340 177144 SHIP24 50340 177624 PICK24 50340 177624 SHIP24 50340 177634 PICK48 50340 177634 SHIP48 50340 19 PICK20 50340 19 SHIP20 50340 20020 RPCK6 50340 20020 SHIP6 50340 701079 PICK100 50340 701079 SHIP100 50340 701206 RPCK30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bad SQL day...help please
Saira, Depends on the level of detail you want. Select sku from ... group by sku, quantity, order_id having mod(count(*), 2) != 0 does it. SF Saira Somani-Mendelin wrote: List, Please excuse the content of this question. I haven't had a breakthrough yet so I'm hoping for some assistance... it may seem trivial to some but for some reason I am SQL-ly challenged today. I have a table which holds historical transaction records. Each PICK or RPCK record should have a corresponding SHIP record with a match on quantity, sku, and order_id. I have to create an exception report where if for any PICK/RPCK record there isn't a corresponding SHIP record, I should be shown the PICK/RPCK record. In other words, each sku has records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2 PICK/RPCK records, then 2 SHIP records. I know what I want in English, but I'm having trouble designing the query in SQL. In the table below, you can see that SKU 117127 has a PICK record but no SHIP record, same case for SKU 701206. Is someone kind enough to offer me some SQL advice? Thanks in advance, Saira OB_OID SKU TRANSACTQTY 50340 115227 RPCK36 50340 115227 SHIP36 50340 115304 RPCK36 50340 115304 SHIP36 50340 174040 RPCK12 50340 174040 SHIP12 50340 177127 PICK36 50340 177144 PICK24 50340 177144 SHIP24 50340 177624 PICK24 50340 177624 SHIP24 50340 177634 PICK48 50340 177634 SHIP48 50340 19 PICK20 50340 19 SHIP20 50340 20020 RPCK6 50340 20020 SHIP6 50340 701079 PICK100 50340 701079 SHIP100 50340 701206 RPCK30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bad SQL day...help please
I have had good success with the minus operator:
select ob_oid, sku, qty from tbl where transact in ('RPCK','PICK')
minus
select ob_oid, sku, qty from tbl where transact = 'SHIP'
At 12:14 PM 11/27/2003, you wrote:
List,
Please excuse the content of this question. I haven't had a breakthrough
yet so I'm hoping for some assistance... it may seem trivial to some but
for some reason I am SQL-ly challenged today.
I have a table which holds historical transaction records. Each PICK or
RPCK record should have a corresponding SHIP record with a match on
quantity, sku, and order_id. I have to create an exception report where
if for any PICK/RPCK record there isn't a corresponding SHIP record, I
should be shown the PICK/RPCK record. In other words, each sku has
records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2
PICK/RPCK records, then 2 SHIP records.
I know what I want in English, but I'm having trouble designing the
query in SQL. In the table below, you can see that SKU 117127 has a PICK
record but no SHIP record, same case for SKU 701206.
Is someone kind enough to offer me some SQL advice?
Thanks in advance,
Saira
OB_OID SKU TRANSACTQTY
50340 115227 RPCK36
50340 115227 SHIP36
50340 115304 RPCK36
50340 115304 SHIP36
50340 174040 RPCK12
50340 174040 SHIP12
50340 177127 PICK36
50340 177144 PICK24
50340 177144 SHIP24
50340 177624 PICK24
50340 177624 SHIP24
50340 177634 PICK48
50340 177634 SHIP48
50340 19 PICK20
50340 19 SHIP20
50340 20020 RPCK6
50340 20020 SHIP6
50340 701079 PICK100
50340 701079 SHIP100
50340 701206 RPCK30
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani-Mendelin
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RE: wait/notify syntax for unix help please
There's no problem with waiting after the process has already finished,
you'll just get a non-zero return code the wait but evrything will still
work fine.
Chris
-Original Message-
Sent: 27 October 2003 18:54
To: Multiple recipients of list ORACLE-L
if you attemp to wait after the process is complete, will it cause a
problem? say the PID no longer exists when you issue wait?
From: Dunscombe, Chris [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 11:39:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and
then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then
have
the master script 'wait' for them to finish. Ive done this in Java and
with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
wait/notify syntax for unix help please
I need to parallelize some sql operations and Im running them from unix scripts. I want to spawn off a few in the background from a master script, then have the master script 'wait' for them to finish. Ive done this in Java and with dbms_alert, but I cant dig up the syntax to do this with korn shell on solaris. Also, if I want to wait for a specific PID, how do I get the PID of the thread I want to wait for? so I have nohup run_sql wait(on previous nohup) then to use notify, I just use 'notify()' inside the script right? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: wait/notify syntax for unix help please
I know that bash has wait built in. It works like this: GODOT=`ps -fu $LOGNAME|grep sqlplus|grep -v PID|perl -e 'while () [EMAIL PROTECTED] /\s+/; print $A[1] }'` wait $GODOT On 10/27/2003 11:09:25 AM, [EMAIL PROTECTED] wrote: I need to parallelize some sql operations and Im running them from unix scripts. I want to spawn off a few in the background from a master script, then have the master script 'wait' for them to finish. Ive done this in Java and with dbms_alert, but I cant dig up the syntax to do this with korn shell on solaris. Also, if I want to wait for a specific PID, how do I get the PID of the thread I want to wait for? so I have nohup run_sql wait(on previous nohup) then to use notify, I just use 'notify()' inside the script right? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then have
the master script 'wait' for them to finish. Ive done this in Java and with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: wait/notify syntax for unix help please
Here's another idea. Expand on it and modify as needed.
COUNT=1
while [ $COUNT -le 8 ]; do
## The first jobs command is to clear out any jobs completed
messages.
jobs /dev/null
if [ -z `jobs` ]; then break; fi
sleep 30
COUNT=$(( $COUNT + 1 ))
done
jobs /dev/null
echo SLEPT $COUNT times $LOG_FILE
## Kill any remaining jobs.
for JOB_NUMBER in `jobs | sed 's/\([^0-9]*\)\([1-9][0-9]*\)\(.*\)/\2/'`;
do
kill %${JOB_NUMBER}
echo killed job number $JOB_NUMBER $LOG_FILE
done
I need to parallelize some sql operations and Im running them
from unix
scripts.
I want to spawn off a few in the background from a master
script, then have
the master script 'wait' for them to finish. Ive done this in
Java and with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the
PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RE: wait/notify syntax for unix help please
if you attemp to wait after the process is complete, will it cause a problem? say the
PID no longer exists when you issue wait?
From: Dunscombe, Chris [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 11:39:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then have
the master script 'wait' for them to finish. Ive done this in Java and with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RE: wait/notify syntax for unix help please
On 10/27/2003 01:54:25 PM, [EMAIL PROTECTED] wrote: if you attemp to wait after the process is complete, will it cause a problem? say the PID no longer exists when you issue wait? Why don't you try it? There is this phenomenal Unix IDE called vi which can help you to write a shell script and O'Reilly has a book explaini9ng how to use it. There is also a heresy against The One True Editor called Emacs, but I'd advise against it. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: wait/notify syntax for Unix help please
Mladen Gogala scribbled on the wall in glitter crayon: On 10/27/2003 01:54:25 PM, [EMAIL PROTECTED] wrote: if you attemp to wait after the process is complete, will it cause a problem? say the PID no longer exists when you issue wait? Why don't you try it? There is this phenomenal Unix IDE called vi which can help you to write a shell script and O'Reilly has a book explaini9ng how to use it. There is also a heresy against The One True Editor called Emacs, but I'd advise against it. Emacs is a nice operating system, but i prefer Unix.;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] I cannot imagine a God who rewards and punishes the objects of his creation, whose purposes are modelled after our own - a God, in short, who is but a reflection of human frailty. Neither can I believe that the individual survives the death of his body, although feeble souls harbour such thoughts through fear or ridiculous egotisms. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i Lite - any help please?
Mogens, We did exactly as indicated in the e-mail from Martin and were encountering the same problems as before. Any helpful hints from your colleagues would be much appreciated. Thanks, Saira -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Mogens Nørgaard Sent: February 7, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle 9i Lite - any help please? Saira, Excellent feedback. I'll forward it to Jacob Christfort and Martin Graf. Martin, by the way, sent me a response to your posting (Jacob forwarded my message to Martin). See below. Best regards, Mogens Mogens, In deed, Oracle9i Lite has over 10'000 posted messages on OTN, which makes it #3 of all Oracle Products. This outstanding number of messagesproduces a lot of work for us, which is whyyou might experience slower than expected turn around for certain postings. We apologize for the delay but you can rest assured that we do everything in our power to resolve your issues. It looks like data does not get applied to the Oracle Lite (PROCESSING)database. Client changes are sent to the Mobile Server(SENDING). The client also received data from the server (RECEIVING). It might be that replication never finishes (commits) the transaction (since it hangs), which is why MGP might not pick up the changes and apply them to the Oracle database. COMPOSING client data SENDING client data to the Mobile Server RECEIVING data from the server PROCESSING (apply) data to the client database The PK change in one of your application table might actually be the culprit. Please follow the procedure below and let me know if you have further questions. 1. Drop the application from Control Center 2. Drop the client database(s) 3. Publish the applicationinto Mobile Server 4. Provision the application 5. Execute sync We recommend to use Oracle9i Lite 5.0.2 release with 5.0.2.3.0 Windows patch (the latest on Windows). -- martin
RE: Oracle 9i Lite - any help please?
Oracle has recommended patchset 2697758 Oracle9i Lite 5.0.1.6.0 patch for base version 5.0.1.0.0 - perhaps that will help. I will keep you informed. Thanks again, Saira -Original Message- Somani Sent: February 17, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Mogens, We did exactly as indicated in the e-mail from Martin and were encountering the same problems as before. Any helpful hints from your colleagues would be much appreciated. Thanks, Saira -Original Message- Nørgaard Sent: February 7, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Saira, Excellent feedback. I'll forward it to Jacob Christfort and Martin Graf. Martin, by the way, sent me a response to your posting (Jacob forwarded my message to Martin). See below. Best regards, Mogens Mogens, In deed, Oracle9i Lite has over 10'000 posted messages on OTN, which makes it #3 of all Oracle Products. This outstanding number of messages produces a lot of work for us, which is why you might experience slower than expected turn around for certain postings. We apologize for the delay but you can rest assured that we do everything in our power to resolve your issues. It looks like data does not get applied to the Oracle Lite (PROCESSING) database. Client changes are sent to the Mobile Server (SENDING). The client also received data from the server (RECEIVING). It might be that replication never finishes (commits) the transaction (since it hangs), which is why MGP might not pick up the changes and apply them to the Oracle database. COMPOSING client data SENDING client data to the Mobile Server RECEIVING data from the server PROCESSING (apply) data to the client database The PK change in one of your application table might actually be the culprit. Please follow the procedure below and let me know if you have further questions. 1. Drop the application from Control Center 2. Drop the client database(s) 3. Publish the application into Mobile Server 4. Provision the application 5. Execute sync We recommend to use Oracle9i Lite 5.0.2 release with 5.0.2.3.0 Windows patch (the latest on Windows). -- martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
View HELP Please!
I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: View HELP Please!
Freeman Robert - IL, Hi, can you show us the different execution path for the view and the sql? I think that is the key to solve the performance problem?Maybe hint like no_merge help? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 07:18:00 ,you wrote£º=== I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: View HELP Please!
Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Title: RE: View HELP Please! I remember some parameter about Query re-writes... it may be only for Materialized Views... but could be the culprit here. Also, do you have Oracle Label Security set on this table, or Fine-Grain Auditing? -Original Message- From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: View HELP Please! Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Predicate - values in the where clause. -Original Message- To: '[EMAIL PROTECTED]' Cc: Freeman Robert - IL Sent: 2/12/2003 1:24 PM Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: View HELP Please!
Freeman Robert - IL wrote: Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert Robert, The way I understand it is that your view is pretty complex. My experience with views is that past some degree of complexity (a hard notion to quantify) Oracle gives up any attempt to rewriting or recombining them and uses them 'as is'. I guess that the boundary increases in complexity with each release of Oracle, but here it is and you usually easily see from the plan whether the stored view was used or if it was broken into its constituent parts. I guess that the execution path it takes doesn't start with the table which contains the 'claim_carrier_key' column when you create the view. I would probably try to do what is considered bad practice by Oracle, i.e. a hint in the view. Some 'ordered' starting with the table which contains claim_carrier_key *might* be appropriate. The snag is that when you apply _other_ conditions to your view, queries may then be far more slower than your 6 mn ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
The explain plans indicate that the predicate is being filtered out after the view itself executes. I don't see how a no_merge hint would help, since I'm not getting view merging anyway. Thanks! Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Freeman Robert - IL, Hi, can you show us the different execution path for the view and the sql? I think that is the key to solve the performance problem?Maybe hint like no_merge help? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 07:18:00 ,you wrote?o=== I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert, Have you considered the current values of _complex_view_merging, _push_join_predicate, _push_join_union_view. Some of them may be obsolete in 9iR2, but there are some published and recommended setting for these for specific Apps 11i environments. You could also look at/play with the value of 'optimizer_max_permutations'. You might have this piece of code up your sleeve, but if you don't here ya go! column ksppinm format a45 heading Name column ksppstvl format a30 heading Value column ksppdesc format a79 heading Description select x.ksppinm, y.ksppstvl, x.ksppdesc from x$ksppi x, x$ksppcv y where (x.indx= y.indx) and (x.ksppinm like '\_undoc_parm' escape '\') order by x.ksppinm / John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: View HELP Please! Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: View HELP Please!
Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
There are 2 or 3 parameters, but none help. Nope, no FGAC... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 12:59 PM I remember some parameter about Query re-writes... it may be only for Materialized Views... but could be the culprit here. Also, do you have Oracle Label Security set on this table, or Fine-Grain Auditing? -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, February 12, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert, Is the view part of an application that you can make use of an OUTLINE? I think that is the proper terminology. Then you could force the desired optimization. Ron [EMAIL PROTECTED] 02/12/03 01:39PM Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Tom - Human grammar terms adapted to computer syntax :-) http://www.student.math.uwaterloo.ca/~cs448/db2_doc/html/db2s0/frame3.htm#ch 2pred Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: View HELP Please!
Hi Rachel, I've actually tried it both ways, with no joy. best luck I've had so far is turning max_purm's to about 100 which makes it run in about 2 minutes. Still to slow though. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 1:54 PM Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: View HELP Please - Problem solved
I think I fixed the problem... within my view, there was actually aggrigation of the predicate going on. I'm thinking that this is why Oracle could not push the predicate into the view. Once I removed the aggregation (I moved it to a higher level view) it worked great. Thanks to everyone for your thoughts. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 1:54 PM Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Re: View HELP Please!
Robert, Why not run a trace on both the view and standalone SQL, and share the results? At least the explain plans. Jared Freeman Robert - IL [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/12/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:View HELP Please! I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 9i Lite - any help please?
e using this product :-) --- - RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC - records to process yet PROCESSING does not occur We have changed records on the client in offline mode and now want to syncronize with the server. There are records to be processed - yet, in MSYNC, it goes through COMPOSING, SENDING, RECEIVING but no PROCESSING. As a result, our records on the server are not updated. MGP is running, and when we retry, we allow for complete cycles to occur before our next attempt. After trying about 20 times (no kidding), the PROCCESSING bar on the MSYNC screen works. This is unacceptable and not to mention unreliable. Please help. Please also see my previous message on a related issue. Perhaps you can see a connection, I can't as yet. Thanks, Saira --- - RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC sticks on PROCESSING bar Recently we made a change to a table in our repository in 8i - changed a primary key. After this, the application was republished in Oracle 9i Lite and all client databases (Pocket PC Strong ARM) were refreshed (i.e. database was removed from the client and sync'd 3 times before usage). We have noticed that now, after the change, during the MSYNC process, that status bar hangs on the PROCESSING phase of MSYNC. The only way to rid this is by warm booting the PDA and trying it multiple times until it is successful (which sometimes it is and sometimes isn't). However, even when the PROCESSING bar has not completed until the end, the data from the PDA is sent but this is unreliable as all the MSYNC processes are not complete. This is inefficient. Any suggestions? Saira Somani IT Support/Analyst Hospital Logistics Inc.
Oracle 9i Lite - any help please?
Posted 2 messages in Oracle 9i Lite Forum on Metalink. No responses yet. I'm looking to this list for help on either of these issues. Thanks in advance for your time. Please e-mail me privately ([EMAIL PROTECTED]) if this is not the proper forum for these issues. Someone out there has to be using this product :-) RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC - records to process yet PROCESSING does not occur We have changed records on the client in offline mode and now want to syncronize with the server. There are records to be processed - yet, in MSYNC, it goes through COMPOSING, SENDING, RECEIVING but no PROCESSING. As a result, our records on the server are not updated. MGP is running, and when we retry, we allow for complete cycles to occur before our next attempt. After trying about 20 times (no kidding), the PROCCESSING bar on the MSYNC screen works. This is unacceptable and not to mention unreliable. Please help. Please also see my previous message on a related issue. Perhaps you can see a connection, I can't as yet. Thanks, Saira RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC sticks on PROCESSING bar Recently we made a change to a table in our repository in 8i - changed a primary key. After this, the application was republished in Oracle 9i Lite and all client databases (Pocket PC Strong ARM) were refreshed (i.e. database was removed from the client and sync'd 3 times before usage). We have noticed that now, after the change, during the MSYNC process, that status bar hangs on the PROCESSING phase of MSYNC. The only way to rid this is by warm booting the PDA and trying it multiple times until it is successful (which sometimes it is and sometimes isn't). However, even when the PROCESSING bar has not completed until the end, the data from the PDA is sent but this is unreliable as all the MSYNC processes are not complete. This is inefficient. Any suggestions? Saira Somani IT Support/Analyst Hospital Logistics Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 9i Lite - any help please?
Saira, I've forwarded your message to the CTO of Oracle Lite, my old friend Jacob Christfort, whom I know from our days at the National Hospital Dormitory in Copenhagen, where we had lots of fun, and where many nurses lived. Those were the days, but sadly we both ended up working with IT. If Jacob responds (he's a rather busy guy) I'll forward the responses to the list. Can I ask you what you think of Lite in general? I don't see many sites using it, but it looks like a cool thing for the right purpose... Best regards, Mogens Saira Somani wrote: Posted 2 messages in Oracle 9i Lite Forum on Metalink. No responses yet. I'm looking to this list for help on either of these issues. Thanks in advance for your time. Please e-mail me privately ([EMAIL PROTECTED]) if this is not the proper forum for these issues. Someone out there has to be using this product :-) RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC - records to process yet PROCESSING does not occur We have changed records on the client in offline mode and now want to syncronize with the server. There are records to be processed - yet, in MSYNC, it goes through COMPOSING, SENDING, RECEIVING but no PROCESSING. As a result, our records on the server are not updated. MGP is running, and when we retry, we allow for complete cycles to occur before our next attempt. After trying about 20 times (no kidding), the PROCCESSING bar on the MSYNC screen works. This is unacceptable and not to mention unreliable. Please help. Please also see my previous message on a related issue. Perhaps you can see a connection, I can't as yet. Thanks, Saira RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC sticks on PROCESSING bar Recently we made a change to a table in our repository in 8i - changed a primary key. After this, the application was republished in Oracle 9i Lite and all client databases (Pocket PC Strong ARM) were refreshed (i.e. database was removed from the client and sync'd 3 times before usage). We have noticed that now, after the change, during the MSYNC process, that status bar hangs on the PROCESSING phase of MSYNC. The only way to rid this is by warm booting the PDA and trying it multiple times until it is successful (which sometimes it is and sometimes isn't). However, even when the PROCESSING bar has not completed until the end, the data from the PDA is sent but this is unreliable as all the MSYNC processes are not complete. This is inefficient. Any suggestions? Saira Somani IT Support/Analyst Hospital Logistics Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i Lite - any help please?
Mogens, Thank you kindly for forwarding my questions to the head honcho. I would be extremely pleased if he responded but not offended if he didn't. Funny enough, we are currently using Lite with Intermec 700 handhelds (StrongARM chipset) running Pocket PC (Windows CE) in hospitals to manage supply carts. Interesting business. So since I have inherited administration of this software, I not an expert yet, but quickly finding out where documentation and logging is sparse. I find that it is quite difficult to figure out where a problem has occurred. Take for example the MSYNC process on the client which synchronizes with the repository on the 8i server. Every time the client syncs, the client tells me it is successful, even though it isn't. Secondly, there is processing that is performed on synchronization - I know for a fact that there is data to be processed, yet this client never fulfills that request to process the data and as a result, changes are not reflected in the repository. Then take migration. I wanted to upgrade from 5.0.1.X.0 to 5.0.2.X.0 - and I found out that I cannot even log into the administrative console. And that would be fine, if there was documentation that told me this is possible (maybe I missed that PDF, I don't know - too much information, too little time). And how about documentation on the tracing. Especially interpretation of the log/trace files - there is no guideline to explain to me what I can expect from the logging/tracing (again, maybe it's me for missing that PDF but I have searched for many months with little success). I think it is a good product with many bugs and I think it can be used very effectively if administered/configured optimally. I just think that there isn't enough expertise out there yet to assist those who have decided to go with this product. From an administrative standpoint, I find it difficult to grasp how and where errors are occurring. Even more difficult is trying to figure out a pattern to the errors. They are sporadic, inconsistent, and show no symptoms. Alerts for MGP failing are non-existent - don't know when it will go down and when it does, I can't explain why. Overall, I can see it having success in the right environment and with a stable release. Hope this helped but didn't offend any. Thanks again, Saira -Original Message- Nørgaard Sent: February 6, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Saira, I've forwarded your message to the CTO of Oracle Lite, my old friend Jacob Christfort, whom I know from our days at the National Hospital Dormitory in Copenhagen, where we had lots of fun, and where many nurses lived. Those were the days, but sadly we both ended up working with IT. If Jacob responds (he's a rather busy guy) I'll forward the responses to the list. Can I ask you what you think of Lite in general? I don't see many sites using it, but it looks like a cool thing for the right purpose... Best regards, Mogens Saira Somani wrote: Posted 2 messages in Oracle 9i Lite Forum on Metalink. No responses yet. I'm looking to this list for help on either of these issues. Thanks in advance for your time. Please e-mail me privately ([EMAIL PROTECTED]) if this is not the proper forum for these issues. Someone out there has to be using this product :-) --- - RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC - records to process yet PROCESSING does not occur We have changed records on the client in offline mode and now want to syncronize with the server. There are records to be processed - yet, in MSYNC, it goes through COMPOSING, SENDING, RECEIVING but no PROCESSING. As a result, our records on the server are not updated. MGP is running, and when we retry, we allow for complete cycles to occur before our next attempt. After trying about 20 times (no kidding), the PROCCESSING bar on the MSYNC screen works. This is unacceptable and not to mention unreliable. Please help. Please also see my previous message on a related issue. Perhaps you can see a connection, I can't as yet. Thanks, Saira --- - RDBMS Version: 8.1.7.0.0 Operating System and Version: Windows 2000 Server SP3 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Oracle 9i Lite Product Version: 5.0.1.1 MSYNC sticks on PROCESSING bar Recently we made a change to a table in our repository in 8i - changed a primary key. After this, the application was republished in Oracle 9i Lite and all client databases (Pocket PC Strong ARM) were refreshed (i.e. database was removed from the client and sync'd 3 times before usage). We have noticed that now, after the change, during the MSYNC process, that status bar hangs on the PROCESSING
Analitics help please....
I need some help with some SQL pleeeaasseee I'm trying to do some summay work with some data using some of the Oracle9i Analytic functions. Here is the sample data: SQL select * from sum_test; ID TODAY CODE VALUE -- - -- 1 02-FEB-03 A 100 1 02-FEB-03 A 200 1 02-FEB-03 B50 1 03-FEB-03 A50 1 03-FEB-03 B25 1 05-FEB-03 A 725 1 05-FEB-03 C 125 1 02-FEB-03 A 100 1 03-FEB-03 A 100 2 02-FEB-03 A 100 2 03-FEB-03 A 100 2 04-FEB-03 B 100 and here is how I want the output to look: Note that I want: 1. for the first day I want total by ID, tday (truncated date) and code. I want the daily total in the VAL_TOTAL column 2. For each subsiquent day, I want that days total in val_total ADDED to the previous total. ID TDAY CODE VALUE VAL_TOTAL -- - -- -- 1 02-FEB-03 A 100400 1 02-FEB-03 A 200400 1 02-FEB-03 A 100400 1 02-FEB-03 B50 50 2 02-FEB-03 A 100100 1 03-FEB-03 A50550 1 03-FEB-03 A 100550 1 03-FEB-03 B25 75 2 03-FEB-03 A 100750 2 04-FEB-03 B 100175 1 05-FEB-03 A 725 1275 1 05-FEB-03 C 125125 Here is the query I'm trying: select b.id, trunc(b.today) tday, b.code, b.value, sum(a.val_total) over (partition by b.id, trunc(b.today), b.code order by b.id, trunc(b.today) range between unbounded preceding and unbounded following) val_total from ( select id, trunc(today) today, code, sum(value) val_total from sum_test group by id, trunc(today), code ) a, sum_test b where a.id=b.id and trunc(b.today)=a.today and a.code=b.code order by 2, 1 / Results... ID TDAY CODE VALUE VAL_TOTAL -- - -- -- 1 02-FEB-03 A 100 1200 1 02-FEB-03 A 200 1200 1 02-FEB-03 A 100 1200 1 02-FEB-03 B50 50 2 02-FEB-03 A 100100 1 03-FEB-03 A50300 1 03-FEB-03 A 100300 1 03-FEB-03 B25 25 2 03-FEB-03 A 100100 2 04-FEB-03 B 100100 1 05-FEB-03 A 725725 1 05-FEB-03 C 125125 Obviously not correct. I know I can do this with a self join to the table, but I was hopeful that I could do it this way. Any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Analitics help please....
Will this work? select b.id , trunc(b.today) tday , b.code , b.value , sum(B.VALUE) over (partition by b.id, trunc(b.today), b.code order by b.id, trunc(b.today), B.CODE range between unbounded preceding and CURRENT ROW) val_total from ( select id, trunc(today) today, code, sum(value) val_total from sum_test group by id, trunc(today), code ) a , sum_test b where a.id=b.id and trunc(b.today)=a.today and a.code=b.code order by 2, 1 / - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 03, 2003 10:09 AM I need some help with some SQL pleeeaasseee I'm trying to do some summay work with some data using some of the Oracle9i Analytic functions. Here is the sample data: SQL select * from sum_test; ID TODAY CODE VALUE -- - -- 1 02-FEB-03 A 100 1 02-FEB-03 A 200 1 02-FEB-03 B50 1 03-FEB-03 A50 1 03-FEB-03 B25 1 05-FEB-03 A 725 1 05-FEB-03 C 125 1 02-FEB-03 A 100 1 03-FEB-03 A 100 2 02-FEB-03 A 100 2 03-FEB-03 A 100 2 04-FEB-03 B 100 and here is how I want the output to look: Note that I want: 1. for the first day I want total by ID, tday (truncated date) and code. I want the daily total in the VAL_TOTAL column 2. For each subsiquent day, I want that days total in val_total ADDED to the previous total. ID TDAY CODE VALUE VAL_TOTAL -- - -- -- 1 02-FEB-03 A 100400 1 02-FEB-03 A 200400 1 02-FEB-03 A 100400 1 02-FEB-03 B50 50 2 02-FEB-03 A 100100 1 03-FEB-03 A50550 1 03-FEB-03 A 100550 1 03-FEB-03 B25 75 2 03-FEB-03 A 100750 2 04-FEB-03 B 100175 1 05-FEB-03 A 725 1275 1 05-FEB-03 C 125125 Here is the query I'm trying: select b.id, trunc(b.today) tday, b.code, b.value, sum(a.val_total) over (partition by b.id, trunc(b.today), b.code order by b.id, trunc(b.today) range between unbounded preceding and unbounded following) val_total from ( select id, trunc(today) today, code, sum(value) val_total from sum_test group by id, trunc(today), code ) a, sum_test b where a.id=b.id and trunc(b.today)=a.today and a.code=b.code order by 2, 1 / Results... ID TDAY CODE VALUE VAL_TOTAL -- - -- -- 1 02-FEB-03 A 100 1200 1 02-FEB-03 A 200 1200 1 02-FEB-03 A 100 1200 1 02-FEB-03 B50 50 2 02-FEB-03 A 100100 1 03-FEB-03 A50300 1 03-FEB-03 A 100300 1 03-FEB-03 B25 25 2 03-FEB-03 A 100100 2 04-FEB-03 B 100100 1 05-FEB-03 A 725725 1 05-FEB-03 C 125125 Obviously not correct. I know I can do this with a self join to the table, but I was hopeful that I could do it this way. Any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Need some SQL help Please...
Good morning List,
Please bare with me, this is somewhat long with
the DDL and DML included.
I have two tables that are populated by triggers
to be used to audit data changes.
The source and target tables are identical in
structure with the addition of the DML and
sequence columns iin the target.
For each key there will be at least an insert (I)
row with possibly update (U) or delete (D) rows
in chronological order.
As each row is inserted it gets a sequence
number.
I am having some trouble getting the data to
return in the correct order.
It must be a chronological return of each parent
rows and its child rows that fall chronologically
between the insert/updates/deletes.
What I expect is this (dates abbreviated) for
par_key = 100:
par_key par_col par_insert par_update par_dml
par_seq chi_key chi_col chi_insert
chi_update chi_dml chi_seq
100 100 1/1/2003I
1100 100 1/1/2003
I1
100 200 1/2/20031/2/2003U
4100 100 1/1/2003
I1
100 200 1/2/20031/3/2003U
5100 100 1/1/2003
I1
100 200 1/2/20031/3/2003U
5100 200 1/1/20031/4/2003
U4
100 200 1/2/20031/3/2003U
5100 300 1/1/20031/5/2003
U5
100 200 1/2/20031/3/2003U
5100 400 1/1/20031/6/2003
U6
I have used this as basis for starting, but can't
seem to get it to show in the correct order or
without extra rows.
select p.par_key,
p.par_col,
p.par_insert,
p.par_update,
p.par_dml,
p.par_seq,
c.chi_key,
c.chi_col,
c.chi_insert,
c.chi_update,
c.chi_dml,
c.chi_seq
from tab_parent p,
tab_child c
where p.par_key = c.chi_key
andc.chi_insert between p.par_insert and
nvl(p.par_update,c.chi_insert)
or c.chi_update between p.par_insert and
nvl(p.par_update,c.chi_insert)
order by
par_key,par_col,par_seq,chi_key,chi_col,chi_seq;
---
drop table tab_parent;
create table tab_parent
(par_key number,
par_col number,
par_insert date,
par_update date,
par_dml char(1),
par_seq number);
drop table tab_child;
create table tab_child
(chi_key number,
chi_col number,
chi_insert date,
chi_update date,
chi_dml char(1),
chi_seq number);
truncate table tab_parent;
truncate table tab_child;
insert into tab_parent
values (100,100,to_date('10-jan-2003
08:00:00','dd-mon- hh24:mi:ss'),null,'I',1);
insert into tab_parent
values (200,200,to_date('10-jan-2003
08:00:00','dd-mon- hh24:mi:ss'),null,'I',2);
insert into tab_parent
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),null,'I',3);
insert into tab_parent
values (100,200,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('13-jan-2003
11:00:00','dd-mon- hh24:mi:ss'),'U',4);
insert into tab_parent
values (100,300,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('15-jan-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',5);
insert into tab_parent
values (200,200,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('17-jan-2003
15:30:00','dd-mon- hh24:mi:ss'),'U',6);
insert into tab_parent
values (200,300,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('17-jan-2003
15:30:01','dd-mon- hh24:mi:ss'),'U',7);
insert into tab_parent
values (200,400,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('18-jan-2003
16:50:00','dd-mon- hh24:mi:ss'),'U',8);
insert into tab_parent
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),
to_date('19-jan-2003
07:30:00','dd-mon- hh24:mi:ss'),'D',9);
insert into tab_child
values (100,100,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),null,'I',1);
insert into tab_child
values (200,200,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),null,'I',2);
insert into tab_child
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),null,'I',3);
insert into tab_child
values (100,200,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('10-jan-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',4);
insert into tab_child
values (100,300,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('15-feb-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',5);
insert into tab_child
values (100,400,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('16-feb-2003
12:33:00','dd-mon- hh24:mi:ss'),'U',6);
insert into tab_child
values (200,300,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('15-jan-2003
08:02:00','dd-mon- hh24:mi:ss'),'U',7);
insert into tab_child
values (200,400,to_date('10-jan-2003
Help Please: ORA-01031
I created a user J1416 with default, temp tablespaces with password expire. Then I granted CREATE SESSION to J1416 And then I tried to give select privs to the user J1416. Got the following error message. I did this while logged in as system with DBA role (checked select * from session_roles). Why is this not working. Please help. J2400 owns table TBL_A. SQL GRANT SELECT ON J2400.TBL_A TO J1416; GRANT SELECT ON J2400.TBL_A TO J1416 * ERROR at line 1: ORA-01031: insufficient privileges thank you Srinivas __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Srinivas 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: Help Please: ORA-01031
Connect as J2400 user then execute the grant. Srinivas wrote: I created a user J1416 with default, temp tablespaces with password expire. Then I granted CREATE SESSION to J1416 And then I tried to give select privs to the user J1416. Got the following error message. I did this while logged in as system with DBA role (checked select * from session_roles). Why is this not working. Please help. J2400 owns table TBL_A. SQL GRANT SELECT ON J2400.TBL_A TO J1416; GRANT SELECT ON J2400.TBL_A TO J1416 * ERROR at line 1: ORA-01031: insufficient privileges thank you Srinivas __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Srinivas 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: Suzy Vordos 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).
AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line (sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line ('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table . .
.
any ideas, please??
thanks, all
--
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: AFTER CREATE trigger help, please
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
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:
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: AFTER CREATE trigger help, please
ok - thanks
i edited the trigger to insert a row into a dummy table, just to see if it
works, and all works fine.
Now - the next piece.
I want this trigger to call a stored procedure that runs as an autonomous
txn and creates a public synonym for the newly-created table.
I've verified independently that the proc works (from the SQL*Plus command
line) and I know now that the trigger is being called, based on the previous
test.
so why wouldn't the public synonym be created?
THANKS!
**
Here's the text of the new trigger and procedure:
CREATE OR REPLACE procedure CREATE_SYNONYMS (
p_lwowner varchar2,
p_objname varchar2,
p_objtype varchar2) AS
pragma autonomous_transaction;
v_lwowner varchar2(30) := p_lwowner;
v_objname varchar2(30) := p_objname;
v_objtype varchar2(30) := p_objtype;
v_sql varchar2(200);
begin
v_sql := 'create public synonym ' || v_objname || ' for ' ||
v_lwowner || '.' || v_objname;
execute immediate (v_sql);
end;
/
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE' then
begin
create_synonyms ('lwdev', sys.dictionary_obj_name,
sys.dictionary_obj_type);
exception
when others then null;
end;
end if;
end;
/
-Original Message-
Sent: Friday, May 10, 2002 1:14 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
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: AFTER CREATE trigger help, please
Actually...
SQL create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
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:
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: Grabowy, Chris
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: AFTER CREATE trigger help, please
Limited yes, but invaluable when debugging a trigger, and other possible
uses.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:33 PM
To: [EMAIL PROTECTED]
Cc: Grabowy, Chris
Chris,
Well, dang, it does work!
Guess I should have tried it myself first.
It will only work from SQL Plus though, or other
tools that capture DBMS_OUTPUT.
Oracle must send it to the big bit bucket in the
sky otherwise.
Jared
Grabowy, Chris [EMAIL PROTECTED]
05/10/2002 11:25 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED],
'[EMAIL PROTECTED]' [EMAIL PROTECTED],
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:RE: AFTER CREATE trigger help, please
Actually...
SQL create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
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:
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: Grabowy, Chris
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: AFTER CREATE trigger help, please
Chris,
Well, dang, it does work!
Guess I should have tried it myself first.
It will only work from SQL Plus though, or other
tools that capture DBMS_OUTPUT.
Oracle must send it to the big bit bucket in the
sky otherwise.
Jared
Grabowy, Chris [EMAIL PROTECTED]
05/10/2002 11:25 AM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED],
'[EMAIL PROTECTED]' [EMAIL PROTECTED],
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:RE: AFTER CREATE trigger help, please
Actually...
SQL create or replace trigger test
2 after insert on test_table
3 begin
4 dbms_output.put_line ('Hello from trigger!!!');
5 end;
6 /
Trigger created.
Elapsed: 00:00:00.02
SQL insert into test_table values ('Hello');
Hello from trigger!!!
1 row created.
Elapsed: 00:00:00.06
Execution Plan
--
0 INSERT STATEMENT Optimizer=CHOOSE
Statistics
--
3 recursive calls
5 db block gets
7 consistent gets
0 physical reads
372 redo size
550 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
Bill, I would simplify the trigger, and then work out from there.
Chris
-Original Message-
Sent: Friday, May 10, 2002 2:23 PM
To: Multiple recipients of list ORACLE-L
Bill,
A trigger cannot display output.
Where would it go? A trigger does not run in an interactive
session, it runs in the database independently, regardless
of the presence or absence of a SQL Plus session.
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/10/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:AFTER CREATE trigger help, please
I'm creating an AFTER CREATE ddl trigger:
CREATE OR REPLACE TRIGGER after_create_trg
after create
on lwdev.schema
begin
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
then
begin
dbms_output.put_line ('TEST');
dbms_output.put_line
(sys.dictionary_obj_name || ' , ' ||
sys.dictionary_obj_type);
exception
when others then
dbms_output.put_line
('ERROR');
end;
end if;
end;
/
Trigger created as user LWDEV
I have serverout set to ON, but I get nothing when creating a new table .
.
.
any ideas, please??
thanks, all
--
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:
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: Need some help PLEASE on an ora 3113 in svrmgrl
Bill, I think u have some hung shared memory segments which need to b cleaned up before u can start ur instance. Do the following : At the Unix prompt, type : $ ipcs -mobs Then identify the segment id which has Owner = Oracle and Nattch = 0. Remove that segment using the following command : $ ipcrm -m shared_memory_id Start up the database. Hope this helps. Samir Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 28 January 2002 21:42 To: Multiple recipients of list ORACLE-L Hi All, i am running oracle 8170 on solaris 7, i had a problem shutting down oracle and ended up having to do a shutdown abort. i reboot the server to clean everything up and when i tried to restart the db i get the ora 3113 inside of svrmgrl. There are 2 other db that start up fine. It creates a 20m core file and there are no errors in the alert log, listener log, sqlnet log etc and no trace files. Any help would be greatly appreciated!! i am lost been looking around metalink for hours and can't find anything useful. Thanks, -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). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir 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: Pl/SQL help - please help me
PROCEDURE A as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-B -'||lv_time);
B;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-B -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
-
PROCEDURE B as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-C -'||lv_time);
C;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-C -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
--
PROCEDURE C as
stack_info VARCHAR2(4096);
i NUMBER :=0;
BEGIN
-- Division By Zero will Rise
INSERT INTO ttt VALUES(7/0);
EXCEPTION WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
- The error code _The errormessage
dbms_output.put_line(TO_CHAR(SQLCODE)||'-'||SQLERRM);
END;
- pick out the name of the procedure that is currently running,
- pick out the start_time of the procedure
- pick out the end_time of the procedure
This eg. will do Above Question.
set serveroutput on
BEGIN
lv_time varchar2(60);
DECLARE
BEGIN
-- Procedure A Start Time
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start- A -'||lv_time);
A
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End- A -'||lv_time);
-- Procedure A Start Time
END;
END;
-
CREATE TABLE ttt
(
f1 NUMBER
)
Nitheesh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pullikol Kumar
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: Pl/SQL help - please help me
I suggest you go buy yourself a good PL/SQL manual. Nothing that you are asking for here is all that complicated. I recommend the O'Reilly book The Oracle PL/SQL CD Bookshelf. -Original Message- [EMAIL PROTECTED] Sent: Sunday, January 27, 2002 9:15 PM To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- 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: Kimberly Smith 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).
Need some help PLEASE on an ora 3113 in svrmgrl
Hi All, i am running oracle 8170 on solaris 7, i had a problem shutting down oracle and ended up having to do a shutdown abort. i reboot the server to clean everything up and when i tried to restart the db i get the ora 3113 inside of svrmgrl. There are 2 other db that start up fine. It creates a 20m core file and there are no errors in the alert log, listener log, sqlnet log etc and no trace files. Any help would be greatly appreciated!! i am lost been looking around metalink for hours and can't find anything useful. Thanks, -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).
Never mind i fixed it but thanks, Need some help PLEASE on an
I found the problem and fixed it thanks. Hi All, i am running oracle 8170 on solaris 7, i had a problem shutting down oracle and ended up having to do a shutdown abort. i reboot the server to clean everything up and when i tried to restart the db i get the ora 3113 inside of svrmgrl. There are 2 other db that start up fine. It creates a 20m core file and there are no errors in the alert log, listener log, sqlnet log etc and no trace files. Any help would be greatly appreciated!! i am lost been looking around metalink for hours and can't find anything useful. Thanks, -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).
RE: Need some help PLEASE on an ora 3113 in svrmgrl
Can you start an instance? -Original Message- Sent: Monday, January 28, 2002 4:42 PM To: Multiple recipients of list ORACLE-L Hi All, i am running oracle 8170 on solaris 7, i had a problem shutting down oracle and ended up having to do a shutdown abort. i reboot the server to clean everything up and when i tried to restart the db i get the ora 3113 inside of svrmgrl. There are 2 other db that start up fine. It creates a 20m core file and there are no errors in the alert log, listener log, sqlnet log etc and no trace files. Any help would be greatly appreciated!! i am lost been looking around metalink for hours and can't find anything useful. Thanks, -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: Khedr, Waleed 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).
Flatten data help please?
Still learning! Appreciate the use of bandwidth ahead of time! Oracle 8.1.4 Data exists in 3 tables tblIdWeeks WeekID,WeekStart, WeekEnd tblIDDates AirID, WeekID,DayNum (0 thru 6),DayDate Leave_Detl_tbl Effective_Dt, Leave_type, Input_AM existing sql is:SELECT tblIDAirWeeks.WeekStart, tblIDAirWeeks.WeekEnd, tblIdAirDates.AirDayDate, [ORIG_LEAVE_TYPE_CD] [ORIGINAL_INPUT_AM] AS Expr1 FROM tblIDAirWeeks INNER JOIN (tblIdAirDates INNER JOIN AMS_AHRSADM_LEAVE_DETL_TBL ON tblIdAirDates.AirDayDate = AMS_AHRSADM_LEAVE_DETL_TBL.EFFECTIVE_DT) ON tblIDAirWeeks.AirWkID = tblIdAirDates.AirWkID WHERE (((tblIDAirWeeks.WeekStart)#12/27/2000#) AND ((tblIDAirWeeks.WeekEnd)#1/5/2002#) AND ((AMS_AHRSADM_LEAVE_DETL_TBL.INTERNAL_EMPL_ID)=000357)); I get data returned like so: WeekStart WeekEnd AirDayDate Expr1 1/21/011/27/01 1/23/01SICKF 450 1/21/011/27/01 1/24/01FLH 450 1/21/011/27/01 1/25/01FLH 450 1/21/011/27/01 1/26/01VAC 450 And would like a result that looks like this: WeekStart MONTUES WED THURS FRIWeekEnd 1/21/01SICKF 450 FLH 450 FLH 450 VAC 450 1/27/01 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, 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).
Need Help Please - With Procedures
Hello all,
I have attached an sql file containing a set of sql's (6) of them, which gives me
information regarding table fragmentation. What I need to do is instead of writing
seperate sql's, I need to write a procedure, where in I pass the owner and table name
and then the result comes out, as you will see in the last sql.
What I need is, if some one could please help in writing a procedure? How to put
cursors and use all the information in different cursor variables etc.
Please help.
Thanks.
Rgds,
Raja
Accept owner prompt 'Enter Owner Name '
Accept tnm prompt 'Enter Table Name '
set echo off verify off heading off
set termout off
col val4 new_val hwm_blocks noprint
col val5 new_val above_hwm noprint
col val6 new_val row_chains noprint
col val7 new_val row_size noprint
col val7a new_val pct_used noprint
col val7b new_val pct_free noprint
col val8 new_val num_rows noprint
col val9 new_val row_chains_pct noprint
select num_rowsval8,
blocks val4,
empty_blocksval5,
chain_cnt val6,
avg_row_len val7,
pct_usedval7a,
pct_freeval7b,
100*chain_cnt/num_rows val9
fromdba_tables
where table_name = upper('tnm')
and owner = upper('owner');
col val9 new_val block_size noprint
select value val9
from v$parameter
where name = 'db_block_size';
col val10a new_val blocks_alloc noprint
col val10b new_val bytes_alloc noprint
col val10e new_val hwm_bytes noprint
col val10f new_val bytes_used noprint
select hwm_blocks+above_hwm val10a,
(hwm_blocks+above_hwm)*block_size/1024/1024 val10b,
(hwm_blocks*block_size)/1024/1024 val10e,
(num_rows*row_size)/1024/1024 val10f
from dual;
col val11a new_val blocks_pct_used noprint
col val11b new_val bytes_pct_used noprint
select 100*hwm_blocks/blocks_alloc val11a,
100*num_rows*row_size/hwm_bytes/1024/1024 val11b
from dual;
col val12 new_val sf noprint
select count(*) val12
fromdba_extents
where segment_name= upper('tnm')
and owner = upper('owner');
set termout on
set echo off feedback off verify off
col bogus format 999,999,999 fold_after
select 'Owner : '||'owner' bogus,
'Table name : '||'tnm' bogus,
'pct_free : '||pct_free bogus,
'pct_used : '||pct_used bogus,
'Number of extents : '||sf||' -- Segment Fragmentation' bogus,
'Rows : '||num_rows bogus,
'Row size : '||row_size bogus,
'Rows frag:migration: '||row_chains bogus,
'Row % frag:migr. : '||row_chains_pct||'% -- Row Fragmentation' bogus,
'DB block size : '||block_size bogus,
'Blocks alloc : '||blocks_alloc bogus,
'Block HWM : '||hwm_blocks bogus,
'% alloc used by HWM: '||blocks_pct_used||'%' bogus,
'MB alloc : '||bytes_alloc||'MB' bogus,
'MB HWM : '||hwm_bytes||'MB' bogus,
'MB used: '||bytes_used||'MB' bogus,
'% HWM bytes used : '||bytes_pct_used||'% -- Block Fragmentation' bogus
from dual;
prompt *** The table owner..tnm must have been recently analyzed for accuracy
prompt *** You may need to ANALYZE TABLE owner..tnm DELETE STATISTICS
set echo on verify on heading on feedback on
RE: Need Help Please - With Procedures
Yeah I am reading, but if I could get a framework for a procedure, referring to my sql's, then that would be a big help. Please help. rgds, raja -- On Wed, 28 Nov 2001 11:25:01 Kevin Lange wrote: Two books . Oracle PL/SQL Programming and Oracle Built-in Packages. Both from Steven Feuerstein on O'reilly press. Good books for this. -Original Message- Sent: Wednesday, November 28, 2001 1:05 PM To: Multiple recipients of list ORACLE-L Hello all, I have attached an sql file containing a set of sql's (6) of them, which gives me information regarding table fragmentation. What I need to do is instead of writing seperate sql's, I need to write a procedure, where in I pass the owner and table name and then the result comes out, as you will see in the last sql. What I need is, if some one could please help in writing a procedure? How to put cursors and use all the information in different cursor variables etc. Please help. Thanks. Rgds, Raja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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: Viraj Luthra 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 Help Please - With Procedures
WOW ! all those new column names wth quirky names.
Well here is the procdure. make sure you got the GRANTs right
in order to compile it.
Also check to make sure I got the your_variable/my_variable match right.
hth
robert chin
CREATE OR REPLACE PROCEDURE table_fragmentation_info
( v_table IN VARCHAR2,
v_owner IN VARCHAR2
)
IS
v_num_rows dba_tables.num_rows%TYPE;
v_hwm_blocks dba_tables.blocks%TYPE;
v_above_hwm dba_tables.empty_blocks%TYPE;
v_row_chains dba_tables.chain_cnt%TYPE;
v_row_size dba_tables.avg_row_len%TYPE;
v_pct_used dba_tables.pct_used%TYPE;
v_pct_free dba_tables.pct_free%TYPE;
v_row_chains_pct NUMBER;
v_block_size v$parameter.value%TYPE;
v_blocks_alloc NUMBER;
v_bytes_alloc NUMBER;
v_hwm_bytesNUMBER;
v_bytes_used NUMBER;
v_blocks_pct_used NUMBER;
v_bytes_pct_used NUMBER;
v_sf INTEGER;
BEGIN
select num_rows,
blocks,
empty_blocks,
chain_cnt,
avg_row_len,
pct_used,
pct_free,
100*chain_cnt/num_rows
INTO
v_num_rows,
v_hwm_blocks,
v_above_hwm,
v_row_chains,
v_row_size,
v_pct_used,
v_pct_free,
v_row_chains_pct
from dba_tables
where table_name = upper(v_table)
and owner = upper(v_owner);
-
select value INTO v_block_size
FROM v$parameter WHERE name = 'db_block_size';
--
v_blocks_alloc := (v_hwm_blocks + v_above_hwm);
v_bytes_alloc := (V_hwm_blocks + v_above_hwm)* v_block_size/1024/1024;
v_hwm_bytes := (v_hwm_blocks * v_block_size)/1024/1024;
v_bytes_used := (v_num_rows * v_row_size)/1024/1024;
--
v_blocks_pct_used := 100 * v_hwm_blocks / v_blocks_alloc;
v_bytes_pct_used := 100 * v_num_rows * v_row_size / v_hwm_bytes/1024/1024;
--
select count(*) INTO v_sf
from dba_extents
where segment_name= upper(v_table)
and owner = upper(v_owner);
--
dbms_output.put_line('Owner : '||v_owner);
dbms_output.put_line('Table name : '||v_table);
dbms_output.put_line('pct_free : '||v_pct_free);
dbms_output.put_line('pct_used : '||v_pct_used);
dbms_output.put_line('Number of extents : '||v_sf||' -- Segment
Fragmentation');
dbms_output.put_line('Rows : '||v_num_rows);
dbms_output.put_line('Row size : '||v_row_size);
dbms_output.put_line('Rows frag:migration: '||v_row_chains);
dbms_output.put_line('Row % frag:migr. : '||v_row_chains_pct||'% -- Row
Fragmentation');
dbms_output.put_line('DB block size : '||v_block_size);
dbms_output.put_line('Blocks alloc : '||v_blocks_alloc);
dbms_output.put_line('Block HWM : '||v_hwm_blocks);
dbms_output.put_line('% alloc used by HWM: '||v_blocks_pct_used||'%');
dbms_output.put_line('MB alloc : '||v_bytes_alloc||'MB');
dbms_output.put_line('MB HWM : '||v_hwm_bytes||'MB');
dbms_output.put_line('MB used: '||v_bytes_used||'MB');
dbms_output.put_line('% HWM bytes used : '||v_bytes_pct_used||'% -- Block
Fragmentation');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(CHR(10)||'Are you sure you entered FIRST TABLE NAME THEN
OWNER NAME correctly ?');
END;
/
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, November 28, 2001 2:05 PM
Hello all,
I have attached an sql file containing a set of sql's (6) of them, which gives
me information regarding table fragmentation. What I need to do is instead of
writing seperate sql's, I need to write a procedure, where in I pass the owner
and table name and then the result comes out, as you will see in the last sql.
What I need is, if some one could please help in writing a procedure? How to
put cursors and use all the information in different cursor variables etc.
Please help.
Thanks.
Rgds,
Raja
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Chin
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 Help Please - With Procedures
raja, I would recommend that you heed Kevin's advise .. in the meantime .. here is a quick start e.g. for what you are looking to do .. create procedure Blah ( para_owner in varchar2, para_table in varchar2) is cursor c1(v_owner varchar2, v_table varchar2) is select blah1 , blah1 , ... from bigBlah where blah= v_owner and bloh = v_table; begin for rec in c1(para_owner, para_table ) loop dbms_output.put_line(rec.blah1); dbms_output.put_line(rec.blah2); end loop; end; / hth Deepak --- Viraj Luthra [EMAIL PROTECTED] wrote: Yeah I am reading, but if I could get a framework for a procedure, referring to my sql's, then that would be a big help. Please help. rgds, raja -- On Wed, 28 Nov 2001 11:25:01 Kevin Lange wrote: Two books . Oracle PL/SQL Programming and Oracle Built-in Packages. Both from Steven Feuerstein on O'reilly press. Good books for this. -Original Message- Sent: Wednesday, November 28, 2001 1:05 PM To: Multiple recipients of list ORACLE-L Hello all, I have attached an sql file containing a set of sql's (6) of them, which gives me information regarding table fragmentation. What I need to do is instead of writing seperate sql's, I need to write a procedure, where in I pass the owner and table name and then the result comes out, as you will see in the last sql. What I need is, if some one could please help in writing a procedure? How to put cursors and use all the information in different cursor variables etc. Please help. Thanks. Rgds, Raja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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: Viraj Luthra 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! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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).
Help Please
If I have a table that has a fields of type long, how can I move the data to a varchar2(300)? Thanks, Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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: Help Please
- Update the table by using either PL/SQL or 3gls. If the LONG is always 32k or less you can do this in plsql. If it may exceed 32k in size, plsql *cannot* manipulate it in any way shape or form. If the long is 32k or less, you simply declare a variable of type LONG : declare my_var long; begin and then you can select into it: ... select my_long_column into my_var from T where condition; ... Use Update here to update the value selected into the new column. end; / HTH Harsh -Original Message- Sent: Monday, August 27, 2001 6:21 PM To: Multiple recipients of list ORACLE-L If I have a table that has a fields of type long, how can I move the data to a varchar2(300)? Thanks, Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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: Harsh Agrawal 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).
Sql loader help please (newbie)
Thanks in advance for help, been lurking for a couple of weeks. I am using sqlldr73.exe to load a text file into an oracle 7.3.2.1.1 table. Field datatype is number(14,0). What is the correct statement in the tablename.clr file that will correctly load the data. The clr file info latest attempt for the two fields looks like this SICK_ACCRUAL position (569:583) number(14,0),VAC_ACCRUAL position (585:599) number(14,0) and doesn't work. I have seen example showing an INTEGER EXTERNAL definition but I'm too wet behind the ears to know what is missing... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, 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: Sql loader help please (newbie)
Try: SICK_ACCRUAL POSITION(569:583) integer external(14), VAC_ACCRUAL POSITION(585:599) integer external(14) Helen "Johnston, Steve" [EMAIL PROTECTED] wrote: Thanks in advance for help, been lurking for a couple of weeks.I am using sqlldr73.exe to load a text file into an oracle 7.3.2.1.1 table. Field datatype is number(14,0). What is the correct statement in the tablename.clr file that will correctly load the data.The clr file info latest attempt for the two fields looks like this "SICK_ACCRUAL position (569:583) number(14,0),VAC_ACCRUAL position (585:599) number(14,0)" and doesn't work. I have seen example showing an INTEGER EXTERNAL definition but I'm too wet behind the ears to know what is missing... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Johnston, SteveINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing Lists-! ! ---To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year!http://personal.mail.yahoo.com/
Re: Statspack report !! Resending Can someone help Please
Hierarchical, network and relational. That's what it was. My true age is - as per 4th of May this year - is 40. Big party, band playing, etc. About 130 guests. My wish list for all the guests I had invited was very short. Among the very few items were black socks, the latest book by John le Carre, a Leatherman Supertool and a Swiss Army knife. Well, I got three Swiss army knives, three Supertool's, six copies of le Carre's latest book...and 67 pairs of black socks. Oh, I got 13 bottles of excllent whisky, too. Well, one of Cary Millsap's relatives sent me two pairs of socks last week, explaining that 67 was 2 short of 69 - and that this would probably be the closest I got to 69 for a while...and Cary wants me to beleive it wasn't his idea. Rather cool relatives he's got himselves, this mr. Millsap. John Kanagaraj wrote: Hi Mogens, I think SQL*Net was called SQL*Star or something, at least with version 5? SQL*Star, as I remember it was SQL*Net + OpenGateway (or something like that). It was basically SQL*Net to other DBMS. (And there were three types of DBMS's - Hierarchical, something else, and that new-fangled Relational). Never got it to work other than test databases... Back in the gold old days, when Oracle2 was written in assembly and running on Now we know your true age Mogens ;-) John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= 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: Statspack report !! Resending Can someone help Please
Here's a response from someone who really knows... a(nother) bitter, old man, in other words. I think SQL*Net was called SQL*Star or something, at least with version 5? = The 'user calls' has nothing to do with the number of SQLs being executed, the number of SQLs being executed is 'execute count'. You may notice, that 'user calls' and 'SQL*Net roundtrips to/from client' are very close to the same figure. Back in the gold old days, when Oracle2 was written in assembly and running on the PDP-11 boxes, the RSX11 operating system had a feature that later was adopted by VMS. Namely the possibility to within one task (a process for you modern folks) to execute in different environments with different privileges. What you today know as client/server or two-task was really a single task, that would run in user mode or oracle mode (and kernel mode, but that's a different story). And whenever you made a call down this stack, this was a 'user call'. Although we now do piggy backing of calls, you can to some extend think of calls like parse, execute, fetch, commit, etc. being these user calls. SQL*Net (honestly, and this is a shame, I don't recall what we earlier called our networking interface) was a new invention in Oracle3, that allowed the user mode process and the oracle mode process to exist on two different systems, or just two processes on Unix with a two task interface such as pipes, and the previous user call was implemented as a SQL*Net roundtrip. Hence, not including some uninteresting details, a 'user call' is today almost identical to a SQL*Net roundtrip, but it really has little relation to the code executed in the client process (such as parse, execute, fetch, etc.) due to our focus on reducing the network roundtrips. (For those, who have seen a picture of the Oracle stack, the user-side is called UPI and the Oracle side OPI, but even these old friends (Oracle2) are now gradually getting thinner and replaced by new stuff. However, the basic principle is still the same) == Valiveru, Siva wrote: All, Can some there PLEASE clarify this question.. what is the corelation between user calls in the statspack report to number of sql's executed during the timeframe.. This is what i am doing please correct me !! We are trying to capture the total number of sql's calls during two time events (say t1, t2), what i was doing to take the snapshot report and copy of the v$sqlarea at t1 t2. 1) the sum of total number of executions from two V$sqlarea tables at t1 and t2 should be the total sql's excecuted. right ?? 2) what way the figure at step1 is releated to the user calls in the statspack report for difference of t1 and t2. *** Is there any other method do u suggest to capture number of sql's application is throwing during a timeframe. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Valiveru, Siva 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). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= 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: Statspack report !! Resending Can someone help Please
Hi Mogens, I think SQL*Net was called SQL*Star or something, at least with version 5? SQL*Star, as I remember it was SQL*Net + OpenGateway (or something like that). It was basically SQL*Net to other DBMS. (And there were three types of DBMS's - Hierarchical, something else, and that new-fangled Relational). Never got it to work other than test databases... Back in the gold old days, when Oracle2 was written in assembly and running on Now we know your true age Mogens ;-) John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Statspack report !! Resending Can someone help Please
All, Can some there PLEASE clarify this question.. what is the corelation between user calls in the statspack report to number of sql's executed during the timeframe.. This is what i am doing please correct me !! We are trying to capture the total number of sql's calls during two time events (say t1, t2), what i was doing to take the snapshot report and copy of the v$sqlarea at t1 t2. 1) the sum of total number of executions from two V$sqlarea tables at t1 and t2 should be the total sql's excecuted. right ?? 2) what way the figure at step1 is releated to the user calls in the statspack report for difference of t1 and t2. *** Is there any other method do u suggest to capture number of sql's application is throwing during a timeframe. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Valiveru, Siva 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).
How to downgrade database from 8.1.5 to 8.0.6 - Urgent Help Please!
Hello: Please help me on this crisis! What is the steps and safe procedures to downgrade database from 8.1.5 to 8.0.6 ??? I need to have this done by noon today. I have database 8.1.5 with all schemas and data. But the application can not support 8.1.5.I have to install 8.0.6.(new one) Then I need get all those data and schemas into 8.0.6. I know in some way this can be done, Now matter how risk I have to do at client's request. What is the steps and safe procedures??? Thanks in advanceDo You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
How to downgrade database from 8.1.5 to 8.0.6 - Urgent Help Please!
Hello DBAs: Please help me on this crisis! What is the steps and safe procedures to downgrade database from 8.1.5 to 8.0.6 ??? I need to have this done by noon today. I have database 8.1.5 with all schemas and data. But the application can not support 8.1.5. I have to install 8.0.6.(new one) Then I need get all those data and schemas into 8.0.6. I know in some way this can be done, Now matter how risk I have to do at client's request. What is the steps and safe procedures??? Thanks in advanceDo You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
Re: How to downgrade database from 8.1.5 to 8.0.6 - Urgent Help Please!
Export/Import this was actually mentioned in chapter 3 in the Oracle8i Migration Release (A76957-01) Hello: Please help me on this crisis! What is the steps and safe procedures to downgrade database from 8.1.5 to 8.0.6 ??? I need to have this done by noon today. I have database 8.1.5 with all schemas and data. But the application can not support 8.1.5. I have to install 8.0.6.(new one) Then I need get all those data and schemas into 8.0.6. I know in some way this can be done, Now matter how risk I have to do at client's request. What is the steps and safe procedures??? Thanks in advance - Do You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell? Roy E. Ferguson II Intel Sacramento 916-854-1123 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson 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).
