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
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
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
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
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
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
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
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
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
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
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
]] 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
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
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
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
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
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
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
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
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
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:
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
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
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
?
** 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
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
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
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
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
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
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
-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
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
connec
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
,
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
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
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
-- -
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,
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
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).
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
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');
: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
: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
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
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
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
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 :
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
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
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
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
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
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,
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,
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
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
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
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
- 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
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
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
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
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
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
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
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
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
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
69 matches
Mail list logo
