Re: Using a '.' in a username
Mladen Gogala wrote: To use . in the username is a cruel and unusual practice which exposes funny sqlplus behavior: $ sqlplus test.yogi Mladen, please have a look 1. It's not sqlplus but $SHELL behavior, try this $ sqlplus \test.yogi\ 2. Oracle9i SQL Reference Release 2 (9.2) CREATE USER 3. Oracle9i SQL Reference Release 2 (9.2) Schema Object Naming Rules Schema Object Naming Examples The following examples are valid schema object names: last_name horse hr.hire_date EVEN THIS THAT! a_very_long_and_valid_name -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Using a '.' in a username
Joe Testa wrote: besides the point the example is a schema.object_name NOT a user.name, evidently since its NOT in quotes. Does that mean that you cannot create such user(s) and connect as it was shown? Please read what it's written in the documentation -- that's why I put references. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Using a '.' in a username
Joe I do not think that message with a simple example and two references to the documentation was a bad and the example of schema naming was an excerption only. I've explained what I think about it in my previous message: I'm not quite sure about common sense here. DBA who creates such names for his/her trusting users would have some headache anyway. But do not mix sqlplus bugs that can be windows dependent and the question Mladen asked. I think most of the messages here in the list have to be problem-related not opinion-related or person-related ones i.e. out of technical topic. That's why oracle-l is a technical mailing list, and it's not about who is who, who should use what oem or command line, windows not unix. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Joe Testa wrote: Please unless you've been at oracle doing oracle stuff since 1990 back in version 5 days, you can spare me the condescending attitude and the company line, the bottom line is oracle is just like C, it will let you shoot off your own foot(and thats the way most of us like it). its not dumbproof from any means or platform, its just gotten gui-fied in alot of cases and people who can right-click in OEM to resize a datafile and think that makes them a dba have a bit to learn. The bottom line still boils down to this, just because you can, doesn't make it a smart thing to do. joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Using a '.' in a username
Mladen Gogala wrote: Thanks, Vladimir. I haven't looked in the manual but as I've said, using those things in usernames or table names is a cruel and unusual practice which should be avoided if possible. Agreed, my point was the examples have to be consistent -- $SHELL does pay attention to the double quotes. That's the only thing I wanted to tell :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Misbehaving query
Bellow, Bambi wrote: SQL select a.*, b.* 2 from 3 ( select * from junk minus select * from junk2 ) a, 4 ( select * from junk2 minus select * from junk ) b; no rows selected SELECT a.* , b.* FROM (SELECT dummy x FROM dual WHERE 1 = 2) a , (SELECT dummy x FROM dual) b WHERE b.x = a.x(+) / SELECT a.* , b.* FROM (SELECT dummy x FROM dual WHERE 1 = 2) a , (SELECT dummy x FROM dual) b / You join empty result set of table a, with some rows from table b. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: diff between FGRD and ARCH in v$archived_log.creator column
[EMAIL PROTECTED] wrote: What is FGRD and ARCH in v$archived_log.creator column. I see something like below in my db. select recid,creator from v$archived_log Oracle9i Database Reference Release 2 (9.2) V$ARCHIVED_LOG CREATOR VARCHAR2(7) Creator of the archivelog: * ARCH - ARCH process * FGRD - Foreground process * RMAN * SRMN - RMAN at standby * LGWR - LGWR process SQL select recid,creator from v$archived_log where recid 55; RECID CREATOR - --- 56 ARCH 57 ARCH 58 FGRD SQL ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL select recid,creator from v$archived_log where recid 55; RECID CREATOR - --- 56 ARCH 57 ARCH 58 FGRD 59 FGRD -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: A brief detour....;-)
Bobak, Mark wrote: Nice solution! I had first briefly considered a SQL solution, but it didn't I like simple ones. immediately come to mind, and the PL/SQL recursive solution was pretty straightforward, so, I went that direction. I wasn't really too concerned about expense, in this case;-) I don't see too many practical applications. ;-) I did not want to discuss this particular case. It's a good one. Good as an example. There was/is a practical application of recursive solution ( 9i) -- check standard.replace which is not so nice if it's heavily used. regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: rebuilding indexes - sure to cause a ruckus
Tanel Poder wrote: Ouch, I gotta take a day off to read this one ;) http://www.netmeister.org/news/learn2quote.html It's all about optimization... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: A brief detour....;-)
Bobak, Mark wrote: Here it is: [...] This concludes this public service announcement. We now return you to our regularly scheduled programming. Interesting. PL/SQL recursive solutions are expensive, though. :) Did not check it, maybe something alike was posted already... SET VERIFY OFF PAGES 200 DEFINE disks=7 REM Please do not use all_objects :) DEFINE big_table=all_objects COLUMN Implementation Plan FORMAT A20 SPOOL hanoi_solution.txt SELECT 'Move it from ' || TO_CHAR(MOD(BITAND(ROWNUM, ROWNUM - 1), 3) + 1) || ' to ' || TO_CHAR(MOD(-BITAND(-ROWNUM - 1, -ROWNUM), 3) + 1) AS Implementation Plan FROM big_table WHERE ROWNUM POWER(2, disks) AND disks 8 / SPOOL OFF Should work correctly. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Silly SQL Question
Jacques, I checked your example, I think there are some issues here: 1. Original queries provided below do use merge join. 2. We could have missing indexes which can exist on real system. 3. Timings below is not a criteria -- after gathering statistics and creation an index on val this both queries take about 1,3 seconds. So it means on your system you checked the *speed of sort operation* only -- because, most probably, merge was used. Even w/o index but with hash join it works much more faster -- 11.87 vs 1.25 (figures are not precise). 4. It'a all for nothing -- life is cruel and real-life examples are much more complex :) If you do not mind I would not continue this discussion. Thank you. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Silly SQL Question
Jacques Kilchoer wrote: Mr. Begun: I'm not convinced that your answer is quite the right one. I've provided two solutions but I'm still confused :). Jacques, does that mean that I understand English and the original query was Ok? :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Gabriel Aragon wrote: Ok, guys I have to apologize twice, ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Silly SQL Question
Jacques,
you can use my first name -- Mr. is too official for this list :). You have modified
the query,
however I would suggest you to check execution plan (and present it here) and remove
LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of
elements in the
list i.e., in your case, 4. As I already said, it was just an example, in real life I
would think
is it Ok or not Ok to use it.
Timing is not everything you can check, consider statisticts. Did you consider
indexing val?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Silly SQL Question
Jacques Jacques Kilchoer wrote: I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. If the given list is created properly, which I think it's a must in this case, one would not need to use PL/SQL, the task can be solved in SQL only. Below is just *an example*, not a generic solution. VAR list VARCHAR2(30); -- number could be counted as well, not a big deal EXEC :list := '1,7,5,'; WITH numbers AS ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM numbers) AND cnt = (SELECT COUNT(DISTINCT element) FROM numbers) -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = (SELECT COUNT(DISTINCT element) FROM numbers) -- same / Again, it's not a generic solution but it's Ok to use it for this particular task -- the number of elements is limited anyway. One could add yet one condition to avoid troubles with TO_NUMBER conversion, it's easy but I'm leaving it as is. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) That's obviously nice :) but I think it's not a reason to use PL/SQL to solve this task. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Select ?
I would suggest to read the documentation. You changed the original question now you want to see something else. SQL CREATE TABLE test_table (id NUMBER, testcolu CLOB); Table created. SQL INSERT INTO test_table VALUES(1, 'test'); 1 row created. SQL INSERT INTO test_table VALUES(2, ' '); 1 row created. SQL INSERT INTO test_table VALUES(3, NULL); 1 row created. SQL INSERT INTO test_table VALUES(4, EMPTY_CLOB()); 1 row created. SQL COMMIT; 1 SELECT id, testcolu 2FROM test_table 3 WHERE dbms_lob.getlength(testcolu) = 0 4* OR (dbms_lob.getlength(testcolu) = 1 AND dbms_lob.instr(testcolu, ' ') = 1) SQL / ID TESTCOLU - -- 2 4 If the columns updated to ' ' it can not show NULL during select. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: When I used query SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; I received error like ORA-00932: inconsistent datatypes I'm on 8163. Let me know if you have any thoughts. I want to findout those columns which are updated thru empty_clob() functions or those columns which are showing NULL during select but those columns were updated either thru EMPTY_CLOB() or ' '.? thx- Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Mon, 10 Nov 2003 10:19:25 -0800 SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Silly SQL Question
Gabriel
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq
UNIQUE (usr, val)
);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('JKL', 8);
INSERT INTO gab VALUES('JKL', 5);
COMMIT;
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
Depending on the existence of the constraint, here gab$uq, you can
either use inline view of run it against original table.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Gabriel Aragon wrote:
I have a table with like this:
Usr val
--
GAP 1
GAP 5
GAP 7
JKL 8
JKL 5
I need a query that returns the user (GAP o JKL) that
has ALL the values in a list. Example: Having the
list: 1,5,7 the result will be GAP, but with the
values 1,5 or 1,5,7,8 there will be no result.
select distinct usr
from xxx
where val = All (1,3,5)
I was trying the ALL operator but it works with part
of the list, I need the user that has (exactly) all
the values in the list. Any idea?
Maybe it's a simple solution, but after several hours
I feel blocked.
TIA
Gabriel
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Deleting partitioned data
Jonathan, Oracle9i SQL Reference Release 2 (9.2) SELECT PARTITION | SUBPARTITION For PARTITION or SUBPARTITION, specify the name of the partition or subpartition within table from which you want to retrieve data. For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the WHERE clause that restricts the retrieval to one or more partitions of table. Oracle will interpret the condition and fetch data from only those partitions. (It is not possible to formulate such a WHERE condition for hash-partitioned data.) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jonathan Gennick wrote: I'd like to ask a question. Consider the two statements below: DELETE FROM county PARTITION (michigan) WHERE county_name = 'Alger'; DELETE FROM county WHERE county_name = 'Alger' AND state = 'MI'; Is there ever a case where the first option is preferable? Is there ever a case where Oracle wouldn't be able to isolate the partition of interest simply by evaluating the conditions in the WHERE clause? There must be, else why would Oracle provide the syntax shown in the first statement? However, I'm having difficulty coming up with a good example of when that syntax makes sense. Can someone help me out here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Silly SQL Question
Jacques
Yes, probably, you are right. I've overlooked example section,
given by Gabriel.
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('PAG', 1);
INSERT INTO gab VALUES('PAG', 7);
INSERT INTO gab VALUES('PAG', 2);
INSERT INTO gab VALUES('JKL', 1);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('GPA', 1);
INSERT INTO gab VALUES('GPA', 5);
INSERT INTO gab VALUES('GPA', 7);
INSERT INTO gab VALUES('GPA', 8);
COMMIT;
PL/SQL is not needed to solve this task as SQL task. There
reason when it would be wise to rewrite it is out of scope
of this topic (but the reason is obvious).
I'm just thinking that the query proposed by you is a bit
expensive. So, I've re-scribbled mine:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (1, 5, 7)
AND cnt = 3
GROUP BY
usr
, cnt
HAVING COUNT(*) = cnt
/
HTH,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: You have not provided me with anything (I cannot post these things to public forums because of my email)! Please re-read my posts. The only thing you said bad code, good code! How should it be named? I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. Check the dates of the posts. The idea is to add value instead of waiting to have the final words! Excuse me, but that just not polite. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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 comparison addition:
Chris
There is a contradiction below:
Chris Stephens wrote:
SQL select sys_context('userenv','session_user'),
2 dump(sys_context('userenv','session_user')), a.reports_login,
3 dump(a.reports_login)
4 from global.client_dim a
5 WHERE sys_context('userenv','session_user') = a.REPORTS_LOGIN;
Produces some output.
Your original query does not return anything.
1 SELECT count(*)
2 FROM global.client_dim a
3* WHERE sys_context('userenv','session_user') = trim(a.REPORTS_LOGIN)
The obvious differences here are:
. TRIM function
. probably when you've tried to launch the original query you had pofile
functions enabled, when you tried it second time it was disabled.
. query rewrite is used (could be, right?)
Could you please check the second and third items?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
SYS_CONTEXT('USERENV','SESSION_USER')
DUMP(SYS_CONTEXT('USERENV','SESSION_USER'))
REPORTS_LOGIN
--
DUMP(A.REPORTS_LOGIN)
REPORTS_DELTA
Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
REPORTS_DELTA
Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
-Original Message-
Sent: Tuesday, November 11, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
I may be barking up the wrong tree, but humour an old dba...
Could you try running the following and post the output?
select sys_context('userenv','session_user'),
dump(sys_context('userenv','session_user')), a.reports_login,
dump(a.reports_login)
from global.client_dim a
WHERE sys_context('userenv','session_user') = a.REPORTS_LOGIN;
Daniel
Chris Stephens wrote:
I just tried:
1 SELECT count(*)
2 FROM global.client_dim a
3* WHERE sys_context('userenv','session_user') = a.REPORTS_LOGIN
SQL /
COUNT(*)
--
1
...but we had a problem 2 weeks ago where the comparison only worked when
I
put in the trim.
?
1 SELECT count(*)
2 FROM global.client_dim a
3* WHERE TRIM(sys_context('userenv','session_user'))
=TRIM(a.REPORTS_LOGIN)
SQL /
COUNT(*)
--
0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Interesting PL/SQL Puzzle
Pete Finnigan wrote: Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. ^ what instructions? and what events do you refer to? I cannot post these things to public forums because of my email, :) sorry. Otherwise this mailing list would be flooded by messages of mine like Russians Oracle newsgroups were flooded before I joined Oracle. The Internet and 'Oracle 24x7 Tips Techniques' book have some description of such. You can also enable native compilation to see what's going on [if you familiar with C language]. regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. P.S.: General note: not everything that's undocumented has any value. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Select ?
SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Interesting PL/SQL Puzzle
Hi! NAMESHARABLE_MEM --- TEST_PLSQL1 185607 TEST_PLSQL5 9123 A lot of junk, right? :) PL/SQL engine works with interpretive code, it does not have any optimizations -- here I do simplify, so do not consider this statement as an absolute truth -- like, e.g. most of the C compilers have. It has its own rules that are not clear, usually. dbms_profiler won't help here (It could mislead, however. For a good example see recent post of Raj), IMHO. As I told: I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage These two things are simple but important. So, I would sugget to change it to (sorry for dirty coding): CREATE OR REPLACE PACKAGE test IS PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); END; / CREATE OR REPLACE PACKAGE BODY test IS pat1 CONSTANT varchar2(1000) := '%tt%'; pat2 CONSTANT varchar2(1000) := 'lll'; pat3 CONSTANT varchar2(1000) := '%dfddiii%'; pat4 CONSTANT varchar2(1000) := 'y'; ls VARCHAR2(1000); b1 BOOLEAN; b2 BOOLEAN; PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); begin if false then b1 := var1 LIKE pat3; b2 := var2 LIKE pat1; -- if b1 then if b2 then ls := pat2; else ls := pat4; end if; end if; .. out1 := ls; END; or something alike, hope you get the idea. On my system it gives: 00:01:28.12 vs 00:00:08.60. When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. I think this statement of yours does answer your original question -- bad PL/SQL coding -- bad NC results. HTH, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Khedr, Waleed wrote: Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code). All the code in proc test_plsql1 is inside an IF clause that will not run. [...] proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Interesting PL/SQL Puzzle
Carel-Jan Engel wrote: It appears that the overhead caused by a 'call by reference' (in out) will cost you some extra time. The 'call by value' appears to be cheaper. PL/SQL User's Guide and Reference Release 2 (9.2) 8 PL/SQL Subprograms Summary of Subprogram Parameter Modes IN OUT -- actual parameter is passed by value (a copy of the value is passed in and out) unless NOCOPY is specified. Whatsoever, it appears that the call by reference is more expensive than a call by value. See above. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) I've provided a selfexplanatory fix of the 'bad' code, please review it. You code uses standard.like, and a lot isntances of booleans -- each IF condition, same could relate to out variables (ls), and I hope you understood why the package is used. I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage I cannot provide you with tech. details open the C (native) code and see. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: I did, but it did not help. As I explained there is nothing in the code that gets executed as I explained in my code. I would suggest to consider some simple things: . data dictionary dependencies . standard Oracle and your application's package(s) dependencies . proper datatypes usage . invoker/definer rights processing big block for string manipulation, two pages of code (substr, instr, etc) What's 'etc' here? Two IF statements (IF FALSE THEN) that will be always FALSE. So nothing of the code inside the if gets executed. Interestingly when I start to remove some of the code inside IF THEN, it starts to speed up. Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -Original Message- Sent: Saturday, November 08, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Without knowing the actual code, I'd use dbms_profiler and run this test say a thousand time. Analysis of collected data will help you find the problem spots. -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Select ?
get length of each of those you will see which one you need. length of #4 should be 0. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: Hi, If we have table with clob column and want to findout whcih clob column rows has been updated/inilialised thru empty_clob() functions? How to do that? Like table with 2 columns ID and testcolu .ID is desc test_table Name Null?Type - ID NUMBER(16) TESTCOLU CLOB Having rows like ID TESTCOLU -- 1 2 3 4 99 Out of these 5 rows id# 4 were inilialised thru EMPTY_CLOB() function.Wondering which sql statement would pickup only those rows having id value 4 . The ID 3 was inilialised thru NULL and 99 was with ' '. thanks in advance. -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: How do you genrate primary keys?
Jonathan
Yet another way [I do understand the drawbacks :)]:
* Table with the sequences my_sequences -- for preliminary definition
sys.seq$ can be considered, if possible each row is placed into dedicated
block (number of sequence does matter in this case so, it's a search for
trade off) -- could help when there is a lot of concurrent requesters.
Suppose number of such sequences defined as N.
* FUNCTION get_next_range('sequence', range) (AUTONOMOUS transaction)
returns next number and updates the sequence number according to
the requested range -- so a caller would get the small pool (session
sub-pool) of sequences. 'SQ' can be emulated via dbms_lock, if needed,
that would take additional time, of course.
* FUNCTION get_next('sequence') that would return next value from the
pool of requested numbers -- it would not touch my_sequences unless
it's out of numbers.
* FUNCTION get_current('sequence'), emulates .CURRVAL
For example, the caller knows that it would need 10 unique numbers for
the given transaction:
1. get_next_range('COMMON_SEQUENCE', 10); - 17
(if someone else call get_next_range now he would get 27)
2. INSERT INTO table_a (id, name) VALUES(get_next('COMMON_SEQUENCE',
'test'); etc.
Practically it does emulate regular sequences but it allows each
caller to define its own pool, that could be a bit more efficient
but requires some programming and accuracy.
Also, one can consider something like:
CHR(65 + MOD(SYS_CONTEXT('USERENV', 'SESSIONID'), N))
add this prefix and make primary keys e.g. character based (or play
around and stick with numeric values only) using the approach described
above. It would make particular sessions based on particular sequence
entry of my_sequences table.
Gaps are unavoidable here.
*Drawbacks* are *clear*, so it's just an idea that can be
implemented and tested. I'm pretty sure that the approach
is disputable, so please let's avoid long discussions.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jonathan Gennick wrote:
The recent article that mentioned sequences got me to
thinking. I might pitch a more detailed article on sequences
to Builder.com. But a more interesting article might be one
that explored various ways to automatically generate primary
keys. So, in the name of research, let me throw out the
following questions:
What mechanisms have you used to generate primary keys?
Which ones worked well, and why? Which mechanisms worked
poorly?
I've run up against the following approaches:
* Hit a table that keeps a counter. This is the roll your
own sequence method. The one time I recall encountering
this approach, I helped convert it over to using stored
sequences. This was because of concurrency problems: with
careful timing, two users could end up with the same ID
number for different records. Is there ever a case when this
roll-your-own approach makes sense, and is workable?
* Stored sequences. I worked on one app that used a separate
sequence for each automatically generated primary key. I
worked on another app, a smaller one, that used the same
sequence for more than one table. The only issue that I
recall is that sometimes numbers would be skipped. But end
users really didn't care, or even notice.
* The SYS_GUID approach. I've never used SYS_GUID as a
primary key generator. I wonder, was that Oracle's
motivation for creating the function? Has anyone used it for
primary keys in a production app? What's the real reason
Oracle created this function?
* Similar to SYS_GUID, I once worked on an obituary-tracking
application that built up a primary key from, as best I can
recall now: date of death, part of surname, part of first
name, and a sequence number used only to resolve collisions,
of which there were few. The approached worked well,
actually, because whatever fields we munged together to
generate a primary key gave us a unique key the vast
majority of the time.
The SYS_GUID approach is interesting, but if you need an ID
number that users will see, and that users might type in
themselves (e.g. social security number), is SYS_GUID really
all that viable?
Best regards,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: I wanna know how Oracle uses file organization in their DB
There is quite some important difference between theoretical definition of the B*/+
trees and their implementation, in particular underflow and overflow could be
implemented not as defined -- a trade off, as usually -- however those two operations
are major ones in the index data management. Knuth's book does not reflect such nuances
as concurrent operations against {B, B+, B*}-trees in particular, at least in the
chapter of the book Cary mentioned. I think, some more information can be found on acm.
Thai, I do not think that such documents are openly available -- never read those :),
but, definitely, knowledge of some basic principles would help -- so start from the
Knuth's books.
regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Tanel Poder wrote:
Hi!
If I recall correctly, a simple B-tree leafs didn't have pointers to last
and next leaf in them, whilst B+tree and B*-tree did...
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, October 31, 2003 6:44 PM
A B-tree is not a binary tree. A binary tree node has 0, 1, or 2
children. A B-tree is a multiway tree in which a node can have
arbitrarily many children.
Oracle implements a thing that's similar to a B*-tree. A B*-tree is
structurally indistinguishable from a B-tree. They differ only in
properties of the insertion and deletion methods used to manipulate
them. For complete information, see Knuth's The Art of Computer
Programming, Volume III: Sorting and Searching, pp473-480.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
-Original Message-
Sinardy Xing
Sent: Thursday, October 30, 2003 10:39 PM
To: Multiple recipients of list ORACLE-L
Hi Thai,
B-tree is short for binary tree, Indexing method make use of Binary
search function to fast retrieve your records, therefore require sorted
records.
B+ tree (I don't know this one, never heard)
Go to www.Oracle.com download the document for free.
Reading order:
1. Concept
2. SQLPlus
3. DB Admin
4. Backup and Recovery
5. Network
After you finish all of these you have basic skill, you can be an Oracle
DBA.
Good luck.
Sinardy
-Original Message-
Sent: 31 October 2003 11:49
To: Multiple recipients of list ORACLE-L
Hello all,
I am looking for documents saying how Oracle uses file organizations
like B-tree, B+ tree, heap file, index file . in their database.
If you know where I can get those documentations, could you let me know?
Thank you.
Thai
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: how to get rid of default
Olga Gurevich wrote: I have created a field in a table with a default clause. - f1 number(1) not null default 1. How can I get rid of the default now? Oracle9i SQL Reference Release 2 (9.2) ALTER TABLE column_clauses add_column_clause If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL. ALTER TABLE tbl MODIFY f1 DEFAULT NULL; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: UNIX OT: deleting file starting with --
Ross Collado wrote: Hi, How do I delete a file named --cart ? Thanks, Ross touch -- --card ls -al -- --card rm -- --card ls -al -- --card -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Solved - RE: UTL_RAW and slowness
Raj I'm in :), so let's check what was the real issue, some more items here... Jamadagni, Rajendra wrote: Thanks Vladimir ... your input has made me look at my code again ... Here is relevant portion of profsum.sql output ... profsum Lines taking more than 1% of the total time, each run separate RUNID HSECSPCT OWNER UNIT_NAME LINE# TEXT - --- -- --- -- -- - 3 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 3 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 3 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 3 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr := utl_raw.cast_to_raw(CHR(ntcpchar)); = = Most popular lines (more than 1%), summarize across all runs HSECSPCT UNIT_OWNER UNIT_NAME LINE# TEXT --- -- --- -- - 809.03 86.3 ST_DVDB2STWRITER_PKG_RAJ246 ntcpchar := ASCII(SUBSTR (msg_text, i,1)); 69.297.4 ST_DVDB2STWRITER_PKG_RAJ256 COMMIT; 13.621.5 ST_DVDB2STWRITER_PKG_RAJ248 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),''); 10.131.1 ST_DVDB2STWRITER_PKG_RAJ247 r_chr = utl_raw.cast_to_raw(CHR(ntcpchar)); /profsum This shows that substr must have been the culprit ... I think, the profile *does not* show that. Moreover I'm not quite sure that the cause of the delays was SUBSTR(), but I would like to clarify some points here. Could you guess what's the difference between these two lines of code? l_n := ASCII(SUBSTR(l_s, j, 1)); l_n := ASCII(SUBSTR(l_s, j, 1)); That's ok if you could not. Nobody could. Because nobody knows that are the datatypes of l_n and l_s. And there is *significant* difference between datatypes in PL/SQL. Am I right assuming that msg_text could be CLOB and l_n could be NUMBER? Could it be like that? I think so. Could you please tell me what those datatypes are/were? BTW, why do you think it *was* OK to use SUBSTR() but not SUBSTRB() -- sure, you know the requirements better -- do you tranfer only US ASCII data? BTW I benchmarked your code, extended the strings to 2000 characters and ran each conversion in a loop of 2000 and using utl_raw method turned out to be the fastest. As I mentioned -- do it in 'bulk' if it's acceptable from security point. thanks again for your insight and sample code ... I never knew nor noticed other utl_raw subprograms like utl_raw.copies ... I would suggest to increase the length of the key at least up to 128 bytes. Now due to pipelining my code is very fast and to accomodate a 122 baud feed, I have insert artificial delays in my code. 8:) What's the point to pipeline it? Appreciate your feedback. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Solved - RE: UTL_RAW and slowness
Raj Jamadagni, Rajendra wrote: Why do you think the profsum output is not right? I did not say that profsum output is not right/correct. I said that it does not show what's needed to be seen. Just imagine to run SUBSTR() you call 1 internal function, to run utl_raw PL/SQL engine has to do a lot more. And now we see that due to some unknown reason utl_raw is faster than simple SUBSTR(). To get you some more clue -- enable SQL tracing (waits) for you PL/SQL block. You would see that most of the time your code is waiting for some temp space operations, your next guess will be correct ... it's because of CLOB datatype. So, SUBSTR() (overloaded one) does operate with data which is very expensive. at least it tells me that 83% of my time is spent on the line that does substr() ... right? Right, but it leaded you to the wrong direction of fixing the issue and to the wrong conclusion as well. msg_text is a clob, l_n is number you are right msg_text has to be changed to VARCHAR2(32767) [32767, or something more suitable for your needs] and l_n has to be changed to BINARY_INTEGER. substr() was used because we transfer only US ASCII data. Ok. The point of pipelining was because in my previous version of code, I was experiencing delays due to (or may be due to) substr() operation ... There is no other easy way to split the CLOB and process it. I am comfortable with pipelining, I know it will work in this scenario so I used it, it worked. If it works for you then Ok. Got better ideas? Sure, have a look at the note 61737.1 or into the documentation, there you find an example of 'CLOB piece wise fetch' -- that's what you need. BTW I on a second (9600 baud) feed I was feeding plain_text using utl_tcp.write_text and my colleagues were experiencing slight delays on the monitor even when I was sending 32k characters. So, I tested with sending 8k characters, convert to raw and use utl_tcp.write_raw, my colleagues are happy, they don't want to change it now. Fine, but it has to be tested carefully as well :) Thanks for your feedback, I appreciate it. You're welcome. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Solved - RE: UTL_RAW and slowness
Raj,
Jamadagni, Rajendra wrote:
Read if you are interested ...
Finally I got some time and luckily the largest message to use with dbms_profiler.
And the results shocked me dbms_profiler showed me that instead of utl_raw,
substr() was the culprit. Remember my operation is character by character.
Could you please show dbms_profiler output data? I'd also suggest to remove
everything related to TCP/IP out from the code -- to get the clear picture.
Some questions/suggestions, if you do not mind
. I do not think that you need utl_raw to do byte by byte xor operation -- you
could do it using BITAND -- it should be faster.
. What's the point to do it char by char in general? Do you modify encryption
key making it dependent on each given char in the string? If not why not to
use something like the code below (see r1), hope I did not make any mistake:
VAR r1 VARCHAR2(256);
VAR r2 VARCHAR2(256);
VAR r3 VARCHAR2(256);
DECLARE
r_key RAW(1) := '41'; -- hex
r_key_n BINARY_INTEGER := 65; -- dec
l_n BINARY_INTEGER; -- ASCII of current char
-- string to be encrypted
l_string VARCHAR2(128) := 'AZBYCXDWEVFUGT';
-- its length
l_string_len BINARY_INTEGER := NVL(LENGTH(l_string), 0);
BEGIN
-- string
-- one can define utl_raw.copies(r_key, 128) as a constant, if it's possible.
:r1 :=
utl_raw.substr(utl_raw.bit_xor(utl_raw.cast_to_raw(l_string), utl_raw.copies(r_key,
128)), 1, l_string_len);
-- char by char
:r2 := '';
FOR i IN 1..l_string_len
LOOP
:r2 := :r2 || utl_raw.bit_xor(utl_raw.cast_to_raw(SUBSTR(l_string, i, 1)), r_key);
END LOOP;
-- bitand
:r3 := '';
FOR i IN 1..l_string_len
LOOP
l_n := ASCII(SUBSTR(l_string, i, 1));
:r3 := :r3 || TO_CHAR(BITAND(-BITAND(-l_n - 1, -r_key_n - 1) - 1, -BITAND(l_n,
r_key_n) - 1), 'FM0X');
END LOOP;
END;
/
PRINT r1
PRINT r2
PRINT r3
BTW, you have double conversion to ASCII then back to CHR (lines 6 and 7) --
it's not dramatic but it can be eliminated.
HTH.
1 msglen := LENGTH (msg_text);
2 nCharsSent := 0;
3 p('Encrypting data...');
4 FOR i IN 1 .. msglen
5 LOOP
6 ntcpchar := ASCII (SUBSTR (msg_text, i, 1));
7 r_chr:= utl_raw.cast_to_raw(CHR(ntcpchar));
8 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'');
9 tcpmsglen := UTL_TCP.write_text (gv_tcp_conn, CHR(nenctcpchar), NULL);
10 nCharsSent := nCharssent + 1;
11 IF MOD(ncharssent,128) = 0 THEN
12p('Before Flush ...');
13UTL_TCP.FLUSH (gv_tcp_conn);
14p('Connection Flushed at ' || ncharssent);
15 END IF;
16 --
17 END LOOP; -- FOR i IN 1 .. msglen
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: index full scan over an index fast full scan in an analytic function?
Tanel Tanel Poder wrote: As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) Full scan will search from index root block using branch blocks to first leaf block. And since all leaf blocks have pointers to next and previous leaf block in index, sequentially reading only leaf blocks is sufficient for returning all values in index, in order (keys are ordered inside leaf blocks as well). FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: index full scan over an index fast full scan in an analytic function?
Vladimir Begun wrote: Tanel Poder wrote: FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Looks, like my text recognition engine got a glitch... ignore that message, please. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: index full scan over an index fast full scan in an analytic function?
Mladen Mladen Gogala wrote: B*tree indexes are ALWAY ordered. That's the way they're created and searched. I don't know the difference between full index scan and fast full index scan. I know that the latter is used when the tble rows are not needed. It's an excerption from Oracle8i Designing and Tuning for Performance Release 2, 4 The Optimizer. Full scan This is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. Full scan is also available when there is no predicate, if all of the columns in the table referenced in the query are included in the index and at least one of the index columns is not null. Full scan can be used to eliminate a sort operation. It reads the blocks singly. Fast full scan This is an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. Fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation. It reads the entire index using multiblock reads (unlike a full index scan) and can be parallelized. Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Sounds like both methods are reading all leaf blocks, from start to finish, using multiblock read. I am not aware of any difference between the two methods. This sounds like a question for asktom or ixora (Tom Kyte or Steve Adams). Wolfgang Breitling and J. Lewis might also know. On 2003.10.23 23:14, Larry Elkins wrote: Because when doing an index range scan things are read ordered? Very different from an index fast full scan where blocks are simply grabbed where they might lie? Regards, Larry G. Elkins The Elkins Organization Inc. [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Ryan Sent: Thursday, October 23, 2003 9:34 PM To: Multiple recipients of list ORACLE-L Subject: Re: index full scan over an index fast full scan in an analytic function? why would you not need a sort with a full index scan and need one with a fast full scan? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 5:19 PM function? Possibly to avoid a sort operation (assuming that you might be able to get away with a NOSORT when doing the full index scan)? It might be deciding that the benefit of the multi-block reads for the fast full scan are more than offset by the sort operation that would be needed (and might not be needed when doing the full index scan). Regards, Larry G. Elkins The Elkins Organization Inc. [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: index full scan over an index fast full scan in an analytic function? i cant attach the 10053 trace. it has proprietary info. There isnt much in analytic explain plan either. does anyone know in general why a full scan would be faster than a fast full scan? From: [EMAIL PROTECTED] Date: 2003/10/23 Thu PM 03:09:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: index full scan over an index fast full scan in an analytic function? I have an index on the two columns used in this query. Why would the optimizer choose an index full scan over an index fast full scan? My question isnt why an index is used, but the type of index scan? select * from (select col1, col2, dense_rank() over (partition by col1 order by col2 desc)tab from mytable) where tab = 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: index full scan over an index fast full scan in an analytic function?
Vladimir Begun wrote: Full scan This is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. Full scan is also available when there is no predicate, if all of the columns in the table referenced in the query are included in the index and at least one of the index columns is not null. Full scan can be used to eliminate a sort operation. It reads the blocks singly. To avoid any confusion 'singly' here means logically singly' -- in the reality (modern versions) it looks more like scattered read. Also this 'singly' depends on session settings. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: What is difference between SYSDATE and SYSDATE@! ??
Jonathan SYSDATE can be local and remote -- how to distinguish them?. Same is related to some other functions as well, e.g. USER. Try to do SELECT SYSDATE FROM [EMAIL PROTECTED]; and trace remote session. Oracle engine is smart enough. Mladen, there is no synonym for @#$%!, be more serious :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jonathan Gennick wrote: Fascinating. I've tried: SYSDATE@ no [EMAIL PROTECTED] no SYSDATE@@ no SYSDATE! no and [EMAIL PROTECTED] yes But why? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: What is difference between SYSDATE and SYSDATE@! ??
Mladen Gogala wrote: Mladen, there is no synonym for @#$%!, be more serious :) I promise! Scout's honor! I believe :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Find an unprintable character inside a column....
DROP TABLE table_1;
CREATE TABLE table_1(data VARCHAR2(10));
INSERT INTO table_1 VALUES(CHR(1)||'ABC');
INSERT INTO table_1 VALUES('ABC'||CHR(25));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(30));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(31));
INSERT INTO table_1 VALUES('ABC');
COMMIT;
VARIABLE npc VARCHAR2(33);
VARIABLE np0 VARCHAR2(33);
VARIABLE np VARCHAR2(33);
BEGIN
:npc := '';
:np0 := '';
:np := '';
FOR i IN 0 .. 31
LOOP
:npc := :npc || CHR(i);
:np0 := :np0 || CHR(0);
END LOOP;
:np := '@' || :npc;
END;
/
COLUMN data FORMAT A10
COLUMN dump FORMAT A30
SELECT ROWID
, data
, DUMP(data) dump
, LENGTH(data) - LENGTH(TRANSLATE(data, :np, '@')) numer_of_np_chars
, INSTR(TRANSLATE(data, :npc, :np0), CHR(0)) first_position
FROM table_1
WHERE TRANSLATE(data, :np, '@') data
/
It's not for unicode. FBI could be used as well.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Robson, Peter wrote:
Some people have requested this code, so I thought you might as well all
have the chance to pick it to bits... Its a function called BAD_ASCII, and
it hunts out for any ascii characters with an ascii value of less than 32 in
a specified field. (Acknowledgments to my colleague Keith Holmes for help
with this code.)
Use it as follows:
Where a field called DATA in a table TABLE_1 may contain an ascci character
with a value less than 32 (ie a non-printing character), the following SQL
will find the row in question:
select rowid,DATA,dump(DATA) from TABLE_1
where BAD_ASCII(DATA) 0;
You could use the PK of the table instead of rowid, of course. You will also
note that I select the DATA field in both normal and ascii 'dump' mode, the
better to locate where the corruption is located.
peter
edinburgh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Find an unprintable character inside a column....
See notes, 113827.1, 119426.1, 154880.1. Could be done and done, but not to solve this particular task -- it would be an overkill. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. [EMAIL PROTECTED] wrote: Definitely worth trying if you have a need for it. I don't, and it's more work than I want to do just because I can. *Mladen Gogala* Actually, I was toying with the idea of writing an external procedure that would allow me to call pcre library (PCRE=Perl Compatible Regular Expressions) which would be nice, but then again, the whole perl is available through the set of external procedures, so it wouldn't be very useful. External procedures can be used in the where clause, provided they're declared as deterministic. Actually, it wouldn't be that hard to extend 9.2 database with regular expressions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: UNIX : script help/input
[EMAIL PROTECTED] wrote:
Left pad with zeroes, take a substring, feed it to the handy-dandy
hex/oct/bin/dec converter package - much easier.
Jared, what Oracle edition do you use? I'm asking because you might
want to consider not to use 'handy-dandy' hex/oct/bin/dec converter
package, but TO_CHAR/TO_NUMBER in case it's = 8i -- it would work
faster.
SELECT TO_NUMBER(SUBSTR('0x50AA', -6), 'FM0X') dec
, '0x' || TO_CHAR(170, 'FM0X') hex
FROM dual
/
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: UNIX : script help/input
Jamadagni, Rajendra wrote: unfortunately we _had_ to do it in pl/sql ... it is part of the encrypted feed that we send out to our clients ... it is decoded by a chip. Oh well I am back to array of references ... I'd suggest to consider external C function -- it's faster, it's easier to write especially when you need to work with bits, shifts etc.. I've recently implemented crc32 for some internal project -- works well, obviously faster than SQL, PL/SQL and native comp of PL/SQL code. Native comp. is also good to consider, if it's 9i. Sure, it depends... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: STAT from trace
Tanel, that's not correct. 10046 10053. To get the STATs lines the trace buffer has to be flushed i.e. the cursor has to be closed and the next statement is processed (or user closes the session) -- depends on the nature of the application, types of opened cursors and instance/session settings. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Tanel Poder wrote: Hi! This is the problem, that everything else was identical. If you executed exactly the same query again, it didn't get hard parsed anymore, thus no STAT lines were generated. Either flush shared pool or just add some bogus comment using /* */ into your query to get parsing and STAT lines. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 PM I was creating some trace files yesterday and came across one of these problems that shows up occasionally (then I forget about it). When I ran my query using ALTER SESSION SET sql_trace=true (sqlplus; 8.1.7.4), I got the STAT line in the trace and the associated 'row source' information after running tkprof. When I ran my query using ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' (or with dbms_system.set_ev), there were no STAT entries for my query. There were some for some of the recursive queries. Everything else was identical. What's up? Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: UNIX : script help/input
Jared
Jared Still wrote:
Good! The more the merrier! Welcome to the club.
Oh most definitely.
As I just finished writing a prototype package for assigning MAC
addresses ( we make network stuff - that's a technical term ), I
have endured the agonies of doing hex math in PL/SQL.
I finally bit the bullet and used string manipulation to convert
hex to decimal and do what I needed that way. Couldn't get BITAND
to work properly on very large integers. Besides, doing XOR with
BITAND in PL/SQL is very painful.
BITAND in PL/SQL works for INTEGER/PLS_INTEGER only.
UTL_RAW has an XOR, but it requires RAW values and I didn't feel
like messing with it.
This is all very simple in Perl.
Sure!
Given a MAC of 5AA, with a fixed portion of 500,
it is very easy to determine the variable portion of the address
via $x = 0x5AA ^ 500.
Not quite so simple in PL/SQL.
Globally unique addresses are allocated by the IEEE in blocks containing 2^24
(16,777,216) addresses. In each allocation, the first 3 octects are fixed (e.g.
00-00-0C is Cisco) and the last three octects are variable (e.g. 00-00-00
through FF-FF-FF). The fixed portion of the allocation is known formally as the
Organizationally Unique Identifier (OUI), and infomally as the Ethernet Vendor
ID. Often, the OUI portion of a MAC address is extremely helpful in indentifying
which physical piece of equipment is generating a particular packet.
00-00-50 is for RADISYS CORPORATION, right?
x := SUBSTR('0x50AA', -6);
Then you can use BITAND. However, I'm not saying that PL/SQL is better
than perl -- every language has its own purpose.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: equivalent for isdate, isnumeric
Tanel Poder wrote: Called from where? :) I think Tanel, Mladen and you missed the ironical point -- if it can't be called from SQL why it is a deterministic one? Erm.. ee.. if you put a wrapper function over it which retuns number... (just joking) Ok, I was too short-sighted there. I just checked from v$type_size that boolean still exists and didn't think much more. :) So, RTFM? P.S.: Tanel, I do not need an example, it was a joke. I actually checked whether it was possible to still conduct some kind of working example, but didn't succed in 5 minutes, so be it ;) Forget about :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: equivalent for isdate, isnumeric
Mladen Gogala wrote: Or, the function can be written to return number. It's not so hard to do. If there are problems, please let me know, I'll post the new version of the code, wrapped. Thank you. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: equivalent for isdate, isnumeric
boolean is not SQL datatype and it's unclear what deterministic means here. Mladen Gogala wrote: create or replace function isnumeric(str varchar2) return boolean deterministic as num number:=0; begin num:=to_number(str); return(true); exception when others then return(false); end; / -- Mladen Gogala Oracle DBA -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: equivalent for isdate, isnumeric
Tanel Poder wrote: Boolean is a datatype existing and usable in Oracle. Deterministic is an Oracle way to tell a function is deterministic, i.e. always returning the same result on the same input. Required for FBIs for example. http://tahiti.oracle.com Could you please kindly provide an example of its usage in SQL. Please create an FBI using the original function below (as is, no modifications or wrappers). It would be really intersting how deterministic functionality would work for a PL/SQL function that returns BOOLEAN datatype. Thank you! -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. boolean is not SQL datatype and it's unclear what deterministic means here. Mladen Gogala wrote: create or replace function isnumeric(str varchar2) return boolean deterministic as num number:=0; begin num:=to_number(str); return(true); exception when others then return(false); end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: equivalent for isdate, isnumeric
Khedr, Waleed wrote: Boolean is A PL/SQL data type won't work in sql. Deterministic is nice to have to reduce the number of times this function gets called for the same value. Called from where? :) I think Tanel, Mladen and you missed the ironical point -- if it can't be called from SQL why it is a deterministic one? It was very simple question asked for fun -- Mladen who said: Hey, I'll sue you for using my code. My code is fair and balanced and you cannot use it without paying royalties. :) So, he put deterministic clause then. :) P.S.: Tanel, I do not need an example, it was a joke. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -Original Message- Sent: Thursday, September 25, 2003 10:05 PM To: Multiple recipients of list ORACLE-L Tanel Poder wrote: Boolean is a datatype existing and usable in Oracle. Deterministic is an Oracle way to tell a function is deterministic, i.e. always returning the same result on the same input. Required for FBIs for example. http://tahiti.oracle.com Could you please kindly provide an example of its usage in SQL. Please create an FBI using the original function below (as is, no modifications or wrappers). It would be really intersting how deterministic functionality would work for a PL/SQL function that returns BOOLEAN datatype. Thank you! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Storage Frust....
Piet de Visser wrote: CCCP: USSR: :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: select distinct values
SELECT DISTINCT testid , FIRST_VALUE(name) OVER (PARTITION BY testid ORDER BY ROWID) name , FIRST_VALUE(status) OVER (PARTITION BY testid ORDER BY ROWID) status FROM testing / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. elain he wrote: Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: how to check 32 or 64 bit?
Oracle DBA wrote: Also how to check whether my DB is 32 or 64 bit SQL SELECT dbms_utility.port_string FROM dual; PORT_STRING SVR4-be-64bit-8.1.0 SQL SELECT paddr FROM v$session WHERE rownum 2; PADDR 00038A57CA28 SQL connect ... Connected. SQL / PORT_STRING SVR4-be-8.1.0 SQL SELECT paddr FROM v$session WHERE rownum 2; PADDR 902B51D8 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: wrapping packages
Tanel Poder wrote: Perhaps you're not aware of the way executables compiled on your Solaris and Windows platforms. In detail, not. In general, yes. Ok, I checked, you're correct, wrap isn't only this 40kB executable, uses orancrypt9.dll (100kB) in Windows, this might be the one where encryption is done... The word 'ecryption' is so amazing and enigmatic, probably that's why so many people are 'poisoned'. It shouldn't be that hard to reverse engineer it. It's an extremely commendable plan... (a touch of irony here) :) I've dealt with disassembling before, back in old dos times (disassembling 4kB graphical intros and few viruses :). I don't think this is a hard job to JFYI, people who made 4kb demos do share their code and ideas, in case one's really interested to get into this. do, it's just time consuming - it gets hard when the authors have planted debugger traps and various other tricks into the code that make the crackers life hard (or should I say interesting :) :) I just think you have/had too much time and nothing serious and important to do. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: wrapping packages
Tanel Poder wrote: :) I just think you have/had too much time and nothing serious and important to do. That was the case, back at highschool days... I think you're still there... at least according to your posts. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: wrapping packages
Anyway's Peter is right in some sense as I heard that some Russian guy Those Russians... :) They can do a lot. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: wrapping packages
Tanel Poder wrote: After all, I do have the right to know, which code is executed on my computer (OTOH, I've not read any agreements too thoroughly, when downloading software). I do have the right to know which code is executed on my computer or not execute that code but not hack it to know what's running there. Read agreements :) [and I would not suggest you to discuss illegal things -- 'how to hack' -- it creates wrong impression about you as about an IT person, IMHO. Intelligent IT individual c00l hazker. Believe me, I know what I'm talking about, it's much more cool when you knock the door (in this case [EMAIL PROTECTED]) than someone else knocks your door -- feel the difference (c)] -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: wrapping packages
Tanel Poder wrote: I checked, the wrap executable in 8.0.6 dist for solaris is about 3MB, but for 9.2 in Windows it's only about 40k. Perhaps you're not aware of the way executables compiled on your Solaris and Windows platforms. It shouldn't be that hard to reverse engineer it. It's an extremely commendable plan... (a touch of irony here) Probably the ones who already have cracked the algorithm aren't spreading the knowledge - why should they anyway?! I suggest you to call Oracle legal and discuss this issue and your original plan of fixing it. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
Tell me about it. :) Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Tanel Poder wrote: Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
rahul
You can use the standard technique for that -- hide your sensitive
columns under a view, something like
...
SELECT pkey
, DECODE(SYS_CONTEXT('CTX$SEC', 'ROLE')
, 'CEO', col1
, 'MANAGER', col1
NULL
) col1
...
Where ctx$sec role is a application role based security context. You
can define whatever context you like. Using this approach you can use
one view that covers different user application roles.
Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
rahul wrote:
how would i write a policy which retuns selected columns if the user has
issued select * from tab ???
using views for each user would work, but then.. i would end up with
so many views in the main schema !!! ;-(
On Sat, 23 Aug 2003 12:24:39 -0800, Jamadagni, Rajendra
[EMAIL PROTECTED] wrote :
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
Use RLS ...
Raj
--
--
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Saturday, August 23, 2003 2:34 AM
To: Multiple recipients of list ORACLE-L
list, i'm ikn the process of designing security for a highly sensitive
schema for a bank,
plan:
have multiple oracle users, and use roles, and grant minimum required
privs, all the user/role/privs management coded in the application (with
in
turn would create the db role and user etc)
probolem:
i cannot do a grant select(col1)on tabname to role1, as select grant on
a
column level is not supported, to workaround this i must
1) use views and include all the columns granted seleted privs for a
user,
then give grant select on this view to user.
2) somehow use RLS ??
TIA
-Rahul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: 9iR2, grant select on a column (without using views) using RL
A Joshi, Big/huge segments do not hurt performance, they only consume space. Some queries (operations) against big segments can lead to performance problems. So, I do not think that one should consider segment's size as an immediate performance problem. v$session is not a segment you should worry about. sys.source$ -- no way, it must live in system tablespace. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. A Joshi wrote: Hi, Unrelated question : If the system tablespace is so big would it not hurt performance for queries to all_tables, v$session, dba_segments etc. In such a case : can tables like source$ be moved out of system tablespace and would it make sense. Thank You -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
You would better count how much space those views' definitions consume in your system tablespace. :) Tanel Poder wrote: using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( SQL select owner, count(*) from dba_views group by owner having count(*) 100 order by 2 desc; OWNERCOUNT(*) -- -- APPS_AF 15899 SYS 1410 Is this too many views for you? ;) This is a regular Oracle Applications 11.5.7 installation... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Week - Date function!
I have found out the reasons. Thanks for your information. That's good. P.S.: 27 of Nov is my birthday. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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 -- List of managers
Hello 9i (ORA-01489! be aware): SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, 19)) ename , ename mgrs FROM emp CONNECT BY PRIOR mgr = empno / Eberhard, Jeff wrote: Using the EMP table as an example I want to create a query that will show a list of employees and the mgrs above them. Like this: ENAME MGRS --- -- SMITH SMITH SMITH FORD SMITH JONES SMITH KING .. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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 -- List of managers
Vladimir Begun wrote: SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, typo: ^RTRIM -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Week - Date function!
Hello!
Sesi Odury wrote:
Given a week between (1 - 52) for a particular year can we get all the
dates within that week. Is there a function to do this in SQL???
Using the simple statement below you can get the first date of the week
(according to ISO standard). Then you can either add 6 to get the last
day of the week and use ranges for your task or using any 'pivot'-approach
(you need 7 rows) you can get all 7 days/dates of the week.
DEFINE yr=1998
DEFINE wk=5
SELECT TRUNC(TO_DATE('2711yr', 'DDMM'), 'IYYY') + (wk - 1) * 7 AS date_from
, TRUNC(TO_DATE('2711yr', 'DDMM'), 'IYYY') + (wk - 1) * 7 + 6 AS date_to
FROM sys.dual
/
Ranges can be used in case one does not have a possibility to use FBI, for
example.
The statement below is a bit more complicated. This one does a simple
check and returns nothing in case week number is out of range.
DEFINE yr=1998
DEFINE wk=53
SELECT TRUNC(TO_DATE('2711yr', 'DDMM'), 'IYYY') + (wk - 1) * 7 AS date_from
, TRUNC(TO_DATE('2711yr', 'DDMM'), 'IYYY') + (wk - 1) * 7 + 6 AS date_to
FROM sys.dual
WHERE TO_NUMBER(
TO_CHAR(
TO_DATE('3112yr', 'DDMM')
+ DECODE(TO_CHAR(TO_DATE('3112yr', 'DDMM'), 'IW')
, '01', -7
, 0
)
, 'IW'
)
) = wk
AND wk 0
/
HTH[, if I did not make a mistake].
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Trolling for ideas
Dennis, ask your developer to enable simple tracing (in case there is no trace file on the server), figure out what statement fails, run it locally on the server using sqlplus/srvmgrl -- you can expect some more meaningful error message that is probably not handled properly. Another idea is to enable client side network logging, however there is problem here your mentioned that it's not a consistent error therefore trace file can be quite huge. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. DENNIS WILLIAMS wrote: I have a developer that wrote a VB program using ADO, connecting to Oracle 8.1.6 on a Compaq Tru64 server. One program of his runs for hours each night, and sometimes receives an ORA-03113 end-of-file on communication channel. Not consistently, just sporadically, and at varying amounts of time. I have been unable to find anything in the server logs. We have traced the program and the error occurs during different SQL statements. Followed most of the tips I've located on solving ORA-03113 errors. I am coming to the conclusion that maybe the only solution will be to upgrade Oracle and hope that solves the problem. Unfortunately we can only upgrade to Oracle 8.1.7.4. Does anyone have any other ideas? Thanks. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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
Jared Jared Still wrote: Though not a dramatic difference, the CONCAT was faster and less resource intensive than the inline view with GROUP BY. :) Ok, let it be like that, but your test does not check some other things, like common sense, logic, and session memory. Performance can vary as I mentioned sometimes can be neglected, however let's consider the tricks you made before your test: 1. create index emp_idx on emp(ename, job, mydate); -- what for do you need it? It was not in the original problem definition. It's not used, however you created it, what's that for? 2. Both queries give different results, that's what I mentioned -- you just proved my words :) One must be very careful with that. Even DISTINCT can lose sometimes like in your example, but it does not mean that the logic of the application works correctly in case of || = CONCAT is used. Think also about an artificial limit your create -- each and every varchar has to be padded to it's maximum length (become CHAR) -- that optional and case dependent, however; all date and numeric columns have to be formatted otherwise you can face the same case like your your example. SQL SELECT COUNT(*) FROM emp; COUNT(*) -- 64000 SQL select count(distinct(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL SELECT COUNT(*) 2 FROM ( 3SELECT DISTINCT 4 ename, job, mydate 5FROM emp 6 ); COUNT(*) -- 7000 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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
Jared Windows 2k 9.2.0.1 534 hsecs 214 hsecs Query I've used: SELECT COUNT( DISTINCT( RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1)) || RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1)) || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*') ) ) AS l FROM emp / As you can see it's tightly bound to table definition one has to handle nulls for varchars/chars. L - 7000 Check the resources -- I have doubts that this query is a winner :) So, the moral of this story: . never trust Vladimir Begun, check everything what he's saying :) . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its performance with that one that works correctly but slowly. Thanks! Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Vladimir Begun wrote: Jared Jared Still wrote: Though not a dramatic difference, the CONCAT was faster and less resource intensive than the inline view with GROUP BY. :) Ok, let it be like that, but your test does not check some other things, like common sense, logic, and session memory. Performance can vary as I mentioned sometimes can be neglected, however let's consider the tricks you made before your test: 1. create index emp_idx on emp(ename, job, mydate); -- what for do you need it? It was not in the original problem definition. It's not used, however you created it, what's that for? 2. Both queries give different results, that's what I mentioned -- you just proved my words :) One must be very careful with that. Even DISTINCT can lose sometimes like in your example, but it does not mean that the logic of the application works correctly in case of || = CONCAT is used. Think also about an artificial limit your create -- each and every varchar has to be padded to it's maximum length (become CHAR) -- that optional and case dependent, however; all date and numeric columns have to be formatted otherwise you can face the same case like your your example. SQL SELECT COUNT(*) FROM emp; COUNT(*) -- 64000 SQL select count(distinct(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL SELECT COUNT(*) 2 FROM ( 3SELECT DISTINCT 4 ename, job, mydate 5FROM emp 6 ); COUNT(*) -- 7000 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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
Jared [EMAIL PROTECTED] wrote: . never trust Vladimir Begun, check everything what he's saying :) Trust? I don't know you well enough to not trust you. May be 'trust' is not a right word here :) Sorry. . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its performance with that one that works correctly but slowly. As you will see in another post, both queries return identical results for me on 8.1.7. Check default NLS_DATE_FORMAT parameters of your session. To summarize, agregation can be done using: 1. (for this particular case) SELECT COUNT( DISTINCT( RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1)) || RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1)) || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*') ) ) AS l FROM emp / 2. SELECT COUNT(*) FROM ( SELECT DISTINCT ename , job , mydate FROM emp ) / 3. SELECT COUNT(COUNT(*)) FROM emp GROUP BY ename , job , mydate / 4. SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; unreliable solution (does not handle nulls and dates properly) SQL SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL ALTER SESSION SET NLS_DATE_FORMAT='DD.MM. HH24:MI:SS'; Session altered SQL SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 6000 Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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
Looks like you are a master of telepathy too... :) Khedr, Waleed wrote: What about: select count(count(*)) from emp group by ename, job Have fun :) We do... :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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
[EMAIL PROTECTED] wrote: I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. elegant = simple, concise, easy to understand. Looks elegant to me. Jared, it just looks that that... CONCAT = || yet another function call, yet another piece of code, yet another byte of memory... If you have more than two columns? If some of those are numeric, date? If ename is Smith and job is Smith and both can be nullable? :) NVLs? NVL2s? I think this approach is only valid when one really understands what she/he is looking for. Could be good for FBI, CHECK constraints but it's very risky and resource consuming (depends, can be neglected) for queries. It's better to write something that just looks ugly but works faster and reliably. Simple, fast, and covers all 'strange' cases: SELECT COUNT(*) FROM ( SELECT DISTINCT ename , job FROM emp ) / Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Global Stats
It's not correct, check metalink -- there is a note about that. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. K Gopalakrishnan wrote: Kirti: I think the interval is changed to 5 minutes from 3 hours starting from 9i (rel2?). Best Regards, K Gopalakrishnan -Original Message- Kirti Sent: Wednesday, January 29, 2003 8:19 PM To: Multiple recipients of list ORACLE-L Lisa, Monitoring, by itself, does not fire any automatic analyze. It simply montiors the DML activity on the monitored table and counts inserts/deletes/upates. Those counts may not be 100% accurate, but are very close. These can be viewed in dba_tab_modifications, and are dumped there by SMON every 3 hours or so (in 9i there is a new procedure, flush_database_monitoring_info, to flush these counts to this view on demand). These counts do not affect the ones maintained in *_tables views. Monitoring is basically there to help identify which tables may need statistics computed again. 'Gather stale' option will only analyze tables that have undergone DML activity (inserts/deletes/updates) that amounts to more than 10% of the number of rows (from previous analyze) in the table. And 'gather auto' option 'figures' out what tables to analyze, but you must execute dbms_stats. So, there is nothing automatic in gathering table stats. You can test it yourself. remember there is a last_analyzed column ;) HTH, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: One potential problem with DBMS_JOBS as is being discussed here is that Oracle computes the next_date at the end of the job. They do that so that if a job -- INTERVAL is a date function, evaluated immediately before the job starts -- executing... runs longer than it's schedule interval the two invocations will not run into each other. Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: dbms_job - running jobs every 15 minutes
[EMAIL PROTECTED] wrote: Now as discussed, if the job is scheduled to start at 9:00 AM and runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it will creep 5 minutes every time. No, as written, my jobs start on every quarter hour, regardless of runtime. e.g. 09:00, 09:15, 09:30, 09:45 ... Jared, I wanted to ask this question before but just provided a solution w/o talking too much :) If it's regardless of runtime (it means potentially one job can consume more than 15 minutes to get things done) is it allowed to run jobs concurrently? In case of positive asnwer, you need 4 jobs. Otherwise in case of more than 15mins runtime you'll face slipped jobs. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: dbms_job - running jobs every 15 minutes
Stephane Faroult wrote:
Vladimir (whose formula I am still trying to understand :-))...
TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 60 / :interval) / (24
* 60 / :interval));
P.S.: could you please answer my question ('100% CPU utilization,
urgent') thread?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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 unusual behavior
John Shaw wrote: I bounced the databases and just like rebooting a pc (even though this db is on solaris) it started working - one of the mysteries of Oracle - Maybe it was really windy and it was having sympathy pains with the yacht. Dunno, I think the policy was enabled, all myths and mysteries are in our minds, a piece of software is either working or getting glitches. The rule: never reboot anything (your car is included :) unless you know the root of the original problem. :) Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 100% CPU utilization, urgent
Stephane Faroult wrote: I hate converting between decimal and hexadecimal :-). You can use TO_CHAR for that :) I am afraid age is showing ... Did not get you. Your routine is fine except the fact it can be faster. When I was 12 it was my first asm (ix86) exercise -- hex2dec/dec2hex routines. ;) Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: dbms_job - running jobs every 15 minutes
Jared in general it's much more better to use bind variables, here I do not use them, shame on me! :) Hope the code below is ok for you at least it does not look so complicated. VARIABLE jobno NUMBER; VARIABLE plsql VARCHAR2(1000); EXEC :plsql := 'BEGIN statspack.snap; END;'; BEGIN dbms_job.submit( :jobno , :plsql , TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 900) / 96) , 'TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, ''S'') / 900) / 96)' ); COMMIT; END; / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. [EMAIL PROTECTED] wrote: Feeling particularly anal the other day, I used the following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the hour. variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' -- every 15 minutes at 00,15,30 and 45 , trunc(sysdate,'hh24') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) , 'trunc(sysdate,''hh24'') + ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' ); commit; end; / Seems to me that the time specs could be simplified a bit. Anyone care to give it a go? :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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 unusual behavior
John Shaw wrote: I am trying to update a small table from a remote table with sqlplus 9.2.0.2 . It seems to indicate that it has inserted 233 row into my local table - however that doesn't really happen. Am I suffering from a severe lack of caffine or is this really odd? SQL select count(*) from facility; 1. FGAC? Connect as sys and check. 2. Could you please show explain plan? 3. What's in the trace file? -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Get Owner of Trigger or Table in Trigger
CREATE TABLE trg_test (p NUMBER);
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT -- ...
ON trg_test
DECLARE
ls_owner sys.v_$access.owner%TYPE;
BEGIN
-- dbms_output.enable;
SELECT a.owner
INTO ls_owner
FROM sys.v_$session s
, sys.v_$access a
WHERE s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
AND s.sid = a.sid
AND a.object = 'TRG_TEST'
AND a.type = 'TRIGGER'
;
-- dbms_output.put_line('Owner of the trigger is ' || ls_owner);
END;
/
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Post, Ethan wrote:
Anyone know how to get the owner of a trigger inside a trigger without using
the stack dump shown here by Tom Kyte which would still need some work to
get just the owner name.
http://groups.google.com/groups?q=get+table+owner+in+trigger+oraclehl=enlr
=ie=UTF-8oe=UTF-8selm=337efeab.1901213%40newshostrnum=1
I have the same trigger in same database in different environments
(DEV,TEST,QA) and the trigger needs to send info using UTL_FILE to different
directories based on which environment the trigger is in. Refreshes from
production are automated but the production trigger get put in these other
environments and I want the same trigger to run everywhere without
modification. Current plan is to figure out the owner and set the path for
UTL_FILE based on that.
Any ideas.
Thanks,
Ethan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: BCHR Tuning
1997, in Vejle, I had an interesting meeting with Bjørn Engsig about Sybase-Oracle migration. Denmark... small Great country. Nice, kind and friendly people who work diligently in peaceful atmosphere -- I love it. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Rachel Carmichael wrote: oh you tempter! I'd love to go back to Cophenhagen, Tivoli will be fun in May. If I get on a plane right after IOUGa and don't bother to stop and do laundry, I could make it. My boss would kill me though. I do plan on attending that presentation at IOUG. No way I can give up the chance to sit in the front and ask innocent questions. --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: Obviously, we don't know what we're talking about. I can see there's a presentation by Rich Niemich at IOUG-A where he'll address all those idiots who are saying you should ignore the Cash Hit Ratio (and who are all just after making big money on their products - I loved that one). Well, to be on the safe side he's also written a very bad article (it's even - amazingly - got the NAMES of the typical waits wrong) about waits in Oracle Magasine. The editor can be excused. But what I truly love is the writing at the bottom of the article, with very small print, stating: Editing help: Steve Adams. Right. Steve would write that kind of stuff and get the wait names wrong. Yep. As a consulting company we here at Miracle are delighted: We expect a lot of calls from customers who cannot locate these events or find events that are not mentioned in the article. Heh-heh. I love it. I'm just really sad I can't be at IOUG-A. Here's another idea: Why don't you all come to my 42nd birthday, which we'll celebrate on May 2nd (a Friday) here in Maaloev, Denmark? You're all welcome, and we'll find ways to let you sleep either in the Garage (our HQ) or my house. Then we'll do some cool presentations in the afternoon and celebrate in the evening. Let me know when you planes land in Copenhagen airport and I'll pick you up. Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: PL/SQL Date Format
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_dateIN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy DATE;
BEGIN
ld_dummy := TO_DATE(p_expire_date, '-MM-DD');
IF (TO_CHAR(ld_dummy, '-MM-DD') = p_expire_date)
THEN
dbms_output.put_line('Modified value: ' || TO_CHAR(ld_dummy,
'-MM-DD'));
ELSE
dbms_output.put('Err:');
dbms_output.put('p_expire_date=' || p_expire_date || '');
dbms_output.put_line(',ld_dummy=' || TO_CHAR(ld_dummy, '-MM-DD'));
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Something is wrong');
END set_expire_date;
/
SET SERVEROUTPUT ON
EXECUTE set_expire_date(1, 'TEST', '0001- 1-01');
EXECUTE set_expire_date(1, 'TEST', '01-01-01');
EXECUTE set_expire_date(1, 'TEST', '2001-13-01');
EXECUTE set_expire_date(1, 'TEST', '12-2002-01');
EXECUTE set_expire_date(1, 'TEST', '2002-12-01');
EXECUTE set_expire_date(1, 'TEST', ' 01-01-01');
EXECUTE set_expire_date(1, 'TEST', NULL);
EXECUTE set_expire_date(1, 'TEST', '01-JAN-03');
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
[EMAIL PROTECTED] wrote:
Ok, try this one. It's a little smarter. :)
create or replace function df1
( date_in varchar2 )
return date
is
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
if owa_pattern.match(date_in,'^\d{4}-\d{2}-\d{2}')
then
null;
else
raise_application_error(-2,'Hey! Thats a bad date!');
end if;
return to_date(date_in, v_source_date_format);
end;
/
show errors function df1
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: Replacing control chars
Deshpande, Kirti wrote: Spasibo balshoye :) :) It's an adequate answer. I've a draft somewhere [I need to find it], very 'roughly' transalted in English, do you want to read it? -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -Original Message- Sent: Friday, January 03, 2003 5:09 PM To: Multiple recipients of list ORACLE-L JFYI, (it's in Russian and PL/SQL :)) http://www.oracle.com/ru/oramag/june2001/index.html?begun.html Be careful with standard.replace (9i) and recursion in PL/SQL. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Extremely Slow Query
Jonathan Lewis wrote: The problem is generic, the specific query isn't the point. RAC is a massive improvement on OPS because block transfer is by wire not disc - but it still takes a serious amount of time to fling blocks from node to node, especially if the blocks have been subject to very recent update at the remote nodes. Thanks, Jonathan! Sorry for misunderstanding. Got it. I thought you are talking about this particular query -- looks like you've hit the big problem with RAC -- sounded like it was not before however it's here now. One of the main concept of RAC/OPS is understanding application(s) workload and assign/partition it to/among different nodes. So, here we have a good example of the (unavoidable) functional clash. Raj, can you try this one: SELECT /*+ LEADING(dba_types.t) INDEX(dba_types.o, i_obj3) INDEX(dba_types.so, i_obj3) */ * FROM dba_types / In this case it's better to scan indexes. I like both your explanations for the size, and the unusual number of obj$ blocks that needed CR serving. ;) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Replacing control chars
JFYI, (it's in Russian and PL/SQL :)) http://www.oracle.com/ru/oramag/june2001/index.html?begun.html Be careful with standard.replace (9i) and recursion in PL/SQL. Connor McDonald wrote: From AskTom ... Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: ORA-1410 Silliness
Lisa
. I've read some messages of this thread -- there is 100% probability :)
that you incorrectly identified the statement which errors 1410.
PL/SQL engine could not point to the line 1970 -- it's in the middle
of the statement -- something is strange there. Do you handle exceptions
in your code? I can bet it's raised from the exceptions handler block.
Another 100% probability -- you do use GUI(?), which incorrectly shows
PL/SQL code lines? :)
ACCEPT l1 PROMPT 'From line: ';
ACCEPT l2 PROMPT 'To line: ';
ACCEPT l3 PROMPT 'Obj: ';
COLUMN LINE FORMAT 9;
COLUMN TEXT FORMAT A70;
SELECT line
, text
FROM user_source
WHERE name = UPPER('l3')
AND line BETWEEN l1 AND l2
ORDER BY type, line
/
. Inline view in the example is just an illustration how indexes can
point to the wrong rowid...
. Did you enable tracing? Do it -- you'll find *everything*.
Kind Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Koivu, Lisa wrote:
Vladimir,
Thanks for your reply.
I have tested the cursor. It does not include any bind variables.
There are no broken rowids, as all objects passed analyze ... validate
structure cascade.
I also tested the scenario you describe in your code below. The code
does break with that error, however there are no inline views in my code.
Lisa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: ORA-1410 Silliness
Jamadagni, Rajendra wrote: Oracle doesn't, has never been able to pinpoint exact line number (in Can you please prove it? Do not you think that this statement contradicts to the sql query you provided below. :) There is quite some difference with GUI which pulls/[s]pools PL/SQL code out and PL/SQL engine. There can be some bugs, for sure, but there are no bugs in PL/SQL (read ADA) concepts of line numbers handling. cases such as these) especially with pl/sql packages. I believe the problems can be found by executing following query ... select line, type, source from user_source where name = your package_name) and line between 1960 and 1980 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Extremely Slow Query
RAC/OPS? A guess: obj$ is a very popular table (and possibly RAC-caches-wide-spread one ;) -- some its blocks were not in the local cache. Does it take 2-3 mins everytime you launch the query? Probably somebody else is doing some manipulations with obj# actively creates/alters/drops objects? Check related RAC statistics regarding to global cache and V$CACHE_TRANSFER. Probably it's not an obj$ but the wait and FTS look suspicious. To speed up the query you might want to use user_types or write our own to avoid FTS. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jamadagni, Rajendra wrote: Does any know how to speed up following query? Select * from dba_types / It is taking about 2-3 minutes on my 9202 database. I see a lot of Global Cache waits. The hammer shows following information ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Extremely Slow Query
Jonathan Lewis wrote: Looks like you've hit the big problem with RAC - Do you think it's really the big problem? I'm just thinking about Oracle APPS instance or something alike. Somebody could start object recompilation (maint. pack/adadmin), synonyms creation or whatever like that on a node. Meanwhile full obj$ scan is/will go on on other node. How often people do FTS of obj$ on OLTP systems? Raj, do you need that query? :) how many nodes do you have, how busy are the nodes which are supposed to supply with with CR copies across the interconnect, and what's the latency and bandwidth of your interconnect ? (And how did you get that many objects into obj$ !!!) There is 80% probability that many of those are/were synonyms or that system is fully synonyms based. I have no other idea -- 463566 looks really cool. Raj, what's that? P.S.: Do I have a good imagination? :) Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: ORA-1410 Silliness
Lisa . Enable SQL tracing . Launch your code . Identify (exactly) the cursor which fails with ORA-01410 and what bind vars are. . Pull out the statement from your code . Run it in 'standalone' mode . If it fails identify rowids which look broken. Check the phys. entities those rowids point out. Are those phys. entities Ok? . Any access BY ROWID in your statement is a potential problem. . You might want to dump error stack too but I suggest to contact oracle support first. Try to make a test case as simple as possible, it definitely would help. There is a simple scenario, may be it can give you some ideas (index_s is a simplified index simulator): DROP TABLE index_s; DROP TABLE tbl; CREATE TABLE index_s ( rid ROWID ); CREATE TABLE tbl ( pNUMBER ); INSERT INTO tbl VALUES(1); INSERT INTO index_s SELECT ROWID FROM tbl; SELECT * FROM tbl WHERE rowid = ( SELECT rid FROM index_s ); DROP TABLE tbl; CREATE TABLE tbl ( pNUMBER ); INSERT INTO tbl VALUES(1); COMMIT; SELECT * FROM tbl WHERE rowid = ( SELECT rid FROM index_s ); -- However this works well: SELECT * FROM tbl , index_s WHERE tbl.rowid = index_s.rid / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Koivu, Lisa wrote: Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at ELVIS.CLEANUP_VEGAS_PK, line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: this doesn't look right
Rachel DBA_CONS_COLUMNS is a view. DECODE is applied against attrcol$.name -- which is varchar 4000. Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Rachel Carmichael wrote: As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: this doesn't look right
Rachel Carmichael wrote: I know it's a view. I'm just curious as to why one is varchar2(30) and one is varchar2(4000) when the column names in a constraint are the same ones in a table and should, in theory, be the same size Rachel, I've replied Rodd's message with self-explanatory example, it's in this thread: http://www.mail-archive.com/oracle-l@fatcity.com/msg61930.html Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Was: Rebuilding Indexes, Now: KEEP INDEX
Connor Connor McDonald wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) As it's done now it can be useful for some real life cases, some of them can be very rare. However keep option was introduced, so it could mean that somebody spent at least some time to design it or make a stub(?), right? I think the reasons are: . To have consistent statement semantics (DROP / KEEP) . Perhaps this functionality will be extended in the further Oracle RDBMS editions . Performance of Exchanging Partitions can be improved, please refer to docs/metalink. . In case some apps. table reorganization needs to be done e.g. primary key is based in unique index, however the constraint has to be extened -- suppose an application is going to support multi-organization feature or whatever else as a temporary solution KEEP index can be used -- I suspect that it sounds like not well thought design but life is cruel -- sometimes is good to have indexes... Thread participants have provided some examples already. Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: unable to create stored outline for sql inside a procedure --
Shaleen Def.Rights: Roles can be enabled or disabled -- an unit must not be dependent on the enabled/disabled roles. There is nothing bad to have such design. This design is well thought, IMHO. At least at it's [was] consistent [on the moment of its invention]. Inv.right Due to the context switching inv.right program units are a little bit (simplified) more expensive to be managed than def.rights. Such units require some more development efforts and accuracy (internal/external names). 2) To take care of this problem invokers rights facility was introduced. Then why this restriction on roles. The advantage is reusable and manageable code but not just the problem with roles. Def.rights units have their advantages too -- the biggest one, IMHO -- no 'context switching'. Stored Java stuff is also based on inv.right facility. Kind regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Shaleen wrote: Hmm. Makes sense. Thanks Tim. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 2:34 PM I don't agree that anyone shirked. Roles are, by design, changeable within a session. The SET ROLE command is not DDL, altering the metadata of the database. Instead, it is only altering already-granted permissions to used subsequently by the session. So, why should permanent objects (such as views, procedure, packages, triggers, etc) be created using permissions which are inherently transitory (i.e. available via roles)? Just because very few people use SET ROLE during a session doesn't alter its basic properties... When that note says that complexity would be raised to the Nth degree, they are not necessarily indicating that Oracle could not have implemented it. This stuff is simplicity itself compared to the transaction-consistency model. Rather, the complexity would have been on the database administration side (not in the database engine), and a major pain in everyone's behind. Think it through. Oracle made a good design decision to prevent unnecessary complexity in database administration. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: how to make PL/SQL wait for 60 seconds
On Jul 20, 2001 at 04:46:13AM, John Dunn wrote: I want to put a sleep or wait in my PL/SQL function Is there an easy way to do this? dbms_lock procedure sleep(seconds in number); -- Suspend the session for the specified period of time. -- Input parameters: --seconds -- In seconds, currently the maximum resolution is in hundreths of -- a second (e.g., 1.00, 1.01, .99 are all legal and distinct values). -- Vladimir Begun | Falling in love is a lot like dying. You http://vbegun.net/ | never get to do it enough to become good at http://vbegun.net/wap/ | it. [EMAIL PROTECTED]| -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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: Extent allocation
On Jul 20, 2001 at 07:50:44AM, Adrian Roe wrote: Hi All, Is there any way to get Oracle (816) to do round robin extent allocation eg. if a tablespace has 4 data files and each file is on a different disk, can extents be allocated from each file in sequence ? As I understand, Oracle will fill one file and then go onto the next file. Try this http://vbegun.net/oramag/extents_allocation/round_robin.sql Inform me please if it's helpful. Thanks. Have a nice weekend. -- Vladimir Begun | I think $[ is more like a coelacanth than a http://vbegun.net/ | mastadon. http://vbegun.net/wap/ | -- Larry Wall in [EMAIL PROTECTED]| [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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: Tracing
On Jul 16, 2001 at 06:45:36AM, Libal, Ivo wrote: Hello I would like to start tracing for different session than my one. How it is possible in 8.1.7 EE? I want to start tracing for different sessions with different levels (not necessary diff. levels). I found that it should be possible with dbms_support package, but i didnt find this package and I also didnt find creation script in my rdbms/admin directory (it should be dbmssupp.sql). Please help me where I can find it or how to do it. Ivo Libal dbms_system.set_ev(sid, serial, event, level, NULL); -- Vladimir Begun | Without freedom of choice there is no http://vbegun.net/ | creativity. http://vbegun.net/wap/ | -- Kirk, The return of the [EMAIL PROTECTED]| Archons, stardate 3157.4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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: Tracing
On Jul 16, 2001 at 07:12:00AM, Vladimir Begun wrote: On Jul 16, 2001 at 06:45:36AM, Libal, Ivo wrote: Hello I would like to start tracing for different session than my one. How it is possible in 8.1.7 EE? I want to start tracing for different sessions with different levels (not necessary diff. levels). I found that it should be possible with dbms_support package, but i didnt find this package and I also didnt find creation script in my rdbms/admin directory (it should be dbmssupp.sql). Please help me where I can find it or how to do it. Ivo Libal dbms_system.set_ev(sid, serial, event, level, NULL); Forgot to mention: 10046 as a value for event. -- Vladimir Begun | Be consistent. http://vbegun.net/ | -- Larry Wall in the perl man http://vbegun.net/wap/ | page [EMAIL PROTECTED]| -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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: find free space under HWM for a table
on Jul 11, 2001 at 11:30:18PM, Chuan Zhang wrote: Hi, DBA gurus, Recently, I will archiving some big tables. How to find free space under HWM for a table? Do analyze and then you can calculate it: dba_segments.blocks - dba_tables.empty_blocks - 1 Be aware the blocks which are below HWM can be empty because of preallocation or delete activities. -- Vladimir Begun | echo Congratulations. You aren't running http://vbegun.net/ | Eunice. http://vbegun.net/wap/ | -- Larry Wall in Configure from [EMAIL PROTECTED]| the perl distribution -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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).
