RE: how to retrieve numeric values only from a varchar2?
I think if you try ltrim(to_char(i,'099')); it will remove a leading blank. There is I'm sure a way of doing it explicitly with the format of the to_char but I can't remember what it is. Iain Nicoll -Original Message- Sent: Monday, October 07, 2002 10:13 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only. I have tried the following piece of pl/sql, unsuccessfully declare begin for i in 1..999 loop update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099'); end loop; end; / Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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: Add_Month... Add_Hour? Add_Minute?
I think it's just that there is no need for a function as hour, minute and second are always known fractions of a day i.e 1/24, 1/1440, 1/86400 whereas month is variable. Given that a functions seems a bit excessive. Iain Nicoll -Original Message- Sent: Thursday, October 03, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Hi Gurus, i know there's 'add_month', is there any built-in function like 'add_hour' or 'add_minute'? or i have to write a function to add it? thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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: BUFFER OVERFLOW, LIMIT OF 2000 BYTES.
set serveroutput on size 100 (I think thats the max) Iain Nicoll -Original Message- Sent: Tuesday, September 24, 2002 5:53 PM To: Multiple recipients of list ORACLE-L Hi All, Below is a script to retrieve data from BFILE column and its output. The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput on DECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320); BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file); END; / == %PDF-1.3 %bcOS 1 0 obj /Creator feff001b7a68001b004d006900630072006f0073006f006600740020005700 6f007200640020 /CreationDate (D:19991019160202) /Title feff001b7a68001b003600310031003700700062006 30031002e005000440046 /Author feff001b7a68001b0052006f006200650072007400630075 /Producer (Acrobat PDFWriter 4.0 for Windows) /ModDate (D:20001019200402+08'00') endobj 2 0 obj [ /PDF /Text /Ima geB ] endobj 3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj 4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R /F2 10 0 R /F0 6 0 R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj 5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj 6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widt hs [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556 556 556 556 556 556 55 6 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278 500 667 556 833 722 778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556 556 500 556 556 278 55 6 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500 334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 5 00 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 DECLARE *ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-065! ! 12: at line 17 _ Do you Yahoo!? New DSL Internet http://rd.yahoo.com/evt=1207/*http://sbc.yahoo.com/ Access from SBC Yahoo! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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: SQL question
If the set of values is not too big and fixed you could do the minus using
dual
e.g.
(select 'A'
from dual
union
select 'B'
from dual
union
...
select 'Z'
from dual)
minus
select code
from table
-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set of values from a table
that match a given list of values -
select code
from table
where code in ('A','B','C','D','E')
I can get a result set of values from a table
that do not match a given list of values -
select code
from table
where code not in ('A','B','C','D','E')
So far so good.
Now, how do I get the set of values from the list
that do NOT have a matching value in the table?
I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.
Thanks in advance, folks.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Haas
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.com
--
Author: Nicoll, Iain \(Calanais\)
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: User / Synonym Question
-- 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.com -- Author: Toepke, Kevin M 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.com -- Author: Nicoll, Iain \(Calanais\) 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: SQL question
what are the 1700 values
if the are all alphabetic and not too long you could do something like the
below though it's all getting a bit long-winded
select
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
from addresses -- any table big enough
where rownum 26*26*26
group by
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
having
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26))) in
('ABA','ACY','ABT'...) -- the 1700 values
minus
select code
from table
-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set of values from a table
that match a given list of values -
select code
from table
where code in ('A','B','C','D','E')
I can get a result set of values from a table
that do not match a given list of values -
select code
from table
where code not in ('A','B','C','D','E')
So far so good.
Now, how do I get the set of values from the list
that do NOT have a matching value in the table?
I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.
Thanks in advance, folks.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Haas
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.com
--
Author: Nicoll, Iain \(Calanais\)
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: Why does my insert creates so many logs?
Doesn't have any triggers does it? -Original Message- Sent: Wednesday, September 18, 2002 8:39 PM To: Multiple recipients of list ORACLE-L Hi. A developer of mine is running a large insert as select: insert /* parallel hint */ into table A nologging (select * from table b where ...); There are no indices on table A and a PK disabled. Still that insert generates a large amount of logs. What could be the reason for that? Any ideas? Table A is not partitioned and has NOLOGGING attribute on the dba_tables set to Yes. thanks Gene __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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.com -- Author: Nicoll, Iain \(Calanais\) 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: sqlplus question
Bill couldn't you just concatenate the fields together with your delimiter between. e.g. select fld1||'|'||fld2||'|'||fld3 I think the trimspool is just trailing blanks so unless you made that the last field output you'll get this behaviour. Iain Nicoll -Original Message- Sent: Thursday, September 19, 2002 4:19 PM To: Multiple recipients of list ORACLE-L Howdy, I am spooling my sqlplus output to a file with no headings and all the fields separated by a delimiter. I have a field that is defined as varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes that and if you select length(fld1) from the table, you will get 4. But if I spool this to a file, I always get the full 56 bytes padded with blanks. In other words, I get 4 bytes of data and 52 blanks for that field. I only want the four valid bytes so that my delimiter comes immediately after that 4th byte. My sqlplus options are as follows: set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback off heading off trimspool on colsep | Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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.com -- Author: Nicoll, Iain \(Calanais\) 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: Suppressing a blank line in a union
Dan,
I think set recsep off might do the trick as because that particular record
wraps (because of the chr(10)) it inserts the blank line as default
behaviour.
This would cause you to lose the desired blank lines between the other
records though unless you added an extra chr(10) at the end. The other
alternative would be to split it into two selects.
Iain Nicoll
-Original Message-
Sent: Wednesday, September 18, 2002 1:13 AM
To: Multiple recipients of list ORACLE-L
I've got a nasty bit of sql using a union to provide a header line. SQL*Plus
likes to place a blank line between the output of the unions and I want to
get rid of it. I've done it before, but I have forgotten. I do recall that
we never found documentation on it and 'stumbled' across the solution.
The sql is below
TIA,
Dan Fink
column session_header format a1000
column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint
set linesize 1001 trimspool on trimout on
break on sort_col1 skip 3
select s.sid sort_col1,
1 sort_col2,
0 sort_col3,
'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname=
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||s.terminal||chr(10)||chr(9)||
'dbServer info'||chr(10)||chr(9)||chr(9)||
'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
'Program = '||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_header
from v$session s,
v$process p
where s.type != 'BACKGROUND'
and s.paddr = p.addr
union
select e.sid sort_col1,
2 sort_col2,
2 sort_col3,
'Wait Event Information '||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
3 sort_col2,
e.total_waits sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE
Client info
O/S user = scott
Machine Name = tiger
Terminal Name = unknown
dbServer info
O/S Process Id = 26276
O/S Username = oracle
Terminal Name = UNKNOWN
Program = mailto:oracle@tiger2 oracle@tiger2 (TNS V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event Information
Event (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
I
want to get rid of this line.
db file sequential read ( 27990 180
0)
log file sync ( 40902441
19)
db file scattered read( 3070 50
0)
latch free( 120 20
1)
direct path write (lob) (50 00
0)
async disk IO (40 00
0)
enqueue (30 93
8)
log file switch completion(10 44
4)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain \(Calanais\)
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: SQL Query tuning help
Dennis, If you use the ordered hint and have sa then so then am and also hint to use the index on sa(ret) then I think that would be about the best as you'd be starting with the best filter ie 1.3m/281 giving less than 5000 on average (assuming ret is indexed). I don't know if you'd have to through in an use_nl also. Iain Nicoll -Original Message- Sent: Tuesday, September 10, 2002 8:19 PM To: Multiple recipients of list ORACLE-L I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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: Must Read for Every Developer and DBA
I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna 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: Nicoll, Iain \(Calanais\) 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: Inserts are taking time !
Marul, Are there any bitmapped indexes on the table Iain Nicoll -Original Message- Sent: Wednesday, September 04, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Marul, 10k records in 1 hour(3600 seconds) 1 record in 3600/1 = approx 0.36 seconds If your application is OLTP you'll be inserting records 1 by 1 rather than in bulk. Which means the effect will hardly be noticed. If you are going to insert record in bulk you can DROP and then recreate the indexes after load. Check what takes more time. See if there is any scope of partitioning the table, to use local partitioned indexes. For bulk load, disabling the constraints is also an option. Naveen -Original Message- Sent: Wednesday, September 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:33 PM Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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: Nicoll, Iain \(Calanais\) 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
RE: Disabling indexes - temporarily
Could you have a trigger which before insert, inserts into another empty table with exactly same layout but rejects the insert on the main table. Then disables the trigger and adds these at a non-busy stage and reenables the trigger. Would be a whole lot quicker if it's possible. -Original Message- Sent: Monday, September 02, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Thanks Naveen, Lets forget about the statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Saturday, August 31, 2002 11:58 PM Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexes from getting updated, it won't increase the performance by a noticable amount. Secondly, there is no way(as far as i know) to make the indexes READ-ONLY with the table in READ-WRITE mode. Thirdly, rebuilding 20 indexes on a table with 1 million record will take a long time, in comparison updation by 100-400 records is nothing. It neither feasible nor advisable. Naveen -Original Message- Sent: Saturday, August 31, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Hi all, Need to know if the following is possible in Oracle(any version):- I have a table of around (a) 30 Columns (b) 20 out of 30 are indexed (c) around 1 million (1,000,000) records. Most of the time there will be heavy reads (select queries) on this table except for some 100-400 records to be inserted in a day. The newly inserted records will not be selected by the queries for the next 24 hours (this is based on some business logic), thats for sure. Now the problem is when ever a record(s) is inserted the entire bunch of indexes is updated/rebuild by the Oracle which considerably slows down the throughput of the system during that period of time (until all indexes are updated). Can we have a solution whereby indexes should not be updated when a record(s) is inserted, because I know that these records will not be the part of the query for the next 24 hrs. The indexes will be re-built manually/scheduled during the off-peak hours once a day. In this way, the next day, new records inserted a day before will be ready to be fetched by the queries. Note- I can't put my indexes offline not for a single minute during peak hours. Any clues? TIA, Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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: Problem in creating DB Link
Aleem, Cany you tnsping the abc database from the server which the database with the link resides on. Can you do a select * from dba_db_links and see if you can see the link and the owner. Also can you advise exactly how you are doing the drop and from which user? Iain Nicoll -Original Message- Sent: Monday, August 26, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Hi, I have created a Database Link under the scenario detailed below: but when I try to access tables through the link it gives error ORA-02019: connection description for remote database not found. If I try to drop the link it gives error ORA-02024: database link not found TIA! Aleem This is the scenario: We have two db servers running on our LAN, for simplicity 'A', connect string 'abc' and 'B', connect string 'xyz'. Some of the tables on A in schema UserA1 are required (read only) by schema UserB1 on server B. As suggested by someone I tried to create a dblink. Using SQL*Plus connected as 'System' to Server B (since it requires to access tables from the other db) and applied the following command. CREATE SHARED PUBLIC DATABASE Link my_link CONNECT TO UserA1 IDENTIFIED BY abc AUTHENTICATED BY UserB1 IDENTIFIED BY def USING 'abc' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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: Nicoll, Iain \(Calanais\) 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: To_Char Problem
Why do you need the to_char? But wouldn't it work anyway with to_char(a.updated_date,'dd/mm/ hh24:mi:ss') = ( select to_char(max(updated_date),'dd/mm/ hh24:mi:ss')) which will also use the 24 hour clock instead of to_char(a.updated_date,'dd/mm/ hh:mi:ss') = ( select max(to_char(updated_date,'dd/mm/ hh:mi:ss')) Iain Nicoll -Original Message- Sent: Wednesday, August 14, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Hi All, I am using the to_char function in the following query. But it treats the date '31/12/2001' as greater than '01/01/2002'. Is there any solution to fix this problem? select distinct(a.default_type_id), a.new_val from amend_default_value a, amend_default_value b where a.effective_from = sysdate and a.effective_to= sysdate and a.group_id = '942' and a.default_type_id = b.default_type_id and to_char(a.updated_date,'dd/mm/ hh:mi:ss') = ( select max(to_char(updated_date,'dd/mm/ hh:mi:ss')) from amend_default_value c where c.effective_from = sysdate and c.effective_to= sysdate and c.group_id = '942' and c.default_type_id = b.default_type_id); Thanks in advance. regards, Karthik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: karthikeyan S 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: Nicoll, Iain (Calanais) 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: TRANSLATE
As in the below
select translate('testdata,''',',''',' ') from dual
but they need to be replaced with something like a space.
Iain Nicoll
-Original Message-
Sent: Wednesday, July 31, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
Hi,
how can i use TRANSLATE to take out commas(,)single
quotes(') and double quotes() from a string ?
Cheers
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Imran Ashraf
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: Nicoll, Iain (Calanais)
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: TRANSLATE
try select translate(string,','''||chr(13),'XYZ ') which should replace carriage return with a space. Iain Nicoll -Original Message- Sent: Wednesday, July 31, 2002 6:51 PM To: Multiple recipients of list ORACLE-L thanks for that everyone 1 more thing.. how can i get rid of Return characters? The problem i have.. i have a comments field on a form, i am exporting this(using text_io) into .csv, so where a user has pressed enter in the comments field, when moving that to csv it adds new line for every Return..cauising a big mess cheers --- Ramasubramanian, Shankar (Cognizant) [EMAIL PROTECTED] wrote: Hi, select translate(string,',''','XYZ') from Dual Only for single quote you need the escape character (another single quote).The above query changes the comma character to X , Double quote character to Y and single quote character to Z. Regards, Shankar This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf 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: Nicoll, Iain (Calanais) 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: Index performance
Also do you know what is the most selective part of the query. If you know
that one index will bring back the fewest rows then try hinting to use it.
-Original Message-
Sent: Tuesday, July 23, 2002 5:59 PM
To: Multiple recipients of list ORACLE-L
why do you think hitting the indexes is a bad thing? what is the
performance of this query? What's the explain plan?
--- Seema Singh [EMAIL PROTECTED] wrote:
Hi
I am executing following query adn this query hits a number of
indices on
this table.let me know what is wrong please.all in where clause are
having
indexes.
select name,last_access, reg_date from empmaster where emp_id100
and reg_dateto_date('2001-01-01','-MM-DD') and
emp_st='valid' and last_accessto_date ('2001-01-01','-MM-DD')
and emp_status='S' and match='FIRST'
Here all conditions in where clause are having indexes.
How to rewrite this query.
The primary key is emp_id.
Thanks
-Seema
_
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
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: FW: bind vars change explain plan
I thought it was simply that with values the optimizer could look at the histograms to see if data was skewed, whereas it couldn't with bind variables. So the index may not have too many distinct values but the values you were supplying had less than their fair share of records. I'd guess that someone at some point has done a analyze table for all indexes (or something similar) on the particular table. I'd also tend to agree though that it's better to hint the index rather than use values (if that is in your control) -Original Message- Sent: Monday, July 22, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Barbara, The path that the optimizer chooses is based on what values are bound into the variables, but also on what information it has in the data dictionary. If those particular tables/indexes have not been analyzed recently then the optimizer will make wrong decisions. Also init parameters like db_file_multiblock_read_count can prejudice the optimizer to a particular path over others. I would not so much blame the use of bind variables before looking at the data dictionary entries for the table/indexes and the init.ora file. And yes, the optimizer in 7.x was flaky, at best. Dick Goulet Reply Separator Author: Baker; Barbara [EMAIL PROTECTED] Date: 7/22/2002 7:08 AM prem. Never did get an answer to this question. I don't know why using bind variables changed the execution path. My best guess comes from the developer. She thinks that when we supplied the values, the optimizer knew what the range of values would be, and could therefore determine to use the index. With the bind variable, the optimizer did not have a range of values to work with and therefore did not choose the index in the execution path. I have no knowledge that using bind variables will suppress indexes. Just happened that it did in this case. Also keep in mind that this particular database is using an old version of Oracle (7.3.4). Optimizer got much better in version 8. The list helped me out with a work-around, which was to index-hint the index I wanted. Bind variables are definitely good guys. I highly recommend you continue with your code changes to include binds. Good luck. Barb -- From: oraora oraora[SMTP:[EMAIL PROTECTED]] Reply To: oraora oraora Sent: Sunday, July 21, 2002 8:24 PM To: [EMAIL PROTECTED] Subject: Re: bind vars change explain plan Baker, sorry i did not read the reply to ur query. what was the reply ? will using bind vars suppress index ? kindly let me know b'coz i have also changed my code to SQL with bind vars just now. Regards, prem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Nicoll, Iain (Calanais) 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: Yesterday's date - unix question
Vladimir, Found this on the net (can't recall where though) for touching a file with yesterdays date. touch `TZ=GB+24 date +%m%d%H%M%y` filename You'd need to change the GB to what is appropiate for you and change the format variables to the format you wish eg echo `TZ=GB+24 date +%y%m%d` to get 020714 yymmdd HTH Iain Nicoll -Original Message- Sent: Monday, July 15, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Good morning / afternoon / evening... How to find yesterday's date in unix? Yes, I know how to find it by using env. variables, sql*plus, redirecting output and so on... But I need pure unix solution. Thanks, Vladimir Barac -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Barac - posao 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: Nicoll, Iain (Calanais) 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: When was analyze run last?
Not there on 7.3.4.4. It seems that the only clue you get is in dba_tab_columns last_analyzed and that won't tell you specifically when an index was last analyzed. Iain Nicoll -Original Message- Sent: Monday, July 08, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Hi Smith, check LAST_ANALYZED column of DBA_INDEXES or DBA_TABLES I believe it shouldb be there in ver. 7.3. HTH Arul. -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: 08 July 2002 17:33 To: Multiple recipients of list ORACLE-L Subject: When was analyze run last? If there a way in 7.3.4 to tell the last time analyze was run on an index? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: Nicoll, Iain (Calanais) 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 query
Roland, This has just been covered in the past day or two. In this case the best solution, where a straight minus doesn't look applicable is the rather elegant (sorry I've forgotten whose it was) construct below. select a.id, a.name from table1 a, table2 b where a.id = b.id(+) and b.id is null Iain Nicoll -Original Message- Sent: Wednesday, June 26, 2002 2:13 PM To: Multiple recipients of list ORACLE-L Hallo, anyone whom can give me an example on how to pick out field id and name from table one and get only the id's that exists in table one doesnt exist in table2. 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: Nicoll, Iain (Calanais) 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: packages procedures
Charlie, Courtesy of TOAD (I'm too lazy to not use it) try looking at all_arguments which seems to have at least a large part of what you need. Iain Nicoll -Original Message- Sent: Wednesday, June 19, 2002 8:34 PM To: Multiple recipients of list ORACLE-L I'm having a senior moment need somebody to refresh my memory. I know that what I want to do CAN be done, I just don't remember how. I'd like to RTFM, but I can't find the right FM which contains the answer. I want to know the name(s) of (public?) procedures/functions contained within a package and what are the argument number and datatype for each procedure/function. So what do I query to obtain this information? -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 There are no stupid questions, but there are many inquisitive idiots! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Nicoll, Iain (Calanais) 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: Permissions on user trace files
_trace_files_public = true in init.ora Iain Nicoll -Original Message- Sent: Wednesday, June 05, 2002 8:55 PM To: Multiple recipients of list ORACLE-L Hi all, User Trace files are currently created as -rw-r- Is there an easy way to change the permissions when they are created to -rw-r--r-- The developers would like to be able to run Sql Trace on queries on the development box and then run tkprof on the resulting file. I'm perfectly happy giving them permission to do so, since it means I won't need to run it for them several times a day. I'm on Solaris 2.6, Oracle 8.1.7.2 TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Nicoll, Iain (Calanais) 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: Statistical sampling and representative stats collection
Have used them on a for all indexed columns basis and they make a massive difference on heavily skewed data - particularly a sort of waiting to be processed flag which only has about 5 distinct values but the ones we want to pick will make up only about 0.01%. Haven't used them on all columns as we don't often filter on non-indexed columns. Iain Nicoll -Original Message- Sent: Wednesday, May 29, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Based on the scarcity of previous responses to emails on this list, it seems that histograms are not that widely used throughout the industry. I'm not sure why. I've used them in the past, but only in very specific instances and certainly not for all tables/columns. Sometimes just 1 or 2, sometimes 15 or 20. And only in those cases where needed. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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: Nicoll, Iain (Calanais) 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 Question
What about select commission_id, replace(com_text_msg,'~',chr(9)) from tab1 which would work if going to a tab separated file for something like excel. Whats wrong with substr/instr? Iain Nicoll -Original Message- Sent: Wednesday, May 29, 2002 7:22 PM To: Multiple recipients of list ORACLE-L I need some help... The database table has following structure. commision_id number com_text_msg varchar2(500) The second column contains data fields that are delimited by ~ and delimiter's position varies. But there are only eight data fields in the column. Is there a way in SQL, other than substr/instr combinations, to extract each data field to report? Thanks. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: host from SQL prompt
See the following link http://soi3.mmtel.ru/books/oracle8-how-to/chap1_11.html http://soi3.mmtel.ru/books/oracle8-how-to/chap1_11.html which gives some details on using product_user_profile. I'm assuming it'll be the same for higher versions. Iain Nicoll -Original Message- Sent: Tuesday, May 21, 2002 5:39 PM To: Multiple recipients of list ORACLE-L How can I prevent users from typing host to get to an OS prompt while logged into a DB account with an OS account? They can host to a prompt now, but they can't do anything useful from there, since $ORACLE_HOME is the only thing in their PATH...just wondered if there is an easy way to prevent the use of the host command all together. TIA! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
PLS-908
Can't see anything on metalink regarding this error over a database link. We have an 8.1.6 database trying to execute a package on a 7.3.2.3 database and it's giving the following error. Any solution other than upgrade? ORA-04052: error occurred when looking up remote object TEST1.TEST_PKG@SANDBOX ORA-06541: PL/SQL: compilation error - compilation aborted ORA-06553: PLS-908: The stored format of TEST1.TEST_PKG@SANDBOX is not supported by this release TIA Iain Nicoll Test and Release De-Regulated Services Internal : 700 2331 External : 0141 568 2331 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Numeric comparison in DECODE statement
I think in this case you're suppose to use sign as decode was only meant to
deal with specific values
e.g.
SELECT DECODE(SIGN(:in_value - 60),1,TO_CHAR(:in_value/60, '9.99')||'
hours',
-1,TO_CHAR(TO_CHAR(:in_value))||' minutes',
TO_CHAR(TO_CHAR(:in_value))||' minutes')
FROM dual
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 4:08 PM
To: Multiple recipients of list ORACLE-L
I was wondering if anyone might have tried this before, because I can't seem
to get it to work. I'd like to be able to determine which unit of measure
to concatenate to a value by using a decode statement in the query. I have
a column in the database that stores time in minutes, and I'd like to be
able to show the output in minutes if the value is less than 60, but in
hours (such as 3.27 hours) if the value is greater than 60. So far I've
tried the following statement, but it seems to be blowing up on the first
comparison operator:
SELECT decode(in_value, to_char(to_number(in_value) = to_number('60')),
to_char(in_value)||' minutes', to_char(to_number(in_value)
to_number('60')), to_char(in_value/60, '9.99')||' hours')
FROM dual
/
I'm selecting from dual just until I can get the query working at all. Is
what I'm trying to do even possible? Any help or ideas would be greatly
appreciated. Thanks in advance.
_YEX_
/*
|| Robert D. Yexley
|| Oracle Programmer/Analyst
|| Easylink Services Corporation
|| Professional Services
|| Contractor - Wright Research Site MIS
|| Det-1 AFRL/WSI Bldg. 45 Rm. 062
|| (937) 255-1984
|| [EMAIL PROTECTED]
|| )))
*/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yexley Robert D Contr Det 1 AFRL/WSI
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: Nicoll, Iain (Calanais)
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: Do you ever have days where you dont want to think ?
I'm not sure an index would ever be used with not in (in seems to be bad enough). Not exists would probably be quicker though it'd probably be reasonable still to do a full table scan of a. Personally I prefer the likes of minus though it'd be a bit convoluted here e.g. select a.f1, a.f2, a.f3, a.f4 from a, (select a.f1 from table1 a minus select b.n1 from b) s where a.f1 = s.f1 -Original Message- Sent: Friday, May 17, 2002 10:23 PM To: Multiple recipients of list ORACLE-L I just just wanna go lie on a beach naked on some remote island far far away and not think of anything for a month. Here is the issue. I have a query that looks like this ... select a.f1, a.f2, a.f3, a.f4 from table1 a where a.f1 not in ( select b.n1 from b ); there is a primary key index on b.n1 there is a concatenated primary key index on a.f1,a.f2,a.f3 there is a non-unique index on a.f1 the query shows that the index is being used on table b, but no indexes are being used on table a. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael 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: Nicoll, Iain (Calanais) 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: column level grants
Andrey, Having difficulty getting access to the 8i docs just now but the below is an excerpt from the Oracle 7 docs. To grant BLAKE the REFERENCES privilege on the EMPNO column and the UPDATE privilege on the EMPNO, SAL, and COMM columns of the EMP table in the schema SCOTT, issue the following statement: GRANT REFERENCES (empno), UPDATE (empno, sal, comm) ON scott.emp TO blake -Original Message- Sent: Wednesday, April 24, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Constraints
Roland have you tried a select butiks_nr, count(*) from pbk.k1 group by butiks_nr having count(*) 1 to check there really are no duplicates -Original Message- Sent: Wednesday, April 24, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? 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: Nicoll, Iain (Calanais) 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: Recompiling Invalid Objects after Table Rename
Can't you just do a variant of
SET HEAD OFF TERMOUT OFF ECHO OFF
select 'alter '||decode(object_type,'PACKAGE BODY',' PACKAGE
',object_type)||
' '||object_name||' compile '||
decode(object_type,'PACKAGE BODY','BODY ','')||';'
from user_objects
where object_type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION',
'VIEW','TRIGGER')
and status != 'VALID'
spool recomp.sql
/
spool off
SET HEAD ON TERMOUT ON ECHO ON
start recomp
which will attempt to recompile anything invalid.
Option 1 wouldn't necessarily ever work as I believe recompilation only
happens when something is called directly i.e. if the user calls a package
which in turn calls an invalid package then recompilation would not happen
(apologies if I'm wrong)
Iain Nicoll
-Original Message-
Sent: Monday, April 15, 2002 1:23 PM
To: Multiple recipients of list ORACLE-L
Hi all,
We have an application which deletes a large number of rows from a
table. It would be faster to simply insert the rows that we want to keep
into a second table, drop the original table and then rename the second
table to that of the one we have just dropped.
The only downside that I can see is that all the source objects which
reference the original table become invalid.
We could:
1. Simply allow the source objects to be recompiled naturally overtime
as they are reused (but with the possibility of a large number of invalid
objects at any one time in the database and little control over when
compilation is done).
2. Force recompilation following the drop table. However this would
require logging all objects which would need recompilation. This is an
additional step for any new development and would therefore the list of
object would be prone to become inaccurate over time. (Could maybe do this
automatically using USER_REFERENCES prior to the drop table? - still seems
a bit clumsy)
Does anyone have any comments on doing this?
Many thanks
- Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
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: Get the Latest Date
Gavin, select username, max(access_time) last_access_time, count(*) no_of_logins from session_info_table group by username should do it Iain Nicoll -Original Message- Sent: Friday, April 12, 2002 11:43 AM To: Multiple recipients of list ORACLE-L Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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: Nicoll, Iain (Calanais) 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: subtract minute from date/time
date_fld - 1/1440 Iain Nicoll Test and Release De-Regulated Services Internal : 700 2331 External : 0141 568 2331 -Original Message- Sent: Thursday, April 11, 2002 12:08 PM To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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 Tuning - How to avoid TOCHAR function against a date
Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = trunc(:b1) and oracle_date trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- 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: Nicoll, Iain (Calanais) 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 again
Roland, I'm afraid I've already deleted the original e-mail so I can't check but it may be you have to handle the BORTTAGS_FLAGG field if it is an empty string (or null). Iain Nicoll -Original Message- Sent: Thursday, April 04, 2002 5:04 PM To: Multiple recipients of list ORACLE-L I sent the wrong pl/sql code in last message. I want the field BORTTAGS_FLAGG to be inserted in prisregister_kopia_wed. (See attached file: regicarol.txt) What is missing? Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Trigger question
Roland, First thing would be that as it is an on insert trigger the OLD reference is not valid (only valid for update and delete I think) though I'm surprised that it accepts the referencing old as old part. Iain Nicoll -Original Message- Sent: Wednesday, April 03, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Hallo, What is wrong with this trigger: I want the trigger to fire(to run the statement : UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); after new record is inserted in this table. The thing to happen should be this: I want that script to be run on the same record that has been inserted in the table. So the UPPer command should run on the namn field that has been inserted in the table. Hope anyone can´help me. CREATE OR REPLACE TRIGGER AFTER_INSERT_ROWins_ON_test after insert ON test REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.namn :=UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); -- :NEW.namn := UPPER(SUBSTR(:OLD.namn, 1, 1) || SUBSTR(:OLD.namn), 2); EXCEPTION WHEN OTHERS THEN raise_application_error(-2, 'ERROR IN TRIGGER AFTER_INSERT_ROWins_ON_test: ' || SQLERRM); END; / Thanks in advance Roland S -- 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: Nicoll, Iain (Calanais) 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: Long running SQL Problem?
Should be better with select col1, col2 from table_1 minus select col3, col4 from table2 Iain Nicoll -Original Message- Sent: Wednesday, March 27, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Make first character Versal
Roland, Substr starts with 1 eg select (substr (namn,1,1)) from test will give you the 1 character starting from the first character. Unfortunately I don't know what you mean by versal. Iain Nicoll -Original Message- Sent: Wednesday, March 27, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Hallo, I know this question sound a bit simple, but can anyone give me a select statement which make the first character of the word in a field Would appreciate very much. I have checked the manual but cant get it right. I am starting with this sql statement: select (substr (namn,0,1)) from test to pick outthe first character and I want that first character to be a VERSAL. 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: Nicoll, Iain (Calanais) 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: Customize my SQLPlus login
Dave, It just needs carriage return at the end of the last line. Iain Nicoll -Original Message- Sent: Tuesday, March 26, 2002 4:18 PM To: Multiple recipients of list ORACLE-L I have added some customizations to my glogin.sql. When I start a session of SQLPlus I get this; Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Input truncated to 13 characters SQL show pagesize pagesize 250 I can see that my changes took place but what is the Input truncated to 13 characters about?? Thanks, Dave -Original Message- Sent: Tuesday, March 26, 2002 8:29 AM To: Multiple recipients of list ORACLE-L login.sql and glogin.sql glogin.sql will be global, from wherever you start your sqlplus session, login.sql is run from your current directory so if you have changed directories it won't be run --- Farnsworth, Dave [EMAIL PROTECTED] wrote: What is the file that I need to edit on my client PC to set personalized settings for SQLPlus so that I do not have to set these at the command prompt every time I start a new session? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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 queries
Lee, The +0 looks bizarrely like the old trick to stop indexes being used though it would appear that here you wouldn't want to do this. Have you got the explain plans and what version is it? Iain Nicoll -Original Message- Sent: Tuesday, March 12, 2002 4:24 PM To: Multiple recipients of list ORACLE-L All, Following SQL runs for ages (almost 2 hours) select * from table1 addr, table2 pers, table3 lookup table4 cust where cust.customer_key = lookup_customer_key and lookup_address_key = addr_address_key and lookup.person_key = pers.person_key and rownum 1000; when this is changed to select /*+ FIRST_ROWS */ ADDR.*, PERS.*, LOOKUP.*, CUST.* from table4 cust, table2 pers, table3 lookup table1 addr where cust.customer_key = lookup.customer_key + 0 and lookup.address_key = addr.address_key and pers.person_key = lookup.person_key + 0 and rownum 1000; this runs instantaneously. I realise that 99.99% of the improvement is down to the first_rows hint BUT, why does the SQL tool use the list of table aliases with .* after it AND what on earth are the + 0s' on two lines of the predicate list. Confused Lee PS. The Tool is SQLExpert brought to you by those nice blokes at cool-tools (Cheers Mark Leith !!) and is proving absolutely priceless here at the moment. TIA The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: question on EXPLAIN_PLAN
Kevin, Have you tried it with autotrace on to see the number of reads etc? Iain -Original Message- Sent: Wednesday, February 06, 2002 10:09 PM To: Multiple recipients of list ORACLE-L Yes, Mike, I analyzed the table and PK index on the two databases at the same way, it seems that there is something wrong with the PK index, the select count(*) from table_name query took 4 seconds, and only 335199 rows atcually. it use fast_full_index scan of the PK index, and I re-created the PK index, same thing. that's very bad. it is Oracle 8.1.6 on win2000. thanks for reply. Kevin Wang - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 1:55 PM Kevin, Have you analyzed the tables on both databases? Card is the CBO's estimate of the number of rows it will process. Mike From: kevin wang [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: question on EXPLAIN_PLAN Date: Wed, 06 Feb 2002 11:43:38 -0800 Hi, guys The problem belows is really make me confused and gave me big trouble, is there someone can give me some hlep? I have two databses, same version(oracle 8.1.6),same O/S(win2000), same schema structure, different data(but small difference of size). and even exactly same explain_plan of my sql query. But on one database, the cardinality of one PK index access upon one table is 27(cost=2,card=27,bytes=756) (table rows 263758) and the other is 11706 (cost=3,card=11706,bytes=199002)( table rows 351173). so, on one DB the sql query took 300ms, one the other, it took 5 seconds! Any advise is highly appreciated. thanks, Kevin Wang Database Administrator Vivonet Canada Inc. _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough 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: kevin wang 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: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Performance Question
How many rows are you bringing back from a typical query, how good is the best filter condition and are you filtering that first before joining to the other tables in the explain plan? What is the query and explain plan? Iain Nicoll -Original Message- Sent: Tuesday, January 29, 2002 4:20 PM To: Multiple recipients of list ORACLE-L You have done very nicely so far in tuning those joins... but... More questions to you : 1. Why do you think this is still a database problem? 2. Will partitioning those larger tables help the queries? 3. What have you checked to make sure that the bottleneck is not the web-server? 4. Are these connections persistent or the app connects/disconnects when accessing the database? Looking for answers to these questions may help you locate other opportunities to improve upon.. - Kirti -Original Message- Sent: Tuesday, January 29, 2002 9:25 AM To: Multiple recipients of list ORACLE-L Hello all: We have an application that is having slow response time against an 8i database, I would like to improve the response time. This is a web based application accessing the database with about 2000 users. Most of the application queries are based on complex joins on 4 big tables with each having over 5 million rows( biggest table has 18 million rows). I have tuned the Package queries for the best explain plan possible, but still do not seem to make dramatic change in the response time. Though, I was able to make significant headway tuning these packages by bringing down response times from 7 minutes to under 3 minutes. But this is not an acceptable response time from a web-application perspective. I am considering creating data cubes based on the most frequently used join conditions and pre-populate them on a nightly basis or use triggers to update the cube simultaneously. I am hoping that this enhance the response times. I would greatly appreciate your suggestions or opinions on this idea. If you have an alternative/better way of achiving this please enlighten me. Thank very much. Srini Rajendran. -- 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Inserting raw ascii into a varchar2 field
I think the only sure non-printable characters are those of less than 32, on or above 32 and they may well be printable depending on the character set. I'm not sure what you're looking for example wise as it should just be a case of using chr(asciicode) to do the insert. If you have more than 255 objects in your database you could try select rownum-1 ascii_code, chr(rownum-1) character from dba_objects where rownum 256 to get an indication of what's printable It's also arguable that BS, TAB, LF and CR (chr(8), chr(9), chr(10)? and chr(13)) are all printable but just have nothing seen. To check whether your data contains unprintable characters you could try adapting the code below SELECT * FROM table_name WHERE filed_name != TRANSLATE(field_name,CHR(0)||CHR(1)||CHR(2)||CHR(3)||CHR(4)||CHR(5)||CHR(6)| | CHR(7)||CHR(8)||CHR(9)||CHR(10)||CHR(11)||CHR(12)||CHR(13)|| CHR(14)||CHR(15)||CHR(16)||CHR(17)||CHR(18)||CHR(19)||CHR(20)|| CHR(21)||CHR(22)||CHR(23)||CHR(24)||CHR(25)||CHR(26)||CHR(27)|| CHR(28)||CHR(29)||CHR(30)||CHR(31),'') Cheers Iain Nicoll -Original Message- Sent: Thursday, January 10, 2002 9:10 PM To: Multiple recipients of list ORACLE-L Hello, Does anyone have an example of how to insert raw ascii into a varchar2 field? For example, CREATE TABLE LH_test ( col1 varchar2(10), col2 varchar2(10), col3 varchar2(10) ) PCTFREE 0 PCTUSED 80 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 5M NEXT 5M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE REGDAT ; commit ; insert into lh_test values ( 'X'||chr(9), 'Y'||chr(A), 'Z'||chr(D) ) ; Why am I doing this? Because some non-printable ascii codes have been inserted in some fields and I am tasked with finding the bad data. I need a test bed to insure I can scan for ranges of ascii characters, and need a range of known ascii printable and non-printable characters in a test table. The bad data can be in over 200 fields, so I need a broad tool; I'll gen the select statements after I have some test data to work with. Any suggestions or referrals are appreciated. Regards, Linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
CASE WHEN or DECODE - any efficiency differences
I've just been asked whether there are any efficiency differences between CASE WHEN and DECODE. I'd imagined that they would use the same underlying code but perhaps not. Does anyone know which is more efficient (I realise that CASE is SQL-92 compliant and allows use of IN but excluding this is there any efficiency difference) Cheers Iain Nicoll -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Problem Setting Up User
Maybe I'm reading it wrong but with the syntax of alter user xxx default role all except role [,role]... and alter user xxx default role all role [,role]... according to my book then any combination of none, one, n, all would be possible. Iain Nicoll Duhveloper (I won't surprise anyone here if I'm wrong then) -Original Message- Sent: Thursday, January 03, 2002 6:38 PM To: Multiple recipients of list ORACLE-L Not true. Only one role can be the default role, if specified. If not specified then ALL are default. That is the only time! Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 03, 2002 12:30 PM To: Multiple recipients of list ORACLE-L Subject:Re: Problem Setting Up User I beg to differ. A default role is simply one that is automatically enabled (or set) upon login. Any or all of a user's roles may be default (see the definition of user_role_privs). May I suggest, Rachel my friend, that *you* RTFM? :-D --- Rachel Carmichael [EMAIL PROTECTED] wrote: third, you can only have ONE default role. Think about it. If you really want the user to have the privs of several roles at once, create another role, a superrole that is granted both DB and RESOURCE and the grant that one as default. And you really do have to RTFM __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: optimizer_mode=choose uses first_rows, or all_rows?
I believe it's all_rows, though I can't find the reference to justify this at the minute. Cheers Iain Nicoll -Original Message- Sent: Monday, December 17, 2001 2:15 PM To: Multiple recipients of list ORACLE-L A developer asked me this question. Any idea which mode the CBO defaults to when stats exist on objects? TIA Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Inserstatement
I hate to disagree but why couldn't you update x set field = (select field1 from p where p.join_field = x.join_field) where conditions Iain Nicoll -Original Message- Sent: Tuesday, November 27, 2001 12:45 PM To: Multiple recipients of list ORACLE-L You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Nicoll, Iain (Calanais) 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 question
Fawzia, You should be able to use mod(field,1) to get the remainder of the number divided by 1, which should be the last four digits. Cheers Iain Nicoll -Original Message- Sent: Wednesday, November 21, 2001 4:04 PM To: Multiple recipients of list ORACLE-L Hi, Can you tell me if its possible to write some sql to change some data. Basically I need to run a scritp to change data of column: id from 12345678 to the last four digits. Is this possible to do in sql/plsql?? Any advice/hints would be greatly appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 7 password security
Does anyone know of a way of implementing password ageing/standards etc in Oracle 7 other than through third-party products or have experience of any third-party products which do this. Unfortunately Oracle 8 is not an option. Cheers Iain Nicoll -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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-important
Roland, If it is only 'No Info' that you want to keep then the below should do SELECT decode(field1,'No Info','No Info', ltrim(substr(mxurval_namn,instr(mxurval_namn,' ' FROM mxurval; Cheers Iain Nicoll -Original Message- Sent: 14 November 2001 10:00 To: Multiple recipients of list ORACLE-L Hallo, How can I do a select statement that creates this: I have the field1 Jimmy Y1000 Timmy L3 No Info and I want the select to give me this: Jimmy Timmy No Info You see It should still be 'No info' after the select statement. How can I change this statement? SELECT ltrim(substr(mxurval_namn,instr(mxurval_namn,' '))) FROM mxurval; Thanks in advance Roland S -- 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: Nicoll, Iain (Calanais) 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: Last day of the month unix
Not sure if you want this from sql or unix but there is the last_day function eg select last_day(sysdate) from dual to get the last date or select to_char(last_day(sysdate),'dd') from dual to get the day portion only. -Original Message- Sent: 29 October 2001 15:55 To: Multiple recipients of list ORACLE-L Try this: select trunc(add_months(sysdate,1),'MM')-1 from dual; Jared On Monday 29 October 2001 00:00, Sinard Xing wrote: Hi, Is there any function that can display out last day of the month for example lastday(Oct,2001) return me 31 Or a variable that store this value. Is Date an object in unix ? Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Nicoll, Iain (Calanais) 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: Last day of the month unix
And personally I prefer your solution as it shows a bit of thought. :) Iain Nicoll -Original Message- Sent: 29 October 2001 20:00 To: Multiple recipients of list ORACLE-L But my version was so much more obtuse1 :) Jared Nicoll, Iain (Calanais)To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] iain.nicoll@cal cc: anais.com Subject: RE: Last day of the month unix Sent by: [EMAIL PROTECTED] 10/29/01 09:25 AM Please respond to ORACLE-L Not sure if you want this from sql or unix but there is the last_day function eg select last_day(sysdate) from dual to get the last date or select to_char(last_day(sysdate),'dd') from dual to get the day portion only. -Original Message- Sent: 29 October 2001 15:55 To: Multiple recipients of list ORACLE-L Try this: select trunc(add_months(sysdate,1),'MM')-1 from dual; Jared On Monday 29 October 2001 00:00, Sinard Xing wrote: Hi, Is there any function that can display out last day of the month for example lastday(Oct,2001) return me 31 Or a variable that store this value. Is Date an object in unix ? Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Nicoll, Iain (Calanais) 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: Nicoll, Iain (Calanais) 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 not exists
select id from table1 minus select id from table2 -Original Message- Sent: 25 October 2001 09:35 To: Multiple recipients of list ORACLE-L Hallo you DBA' Can anyone give me a good example on a sql select statement checking which ids exists in table one but not in table two? Table two also contains the corresponding id field but with other field names besides. Like this Table 1: Id Name Year Table 2: :Id City Country Thanks in advance Roland Sköldblom -- 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: Nicoll, Iain (Calanais) 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).
CTAS use of rollback
Could anyone tell me whether Create table .. as select .. uses rollback. I initially thought it would (despite being a cross between ddl and dml) but having created a 3.5 million row table and checked the sum of the writes in v$rollstat it had only done ~130k writes between the start of the ctas and the end. It also doesn't create the table initially but just has a numbered object which it seems to rename only at the very end, so if it fails I would have though it would just drop that object and if it completes successfully then a commit would be done because of the ddl aspects of the command. I tried inserting 10k rows into the same table and this came back with about 25k writes (seemed reasonable if it's only storing the rowid). Given this it doesn't seem to be using rollback (other than recording changes to extents etc) but I'd appreciate confirmation. Iain Nicoll -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Monitoring CPU per session
Try setting timed_statistics to on (init.ora) Iain Nicoll -Original Message- Sent: 03 October 2001 11:41 To: Multiple recipients of list ORACLE-L Hello I'm trying to figure out which sessions use most CPU time. Oracle manual gave me this sql query: SELECT v.SID, SUBSTR(s.NAME,1,30) Statistic, v.VALUE FROM V$STATNAME s, V$SESSTAT v WHERE s.NAME = 'CPU used by this session' AND v.STATISTIC# = s.STATISTIC# .. but the value of 'CPU used by this session' is always 0 for all sessions. So my question is do I have to do something special to monitor CPU resourses? The technical details about my environment: OS: Windows 2000 Advanced Server Oracle 8.1.7.1.1 Standard Edition The server has 4 processes of which the Oracle process is allowed to use 3... The manual gave me another question Regards /Jonas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonas A Wetterberg 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: Nicoll, Iain (Calanais) 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: Meaning of V$WAITSTAT statistics
Can't remember where this came from but the following events can be safely ignored. Sorry it's just a starter but my understanding of this isn't what it should be. Iain Nicoll client message SQL*Net message from client SQL*Net more data from client rdbms ipc message pipe get Null event pmon timer smon timer parallel query dequeue -Original Message- Sent: 02 October 2001 23:50 To: Multiple recipients of list ORACLE-L Yes, you are right, but my pupils... they WANT TO KNOW THE MEANING... From: Greg Moore [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Meaning of V$WAITSTAT statistics Date: Tue, 02 Oct 2001 14:13:38 -0800 The Oracle 8i Reference manual Appendix A While it describes the wait events, it doesn't tell you which ones you can safely ignore when using waits for tuning. Even if you know which ones to focus on, the descriptions are pretty inadequate. We are informed that a db file sequential read means the session is waiting while a sequential read from the database is being performed. On the other hand, a db file scattered read is similar to db file sequential read, except that a session is reading multiple data blocks. Worse, suppose one of these two waits is twice as significant in my database than the other. Does that mean I have a problem and should start using the wait interface methodologies to track down the SQL that's causing the wait event to be high? Or is it normal that in a well tuned database that the one will be higher than the other by a factor of two? Or in fact is it typical in a well tuned database that the one that appears to be the least significant should actually be much lower, so I should focus on that? Or is there really no such thing as a typical profile for a well tuned database, because various wait events might be high on your system and low on mine simply because of the way they're used, so no one really has any idea what to focus on at any given time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). _ Descargue GRATUITAMENTE MSN Explorer en http://explorer.msn.es/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alejandra Pazos Freire 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: Nicoll, Iain (Calanais) 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: comma_to_table cannot convert a list of numbers ??
Probably your nls_numeric_characters are ., (look at the nls_session_parameters view) (comma will be for thousands parameter). You should be able to change it with an alter session but I can't see how yet. Iain Nicoll -Original Message- Sent: 03 October 2001 13:56 To: Multiple recipients of list ORACLE-L List, i'v been trying out a simple use of dbms_utility.comma_to_table procedure the procedure takes a comma delimited string of values and returns the individual values in an array the procedure works fine when i pass a stirng like 'A,B' but error is returned when i pass '1,2' the OUT parameter is and array of varchar2 ResultTab dbms_utility.uncl_array; in_str := '1,2'; dbms_utility.comma_to_table(in_str, strLen, ResultTab); * does not work ORA-00931: missing identifier what is the workaround ?? TIA Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: Nicoll, Iain (Calanais) 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: comma_to_table cannot convert a list of numbers ??
ALTER SESSION SET nls_numeric_characters = './' where / is the group separator. You could change this to something that you would never come across. -Original Message- Sent: 03 October 2001 13:56 To: Multiple recipients of list ORACLE-L List, i'v been trying out a simple use of dbms_utility.comma_to_table procedure the procedure takes a comma delimited string of values and returns the individual values in an array the procedure works fine when i pass a stirng like 'A,B' but error is returned when i pass '1,2' the OUT parameter is and array of varchar2 ResultTab dbms_utility.uncl_array; in_str := '1,2'; dbms_utility.comma_to_table(in_str, strLen, ResultTab); * does not work ORA-00931: missing identifier what is the workaround ?? TIA Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: Nicoll, Iain (Calanais) 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: CI locks
According to the manual (Concepts) it is a Cross-Instance Call Invocation and is used to invoke specific actions in background processes on a specific instance or all instance. These include checkpoint, log switch etc. Id1 gives the particular type. -Original Message- Sent: 03 October 2001 19:50 To: Multiple recipients of list ORACLE-L Would anyone know off hand what a CI type lock is? This is from the output of the following query: select ksqsttyp eq_type, ksqstget gets, ksqstwat waits from x$ksqst where ksqstget !=0 / On Oracle 7.3.4.4.0 Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Comparing data between two tables in two schema
If its the actual data you could look at the minus, intersect operators etc eg select * from schema1.table minus select * from schema2.table give the data in schema1 not in schema 2. Iain Nicoll -Original Message- Sent: 02 October 2001 04:15 To: Multiple recipients of list ORACLE-L I think that the Change Management Pack of the Oracle Enterprise Manager may be useful to you. From: Rao, Maheswara [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Comparing data between two tables in two schema Date: Mon, 01 Oct 2001 12:29:41 -0800 List, I have two schema. The tables in both schema are having same name and structures. Is there any tool to compare the data between two schema tabels? Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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). _ Descargue GRATUITAMENTE MSN Explorer en http://explorer.msn.es/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alejandra Pazos Freire 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: Nicoll, Iain (Calanais) 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: Searching across multiple columns
I'm sure Perl would be more efficient especially as you don't have to name
the columns, but if you don't have to worry about the combined columns being
too large isn't it equivalent to
select col1||'|'||col2 etc
from table
having col1||'|'||col2 etc like '%value%'
Iain Nicoll
-Original Message-
Sent: 02 October 2001 13:00
To: Multiple recipients of list ORACLE-L
HELL of a reason to learn Perl!
Nice..
Mark
-Original Message-
[EMAIL PROTECTED]
Sent: Monday, October 01, 2001 20:51
To: Multiple recipients of list ORACLE-L
PerlEvangelism
my $dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{ RaiseError = 1, AutoCommit = 0 }
);
die connect failed\n unless $dbh;
my $sql='select * from persons;'
my $sth = dbh-prepare($sql) || die
my $rv = $sth-execute || die error in execution\n;
while ( my $arrarRef = sth-fetchrow_arrayref ) {
my @array = @{$arrayRef};
if ( grep(/\s+hoser\s+/gi, @array ) ) {
print Hey! I found a hoser!\n;
}
}
/PerlEvangelism
This connected, built a cursor, read it and searched it.
Compare to how many lines of PL/SQL this would take.
Good reason to learn Perl? :)
Jared
rick_stephenso
[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Searching across
multiple columns
om
10/01/01 11:55
AM
Please respond
to ORACLE-L
Does Oracle have a way to do a search across multiple columns/tables for
specific data? I know I can issue a query with a bunch of or statements,
but is there something similar to fulltext searching?
Thanks for the information,
Rick Stephenson
--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
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: Nicoll, Iain (Calanais)
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: TEMP Tablespace
Wouldn't it need to have something in it? -Original Message- Sent: 25 September 2001 16:10 To: Multiple recipients of list ORACLE-L TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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: Nicoll, Iain (Calanais) 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: Sqlplus tunning
I thought that hash_join was supposed to be best where you had one table much smaller than the other. As there are no restrictions on the data being brought back what is wrong with doing a full table scan of each?. -Original Message- Sent: 19 September 2001 07:25 To: Multiple recipients of list ORACLE-L try nested query in place of sort join method... - Original Message - Date: Wednesday, September 19, 2001 11:10 am Try to use Index for big table ITEM To avoid full table scan. Create index item_index on item(no); This will speed the process... --- Sinardy [EMAIL PROTECTED] wrote: Hi, I have 2 big tables, ITEM (is about 1 million rows) and RTNITEM (is about 20K rows) When I do: SELECT ITEM.no, NVL(SUM(ITEM.CUSTSOLD), 0), NVL(SUM(RTNITEM.CUSTRTN) FROM ITEM, RTNITEM WHERE ITEM.no=RTNITEM.no GROUP BY ITEM.no; Time to execute above query is to long. I tried CREATE OR REPLACE VIEW proc_view_itemsold AS SELECT no, NVL(SUM(custsold, 0)) AS sold FROM item GROUP BY no; CREATE OR REPLACE VIEW proc_view_itemrtn AS SELECT no, NVL(SUM(custrtn, 0)) as return FROM rtnitem GROUP BY no; SELECT i.no, i.sold r.return FROM proc_view_itemsold, proc_view_itemrtn WHERE i.no = r.no; DROP VIEW proc_view_itemsold; DROP VIEW proc_view_itemrtn; The result is the same, it took more than 25 minutes. Do I have to create a temporary tables instead of view to prevent these two giant tables producing a cardinality product ? In this situation is that possible using inner query with where clause again to prevent those giant tables combined? Thank you, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy 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). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH 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: Nicoll, Iain (Calanais) 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: Sqlplus tunning
Doh!. I guess I should have looked at what the table names suggest the restriction is. Sorry! Iain Nicoll -Original Message- Sent: 19 September 2001 18:30 To: '[EMAIL PROTECTED]' I thought that hash_join was supposed to be best where you had one table much smaller than the other. As there are no restrictions on the data being brought back what is wrong with doing a full table scan of each?. -Original Message- Sent: 19 September 2001 07:25 To: Multiple recipients of list ORACLE-L try nested query in place of sort join method... - Original Message - Date: Wednesday, September 19, 2001 11:10 am Try to use Index for big table ITEM To avoid full table scan. Create index item_index on item(no); This will speed the process... --- Sinardy [EMAIL PROTECTED] wrote: Hi, I have 2 big tables, ITEM (is about 1 million rows) and RTNITEM (is about 20K rows) When I do: SELECT ITEM.no, NVL(SUM(ITEM.CUSTSOLD), 0), NVL(SUM(RTNITEM.CUSTRTN) FROM ITEM, RTNITEM WHERE ITEM.no=RTNITEM.no GROUP BY ITEM.no; Time to execute above query is to long. I tried CREATE OR REPLACE VIEW proc_view_itemsold AS SELECT no, NVL(SUM(custsold, 0)) AS sold FROM item GROUP BY no; CREATE OR REPLACE VIEW proc_view_itemrtn AS SELECT no, NVL(SUM(custrtn, 0)) as return FROM rtnitem GROUP BY no; SELECT i.no, i.sold r.return FROM proc_view_itemsold, proc_view_itemrtn WHERE i.no = r.no; DROP VIEW proc_view_itemsold; DROP VIEW proc_view_itemrtn; The result is the same, it took more than 25 minutes. Do I have to create a temporary tables instead of view to prevent these two giant tables producing a cardinality product ? In this situation is that possible using inner query with where clause again to prevent those giant tables combined? Thank you, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy 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). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH 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: Nicoll, Iain (Calanais) 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: explain plan is changing ...
!! Please do not post Off Topic to this List !! Does estimate without samples size or percentage not just use 1024 as the sample size?. If you look at dba_tab_columns the samples size will be in there. If the table is not too big could you try it with compute statistics or estimate statistics with 20 percent sample? Iain Nicoll -Original Message- Sent: 13 September 2001 21:26 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! I have analyzed them today via: analyze table ... estimate statistics; analyze table ... estimate statistics for all indexed columns; No data were added/modified agter that --- Nicoll, Iain (Calanais) [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! Have they been analyzed recently? as if you were using histograms then if the last two months were added after your last analyze it would think they were fairly rare. Cheers Iain Nicoll -Original Message- Sent: 13 September 2001 19:11 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi all: I have a query, which behaves differently depending on the input data (month/year). I have more than 15month worth of data in the database. The query is completed under 1 minute for the first 13 month, but for the last two months it just doesn't finish. I have cancelled it after 36 minutes. The explain plans are differ in that the quick query uses more hash jonts, while slow one utilizes more nested loops. All the tables are analyzed. This must have something to do with the data distribution, but what? Can anyone shed some light onto that? tia __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: g g 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: Nicoll, Iain (Calanais) 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: explain plan is changing ...
!! Please do not post Off Topic to this List !! Have they been analyzed recently? as if you were using histograms then if the last two months were added after your last analyze it would think they were fairly rare. Cheers Iain Nicoll -Original Message- Sent: 13 September 2001 19:11 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi all: I have a query, which behaves differently depending on the input data (month/year). I have more than 15month worth of data in the database. The query is completed under 1 minute for the first 13 month, but for the last two months it just doesn't finish. I have cancelled it after 36 minutes. The explain plans are differ in that the quick query uses more hash jonts, while slow one utilizes more nested loops. All the tables are analyzed. This must have something to do with the data distribution, but what? Can anyone shed some light onto that? tia = __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich 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: Nicoll, Iain (Calanais) 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: QUERY HELP?
Seema, The following would work (there will be better ways to do it especially if you're on Oracle 8) but I'm stuck with 7.3. You'll need to have access to a table which will always have at least have 15 rows (I've used all_objects here). SELECT day, COUNT(*) FROM table_name, (SELECT ROWNUM day FROM ALL_OBJECTS WHERE ROWNUM 16) WHERE day BETWEEN sday AND eday GROUP BY day -Original Message- Sent: 12 September 2001 14:20 To: Multiple recipients of list ORACLE-L Hi I need help to get query sno is primary key of table sday and eday will be between (1 and 15) rowno, sdayeday 1 2 5 2 4 4 3 4 5 4 8 9 5 9 10 the day output will be the no which can be equal to sday or equal to eday or between sday and eday we should get output as day count 2 1 ( in row 1, 2 is equal to sday ) 3 1 ( it is in row 1, b/n 2 and 5 ) 4 3 ( in row 2,3 equal to sday and eday ,and b/n 2and5 in row 1 ) 5 2 like that... 8 1 9 2 10 1 Thanks Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Procedure Builder vs SQL question.
You could try set define off -- stops SQL*Plus looking for substitution variables off or ser scan off-- much the same thing I think -Original Message- Sent: 05 September 2001 19:41 To: Multiple recipients of list ORACLE-L List, One of the developers here is using Procedure Builder to create and compile his PL/SQL packages. It works file until he sends me the package in text format to compile on the database. I call the package by @g:\packages\test1 where test1 is the test1.sql of the package text. Question. In the text /* yadi yadi */ is comments but if I put /* yadi yadi */ the compile askes for the input value yadi: I thought that everything in the comment line is just that comments. If the Procedure Builder is used to compile rather than the database sql it works okay. Any ideas. Thanks, Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
Analyze all indexed columns
Apologies for what is probably a really simple question but what is the default behaviour of analyze table table_name compute statistics and would analyze table table_name compute statistics for table for indexed columns for all indexes be any better/worse than analyzing separately eg analyze table table_name compute statistics analyze table table_name compute statistics for all indexed columns analyze index index_name compute statistics Version is Oracle 7.3. The only references I've got access to don't appear to make it clear. TIA Iain Nicoll -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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 execution plan changes when using a dblink
Have had problems before where anything over a db link was flaky explain plan wise. If you can have the basic query as a view stored remotely on the db you link too it should work more consistently. Iain Nicoll -Original Message- Sent: 29 August 2001 17:01 To: Multiple recipients of list ORACLE-L Hi, We face the strange problem that an executionplan is changed recently without any reason. Now it runs with a full_table scan and it did not do so. The query is executed through a dblink and is part of a batch. - nothing has been changed recently in the app everybody agrees on here, - analyze runs each weekend and a select count(*) differs slightly between num_rows in dba_tables for the specific table - when executing the query manually in two parts the subquery first and ten the value returned hardcoded in the main-query it performs very good - when I ask for an explain plan in the database the link points to the explain plan says it uses an index Details: oracle 7.3.4, hp-ux 10.20 Query: select * from debtor_claims@deca_link where debtor_claim_id = ( select max(debtor_claim_id) from debtor_claims@deca_link where res_id = 1291 and counter_booking_flag = 'N' ) Tia, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeroen van Sluisdam 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: Nicoll, Iain (Calanais) 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: Unix - performance tuning (vmstat 5)
Can't you just change your grep to additionally pipe into a grep -v grep
to exclude the grep vmstat.
I'm sure all the unix gurus will give you a much better way but this should
work
-Original Message-
Sent: 22 August 2001 13:05
To: Multiple recipients of list ORACLE-L
Hi Unix Gurus,
I submit a job to monitor the CPU utilization etc every 15 mins using the
command vmstat 5.
How do I kill the submitted unix process of the batch job ?
I tried to kill the submitted process using a batch job using the following
command :
var_pid=`echo $var_grep_vmstat | (read u v w x y z; echo ${v} )`
kill $var_pid
This works if the output is
$ ps -ef | grep vmstat
orahrms 11271 11263 0 17:30:00 ?0:00 vmstat 5
orahrms 11612 11576 0 18:50:53 pts/11 0:00 grep vmstat
If the output is as below, the program will not work
$ ps -ef | grep vmstat
orahrms 11612 11576 0 18:50:53 pts/11 0:00 grep vmstat
orahrms 11271 11263 0 17:30:00 ?0:00 vmstat 5
Please help. Thanks in advance.
Regds,
New Bee
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CHAN Chor Ling Catherine (CSC)
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: Nicoll, Iain (Calanais)
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: How do I check for partial duplicates?
Wouldn't this delete all the non-duplicate records? -Original Message- Sent: 15 August 2001 23:27 To: Multiple recipients of list ORACLE-L All disclaimers attached to a delete command sent out Try this delete from mem_info where (mem_id, mem_name, mem_time) in (select mem_id, mem_name, min(mem_time) from mem_info group by mem_id, mem_name) -Original Message- Sent: Wednesday, August 15, 2001 5:08 PM To: Multiple recipients of list ORACLE-L Thanks! Now I need to delete the records that have the earlier MEM_TIME. How would I do that? Chris Kevin Lange wrote: select mem_id, mem_name from mem_info group by mem_id, mem_name having count(*) 1 -Original Message- Sent: Wednesday, August 15, 2001 3:48 PM To: Multiple recipients of list ORACLE-L We have a table that may have partial duplicate rows. What select statement do I write to return the partial duplicates? table_name = MEM_INFO MEM_ID MEM_NAME MEM_TIME MEM_LAST I am concerned about two rows having the same MEM_ID and MEM_NAME. Having the same MEM_TIME and MEM_LAST is okay. Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chris Rezek 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: 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: Chris Rezek 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: 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: Nicoll, Iain (Calanais) 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: How do I check for partial duplicates?
Not the most elegant I'm sure but I'm tired and it's late here again wth Kevin's All disclaimers attached to a delete command sent out DELETE FROM mem_info mi WHERE EXISTS (SELECT '' FROM mem_info mi2 WHERE mi.mem_id = mi2.mem_id AND mi.mem_name = mi2.mem_name AND mi.mem_time mi2.mem_time AND mi.ROWID!= mi2.ROWID) -Original Message- Sent: 15 August 2001 23:27 To: Multiple recipients of list ORACLE-L All disclaimers attached to a delete command sent out Try this delete from mem_info where (mem_id, mem_name, mem_time) in (select mem_id, mem_name, min(mem_time) from mem_info group by mem_id, mem_name) -Original Message- Sent: Wednesday, August 15, 2001 5:08 PM To: Multiple recipients of list ORACLE-L Thanks! Now I need to delete the records that have the earlier MEM_TIME. How would I do that? Chris Kevin Lange wrote: select mem_id, mem_name from mem_info group by mem_id, mem_name having count(*) 1 -Original Message- Sent: Wednesday, August 15, 2001 3:48 PM To: Multiple recipients of list ORACLE-L We have a table that may have partial duplicate rows. What select statement do I write to return the partial duplicates? table_name = MEM_INFO MEM_ID MEM_NAME MEM_TIME MEM_LAST I am concerned about two rows having the same MEM_ID and MEM_NAME. Having the same MEM_TIME and MEM_LAST is okay. Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chris Rezek 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: 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: Chris Rezek 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: 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: Nicoll, Iain (Calanais) 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: Source for DB links
George, The code below will near enough do it (at least way back here in 7.3.4) assuming the passwords aren't encrypted (I think you can force that in the init.ora) -Original Message- Sent: 31 July 2001 16:48 To: Multiple recipients of list ORACLE-L All, Where is the source for database links stored ? Perhaps, another to state my question is:How can I fully reconstruct a database link ? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. 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: Nicoll, Iain (Calanais) 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: Input truncated
Just do a carriage return at the end of the last line. The last line will be 35 characters long. Iain -Original Message- Sent: 24 July 2001 15:01 To: Multiple recipients of list ORACLE-L I don't know why when I try to load error_p1 procedure it's printed Input truncated to 35 characters. SQL @error_p1 Procedure created. Input truncated to 35 characters No errors. Can anybody help me ? Thanks, Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea 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: Nicoll, Iain (Calanais) 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 SQL Example
I think you use sign eg the above will work for integers at least and just requires the the lower and upper values of the ranges plugged in. I'm sure it could be adapted for real numbers but hopefully this will do select sum(decode(sign(:value - (0-1)),1,decode(sign(:value - (64+1)),-1,1,0), 0)), sum(decode(sign(:value - (65-1)),1,decode(sign(:value - (128+1)),-1,1,0), 0)), sum(decode(sign(:value - (129-1)),1,decode(sign(:value - (192+1)),-1,1,0), 0)) from dual Cheers Iain Nicoll -Original Message- Sent: 24 July 2001 18:29 To: Multiple recipients of list ORACLE-L Anyone got a good example of flipping a range of values into columner buckets. I have done this in the past but my solutions always seem so convoluted, it seems I have seen more elegant examples in the past. I want to use decode so it will run on older versions of Oracle. Pseudo Example: select sum(decode(if value between 0 and 64 then return 1 else 0)) count_of_this_bucket, sum(decode(if value between 65 and 128 then return 1 else 0)) count_of_this_bucket,... from table Thanks, Ethan -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Select only one of three tables
Can't you just check if emp_id is null eg decode(dept_one.dept,null,decode(dept_two.dept,null,dept_three.dept, dept_two.dept), dept_one.dept) dept Iain Nicoll -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 03 July 2001 15:11 To: Multiple recipients of list ORACLE-L Hello list I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column. While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only: select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3 where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_id Can someone recommend a solution? Thanks Witold -- 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: Nicoll, Iain (Calanais) 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: Why are my indexes being ignored?
You table doesn't have a degree 1 does it?. I've seen examples where this caused a lot of indexes to be ignored. -Original Message- Sent: 29 June 2001 16:53 To: Multiple recipients of list ORACLE-L I'm returning 117 rows. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Friday, June 29, 2001 9:50 AM To: [EMAIL PROTECTED]; Carle, William T (Bill), NLCIO Subject:Re: Why are my indexes being ignored? Carle, William T (Bill), NLCIO wrote: Hi, I created an index on a table. The table has about 83,000 rows. The index is a simple index on one field, 35 different values of the index. I analyzed the table and I analyzed the index, trying it both with a histogram and without a histogram. No matter how I do it, it does a full table scan. Any ideas why? I know I can use a hint, but it seems to me that it ought to use the index. how many rows are you returning? if i remember right, and if i don't i'm sure i'll be reminded;-), if you return more than a certain % of the table it does a full table scan even if there are indexes. -- Bill Shrek Thater Certifiable ORACLE DBA Telergy, Inc.[EMAIL PROTECTED] ~~ You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. ~~ It's not a bug. It's an undocumented feature -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), NLCIO 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: Nicoll, Iain (Calanais) 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: HOW TO SUBSTR INSTR THIS LIST
Just as a starter you could start with the below the first two decodes check whether there are any spaces and the third decode checks that the first and last space are different i.e there is a middle name. The instr(full_name,' ',-1) is checking for a space from the end of the string. You might have to look at rtrim and ltrim to dump any trailing/leading spaces and the code below will not deal with the Sophia Cadi-Soussi ( Gailhardou ) example. Iain Nicoll select decode(instr(full_name,' '), 0,full_name, substr(full_name,1,instr(full_name,' ')-1)) first_name, decode(instr(full_name,' ',-1),0,null, substr(full_name,instr(full_name,' ',-1)+1)) last_name, decode(instr(full_name,' ') - instr(full_name,' ',-1), 0, null, substr(full_name, instr(full_name,' ')+1, (instr(full_name,' ',-1)-1) - (instr(full_name,' ' middle_name from nametable -Original Message- Sent: 27 June 2001 17:27 To: Multiple recipients of list ORACLE-L Hey all, I've got this list of names. It's not a very structured list. So my question would be how do I get this names in a select statement and break them up in columns: first name (is the first name in list), last name (last one), middle name (everything in between first and last names) I know that this may be done by using SUBSTR AND INSTR. But how? Would you please help? Thanks a lot. Here is a fragment of the list of names: FULL_NAME - Caroline Bernard Sophia Cadi-Soussi ( Gailhardou ) Rudy Sicard Luis Haro-Garcma Philip Cohen Socrates Fragoulis Michael Munch Hardip Kaur Robert Szasz Sebastien Schneider Telma Quiroga Lspez Stiphanie Frenkel Samuel Tietse Nicola Rose Oliver Cornely Philippe Saiag M.t. Hamed Mosavian R. Bruce Nicklas Valery Tsukerman FULL_NAME - Lidiya Smirenina Marie-Theres Hauser Jelel Ezzine Radhi Mhiri Franco Fenzi Hachne Djellout Beatrmz Quarterolo Bram van Dam Ted Gaten Sergio Aravena Alberto Monroy-Garcia Pedro Montecinos Becerra Michalis Vafopoulos Klaus E. Gempel Guijun Yan Stiphane Schaak __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stas 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: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ora-1654 Unable to extend index on tablespace
Mitchell,
I may well be wrong but check your db_block_size in v$parameter
eg
select name, value
from v$parameter
where name = 'db_block_size'
/
NAMEVALUE
db_block_size 4096
I think you have to multiply the reported figure by the blocksize to get the
extent size being looked for.
-Original Message-
Sent: 26 June 2001 16:42
To: Multiple recipients of list ORACLE-L
Hi DBAs
Whenever I have the ora-1654, I will
1. alter index/table name deallocate unused
2. alter tablespace name coalescs;
3. run querys to check dba_free_space and dba_data_files
There are total 140 indexes on this tablespace with setting init 1024k and
next 1024k.
I got confused now that for message 'unable to extend by 256'. What is mean
for 256 here?
The free space(byte) must be over 1024k here to avoid ora-1654 for each of
140 index segments?
Thanks in advance.
Mitchell
This the query I run today. I only take first few lines and last few lines.
compute sum LABEL 'TOTAL of SEGMENTS' of totalofsegments on report
select tablespace_name, bytes free_space, count(bytes) segcount,
(bytes * count(bytes)) totalofsegments
from dba_free_space
where tablespace_name=UPPER('1')
group by tablespace_name, bytes
order by tablespace_name, bytes;
TABLESPACE_NAME FREE_SPACE SEGCOUNT TOTALOFSEGMENTS
--
IDX_FINC_C70614 4,09614,096
IDX_FINC_C70614 24,5766 147,456
IDX_FINC_C70614 28,6721 28,672
IDX_FINC_C70614 364,5441 364,544
IDX_FINC_C70614 368,6402 737,280
IDX_FINC_C70614 1,396,73611,396,736
IDX_FINC_C70614 2,801,66412,801,664
TOTAL of SEGMENTS 913,092,608
- Original Message -
To: '[EMAIL PROTECTED]' ; '[EMAIL PROTECTED]'
Sent: Tuesday, June 26, 2001 8:08 AM
Mitchell have you tried coalescing your tablespace? How big are your
extents?
-Original Message-
Sent: Monday, June 25, 2001 5:28 PM
To: Multiple recipients of list ORACLE-L
Dear DBAs
I have a tablespace for index with 5 file with different size from
500mb - 2000 mb.
Total tablespace size is 6g and used 5317mb abote 86.13% usage.
I got the error today.
ora-1654 unable to extend indx sechma.indexname by 256 in tablespace
tablespacename.
The following is the query I got for the tablespace .
We can see the index takes 92 extents and maxextends setting is 8192.
I then set autoextend on a datafile then error is gone.
What is the reason to cause ora-1654 even there are 700mb space avai. I also
checked the tablespace and index setting with both have next extend 1024k,
maxextend 8092.
Mitchll
SEGMENT TYP
BYTES NEXT_EXTENT EXTENTSMAX_EXTENTS
--- ---
-
8,192
C70614.FINC_INFO_ATTRIBUTE_080101_PKIND
94,269,4401,048,576 92 8,192
C70614.FINC_INFO_ATTRIBUTE_090101_PKIND
52,457,4721,048,576 51 8,192
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mitchell
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: Nicoll, Iain (Calanais)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle reports
You could try something like select item, sum(decode(sign(transaction_date - trunc(sysdate,'Month'),-1,0, 0,nvl(value,0), 1,nvl(value,0)) mtd, sum(decode(sign(transaction_date - trunc(sysdate,'Year'),-1,0, 0,nvl(value,0), 1,nvl(value,0)) ytd from transactions_table group by item The sign should work out whether the transaction date is greater than start of month or start of years and if not add 0 to the total. The nvls are just in case you might have any null values lurking about. You may be able to do it better in reports but I don't have it to hand right now. HTH Iain Nicoll -Original Message- Sent: 11 June 2001 21:20 To: Multiple recipients of list ORACLE-L I am creating a report based on a couple tables. The report should show the total amount sold for the current month and another field for total amount sold Year To Date. How can I created the report so that the month to date value for a particular item is adjacent to the total amount for Year to date. Please see below for desired reports output. Item # Item DescriptionAmount Sold (Current Month) Amount Sold (YTD) 54654 Door jams 20,245.00 465,475.00 46545 Windows Casing 1500.00 124,445.57 Does anyone know what reference material I should purchase for oracle reports? 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: Nicoll, Iain (Calanais) 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: how to substr '%' from the data?
Not exactly sure what you're after
but possibly the below if you're simply looking to stop before the first
occurence of '%'
select substr('CS-%-ABC-%-%', 1, instr('CS-%-ABC-%-%','%') - 1) from dual;
-Original Message-
Sent: 12 June 2001 00:07
To: Multiple recipients of list ORACLE-L
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until % and return without %:
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did select RTRIM('CS-%-%-%-%', '%-%') from DUAL, but it can not return
correct for the last record
eg, select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL -- will not work
correctly
Thanks for help
_
Do You Yahoo!?
Yahoo! Mail http://personal.mail.yahoo.com/?.refer=mailiyfoot Personal
Address - Get email at your own domain with Yahoo! Mail.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
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: unable to create spatial index via dynamic_sql
May be like the reply to your previous question in that you may be able to create an index through a role from sqlplus but from pl/sql the privilege has to be granted directly to the user. Cheers Iain -Original Message- Sent: 15 May 2001 06:45 To: Multiple recipients of list ORACLE-L hi list, I am able to create index from Sqlplus as... create index i_sptl_test on ADDRESSED_PROPERTY_(polygon_geometry) indextype is mdsys.spatial_index; Thisstatement works fine and I ma able to create index. But the same statement when executing through package - procedure p_crt_sptl_index ( it fails listed error messages. Of course by using Dynamic Sql. DDL Statement: create index i_sptl_test on ADDRESSED_PROPERTY_(polygon_geometry) DDL Statement: indextype is mdsys.spatial_index DDL Statement: ** Fatal Exception p_crt_sptl_index** ORA-29855: error occurred in the execution of ODCIINDEXCREATEroutine ORA-13231: failed to create index table [Create index table failed forI_SPTL_TEST_rt$] during R-tree creation ORA-29400: data cartridge error ORA-01031: insufficient privileges ORA-010 ** Fatal Exception incrt_metadata_entries ** *** p_crt_sptl_indexes: creation of SpatialIndex on ADDRESSED_PROPERTY_.polygon_geometry via p_crt_sptl_index failed *** Anybody got ideas why this is Oracle Saptial database 8.1.7 on sunsparc solaris 7. narender.akula http://www.terralinkltd.com Oracle DBA, Terralink Limited Wellington, NZ. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Narender Akula 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: Nicoll, Iain (Calanais) 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: What columns are in the Primary Key
Could also try select * from user_ind_columns where index_name = 'PK_ACCOUNTS' -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 10 May 2001 19:16 To: Multiple recipients of list ORACLE-L use user_cons_columns: SQL desc user_cons_columns NameNull?Type --- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) POSITION NUMBER SQL select constraint_name , table_name, column_name from user_cons_columns ord er by constraint_name; Above query is crude, but effective... Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Helmut Daiminger [SMTP:[EMAIL PROTECTED]] Sent: Thursday, May 10, 2001 12:23 PM To: Multiple recipients of list ORACLE-L Subject: What columns are in the Primary Key Hi! Is there a data dictionary view that gives me all the columns in a primary key of a table? I can query user_constraints to find out about all constraints on a table: select * from user_constraints where table_name='TBACCOUNTS' OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION -- --- - - VIVOUSER SYS_C003011 C TBACCOUNTS COMPANYID IS NOT NULL VIVOUSER SYS_C003012 C TBACCOUNTS STORELOCID IS NOT NULL VIVOUSER SYS_C003013 C TBACCOUNTS ACCOUNTID IS NOT NULL VIVOUSER PK_ACCOUNTS P TBACCOUNTS How can I get what columns are in the primary key PK_ACCOUNTS ? I have more NOT NULL columns in the table than in the PK. Any idea? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger 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: Nicoll, Iain (Calanais) 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: Order of columns in a table
Try 1 select table_name, column_name, column_id 2* from user_tab_columns where table_name = 'PRODUCT_PROFILE' SQL / TABLE_NAME COLUMN_NAMECOLUMN_ID -- -- - PRODUCT_PROFILEPRODUCT1 PRODUCT_PROFILEUSERID 2 PRODUCT_PROFILEATTRIBUTE 3 PRODUCT_PROFILESCOPE 4 PRODUCT_PROFILENUMERIC_VALUE 5 PRODUCT_PROFILECHAR_VALUE 6 PRODUCT_PROFILEDATE_VALUE 7 PRODUCT_PROFILELONG_VALUE 8 8 rows selected. SQL desc product_profile NameNull?Type --- PRODUCT NOT NULL VARCHAR2(30) USERID VARCHAR2(30) ATTRIBUTEVARCHAR2(240) SCOPEVARCHAR2(240) NUMERIC_VALUENUMBER(15,2) CHAR_VALUE VARCHAR2(240) DATE_VALUE DATE LONG_VALUE LONG -Original Message- Sent: 10 May 2001 20:01 To: Multiple recipients of list ORACLE-L Hi! How can I find out what the order of columns in a table is? If I do a select * from tablename, the columns appear in a specific order. The same order than they appear when I do a describe tablename. I can even do an insert without specifying the columns in my VALUES (.,) when they are in that specific order. How and where can I find the order of the columns within a table? Any ideas? Yhis is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger 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: Nicoll, Iain (Calanais) 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 Exception
Don't think you get an exception as such but I think you can use %FOUND or %ROWCOUNT -Original Message- Sent: 09 May 2001 17:46 To: Multiple recipients of list ORACLE-L Hi all, Do you know if an exception is raised for 0 rows updated. Take the following scenario but using PL/SQL. select * from a; C1 1 1 1 1 You get: update a set c1 = 5 where c1 = 6; 0 rows updated. Thanks Kumanan ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumanan Balasundaram 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: Nicoll, Iain (Calanais) 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: consistency in cost?
Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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: Nicoll, Iain (Calanais) 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: Input truncated to 1 characters
I think it's as simple as no carriage return on the end of the last line (I'm assuming it's the ';' character). Cheers Iain Nicoll -Original Message- Sent: 03 May 2001 17:27 To: Multiple recipients of list ORACLE-L I get the exact same message every time and the procedures always work and there are no invalid objects in dba_objects. I think you can safely ignore the message. I have no idea why it comes out, hopefully someone else can spread some light on that. [EMAIL PROTECTED] 05/03/01 11:56AM I noticed when I install procedures, i get this message: Input truncated to 1 characters can someone help explain this to me? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leyden, Joseph 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: William Beilstein 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: Nicoll, Iain (Calanais) 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: Priv to view package bodies created by other users
Debbie, You could try grant select on dba_source view. Cheers Iain Nicoll -Original Message- Sent: 18 April 2001 16:45 To: Multiple recipients of list ORACLE-L I am a relatively new DBA and looking for advise. I have a request from our developers for the ability to view package body information which has been created by other users. We are currently using Oracle8i Enterprise Edition Release 8.1.6.1.0. I have been advised that in order to view package bodies created by other users you need the "create any procedure" system privilege. To view type bodies created by other users you need the "create any type" system privilege. Are there any object level privileges that would accomplish this request? TIA for your help, Debbie James -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Debbie James 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: Nicoll, Iain (Calanais) 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).
