RE: measuring TPM
Charlie, I understand a transaction as a succession of SQL statements between two successive COMMITs or ROLLBACKs - you will find inside V$SYSSTAT how many COMMITs and ROLLBACKs were issued. If you are interested, besides transactions proper, in the number of statements executed, then have a look at 'execute count'. You also have stats to tell you how many of them were recursive statements I believe. Talking about metrics (and forgetting about what you have been asked to provide :-)), methinks you can have a reasonably fair (and balanced) view of what is going on by collecting six values : o Number of sessions and number of executions to see what users are asking of your database o Redo blocks written to see the 'update' activity and the number of bytes sent which roughly tell you what users want to be done o Physical and logical I/Os to see how efficiently it is done Discrepancies should trigger investigation. HTH, Stephane Faroult - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 28 Jan 2004 07:29:25 I've been asked to provide value for the Transactions Per Minute going through our primary OLTP production database. I believe I can use deltas in SCN values to measure transactions which do INSERT/UPDATE/DELETE and then COMMIT; Is there any way to measure/count the number of SELECTs which occur? If so, how? How would you derive a value for TPM for your DB? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ** field names of a ref cursor
AFAIK there are no PL/SQL functions to do what you want to do; they are available as OCI functions, though, but PL/SQL only implements a very small subset of what is available with OCI. An external C procedure might be an option, but only if not called too often. In my experience trying to write fully generic procedures is more often than not a recipe for unmaintainable code and hard to predict performance. I would possibly be simpler to identify the various cases you may have and write small wrapper procedures. HTH, SF - --- Original Message --- - From: A Joshi [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 25 Jan 2004 22:19:27 Hi, I am calling a Oracle stored procedure which has a ref cursor as a out param. I get the values. Is there a way for me to know the field names and type of the fields being passed in the ref cursor. Basically when the procedure sends back the ref cursor what information do I get apart from the data. Do I get any metadata (field names types etc). This is basically to write a calling program which I do not have to change when the ref curor /record changes in the stored procedure. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Views for a table
The only privilege you can grant to yourself : GRANT RTFM TO user [WITH GRANT OPTION]; Igor Neyman wrote: Mladen, Any privileges required to view this table, or just common sense? :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mladen Gogala Sent: Friday, January 23, 2004 1:24 PM To: Multiple recipients of list ORACLE-L The table you want to look into is USER_OTN, Column DOCUMENTATION. On 01/23/2004 12:49:34 PM, Mauricio V?lez wrote: Hi everybody I have the following question How can I query a table's views? For example I have the table students and I want to know the views related to this table. Thanks, Mauricio V?lez - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT : Happy Spring Festival
... to whomever is concerned ... Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What gives??
[EMAIL PROTECTED] oriole]$ export AWK=awk '{print \$4}'
[EMAIL PROTECTED] oriole]$ echo ${AWK}
awk '{print $4}'
[EMAIL PROTECTED] oriole]$ cal | awk '{print $4}'
We
7
14
21
28
[EMAIL PROTECTED] oriole]$ cal | ${AWK}
awk: cmd. line:1: '{print
awk: cmd. line:1: ^ Invalid char ''' in expression
[EMAIL PROTECTED] oriole]$ cal | ${AWK}
bash: awk '{print $4}': command not found
[EMAIL PROTECTED] oriole]$ cal | eval ${AWK}
We
7
14
21
28
[EMAIL PROTECTED] oriole]$
Nikhil Khimani wrote:
LG,
I know there is a simple solution to this .. but I can't think of it right
now. Any help will be appreciated ...
Thanks,
Nikhil
=
[ny-nikhil1:/export/home/nkhimani/bin]$ export AWK=awk '{print \$4}'
[ny-nikhil1:/export/home/nkhimani/bin]$ echo ${AWK}
awk '{print $4}'
[ny-nikhil1:/export/home/nkhimani/bin]$ cal | awk '{print $4}'
W
7
14
21
28
[ny-nikhil1:/export/home/nkhimani/bin]$ cal | ${AWK}
awk: syntax error near line 1
awk: bailing out near line 1
[ny-nikhil1:/export/home/nkhimani/bin]$
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nikhil Khimani
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Re: Re[2]: Oracle vs Mysql
[snip] 120 col. punch cards? You had a high-density model. Mine only had 80 cols, of which 72 were usable for my goto-happy Fortran statements. SF No hard drives? My $0.02 worth, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 8:39 AM Careful Mladen, your revealing your age!! Bet you remember RPT RPF as well!! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 20, 2004 2:04 AM To: Multiple recipients of list ORACLE-L On 2004.01.19 23:39, Jonathan Gennick wrote: I used to use a SQL Module compiler. Not with Oracle though. It's rare for me to run into someone else who likes that approach. Actually, it's rare for me to encounter someone who's even heard of it... Jonathan, I've been around for a long time. I've seen things like DataLens for Lotus123, SQL*Calc, Easy*SQL, then there was an Oracle version of then popular DB2 tool, which looked like an IBM 3874 terminal on top of VT320, SQL*Graph does deserve a honorable mention, then there was PRO*Pascal, and a myriad of other exotic stuff that I cannot remember now. I was laughing when I saw UNDO TABLESPACES in 9i. What exactly is a difference between a specialized undo tablespace and a file that was just laying around and couldn't be touched and was named Before Image file or BI file. Logical names (another concept that many youngsters are probably unfamiliar with) were usually VAX$BI or ORACLE$BI. Unfortunately, discussions like that are not part of OCP curriculum. The file is not really part of the database, you can't create any objects in it, it manages itself and it stores the old values of oracle blocks, in case rollback is needed. I could be talking about BI file or UNDO TABLESPACE, there is no difference whatsoever. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- --- -- --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pga_aggregate_target and a memory leak
[EMAIL PROTECTED] wrote: One of our production DBAs does not want to use pga_aggregate_target on a 9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995 doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont know version offhand. he is under the impression that if we patch to 9.2.0.4 this goes away. not sure about that either... Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Renumber a set of grupped rows?
Maryann Atkinson wrote: I have a 1-rows table with 2 columns, Emp_ID and Req_ID. There are about 150 different emp_ids in these 1+ records. What I want to do is the following: For every different Emp_id, I need the Rec_ids that corresponds to it to be updated/renumbered starting from 1 and keep going up by 1. So I want it to look something like this: Emp_ID Req_ID 10001 001 10001 002 10001 003 10001 004 10001 005 10001 006 10001 007 10001 008 10002 001 10002 002 10002 003 10002 004 10002 005 10003 001 10004 001 10004 002 10004 003 10004 004 10004 005 10004 006 etc Any ideas? Thanks, maa MaryAnn, The row_number() analytic function was invented for you. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What to look for in STATSPACK report
Helmut, Performance, like beauty, is in the eye of the beholder. Ideal thresholds only exist in poor tuning courses and poor tuning books. IMHO, the wisest thing to do would be to collect information at a time when performance is perfectly satisfactory and use it as a baseline. Then check when there is some _significant_ difference with your baseline. If nobody complains, it means that you can probably allow pretty wide variations for some values. However, if users do really notice (and it's not purely psycho-somatic, as it sometimes is) some degradation, it will be time to enquire. But bear in mind that the most significant indicators will not necessarily be the same ones for all applications, nor even for all times of day. Also, be careful to collect some information about the actual, business-related work being done. I have seen people complaining that they didn't have the same performance as 6 months earlier and forgetting that they were processing twice as many invoices or whatever. Granted, a well-written application should scale. But at least it helps you explain even the most Oracle-challenged manager that it isn't a simple tuning matter (it will be harder to explain that it isn't a question of throwing more hardware to the problem either, but this is another story). HTH Stephane Faroult - --- Original Message --- - From: Daiminger, Helmut [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 18 Jan 2004 22:24:36 Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL server and JOB
Try to have the SQL Server DBA fired. - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 16 Jan 2004 04:59:26 Hi , How can i create a job in sql server ? Rgds. Arslan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how to hide oracle password from a unix ps -ef | grep?
Google for hide.c -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Who is Melanie Craft?
I have just done an Amazon search, I guess that Melanie Craft's book 'A Hard-Hearted Man (Intimate Moments, No 870)' will bring some terrific change to my usual reading list. Although one of her other masterpieces, 'Trust Me' looks terribly reminiscent of the Oracle doc, doesn't it. SF (almost time to go home) - --- Original Message --- - From: Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 15 Jan 2004 07:29:56 Well, at least he didn't marry Lara Croft. That would do him in faster then Bill Gates. On 01/15/2004 09:19:26 AM, KENNETH JANUSZ wrote: Melanie Craft is a romance novelist and the fourth woman to be known as Mrs. Larry Ellison. This is from an article is today's WSJ print edition page B4. Mrs. Ellison also has a web site although I don't know the address. Mrs. Ellison is 34 years old and Larry is 59. My $0.02 worth, Ken Janusz, CPIM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Table access
'),
upper(substr('2',
instr('2','.')+1
and grantee# in (select 1
from dual
union all
select privilege#
from sys.sysauth$
connect by grantee# = prior privilege#
start with grantee# = u.user#
union
select u.user#
from dual))
or u.name = decode(instr('2', '.'),
0, sys_context('USERENV',
'CURRENT_SCHEMA'),
upper(substr('2', 1,
instr('2','.')-1
-- Check that the object exists !
and exists (select null
from sys.obj$ o,
sys.user$ u
where o.owner# = u.user#
and u.name = decode(instr('2', '.'),
0, sys_context('USERENV',
'CURRENT_SCHEMA'),
upper(substr('2', 1,
instr('2','.')-1)))
and o.name = decode(instr('2', '.'),
0, upper('2'),
upper(substr('2',
instr('2','.')+1
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Export / Import Question
Ron, I share your feeling. All stored objects are recreated with CREATE OR REPLACE - IGNORE=Y is inoperant for them. IMHO the best you can do is generate as many table-level exports as you have tables, with TRIGGERS=N. Of course, usual fun with constraints. On the bright side, you will be able to parallelize like crazy. At this stage, dumping to a flat file and using sqlldr might be an even better option than imp/exp. HTH S Faroult - --- Original Message --- - From: Smith, Ron L. [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 09 Jan 2004 06:09:26 I have a user who want to refresh only the DATA in a test database with DATA from the production database. He does not want to replace any procedures, functions, triggers, etc... My question is, if I do a full or user level export, then turn around and do a full or user level import with IGNORE=Y (after truncating the tables) will the procedures, functions, triggers be replaced anyway? I have a feeling they will. If so, is there any way to prevent this? Thanks! R Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Problem with understanding Optimization methods.
To what Mladen said about the optimizer_blah parameters, there is another thing which
can be added. Most third-party software is over-indexed (as you have noticed) because
they optimistically want to cover *all* possible cases and quite often a column which
is highly discreminant at one site contains a single value elsewhere and so on. What I
mean is that it is common to have totally skewed distributions, and AFAIK collecting
column statistics for the indexed columns isn't the default. Try it, the optimizer may
then have more information, and pick intelligently the index which it picks randomly
in rule mode. Especially if you follow Mladen's advice about cost adjustment.
HTH,
SF
- --- Original Message --- -
From: Mladen Gogala [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tue, 06 Jan 2004 22:09:24
You can find out by employing the event 10053, lev
8. Looking from afar, however, it seems more
likely that you haven't configured your CBO
properly. Here is something you can try:
Execute the following commands:
alter session set optimizer_index_caching=40;
alter session set optimizer_index_cost_adj=25;
After that, retry the query. If I'm correct,
optimizer will now know that index I/O is much
cheaper
then the table one and will be much more likely to
select full index scan over the full table scan.
When you're really, really bored, you can read
Practical Oracle 8i - Building Efficient Databases,
it has a few pages about the parameters above.
Read the Gospel of Jonathan and enjoy.
On 2004.01.07 00:29, Denham Eva wrote:
Hello Listers,
A normal sql query from a data warehouse tool
called Sagent.
SELECT COL1, COL2, COL3
FROM TABLE
ORDER BY 3;
The table has approximately 2 mil records.
table has 22 indexes.
The database is set up optimizer CHOOSE.
I run
DBMS_Stats.Gather_Schema_Stats('SchemaName')
regularly.
OS is Win2k
ORACLE 81741
OK, when doing a explain plan on the above sql, I
get the following...
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT ORDER BY
TABLE ACCESS FULL TABLENAME
-- Very slow and takes
hours!
When adding the hint /*+RULE*/ for example I get
SELECT STATEMENT Optimizer Mode=Hint:RULE
TABLE ACCESS BY INDEX ROWID
TABLENAME
INDEX FULL SCAN
TABLE_INDEX --
Much faster!!!
Have I given enough info that anyone can explain
why the CHOOSE mode insists
on doing a TABLE ACCESS FULL?
Is there anything I can do to improve
performance? Please remember that this
query comes from a Data Warehouse tool and hence
does not appear to accept
hints.
Any help will be much appreciated!
Denham
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: table reorganizations
Shrake, Jolene wrote: What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene Jolene, If your tables are reasonably sized initially, very few reasons may justify a reorganization (moreover, the mere size of some tables rules it out from the start ...). The only reasonable cases are substantial chaining, when there is no 'good' reason for that (ie if a row can fit into a block), which you will see if you collect statistics in DBA/USER_TABLES, and the other one is a high water mark in a table which is supposed to normally contain few rows. This one is harder to check, the easiest is probably to SET AUTOTRACE under SQL*Plus and run something like SELECT /*+ FULL */ COUNT(*) and check how many blocks (consistent gets + db block gets) were visited. If it's very high compared to what you would have normally expected, reorganizing may be necessary. But this only affects tables in which you can have massive deletes. Your second question gives the impression that you consider full table scans as a bad thing, which they are not necessarily. What is bad is what is much slower than it could be, and occurs too often for comfort. One of the places you can check is V$SQL; With Oracle 9.x, make sure that timed_statistics is set to TRUE et looks for statements with the highest elapsed_time. For older versions, buffer_gets is a good indicator. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: table reorganizations
Or in yet other words, is it worth spending two or three days (preparation + actual reorg - preferably on a sunday morning between 2 and 4am) on an inherently risky operation to shave 0.01% off response times ? Nobody will notice, or hardly. There is certainly much more to be gained checking queries which are run. Now, if you can identify with certainty that a critical query would significantly benefit from a reorg, do it. HTH, SF Rachel Carmichael wrote: The point of these questions is... why do you think you have to reorganize the tables? Define a hole. How does it get created? Is it ever filled in (as in, do you ever insert rows)? Do you ever delete or update? Figure out WHY you want to do something before you try to solve it. or, in the same vein as the prior posts if it ain't broke, don't fix it --- Shrake, Jolene [EMAIL PROTECTED] wrote: I'm surprised at these responses. I'm asking what sql statement most people use to identify tables that need reorganization because of holes. We had an Oracle consultant here and he uses Select table_name, blocks-((num_rows*avg_row_len/block_size)*(1+(pct_free/100))) blkdiff From dba_tables Where blkdiff 100; To determine reorganization need. What sql statement is used by others? Jolene -Original Message- Sent: Wednesday, January 07, 2004 2:25 PM To: Multiple recipients of list ORACLE-L I usually recommend Gospel by Jonathan for its completeness and a wide range of subjects. The book you mentioned is great for beginner as well. As for the number 42, I'll continue using it until this Saturday (1/10/2004) when it will become 43. Inflation is not as big as you think. PS: --- I was born on 1/10/1961, and that makes January 10th so special. I don't have to work on that great day, mostly because it's Saturday. On 01/07/2004 03:09:53 PM, Thater, William wrote: Mladen Gogala scribbled on the wall in glitter crayon: Lemme guess: you just started on your new job as a DBA? You are another person to which can only wholeheartedly recommend Jonathan's book. As for your questions, the answer is 42. actually, if she's just starting out, i'd recommend Marlene, Rachel and Jim's book first, then Jonathan's. and are you sure it's not 57 now due to inflation? -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to remove PL/SQL successfully completed Message
- From: Mudhalvan, Moovarkku [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 06 Jan 2004 00:49:25 Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. a) Make the procedure fail -- or -- b) set feedback off SF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
Mark, This is what spontaneously comes to my mind and may not make a lot of sense in your case, but why do you need TWO bitmaps in the first place? Your second (Permission) bitmap uses 2 bits to store three states (no permission/P1/P2). This is enough to hold the STORAGE information as well, eg 00 Not stored 01 Stored with no permission 10 Stored with P1 11 Stored with P2 I of course assume, and may be wrong on this account, that storage is a pre-requisite for permission. Otherwise I would use 24 bits. I presume that your bitmaps don't appear as such, and therefore whether you have one or two fields is pretty irrelevant to the end-user. It looks to me easier to understand and maintain that some Rubik-cube-like bit-twiddling. HTH, SF - --- Original Message --- - From: Bobak, Mark [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 06 Jan 2004 01:39:25 Hi, Well, since I can't sleep, I may as well try solving a problem. This is a bit odd, and I'm trying to think of the most efficient way to do it. I've set up some bitmaps in my app. Consider we have documents that we want to sell. In order to be able to sell a given doc, we need to have it stored in the vault and we also need to have negotiated the proper contract w/ the publisher. So, I've got two bitmaps, STORAGE and PERMISSIONS. But, here's the hook. There are 8 different types of storage, so I have an 8 bit mask. However, for every storage type, there are two types of permission. So, I have a 16 bit permissions mask. What I'd like to do is take my 8-bit STORAGE mask, say it's 10110011 and convert it to 1100. Note that all I did there was take each bit in the input mask, and make the same value repeat. So, 0 would become 00, 1 would become 11, 10 would become 1100. Does that make sense? Once I've done that, I can take my STORAGE mask that's now stretched to 16 bits, and directly AND it with my PERMISSIONS mask. So, my question is: Is there a nice, scalable way to take my 8 bits and expand them into 16 bits, in the way that I'm describing? A clever bit twiddling expression would be perfect, but anything that's efficient and scalable will do. (The end product will be hidden behind a view or stored procedure.) Any thoughts, anyone? Thanks in advance, -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE:
Mark, All right, I see it better. If I were you I would try arithmetic methods. To take your example, when you start with 10110011 it means power(2,7) + power(2,5) + power(2,4) + power(2,1) + power(2,0) What you want is nothing else than 3 * power(4,7) + 3 * power(4,5) + 3 * power(4, 4) + 3 * power(4, 1) + 3 * power(4,0) ... if I don't err. In other words, if you consider your 1s to mark powers of 4 instead of 2 and multiply by 3 instead of 1 each time, you have your expansion. I let you write the PL/SQL function ;-). HTH SF - --- Original Message --- - From: Bobak, Mark [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 06 Jan 2004 02:34:27 Hi Stephane, I of course simplified the problem a bit. However, the bitmaps are actually artificial entities which I'm building up from some underlying tables and some clever indexing schemes. The problem is, I don't have total data model control, and the permissions and storage info are stored in two different tables. Through some clever indexing and views, I'm presenting two different bitmap views. The idea is I can do a unique lookup on storage, a unique lookup on permissions, and then AND them together and provide a resultant bitmap, availability. The front-end app will then use that availability bitmap to decide what options to present to the end-user. The problem is the heterogeneous nature of the two bitmaps. Finally, I'm fully utilizing the bitspace I have, because the 2 bits for permission represent two different (and independent) pieces of info. In the example you provide below, there is no way to express P1 AND P2 with storage. In other words, my permissions matrix looks like: 00 - No permissions 01 - Permission for user to acquire doc via email 10 - Permission for user to acquire doc via on-line viewing 11 - Permission for user to acquire doc via email or on-line Note that the bitmap is full, and storage has not entered into the picture yet. So, Storage will say either 0, not stored or 1, stored. I need to AND the permissions and the storage. But, I need to AND the single bit storage with two bits of permissions. Does that make more sense? -Mark -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED] Sent: Tue 1/6/2004 5:13 AM To: Multiple recipients of list ORACLE-L Cc: Subject:RE: Mark, This is what spontaneously comes to my mind and may not make a lot of sense in your case, but why do you need TWO bitmaps in the first place? Your second (Permission) bitmap uses 2 bits to store three states (no permission/P1/P2). This is enough to hold the STORAGE information as well, eg 00 Not stored 01 Stored with no permission 10 Stored with P1 11 Stored with P2 I of course assume, and may be wrong on this account, that storage is a pre-requisite for permission. Otherwise I would use 24 bits. I presume that your bitmaps don't appear as such, and therefore whether you have one or two fields is pretty irrelevant to the end-user. It looks to me easier to understand and maintain that some Rubik-cube-like bit-twiddling. HTH, SF - --- Original Message --- - From: Bobak, Mark [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 06 Jan 2004 01:39:25 Hi, Well, since I can't sleep, I may as well try solving a problem. This is a bit odd, and I'm trying to think of the most efficient way to do it. I've set up some bitmaps in my app. Consider we have documents that we want to sell. In order to be able to sell a given doc, we need to have it stored in the vault and we also need to have negotiated the proper contract w/ the publisher. So, I've got two bitmaps, STORAGE and PERMISSIONS. But, here's the hook. There are 8 different types of storage, so I have an 8 bit mask. However, for every storage type, there are two types of permission. So, I have a 16 bit permissions mask. What I'd like to do is take my 8-bit STORAGE mask, say it's 10110011 and convert it to 1100. Note that all I did there was take each bit in the input mask, and make the same value repeat. So, 0 would become 00, 1 would become 11, 10 would become 1100. Does that make sense? Once I've done that, I can take my STORAGE mask that's now stretched to 16 bits, and directly AND it with my PERMISSIONS mask. So, my question is: Is there a nice, scalable way to take my 8 bits and expand them into 16 bits, in the way that I'm describing? A clever bit twiddling expression would be perfect, but anything that's efficient and scalable will do. (The end product will be hidden behind a view or stored procedure.) Any thoughts, anyone? Thanks in advance, -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San
RE: SQL*net message from client severly impact the Parse call of an insert statement
John, These are just a couple of ideas coming to me (I haven't checked the attachments, answering to this through a web interface). First of all, having a _whole_ process much slowed by parsing proves, if nothing else, that you are doing too much of it. If it happened very few times you wouldn't even have noticed it. I am far from being a Java expert but there is the equivalent of bind variables with JDBC (forgot how it's called), your developers should use them. There is also the quick and dirty fix of forcing cursor sharing for the session, but don't mention it to them too quickly. Second, I am surprised by the kernel waiting for some client information in the parse phase. What does it need from the client side to parse? It has everything; unless, perhaps, it's the CBO requiring information about the session's own environment? But this resides on the server host too. The only explanation which makes sense to me is that parsing occurs as a separate phase, before execution, and that the wait is between parsing and execution. In C the 'parse' call can be either immediate (requiring one round-trip on its own) or delayed (parsing occurring when execution is requested). You probably are in the first case, and should ask your developers to do whatever is needed to be in the second one. HTH S Faroult - --- Original Message --- - From: Hatzistavrou John [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 30 Dec 2003 23:34:25 Dear All, I am faced with the following situation. Oracle 8.1.7.4. 64 bit , Solaris 8 There is a loader java process that when is executed against a test database(dwdsa)the response time is as expected to be. However when it is executed against the production instance (dwods) it is 2,5 to 3 times slower. I have traced the session on both occasions and reading the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from client is very high for the PARSE call of an INSERT statement, whereas for the test instance there is no delay. I cannot however explain what might be the cause of this. Can somebody sched some light into this problem. Attached please find the SQL 10046 trace with level 8 a) Production trace (parser_dwods.zip) b) Test trace parser_dwdsa.zip Kind Regards, Hatzistavrou Yannis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: getting estimate of result set from v$sql_plan
[EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: order by
[EMAIL PROTECTED] wrote: Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete vs. truncate to free up spaces.
Nguyen, David M wrote: I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David David, Several solutions. If you have paid for the partitioning option, you can truncate partitions. Otherwise, it may be faster to do a CREATE TABLE AS SELECT with nologging, parallel, blahblah, to select all the lines you want to keep, truncate the table, and INSERT ... SELECT back in the same manner. Better to have your database in restricted mode then. I used this method in a (euro-zone) bank to delete everything but CHF, GBP and the like when we waved farewell to national currencies, worked pretty efficiently. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Career Advice
Believe somebody who first learned SQL back in 83, it's too late for Java now. Run-of-the-mill skill. Any young grad will know it and will be less expensive. ERP would be a good bet, because people learn them at work, mostly. Now, would a company change be justified just for that? Probably not. As you said, you are hired for what you know, not what you want to learn. Grasp opportunities, learn whatever looks to you useful - and fun. My 0.02 EUR. SF [EMAIL PROTECTED] wrote: learn java and object oriented programming. go to sun.com and start reading the java docs. go to www.bruceeckel.com and read his java book. do a search on any job sites. a ton more work for java than oracle. people who can do both are in demand. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/12/17 Wed PM 01:49:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Career Advice Have you ever considered a career in country music? Try getting Stand By your man just right and the rest will come. You have to learn both kinds of music, country and western. May Jake and Elwood be with you. On 12/17/2003 12:44:28 PM, Saira Somani-Mendelin wrote: As an applications analyst/junior dba, I feel I need to learn more but I'm not sure of the direction I should take, so I'm asking for advice. Should I become interested in Oracle Apps? Or should I learn another suite like SAP or Siebel or PeopleSoft? The difficulty is that my company does not use any of these. We use a smaller package by Tecsys called Elite and they don't have as many customers - or should I say, as many customers with deep pockets. I know I can get my hands on a working copy of SAP, what about the others? I believe you can purchase an evaluation copy of Apps from the Oracle Store. Has anyone actually tried to train themselves on any of these products? Has anyone installed Apps at home for testing? Sorry if this question has been presented on the list before. Thanks, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Who are certified Oracle Masters?
Ryan wrote: you mean niemic didnt actually have to take the test? Scary, eh ? (couldn't resist) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Documenting databases
Alan, The IT departments of several sites, hitherto fairly independent, have all been brought under a single roof at one of my customers and as a result a lot of databases have fallen into the herd of databases we had to manage there. IMHO the key point to inventory is automation; if you don't automate, it will never stay up-to-date. First of all, get hold of some platform for scripting. VERY VERY SMALLI don't know perl,/VERY VERY SMALLMICROSCOPIC I don't even plan to learn it any soon/MICROSCOPIC and as I feel comfortable with ksh, sed, awk and the like I jumped on a Unix platform, but your choice may be different. The first challenge in our case was to build an inventory of databases (asking people is totally unreliable); I have used scripts from Tim Gorman which you will find on his site (http://www.evdbt.com) - from a security paper, which I have reworked to suit my case. The idea was to probe the network (fortunately all servers are supposed to follow a special address pattern) and check for listeners, and send the lsnrctl stat command. This helps you identify servers, listeners, and instances. A suitable schema was built into a database (Oracle, but see below) to store this; note that relationships are sometimes not very simple, since a same instance can be served by several listeners. Next step was to secure a foothold into each database to execute inventory queries (it has been a good opportunity to check security too). DBSNMP/DBSNMP is a good bet. Actually, we created a special MONITOR account on each database, with only the minimum rights required. Everyday a script runs, which checks V$DATABASE, V$INSTANCE, V$LICENCE, V$VERSION (the only place BTW when you find some indication about which OS you are running on), getting information and updating it if required. Storage is of course checked as well. Database links are collected too. We have a PHP application displaying all the information (with the refresh date), conveniently crossed (for instance, we list for each database the dblinks to the database as well as the dblinks from the database). We have some summary PDF reports (storage, databases per OS, per version, etc.) which are printed every week. We are also linking to a (static) inventory of applications. It's still work in progress. We have recently added a connection test every 15mn to check database availability (trying a non-existent user. If we don't get ORA-1917 we try to ping the server and tnsping the listener to pinpoint the reason for the problem - of course we skip the other databases on the server if we can't ping it) and compute some availability percentage figure. We also intend to collect some metrics at regular intervals to have an idea about the load. I have nothing against using Access to store the data; in fact, some of the ideas were borrowed from another customer where the repository is a Sybase database (TCL scripts do a full inventory of both the Sybase and Oracle databases - several hundreds of them). But, once again, do it AUTOMATICALLY. HTH Stephane Faroult - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 09 Dec 2003 15:34:32 Dan, That's a good idea for documenting structures inside the database. However, my database manager wants more high level info: database name / host, oracle version, listeners, applications that use it, cron job descriptions and times, main schemas and what they are used for, lists of developers names that access the databse, etc... Alan Daniel Hanks [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] c.com cc: Sent by: Subject: Re: Documenting databases [EMAIL PROTECTED] .com 12/09/2003 04:09 PM
RE: Programmatically redefining authid setting on packages
Adam, No, I don't think you can do it through DDL. For for regenerating and modifying the code, the simplest to use is DBA_SOURCE. HTH, SF - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 03 Dec 2003 19:59:25 Folks, Is there a way to change the authid setting of a package via a simple procedure, e.g., alter package x authid current_user; I believe this is a compile-time setting, part of the source of the package (user_source, dbms_metadata output), which leads me to believe there's no simple way to redefine this short of recreating the package completely. But let's say we have several hundred packages to perform this on -- any easier way to accomplish this? Obviously I could use various dbms_metadata/substr/instr/replace/concatenate combinations, or Perl, or any other s//-like mechanism to generate the updated package text, but I'm open to other ideas, or perhaps a command/tool I'm missing. Adam -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: an article comparing Oracle to other databases
Jared, It is no longer MySQL the hot product. You have 9,000 times faster than Oracle http://www.prevayler.org/wiki.jsp?topic=ScalabilityTestResults got there from a link on Fabian Pascal's site. You can guess he is unimpressed ;-) ... You can get any answer indeed. Just ask the question which matches the answer you want. Stephane Faroult Jared Still wrote: So is MySQL, supposedly. These things are usually very subjective, and rarely objective. Jared On Thu, 2003-11-27 at 08:49, Ryan wrote: where do they get these 'speed' indicators from? the article says that ibm is faster than oracle? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 27, 2003 11:04 AM http://www.ecommercetimes.com/perl/story/32200.html I have yet to see an objective, detailed comparison of Oracle, DB2 and SQL Server. From a technical (i.e. what can it do) as well as from an organisational (i.e. how is it to manage) point of view. Even 3rd party think tanks seem to walk on egg shells when evaluating software from major vendors, possibly to avoid alienating any of them. Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bad SQL day...help please
Saira, Depends on the level of detail you want. Select sku from ... group by sku, quantity, order_id having mod(count(*), 2) != 0 does it. SF Saira Somani-Mendelin wrote: List, Please excuse the content of this question. I haven't had a breakthrough yet so I'm hoping for some assistance... it may seem trivial to some but for some reason I am SQL-ly challenged today. I have a table which holds historical transaction records. Each PICK or RPCK record should have a corresponding SHIP record with a match on quantity, sku, and order_id. I have to create an exception report where if for any PICK/RPCK record there isn't a corresponding SHIP record, I should be shown the PICK/RPCK record. In other words, each sku has records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2 PICK/RPCK records, then 2 SHIP records. I know what I want in English, but I'm having trouble designing the query in SQL. In the table below, you can see that SKU 117127 has a PICK record but no SHIP record, same case for SKU 701206. Is someone kind enough to offer me some SQL advice? Thanks in advance, Saira OB_OID SKU TRANSACTQTY 50340 115227 RPCK36 50340 115227 SHIP36 50340 115304 RPCK36 50340 115304 SHIP36 50340 174040 RPCK12 50340 174040 SHIP12 50340 177127 PICK36 50340 177144 PICK24 50340 177144 SHIP24 50340 177624 PICK24 50340 177624 SHIP24 50340 177634 PICK48 50340 177634 SHIP48 50340 19 PICK20 50340 19 SHIP20 50340 20020 RPCK6 50340 20020 SHIP6 50340 701079 PICK100 50340 701079 SHIP100 50340 701206 RPCK30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Disadvantages of Remote Servers
Valerie, It depends on who you are, how remote is remote and the OS you are using. I guess that if you are a production DBA, the farther away from development the better. If you are a developer, you may be blocked by those mundane IT problems which spice our otherwise so dull lives, ie wrong version of that, cannot access this, and so on. Either you have the password, or you have to ask another guy to fix things for you. And experience proves that the farther away the guy, the longest it takes, especially if you have never met him. Can be frustrating. 'Remoteness' can also be a problem. I have installed from a Paris, France, office a database in Tokyo, Japan. 'Ping' takes 300 ms, and echo is slow (forget about anything but telnet, but anyway I consider graphical environments as a nice way to open multiple telnet sessions and that's all). Certainly cheaper than flight+hotel, but if it's OK for something you do once, you can't work on a regular basis in such conditions. And finally the OS. Anything starting with W is a pain to work with in a remote environment. My EUR 0.02 SF - --- Original Message --- - From: WEBBER, VALERIE [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 26 Nov 2003 06:29:25 What are the disadvantages to managing/deploying applications with a remote iAS server? Are there significant reason to try to have it local to the development? Thanks in advance! Valerie Valerie H. Webber Valspar Corporation 7930 W. Kenton Circle Huntersville, NC 28078 800-241-4568 ext. 6313 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: xref table - design consideration
Barbara, I totally agree with what Jared said. You should, if your customers have attributes in common (I guess that the address where you send the invoice is a good common attribute to start with :-)), have a common, basic 'customers' table (as seen in the sales rep's eye, somebody you can bring a commission) with a type which tells you what kind of customer you have - whence where to look for the specific attributes. This is where your lookups will take place. But I don't see why you want a trigger. And rather than storing the primary key from each of the 3 tables, you should use as primary key of those tables distinct subsets of the primary key of the 'customers' (as defined above) table. HTH, SF Barbara Baker wrote: List: We're trying to design a CRM app. We believe we need 3 tables (Prospect/Customer, Private Party, and Agency) because those 3 kinds of (potential) customers have different attributes. The sales rep should know whether they're looking up cust, private party, or agency. But what if they don't? (They're sales, after all. What if the have a hangover?) For performance reasons, we'd prefer not to join all 3 tables for a lookup. I was thinking about 1 cross-reference table with the primary key from each of the 3 tables stored in one cross-ref table. Any way to keep such a table updated other than with a trigger? Any other ideas about how to do a quick lookup without 1 big join? In case you can't tell, db design is NOT my forte. Thanks for any ideas! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pl/sql question and owa_pattern question
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Re: IOT Tuning Question
Zhu Chao, You are right to say that with a heap organized table you also have the index to encumber the SGA and indeed you are right to say that, as I put it, what I said is not totally correct. I should have been more specific. The reference to _partitioned_ IOTs implicitly associated them to full partition scans in my mind, because the case I was referring to was some massive swoop among a lot of data, with many scans. In such a case, then indexes in general, and IOTs in particular, tend to stay much longer than required in memory, which may become a problem over time with long running processes (while table blocks are prime candidates for replacement after full scans). Quite obviously, if you are doing mostly indexed accesses, the picture may be different. I don't think that with 3 columns, unless they are well-filled VARCHAR2(4000) columns (you never know, with 3rd party software ...) overflow will be much of a problem. I'd rather fear contention, but of course it depends on the level of concurrency. SF - --- Original Message --- - From: zhu chao [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 17:50:11 Hi, Jay: Since your table is just a table with 1M records and you have only three columns(all of them are pk), so using IOT is really an good candicate. The space save is not important because 1M records with three columns typically consumes several megabytes, which is not important at all these days. If your table is heavily DMLed, then using IOT reduced the DML to the base table, so less IO generated and less redo. I do not think SF's words are correct. IOT is indices, right. But Regular tables with indexes also consumes memory in SGA, and the index on the regular do the same thing as IOT table does. And the base table itself also consumes SGA memory. Overflow in IOT(oracle 8i) is just heap organized, in 9i it is also index organized(from my test), so if your table has overflow segment, and you insert more and more data into the table, IOT *WILL* be less efficient and you need to move the overflow segment to make the table efficient. Regards. zhu chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:30 AM Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Compare the *size* of different schemas
Linda, When I saw your reference to 'cloning' below, I first thought you were taking physical copies of your files. Perhaps this is something you might want to consider in the future. For one thing, you just have to add up file sizes to see how much space is required on the target machine. With exp/imp I think that you would make your life much simpler using first COMPRESS=N when exporting and then just checking total sizes. You will no longer need big contiguous chunks - having as much space on the target database as on the source database will usually be enough. I don't see why you want to get down to the extent level - it looks like an overkill. HTH SF - --- Original Message --- - From: Seley, Linda [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 21 Nov 2003 07:14:43 I'm wondering if someone has a better solution than mine (see below) to the following: We have a number of schemas that get cloned from our production schema (more on that later). I need to be able to compare the size of the production schema to the target schema and determine how much the objects have grown. The part I'm working on/struggling with is how to determine whether or not the target tablespace has enough space to handle the additional space required. What I have so far: I have a statistics database that I've modified to store object information for each schema (which database/tablespace it came from, how big it is now, how big the next extent will be, etc). I've also got the query that says this schema is x bytes/blocks larger in the production database than the target database. From this I'm able to figure out how many extents will be needed in the target database to handle the size growth. My problem: I can't just compare the size of the next extent to the largest free chunk in the tablespace. While that's useful information it won't alert me if I've only got room for one extent but will need two. If the target schemas were refreshed regularly then this might work since any given object should not have extended more than once (or a small number of times) but sometimes weeks or months go between refreshes. Along the same lines I can't add all of the extents and try to fit them in the total free space because the blocks may not be contiguous. (We have a mixture of extent sizes, I'll convert someday, really I will!) In addition, if there are 5 tables that have grown I'd like to be able to determine if table 1 is going to use up all of the free space and tables 2-5 won't have enough space to extend. Etc. My 'best' solution: Build a table of existing free space for each target database/tablespace and do mock updates attempting to mimic Oracle's behavior then, from that, determine if I will run out of space. This seems cumbersome and time-consuming but it's the only reasonably accurate solution I've come up with. Does anyone have a better idea? Has anyone done something similar? Some background about our environment: Currently we're exporting/importing to get the production data into the other schemas. In some instances we drop the tables first then import, in others we truncate then import. In the future some of the tables that are being truncated will be incrementally updated (unless the structure changes then they'll be dropped and re-imported). The table structures are identical, in general the initial and next extents are identical but that isn't true for all objects. The target schemas are used for development, test, reporting, etc. Thanks for taking the time to read this! Linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seley, Linda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: IOT Tuning Question
Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Development vs. Production DBA
George, Early involvement and advice are certainly in my view essential to the success of a project. However, concerning the creation of packages, etc. I fear I don't share your views. Involvement is justified if it adds value. If it's just adding another layer of red tape, forget about it. I think that DBAs should _review_ installation scripts, especially those creating tables, indices, constraints (I sometimes dream of meeting a developer aware of the 'using index' clause), not necessarily to _run_ them but to check that they satisfy local standards; and if they don't, they should be returned to the sender for correction. If you correct scripts and run them, you'll have to do it again and again with each release. We have a duty to teach developers :-). Concerning procedures, if you are yourself a competent PL/SQL developer and can review the code and tell people how they can do it better and faster, great. But many competent DBAs are not necessarily competent developers themselves - and I don't think that they have to be. I don't see where having stored procedures created by DBAs on a development database can improve development quality or speed. I see more added value creating a suitable environment (generating a realistic volume of data, creating and administering the suitable roles, creating synonyms to allow people to work on separate parts of a project without having multiple copies of the same database, helping with version control, helping with developing performance monitoring tools, etc.) than running scripts. In many ways, regularly meeting the project manager at the coffe machine may prove more fruitful. My $ 0.0238 ... SF - --- Original Message --- - From: Rusnak, George A. (SEC-Lee) CTR [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 19 Nov 2003 07:50:21 Group, If this was discussed before, I missed it. There is a discussion going on trying to define the duties of a development vs. production DBA and where in-depth DBA involvement should occur. Is there any papers that anyone can share w/me on this subject. IMHO a DBA should be involved early on in the project to translate the functional requirements into a physical model using the features of the target version. I also think that it should be the DBA's job to create the packages, procedures and triggers in the development and testing phases. To me,this would facilitate the transition from testing to production. Our development DBA's are involved in the production side so are aware of our standards. Comments, opinions please. TIA Al Rusnak DBA - WEB Team/CISIS, Computer Operations * 804-734-8371 * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why is Oracle process using 25 MB of RAM when idle?
Helmut, I don't know how you are measuring your numbers, but beware that what the operating system reports is often somewhat misleading. Typically, shared memory is often 'attributed' to each and every process linked to it. When you think about it it makes sense, but at the same time it does mean that n processes will really use much less than n * the amount of memory reported as used by one process. This is true both of the 'program' part of user memory (shared libraries) and of the 'data' part of it (SGA). When your process connects, it attaches the SGA and some shared libraries, and more shared libraries come into play as it starts doing something. You may have a better view of what is really used by your process by checking into V$SESSTAT, which holds a number of values about it. HTH, SF - --- Original Message --- - From: Daiminger, Helmut [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 16 Nov 2003 23:09:26 Hi, we are running 9.2 on HP-UX here. We have pg_aggregate_target configured, but I realized (in my opinion) very high memory consumption of Oracle Unix processes. a) How come that one Oracle Connection (i.e. dedicated Unix process on HP) is using up at least 22 MB of RAM? It is using 22 MB if the user is just connected, not doing anything. Any way I can modify this? b) If the user is querying data and the like, the memory consumption goes up to 60 MB. How come? Thanks! Regards, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Why is Oracle process using 25 MB of RAM when idle?
Helmut, Seems a bit high to me as well. Concerning the PGA issue, what about running something like : col name format A25 select n.name, round(min(s.value) / 1024) MIN K, round(avg(s.value) / 1024) AVG K, round(max(s.value) / 1024) MAX K from v$statname n, v$sesstat s where n.name like 'session pga memory%' and n.statistic# = s.statistic# group by n.name / IMHO, the Oracle insider view will probably give you something more realistic. Now, concerning what HP-UX reports to you, I would certainly suspect the run-time linker. I guess that there are better specialists of HP-UX than myself (anybody qualifies :-)) on this list who know how to tell which dynamically loaded libraries are used by a currently running program ? HTH, SF - --- Original Message --- - From: Daiminger, Helmut [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 17 Nov 2003 03:59:26 Hi Stephane, thanks for your reply. We are measuring the values by getting the OS process ID of a specific Oracle connection and then trach that process ID using glance (on HP-UX). Since the SGA is ab 1.5 GB, it is definitely not attached to the memory consumed by each process. I know that this is an issue on Solaris. We tried and used a whole bunch of different processes and they were all using 20-25 MB of RAM (doing nothing). This number seems just a little bit high to me... Example: If I have an SGA with 1 GB, 200 MB of pga-aggregate-target and 200 users connecting to the datbase (although only about 10% of them are active at the same point in time). This would mean that my memory consumption is: 1 GB + 200 MB + 200*25 MB = 6.2 GB... Regards, Helmut -Original Message- Sent: Monday, November 17, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Helmut, I don't know how you are measuring your numbers, but beware that what the operating system reports is often somewhat misleading. Typically, shared memory is often 'attributed' to each and every process linked to it. When you think about it it makes sense, but at the same time it does mean that n processes will really use much less than n * the amount of memory reported as used by one process. This is true both of the 'program' part of user memory (shared libraries) and of the 'data' part of it (SGA). When your process connects, it attaches the SGA and some shared libraries, and more shared libraries come into play as it starts doing something. You may have a better view of what is really used by your process by checking into V$SESSTAT, which holds a number of values about it. HTH, SF - --- Original Message --- - From: Daiminger, Helmut [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 16 Nov 2003 23:09:26 Hi, we are running 9.2 on HP-UX here. We have pg_aggregate_target configured, but I realized (in my opinion) very high memory consumption of Oracle Unix processes. a) How come that one Oracle Connection (i.e. dedicated Unix process on HP) is using up at least 22 MB of RAM? It is using 22 MB if the user is just connected, not doing anything. Any way I can modify this? b) If the user is querying data and the like, the memory consumption goes up to 60 MB. How come? Thanks! Regards, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Reducing rollback segment tablespace sizes
Michael Milligan wrote: I have a client where rollback segments were set up RBS01-RBS05, all in a tablespace called RBS. The person who set it up set %INCREASE at 50%. Well, I shrank the segments to themselves down to less than 500MB total from 4.6GB! But in using ALTER DATABASE DATAFILE . . . RESIZE . . . it won't let me resize it at all (it has extents scattered throughout). Anyone know how I go about shrinking it? Do I have to create a new tablespace/datafile, then drop the old one? TIA, Michael Milligan Oracle DBA Ingenix, Inc. 2525 Lake Park Blvd. Salt Lake City, Utah 84120 wrk 801-982-3081 mbl 801-628-6058 [EMAIL PROTECTED] Waow !!! A true, live Oracle 6 database ? AFAIK, PCTINCREASE has been hard-coded to 0 for rollback segments since the beginnings of Oracle 7. Concerning your question, it all depends on where your existing rollback segments are located in the datafile (look at dba_extents). All you have to do is drop and recreate each roolback segment in tirn. Now, if this really is an Oracle6 database, RESIZE doesn't work ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL*Loader and rollback segment
[EMAIL PROTECTED] wrote: Dear Gurus, I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? Thanks Jay One work around might be to create a dedicated Oracle account with the suitable rights (INSERT on the table to load) and to use a login trigger to assign the rollback segment. Another, and probably much better, way would be to have several sessions running and parallel (and hopefully assign to different rollback segments by Oracle). If your volume of data is really big, chances are that you are loading into a partitioned table. If your input data had the good taste of being made of several files, each one destined to a separate partition, would be great. Perhaps some preprocessing is required. Otherwise split your data file, be certain to have several free lists to avoid contention, and there you go. You may have to play with constraints, this is usually the price to pay to do things in parallel. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help needed -- Replication and DBMS_JOB
Jay, Check http://www.oriole.com/aunt_2001_0.html and look for the 19th. March 2001 entry. Otherwise look for a snp*.trc in either bdump or udump (never remember where it goes). HTH SF - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 14 Nov 2003 05:29:30 Dear Gurus, I have dbms_job in replication environment to push deffered transactions from site A to B and B to A. The job which is running at site A is working fine but job which is running at site B is not pushing the transactions automatically. But if I do it manually (either dbms_job.run(job_number) or using OEM UI) it is working fine. Otherwise dbms_job failed count keep increases every minute and finally it becomes broken. The owner of the DBMS_JOB is REPADMIN at both sites. Is there a way to check the root cause for the failed DBMS_JOB?. I need to check the reason why the particular DBMS_JOB is failing. I don't want DBA_JOBs view because DBA_JOBS gives the number of failed count. Note:- Number of Job_queue_processes is configured as 20 in both sites(A and B). Also iI confirmed that 20 # of snp unix process is running at each node. job_queue_interval is 60 secs. thanks Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query
Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query
The aggregate function operates last, on a result set. Why I suggested to suppress the GROUP BY is that then you would have seen that the number of bytes from each datafile is returnedtoo many times. If you have F1 and F2 associated to your database, with E1 and E2 in F1 and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on the tablespace name you get F1 E1 F1 E2 F1 E3 F2 E1 F2 E2 F2 E3 Quite obviously, when you sum up the result is much too big. For files in that case its 3 times too big for each, and for extents two times too big for each. By contrast, the inline views in the query which works force the aggregates to be computed _before_ the final calculation. SF Bellow, Bambi wrote: But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this... -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Re: Logical StandBy question
I tried it on 9.2.0.3.0 running on two Linux machines. I doubt all bugs were fixed in 9.2.0.4. I currently consider LSB to be a prototype, an interesting foretaste of things to come, but hardly more. It of course depends on the size of the database, but couldn't you consider doing reporting on a Day - 1 database? Might be simpler to use your hot backups and recreate a backup database every night. Or perhaps use snaphots (sorry, materialized views) - traditional replication (you don't need the 'advanced' stuff). If the production database can bear the overhead. Anyway, if you are as lucky as I was, this is (rebuilding the database from your backups) what you may well end doing with LSB (plus the 26 step process each time - well, I wrote scripts to help). HTH, SF - --- Original Message --- - From: Juan Miranda [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 13 Nov 2003 00:34:25 I am just planning a LOGICAL data guard installation in an important client. They need it for reporting and backup (primary is 24x7x365 and we have hot backup.) I didn?t kwon that LSB are so bad. So do you think It is so bad that you don?t put it into production ??? Do you try 9.2.0.4 ?? I need to take a decision I thank your previous answers. (I read doc, of course, but It is not explicity say that) -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Carel-Jan Engel Enviado el: miercoles, 12 de noviembre de 2003 19:59 Para: Multiple recipients of list ORACLE-L Asunto: RE: Re: Logical StandBy question Walt, drop me your email-address, and I send you the handouts of a special I presented about DG for Oracle University in Stockholm. I'm going out now for a few hours (it's 19.30 over here), but I'll respond later this evening. regards, Carel-Jan At 09:19 12-11-03 -0800, you wrote: Stephane, What sort of problems can one expect from logical standby? I'm toying with the idea of using it as a replication database -- no additional schema objects will be created, but users will have read-only access to it. It's one of the options I'm looking at. Seems to me like there was a thread on this a few months ago, but I'm not sure... --Walt On Wed, 2003-11-12 at 09:49, Stephane Faroult wrote: Jose Luis, What you say refers to the physical standby database (which works well), not to the logical standby database (which on the paper looks great, allows you to open the database, create additional tablespaces, create additional indexes on replicated objects etc) but which in practice still has a lot of teething troubles. Wouldn't use it in production on Oracle 9.2. HTH, SF - --- Original Message --- - From: Jose Luis Delgado [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 12 Nov 2003 08:09:27 Hmm... I'd like to know where in the manuals... :-) I do not think so since the standby database stay in permanent recovery mode. JL --- Rachel Carmichael [EMAIL PROTECTED] wrote: yes. Well documented in the manuals --- Juan Miranda [EMAIL PROTECTED] wrote: Hi It is posible to create other schemas on a logical stand by database ? I mean, schemas that don?t exist in the primary database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Juan Miranda INET: [EMAIL PROTECTED] -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: data modelling question - job vs. job history table
My personal preference is with solution 2 - moving the current information to JOB. The scheduler can quietly insert into JOB_HISTORY when it is done with a job, and update the current line (do it through triggers if you like). Solution 3 violates the beloved KISS principle ... Moreover, when you want to do some maintenance operation over the history table (purge, archival, whatever) you are going to interfere with the process which presumably polls the table. My EUR 0.02 SF - --- Original Message --- - From: Yechiel Adar [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 13 Nov 2003 03:14:25 I usually use a switch in the scheduled job tables to indicate active status. After the job run jus NULL or put another value in it. If you put bitmap index on this the search will be a snap. There is no reason to hold this pointer in the jobs table. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 2:49 AM Warning - this is a little long. Thank you to those who take the time to read it. I have a data modelling question (the target database will be an Oracle database.) I am keeping track of scheduled jobs run by a job agent. Table 1: JOB with columns JOBNO (primary key), JOBNAME Table 2: JOB_SCHED with columns JOB_SCHED_ID (primary key), JOBNO (foreign key to JOB), JOB_INTERVAL, JOB_START_DATE, JOB_END_DATE, JOB_RESULT Table JOB_SCHED can have: completed jobs: JOB_START_DATE not null and JOB_END_DATE not null scheduled jobs: JOB_START_DATE not null and JOB_END_DATE null unscheduled jobs: JOB_START_DATE null and JOB_END_DATE null The job can be scheduled to run only once: JOB_INTERVAL null or scheduled to run periodically: JOB_INTERVAL not null A user can save an unscheduled job and then schedule it later. As currently designed JOB_SCHED contains job history for past jobs. My background scheduler often looks up jobs to see which jobs should run now. If JOB_SCHED contains the history of all jobs run then I will have to scan through many rows to find out those jobs which should run now. I could do this in several ways: Option 1: put completed jobs in a different table called JOB_HISTORY, and then JOBNO would be UNIQUE in JOB_SCHED, or I could combine the columns in JOB and JOB_SCHED Option 2: select * from JOB a, JOB_SCHED b where a.JOBNO = b.JOBNO and b.JOB_START_DATE is not null and b.JOB_END_DATE is null But I propose option 3: Add to JOBNO a column called CURRENT_JOB_SCHED_ID (foreign key to JOB_SCHED) This should make it faster to find the current schedule for the job. The tables have reciprocal foreign key relationships: JOB_SCHED.JOBNO foreign key references JOB.JOBNO - FK_JOBNO JOB.CURRENT_JOB_SCHED_ID foreign key references JOB_SCHED.JOB_SCHED_ID - FK_JOB_SCHED FK_JOBNO characteristics: ON DELETE CASCADE FK_JOB_SCHED characteristics: DEFERRABLE INITIALLY DEFERRED (you insert into JOB before you insert into JOB_SCHED) On JOB I have a BEFORE INSERT TRIGGER that generates JOBNO and CURRENT_JOB_SCHED_ID based on a sequence On JOB_SCHED I have a BEFORE INSERT TRIGGER that generates JOB_SCHED_ID based on a sequence if JOB_SCHED_ID is null To create a new job: insert into JOB returning the new JOBNO and CURRENT_JOB_SCHED_ID set by trigger -- the insert into JOB will succeed because the FK relationship to JOB_SCHED is a DEFERRABLE FK constraint insert into JOB_SCHED using the schedule ID returned by the above insert commit When a periodic job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE insert into JOB_SCHED returning the new JOB_SCHED_ID set by trigger, START_DATE = previous START_DATE + INTERVAL update JOB set CURRENT_JOB_SCHED_ID to the schedule ID returned by the above insert commit When a run-once job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE update JOB set CURRENT_JOB_SCHED_ID to null commit Is there any reason why option 3 should be avoided? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Geometric mean
Peter, I am a mere shadow of what I used to be in mathematics, but wouldn't POWER(10,AVG(LOG(10,column))) do it? SF - --- Original Message --- - From: Robson, Peter [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 13 Nov 2003 08:49:24 Has anyone any code (SQL, PL/SQL) that will permit the calculation of the geometric mean of 'n' values? appreciated... peter edinburgh ps - yes I know Perl will do it, but that is not the answer... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RBO to CBO migration books/ material
Especially where he says that the King shouldn't meddle with the general's doings. SF Mladen Gogala wrote: Also, Sun-Tszu, The Art of War is the useful book to read before attempting migration. On 11/13/2003 10:44:39 AM, K Gopalakrishnan wrote: Suhen: John K has a nice paper (IOUG-A?) at his website. Google for his name or 'RBM CBO Migration minefields' Regards, Gopal -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Need A Split File Option
Tracy, Tell them to type man awk Your instinct is right :-). SF Tracy Rahmlow wrote: A developer in our shop would like to read an input file and based on some field values for each record in the file split them into multiple files. The output files will be used by a 3rd party package for processing. (the package does not need the oracle database) For example: Input File: record a: WI auto ... record b: WI auto ... record c: NY auto ... record d: YY home ... Output file WI_AUTO: record a: WI auto ... record b: WI auto ... Output file NY_AUTO: record c: NY auto ... Error file: record d: YY home ... (no entry in the criteria lookup table to pick up this record therefore send it to an error file) Their solution is to load an oracle table with the input file. An additional table would contain the criteria and the name of the output file to write to. They would write a procedure to read the criteria and input table and utilize the utl_file package to create the output files. There may be 50+ output files initially and likely will grow over time. My gut tells me that this does not belong in the database, rather we should be able to split the file using C or a utility such as syncsort (which we do not have). We are currently at 8.1.7.4 on AIX 4.3.3. Is there a more elegant solution and what would it be??? Are there any open source unix utilities that may help? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
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 select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Logical StandBy question
Jose Luis, What you say refers to the physical standby database (which works well), not to the logical standby database (which on the paper looks great, allows you to open the database, create additional tablespaces, create additional indexes on replicated objects etc) but which in practice still has a lot of teething troubles. Wouldn't use it in production on Oracle 9.2. HTH, SF - --- Original Message --- - From: Jose Luis Delgado [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 12 Nov 2003 08:09:27 Hmm... I'd like to know where in the manuals... :-) I do not think so since the standby database stay in permanent recovery mode. JL --- Rachel Carmichael [EMAIL PROTECTED] wrote: yes. Well documented in the manuals --- Juan Miranda [EMAIL PROTECTED] wrote: Hi It is posible to create other schemas on a logical stand by database ? I mean, schemas that don?t exist in the primary database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Juan Miranda INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Logical StandBy question
Walt Weaver wrote: Stephane, What sort of problems can one expect from logical standby? I'm toying with the idea of using it as a replication database -- no additional schema objects will be created, but users will have read-only access to it. It's one of the options I'm looking at. Seems to me like there was a thread on this a few months ago, but I'm not sure... --Walt Walt, This is basically my feelings after the tests : o Properly monitoring is rather difficult. You must check at both ends to have more than a vague feeling that things could have gone awry. This is just one aspect of a general user-friendliness which first shows up in a 26 step installation procedure. o The automated check for incompatibilities (there is normally a view to tell you what will not work) is fairly deficient. I have (by mistake) tested on a schema with lots of (unsupported) LONGs, do you think I got any warning? o Although a surprisingly high number of DDL commands are successfully replicated (including CREATE USER, etc), others are understandably not replicated (when you extend a tablespace - well the directory lay-out may be different, so it makes sense. The workaround is to have AUTOEXTEND ON, which I am usually reluctant to have), something as mundane as RENAME is not - with all the ensuing consequences you may imagine. o I have found no way to ensure that the time gap between the two databases stayed below some predefined threshold. Not sure that issuing regular ALTER SYSTEM SWITCH LOGFILE on the master is enough. I wanted to test the performance impact of logical standby by running an import, first without it, then with it, and also to measure how fast the copy was catching up, but I've given up my tests after a few ORA-600 errors. The concept is great, and I am sure to have another look at it ... later. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Stop using SYS, SYSTEM?
Smith, Ron L. wrote: We are being asked by Auditing to stop using the SYS, and SYSTEM accounts. They would like for us to create an Oracle Role with the same permissions a SYS and SYSTEM, then grant the role to each of the DBA's. Don't ask me why. Nothing is being audited in 99% of the databases. They just say it in a paper some where so they said we shouldn't use it. This seems like it would cause lots of problems with exports, imports, installs, etc... Has anyone had to deal with this type of request? Any potential problems with making the change? Thanks! Ron Smith -- I agree about SYS, but I don't have any problem with SYSTEM, which for the ownership of PRODUCT_USER_PROFILE and perhaps a couple of other dictionary-related tables, views or package is as equal a DBA as any other (SYS excepted). I like having an externally identified DBA account for running all those cron scripts etc., but on the other hand I am not in favour of unduly multiplying DBAs. This is pushing democracy too far for my taste. The more DBAs you have, the more chances you take of having an easy-to-guess or leaked password. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Migration
It raises an interesting question. As of today, we have datafiles which are OS dependent and _not_ binary compatible from one system to another. We upgrade to 10g and it will become magically binary compatible. Which means that the upgrade process will do more intimate things than updating some file header block, creating a couple of new tables in the data dictionary and recreating view. Has anybody tried to upgrade from 9.x to 10g yet, on some database of decent size ? SF - --- Original Message --- - From: Yechiel Adar [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 10 Nov 2003 05:54:25 Wait for 10g. They say that you could just copy the datafiles and them plug them in to he new database, even across platforms. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:24 AM Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? 3.. It is possible to apply the mentioned technique or some other (witch one?) in uptime, totally or partially? 4.. Witches are the main tasks to consider in a planning schedule? 5.. Witches are the time frames associated to these tasks? Thanks Armo Teles -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Re: Migration
Tanel, Any idea about speed and temporary storage requirements? Especially for 32G+ datafiles ;-) ? Wondering if it will really be useful in practice, compared to what is available today. Well, it may do for simpler operations, but not necessarily faster. SF - --- Original Message --- - From: Tanel Poder [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 10 Nov 2003 07:49:26 You can't just copy over the files with os commands and hope that Oracle will somehow recognize them. You have to use RMANs new convert tablespace command to do the byte order conversion. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 10, 2003 4:54 PM It raises an interesting question. As of today, we have datafiles which are OS dependent and _not_ binary compatible from one system to another. We upgrade to 10g and it will become magically binary compatible. Which means that the upgrade process will do more intimate things than updating some file header block, creating a couple of new tables in the data dictionary and recreating view. Has anybody tried to upgrade from 9.x to 10g yet, on some database of decent size ? SF - --- Original Message --- - From: Yechiel Adar [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 10 Nov 2003 05:54:25 Wait for 10g. They say that you could just copy the datafiles and them plug them in to he new database, even across platforms. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:24 AM Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? 3.. It is possible to apply the mentioned technique or some other (witch one?) in uptime, totally or partially? 4.. Witches are the main tasks to consider in a planning schedule? 5.. Witches are the time frames associated to these tasks? Thanks Armo Teles -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Valid Oracle Passwords
Smith, Ron L. wrote: Can anyone tell me the requirements and characters for a valid Oracle password. I have looked through the Oracle 8i documentation CD and I can't seem to find anything. Thanks! Ron Smith Ron, Passwords follow the same rules as identifiers, they are internally managed exactly as identifiers. Maximum length is 30 and anthing goes between double quotes. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: for years I have been using korn shell
[EMAIL PROTECTED] wrote: I have been working with Oracle on Unix - various platforms since Version 6.X. For the first time a vendor has sent us an install that installs under the C-shell. Up until this point I have always worked on and installed under the korn shell. This introduces a different shell environment in our current environment. I find this really annoying. However, I cannot find anything that stipulates which shell environment to use. -What is everyone else using out there? -Do you think I should make an issue of this one? Thanks, Paula If it's just a matter of installing the product, I wouldn't care. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sequences CYCLEing -- was RE: How do you genrate primary
Hemant K Chitale wrote: So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant What I have seen used are non-cycling sequences which are forced to cycle - the idea is to restart the numbering from 1 everyday. So, everyday at midnight the sequences are ALTERed so that their maximum is today's maximum, and they are forced to return to 1 - before making them NOCYCLE again. The big advantage on dropping and recreating them is that existing privileges stay in place and you don't have to GRANT SELECT to everybody ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Yechiel's idea reminds me of one of that 'tuning tips' of old which said 'avoid calling a routine too far away in the code' (because it could be in another page, etc.). This kind of phenomenon, reference to chunks of code which have been safely parked away on disk may also come into play. SF Khedr, Waleed wrote: I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Migration
A. Teles wrote: Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? No. Would take ages. 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? With imp? Could really be weeks. 3.. It is possible to apply the mentioned technique or some other (witch one?) in uptime, totally or partially? You must try to do as many things as you can in parallel, running many processes at once. If you can safely take all the network bandwidth without being fired, SQL*Plus COPY on a table-per-table basis. Which means that you have, prior to that, recreated tablespaces, users, etc. Other than SQL*Plus COPY (or CTAS across a dblink), you can download to flat files (there are tools for that), ftp, SQL*Loader direct, which assumes you already have recreated empty tables (exp ROWS=N CONSTRAINTS=N can help). 4.. Witches are the main tasks to consider in a planning schedule? The great thing is that you need not backup your database. But you must be able to switch back quickly to the old system if anything goes wrong. Be careful with your scripts (NOVALIDATE when reenabling constraints, for instance). Test a lot beforehand. Otherwise it really depends on your operational constraints. For a hospital you can't afford be offline for a long time. 5.. Witches are the time frames associated to these tasks? Can really vary. Order of magnitude is likely to be several hours in the best of cases IMHO. Also, it also depends on how much of your data is really active. You may consider quietly moving the 'dead' or archived part of it, and switching later the really active, much smaller set. Logging activity is also something which can be cntemplated for really difficult cases. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Getting Number of Rows in CTAS across DBLink
Arup, I am currently devising something I have already more or less done in the past (version 6, pre-analyse) to get a low-cost and fast estimate of the size of huge tables, which I have recently redone at a site where some of their applications are stubbornly stats-free. Restrictions : - Must be dictionary managed - May be more complicated and slower with partitioned tables. The idea is to heavily use dbms_rowid. First compute in how many blocks are, say, the first 2,000 rows. Then get the extent list in reverse order, and try to identify which is the last block to contain rows. Easy to do with a binary search, by building (dbms_rowid) the rowid of the first row in each block. Especially after a CTAS, you are sure to have a row #1. If no row at all is found, skip to the next (ie previous) extent. I have always found estimates obtained in this way pretty close to reality, and often better than ANALYZE ... ESTIMATE STATISTICS. In under one second. In your particular case, I also believe that you may find something in V$SQL - perhaps the SELECT * on the source database. You should get the number of rows processed here. HTH, SF Arup Nanda wrote: Dennis, Thanks. Sorry for not being explicit about it. Since the table created is huge, I want to avoid the count(*) if I can get the number in some other way. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 3:44 PM Arup select count(*) from table? What is your goal? Corruption detection? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 07, 2003 2:34 PM To: Multiple recipients of list ORACLE-L List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: New member / outer join problem
Attila,
Review your logic. A row full of NULLs has no signification. If your statement is
embedded into either some PL/SQL code or a 3rd generation language, Oracle will
generate a 'no data found' error, and this is what you need to trap.
For instance in PL/SQL
begin
select c1, c2, c3
into var1, var2, var3
from some_table
where some_condition;
exception
when no_data_found yhen
var1 := null;
var2 := null;
var3 := null;
end;
At this point, you have what you want into var1 ... varn.
Assuming that the query may return several rows, you can do a BULK COLLECT into
arrays.
HTH,
SF
- --- Original Message --- -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 06 Nov 2003 04:04:38
Hi All,
I'm a new member on this list. I'm not too familiar
with ORA, but I have some experience in other
RDBMS's.
I had run into the following problem.
My task is to write a query, which returns with an
empty (full of NULLs) row, if the condition is
false.
I'm thinking in outer joins.
I made a test. It returns all the matching rows (if
there are such rows), but unfortunately returns
with no row, if the condition is false.
Could someone help me, what could be the problem?
My oppinion is that it must be work fine... :-)
I use Oracle9i Enterprise Edition Release 9.2.0.1.0
.
My test is here:
--
create table ATTILA_1 (m1 varchar2(10));
insert into attila_1 values('a');
insert into attila_1 values('a');
insert into attila_1 values('b');
SELECT
b.*
FROM
dual left outer JOIN ATTILA_1 b
ON (b.m1='c');
---
It returns correctly (with 2 or 1 rows) if the
string in the condition is 'a' or 'b', but returns
with no row (I'd like to see one row, with full of
NULLs) if I call it with 'c'... :-(
Could you tell me, why is this? Or any idea to
solve this task?
Thanks in advance:
Attila
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected.' message?
You have multiple solutions; one would be to use an externally identified Oracle account; another one to use the hide.c program (should find it with google) to prevent people from seeing command arguments through 'ps'; another one to write something such as myvar=`echo your stuff here blabla exit | sqlplus -s /nolog | grep -iv connected` .. just a few ideas. HTH SF - --- Original Message --- - From: Charu Joshi [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 05 Nov 2003 04:04:38 Hello all, I am calling SQL*Plus from a unix shell script and storing the results of the query executed in a shell variable. It goes like this: FL_SUFFIX=`sqlplus -s /nolog EndOfSQL SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 CONN $ORA_ID/$ORA_PASS SELECT dummy FROM dual; -- Dummy query.. unrelated to the question. EXIT SQL.SQLCODE EndOfSQL` But the contents of the FL_SUFFIX are 'Connected.' instead of the value returned by the query. This is obviously because of the 'CONN $ORA_ID/$ORA_PASS' statement. Is there a way to suppress the 'Connected.' message that comes on connecting to database? I have thought about 2 solutions: 1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last alternative in case everything else fails .. obviously from security point of view. 2. Create a .sql script as: SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET PAGESIZE 0 SET TERMOUT OFF -- The important bits. SPOOL /dev/null -- CONN $ORA_ID/$ORA_PASS SPOOL OFF -- SET TERMOUT ON -- The important bits. SELECT dummy FROM dual; -- Dummy query. EXIT SQL.SQLCODE and then call this script as FL_SUFFIX=`sqlplus -s /nolog @a.sql` I think solution 2 will work, but I am loathe to writing a script for a single SQL statement unless there is no other way. Any new ideas would be greatly appreciated, the quicker the better. Thanks regards, Charu. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** SQL WHERE clause order
A Joshi wrote: Hi, In a SQL statement I want a certain where clause to be done first. Is it enough to list it first as follows or do I (and can I) do something else to make it get checked first before other WHERE/AND clause are looked at. Thanks : SELECT emp_id FROM emp WHERE select_sen_emp_chk_first = 'Y' AND dept = :dept AND salary :min_sal JUMPING AROUND What are you doing ? Firstly the answer to your question is a resounding NO, and secondly your approach is totally illogical. I fear something like 'one of the other conditions forces a to_number() which fails if I haven't checked beforehand that I am dealing with a number' or something as ghastly. If this is really what you have in mind, you can very easily with decode() have a condition which depends on the contents of another column. Never, ever depend on order. Now if your only concern is to force the optimizer to use one specific index, you can put an hint or the old dept+0 = :dept trick. But I fear you are on a slippery slope. /JUMPING AROUND -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Client Search Info Needed
Tracy,
IMHO the simplest and most efficient solution is :
1) to define a name_cleanup() function which does something like
replace(translate(replace(upper(arg), 'MC', 'MAC'), '- ', '##'),
'#', '')
(this is of course a very simple example)
2) to maintain by trigger an indexed CLEANED_UP_NAME which is just
name_cleanup(last_name) (you can do the same for first_name)
3) and to have your queries being written as
CLEANED_UP_NAME like name_cleanup(input) || '%'
Somebody has mentioned soundex, I am no great fan of soundex :
SQL select soundex('mac gregor'), soundex('mcgregor')
2 from dual;
SOUN SOUN
M226 M262
SQL select soundex('thompson'), soundex('thomson')
2 from dual;
SOUN SOUN
T512 T525
HTH,
Stephane Faroult
Tracy Rahmlow wrote:
I am looking for an efficient solution to the following:
We intend to capture information about a client such as:
first name - John
last name - McDonald
phone numer - 222.222.
zip code - 4
state - FL
client number - 123343
The names will be stored in mixed case for proper printing on client documents.
The reps would like the flexiblity to enter the search criteria in a number of
formats such as:
1) last name like mcdon* (wildcard) and first name = john
2) client number = 123343 (note: some clients do not always have their client
number handy so it can not be the only available search mechanism)
3) last name = mac gregor (and locate both macgregor and mac gregor)
4) last name = kinney-jones (and locate both kinney-jones and kinney jones)
How many indexes and of what type are required? Does the leading the column of
an index have to be specified for the index to be used? I thought I remember
hearing that that was a limitation of an older release, but that is no longer
the case with 8 and up. Are there any white papers available that address the
topic of client search and best practices?
Thanks for your help!!
American Express made the following
annotations on 10/30/2003 04:11:07 PM
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Finding overlapping time periods - suggestions please
[EMAIL PROTECTED] wrote: I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). Babette, I see the problem as quite similar to the 'let's fill up the calendar' problem. Basically the problem is to have time slices and to know what is going on during those slices. It's pretty easy to build up a view returning one row per minute in the timespan which matters; I am using all_tab_columns as a table with more rows than I need, a smarter solution would be the infinite_dual once suggested by Tim Goraman : select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME, END_TIME) is indexed on both START_TIME and END_TIME, it should be fast enough. From there, it is easy enough to build up a kind of 'bitmap' of activities - this for instance shows a '1' when a given task is active, '0' when it is not : select b.current_time, a.activity, decode(sign(b.current_time - a.start_time), -1, 0, decode(sign(a.end_time - b.current_time), 1, 1, 0)) active from activities a, (select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum = (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y) b / a SUM() and a GROUP BY on the current time tell you how many tasks are concurrently active at a given time, etc. Should be enough to get you started ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Finding overlapping time periods - suggestions please
Mladen, If you suggest a convoluted solution like this takes water when you have several million rows I fully agree :-). Funny enough, because it really looks like a purely relational problem, and yet it requires bending backwards. My feeling (and it definitely would deserve time to prove) is that quite possibly it's a design issue - perhaps the proper way would not be to say 'this activitity started then and ended then' but 'at this point in time that activity was running'; in fact, the convoluted part of what I suggest roughly means to do that, changing the design on the fly. SF Mladen Gogala wrote: Stephane, my solution was suggested because the client was a telco which was offering each client billing period of their own choosing (weekly, bi-weekly, monthly) starting whenever the client wanted. Finding which calls fall in the certain period was a major hassle. Of course, the solution like the one that I've suggested (and I don't know whether it would really work) would not make sense for 3 time periods altogether. What they've ended up implementing was a bunch of external procedures based on C and bitmaps, which is, accidentally, similar in concept to my solution. On 2003.11.01 08:09, Stephane Faroult wrote: [EMAIL PROTECTED] wrote: I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). Babette, I see the problem as quite similar to the 'let's fill up the calendar' problem. Basically the problem is to have time slices and to know what is going on during those slices. It's pretty easy to build up a view returning one row per minute in the timespan which matters; I am using all_tab_columns as a table with more rows than I need, a smarter solution would be the infinite_dual once suggested by Tim Goraman : select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME, END_TIME) is indexed on both START_TIME and END_TIME, it should be fast enough. From there, it is easy enough to build up a kind of 'bitmap' of activities - this for instance shows a '1' when a given task is active, '0' when it is not : select b.current_time, a.activity, decode(sign(b.current_time - a.start_time), -1, 0, decode(sign(a.end_time - b.current_time), 1, 1, 0)) active from activities a, (select y.t0 + rn / 1440 current_time from (select
RE: char(1) VS varchar2(1)
I have believed for a while that a varchar2(1) would have included a 'length byte',
making it more wasteful of storage than a char(1) but in fact the two are strictly
identical storage-wise :
SQL create table t(c1 char(1),
2 c2 varchar2(1));
Table created.
SQL insert into t values('A', 'B');
1 row created.
SQL select vsize(c1), dump(c1), vsize(c2), dump(c2)
2 from T;
VSIZE(C1)
--
DUMP(C1)
VSIZE(C2)
--
DUMP(C2)
1
Typ=96 Len=1: 65
1
Typ=1 Len=1: 66
SQL
That said, for the sake of logic I still prefer using CHAR instead of VARCHAR when the
length doesn't vary at all - call it autodocumentation. VARCHAR2(1) columns -
especially when NOT NULL - are unlikely to vary much in length.
SF
- --- Original Message --- -
From: Stephane Paquette
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Fri, 31 Oct 2003 08:04:27
Hi,
Some people here are telling me that using char(1)
is better than
varchar2(1) for a field code.
I do not see why.
I never used char as it may cause problems when
doing some comparisons.
Any reasons ?
Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: splitting files with tar
tar to stdout and pipe into 'split' eg tar cvf - mydir | split ... - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 30 Oct 2003 06:34:38 how can I splitt a file into many files with tar command. Rgds. Arslan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: DBA Support Database
I have seen something of the kind done at one of my large customers (600+ databases, Oracle + Sybase), where I have had more than a hand into the Oracle part (the inventory is stored in *blush* Sybase) and I am working on something similar with a colleague elsewhere, where there are _only_ 80 databases. In both cases the idea is to store an inventory, and to have scripts (tcl at one place, ksh at the other) running once a day to update the inventory; it of course requires having a suitable account with the required privileges created on each database. Inventory set up has been done at one place by scanning possible listener ports over the network, sending lsnrctl stat everywhere. Of course some cleanup required afterwards (... to remove extproc, among other things ...). Information collected involves startup time, Oracle version, tablespace information (also stored to an history table), database links - whatever you wish. For applications, it's a bit more manual. It's easy to get how much space is used by each schema (except SYS, SYSTEM, OUTLN ...) but then you have to 'attach' a schema to an application - and of course a DBA to an application as well. If some standards are respected, it's not too difficult ... The interface has been written with PHP at both places, with regular generation of colorful PDF reports for the management. Looks a bit like reinventing OEM, but the big advantage is that you can plug whatever you want into the system. At the biggest customer, it's linked to a similar system set up by the sysadmins showing all the information you can dream of about servers, hardware-wise as well as software-wise. Pretty impressive. For the record, the (huge team) of DBA there has at one point been forced upon some 3rd party software but they are much happier with their tools. HTH SF - --- Original Message --- - From: Vergara, Michael (TEM) [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 30 Oct 2003 08:29:27 We recently signed a corporate agreement with Oracle that basically gave us a named-user license for every person in the company. Now, we have databases sprouting like rabbits. Our four-dba team now is supporting SAP, two different Siebels, a data warehouse, and myriad other projects - over 200 databases so far. I have recently been tasked with a similar project, so I am also very interested in knowing if this has been done before. In-house, purchased, anything? Thanks, Mike -Original Message- Sent: Thursday, October 30, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Ron: I have heard of this being done especially in large companies that have many, many databases. It is difficult to keep track of all the little details that are spread out all over the company. Having a central data mart for this information I thing would be very helpful. The only problem I see is keeping it up to date. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:29 AM I was thinking about putting together a database that contains a list of DBAs, servers, databases, and applications. The database would be used by the Helpdesk and Management to see who is responsible for a given application or database when problems occur. I thought I would check first and see if anyone has already designed such a database and might be willing to share it. Thanks! Ron Smith -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Date-based query Q
Same idea as what Iain suggests, dreadful implementation : SQL select trunc(ukdate) ukdate, count(*) from test 2 group by trunc(ukdate); UKDATE COUNT(*) -- -- 01/01/2003 5 02/01/2003 6 04/01/2003 6 SQL get x 1 select y.full_ukdate ukdate, 2 nvl(x.cnt, 0) COUNT(*) 3 from (select trunc(ukdate) ukdate, 4 count(*) cnt 5from test 6group by trunc(ukdate)) x, 7 (select a.rn + b.mindate - 1 full_ukdate 8from (select rownum rn 9 from all_tab_columns) a, 10 (select min(ukdate) mindate, 11 max(ukdate) maxdate 12 from test) b 13 where a.rn = b.maxdate - b.mindate + 1) y 14* where x.ukdate (+) = y.full_ukdate SQL / UKDATE COUNT(*) -- -- 01/01/2003 5 02/01/2003 6 03/01/2003 0 04/01/2003 6 Do you _really_ want that :-) ? Didn't find analytical functions of much help on this one ... SF - --- Original Message --- - From: Nicoll, Iain [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 29 Oct 2003 04:44:25 You colud try joining to an in-line view something like SELECT :XDATE+(ROWNUM-1) DDATE FROM DBA_OBJECTS WHERE ROWNUM = (:YDATE - :xdate)+1 where dba_objects could be any table with enough rows to ensure you always covered the complete range. -Original Message- Aidan Whitehall Sent: 29 October 2003 10:49 To: Multiple recipients of list ORACLE-L This is probably a no-brainer... We have some date-based data for which most days have several records but where some days have none. I'm COUNT()ing the number of records for each day (between day x and day y) and need a record set that also includes a row for those days which have no records: UkDate Total 1/1/20035 2/1/20036 3/1/20030 4/1/20036 I could post-process the record set to achieve this, but is there any way in 9i to do an aggregate query with an outer join on a date range (if that makes sense)? Someone made the suggestion of creating another table with a row for every day under the sun in it, against which you could inner join the main query, but I'm not keen on that (that is just a gut response though). Any ideas? Thanks! -- Aidan Whitehall mailto:[EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 Queen's Awards Winner 2003 http://www.fairbanks.co.uk/go/awards -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: SQL and PL/SQL tuning template document required urgently
Ryan, 'can be faster' is rarely inaccurate. It all depends. SF - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 29 Oct 2003 06:49:33 the only book out there that is any good is High Performance Tuning by Guy Harrison. Ignore the part where he says that a cursor with an update in the loop can be faster than using an update with where exists. That is inaccurate. the rest is solid. unforunately its not that simple. How you tune depends on the type of system you have. If your in an OLTP system with alot of concurrency, you tune heavily for Logical I/Os even if the query takes longer to run in isolation. If your doing batch queries, then you tune more for response time and less for logical I/Os. its just not that simple. From: Ranganath K [EMAIL PROTECTED] Date: 2003/10/29 Wed AM 09:24:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: SQL and PL/SQL tuning template document required urgently Hi there, Does any body have a template for proactive and reactive query tuning which can be used as a guideline/report while tuning simple, medium complex and complex SQL queries and PL/SQL stored procedures? If so, can you please forward the same to me please? If not, can anybody suggest as to how to go about doing one? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: performance issue on select count(*)
Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF - --- Original Message --- - From: Linda Wang [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 27 Oct 2003 05:24:32 Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again ( 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query Tuning Help
David, I would probably not try to tune a query to make it use an index but tune a query to make it run faster - I have had recently a nice example, a join between a 500K row table and two 2K row tables (returning about 2K rows too) was running faster with FTS (followed by hash joins) on _ALL_ tables than when using the primary key of the big table (it was about twice as fast). Try avoiding prejudices... Otherwise, no, the calculation cannot cause the problem in that case. I would also get all columns in the SELECT list from the same table, just in case. HTH, SF - --- Original Message --- - From: David Wagoner [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 27 Oct 2003 07:34:26 I'm trying to tune the following query to use an index on the FILE_DTS column, rather than a FTS on the CLASS_CONFIG table (~350,000 rows). SELECT a2.class_config_id, a1.schedule_name FROMclass_config a2, class_schedule a1 WHERE a2.class_config_id = a1.class_config_id AND to_date(a2.file_dts, 'mmddyyhh24miss') SYSDATE - 35 I created a function-based index on FILE_DTS, like this: CREATE INDEX CLASS_CONFIG_FILE_DTS_FX_IDX ON CLASS_CONFIG to_date(file_dts, 'mmddyyhh24miss') and analyzed the table, but the explain plan still shows a FTS. I can change the query to something simpler and get it to use the new index, but I assume the calculation ( SYSDATE - 35) is causing the problem. Any suggestions? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com ...OLE_Obj... The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Boolean dates...
Jose Luis, The only boolean date I can think of is 'now' vs 'at another time'. You should check your program to see how they convert this - or, if you happen to *know* the meaning of some of the numbers, give a few examples. It can probably be guessed back. HTH SF - --- Original Message --- - From: Jose Luis Delgado [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 22 Oct 2003 07:19:26 Hi to all! We have an old app that manages something that my boss calls: boolean dates. He told me that exists an algorithm that manages dates as a boolean format. We have several tables in this form: Note: The following table: PAAM has the field BDATE defined as NUMBER. sql select bdate from paam sql where rownum 6 BDATE -- 728464 728434 728403 728495 728283 now, I need to convert that format to an 'understandable' format to get the old data and old dates. I'm looking (google-ing) for that subject but, without luck. any ideas? help?, pls... Thanks in advance Regards! JL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Boolean dates...
Mladen Gogala wrote: Nope. It's the dates according to the format invented by Booleous Caesar. Who was counting his days 'ab urbe condita' (753 BC), hence the 700 years shift. On 10/22/2003 12:24:30 PM, [EMAIL PROTECTED] wrote: Are you sure he didn't mean Julian dates? Somewhere, someplace you are bound to find the algorithm that converts between Julian and Gregorian dates. I had it once a long time ago. -Original Message- Hi to all! We have an old app that manages something that my boss calls: boolean dates. He told me that exists an algorithm that manages dates as a boolean format. We have several tables in this form: Note: The following table: PAAM has the field BDATE defined as NUMBER. sql select bdate from paam sql where rownum 6 BDATE -- 728464 728434 728403 728495 728283 now, I need to convert that format to an 'understandable' format to get the old data and old dates. I'm looking (google-ing) for that subject but, without luck. any ideas? help?, pls... Thanks in advance Regards! JL __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: question on comments -sanity check
Bob Metelsky wrote: All The powers that be have it in their minds that there is a place for comments on each column in a table. afaik.. comments are only associated with tables not columns Eg Select * from user_tab_comments; People here seem to think they can document their columns by comments. Maybe I'm just burnt out... or am I missing something? A sanity check please. Also, is there a relatively easy way to maintain comments? Thanks! bob Failed -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: How to call unix shell scripts from 'C'?
Quintin, Richard wrote: fork() and exec() are what you're looking for. I haven't done it in a while, but you should be able to find plenty of info online. On Wed, 2003-10-22 at 11:44, [EMAIL PROTECTED] wrote: The unix and C forums are pretty inactive. Hope its ok to ask this here. Anyone know how to do this? -- Acutually, there is simpler than this. If the script echoes nothing, system() can be used. Otherwise, look for popen(). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Can I concatenate several rows without a procedure?
Jake Johnson wrote: The following query returns 33 records. SYS0 freestyle!! 12-MAY-02 SYSTEM5 freestyle!! 12-MAY-02 OUTLN11 freestyle!! 12-MAY-02 But, I would like to have all 33 records appended together to have one long record. SYS0 freestyle!! 12-MAY-02SYSTEM5 freestyle!! 12-MAY-02OUTLN11 freestyle!! 12-MAY-02 Thanks again, Jake On Tue, Oct 21, 2003 at 07:49:25AM -0800, Bob Metelsky wrote: select username||user_id||' freestyle!! '|| created as concat from all_users; Hello, I am trying to concatenate several records with simple sql. Is this possible? -- Thanks, Jake Johnson [EMAIL PROTECTED] SQL select * from sliced_kipling; VERSE PIECE CHUNK -- -- -- 1 1 Oh, East is East, 1 2 and West is West, 1 3 and never the twain shall meet, 2 1 Till Earth and Sky stand 2 2 presently at God's great Judgment Seat; 3 1 But there is neither East nor West, 3 2 Border, 3 3 nor Breed, 3 4 nor Birth, 4 1 When two strong men stand face to face, 4 2 tho' they come from the ends of the earth! 11 rows selected. SQL @magic_query VERSE Oh, East is East, and West is West, and never the twain shall meet, Till Earth and Sky stand presently at God's great Judgment Seat; But there is neither East nor West, Border, nor Breed, nor Birth, When two strong men stand face to face, tho' they come from the ends of the earth! SQL l 1 select translate(ltrim(x.text, '/'), '/', ' ') verse 2 from (select verse, level lvl, sys_connect_by_path(chunk, '/') text 3from sliced_kipling 4connect by verse = prior verse 5 and piece - 1 = prior piece) x, 6(select verse, max(piece) piecemax 7 from sliced_kipling 8 group by verse) y 9 where x.verse = y.verse 10and x.lvl = y.piecemax 11* order by x.verse SQL I am not sure though that I satisfy the 'simple SQL' requirement :-). Stephane Faroult -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How do I match something like %abc
Roger Xu wrote: How come the following does not work select * from mytable where mycol like '\%%' Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 Because '\' is an escape character only when explicitly declared as such .. where mycol like '\%%' escape '\' -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Effect on Oracle of changing server time from EST to GMT
Well, some jobs (including snapshot refreshes) may get confused, and if you create or alter objects I wonder how Oracle may handle dependencies ... Fortunately you are moving 5 hours ahead, rather than back, so risks are probably quite limited. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OW Paris get-together, was : Re: where is Tanel ?
I have just received an e-mail from Tanel saying he had not had the time to participate recently and enquiring about any list get-together at Oracle World Paris. I won't attend OW myself but I'll happen to be in the very same area, invoicing happily, on Monday, Wednesday and Thursday till I leave to catch a plane for the Chris Date seminar in Edinburgh. For those of you who don't know La Defense, where it takes place, and have a romantic vision of Paris, brace yourself for a cruel disappointment, since La Defense is the business district, also known as Manhattan-sur-Seine (although in truth buildings are a modest 40 floors at most) and technically speaking isn't in Paris proper. Places to have a drink are nevertheless numerous. I guess that as a native I am designated to be the coordinator, so please e-mail me directly so that we try to arrange something. SF M Rafiq wrote: I am missing his presence too? It looks he became angry because of some personal remarks by our some fellow listers. Tanel, where are you? We already lost active participation of Steve Adam too. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 16 Oct 2003 08:19:52 -0800 Looks like Tanel Podar is hiding some where or restraining from answering ? Missing his highly sophisticated answers ;) -ak _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert and commit 1000 records at a time
Mladen Gogala wrote:
I believe that this would be the best solution:
DECLARE
RowCount NUMBER:= 0;
BEGIN
/* This will work if the RESOURCE table has the parallel
attribute set. In 8i, table needs to be partitioned as well */
EXECUTE IMMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT /*+ APPEND */ INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise NOT_LOGGED_ON;
END;
/
On 10/16/2003 01:29:33 PM, [EMAIL PROTECTED] wrote:
yeah dont commit every 1000 records and do it in one shot. this is
going to be much slower.
why do you want to do it this way? Ive done 100m inserts with just an
insert select and one commit.
From: Maryann Atkinson [EMAIL PROTECTED]
Date: 2003/10/16 Thu AM 11:54:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Why do you need the first count(*) ? If the table you have to insert
from is big it's a waste of time. You can check SQL%ROWCOUNT after the
insert in all cases.
Otherwise I fully agree with the implicit suggestion that you should
question the reason for committing every 1000 rows. It would force you
to adopt a row-by-row logic which will kill performance.
The most acceptable solution might perhaps be an OCI program, in which
you would fetch 1000 rows per 1000 rows in arrays and insert likewise. I
don't see any way to do something similar in PL/SQL but it's close to
midnight here and I am getting pretty tired.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Re: Resource for index
Sorry for having made myself misunderstood. + 0 does, in your case, NOTHING AT ALL. Nothing bad, nothing good. You can keep it or remove it. A + 0 can do something when : 1) Put after the name of a number column, eg WHERE SAL + 0 = 2000 2) And if there is an index on this column. (none of the conditions is verified in your case). What does it then? It prevents Oracle from using the index. The reason is that indexes are trees which are descended using comparisons (if the key value is bigger than the value I am looking for, I recursively search this subtree, otherwise I recursively search this other subtree). Oracle is not smart enough to see whether an operation changes the order (eg * -1) or doesn't (eg * 1 or + 0). In doubt, it takes the safest option and says that the operation will screw up comparisons (a technical term) and doesn't use the index. The same occurs with type conversions, because 'smaller' means quite different things for a string, a number or a date. It can be useful when you are using the rule-based optimizer (RBO) and have an index created for reasons unrelated to performance (a foreign key column, for instance - or when you are running queries against tables of a canned application). HTH, SF - --- Original Message --- - From: Eriovaldo Andrietta [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 14 Oct 2003 17:44:25 Dear Stephane, At first, my apologize by the word Resource, it was the first word that I found out and wrote it. The case is that this command +0 , you can put it in the sentence line of where and the sintaxe is correct. Oracle executes the statment and don't gives error message. My doubt is : What this +0 does in the sql ? Imagine a column unindexed that you can not create a index, because it will do a bad performance in all the system, you can use +0 that it will break the index . It is what i eard. But i am searching about this +0 and until now, i didn't find out nothing about it. For me it doesn't exist. Did you do a test ? You can put it at the statment that will run , normaly. And more, if you to use explain plan the table (unindexed) will have a FULL TABLE SCAN. So, I ask : What is this +0 ? Resource or not , it is my hard doubt. Regards Eriovaldo - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 13, 2003 4:04 PM Friends : I have a part of statment as below : select column1 from table1 where column1 = v_parameter; The column1 is not indexed, so the table full scan will be executed. Right? Well, I would like to know if anyone knows the resource : select column1 from table1 where column1 = v_parameter +0 What does it mean (+0). What kind of resource is it ? What does it do ? Best Regards Eriovaldo Eriovaldo, Don't understand too well what you mean by 'resource'. One of the most cunning performance tips of yore was to either concatenate an empty string to a string or date column or add a zero to a number column to prevent the RBO from using an index on this column (BTW it's a trick which can still be useful at times) when this index was known to be not very selective. Quite obviously, applying this to the constant part of an equality is totally pointless. And since the column is unindexed, it wouldn't have much utility either on the other side of the equality. Another case of ill-digested and ill-understood trick. -- Regards, Stephane Faroult Oriole Software -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Resource for index
Sorry for having made myself misunderstood. + 0 does, in your case, NOTHING AT ALL. Nothing bad, nothing good. You can keep it or remove it. A + 0 can do something when : 1) Put after the name of a number column, eg WHERE SAL + 0 = 2000 2) And if there is an index on this column. (none of the conditions is verified in your case). What does it then? It prevents Oracle from using the index. The reason is that indexes are trees which are descended using comparisons (if the key value is bigger than the value I am looking for, I recursively search this subtree, otherwise I recursively search this other subtree). Oracle is not smart enough to see whether an operation changes the order (eg * -1) or doesn't (eg * 1 or + 0). In doubt, it takes the safest option and says that the operation will screw up comparisons (a technical term) and doesn't use the index. The same occurs with type conversions, because 'smaller' means quite different things for a string, a number or a date. It can be useful when you are using the rule-based optimizer (RBO) and have an index created for reasons unrelated to performance (a foreign key column, for instance - or when you are running queries against tables of a canned application). HTH, SF - --- Original Message --- - From: Eriovaldo Andrietta [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 14 Oct 2003 17:44:25 Dear Stephane, At first, my apologize by the word Resource, it was the first word that I found out and wrote it. The case is that this command +0 , you can put it in the sentence line of where and the sintaxe is correct. Oracle executes the statment and don't gives error message. My doubt is : What this +0 does in the sql ? Imagine a column unindexed that you can not create a index, because it will do a bad performance in all the system, you can use +0 that it will break the index . It is what i eard. But i am searching about this +0 and until now, i didn't find out nothing about it. For me it doesn't exist. Did you do a test ? You can put it at the statment that will run , normaly. And more, if you to use explain plan the table (unindexed) will have a FULL TABLE SCAN. So, I ask : What is this +0 ? Resource or not , it is my hard doubt. Regards Eriovaldo - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 13, 2003 4:04 PM Friends : I have a part of statment as below : select column1 from table1 where column1 = v_parameter; The column1 is not indexed, so the table full scan will be executed. Right? Well, I would like to know if anyone knows the resource : select column1 from table1 where column1 = v_parameter +0 What does it mean (+0). What kind of resource is it ? What does it do ? Best Regards Eriovaldo Eriovaldo, Don't understand too well what you mean by 'resource'. One of the most cunning performance tips of yore was to either concatenate an empty string to a string or date column or add a zero to a number column to prevent the RBO from using an index on this column (BTW it's a trick which can still be useful at times) when this index was known to be not very selective. Quite obviously, applying this to the constant part of an equality is totally pointless. And since the column is unindexed, it wouldn't have much utility either on the other side of the equality. Another case of ill-digested and ill-understood trick. -- Regards, Stephane Faroult Oriole Software -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what are leaf blocks and table high water marks?
can anyone explain what are leaf blocks and table high water marks? thanks. Leaf blocks refer to indexes, and, more generally speaking, trees (I can only advise you to read Donald Knuth's 'The Art of Computer Programming' volume 3 (Addison-Wesley) if you want to know all about trees - one of the very few really useful computer books IMHO). In the case of Oracle, these are the blocks where you find the rowids which are the physical addresses of the data associated with keys. The other index blocks are 'nodes', basically key values and pointers allowing you to manage your way from the top of the tree down to the leaf block. The high-water mark refers, as its name implies, to the last block (among those allocated to a table) to have ever contained data. Oracle keeps a track of this block for two reasons at least : 1) when doing a full scan it knows it can stop there, since there is nothing afterwards. It makes quite a difference when you have allocated a huge storage in prevision of future growth but your table still is relatively small. 2) when appending data in a fast mode it can store everything there without having to look for 'holes' resulting from row deletions in the blocks which already contain data - losing space but saving time (the usual trade-off). Of course, if you delete rows (some of them or all of them) you will be able (all right, there's also PCTUSED but let's keep things simple) to insert data below the high-water mark. The high-water mark is reset (to 0) by TRUNCATE. HTH, SF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bitmap conversion on a index that is not bitmapped ???
K Gopalakrishnan wrote: John: Optimizer is a smart boy!!! He knows the column has few distinct values and decides the BITMAP access would be appropriate and making BITMAP plans from the BTree indexes. If you delete the stats for that index, you will get the old behavior. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- ... Unfortunately the old behaviour seems to be the bad one. I guess that the solution would rather be to rebuild indexes on a regular basis ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Operation and Row size
Daniel Fink wrote: When retrieving data, does Oracle place just the columns of interest into the result set or does it place the entire row in the result set (and filter out the columns of interest later)? I'd say just the columns of interest. Can be checked by looking at the number of buffer gets on a table with some noticeable chaining, comparing select * to select pkcol. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bitmap conversion on a index that is not bitmapped ???
Fedock, John (KAM.RHQ) wrote: Stephan, That is correct. The old behavior is not what I want. I have rebuilt and reanalyzed, deleted stats - tried all sorts of combinations and I cannot get it to use the bitmapped access as it once did. Again - these are not bitmapped indexes to begin with. John John, Interesting. Please understand that, as Gopal pointed out, thoes bitmapped indexes really are built 'on the fly' as a (as it appears, efficient) way to boost the query speed. The question, and I don't have the answer (but perhaps the eminent specialist of event 10053 on the list could shed some light) is what incites the optimizer to do this. Low cardinality is obviously a factor, but it cannot be the only one since I guess that the cardinality of your column musn't have changed much. This is a shot in the dark, but I'd certainly consider the clustering factor too. If keys with similar values are clustered, my guess is that bitmaps will contain big swathes of 1s or 0s or whatever, and Oracle will be able to pick lots of blocks of interest in one pass. If, however, the distribution of 'good' rows among 'bad' rows is pretty uniform, there is not much point in taking the trouble of building the bitmapped index. Would be interesting to play with the stats on a development database. HTH, SF -Original Message- Sent: Wednesday, October 15, 2003 4:29 PM To: Multiple recipients of list ORACLE-L K Gopalakrishnan wrote: John: Optimizer is a smart boy!!! He knows the column has few distinct values and decides the BITMAP access would be appropriate and making BITMAP plans from the BTree indexes. If you delete the stats for that index, you will get the old behavior. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- ... Unfortunately the old behaviour seems to be the bad one. I guess that the solution would rather be to rebuild indexes on a regular basis ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: space taken up by number data types
Ryan wrote: I could have swarn I read that Precision with number data types effects how much space is reserved in the database. So number(38) and number(3) reserve different amounts of space. Here is a link from Tom Kyte in 1998 saying the opposite. Is what he says still true? http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=355e25d0.17874392%40192.86.155.100rnum=1prev=/groups%3Fas_q%3Dnumber%2520datatype%2520storage%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3D*oracle*%26lr%3D%26num%3D50%26hl%3Den No such thing as trying by oneself. Check the VSIZE() function, not very well known but useful for this kind of question. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Resource for index
Friends : I have a part of statment as below : select column1 from table1 where column1 = v_parameter; The column1 is not indexed, so the table full scan will be executed. Right? Well, I would like to know if anyone knows the resource : select column1 from table1 where column1 = v_parameter +0 What does it mean (+0). What kind of resource is it ? What does it do ? Best Regards Eriovaldo Eriovaldo, Don't understand too well what you mean by 'resource'. One of the most cunning performance tips of yore was to either concatenate an empty string to a string or date column or add a zero to a number column to prevent the RBO from using an index on this column (BTW it's a trick which can still be useful at times) when this index was known to be not very selective. Quite obviously, applying this to the constant part of an equality is totally pointless. And since the column is unindexed, it wouldn't have much utility either on the other side of the equality. Another case of ill-digested and ill-understood trick. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Data Modelling Tools for a DBAs Job
quriyat wrote: How essential the following tools to a DBAs job? (viz) Rational Rose, Clear case for Versioning etc. What are Data Modelling tools avbl. in the market and which are widely used (other than Oracle Designer)?. TIA The only essential DBA tool is sqlplus. Modelling tools can be useful if you are involved in design (in which many DBAs are not); they can help getting a grasp of an existing application but how much they bring you depends on you. I have also seen AMC Designer (this is the old name, a tool now sold by Sybase as, I think, PowerDesigner or something like that) much used - and with Oracle more than Sybase. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Weblogic, thin driver and lob segments
Gunnar Berglund wrote: Hi all, we are facing some serious problems with the following case: we have an application which stores quite a lot of documents to database (average document size is 2mb). Application loads document template from database and when we are having 100-200 hundred users there will be a queue on a weblogic server in order to wait to handle those documents and saving the updates to database. Do you have any ideas how to do this faster or is there something we haven't realized. TIA gb Is saving to database really the thing to do in such a case? Can't you just save pointers to the documents? Also, if I understand you well, in MSWord-speak people load a .dot and save a .doc; do you really have to save the formatted document? Couldn't you save something saying 'this is the model' and 'this is the associated data'? In other words, use a 'style-sheet' logic? The really useful data in a document is often something relatively small ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: RE: Find an unprintable character inside a column....
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 ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Find an unprintable character inside a column....
[EMAIL PROTECTED] wrote: I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] To:Multiple Sent by: [EMAIL PROTECTED] recipients of list ORACLE-L [EMAIL PROTECTED] 10/10/2003 07:09 AM cc: Please respond to ORACLE-LSubject:RE: RE: RE: Find an unprintable character inside a column 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 ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Jared, you're the scourge of people who just write things out of the top of their head and don't test them thoroughly :-). I had made my usual mistake of using REPLACE instead of TRANSLATE. Just tried it with 'regular' data, since this is the only case where it can be faster that Peter's routine. Works like Peter's routine with TRANSLATE, only somewhat faster. Ooops again. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Find an unprintable character inside a column....
Steve, If you are patient, I guess that something like where dump(problem_column) like '%target hex%' should more or less answer your question. HTH SF - --- Original Message --- - From: Steve Main [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 08 Oct 2003 15:44:26 Hello list, I have an application that is choking on the following error, ...invalid character (Unicode: 0x19) was found in the element content... Does anyone know how I could go about searching for this invalid character? Thanks Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: alter session
Goulet, Dick wrote: That only affects how Oracle finds objects. If for instance you would have to access dba_users normally as sys.dba_users then using the alter session command means you can drop the 'sys.' portion thereof. It has no affect on your priviledges. Down side is that if you then want to reference one of your personal tables you have to say so. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA It *no longer* affects your privileges (since 7.0.4 ...) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Avoiding full table scan
Jack van Zanen wrote: Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack If you index name and put all the columns from the SELECT list into the index, my guess is that you will get an index fast full scan, which may not be that bad. Basically depends on how many blocks you have to wade through. Otherwise I don't see any other solution than Intermedia. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Update Japanese character without Exp/IMp and DB Link
Oracle DBA wrote: Hi List: I have Database DB1 Table name T1 Column Name C1 C1 has some japanese character Database DB2 Table name T2 Column Name C2 I want to update C2 column(only one record) with C1 column value. Note:- Exp/Imp AND db_link is not possible === Any help would be really appreciated. Thanks Sami In one window connect to DB1 select C1 from T1; In a second window connect to DB2 type update T2 set C2 = painfully type what you see in the other window here; commit; -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question with lock script - phantom objects
BRFONT SIZE=3D2 FACE=3DCourier Newcolumn table_name format a20 = trunc/FONT BRFONT SIZE=3D2 FACE=3DCourier Newcolumn owner format a10/FONT BRFONT SIZE=3D2 FACE=3DCourier Newcolumn inst_id format a5/FONT BRFONT SIZE=3D2 FACE=3DCourier Newselect --+ no_merge(l) = no_merge(s)/FONT BRFONT SIZE=3D2 FACE=3DCourier = New (select instance_name = /FONT BRFONT SIZE=3D2 FACE=3DCourier = sys.gv_$instance /FONT BRFONT SIZE=3D2 FACE=3DCourier = instance_number =3D l.inst_id) inst_id,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New l.sid || ',' || = s.serial# sid,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New s.username,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New = replace(s.terminal,'WTS-') terminal,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New decode(l.type,/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 'RW','RW-Row Wait Enq',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 'TM','TM-DML Enq',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 'TX','TX-Trans Enq',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 'UL','UL-User',l.type||'-System') res,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New t.name = table_name,u.name owner,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New l.id1,l.id2,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New decode(l.lmode,1,'No = Lock',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 2,'Row Share',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 3,'Row Excl',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 4,'Share',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 5,'Shr Row Excl',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 6,'Excl',null) lmode,/FONT BRFONT SIZE=3D2 FACE=3DCourier = New decode(l.request,1,'No = Lock',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 2,'Row Share',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 3,'Row Excl',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 4,'Share',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 5,'Shr Row Excl',/FONT BRFONT SIZE=3D2 FACE=3DCourier = nbsp; 6,'Excl',null) request/FONT BRFONT SIZE=3D2 FACE=3DCourier Newfrom sys.gv_$lock l, = sys.gv_$session s, sys.user$ u,sys.obj$ t/FONT BRFONT SIZE=3D2 FACE=3DCourier Newwhere l.sid =3D s.sid/FONT BRFONT SIZE=3D2 FACE=3DCourier Newand s.type !=3D = 'BACKGROUND'/FONT BRFONT SIZE=3D2 FACE=3DCourier Newand t.obj# =3D l.id1/FONT BRFONT SIZE=3D2 FACE=3DCourier Newand u.user# =3D t.owner#/FONT BRFONT SIZE=3D2 FACE=3DCourier Newand l.inst_id =3D = s.inst_id/FONT BRFONT SIZE=3D2 FACE=3DCourier New//FONT BRFONT SIZE=3D2 FACE=3DCourier Newprompt/FONT BRFONT SIZE=3D2 FACE=3DCourier Newset feedback on/FONT BRFONT SIZE=3D2 FACE=3DCourier Newprompt/FONT BRFONT SIZE=3D2 FACE=3DCourier Newexit/FONT BRFONT SIZE=3D2 FACE=3DCourier = =3D cut here = /P PFONT SIZE=3D2 FACE=3DCourier NewBtu here is the problem ... once = in a while (aka many times a day) when we run this script, we see = objects as locked by some user which should NEVER be even accessed. We = have one schema that deals with out affiliates, and it is practically = independent of other schema in the database. Still sometimes we see = objects within the affiliate schema beign accesses by other users who = have nothing to do (or the code they execute has nothing to do) with = the objects displayed in the list./FONT/P PFONT SIZE=3D2 FACE=3DCourier NewOne peculier thing I have noted, = is affiliate schema used private synonyms and the objects listed in the = lock scripts are _always_ private synonyms pointing to objects in the = affiliate schema and the private synonym belongs to the user who is = _not_ the locking user./FONT/P PFONT SIZE=3D2 FACE=3DCourier Newe.g./FONT /P PFONT SIZE=3D2 FACE=3DCourier NewABC1 = 47,820 BROWNBRE BRS02 = TX-Trans Enq SYSTEM_NETWORK_HIST = MURPHYM Excl/FONT /P PFONT SIZE=3D2 FACE=3DCourier Newhere locking user ius brownbre = locking system_network_hist owned by murphym. Actually the table is = owned by affiliate and murphym has a private synonym to the = table./FONT/P PFONT SIZE=3D2 FACE=3DCourier NewAny clues? DB is 9202 = RAC./FONT BRFONT SIZE=3D2 FACE=3DCourier NewTIA/FONT BRFONT SIZE=3D2 FACE=3DCourier NewRaj/FONT /P PFONT SIZE=3D2 FACE=3DCourier = -/FONT BRFONT SIZE=3D2 FACE=3DCourier NewRajendra dot Jamadagni at = nospamespn dot com/FONT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
