RE: xref table - design consideration

[Aponte, Tony]

I would recommend "The Data Model Resource Book, Vol. 1. A library of
Universal Data Models for All Enterprises".  It has a good started model
for this kind of requirement but doesn't resort to multiple tables for
each relationship.  The book alone without the CDROM is under $100 US
and might save you a lot of modeling time.

HTH
Tony Aponte

-Original Message-
Sent: Monday, November 24, 2003 12:44 PM
To: Multiple recipients of list ORACLE-L


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


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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

[Aponte, Tony]

Sorry I missed one.  Try this.

SELECT 
 usr  
FROM 
  bogus
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0)) > 0
AND
 SUM(DECODE(val,5,1,0)) > 0
AND
 SUM(DECODE(val,7,1,0)) > 0
AND
 SUM(DECODE(val,1,0,5,0,7,0,val)) = 0

Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L


Here's one solution.

SELECT 
 usr  
FROM 
 xxx
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0)) > 0
AND
 SUM(DECODE(val,5,1,0)) > 0
AND
 SUM(DECODE(val,7,1,0)) > 0

HTH
Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5:05 PM
To: Multiple recipients of list ORACLE-L


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


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gabriel Aragon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or 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

[Aponte, Tony]

Here's one solution.

SELECT 
 usr  
FROM 
 xxx
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0)) > 0
AND
 SUM(DECODE(val,5,1,0)) > 0
AND
 SUM(DECODE(val,7,1,0)) > 0

HTH
Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5:05 PM
To: Multiple recipients of list ORACLE-L


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


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gabriel Aragon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Veritas and disks

[Aponte, Tony]

Title: RE: Veritas and disks






This note may describe the behavior you are observing. 


www.sun.com/blueprints/0400/ram-vxfs.pdf 


HTH

Tony Aponte


-Original Message-

From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]

Sent: Monday, August 25, 2003 3:34 PM

To: Multiple recipients of list ORACLE-L

Subject: Veritas and disks



Anyone have any experience with Veritas CFS (running RAC) and disk IO? We

are seeing some interesting things here with regards to available memory and

disk throughput. We get great throughput when there is sufficient memory.

But, as we load up DD processes, we find available memory decreasing

rapidly, like the copies are being cached to free memory by veritas first.

Then, after the copies appear to be done from the OS, we see consistant disk

activity afterwards.


I suspect that veritas is caching these cp's into memory and then writing to

disk. This works great until I run out of memory, at which time the cp

starts to perform terribly. Is this how Veritas works and is there any way

to configure this behavor? 


RF


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 

Author: Freeman Robert - IL

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

SharePlex info

[Aponte, Tony]

Title: Message



You 
are correct in the first place.  SharePlex works as you describe, it mines 
the log and sends only the absolute minimum to reassemble the transaction on the 
target.  It doesn't send SQL.  The target side processes take the data 
and rebuild a SQL statement from the DDL definitions it got from the data 
dictionaries of the source and target (just in case you only want a subset of 
the columns.)  Sorry if I confused you.
 
Tony

  -Original Message-From: Gorbounov,Vadim 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, August 21, 
  2003 5:01 PMTo: Aponte, TonySubject: RE: SharePlex 
  info
  Tony, 
   
  My 
  question was inspired by belief that SharePlex does log mining on the source 
  DB and hence do not send unnecessary data over the network. Apparently, this 
  is not the case. I didn't want to compare SharePlex to logical standby cause I 
  know that logical standby definitely needs all logs transported to the target 
  site where is does log mining. 
  We 
  considering remote disaster recovery site where we want to have working data 
  and we don't care much about "log" tables. 
   
  Thank you for valuable info. 
  
-Original Message-From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]Sent: Thursday, August 21, 2003 4:40 
PMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: SharePlex 
info
Your bandwidth requirements will be the rate of 
changes to the actual data.  The traffic consists of the actual data 
and control information needed to reassemble the transaction on the 
target.  The source database's other redo payload (i.e., index 
operations, rollback segment maintenance, etc.) is not used by 
Shareplex.  
 
In 
our environment of dual Sun 6800's, 10 CPU's each, we observe less that 1% 
CPU consumption on the source and target sides combined.  It varies 
according to the DML load on the source but not by much.  We've never 
had a problem with it consuming a noticeable amount.
 
I 
have a question on the comparison between a physical standby and Shareplex 
replication.  Isn't 9i's logical standby feature better 
suited for the comparison to Shareplex?  I'm assuming that you are 
considering offloading some processing to another host since you are looking 
to replicate about 50% of the tables in the source 
database.
 
HTH
Tony Aponte
 
 

  -Original Message-From: Gorbounov,Vadim 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, August 21, 
  2003 1:49 PMTo: Multiple recipients of list 
  ORACLE-LSubject: SharePlex info
  Hi All, 
  
   
  I'm trying to find some 
  technical details about SharePlex, that is:
   
  - How much network bandwidth 
  I'd expect to replicate from database, generating 1-5 MB/sec redo. 
  Does SharePlex send SQL text over the network or data in some 
  internal (hopefully compressed) format
  - How much CPU on the source 
  DB server side would it cost  - just a ball park - very little- 
  little - or a lot 
  - Of two options, using 9.2 
  physical async standby db and clone whole database vs replicate 50% 
  (enough from business requirements) of tables using SharePlex, which 
  one sounds preferrable keeping in mind minimizing CPU burden on the 
  source database. 
   
  Any opinion or pointer to any 
  benchmark is highly appreciated. 
   
  Thanks a 
  lot
  Vadim

RE: SharePlex info

[Aponte, Tony]

Title: Message



Your 
bandwidth requirements will be the rate of changes to the actual data.  The 
traffic consists of the actual data and control information needed to reassemble 
the transaction on the target.  The source database's other redo payload 
(i.e., index operations, rollback segment maintenance, etc.) is not used by 
Shareplex.  
 
In our 
environment of dual Sun 6800's, 10 CPU's each, we observe less that 1% CPU 
consumption on the source and target sides combined.  It varies according 
to the DML load on the source but not by much.  We've never had a problem 
with it consuming a noticeable amount.
 
I have 
a question on the comparison between a physical standby and Shareplex 
replication.  Isn't 9i's logical standby feature better 
suited for the comparison to Shareplex?  I'm assuming that you are 
considering offloading some processing to another host since you are looking to 
replicate about 50% of the tables in the source database.
 
HTH
Tony 
Aponte
 
 

  -Original Message-From: Gorbounov,Vadim 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, August 21, 
  2003 1:49 PMTo: Multiple recipients of list 
  ORACLE-LSubject: SharePlex info
  Hi All, 
  
   
  I'm trying to find some technical 
  details about SharePlex, that is:
   
  - How much network bandwidth I'd 
  expect to replicate from database, generating 1-5 MB/sec redo. 
  Does SharePlex send SQL text over the network or data in some internal 
  (hopefully compressed) format
  - How much CPU on the source DB 
  server side would it cost  - just a ball park - very little- little - or 
  a lot 
  - Of two options, using 9.2 
  physical async standby db and clone whole database vs replicate 50% (enough 
  from business requirements) of tables using SharePlex, which one sounds 
  preferrable keeping in mind minimizing CPU burden on the source database. 
  
   
  Any opinion or pointer to any 
  benchmark is highly appreciated. 
   
  Thanks a 
  lot
  Vadim

RE: Tracing a user session with multiple database connections

[Aponte, Tony]

Title: RE: Tracing a user session with multiple database connections






It may need some changes for the Alter Session syntax but the trigger should help you isolate the activity by these session.

HTH

Tony Aponte


CREATE OR REPLACE TRIGGER sys.trap_conversion_connect_trig

  AFTER LOGON ON conversion.SCHEMA

BEGIN

  BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER,LEVEL 12''';

  EXCEPTION

  WHEN OTHERS THEN

   SYS.DBMS_SYSTEM.KSDWRT(2,SQLERRM);

  END;

END;


-Original Message-

From: Schauss, Peter [mailto:[EMAIL PROTECTED]]

Sent: Monday, August 18, 2003 11:29 PM

To: Multiple recipients of list ORACLE-L

Subject: Tracing a user session with multiple database connections



We have a third party application which makes multiple connections

to the database.  The application keeps one connection open for

the duration of the session and then opens and closes one or more

connections

in succession.  Some of the connections only last for a few seconds, so

running the set_sql_trace_in_session stored procedure by hand is not

an option.  All of the connections have the same sid.


Is there any way to trace all of the connections

for a given sid without setting sql_trace for the entire instance?


This is Oracle 8.1.7 on AIX 4.3.3.


thanks,

Peter Schauss

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 

Author: Schauss, Peter

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Veritas Quick I/O for Oracle

[Aponte, Tony]

Title: RE: Veritas Quick I/O for Oracle






Dennis,


The benefits of QIO are realized not only by Oracle but also by the storage administrators.  Oracle improvements come from kernelized async I/O, elimination of UNIX double-buffering and single-writer file header locking in the O/S.  Storage administrators gain from managing a cooked file system.  It enables many online tasks (like provisioning for new storage on active file systems, restriping on the fly, wholesale relocation of databases) that are impossible or very painful and error prone with raw partitions.  My group has a good relationship with UNIX SAs and we try help whenever we can.

However, it ain't a no-brainer.  In pre-ODM environments QIO files have to be pre-created on the UNIX side before being added to the database, or the file offlined and enabled for QIO after the fact.  It's a simple command line tool but it takes planning and preparation plus adjustments to existing procedures.  Our biggest point of pain is during recoveries.  In the thick of things we keep forgetting to pre-create the QIO files before restoring datafiles to an alternate location.  The recoveries are successful but we pay a performance penalty until we negotiate downtime to QIO-enable the recovered files (which is really renaming the data file and creating a link with a specific pattern that triggers the code path for QIO handling.)

As for the speed claims coming from your salesperson, all I can say is that we get pretty close to raw speeds.  I highly recommend it especially for online redo logs, and it's easy to revert to regular files if you change your mind.

HTH

Tony Aponte


-Original Message-

From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]

Sent: Friday, March 14, 2003 11:14 AM

To: Multiple recipients of list ORACLE-L

Subject: Veritas Quick I/O for Oracle



Is anyone using Veritas Quick I/O for Oracle? We are purchasing some new

Solaris systems with fiber channel and Veritas File System, and the Veritas

salesperson is claiming "up to 400 times faster". I would like to know if

anyone else has discovered this miracle and what benefits you are seeing.

Thanks.

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 

Author: DENNIS WILLIAMS

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: OEM and monitoring clustered databases

[Aponte, Tony]

We got around it by dropping the natural and failover nodes from OEM,
modifying the oratab files to contain only the databases active the
node, bounce the dbsnmp agents, rediscover the nodes and change the
oratab files back to their original.  The trick is in the discovery of
the services on that node, the agents look through the oratab file for
that info.  We have to do this every time a database fails or is
switched over manually.

HTH
Tony Aponte

-Original Message-
Sent: Wednesday, March 05, 2003 2:35 PM
To: Multiple recipients of list ORACLE-L


I've had problems trying to use OEM (9.0.1) to monitor clustered
databases.
The problem is that each database is set up in it's own resource group,
therefore each has it's own virtual hostname. Even though I may have
several
databases all running on the same physical host, they all have different
virtual hostnames that move with the database when it fails over to
another
node in the cluster. When you discover a node, it picks up all of the
databases on the node and registers them with the OMS as being on that
node.
Once one of them fails over it's no longer on that node and can't be
monitored from OEM anymore. I'm sure others on this list are using this
type
of failover for high availability. How did you get around it?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chuck Hamilton
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: a DIFFERENT sql question

[Aponte, Tony]

Title: RE: a DIFFERENT sql question






SELECT DISTINCT 

 t1.category CAT

,t2.type TYP

,SUM(DISTINCT t1.amount1) OVER(PARTITION BY t1.category) Sum1

,SUM(t2.amount2) OVER(PARTITION BY t1.category, t2.type) Sum2

FROM

 t1

,t2

WHERE

 t1.mykey1 = t2.mykey1

/


CAT TYP SUM1    SUM2

AA  x   8   27

AA  y   8   6

AA  z   8   10

BB  y   50  27



HTH

Tony APonte

Home Shopping Network



-Original Message-

From: STEVE OLLIG [mailto:[EMAIL PROTECTED]]

Sent: Thursday, March 13, 2003 2:24 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: a DIFFERENT sql question



ok - i came up with a solution.  but in real life i have a lot of amount1's

in t1 so it becomes an ugly brute force looking query.  anybody have a more

elegant solution?



  1  select a.category

  2   , (select sum(s.amount1) from t1 s where a.category = s.category)

as amount1sum

  3   , b.type

  4   , sum(b.amount2)

  5    from t1 a

  6   , t2 b

  7   where a.mykey1 = b.mykey1

  8   group by

  9 a.category

 10*  , b.type

SQL> /


CATEG AMOUNT1SUM TYPE  SUM(B.AMOUNT2)

- -- - --

AA 8 x 27

AA 8 y  6

AA 8 z 10

BB    50 y 27


4 rows selected.


SQL> 


-Original Message-

Sent: Thursday, March 13, 2003 10:49 AM

To: Multiple recipients of list ORACLE-L



since we're having fun with SQL today - here's one that's hurting my brain

at the moment.  I need to sum columns at 2 different groupings in my

resultset.  The first select is perfect; the 2nd is where i have trouble;

but i know i can do stuff like the 3rd example.  How do I get the 2nd one to

work?


SQL SCRIPT:

drop table t1;

drop table t2;

create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5));

create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5),

amount2 number(5));

insert into t1 values (1, 'AA', 5);

insert into t1 values (2, 'AA', 3);

insert into t1 values (3, 'BB', 50);

insert into t2 values (1, 1, 'x', 1);

insert into t2 values (2, 1, 'x', 2);

insert into t2 values (3, 1, 'y', 6);

insert into t2 values (4, 2, 'x', 4);

insert into t2 values (5, 2, 'z', 10);

insert into t2 values (6, 2, 'x', 20);

insert into t2 values (7, 3, 'y', 12);

insert into t2 values (8, 3, 'y', 15);

select a.category

 , a.mykey1

 , sum(distinct a.amount1)

 , b.type

 , sum(b.amount2)

  from t1 a

 , t2 b

 where a.mykey1 = b.mykey1

 group by

   a.category

 , a.mykey1

 , a.amount1

 , b.type

/

select a.category

--   , a.mykey1

 , sum(distinct a.amount1)

 , b.type

 , sum(b.amount2)

  from t1 a

 , t2 b

 where a.mykey1 = b.mykey1

 group by

   a.category

--   , a.mykey1

--   , a.amount1

 , b.type

/

select decode(grouping(a.category), 1, 'All', a.category) as category

 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job

 , decode(grouping(b.type), 1, 'All', b.type) as type

 , count(*)

 , sum(distinct a.amount1)

 , sum(b.amount2)

  from t1 a

 , t2 b

 where a.mykey1 = b.mykey1

 group by rollup

 ( a.category

 , a.mykey1

 , b.type

 )

order by

   a.category

 , a.mykey1

 , b.type

/


here's what i get:


SQL> select a.category

  2   , a.mykey1

  3   , sum(distinct a.amount1)

  4   , b.type

  5   , sum(b.amount2)

  6    from t1 a

  7   , t2 b

  8   where a.mykey1 = b.mykey1

  9   group by

 10 a.category

 11   , a.mykey1

 12   , a.amount1

 13   , b.type

 14  /


CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)

- -- -- - --

AA 1  5 x  3

AA 1  5 y  6

AA 2  3 x 24

AA 2  3 z 10

BB 3 50 y 27


5 rows selected.


perfect.


but this is the problem query:


SQL> select a.category

  2  --   , a.mykey1

  3   , sum(distinct a.amount1)

  4   , b.type

  5   , sum(b.amount2)

  6    from t1 a

  7   , t2 b

  8   where a.mykey1 = b.mykey1

  9   group by

 10 a.category

 11  --   , a.mykey1

 12  --   , a.amount1

 13   , b.type

 14  /


CATEG SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)

- -- - --

AA 8 x 27

AA 5 y  6

AA 3 z 10

BB    50 y 27


4 rows selected.


wrong.  i want the resultset to look like this:


CATEG SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)

- --

RE: Oracle, Siebel and rule-based optimization

[Aponte, Tony]

Take a look at the query plan stability features.  I believe the original purpose was 
to facilitate migration from RBO to CBO.  Its been looking good for the small proof of 
concepts I have done with it.  My overall plan is to capture all plans while in RBO, 
turn on usage of Stored Outlines and switch to CBO.  The hard part is bringing people 
and process into this strategy.  The developers need to provide the outlines for new 
SQL that will be emitted from the app.  The primary tools are Siebel trace files and 
TOAD's explain plan output.

HTH
Tony Aponte  

-Original Message-
Sent: Friday, January 31, 2003 3:19 AM
To: Multiple recipients of list ORACLE-L


One relatively effective way to convert an app from
rule to cost is to enable cost in a very
"rule-oriented" way (namely, you want indexes to be
chosen almost religiously).

You enable cbo but set things like
optimizer_index_cost_adj and optimizer_index_caching
to very index favourable values.  I remember when SAP
went to rule to "cost" and whilst I'm sure some code
changes took place I also remember setting the adjust
parameter to '10' (instead of 100) so nested loop
access was still the dominant access path.  

Of course, this all needs testing and your good
friends at Siebel would probably not support you - but
I've seen it used very successfully a number of times
in the past because you get "rule-like" access paths
but all the goodies that go with cbo as well.

hth
connor

 --- Michael Fontana <[EMAIL PROTECTED]> wrote: > 
> 
> I would like to ask a question of this esteemed and
> knowledgeable audience 
> about running
> SIEBEL applications on Oracle.  Siebel insists that
> their CRM application 
> will only run with
> acceptable performance if rule-based optimization is
> chosen, yet, as we all 
> know, there are
> documented examples of poor performance, and strong
> recommendations to the 
> contrary from Oracle.
> 
> At one point, I recall reading an article from a
> knowledgeable consultant 
> describing what must be done
> to get Siebel to run with optimizer_mode=choose. 
> Any assistance in 
> reaching this goal, or comments,
> would be more than appreciated.  We're still in the
> implementation phase, 
> and are already receiving
> poor performance on certain processes, even using
> the recommended rule 
> based optimization.  I
> predict it will only get worse!
> 
> 
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Rolling up Sums

[Aponte, Tony]

Title: RE: Rolling up Sums








create table genledg (budget number(4),week number(2)) tablespace tools



insert into genledg values (100,1)


insert into genledg values (100,2)


insert into genledg values (100,3)


insert into genledg values (100,4)


commit



Select sum(budget) over (order by week) budget, week from genledg


BUDGET  WEEK

100 1

200 2

300 3

400 4


HTH

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, January 07, 2003 2:26 PM

To: Multiple recipients of list ORACLE-L

Subject: Rolling up Sums




I have some sql(below) that when run gives me Result #1.

What I'm trying to do is to have week 1 roll into week 2, and the total

from week 2 roll into week 3 and so forth.

I've played around with the "rollup" function but I can't get it to come

out like Result #2.

Anyone have any ideas or a way I could get the "rollup" function to produce

results like #2?


SELECT ROUND(SUM(t1.baseline_budget),0) cc_budget,

   t2.week_seq

  FROM ipcs.all_cost_view_outage t1,

    ipcs.week_detail t2

 WHERE t2.week_seq = t1.week_seq

   AND t2.week_detail.proj_id = 2108

 GROUP BY t2.description,

 t2.week_seq;


Result #1

   Budget    Week

  100   1

  100   2

  100   3

  100   4


Result #2

   Budget    Week

  100   1

  200   2

  300   3

  400   4


Thanks all...

[EMAIL PROTECTED]



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: 1 Volume of Software RAID 1+0 versus Multiple Volumes of Soft

[Aponte, Tony]

Title: RE: 1 Volume of Software RAID 1+0 versus Multiple Volumes of Soft






Would you happen to know how the software layer that combined LUNs to make it look like a large file system was enabled?  We're experiencing similar issues with one of our Clarion arrays.

TIA

Tony Aponte


-Original Message-

From: John Kanagaraj [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, October 29, 2002 9:13 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: 1 Volume of Software RAID 1+0 versus Multiple Volumes of Soft


Yes - and I just completed stress-testing an OLTP database were we went

from 2 distinct 'filesystems' for data files to 12 separate filesystems.

Average wait times for I/O dropped from 40 - 50 msec (yes 40 - 50 msec!) to

about 10 - 13 msec. All this was on a Disk Array and all we did was to 

convince the SAs to chop it up this way. Apparently, some software layers at 

the Array level that 'combined' LUNs to make it look like a lar

RE: logon trigger

[Aponte, Tony]

 damages
of whatsoever nature, arising as a result of the reliance on such
information by anyone. 
 
Whilst all reasonable steps are taken to ensure the accuracy and
integrity of information transmitted electronically and to preserve the
confidentiality thereof, Dimension Data accepts no liability or
responsibility whatsoever if information or data is, for whatsoever
reason, incorrect, corrupted or does not reach its intended destination. 

*


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Leonard (ZA)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: urgent help: replication, shareplex

[Aponte, Tony]

Title: RE: urgent help:  replication, shareplex






I suggest you take a peek at the waits for the Shareplex process on the source system.  We found that the sp_ordr processes query the source tables via the rowid mined out of the redo stream.  Sp_ocap also issues queries as well as perform DML on the Shareplex rowid map table.  

I don't see how the single-row commits would have an impact on the replication.  Shareplex doesn't wait for the source's commit before sending the update to the target so that activity remains the same.  Upon encountering a commit in the redo stream, it then sends a message to the target so that it also does a commit.  We do approximately 40 commits per second consisting of multiple DML operations and we don't any latency (when everything is working correctly.)

HTH

Tony Aponte


-Original Message-

From: Ji, Richard [mailto:[EMAIL PROTECTED]]

Sent: Monday, September 30, 2002 1:09 PM

To: Multiple recipients of list ORACLE-L

Subject: urgent help: replication, shareplex



Hi All,


I needed some help here involving shareplex.


We run two databases (an OLTP type, and a repository type) on the same E10K

domain

which has 8 CPUs and 8GB of RAM, using Hatachi SAN (RAID 5).  Shareplex is

being used

to replicate a table from the OLTP type to the repository.  The current

volume we are getting

is about 40 inserts per second to the OLTP.  And at this rate shareplex is

lagging behind

doing the replication, for 24 hours now.  And I see the shareplex process

running at 10%

while all Oracle processes are below 1% of the CPU.


The situation is complicated, because it involves a hosting company.  For

instance, I would

like to run the two databases on two separate domains but they chose not to.

So they

are blaming our application for doing commit on every insert being the

problem.  I agree that

(and I will make the change) to commit every 1000 inserts or so.  However, I

don't believe

that's the root of the problem.  From what I understand, shareplex is log

based replication

and should not be as resource instensive.  And 40 per/second isn't a huge

volume per se

and I am sure shareplex can handle a lot more than that.


So any suggestions, feedbacks are welcome.


Thanks


Richard

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Ji, Richard

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: using obfuscation

[Aponte, Tony]

Title: RE: using obfuscation






I was wondering if this was of any help.


Tony Aponte


-Original Message-

From: Aponte, Tony 

Sent: Tuesday, September 17, 2002 7:04 PM

To: '[EMAIL PROTECTED]'

Cc: '[EMAIL PROTECTED]'

Subject: RE: using obfuscation



I've been developing a solution for a similar requirement.  Although I reached a dead-end with this thread I think it solves your problem.

I'm picking it up from the point where the data in encrypted_data of sensitive_table needs encryption. I did that with an anonymous PL/SQL block:

CREATE TABLE sensitive_table (encrypted_data VARCHAR2(30), clear_text VARCHAR2(30));


I'll populate the table with text 16 characters long.  I used 16 to simplify the example.  DBMS_OBFUSCATION_TOOLKIT needs the data length to be a multiple of 8:

INSERT INTO sensitive_table 

SELECT TO_CHAR(ROWNUM,'fm0009'),TO_CHAR(ROWNUM,'fm0009') FROM DBA_OBJECTS WHERE ROWNUM < 1000;


COMMIT;


Now run the anonymous block to encrypt the data in the encrypted_data column:


--Encrypt data

DECLARE

CURSOR xtab IS SELECT encrypted_data FROM sensitive_table FOR UPDATE;

input_string    VARCHAR2(16) ;

raw_input   RAW(400) ;

key_string  VARCHAR2(8):= 'abcde123';

raw_key RAW(400) := UTL_RAW.CAST_TO_RAW(key_string);

encrypted_raw   RAW(2048);

error_in_input_buffer_length EXCEPTION;

PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);

INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=

    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';

double_encrypt_not_permitted EXCEPTION;

PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);

DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=

    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

BEGIN

FOR xrec IN xtab LOOP

    input_string:=xrec.encrypted_data;

    raw_input:= UTL_RAW.CAST_TO_RAW(input_string);

    DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input => raw_input,key => raw_key, encrypted_data => encrypted_raw );

    UPDATE sensitive_table 

       SET encrypted_data = UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw)

 WHERE CURRENT OF xtab; 

END LOOP;

COMMIT;

EXCEPTION

  WHEN error_in_input_buffer_length THEN

   dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);

END;

/


I want to create a package (spec only) to contain global variables to be used by my encrypt/decrypt functions:


CREATE OR REPLACE PACKAGE cc_security_pkg

IS

g_KeyString_txt     VARCHAR2(8):= 'abcde123';

g_Key_raw   RAW(400) := UTL_RAW.CAST_TO_RAW(g_KeyString_txt);

g_Input_raw RAW(400) ;

g_Decrypted_raw RAW(2048);

g_Encrypted_raw RAW(2048);

g_ErrorInInputBufferLength_exc EXCEPTION;

PRAGMA EXCEPTION_INIT(g_ErrorInInputBufferLength_exc, -28232);

g_InputBufferLengthErrMsg_txt VARCHAR2(100) :=

    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';

g_DoubleEncrypt_exc EXCEPTION;

PRAGMA EXCEPTION_INIT(g_DoubleEncrypt_exc, -28233);

g_DoubleEncryptionErrMsg_txt VARCHAR2(100) :=

    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

--

END;

/


Here is the decrypt function.  Not much to explain:


CREATE OR REPLACE cc_decrypt(p_Input_txt VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

  DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT => UTL_RAW.CAST_TO_RAW(p_Input_txt)

                                 ,KEY => cc_security_pkg.g_Key_raw

                                 ,DECRYPTED_DATA => cc_security_pkg.g_Decrypted_raw );

  RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Decrypted_raw));

END cc_decrypt;

/


This is the corresponding encryption function.  Two things to note, the use of the DETERMINISTIC pragma and the conflicting use of the value of CLIENT_INFO to determine the return value (encrypted or not.)  I am deliberately (mis?)using DETERMINISTIC to trick Oracle into trusting that I will always return the same value for p_Input_txt.  I'll explain why later on when I get to the function-based index.  But for now all you need to know is that when the value of CLIENT_INFO equals 'buildingindex' I simply return the original value passed to the function.  Otherwise the parameter is decrypted and returned:

CREATE OR REPLACE FUNCTION cc_encrypt(p_Input_txt VARCHAR2)

RETURN VARCHAR2

DETERMINISTIC

IS

BEGIN

  IF USERENV('CLIENT_INFO') = 'buildingindex' THEN

     RETURN(p_Input_txt);

  ELSE

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT => UTL_RAW.CAST_TO_RAW(p_Input_txt)

                                 ,KEY => cc_security_pkg.g_Key_raw

                                 ,ENCRYPTED_DATA => cc_security_pk

RE: Spool Oracle Tables into Excel Format

[Aponte, Tony]

Title: RE: Spool Oracle Tables into Excel Format






There were some posts recently suggesting the spooling of the columns using a comma as a separator.  But check out this article in XML Journal (http://www.syntelinc.com/syntel/english/0072/SYNT_XMLjrnl.pdf).  It shows how to build preformatted Excel reports with XML, but it requires Excel 2002.

HTH

Tony Aponte


-Original Message-

From: Bob Robert [mailto:[EMAIL PROTECTED]]

Sent: Friday, September 20, 2002 4:33 PM

To: Multiple recipients of list ORACLE-L

Subject: Spool Oracle Tables into Excel Format



All,


Is it possible to create Oracle reports into Excel

format ? 

Is it possible to spool Oracle tables into Excel

format?


Thanks in Advance

Bob


__

Do you Yahoo!?

New DSL Internet Access from SBC & Yahoo!

http://sbc.yahoo.com

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Bob Robert

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

San Diego, California    -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: using obfuscation

[Aponte, Tony]

Title: RE: using obfuscation






I've been developing a solution for a similar requirement.  Although I reached a dead-end with this thread I think it solves your problem.

I'm picking it up from the point where the data in encrypted_data of sensitive_table needs encryption. I did that with an anonymous PL/SQL block:

CREATE TABLE sensitive_table (encrypted_data VARCHAR2(30), clear_text VARCHAR2(30));


I'll populate the table with text 16 characters long.  I used 16 to simplify the example.  DBMS_OBFUSCATION_TOOLKIT needs the data length to be a multiple of 8:

INSERT INTO sensitive_table 

SELECT TO_CHAR(ROWNUM,'fm0009'),TO_CHAR(ROWNUM,'fm0009') FROM DBA_OBJECTS WHERE ROWNUM < 1000;


COMMIT;


Now run the anonymous block to encrypt the data in the encrypted_data column:


--Encrypt data

DECLARE

CURSOR xtab IS SELECT encrypted_data FROM sensitive_table FOR UPDATE;

input_string    VARCHAR2(16) ;

raw_input   RAW(400) ;

key_string  VARCHAR2(8):= 'abcde123';

raw_key RAW(400) := UTL_RAW.CAST_TO_RAW(key_string);

encrypted_raw   RAW(2048);

error_in_input_buffer_length EXCEPTION;

PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);

INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=

    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';

double_encrypt_not_permitted EXCEPTION;

PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);

DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=

    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

BEGIN

FOR xrec IN xtab LOOP

    input_string:=xrec.encrypted_data;

    raw_input:= UTL_RAW.CAST_TO_RAW(input_string);

    DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input => raw_input,key => raw_key, encrypted_data => encrypted_raw );

    UPDATE sensitive_table 

       SET encrypted_data = UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw)

 WHERE CURRENT OF xtab; 

END LOOP;

COMMIT;

EXCEPTION

  WHEN error_in_input_buffer_length THEN

   dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);

END;

/


I want to create a package (spec only) to contain global variables to be used by my encrypt/decrypt functions:


CREATE OR REPLACE PACKAGE cc_security_pkg

IS

g_KeyString_txt     VARCHAR2(8):= 'abcde123';

g_Key_raw   RAW(400) := UTL_RAW.CAST_TO_RAW(g_KeyString_txt);

g_Input_raw RAW(400) ;

g_Decrypted_raw RAW(2048);

g_Encrypted_raw RAW(2048);

g_ErrorInInputBufferLength_exc EXCEPTION;

PRAGMA EXCEPTION_INIT(g_ErrorInInputBufferLength_exc, -28232);

g_InputBufferLengthErrMsg_txt VARCHAR2(100) :=

    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';

g_DoubleEncrypt_exc EXCEPTION;

PRAGMA EXCEPTION_INIT(g_DoubleEncrypt_exc, -28233);

g_DoubleEncryptionErrMsg_txt VARCHAR2(100) :=

    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

--

END;

/


Here is the decrypt function.  Not much to explain:


CREATE OR REPLACE cc_decrypt(p_Input_txt VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

  DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT => UTL_RAW.CAST_TO_RAW(p_Input_txt)

                                 ,KEY => cc_security_pkg.g_Key_raw

                                 ,DECRYPTED_DATA => cc_security_pkg.g_Decrypted_raw );

  RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Decrypted_raw));

END cc_decrypt;

/


This is the corresponding encryption function.  Two things to note, the use of the DETERMINISTIC pragma and the conflicting use of the value of CLIENT_INFO to determine the return value (encrypted or not.)  I am deliberately (mis?)using DETERMINISTIC to trick Oracle into trusting that I will always return the same value for p_Input_txt.  I'll explain why later on when I get to the function-based index.  But for now all you need to know is that when the value of CLIENT_INFO equals 'buildingindex' I simply return the original value passed to the function.  Otherwise the parameter is decrypted and returned:

CREATE OR REPLACE FUNCTION cc_encrypt(p_Input_txt VARCHAR2)

RETURN VARCHAR2

DETERMINISTIC

IS

BEGIN

  IF USERENV('CLIENT_INFO') = 'buildingindex' THEN

     RETURN(p_Input_txt);

  ELSE

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT => UTL_RAW.CAST_TO_RAW(p_Input_txt)

                                 ,KEY => cc_security_pkg.g_Key_raw

                                 ,ENCRYPTED_DATA => cc_security_pkg.g_Encrypted_raw );

     RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Encrypted_raw));

  END IF;

END cc_encrypt;

/


Now I want to be able to use and index when the query includes sensitive_table.encrypteed_data in the predicate.  I believe this is where you are stuck.  I want to execute this query that doesn't require pre-encryption of 0010:

SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_tabl

RE: Table Locks

[Aponte, Tony]

Title: RE: Table Locks






Call me crazy if you wish.  But I would take a process or system state dump and navigate the locking session's object hierarchy.  Yes, I know, ugly as Sin and potentially life-shortening.

HTH

Tony Aponte


-Original Message-

From: Alan Davey [mailto:[EMAIL PROTECTED]]

Sent: Thursday, August 29, 2002 4:44 PM

To: Multiple recipients of list ORACLE-L

Subject: Table Locks



Hi All,


I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks.  In this example, the lock isn't being released because the developer forgot to include a commit/rollback.

If I look at v$session which is causing the lock and query v$sqlarea with  the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete).  I can query 

v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it?  Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed?

I'm RTFMing, but so far no luck.  Any help would be greatly appreciated.


Regards,

-- 


Alan Davey

[EMAIL PROTECTED]

212-604-0200  x106




-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Alan Davey

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Changing sysdate

[Aponte, Tony]

Title: RE: Constraints problem



I 
remember a Y2K testing parameter for setting the database date.  It still 
might work.  Search for FIXED_DATE to see what NLS date format to 
use.
 
HTH
Tony 
Aponte

  -Original Message-From: Karthikeyan S 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 
  2002 2:48 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Changing sysdate
  Hi,
   
  Is 
  it possible to change the sysdate?  (Solaris 5.8, Oracle 8.1.7) 
  
  TIA.
   
  K. 
  
   
   

RE: Re:RE: Re[2]: controlling CPU usage through Oracle

[Aponte, Tony]

Title: RE: Re:RE: Re[2]: controlling CPU usage through Oracle






Just some thoughts.  Does your OS have any domain partitioning features that you can use to create separate "servers"?  You could carve out a portion of the CPU resources into a small domain and dedicate it to your problem child.  The net effect would be to throttle the problem database by denying it the resources used by the others.

How about using processor sets do achieve the same thing.  I'm thinking along the lines of two sets, one with a very low number of CPU's.  You would then bind your problem child to the small set and the rest to the other.


HTH

Tony Aponte

RE: DB Refreshes

[Aponte, Tony]

What 
kind of SAN?  
 
Tony 
Aponte

  -Original Message-From: Gulamabbas Sikiladha 
  [mailto:[EMAIL PROTECTED]]Sent: 
  Monday, August 26, 2002 3:08 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: DB Refreshes
  Thanks for the response Dennis, I got a very good idea from your email, 
  here is my situation our OLTP DB is 24/7 but the reporting DB has to be 
  refreshed on a nightly basis thus I can consider the snapshot. The only 
  problem I see with this, I will have to have a snapshot for over 2092 tables. 
  Will have to read more about snapshot documentation and how best to implement 
  this. We are planning to use RMAN for backups/restores so maybe that could be 
  another option. We have 2 separate servers one for the OLTP and 2nd for the 
  DSS sharing the SAN. 
  Any other suggestions/comments more than welcomed 
  Thanks 
   

RE: Analyzing a Trigger for Performance

[Aponte, Tony]

Title: RE: Analyzing a Trigger for Performance






Hi Hannah,


Have you looked into the DBMS_PROFILER and DBMS_TRACE packages?  They can be used to trace PL/SQL execution and performance.  

Tony Aponte

Home Shopping Network, Inc.


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, August 21, 2002 5:01 PM

To: Multiple recipients of list ORACLE-L

Subject: Analyzing a Trigger for Performance



Hi,


I had noticed that one of our triggers is performing very badly.  I had disabled the trigger to test replication and there was a issue with the trigger. When the developer changed the trigger and enabled it, replication slowed down to one record per second (other tables are being updated, instered, and deleted from).

    I analyzed the plans for all sql statements generated in the trigger itself and they are all using the correct indices etc.  There are no full table scans etc.  We have simliar trigger on our other replicated tables that are not seeing THIS bad performance.

    Any one have any suggestions/links for putting this trigger through the paces to determine the bottleneck?


Thanks,


    Hannah

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: simple problem

[Aponte, Tony]

Here's 
an answer I posted to a similar question a few weeks ago.
 
HTH
 
Tony 
Aponte
 
 
 
I hope this is not to late for you.  Anyway, this questions 
comes up often.  Below is the solution to pivot rows for up to 12 values of 
field1.  Just adjust to fit your range of values.
HTH Tony Aponte Home Shopping Network, Inc. 
create table tab1 (field1 number,field2 varchar2(30)); 
insert into tab1 values( 1,'RAM'); insert into tab1 values( 1,'SHAM'); insert into 
tab1 values( 1,'PAT'); insert into tab1 values( 
2,'MAN'); insert into tab1 values( 2,'JOHN'); 
commit; 
SELECT g1 ,MAX(DECODE(line_no,01,value,NULL)) || MAX(DECODE(line_no,02,value,NULL)) || MAX(DECODE(line_no,03,value,NULL)) || MAX(DECODE(line_no,04,value,NULL)) || MAX(DECODE(line_no,05,value,NULL)) || MAX(DECODE(line_no,06,value,NULL)) || MAX(DECODE(line_no,07,value,NULL)) || MAX(DECODE(line_no,08,value,NULL)) || MAX(DECODE(line_no,09,value,NULL)) || MAX(DECODE(line_no,10,value,NULL)) || MAX(DECODE(line_no,11,value,NULL)) || MAX(DECODE(line_no,12,value,NULL)) FROM (SELECT 
g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no 
FROM (SELECT field1 g1,field2 value from tab1) 
) GROUP BY g1; 
G1  
MAX(DECODE(LINE_NO,01,VALUE,NU 1   RAMSHAMPAT 2   MANJOHN 
-Original Message- From: 
Ramasubramanian, Shankar (Cognizant) [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, July 18, 2002 4:35 PM To: Multiple recipients of list ORACLE-L Subject: 
SQL Query 
Hi Friends, 
    I just need a help 
in a sql . I am having rows in a table as follows 
Field1(ID)    Field2(NAME) -- 1   
      RAM 1   
      SHAM 1   
      PAT 2   
      MAN 2   
      JOHN 
Now i want the output to be as follows 
FIELD1  FIELD2 -- 1   
    RAMSHAMPAT 2   
    MANJOHN 
In the output i have to show all the names for the same id in a 
single row. Please help me in getting this output using 
a SQL query  and not through cursors. 
Thanks in advance. 
Regards, Shankar 

 
 

  -Original Message-From: Shishir Kumar Mishra 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 9:53 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  simple problem
  Hi Lists!
   I have one simple problem.  My query 
  is following SELECT 
  SOD.DESCRIPTIONFROM SO_TRN_DETAIL 
  SODWHERESOD.SO_TRN_ID =90
  and result is : 
   PREM_NET TAX_SPF TAX_SUR FEE_PDCR FEE_INSP PREM_GROSS COM_GROSS COM_PDCR
   
  I want to concatenate all strings in a single 
  string and want to display as a single record using SQL. I had seen 
  solution somewhere but i can not search in archive.  
  How to do that?
   
  Thanks in advance ...Shishir Kumar MishraAgni Software (P) Ltd.www.agnisoft.com--Vidya 
  Dadaati Viniyam--
   
   

RE: db locking quandry

[Aponte, Tony]

Title: RE: db locking quandry





  
  

  Doc ID: 


  Note:1057439.6
  

  Subject: 

  ORA-03113 OR TNS-12571 - INCREASING TCP/IP 
  RETRANSMISSIONS

  -Original Message-From: Magaliff, Bill 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 
  2:24 PMTo: Aponte, Tony; [EMAIL PROTECTED]Subject: 
  RE: db locking quandry
  Can 
  you point me to the note on MetaLink that had this TCP 
  setting?
   
  thx
  bill 
  
  
-Original Message-From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 2:15 
PMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: db locking 
quandry
We had a similar situation that ended up being a network 
setting issue.  The server was showing blocking locks.  It turned 
out to be that the client application was getting a network error.  
After the error it re-established the database connection and re-submitted 
the transaction.  The problem on the server side was that the Oracle 
process had not detected the client's death and was waiting for 'SQL*Net 
message from client'.  We changed the Windows TCP setting for the 
number of retry attempts to 15 (as per Metalink) and the so-called locking 
problem has not been seen since.  I guess we could have enabled Dead 
Connection Detection but we decided to fix it at the source.
HTH Tony Aponte Home Shopping Network, Inc. 
-Original Message- From: 
Magaliff, Bill [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, August 20, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject: db locking quandry 
We have a client running OPS (no load balancing or 
transparent failover enabled due to middle-tier 
software limitation) who is running into db locking 
issues.  Not sure they're related to OPS but pursuing that line 
of thought. 
Here's the basic scenario: 
1)  application (ours) access 
Oracle 8.1.7 via standard Net8 . . . had been 
divided so that different userid's go through different OPS nodes, 
but we disabled that for testing 
2)  multiple sessions each 
running lengthly transactions involving many tables 
(up to 20) - each txn inserts one or several rows into each of these 
20 tables and then commits at the end 
3)  application log files 
showed txn's hanging while inserting into the n'th 
table in the list.  Realized that for each of these tables INITRANS 
had been set to 1.  bumped that up on most of 
these tables (to either 8 or 16, depending on how 
much we anticipate each table being hit) and that seemed to get us further along in the list.  But they still encounter 
locking.  Oracle recommended changing 
GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still 
occurs.  System state dumps and trace files 
show a variety of things, but rather inconsistent  - sometime waiting on a high water mark enqueue, 
sometimes (today) waiting on SQLNet message from 
client (in this case it appears that Oracle is 
waiting for the app, but the app logs indiate it's waiting for 
Oracle).  Our client is trying to get a sniffer to 
evaluate potential network issues. 
I've been reading about OPS locking issues - and they might 
try disabling OPS for a day just to see if this 
keeps happening.  
Oh yeah - and of course this is occurring in production and 
is not reproducable on any other system! 
Wanted to throw this out for thoughts of where to look next 
. . . 
thanks -bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill   INET: [EMAIL PROTECTED] 
Fat City Network Services    -- (858) 
538-5051  FAX: (858) 538-5051 San Diego, 
California    -- Public Internet 
access / Mailing Lists  
To REMOVE yourself from this mailing list, send an E-Mail 
message to: [EMAIL PROTECTED] (note EXACT 
spelling of 'ListGuru') and in the message BODY, 
include a line containing: UNSUB ORACLE-L (or the 
name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like 
subscribing). 

RE: db locking quandry

[Aponte, Tony]

Title: RE: db locking quandry






We had a similar situation that ended up being a network setting issue.  The server was showing blocking locks.  It turned out to be that the client application was getting a network error.  After the error it re-established the database connection and re-submitted the transaction.  The problem on the server side was that the Oracle process had not detected the client's death and was waiting for 'SQL*Net message from client'.  We changed the Windows TCP setting for the number of retry attempts to 15 (as per Metalink) and the so-called locking problem has not been seen since.  I guess we could have enabled Dead Connection Detection but we decided to fix it at the source.

HTH

Tony Aponte

Home Shopping Network, Inc.


-Original Message-

From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, August 20, 2002 2:19 PM

To: Multiple recipients of list ORACLE-L

Subject: db locking quandry



We have a client running OPS (no load balancing or transparent failover

enabled due to middle-tier software limitation) who is running into db

locking issues.  Not sure they're related to OPS but pursuing that line of

thought.


Here's the basic scenario:


1)  application (ours) access Oracle 8.1.7 via standard Net8 . . . had

been divided so that different userid's go through different OPS nodes, but

we disabled that for testing


2)  multiple sessions each running lengthly transactions involving many

tables (up to 20) - each txn inserts one or several rows into each of these

20 tables and then commits at the end


3)  application log files showed txn's hanging while inserting into the

n'th table in the list.  Realized that for each of these tables INITRANS had

been set to 1.  bumped that up on most of these tables (to either 8 or 16,

depending on how much we anticipate each table being hit) and that seemed to

get us further along in the list.  But they still encounter locking.  Oracle

recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections

through a single node, which they did but the locking still occurs.  System

state dumps and trace files show a variety of things, but rather

inconsistent  - sometime waiting on a high water mark enqueue, sometimes

(today) waiting on SQLNet message from client (in this case it appears that

Oracle is waiting for the app, but the app logs indiate it's waiting for

Oracle).  Our client is trying to get a sniffer to evaluate potential

network issues.


I've been reading about OPS locking issues - and they might try disabling

OPS for a day just to see if this keeps happening.  


Oh yeah - and of course this is occurring in production and is not

reproducable on any other system!


Wanted to throw this out for thoughts of where to look next . . .


thanks

-bill

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Magaliff, Bill

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Delete performance

[Aponte, Tony]

Title: RE: Delete performance






I would use your method to CTAS but combine it with partitioning in order to overcome the unavailability issue.  The new table would be a single-partition (MAXVALUE) object that would enable the use of EXCHANGE feature.  I posted a nugget a while back describing the use of a one-partition table (and indexes) and then swapped the underlying segment with a normal table on the fly.  The catch is the licensing cost for partitioning.  But we already had it for it's intended use and this "availability" feature was icing on the cake.  This method is replaced by 9i's online reorg feature but we got a good 3 years out of it.

Tony Aponte

Home Shopping Network, Inc.


-Original Message-

From: Tim Gorman [mailto:[EMAIL PROTECTED]]

Sent: Thursday, August 01, 2002 4:29 PM

To: Multiple recipients of list ORACLE-L

Subject: Re: Delete performance



If the table can be "unavailable" for a very brief period of time while this

is happening, I'd suggest performing a PARALLEL NOLOGGING CREATE TABLE AS

SELECT to perform this mass deletion.  Use a WHERE clause in the SELECT

portion of the CTAS that picks up all the rows you want to keep, which is

the logical negation of the WHERE clause you already have for the DELETE.


Advantages:  faster (INSERT operations are always faster than UPDATE or

DELETE), using NOLOGGING is possible (faster, reduce overall impact on

system), no undo is generated (faster, reduce one possible point of

failure), and if you had any ambitions to re-build the table (i.e. get rid

of chained rows, move to locally-managed tablespace), that gets done too.

Same for the associated indices (rebuild them in parallel, nologging,

compute stats).  Also, the original table can be renamed and saved in case

it's ever needed (for fast "rollback", for example)...


Disadvantages:  if table cannot be unavailable for the last-second RENAME

operation when the "old" table is swapped for the "new", which would

invalidate any associated PL/SQL stored objects and open cursors, then this

won't work.  However small that window of unavailability may be, sometimes

you just can't go there...


Just an idea...


- Original Message -

To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>

Sent: Thursday, August 01, 2002 8:03 AM



> Hi all:

>

> Someone at my shop wants to delete about 20% of roes

> in a table (20 rows out of a million). He wants to

> set  a commit frequency (like every 1000 records or

> so)

> to keep the rbs under control.  I am not aware of any

> easy way to do it other then writing a procedure, but

> I may be missing something here. Is there any simple

> way to accomplish this?

>

> Also I have suggested instead of deleting 20% of the

> rows, create a new table as a select and insert the

> rest of the rows into it (then rebuild the indices and

> rename). This can be done in nologging mode, without

> redo logs and rbs segments. Is this a good idea to

> try?

>

> thanks for any info

>

> Gene

>

> __

> Do You Yahoo!?

> Yahoo! Health - Feel better, live better

> http://health.yahoo.com

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: Gurelei

>   INET: [EMAIL PROTECTED]

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California    -- Public Internet access / Mailing Lists

> 

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Tim Gorman

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Brain cramp on analytical functions and grouping.

[Aponte, Tony]

I pivoted the result set on the 
WO column.  This example works for up to 12 distinct values for the CP 
column.  I don't know if you need to pivot it again to get back to the 
original result set but at least it gives you the sort order you 
described.
 
HTH
Tony Aponte
Home Shopping Network, 
Inc.
 
create table work_orders (WO 
VARCHAR2(7),CP VARCHAR2(7))insert into work_orders values 
('W859674','A120003')insert into work_orders values 
('W859674','A120004')insert into work_orders values 
('W859674','A120006')insert into work_orders values 
('W838796','A12')insert into work_orders values 
('W838796','A120003')insert into work_orders values 
('W844656','A12')insert into work_orders values 
('W844656','A120004')insert into work_orders values 
('W849769','A12')insert into work_orders values 
('W849769','A120004')insert into work_orders values 
('W858835','A12')insert into work_orders values 
('W858835','A120003')insert into work_orders values 
('W880717','A120003')insert into work_orders values 
('W880717','A120006')commit
SELECT 
g1 
,MAX(DECODE(line_no,01,value,NULL)) A, 
MAX(DECODE(line_no,02,value,NULL)) B, 
MAX(DECODE(line_no,03,value,NULL)) C, 
MAX(DECODE(line_no,04,value,NULL)) D, 
MAX(DECODE(line_no,05,value,NULL)) E, 
MAX(DECODE(line_no,06,value,NULL)) F, 
MAX(DECODE(line_no,07,value,NULL)) G, 
MAX(DECODE(line_no,08,value,NULL)) H, 
MAX(DECODE(line_no,09,value,NULL)) I, 
MAX(DECODE(line_no,10,value,NULL)) J, 
MAX(DECODE(line_no,11,value,NULL)) K, 
MAX(DECODE(line_no,12,value,NULL)) 
L
FROM (SELECT g1,value,row_number() over(partition by g1 
order by g1 nulls last) line_no 
FROM (SELECT wo g1,cp value from work_orders) 

) 
GROUP BY g1
ORDER BY 2,3,4,5,6,7,8,9,10,11,12,13,1 


 
G1 A B C D E F G H I J K LW838796 A12 A120003  W858835 A12 A120003  W844656 A12 A120004  W849769 A12 A120004  W859674 A120003 A120004 A120006 W880717 A120003 A120006  -Original Message-From: Jesse, Rich 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 07, 2002 2:35 PMTo: Multiple recipients 
of list ORACLE-LSubject: Brain cramp on analytical functions and 
grouping.OK, my brain hurts.  A dev wants a query to return in 
a peculiar sort orderon 8.1.7.2, but I'm having no luck.  He needs 
groups of rows sorted by thewhole of their key values.  That doesn't 
sound right, so maybe an example:Table 
ARI  WO  
    CP      
RC  RN1   
W859674   
A120003     
3 12   
W859674   
A120004     
3 23   
W859674   
A120006     
3 34   
W838796   
A12     
2 15   
W838796   
A120003     
2 26   
W844656   
A12     
2 17   
W844656   
A120004     
2 28   
W849769   
A12     
2 19   
W849769   
A120004     
2 210  
W858835     
A12   2   
111  W858835  
   A120003 
  2   
212  W880717  
   A120003 
  2   
113  W880717  
   A120006 
  2   2In an attempt to breakdown 
the problem, I added columns RC and RN as"COUNT(*) OVER (PARTITION BY WO)" 
and "ROW_NUMBER() OVER (PARTITION BY WOORDER BY CP)", respectively.  I 
also added the row spacing here for clarity.The dev would like the group 
of WO W858835, rows 10 and 11, immediatelyafter WO group W838796 because the 
groups have the same number of rows (RC)and same values of CP within the 
groups.MIN and MAX would work in this case, but if the groups are larger 
than twoit's no guarantee of order.  What I was thinking is a report 
column thatwould be the concatonation of all the CPs for the group, but 
since it'sVARCHAR2 and not numeric, I'm not sure how that could be 
accomplished.Any suggestions, including favorite beers, is more than 
welcome.TIA!Rich 
Jesse   
System/Database 
Administrator[EMAIL PROTECTED]  
Quad/Tech International, Sussex, WI USA--Please see the official 
ORACLE-L FAQ: http://www.orafaq.com--Author: Jesse, Rich  INET: 
[EMAIL PROTECTED]Fat City Network Services    -- 
(858) 538-5051  FAX: (858) 538-5051San Diego, 
California    -- Public Internet access / 
Mailing 
ListsTo 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).

RE: Select Query - Help required

[Aponte, Tony]

Title: RE: Select Query - Help required






I posted an answer on a similar question about 2 weeks ago.  The underlying concept is how to pivot a result set.  I've attached the thread below.

HTH

Tony Aponte

Home Shopping Network




-Original Message-

From: Aponte, Tony 

Sent: Tuesday, July 23, 2002 10:38 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: SQL Query



I hope this is not to late for you.  Anyway, this questions comes up often.  Below is the solution to pivot rows for up to 12 values of field1.  Just adjust to fit your range of values.

HTH 

Tony Aponte 

Home Shopping Network, Inc. 


create table tab1 (field1 number,field2 varchar2(30)); 

insert into tab1 values( 1,'RAM'); 

insert into tab1 values( 1,'SHAM'); 

insert into tab1 values( 1,'PAT'); 

insert into tab1 values( 2,'MAN'); 

insert into tab1 values( 2,'JOHN'); 

commit; 


SELECT 

g1 

,MAX(DECODE(line_no,01,value,NULL)) || 

MAX(DECODE(line_no,02,value,NULL)) || 

MAX(DECODE(line_no,03,value,NULL)) || 

MAX(DECODE(line_no,04,value,NULL)) || 

MAX(DECODE(line_no,05,value,NULL)) || 

MAX(DECODE(line_no,06,value,NULL)) || 

MAX(DECODE(line_no,07,value,NULL)) || 

MAX(DECODE(line_no,08,value,NULL)) || 

MAX(DECODE(line_no,09,value,NULL)) || 

MAX(DECODE(line_no,10,value,NULL)) || 

MAX(DECODE(line_no,11,value,NULL)) || 

MAX(DECODE(line_no,12,value,NULL)) 

FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no 

FROM (SELECT field1 g1,field2 value from tab1) 

) 

GROUP BY g1; 




G1  MAX(DECODE(LINE_NO,01,VALUE,NU 

1   RAMSHAMPAT 

2   MANJOHN 


-Original Message- 

From: Ramasubramanian, Shankar (Cognizant) 

[mailto:[EMAIL PROTECTED]] 

Sent: Thursday, July 18, 2002 4:35 PM 

To: Multiple recipients of list ORACLE-L 

Subject: SQL Query 




Hi Friends, 

    I just need a help in a sql . I am having rows in a table as follows 


Field1(ID)    Field2(NAME) 

-- 

1 RAM 

1 SHAM 

1 PAT 

2 MAN 

2 JOHN 


Now i want the output to be as follows 


FIELD1  FIELD2 

-- 

1   RAMSHAMPAT 

2   MANJOHN 


In the output i have to show all the names for the same id in a single row. 

Please help me in getting this output using a SQL query  and not through 

cursors. 


Thanks in advance. 


Regards, 

Shankar 




-Original Message-

From: karthikeyan S [mailto:[EMAIL PROTECTED]]

Sent: Monday, July 29, 2002 10:08 AM

To: Multiple recipients of list ORACLE-L

Subject: Select Query - Help required



Gurus,


Please read the following problem and help me if you have any solution. 


Select product_id from  where id = 2; 


Product_ID

--

A

B

C

D


But I want the output as follows: 


Select product_id from  where id = 2; 


Product ID

-

ABCD.


Thanks in advance. 


regards,

Karthik 


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: karthikeyan S

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: SQL Query

[Aponte, Tony]

Title: RE: SQL Query






I hope this is not to late for you.  Anyway, this questions comes up often.  Below is the solution to pivot rows for up to 12 values of field1.  Just adjust to fit your range of values.

HTH

Tony Aponte

Home Shopping Network, Inc.


create table tab1 (field1 number,field2 varchar2(30));

insert into tab1 values( 1,'RAM');

insert into tab1 values( 1,'SHAM');

insert into tab1 values( 1,'PAT');

insert into tab1 values( 2,'MAN');

insert into tab1 values( 2,'JOHN');

commit;


SELECT 

g1 

,MAX(DECODE(line_no,01,value,NULL)) || 

MAX(DECODE(line_no,02,value,NULL)) || 

MAX(DECODE(line_no,03,value,NULL)) || 

MAX(DECODE(line_no,04,value,NULL)) || 

MAX(DECODE(line_no,05,value,NULL)) || 

MAX(DECODE(line_no,06,value,NULL)) || 

MAX(DECODE(line_no,07,value,NULL)) || 

MAX(DECODE(line_no,08,value,NULL)) || 

MAX(DECODE(line_no,09,value,NULL)) || 

MAX(DECODE(line_no,10,value,NULL)) || 

MAX(DECODE(line_no,11,value,NULL)) || 

MAX(DECODE(line_no,12,value,NULL)) 

FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no 

FROM (SELECT field1 g1,field2 value from tab1) 

) 

GROUP BY g1;



G1  MAX(DECODE(LINE_NO,01,VALUE,NU

1   RAMSHAMPAT

2   MANJOHN


-Original Message-

From: Ramasubramanian, Shankar (Cognizant)

[mailto:[EMAIL PROTECTED]]

Sent: Thursday, July 18, 2002 4:35 PM

To: Multiple recipients of list ORACLE-L

Subject: SQL Query



Hi Friends,

    I just need a help in a sql . I am having rows in a table as follows


Field1(ID)    Field2(NAME)

--

1         RAM

1         SHAM

1         PAT

2         MAN

2         JOHN


Now i want the output to be as follows


FIELD1  FIELD2

--

1       RAMSHAMPAT

2       MANJOHN


In the output i have to show all the names for the same id in a single row.

Please help me in getting this output using a SQL query  and not through

cursors.


Thanks in advance.


Regards,

Shankar

RE: Where is Oracle 9.2 init.ora?

[Aponte, Tony]

Title: RE: Where is Oracle 9.2 init.ora?






On my version of UNIX (Solaris) moving/removing a file that is in use only affects the directory entry and not the actual payload on disk.  I suspect that the spfile was held open by your sqlplus program and it executed the I/O operation using the open file descriptor.  I've participated in similar (and unplanned) tests with ufs-based file systems where all of the underlying files where rm-ed and the database continued to run.  It crashed when it tried to switch into the next and non-existent redo log files.  A post-mortem on the incident taught us that removing an open file only removed the entry from UNIX's directory file but the cleanup was postponed until the OS closed the last open handle for that i-node.  

HTH

Tony Aponte


-Original Message-

From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]

Sent: Sunday, July 14, 2002 6:58 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: Where is Oracle 9.2 init.ora?



All Right, Larry. Since we have the test servers and databases; and my

Company still pays for 'doing Oracle' the 'scary' way, here is another

'scary thing' I did with SPFILE :) 

(9iR1 on HP)


SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup    < using spfile 


ORACLE instance started.

Total System Global Area   72273416 bytes

Fixed Size   437768 bytes

Variable Size  37748736 bytes

Database Buffers   33554432 bytes

Redo Buffers 532480 bytes

Database mounted.

Database opened.

SQL> show parameter db_cache_size

NAME TYPE    VALUE

 ---

--

db_cache_size    big integer 33554432    


SQL> !mv spfileKED9.ora spfileKED9.ora.bak  <-- hide the spfile


SQL> !ls -l *.ora

-rw-r--r--   1 oracle dba  12920 May 10  2001 initdw.ora


SQL> alter system set db_cache_size=10M scope=both;   <-- try to set a new

value 


System altered.   <--- No problem?  


SQL> show parameter db_cache_size


NAME TYPE    VALUE

 ---

--

db_cache_size    big integer 12582912   


--> New value in effect. 


SQL> !ls -l *.ora

-rw-r--r--   1 oracle dba  12920 May 10  2001 initdw.ora 


--> Still no SFILE 

--> Now, why would not Oracle tell us that there was no spfile to process

SCOPE=BOTH ?


SQL> c/both/spfile

  1* alter system set db_cache_size=10M scope=spfile

SQL> /

alter system set db_cache_size=10M scope=spfile

*

ERROR at line 1:

ORA-27037: unable to obtain file status

HP-UX Error: 2: No such file or directory

Additional information: 3


-->This is what should have happened with SCOPE=BOTH as well, or at least a

warning that SCOPE=BOTH was processed as SCOPE=MEMORY since there was no

SPFILE available. I would not have objected if Oracle re-recreated SPFILE in

the default location and told me so! 


If anyone has seen any mention of this particular behaviour of SCOPE=BOTH, I

would like to know the source of that information. I have searched Metalink,

Google but have not come across any. I have created an iTar with OWS.

Thanks. 


As I said before, SPFILE has some things that need to be made fool proof. 


This time I did not drink prior to doing this 'scary' stuff !!    ;-)  


Regards,


- Kirti 


> -Original Message-

> From: Larry Elkins [SMTP:[EMAIL PROTECTED]]

> Sent: Friday, July 12, 2002 9:03 PM

> To:   Multiple recipients of list ORACLE-L

> Subject:  RE: Where is Oracle 9.2 init.ora?

> 

> Man, it scares the heck out of me too that Jared and Kirti are actually

> "doing Oracle" -- I can't believe companies actually pay them ;-)

> 

> And you two guys, and I'm talking to you Kirti and Jared, probably dig in

> and do things you shouldn't on test boxes just to see how things work and

> to

> learn. FWIW, I've heard rumors about other people doing similar things.

> You've probably even intentionally crashed a DB or pulled the plug just to

> see if you could recover. Shame on you two. You should both be banished

> from

> the list for doing such unconventional things ;-)

> 

> And neither of you will ever be allowed close to a DB I deal with -- I'll

> call ltiu from now on ;-)

> 

> Larry

> > -Original Message-

> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of

> > [EMAIL PROTECTED]

> > Sent: Friday, July 12, 2002 8:08 PM

> > To: Multiple recipients of list ORACLE-L

> > Subject: RE: Where is Oracle 9.2 init.ora?

> >

> >

> > Some of us have been around the block a few times.  :)

> >

> > Editing binary files is no big deal.

> >

> > You neophytes are all the same.

> >

> > Jared

> >

> > ltiu <[EMAIL PROTECTED]>

> > Sent by: [EMAIL PROTECTED]

> > 07/12/2002 04:28 PM

> > Please respond to ORACLE-L

> >

RE: Virtual drive on Solaris

[Aponte, Tony]

Title: RE: Virtual drive on Solaris



Thanks.

  -Original Message-From: Ferenc Mantfeld 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, June 25, 2002 12:08 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Virtual drive on Solaris
  Tony
   
  Ever 
  since I first worked with you, I've always regarded you as best amongst the 
  best (and I don't give that title lightly), and you have substantiated it once 
  again. Thanks for the info, this is great ! Can't wait to freak out some minds 
  :-) !
  Regards: Ferenc Mantfeld Senior Performance Engineer Siebel 
  Performance Engineering Melbourne, 3000, 
  VIC, Australia Only 
  Robinson Crusoe had all his work done by Friday 
  
-Original Message-----From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]]Sent: Monday, 24 June 2002 7:58 
AMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: Virtual drive on 
Solaris
Here is the command sample: 
  mount -f tmpfs -o size=800m 
swap /ramdisk 
We use it to run a small database totally in memory.  
We decided that staying within the Oracle product suite was better for us 
vs. using a real memory-only rdbms.  The mounting of the temporary disk 
is in our server startup scripts along with the other physical file 
systems.  Then another script just copies a cold backup onto the temp 
file system before it starts up the database.  Its been in production 
for almost 4 years now with no issues whatsoever.  
Thinking about it a bit, is the 300 GB not on some sort of 
disk array with a hefty cache fronting it that would be slowing your LGWR 
down?  Maybe it isn't the disk that's holding the LGWR back but instead 
some other resource on the server.  Anyway, I guess this is an easy 
hypothesis to test with the ramdisk.  If you get the performance boost 
that you expect without inducing additional paging then you can look into 
acquiring some solid state disks so that your successors don't totally freak 
out about the ramdisk (we still blow people's mind away when we show them 
our implementation.)
HTH Tony Aponte 
-Original Message- From: 
Ferenc Mantfeld [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, June 23, 2002 10:53 PM To: Multiple recipients of list ORACLE-L Subject: Virtual drive on Solaris 
Hi All 
does anyone have any white paper or info on how to configure 
a dedicated portion of real memory as a virtual 
drive on Solaris ? I want to move my online redo 
logs (4 X 128 M single threaded) for a 300 GB DW onto it, to 
speed up Informatica ETL, since Informatica does not allow 
me to specify /*+ APPEND */ mode of insert. I know I 
will not bypass the SQL layer this way, but at 
least, the LGWR will be writing to memory instead of disk. Thanks in 
advance. 
Regards: Ferenc Mantfeld 
Senior Performance Engineer Siebel 
Performance Engineering Melbourne, 3000, VIC, 
Australia Only Robinson Crusoe had all his work done 
by Friday 
-Original Message- Sent: 
Saturday, 22 June 2002 9:03 PM To: Multiple 
recipients of list ORACLE-L 
On Solaris 
ps -ef -opid,ppid,vsz=VIRTMEM -orss=PHYSMEM 
-opmem,pcpu,user,args 
use: 
psrinfo -v prtconf | grep Mem 
format uname -a 
HTH 
Richard 
-Original Message- Sent: 
Saturday, June 22, 2002 1:38 PM To: Multiple 
recipients of list ORACLE-L 
Good day to everyone... 
I have two questions related to Linux and Solaris... 

* I need do find memory usage (physical, virtual...) of a 
particular proccess. PID is given by by "ps", but 
what aditional parameters I have to provide? At a 
first glance, output of "man ps" vas confusing... 
* How do I find computer's configuration - what CPU, numbers 
of CPUs, clock, amount of memory, number of 
harddrives, what version of OS, what OS patches are 
applied...? 
No, I can't ask sysadmin about that (hard to explain), and, 
no, I don't have any kind of advanced 
manuals... 
Thanks in advance, Vladimir 

-- Please see the official ORACLE-L 
FAQ: http://www.orafaq.com 
-- Author: Vladimir Barac - 
posao   INET: [EMAIL PROTECTED] 
Fat City Network Services    -- (858) 
538-5051  FAX: (858) 538-5051 San Diego, 
California    -- Public Internet 
access / Mailing Lists  
To REMOVE yourself from this mailing list, send an E-Mail 
message to: [EMAIL PROTECTED] (note EXACT 
spelling of 'ListGuru') and in the message BODY, 
include a line containing: UNSUB ORACLE-L (or the 
name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like 
subscribing). -- Please see 
the of

RE: SESSION_CACHED_CURSORS and shared_pool sizing.

[Aponte, Tony]

Title: RE: SESSION_CACHED_CURSORS and shared_pool sizing.






I don't recall where I read this but I remember a few things about it.  As you noted, the use of SESSION_CAHED_CURSORS has an impact on the UGA.  If you are using MTS then that becomes part of the SGA in the shared pool (or large pool depending on your settings.)  Also, attempts by the client to destroy the cursor are ignored.  A beneficial effect is that upon the 3rd execution of a specific cursor (explicit or implicit) the statement's plan is cached in the UGA and subsequent executions are satisfied from that private cache.  This relieves the shared pool (and its memory structures) from the burden of caching those statements, and relieves the session from serializing on the shared pool resources.  The session will look at its private cache first to find SQL and go to the shared pool if it's a miss.  

HTH

Tony Aponte



-Original Message-

From: Jesse, Rich [mailto:[EMAIL PROTECTED]]

Sent: Monday, June 24, 2002 12:34 PM

To: Multiple recipients of list ORACLE-L

Subject: SESSION_CACHED_CURSORS and shared_pool sizing.



Hi all,


Trying to deal with SHARED_POOL sizing and perf tuning for an 8.1.6.0.0 DB

that's used by 3rd party software.  I can't seem to find if higher values of

SESSION_CACHED_CURSORS adversely affects the shared pool in any way.

Specifically, I'm wondering if the higher number of cached cursors can keep

objects from being flushed from the shared pool.


I believe that higher values of SESSION_CACHED_CURSORS require more mem for

the UGAs, but I'm looking for effects on the SGA.


My apologies to Steve Adams -- I haven't read that far in his "Internals"

book yet!  :)


TIA!


Rich Jesse   System/Database Administrator

[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Jesse, Rich

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Virtual drive on Solaris

[Aponte, Tony]

Title: RE: Virtual drive on Solaris






Here is the command sample:


  mount -f tmpfs -o size=800m swap /ramdisk


We use it to run a small database totally in memory.  We decided that staying within the Oracle product suite was better for us vs. using a real memory-only rdbms.  The mounting of the temporary disk is in our server startup scripts along with the other physical file systems.  Then another script just copies a cold backup onto the temp file system before it starts up the database.  Its been in production for almost 4 years now with no issues whatsoever.  

Thinking about it a bit, is the 300 GB not on some sort of disk array with a hefty cache fronting it that would be slowing your LGWR down?  Maybe it isn't the disk that's holding the LGWR back but instead some other resource on the server.  Anyway, I guess this is an easy hypothesis to test with the ramdisk.  If you get the performance boost that you expect without inducing additional paging then you can look into acquiring some solid state disks so that your successors don't totally freak out about the ramdisk (we still blow people's mind away when we show them our implementation.)

HTH

Tony Aponte



-Original Message-

From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]

Sent: Sunday, June 23, 2002 10:53 PM

To: Multiple recipients of list ORACLE-L

Subject: Virtual drive on Solaris



Hi All


does anyone have any white paper or info on how to configure a dedicated

portion of real memory as a virtual drive on Solaris ? I want to move my

online redo logs (4 X 128 M single threaded) for a 300 GB DW onto it, to

speed up Informatica ETL, since Informatica does not allow me to specify /*+

APPEND */ mode of insert. I know I will not bypass the SQL layer this way,

but at least, the LGWR will be writing to memory instead of disk. Thanks in

advance.


Regards:

Ferenc Mantfeld

Senior Performance Engineer

Siebel Performance Engineering

Melbourne, 3000, VIC, Australia

Only Robinson Crusoe had all his work done by Friday



-Original Message-

Sent: Saturday, 22 June 2002 9:03 PM

To: Multiple recipients of list ORACLE-L



On Solaris


ps -ef -opid,ppid,vsz=VIRTMEM -orss=PHYSMEM -opmem,pcpu,user,args


use:


psrinfo -v

prtconf | grep Mem

format

uname -a


HTH


Richard


-Original Message-

Sent: Saturday, June 22, 2002 1:38 PM

To: Multiple recipients of list ORACLE-L



Good day to everyone...


I have two questions related to Linux and Solaris...


* I need do find memory usage (physical, virtual...) of a particular

proccess. PID is given by by "ps", but what aditional parameters I have to

provide? At a first glance, output of "man ps" vas confusing...


* How do I find computer's configuration - what CPU, numbers of CPUs, clock,

amount of memory, number of harddrives, what version of OS, what OS patches

are applied...?


No, I can't ask sysadmin about that (hard to explain), and, no, I don't have

any kind of advanced manuals...


Thanks in advance,

Vladimir


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Vladimir Barac - posao

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Ji, Richard

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Ferenc Mantfeld

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other

RE: what is the latest db that will support ***CLIENT-SERVER***

[Aponte, Tony]

I 
would love to join a list were we can post all of the colossal dumb-isms emitted 
by Oracle Support.
 
Tony 
Aponte

  -Original Message-From: Brian McGraw 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 
  20, 2002 11:29 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: what is the latest db that will support 
  ***CLIENT-SERVER***
  
  True Story... Names have 
  not been changed to protect the innocent.
   
  I had an issue with a 
  standby database about a week ago, and had found a solution on Metalink after 
  logging the iTar.  The analyst called, and the conversation went 
  something like this:
   
  Me:  Thanks for 
  calling, but I found the solution on Metalink.
   
  Him:  So you know 
  that it's impossible??
   
  Me:   No... I 
  found the documents on Metalink detailing how to do it.
   
  Him:  Well, that's 
  Metalink.  I'm with support - we're the experts.
   
  Me:   Here are 
  the document numbers on Metalink.  You might want to look at them.  
  
   
  Him:  How do I look 
  that up?
   
  : )
   
  --| 
  Brian McGraw /* DBA */  Infinity Insurance || mailto:[EMAIL PROTECTED] 
  |--
   
   
  -Original 
  Message-
   
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Eric 
  D. Pierce
   
  Sent: Thursday, June 20, 
  2002 3:48 AM
   
  To: Multiple recipients of 
  list ORACLE-L
   
  Subject: RE: what is the 
  latest db that will support ***CLIENT-SERVER*** "
   
   
   
   
   
  On 19 Jun 
  2002 at 5:18, Jamadagni, Rajendra 
  wrote:
   
   
   
  > It means to get any 
  kind of support you must be using Developer 6i
   
  with
   
  > patch set 5a or later 
  when connecting with a 9i database.
   
   
   
  ok. 
  thanks.
   
   
   
   
   
  >If OWS says 
  that
   
  > client server is 
  supported only on 817, cross check if they work for
   
  Oracle
   
  > and not outsourced, 
  and of course remind then to read Metalink
   
  regularly.
   
   
   
  ha haa haaa haa 
  h haaa haa hahahahahahahahaa ha haa haaa haa 

   
  h haaa haa 
  hahahahahahahahaa ha haa haaa haa h haaa haa 

   
  hahahahahahahahaa ha haa 
  haaa haa h haaa haa hahahahahahahahaa
   
   
   
   
   
  makes one wonder who the 
  developers of metalink call for "tech support" !!!
   
   
   
  regards,
   
  ep
   
   
   
  -- 
   
  Please see the official 
  ORACLE-L FAQ: http://www.orafaq.com 
   
  
   
  -- 
   
  Author: Eric D. 
  Pierce
   
    INET: 
  [EMAIL PROTECTED]
   
   
   
  Fat City Network 
  Services    -- (858) 538-5051  FAX: (858) 
  538-5051
   
  San 
  Diego, California    
  -- Public Internet access / Mailing Lists
   
  
   
  To REMOVE yourself from 
  this mailing list, send an E-Mail message
   
  to: [EMAIL PROTECTED] 
  (note EXACT spelling of 'ListGuru') and in the message BODY, include a line 
  containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed 
  from).  You may also send the HELP command for other information (like 
  subscribing).
   
   
   

RE: DBMS_SYSTEM.KSDWRT question

[Aponte, Tony]

Title: RE: DBMS_SYSTEM.KSDWRT question






We've been using KSDWRT in logon and table triggers (as well as in maintenance jobs for our 24x7 databases that didn't have a UTL_FILE_DIR entry) and have yet to encounter a problem.  I trussed a background process that used the procedure and it wrote to the files using the same I/O commands that it uses for other commands (like ALTER DATABASE BACKUP CONTROLFILE TO TRACE).

For the other procedures I'd do truss the session connected to a development server to see what they cause at the OS level.

Is there a reason why you aren't using standard auditing to capture DDL activity?


HTH

Tony Aponte


-Original Message-

From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]

Sent: Thursday, June 20, 2002 11:34 AM

To: Multiple recipients of list ORACLE-L

Subject: DBMS_SYSTEM.KSDWRT question



Hi all,


on our 8161 database, I want to capture all the DDLs issued by non SYS users

(believe me, some developers still do). As production locking is away by few

weeks, I wrote a database level trigger to capture all the DDL statements.

But on 8161 it fails due to a bug when a distribution transaction is taking

place (as the trigger uses autonomous transaction).


Alternatively I am thinking of using DBMS_SYSTEM.KSDWRT ... does it have any

such side effects? Basically, does it affect current transaction at all? Is

it transaction independent?


Also on the similar lines, does anyone know what these procedures do? I

tried running them on a test database, but couldn't see anything ... in the

session or in the alert log.


dbms_system.KSDIND;

dbms_system.KCFRMS;

dbms_system.KSDDDT;

dbms_system.KSDFLS;


Thanks in advance

Raj

__

Rajendra Jamadagni      MIS, ESPN Inc.

Rajendra dot Jamadagni at ESPN dot com

Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art!

RE: RE: Advance Oracle Trigger Writing Advice

[Aponte, Tony]

Title: RE: RE: Advance Oracle Trigger Writing Advice






Hi Hannah,


I think you could use a simpler feature to brute-force a trace of your trigger's execution path.  The SYS.DBMS_SYSTEM.KSDWRT procedure provides output facilities for the alert.log and session trace files.  SYS.DBMS_SYSTEM.KSDWRT(1,'I am here') would write that message to the server process' trace file.  SYS.DBMS_SYSTEM.KSDWRT(2,'This message goes to the alert log') does what the message describes, although I myself would pollute the alert log with debug messages.

Just curious on the mainframe tool you are suing.  Could it Databridge?


Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:    

Sent: Tuesday, June 18, 2002 8:38 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: RE: Advance Oracle Trigger Writing Advice



Hi,


    The problem is fairly complex and I am unable to reproduce it by manually feeding in the statements of the suspect transaction.  Both statements hit triggers which use autonomous transactions to do things.  Its the second statement that fails and I am unable to tell if something in the first trigger *primes* a tables, because when you rerun the transaction, it does not error out.  We are using a product to replicate data from a mainframe to Oracle.  The target tables in Oracle have triggers on them.  When the product processes the suspect transactions, an Oracle error is raised.  I can read the transaction in the Mainframe logs and I see that the transactions fail every 100 or so transactions (and obviously I did not manually feed in THAT many).

    I think that I am going to look into dbms_pipe, I have used that in the past to hone in on renegade sql:>  Good idea.  The triggers already both use autonomous trans and I am writing out to a table whenever I enter and leave a block of code.  The last block visited befor error is a select statement only, so I am thinking that something reallly strange is going  on.

    Thanks for the suggestion.  I can get MUCH better control over things with dbms_pipe without effecting the code in the trigger ( I think that the commits can) .  Darn, can't believe that I didn't think about it!

Thanks,


    Hannah

    

>  -Original Message-

> From:     [EMAIL PROTECTED]@SUNGARD   On Behalf Of "Stephane Faroult"<[EMAIL PROTECTED]>

> Sent: Tuesday, June 18, 2002 6:48 AM

> To:   Multiple recipients of list ORACLE-L

> Subject:  RE: RE: Advance Oracle Trigger Writing Advice

> 

> Hanna,

> 

>    I still have a fairly confused view of what your problem is and how you plan to solve it. Jean is right about the solution - autonomous transactions are the easier way (dbms_pipe is the other one) to ensure that you will not lose any track of your debugging information even if the transaction is rolled back.

>   FYI, Oracle 9i provides you (it is documented with DDL triggers, but in fact works with any trigger) with a function which returns the text of the statement which fired the trigger. This function can be faked with previous versions (did it on 7.3, 8.0.5, 8.1.5, 8.1.7). Can be helpful if identifying a faulty statement is what you are looking for. Bind variables can also be caught but it involves a bit of backbending.

> 

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Replication question

[Aponte, Tony]

Title: RE: Replication question






Another potential HA use of AR is that you can use different platforms in an HA configuration.  You can fail over to another platform with some idle capacity or a workload that can be shifted around until the failed services are restored.

Yet another is during planned downtime when upgrading Oracle and OS versions.  You could upgrade the target while the source is the actively used node.  Then move the users off the source and let the remaining changes post to the target.  Now reverse the replication source/target roles and upgrade the old source (now they new target) an let it be until the next time.  The outage should be shorter.  The same technique could be used to roll back to a usable database after an major application change.  Just change the target and let the users try things out.  If there's a problem point them back to the source and regroup.  It should be much quicker that undoing the changes.


Just some thoughts.


Tony Aponte


-Original Message-

From: John Weatherman [mailto:[EMAIL PROTECTED]]

Sent: Friday, June 14, 2002 2:19 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: Replication question



Jarad,


A standby is simpler, however it has limited use for offloading some

system load (opening for read access suspends roll forward, so the

data is somewhat stale).  By using Master-Master synchronous replication 

with good deadlock handlers, you can use BOTH instances so you get the 

benefit of not having an unused instance lying around (damagement hates 

that), but still have fail over available.  Since you can have different 

users/locations attach to different instances, you also get some 

scalability advantages. 


In general, I agree a standby is MUCH simpler.


John P Weatherman

Database Administrator

Replacements Ltd.




-Original Message-

Sent: Friday, June 14, 2002 1:41 PM

To: Multiple recipients of list ORACLE-L



Curious, that note suggests Advanced Replication as a failover 

methodology.


Seems that a standby database would be _much_ simpler. 


Any thoughts ( from anyone ) on why one would use AR for failover, rather 

than using a standby database?


Jared






John Weatherman <[EMAIL PROTECTED]>

Sent by: [EMAIL PROTECTED]

06/14/2002 09:50 AM

Please respond to ORACLE-L


 

    To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>

    cc: 

    Subject:    RE: Replication question



Mitchell,


There are a LOT of good papers in Metalink.  I've been getting my own

education over the last few months.  Replication is a really great swiss

army knife though, you need to do a little looking for what you 

specifically

need to do, then test, test, test.  Oh, and did I mention test? :)  I 

found

Note: 138181.1 particularly helpful.  Oh, and plan on some TARs.  I have

found Support very helpful/informative in this area.


Good Luck,


John P Weatherman

Database Administrator

Replacements Ltd.


-Original Message-

Sent: Friday, June 14, 2002 12:05 PM

To: Multiple recipients of list ORACLE-L



Hi all


I will work on replication soon. any advice for reference I can get.


Thanks in advance.


Mitchell

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: John Weatherman

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: John Weatherman

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and i

RE: I/O contention with external process reading the oracle logs (online redo logs)

[Aponte, Tony]

Title: RE: I/O contention with external process reading the oracle logs (online redo logs)



Actually, for us the percentage is lower since the OLTP 
application we're using it for is heavily indexed ( with the exception 
of single SQL that updates many rows.)  It's one of those claims that is 
usually followed by "your mileage may vary."
 
Tony

  -Original Message-From: Tim Gorman 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 9:23 
  PMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  I/O contention with external process reading the oracle logs (online redo 
  logs)
  It shouldn't need to be a "theoretical" or 
  "statistical" claim at all.  A prospective customer should be able to 
  ship a few archived redo log files (the more the better!) to Quest and have 
  them run it through that part of SharePlex that will read the redo and produce 
  SQL.  I'm surprised they haven't suggested it already...  
  :-)
  
    ----- Original Message - 
From: 
Aponte, Tony 

To: [EMAIL PROTECTED] 
Cc: [EMAIL PROTECTED] 
Sent: Thursday, June 13, 2002 8:47 
AM
Subject: RE: I/O contention with 
external process reading the oracle logs (online redo logs)

I think Yechiel is referring to a statistical claim by Quest 
that only 30% of the redo stream is usable in re-assembling the SQL 
statement.  The rest is like you suspect, index maintenance, rbs 
segment maintenance, etc.  But you are right to point out (so 
right) that a multi-row update by a single SQL on the source results in 
individual updates on the target.  That's a little nugget that the 
marketing folks left out of their 30% claim.
Tony 
-Original Message- From: Tim 
Gorman [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, June 12, 2002 10:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: I/O contention with external process reading the 
oracle logs (online redo logs) 
Just curious, why do you think replication will be less 
bandwidth?  Are you replicating only certain 
schemas/accounts and not the entire database? 
Is Quest asserting that shipping the SQL statements are more 
"compact" than shipping the redo?  That could 
be possible, but I'm quite certain that it is near 
thing, unless the heavily-modified tables in the app have been 
indexed with a heavy hand.  For example, unless 
SharePlex has some remarkable logic, it won't be 
"coalescing" a million-row update into the single SQL statement 
that spawned it, which ironically Oracle's advanced 
replication might be able to do!  Instead, 
they'll need to reverse-engineer individual UPDATE statements for each row, just like Oracle's LogMiner.  The 
only circumstances under which I can imagine 
individual row-level SQL statements being more 
compact that the redo resulting from them is when there are lots 
of large indices on the table... 
--- 
On another note, the 9iR2 "logical standby" feature is a 
direct knockoff of SharePlex, in that the RDBMS 
ships the SQL instead of the redo logfile, so the 
characteristics should be very similar.  Of course, 9iR2 is very new 
and *very* raw at the moment, while SharePlex has 
been around for something like 5-6 years already 
(i.e. eons!), so that should be a strong consideration. But, when I last worked with SharePlex (3.0, I think), it had lots of 
bad habits like demanding "DBA" role to be granted 
to it's account both for installation as well as 
run-time, setting SETUID on executables owned by "root" (17-18 of them! drove the UNIX sysadmins insane!  with 
good reason); just a lot of lazy development 
practices that I hope have been fixed... 
- Original Message - To: 
"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 
Sent: Wednesday, June 12, 2002 8:48 AM (online redo logs) 
> Hello Tim and Rachel > > There is band width problem. The 
line is 256K (we are checking upgrade to > 
512k). > The database, during peek time produce 
10MB of logs every 2-3 minutes. > On this line it 
will take 7-8 minutes to pass 10MB if the line was dedicate > and it is not dedicated. 
> > Upgrading the line to 
more then 512K need E1 at least and it is expansive. > > Since replication will need less 
band width we are checking it. > > To return to my original question: > 
Quest Shareplex - > Any success stories? 
> Why use this and not replication? > Ant performance tests between Shareplex and Oracle 
replication? > > 
Yechiel Adar > Mehish > - Original Message - > To: 
Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]

RE: oracle error logging

[Aponte, Tony]

One 
more link with a complete package.  http://www.quest-pipelines.com/Pipelines/PLSQL/archives.htm#code35
 
Tony

  
-Original Message-From: Aponte, Tony 
Sent: Thursday, June 13, 2002 12:29 PMTo: Multiple 
recipients of list ORACLE-LSubject: RE: oracle error 
logging
PL/SQL Tips & Techniques from Oracle Press 
has a few sections that may interest you.  There's one on 
encapsulating exception blocks, with code to set up an infrastructure for 
exception routines.  Another good on is a complete package to 
encapsulate UTL_FILE exception handling.
 
HTH
Tony Aponte

  -Original Message-From: BigP 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 12, 
  2002 6:54 PMTo: Multiple recipients of list 
  ORACLE-LSubject: oracle error logging
  I am looking for some good design to log and 
  handle oracle /user defined error messgaes in our system , which can be 
  used for debugging and error reporting also .Can any body out there share 
  their experience ?
   
  Bp

RE: Management Reports

[Aponte, Tony]

Title: RE: Management Reports



I'm 
currently working on producing statistics for mid-managers.  We have a lot 
of tools that collect statistics for our 30 production databases so my challenge 
it to map their individual repositories with the categories I want to 
report.  But these are the categories I'm proposing, and they can be 
collected from v$ views and application tables:
 
 Total CPU 
time used
DISK I/O counts
Network I/O counts
Session count (peak and average)
Total number of Oracle transactions
Total number of application transactions (the logical 
unit of work for the application. i.e. orders taken for our Order Entry app., 
packages shipped for Fulfillment, IVR calls, outbound calls placed, 
etc.)
 
HTH
Tony

  
-Original Message-From: Vergara, Michael (TEM) 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 12:26 
PMTo: Aponte, Tony; [EMAIL PROTECTED]Subject: RE: 
Management Reports
Tony:
 
Scorecard/historical.  My management would not know what to 

make of a Spotlight screen if they sat on it.
 
Can I say that?
 
Mike

  -Original Message-From: Aponte, Tony 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 8:33 
  AMTo: [EMAIL PROTECTED]Cc: Vergara, Michael 
  (TEM)Subject: RE: Management Reports
  Is your management looking for real-time charts or are 
  they looking for higher-level scorecard-like info (i.e.. number of 
  transactions yesterday, last 7 days, yada yada yada?)
  Tony Aponte 
  -Original Message- From: 
  Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, June 13, 2002 11:33 AM To: Multiple recipients of list ORACLE-L Subject: Management Reports 
  Good Morning Everyone! 
  My management wants a chart that shows the performance of 
  the database.  If this was your boss, what 
  would you show them? 
  Thanks, Mike 
  P.S.  This is a repeat e-mail.  I never saw my 
  other one hit the list. 
  --- === 
  Michael P. Vergara Oracle 
  DBA Guidant Corporation 
  -- Please see the official 
  ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 
    INET: [EMAIL PROTECTED] 
  Fat City Network Services    -- (858) 
  538-5051  FAX: (858) 538-5051 San Diego, 
  California    -- Public Internet 
  access / Mailing Lists  
  To REMOVE yourself from this mailing list, send an E-Mail 
  message to: [EMAIL PROTECTED] (note EXACT 
  spelling of 'ListGuru') and in the message BODY, 
  include a line containing: UNSUB ORACLE-L (or the 
  name of mailing list you want to be removed from).  You may 
  also send the HELP command for other information (like 
  subscribing). 

RE: oracle error logging

[Aponte, Tony]

PL/SQL 
Tips & Techniques from Oracle Press has a few sections that may 
interest you.  There's one on encapsulating exception blocks, with code to 
set up an infrastructure for exception routines.  Another good on is a 
complete package to encapsulate UTL_FILE exception handling.
 
HTH
Tony 
Aponte

  -Original Message-From: BigP 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 12, 2002 
  6:54 PMTo: Multiple recipients of list ORACLE-LSubject: 
  oracle error logging
  I am looking for some good design to log and 
  handle oracle /user defined error messgaes in our system , which can be used 
  for debugging and error reporting also .Can any body out there share their 
  experience ?
   
  Bp

RE: Management Reports

[Aponte, Tony]

Title: RE: Management Reports






Is your management looking for real-time charts or are they looking for higher-level scorecard-like info (i.e.. number of transactions yesterday, last 7 days, yada yada yada?)

Tony Aponte


-Original Message-

From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]]

Sent: Thursday, June 13, 2002 11:33 AM

To: Multiple recipients of list ORACLE-L

Subject: Management Reports



Good Morning Everyone!


My management wants a chart that shows the performance of the

database.  If this was your boss, what would you show them?


Thanks,

Mike


P.S.  This is a repeat e-mail.  I never saw my other one hit the

list.


---

===

Michael P. Vergara

Oracle DBA

Guidant Corporation


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Vergara, Michael (TEM)

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: oratcl

[Aponte, Tony]

Title: RE: oratcl






I use TCL/TK Tools by Mark Harrison.  It has a chapter on Oratcl.  There's also good information in the OEM's Intelligent Agent Users Guide.

HTH

Tony Aponte


-Original Message-

From: Arslan Bahar [mailto:[EMAIL PROTECTED]]

Sent: Thursday, June 13, 2002 11:29 AM

To: Multiple recipients of list ORACLE-L

Subject: oratcl



How  can I found  a complete reference  or  book   etc  about  oratcl

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Arslan Bahar

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Any Good , Complete Docs , Source , Links on OUTLN ?

[Aponte, Tony]

Title: RE: Any Good , Complete Docs , Source , Links on OUTLN ?






Chapter 11 in Tom Kyte's Oracle one-on-one Export (start here if you can and save yourself time)

Metalink Note 92202.1 How To Specify Hidden Hints in SQL

Oracle Corporation paper by David McElhoes, Stabilizing Query Performance With Stored Outlines

Chapter 10 of the Designing and Tuning for Performance documentation 


Also, just in case you haven't already, try using "Plan Stability" in your web searches.


Good luck.


Tony Aponte


-Original Message-

From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]

Sent: Thursday, June 13, 2002 9:33 AM

To: Multiple recipients of list ORACLE-L

Subject: Any Good , Complete Docs , Source , Links on OUTLN ?



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: VIVEK_SHARMA

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: I/O contention with external process reading the oracle logs (online redo logs)

[Aponte, Tony]

Title: RE: I/O contention with external process reading the oracle logs (online redo logs)






I think Yechiel is referring to a statistical claim by Quest that only 30% of the redo stream is usable in re-assembling the SQL statement.  The rest is like you suspect, index maintenance, rbs segment maintenance, etc.  But you are right to point out (so right) that a multi-row update by a single SQL on the source results in individual updates on the target.  That's a little nugget that the marketing folks left out of their 30% claim.

Tony




-Original Message-

From: Tim Gorman [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, June 12, 2002 10:48 PM

To: Multiple recipients of list ORACLE-L

Subject: Re: I/O contention with external process reading the oracle

logs (online redo logs)



Just curious, why do you think replication will be less bandwidth?  Are you

replicating only certain schemas/accounts and not the entire database?


Is Quest asserting that shipping the SQL statements are more "compact" than

shipping the redo?  That could be possible, but I'm quite certain that it is

near thing, unless the heavily-modified tables in the app have been indexed

with a heavy hand.  For example, unless SharePlex has some remarkable logic,

it won't be "coalescing" a million-row update into the single SQL statement

that spawned it, which ironically Oracle's advanced replication might be

able to do!  Instead, they'll need to reverse-engineer individual UPDATE

statements for each row, just like Oracle's LogMiner.  The only

circumstances under which I can imagine individual row-level SQL statements

being more compact that the redo resulting from them is when there are lots

of large indices on the table...


---


On another note, the 9iR2 "logical standby" feature is a direct knockoff of

SharePlex, in that the RDBMS ships the SQL instead of the redo logfile, so

the characteristics should be very similar.  Of course, 9iR2 is very new and

*very* raw at the moment, while SharePlex has been around for something like

5-6 years already (i.e. eons!), so that should be a strong consideration.

But, when I last worked with SharePlex (3.0, I think), it had lots of bad

habits like demanding "DBA" role to be granted to it's account both for

installation as well as run-time, setting SETUID on executables owned by

"root" (17-18 of them! drove the UNIX sysadmins insane!  with good reason);

just a lot of lazy development practices that I hope have been fixed...


- Original Message -

To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>

Sent: Wednesday, June 12, 2002 8:48 AM

(online redo logs)



> Hello Tim and Rachel

>

> There is band width problem. The line is 256K (we are checking upgrade to

> 512k).

> The database, during peek time produce 10MB of logs every 2-3 minutes.

> On this line it will take 7-8 minutes to pass 10MB if the line was

dedicate

> and it is not dedicated.

>

> Upgrading the line to more then 512K need E1 at least and it is expansive.

>

> Since replication will need less band width we are checking it.

>

> To return to my original question:

> Quest Shareplex -

> Any success stories?

> Why use this and not replication?

> Ant performance tests between Shareplex and Oracle replication?

>

> Yechiel Adar

> Mehish

> - Original Message -

> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>

> Sent: Monday, June 10, 2002 4:33 AM

> (online redo logs)

>

>

> > and if you need the remote site to support users, you could use the

> > logical standby feature of 9iR2, which generates SQL statements to be

> > applied and allows the database to be open and active.

> >

> > --- Tim Gorman <[EMAIL PROTECTED]> wrote:

> > > why wouldn't you consider simply using the standby database feature?

> > >

> > > do you need the remote site to support users also?

> > >

> > > - Original Message -

> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>

> > > Sent: Sunday, June 09, 2002 11:43 AM

> > > (online redo logs)

> > >

> > >

> > > > Hello All

> > > >

> > > > I just had a meeting today about replication.

> > > > The situations is: One master db that is currently replicated

> > > > (master to master synchronous replication) to a second DB.

> > > > Both machines are NT and the is a direct cable connection

> > > > between the network cards on both machines.

> > > >

> > > > However, this solves the problem of machine failure but does not

> > > cover

> > > > the full disaster recovery as both machines are in the same room.

> > > > In case of fire both machines will be destroyed.

> > > >

> > > > We are thinking about adding asynchronous replication to replicate

> > > the

> > > > changes

> > > > across wan to a remote site. The problem is that this will load the

> > > > production system and the network link (wan is expensive), as the

> > > system

> > > > generates during peek time 10MB of archive logs ever

RE: Diagnose Slow System

[Aponte, Tony]

Title: RE: Diagnose Slow System






I apologize if I missed the final post.  Did you discovered the cause of the problem?


Tony Aponte


-Original Message-

From: Baker, Barbara [mailto:[EMAIL PROTECTED]]

Sent: Thursday, May 23, 2002 6:13 PM

To: Multiple recipients of list ORACLE-L

Subject: Diagnose Slow System



List:

We've been fighting problems for several days. I've sort of overwhelmed

myself with data, but I don't know what any of it means.


Solaris 2.6, Oracle 8.0.5, MTS   

Users complain of extreme slowness.  No errors in alert, no trace files

generated.

Database is bounced every day.  I capture wait statistics each day before

the database goes down.  The statistics from v$system_event for enqueue

waits has gone up considerably since the problems started last Wednesday.

But when I look at v$lock (I'm using Steve Adams' enqueue_locks.sql

scripts), nothing pops up.


Any ideas where I should start looking?   I would appreciate any help.

(I really believe this is a connectivity (networking) issue, but don't know

how to confirm this)

Thanks!

Barb


(accumulted since last night at 11:00 pm)



EVENT   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED

AVERAGE_WAIT

--- --- -- ---



latch free   814316   4064  106360

.130612686

enqueue 147 26   12033

81.8571429

free buffer waits 4  0  23

5.75

buffer busy waits  2959  0 567

.19161879

log file parallel write   68177  0   78788

1.155639

log file sync 66683  1   77517

1.16247019

db file sequential read 1385334  0  144617

.104391432

db file scattered read  1113301  0  142545

.12803815



(The info captured below is unusual.  running this repeatedly normally shows

nothing

except smon TS resource wait)



RESOURCE  NSID  SID HOLDING WANTING    SECONDS

 -  --- --- --

RT-1-0   4 LGWR   X  0

TM-1949-0   46   46  SX  0

TM-1999-0  423  423  SX  4

    46   46  SX  0

TM-2014-0   46   46  SX  0

TM-2106-0   46   46  SX  0

TM-2218-0   46   46  SX  0

TM-2270-0  423  423  SX  4

TM-2275-0  423  423  SX  4

    46   46  SX  0

TS-1-8388610 6 SMON  SX  48069

TX-1114154-43605    46   46   X  0

TX-852064-43554    423  423   X  4



(Below is also unusual.  Running this repeatedly normally returns no rows)



Sess    Ser Wait   Wait Time   W'd So

  ID No Event  State    W'd (ms) Far (ms) P1 P2

P3

 -- --    -- --



  16 19 latch free WAITING 0    0 2147519876 59

0

  92 38 latch free WAITED S   -1    0 2147519876 59

0

   HORT TIM

   E


 565 31 latch free WAITING 0    0 2147519876 59

0

 636  11604 latch free WAITED S   -1    0 2147519876 59

0

   HORT TIM

   E



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Baker, Barbara

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: RMAN NETBACKUP recovering from say 6 MONTHS ago

[Aponte, Tony]

Title: RE: RMAN NETBACKUP recovering from say 6 MONTHS ago



The 
logs are usually located in /opt/openv/netbackup/logs.  The instructions on 
how to activate logging for each of the Netbackup components is in 
/opt/openv/netbackup/logs/README.debug.  The process you are looking to 
debug is bprestore.  The instructions for activating debug logging for 
bprestore are as simple as having the SA create the directory 
/usr/openv/netbackup/logs/bprestore, although I would activate all of them in 
order to get a complete picture.  After the directories are created rerun 
your restore and go mining in the logs.  I would start on the client side 
logs (just to make sure it's not a connectivity problem) and then proceed to 
troubleshoot the server side of things.
 
Good 
luck.
Tony 
Aponte

  -Original Message-From: Sam Roberts 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, June 11, 2002 8:34 
  AMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  RMAN NETBACKUP recovering from say 6 MONTHS ago
  Thanks Tony
  This is how I imagined it would work, yet the SYS ADMins are 
  saying that there is no request for tape in the logs. Im in a different 
  location so I guess I will have to travel to the other side of town and look 
  myself. You dont know the directory of the logs do u by chance. I dont 
  actually have access to the netbackup server so I am not familiar with its 
  file locations
  Thanks again
  Sam

RE: RMAN NETBACKUP recovering from say 6 MONTHS ago

[Aponte, Tony]

Title: RE: RMAN NETBACKUP recovering from say 6 MONTHS ago






Netbackup has a repository that keeps track of what files are on which tapes, dates, sizes, volumes, serial number, etc.  Depending on how you are restoring the files, Netbackup gets a request to get a file name (as it is know on tape) and checks it's catalog for the entry.  The bprestore process (or whatever it's called on your platform) sends a request in a proprietary format to the media manager host.  You (or your SA) can look at the Netbackup logs or use bpmon to view the details for the request, including the tape label.

The hang you are seeing is actually controlled by a timeout setting.  Once the request is made for loading of a tape, the restore process is at the mercy of whatever mounter is used on the media manager host (robot, tape jockey, etc.)  The timer expires, signals the bprestore and lets it exit gracefully returning an exit code in the process.  Use this exit code to determine what the caused timeout; it's in the Veritas Netbackup manual.  I bet the error code is for "media not found and timer expired" or something like that since the request isn't failing right away (which would mean that the tape was explicitly expired and the catalog entries updated to reflect the changes.)  

HTH

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Monday, June 10, 2002 4:18 AM

To: Multiple recipients of list ORACLE-L

Subject: RMAN NETBACKUP recovering from say 6 MONTHS ago



I want to alternate host my database to 6 months ago: The media manager

only keeps 2 months of data online and when i recover until etc.. the job

hangs and then finally gives up, obviously as the tape is not inside:

My question is how does Netbackup know which tape to load, or how do I know

which tape to put in the media manager or which range of tapes:

Anybody an help with this


Thanks


Sam




-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Wrong Results Bug in Oracle 8.1.7.1

[Aponte, Tony]

Title: RE: Wrong Results Bug in Oracle 8.1.7.1






We had a similar issue, although it always resulted in an ORA-600.  It was with descending indexes as defined in the Peopletools repository.  We had to set _IGNORE_DESC_IN_INDEX=TRUE (and _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT=1 to comply with the Certification Requirements) and recreate the development databases.  For Production, PS Support gave the OK to recreate the indexes without having to redo the upgrade.

HTH

Tony Aponte


-Original Message-

From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]]

Sent: Monday, June 10, 2002 1:48 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: Wrong Results Bug in Oracle 8.1.7.1



Accounts payable.  But this is not a Peoplesoft problem, but an Oracle one.  I tried the same query against another Peoplesoft instance, and it ran fine.  So there's something more than the view involved here.  It's in Oracle Support's court now.

I must be living right.  It's failing in development and working in production.


Ian


-Original Message-

Sent: Sunday, June 09, 2002 7:13 PM

To: Multiple recipients of list ORACLE-L



what modules, if you dont mind me asking, i'm at a site where we're 

going to implement HR, Financials And EPM soon.


thanks, joe



MacGregor, Ian A. wrote:


>Yep sure is.  

>

>Ian

>

>

>-Original Message-

>Sent: Sunday, June 09, 2002 5:43 AM

>To: Multiple recipients of list ORACLE-L

>

>

>Ian, is that peopleslop?

>

>joe

>

>

>MacGregor, Ian A. wrote:

>

>>SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE ROWNUM < 10;

>>

>>VOUCHER_

>>

>>3394

>>3395

>>3396

>>3397

>>3398

>>3399

>>3400

>>3401

>>3402

>>

>>

>>set feedback on

>>

>> 1  SELECT VOUCHER_ID FROM PS_VCHR_MM_VW

>> 2* WHERE VOUCHER_ID  = '3394'

>>SQL> /

>>VOUCHER_

>>

>>

>>

>>1 row selected.

>>--

>>Zounds !!!  Select dump(voucher_id) shows that Oracle is returning a null here.

>>

>>Here a function is used to force the query to do full tablescans

>>

>>1  SELECT VOUCHER_ID FROM PS_VCHR_MM_VW

>> 2* WHERE CONCAT(VOUCHER_ID,'A') = CONCAT('3394', 'A')

>>SQL> /

>>

>>VOUCHER_

>>

>>3394

>>-

>>I get the expected results  if I force full table scans.

>>

>>I looked at the explain plan for the failing query and rebuilt the ps_voucher index.  This 

>>did not change its erroneous results.  I rebuilt the view itself again to no avail.  A check on Metalink revealed bug 1852163.  Although this bug's conditions were different  from the one above, their were some similarities.  One workaround for bug 1852163

>>

>>alter session set "_complex_view_merging" = true;

>>

>>I tried this and the  original query still gave improper results.

>>---

>>All queries against the component tables of the view work fine.

>>--

>>The view text is

>>

>>CREATE VIEW SYSADM.PS_VCHR_MM_VW

>>AS

>>SELECT DISTINCT  A.BUSINESS_UNIT,  A.VOUCHER_ID,  A.INVOICE_ID,

>>A.INVOICE_DT,  A.PROCESS_INSTANCE,  A.ENTRY_STATUS,  A.POST_STATUS_AP,

>>A.MATCH_ACTION,  C.PPV_POST_FLG,  C.ERV_POST_FLG,  A.ORIGIN  FROM

>>SYSADM.PS_VOUCHER A,  SYSADM.PS_PO_LINE_MATCHED C  WHERE  A.BUSINESS_UNIT =

>>C.BUSINESS_UNIT_AP AND  A.VOUCHER_ID = C.VOUCHER_ID AND

>>A.MATCH_ACTION IN ('Y', 'E')

>>-

>>If I run  the select statement outside of the view and tack on the 'voucher_id = ' clause 

>>SELECT DISTINCT  A.BUSINESS_UNIT,  A.VOUCHER_ID,  A.INVOICE_ID,

>>A.INVOICE_DT,  A.PROCESS_INSTANCE,  A.ENTRY_STATUS,  A.POST_STATUS_AP,

>>A.MATCH_ACTION,  C.PPV_POST_FLG,  C.ERV_POST_FLG,  A.ORIGIN  FROM

>>SYSADM.PS_VOUCHER A,  SYSADM.PS_PO_LINE_MATCHED C  WHERE  A.BUSINESS_UNIT =

>>C.BUSINESS_UNIT_AP AND  A.VOUCHER_ID = C.VOUCHER_ID AND

>>A.MATCH_ACTION IN ('Y', 'E')

>>and a.voucher_id = '3394'

>>/

>>

>>I get the expected results.  The query plan matches the one for the failing statement.

>>--

>>If I  select more than voucher_id from the view with the 'voucher_id  = ' predicate

>>the other fields are projected correctly, but returns voucher_id as null.

>>===

>>

>>Ian MacGregor

>>Stanford Linear Accelerator Center

>>[EMAIL PROTECTED]

>>

>>

>>

>

>



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Joe Testa

  INET: [EMAIL PROTECTED]


Fat City Netwo

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking



I once 
had an instructor that said "a test is worth a thousand pages of 
documentation."  If you have access to the book, give the sample a 
try.  
 
Regards,
 
Tony 
Aponte

  -Original Message-From: Khedr, Waleed 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, June 10, 2002 11:29 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Complex Integrity Checking
  Again I do not 
  see anything here saying that the child session (Autonomous TX) will see the 
  changes made by the parent TX.
   
  If you implied 
  this in your message, then we are in agreement.
   
  regards,
   
  Waleed
  
-Original Message-From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]]Sent: Monday, June 10, 2002 10:16 
AMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: Complex Integrity 
Checking
Sorry for the delayed reply (I type with 2 fingers.)  
The section starts on page 685. 
<<< rip >>>>> Database Changes Now, this is were things get 
interesting - database changes.  Here, things can get a little 
murky.  Database changes made, but not yet committed by a parent 
transaction are not visible to the autonomous transactions.  Changes 
made, and already committed by the parent transaction, are always visible to 
the child transaction.  Changes made by the autonomous transaction may 
or may not be visible to the parent depending on its isolation 
level.
I said before though, that this is were things get 
murky.  I was pretty clear above in saying that changes made by the 
parent transaction are not visible to the child but that's not 100 percent 
of the story.  A cursor opened by the child autonomous transaction will 
not see uncommitted changes, but a cursor opened by the parent and fetched 
from the child will.  The following case shows how this works.  We 
will recreate our EMP  ..
<<< end rip >>>>> 
The rest of the section demonstrates the voodoo magic.  
I confess that we didn't go this route and used the global array in a PL/SQL 
package, and the author discourages the use of autonomous transactions to 
get around mutating table errors.  But it might be just right for 
someone's need.
Regards, 
Tony Aponte 
-Original Message- From: 
Khedr, Waleed [mailto:    Sent: 
Thursday, June 06, 2002 12:08 PM To: Multiple 
recipients of list ORACLE-L Subject: RE: Complex 
Integrity Checking 
I could not find this and do not know how it could happen! 

If you can post here what you read, it will be 
appreciated. 
Waleed 
-Original Message- To: 
[EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 
Sent: 6/6/02 9:26 AM 
Waleed,   The chapter on Autonomous transactions demonstrates how to give 
the child transaction the ability to see uncommitted 
changes made by the parent transaction. 
  Regards,   Tony Aponte 
-Original Message- Sent: 
Wednesday, June 05, 2002 9:03 PM To: Multiple 
recipients of list ORACLE-L 
The problem with this solution is the Autonomous 
Transactions  will not be able to see any 
changes done within the current transaction only the committed one. So no way to enforce business logic during the context 
of the transaction.   This is why I asked before how 
frequently commit happens.   Regards,   Waleed 
-Original Message- Sent: 
Wednesday, June 05, 2002 6:33 PM To: Multiple 
recipients of list ORACLE-L 
With the introduction of Autonomous Transactions this is no 
longer entirely true.  If you call an 
autonomous transaction procedure, it is executed in 
a separate transaction context.  This gives you the ability 
to probe the mutating table without inducing the 
error.  A good explanation can be found in Tom 
Kyte's Export One-on-one Oracle book in the chapter 
on Autonomous Transactions. 
HTH Tony Aponte 
-Original Message- <mailto:[EMAIL PROTECTED]> 
] Sent: Wednesday, June 05, 2002 9:24 AM 
To: Multiple recipients of list ORACLE-L 

no matter what you do, if you access table A inside a 
trigger on table A, oracle 
will give you mutating table error. What you could (and I really 
mean you have to consider your 
business logic here) is go ahead and insert the 
rows with a temp flag. As soon as you commit, fire 
up a procedure that will do 
the scan on the table and delete appropriate rows which have the temp 

status. 
BTW how big is this table? What is the frequency of inserts 
and updates? 
Raj __ 
Rajendra 
Jamadagni  
MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot 
com Any opinion expressed here is personal and 

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking






Sorry for the delayed reply (I type with 2 fingers.)  The section starts on page 685.



<<< rip >

Database Changes

Now, this is were things get interesting - database changes.  Here, things can get a little murky.  Database changes made, but not yet committed by a parent transaction are not visible to the autonomous transactions.  Changes made, and already committed by the parent transaction, are always visible to the child transaction.  Changes made by the autonomous transaction may or may not be visible to the parent depending on its isolation level.

I said before though, that this is were things get murky.  I was pretty clear above in saying that changes made by the parent transaction are not visible to the child but that's not 100 percent of the story.  A cursor opened by the child autonomous transaction will not see uncommitted changes, but a cursor opened by the parent and fetched from the child will.  The following case shows how this works.  We will recreate our EMP  ..

<<< end rip >


The rest of the section demonstrates the voodoo magic.  I confess that we didn't go this route and used the global array in a PL/SQL package, and the author discourages the use of autonomous transactions to get around mutating table errors.  But it might be just right for someone's need.

Regards,


Tony Aponte




-Original Message-

From: Khedr, Waleed [mailto:    

Sent: Thursday, June 06, 2002 12:08 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: Complex Integrity Checking



I could not find this and do not know how it could happen! 


If you can post here what you read, it will be appreciated.


Waleed


-Original Message-

To: [EMAIL PROTECTED]

Cc: [EMAIL PROTECTED]

Sent: 6/6/02 9:26 AM


Waleed,

 

The chapter on Autonomous transactions demonstrates how to give the

child transaction the ability to see uncommitted changes made by the

parent transaction.

 

Regards,

 

Tony Aponte


-Original Message-

Sent: Wednesday, June 05, 2002 9:03 PM

To: Multiple recipients of list ORACLE-L



The problem with this solution is the Autonomous Transactions  will not

be able to see any changes done within the current transaction only the

committed one. So no way to enforce business logic during the context of

the transaction. 

 

This is why I asked before how frequently commit happens.

 

Regards,

 

Waleed


-Original Message-

Sent: Wednesday, June 05, 2002 6:33 PM

To: Multiple recipients of list ORACLE-L




With the introduction of Autonomous Transactions this is no longer

entirely true.  If you call an autonomous transaction procedure, it is

executed in a separate transaction context.  This gives you the ability

to probe the mutating table without inducing the error.  A good

explanation can be found in Tom Kyte's Export One-on-one Oracle book in

the chapter on Autonomous Transactions.


HTH 

Tony Aponte 


-Original Message- 

 ] 

Sent: Wednesday, June 05, 2002 9:24 AM 

To: Multiple recipients of list ORACLE-L 



no matter what you do, if you access table A inside a trigger on table

A, 

oracle will give you mutating table error. What you could (and I really

mean 

you have to consider your business logic here) is go ahead and insert

the 

rows with a temp flag. As soon as you commit, fire up a procedure that

will 

do the scan on the table and delete appropriate rows which have the temp


status. 



BTW how big is this table? What is the frequency of inserts and updates?



Raj 

__ 

Rajendra Jamadagni  MIS, ESPN Inc. 

Rajendra dot Jamadagni at ESPN dot com 

Any opinion expressed here is personal and doesn't reflect that of ESPN

Inc. 


QOTD: Any clod can have facts, but having an opinion is an art! 


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Khedr, Waleed

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: I/O contention with external process reading the oracle logs (online redo logs)

[Aponte, Tony]

Title: RE: I/O contention with external process reading the oracle logs (online redo logs)






I think they are alluding to UNIX file system contention.  If the redo logs are in regular file systems (not raw, Veritas Quick I/O, etc.) then UNIX (at least in my Solaris environment) needs to lock the file for each of the Shareplex capture processes, in addition to LGWR. 

There will also be some contention inside the source database that is not mentioned in their response.  Shareplex needs to query the source table to get the primary key value for the row that changed.  It does it using the rowid that was scraped off the redo log.  It then uses the primary key value from the source table to build the insert statement for the target.  In our installation this process amounts to 5% of the CPU used by this session statistic.  Although the blocks needed are still in the buffer cache, there is some serialization that has to occur to fulfill the logical I/O.

BTW, in 9i the logical standby implementation includes the primary key value in the redo stream after extended logging is activated.  This relieves the source from the backwards-looking access for the primary key as done by Shareplex.  I doubt that the performance gain of extended logging is totally free though.

HTH.

Tony


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Thursday, June 06, 2002 10:32 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: I/O contention with external process reading the oracle

logs (online redo logs)



NB_ RESENDING in plain text - sorry, Outlook keeps seinding in html no matter what default i set!

Hi lists,

 

    I am using Quest Shareplex product for Oracle to Oracle one way replication.  I have two systems (source and target) and two environments  (dev, demo).  On system one, the environments are setup as schemas within one oracle instance (therefore each schema will be a SOURCE in the replication).  My other system has each environment set up a separate Orace Instances (therefore each instance will become a TARGET in the replication).

 

    I am trying to configure 2 separate replication streams (ie so that each replication process is SEPARATE from the other - one for DEV and one for DEMO).  I will accomplish this by setting up Shareplex to use mulitple processes.

 

    HOWEVER, Quest technical support has told me that this will cause contention.  However, I dont see why is would from an os/oracle point of view.  Basically Shareplex has a process which reads the online redo logs. tech support is suggesting that is there a two processes trying to access the same block in the logs that contention can occur.  This does not make sense to me.  Below is the blurb from techincal support when I questioned their initial repsonse:

 

*

The reason you might run into a contention is because multiple captue processes may be reading the same data block in the redo log.  Since there is only one process that can access a single block, the other process may have to wait.

Contention is a possibilty, and you will need to run some bench marks to find out how much, if any, contention you will have.

*

 

I would find it HARD to believe that only ONE process can read a block at a time.  If this were true, then OLTP system would FAIL miserably!

 

Anyone have any ideas/comments regarding the OS and Oracle interaction  I mean are not the logs at this pointa UNIX file?  and can't multiple processes read a single unix file without bringing the whole system to its knees?

Also,  I am NOT knocking the techincal support, but I believe that the opinion was formulated on an incorrect assumption on the operating system and Oracle.

Thoughts/comments?

 

Thanks in advance. 

 

Hannah

 

 

 

 

 

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking



Waleed,
 
The chapter on Autonomous transactions demonstrates how 
to give the child transaction the ability to see uncommitted changes made by the 
parent transaction.
 
Regards,
 
Tony Aponte

  
-Original Message-From: Khedr, Waleed 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 9:03 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Complex Integrity Checking
The problem 
with this solution is the Autonomous Transactions  
will not be able to see any changes done within the current transaction only 
the committed one. So no way to enforce business logic during the context of 
the transaction. 
 
This is why I asked before 
how frequently commit happens.
 
Regards,
 
Waleed

  -Original Message-From: Aponte, Tony 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 6:33 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Complex Integrity Checking
  With the introduction of Autonomous Transactions this is 
  no longer entirely true.  If you call an autonomous transaction 
  procedure, it is executed in a separate transaction context.  This 
  gives you the ability to probe the mutating table without inducing the 
  error.  A good explanation can be found in Tom Kyte's Export 
  One-on-one Oracle book in the chapter on Autonomous 
  Transactions.
  HTH Tony Aponte 
  -Original Message- From: 
  Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] 
  Sent: Wednesday, June 05, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking 
  no matter what you do, if you access table A inside a 
  trigger on table A, oracle will give you mutating 
  table error. What you could (and I really mean you 
  have to consider your business logic here) is go ahead and insert 
  the rows with a temp flag. As soon as you commit, 
  fire up a procedure that will do the scan on the 
  table and delete appropriate rows which have the temp status. 
  BTW how big is this table? What is the frequency of 
  inserts and updates? 
  Raj __ 
  Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN 
  Inc. 
  QOTD: Any clod can have facts, but having an opinion is an 
  art! 

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking






With the introduction of Autonomous Transactions this is no longer entirely true.  If you call an autonomous transaction procedure, it is executed in a separate transaction context.  This gives you the ability to probe the mutating table without inducing the error.  A good explanation can be found in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous Transactions.

HTH

Tony Aponte


-Original Message-

From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, June 05, 2002 9:24 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: Complex Integrity Checking



no matter what you do, if you access table A inside a trigger on table A,

oracle will give you mutating table error. What you could (and I really mean

you have to consider your business logic here) is go ahead and insert the

rows with a temp flag. As soon as you commit, fire up a procedure that will

do the scan on the table and delete appropriate rows which have the temp

status.



BTW how big is this table? What is the frequency of inserts and updates?


Raj

__

Rajendra Jamadagni      MIS, ESPN Inc.

Rajendra dot Jamadagni at ESPN dot com

Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art!

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking






In my suggestion you example for check_for_overlapped_intervals would have the autonomous transaction pragma, thereby avoiding the mutating table error.

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, June 05, 2002 10:39 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: Complex Integrity Checking



-Original Message-

Sent: Wednesday, June 05, 2002 4:53 PM

To: Multiple recipients of list ORACLE-L



**

This email has been tested for viruses by F-Secure Antivirus

administered by IT Network Department.

**


 two questions: How many records do you insert into that table before a

commit ?


Is the whole issue simply mutating table error when running some business

logic in an insert/update trigger for the intervals table?


Regards,


Waleed


I'm sorry bu I can't answer to your questions because I don't see the point.



Here's a test table:

CREATE TABLE intervals (

    start_time NUMBER NOT NULL,

    end_time NUMBER NOT NULL

)


Here are some statemens:


INSERT INTO intervals

(START_TIME,END_TIME)

VALUES 

(3,5)

/

INSERT INTO intervals

(START_TIME,END_TIME)

VALUES 

(2,3)

/

INSERT INTO intervals

(START_TIME,END_TIME)

VALUES 

(7,8)



What I want is that the integrity rule (no overlapped intervals) be

operational even if i insert a new record or more or update one or more.

Think of it the same way an unique key works.

This is a simplified table for example purpose. In fact my application is a

resource scheduler, so I want a resource not to be assigned for more than 1

client at the same time.

Here the start_time and end_time are of number type just for testing, but of

course it'll be of date type.


I'm starting to think that what I want, can be done in a simple, clean

manner but using complex workarounds, isn't it?

Thanks!


iulian


-Original Message-

To: Multiple recipients of list ORACLE-L

Sent: 6/5/02 4:33 AM


First of all I want to thank you all for your answers.

Let's take'em one by one:






Attn: Mercadante, Thomas F [[EMAIL PROTECTED]]

- I cannont use "instead of" trigger because of this error:


ORA-25002: cannot create INSTEAD OF triggers on tables

Cause: Only BEFORE or AFTER triggers can be created on a table.

Action: Change the trigger type to BEFORE or AFTER.


I have an Oracle database version 9.0.1.1.1







Attn: Stephane Faroult [[EMAIL PROTECTED]]

- for insert your approach works (although I have to change a bit the

select

in exists condirion) but what about the update statements. 

- moreover i think this will not keep my integrity rule consistent, if

someone try to simply use typical insert&update statements.







Attn: Khedr, Waleed [[EMAIL PROTECTED]]

- Can you give me an example for your unique function based index, I

mean

how can you assign an unique number for various intervals. 

- anyway if this can be done I assume that would be a very nice, clean

solution







Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen

[[EMAIL PROTECTED]]

- this really doesn't suit my needs, create 2 tables instead of one



========



Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony

[[EMAIL PROTECTED]]

- I did make a function: 


FUNCTION check_for_overlapped_intervals (

    p_start_time IN NUMBER, 

    p_end_time IN NUMBER)

RETURN NUMBER

IS

    n NUMBER;

BEGIN

    -- when this select have records to count 

    -- means that the new interval overlap an existing one

    -- and still is not corectly implement for update stament

    -- where it should not consider the current record

    SELECT COUNT(*) INTO n

    FROM intervals

    WHERE start_time < p_end_time 

    AND end_time > p_start_time;

    RETURN(n);

END;


and use it in the trigger:


CREATE OR REPLACE TRIGGER bi_interval

BEFORE INSERT  OR UPDATE

ON intervals

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

  IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0

THEN

    raise_application_error(-20100, 'Overlapped intervals');

  END IF;

END;


but still got the same mutating table error. Am I wrong someplace.







Thanks again. I try to test all of your solution and above are my

answers.

Can you still help me.

RE: Complex Integrity Checking

[Aponte, Tony]

Title: RE: Complex Integrity Checking






I would look into combining a before-insert row-level trigger with an autonomous transaction procedure.  The procedure would execute the validating query using parameters passed by the trigger.  If your new row values would cause an overlap then return a user defined exception to the trigger.  The trigger should trap this exception and cause a failure in the transaction.

I think it would work something like this:

You attempt to insert an interval that will violate your rule.

In the trigger on INTERVALS you pass the 2 :NEW values to the procedure.

The procedure queries INTERVALS and sees the table as it existed before your insert but does not cause a mutating table condition (because it's defined as an autonomous transaction procedure.)

The procedure finds that the new row will cause an overlap and returns an exception.

The trigger receives an exception and propagates it with some meaningful message.

The insert fails.


Let us know how (if) this works for you.


Tony Aponte




-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, June 04, 2002 10:38 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: Complex Integrity Checking



I said something like "the way the unique constraints work".

Ok. Here's my context.

I have a table say intervals and 2 columns start_time and end_time.

I want to check for overlapped intervals.

I know what conditions to check but I can't implement them.

Thanks!


iulian


-Original Message-

Sent: Tuesday, June 04, 2002 5:13 PM

To: Multiple recipients of list ORACLE-L



**

This email has been tested for viruses by F-Secure Antivirus

administered by IT Network Department.

**



Hi



if unique does not suit your need what exactly do you need to check?

duplicates: use primary key



Jack



 


  Iulian.ILIES@oran


  ge.ro    To:   Multiple recipients

of list ORACLE-L <[EMAIL PROTECTED]>   

  Sent by: cc:   (bcc: Jack van

Zanen/nlzanen1/External/MEY/NL)    

  [EMAIL PROTECTED] Subject:  Complex Integrity

Checking    

 


 


  04-06-2002 15:58


  Please respond to


  ORACLE-L


 


 





Hi guys. Here's my problem.

I want to check the new values (when inserting&updating a table) against

the

ones in the existing rows. Something like checking for duplicate values,

but

using a unique constraint doesn't suit my needs.

I think of a before insert&update trigger, wherein checking my condition

and

raise a error if not valid. The problem is, in case of an update statement,

I get the mutating "ORA-04091 table  is mutating".

I read a lot of doc but I didn't find any helping ideas. Can you give me

some, or maybe a new approach to this kind of problem?

Thanks in advance!


iulian





**


The information contained in this communication is confidential and

may be legally privileged. It is intended solely for the use of the

individual or entity to whom it is addressed and others authorised to

receive it. If you are not the intended recipient you are hereby

notified that any disclosure, copying, distribution or taking action in

reliance of the contents of this information is strictly prohibited and

may be unlawful. Orange Romania SA is neither liable for the proper,

complete transmission of the information contained in this communication

nor any delay in its receipt.



**



--

Please see the official ORACLE-L FAQ: http://www.orafaq.com

--

Author:

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).





==

De informatie verzonden in dit e-mailbericht is vertrouwelijk en is

uitsluitend bestemd voor de geadresseerde. Openbaarmaking,

vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan

derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &

Young, niet toegestaan. Ernst & You

RE: Sql Question

[Aponte, Tony]

Here's 
a quick-n-dirty SQL that pivots the result set into one row.  It has 
its limits (you must know the number of rows that would be returned so that you 
can adjust the grouping columns value01 through value12.  

 
SELECT 
g1 
,MAX(DECODE(line_no,01,value,NULL)) value01 
,MAX(DECODE(line_no,02,value,NULL)) value02 
,MAX(DECODE(line_no,03,value,NULL)) value03 
,MAX(DECODE(line_no,04,value,NULL)) value04 
,MAX(DECODE(line_no,05,value,NULL)) value05 
,MAX(DECODE(line_no,06,value,NULL)) value06 
,MAX(DECODE(line_no,07,value,NULL)) value07 
,MAX(DECODE(line_no,08,value,NULL)) value08 
,MAX(DECODE(line_no,09,value,NULL)) value09 
,MAX(DECODE(line_no,10,value,NULL)) value10 
,MAX(DECODE(line_no,11,value,NULL)) value11 
,MAX(DECODE(line_no,12,value,NULL)) value12 
FROM (SELECT 
g1,value,row_number() 
over(partition by g1 order 
by g1 nulls last) line_no 
FROM (SELECT 'DEPTNO' g1,deptno value from 
dept) 
) 
GROUP BY 
g1; 
 

 
I took 
it a little further and came up with the following:
 
SELECT 
g1 
,MAX(DECODE(line_no,01,value,NULL)) ||','|| 
MAX(DECODE(line_no,02,value,NULL)) ||','|| 
MAX(DECODE(line_no,03,value,NULL)) ||','|| 
MAX(DECODE(line_no,04,value,NULL)) ||','|| 
MAX(DECODE(line_no,05,value,NULL)) ||','|| 
MAX(DECODE(line_no,06,value,NULL)) ||','|| 
MAX(DECODE(line_no,07,value,NULL)) ||','|| 
MAX(DECODE(line_no,08,value,NULL)) ||','|| 
MAX(DECODE(line_no,09,value,NULL)) ||','|| 
MAX(DECODE(line_no,10,value,NULL)) ||','|| 
MAX(DECODE(line_no,11,value,NULL)) ||','|| 
MAX(DECODE(line_no,12,value,NULL)) 
FROM (SELECT 
g1,value,row_number() 
over(partition by g1 order 
by g1 nulls last) line_no 

FROM (SELECT 'DEPTNO' g1,deptno value from dept) 
) 

GROUP BY 
g1; 

 
I'll 
leave the trimming of the trailing commas to you.
 
HTH
Tony 
Aponte
 

  -Original Message-From: kranti pushkarna 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 30, 
  2002 3:48 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Sql Question
  Hi 
  List,
      
  Can someone give a SQL query to retuen all values in paricular column in comma 
  separed format.
  e.g. suppose I 
  fire "select deptno from dept" the output would be like
   
  Deptno
  10
  20
  30
  40
   
  I want the 
  output like 10,20,30,40. 
   
  I am just 
  wondering can it be done in a single query.
   
   
  TIA
  Kranti
   

RE:

[Aponte, Tony]

Title: RE: 






There is a solution in Tom Kyte's Expert One on One book.  It implements as SQL Loader in PL/SQL with UTL_FILE.


Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Monday, April 22, 2002 11:38 AM

To: Multiple recipients of list ORACLE-L

Subject: 




Hi all!


I need a solution about calling sql*loader from pl/sql. I have a version

now with external dlls, but actually I don't know the platform so it not

seems a good choice. I would like something native oracle solution with

oracle's packages or something like that.




-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Script for identifying objects having freelists contention

[Aponte, Tony]

Steve Adams' site is a good 
start.  http://www.ixora.com.au/
 
Tony Aponte

  -Original Message-From: Anand Prakash 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 
  2002 5:29 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Script for identifying objects having freelists 
  contention
  
  Does anyone have the scripts (or URL) for identifying objects having 
  freelists contention?
   
  Thanks.
  Anand Prakash

RE: How can we make any column of a table as case insensitive

[Aponte, Tony]

Can you post what requiremment is driving this?  I.E. Is it to be able to query for a 
mixed-case input value?  Maybe to sort mixed case as if it were all upper or lower?

Tony Aponte

-Original Message-
Sent: Tuesday, April 16, 2002 12:34 PM
To: Multiple recipients of list ORACLE-L


Greetings All,

We have a requirement of making one or multiple or all columns of a table case 
insensitive. We can not modify the query as it comes from a 3rd party application. One 
option I can think of is that introduce a trigger on the table and before inserting or 
modifying the column's data force it to be all uppercase or all lowercase.

Is there any other way of doing it? Can we do it using a constraint?


This requirement is like the feature of ic(ignore case) in vi editor.

Thanks,
Ashoke
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mandal, Ashoke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Aponte, Tony
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HIGH CPU WITH MULTIPLE CONCURRENT USERS (long)

[Aponte, Tony]

I 
think you are running into the stampeding herd phenomenon.  I'm 
suspicous of the low value for the spin count.  It seems timid.  
Could you truss one of the shadow processes with the timing option and post the 
output?  Just enough to identify the repeating pattern.  Also, please 
run the truss in dedicated server mode to get a complete picture.  

 
One 
you have the truss of the problem, try comenting out the entry for 
_spin_count in the init.ora and reruning your tests.  I'm not reading email 
during the day while I'm at IOUG but hopefully I'll see your post after I return 
to my hotel.
 
One 
more thing, thanks for posting your problem with such clarity and supporting 
detail.
 
Tony 
Aponte

  -Original Message-From: Richard Eastham 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 2002 1:58 
  AMTo: Multiple recipients of list ORACLE-LSubject: HIGH 
  CPU WITH MULTIPLE CONCURRENT USERS (long)
  A co-worker is having a fairly serious issue with performance tuning of a 
  system.  The system is in the stress testing phase prior to rolling out 
  into production.  I have not included all the information as so far they 
  have exceeded three TARs and are working on the fourth one right now.  
  Oracle has become fairly heavily involved and is sending in the Advanced 
  services team is now involved.  He has identified that the main issue is 
  a wait after the parsing of the SQL and during the fetch portion of the 
  execution.  The short version is running the same SQL statement ( 
  basically  nothing more than a simple query against a single table) the 
  machine starts bogging down with a simulated 20+ users sessions and the 
  system starts to choke at 100+ user sessions.  We are talking a 
  fairly decent midrange system.  The query is a select with 5 columns 
  extracted and a where clause that uses the in clause to select the same rows 
  for each query.  The question is has anyone seen this type of behavior 
  before?  If you have seen this before what was the root cause? Did you 
  find a solution?
   
  Oracle acknowledges that the scenario is reproducible within their test 
  environment, but the core team is stating that it is working as 
  designed.  Oracle is working with us, but why not check with other 
  sources.
   
   
  
  
  
  A summary of where we are at: (4th TAR)
   
  
We 
tried to simulate the same performance degradation on an entirely different 
environment. We have been able to do the same. 
We 
had requested Oracle to simulate the test case in their environment. They 
have been able simulate the performance degradation. Their analysis is also 
provided in this attachment. 
To 
summarize, they have simulated where 1 user query runs in 2 seconds and 
10-user query takes 7 seconds on a 4-processor 
server. 
The 
development team of Oracle has answered to this degradation as normal and as 
designed. However, the degradation is very high and is in contrast with 
their alleged benchmark results (67000 transactions per minute on a 8 
processor hardware). For us the degradation is so high that we are not able 
to run 150 transactions per minute on a 4-processor server. The simulation 
within oracle also supports this degradation
   
   
  
  
  
  
  15-APR-02 22:09:08 GMTPasting information into the tar on 
  bug:2321553  since 
  currently unavailable on MetaLink:"PROBLEM:Customer 
  has a production database that was installed on a Sun Solaris 2.8. The 
  Solaris was a fresh install. The database was a fresh install. Customer is 
  having the following problems:.1. Performance problems with multiple 
  users - more users more performance problems2. The query runs fine, 
  explain plan runs fine, query just takes moer time with more users - same 
  query3. Customer tested multi-user connection from the box via sqlplus ( 
  no network ) - same issue4. Customer removed the application from the 
  env and ran multi-user test - same problem.5. Customer loaded data in 
  another 8.1.7 database on Win 2000 - same performance problem with more 
  users...DIAGNOSTIC ANALYSIS:Analysis 
  by NSAWYER.US ---I have looked over the RDA and 
  have found nothing that would cause the CPU to run high. - the more I 
  review the issue the more it seems to be less a tuning issue and more a 
  memory leak problem. But cannot find any report of it in the trace. 
  .Looking for any bugs that could be related to this type of behavior. 
  - I have found none..Analysis 
  Reviewed the statpack and noticed that a query 
  parses and retrieves for a split second, but the data is not return for 10 to 
  20 seconds. What is going during that extra time. This might be the place to 
  concentrate 
  on..WORKAROUND:---None.INFORMATION: 
  SERVER: E4500, 4 CPU 400MHZ, 4GB RAM (Oracle SGA 1.6GB)..PROBLEM 
  STATEMENT: Per customer,There is a

RE: How to test BCV and backups

[Aponte, Tony]

Title: RE: How to test BCV and backups






Are you going to use RMAN or only OS utilities?


Tony Aponte


-Original Message-

From: Seppo Kaasalainen [mailto:[EMAIL PROTECTED]]

Sent: Monday, March 25, 2002 12:40 PM

To: Multiple recipients of list ORACLE-L

Subject: How to test BCV and backups



Hello,


we are implementing EMC Time Finder with our Sun boxes.


Now I wonder how I can test backup and recovery scenarios with BCV. Do you 

have any ideas or good documents concerning that.


Thank you in advance,


Sepi




_

Join the world’s largest e-mail service with MSN Hotmail. 

http://www.hotmail.com


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Seppo Kaasalainen

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: EMC's DBTuner

[Aponte, Tony]

Title: RE: EMC's DBTuner






We've been using Precise/SQL since its early days.  We also are using DBTuner for our servers that have Symmetrix devices attached.  As far as we know it attaches to the SGA for read-only but you can probably verify that with pmap. But we've never had any problems with it interfering with the databases in any way.  

I hope you are lucky enough to get it.  Sampling from the SGA lets us throttle the interval to very high numbers that we couldn't achieve with any of our other tools.  We start off at 5 samples per second and go higher when running traces.  Imagine banging away at the v$/x$ tables 40 times per second and not bringing something to its knees.  And speaking of traces, we use the features to selectively trace a session/program/user/osuser/module,etc. without having to use logon triggers or continuos loops selecting from v$session/v$process.  Then there's that "what changed " tracking features.  We've identified dropped indexes, dropped statistics, recreated tables and other changes that "nobody" remembers making but "nobody" was in the office on /mm/dd/yy and happened to drop and index for xyz table.  You get the picture.  The tool gave us an immediate return the first time we used it.  We caught a problem that we weren't able to identify via the mere mortal sampling methods.  By cranking the sampling interval to 5/second we observed that FirstWatch had been configured to do a check on the database by connecting as internal several times per second and viola, our problem with a high number of connections per hour was solved.

Ditto for the Symmetrix option.  We had a recurring problem with a 7.3.4 hot backup.  The backup progressed fine until it got to a certain area of one data file at which time the database hung.  Yet we could not see anywhere where this data file coexisted with any other data file for any of our databases.  This one stumped many fire-breathers that looked into it.  The very first time we looked at the Symmetrix devices we noticed that 2 slices where very active and one of our rollback segment data files was stripped on them.  We created another rbs tablespace on other devices and the problem was solved.  A 5 minute return and most of it was spent appreciating each other's disbelief.

And if that's not enough their tech support folks are superior to any that we get from all of our other vendors.  Puts OSS to shame.

And to any skeptical list members, no I do not receive any financial benefit from Precise's or EMC's sales whatsoever.


HTH and really hope you get to enjoy (yes, enjoy) providing a superior DBA service to your company with less tedious efforts.

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, March 13, 2002 5:31 PM

To: Multiple recipients of list ORACLE-L

Subject: EMC's DBTuner



Now I know from previous posts that a number of folks are using EMC symmetric

arrays, so I should get a couple of replies.  Are any of you using

their/Precise's DBTuner product as well?  We had a demo today, but I'm still

very leery of third party products that attach to Oracle's SGA.  My point being

that Oracle goes to quite a bit of effort through the latch mechanism to keep

their processes out of each other's way.  If your third party program, although

only trying to read data, gets in the way all hell can & will break loose.  Also

if you can read something, you sure as heck can write there as well, even if not

by intent.


Any experiences welcome.


Dick Goulet

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Sun Cluster and VCS failover

[Aponte, Tony]

We 
have several 2-way VCS clusters with 16 and 10 CPU's per server.  We found 
that the failover time was proportionate with the number of file systems that 
need to be mounted by the take-over node.  We consolidated the file systems 
containing the datafiles down to 1 and each database takes 2-3 minutes 'till 
service is fully restored.
 
HTH
Tony 
Aponte

  -Original Message-From: Nick Wagner 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, March 04, 2002 4:24 
  PMTo: Multiple recipients of list ORACLE-LSubject: Sun 
  Cluster and VCS failover 
  Situation:  
  Sun Cluster, or VERITAS Cluster. Oracle 8, 8i, or 9i
  2 node Sun E6500s 
  w/ 8 CPUs
   
  If the primary 
  node fails, how long does it take before a user is able to connect to the 
  secondary node, and continue their activity?   I'm sure reality, and 
  marketing times are different... I'm really interested in reality times, but 
  at this point either would be nice.  
   
  Thanks!! 
  
   
  Nick 
   
   

RE: How to make deletes faster.

[Aponte, Tony]

Mmm.  Max sequence number.  
I'll go on the assumption that you are selecting the max for a column populated 
by a sequence. Here's one option.1) CREATE TABLE 
 AS SELECT ROWID myrowid FROM  WHERE 
 < 2) 
DELETE FROM  WHERE ROWID IN (SELECT myrowid FROM 
)3) DROP TABLE Another 
option:
1) establish a maintenance window 
with users2) CREATE TABLE  3) 
INSERT INTO  SELECT * FROM  WHERE 
 >= 4) 
DROP TABLE 5) RENAME TABLE  to 
6) recreate indexes7) recreate constraints8) 
recreate triggers9) recompile invalid objects10) recreate the 
grantsHere's a slicker option that I had posted 
previously:        
"Aponte, 
Tony" 
<[EMAIL PROTECTED]   
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
et>  
cc:   
Sent by: 
Subject: RE: providing 24*7 database 
---  
[EMAIL PROTECTED] 
om   10/22/01 
09:05 
AM 
Please 
respond 
to 
ORACLE-L  We 
use a modified version of your duplicate schema idea.  But we don't 
havethe objects in different schemas.  We use partitioned objects so 
that wecan exchange the partitions with the production tables at a scheduled 
time.The voodoo is that we use a single range partition of MAXVALUE and 
allindexes are LOCAL PARTITIONED.  The partitioning key doesn't really 
matterin this setup since we aren't using the features for its advantages, 
justto be able to swap data and index segments on the fly.  I've 
attached atranscript showing the actual sequence but I'll give you a 
shortexplanation first:There are production tables/indexes that 
are used by the application,whether directly or via synonyms.  There is 
a second set of tables with a_TEMP suffix that have duplicate structural 
definitions (constraints,column names and data types, 
etc.)    The indexes also end with a _TEMP butare identical 
to the production ones.  The only difference is that they 
arepartitioned tables/indexes.  All partitioned objects have a single 
rangepartition by a bogus column.  The single partition is bounded by 
theMAXVALUE keyword, so all of the data is contained in one 
partition.Now you can manipulate the _TEMP tables at your 
convenience withoutinterrupting the access tot he "published" objects.  
Once you haverefreshed your _TEMP objects and are ready to publish the new 
data yourwould execute a series of ALTER TABLE _TEMP 
EXCHANGE PARTITIONTABLE .  That's it.  No 
re-pointing of synonyms, revalidating ofviews/stored procs./etc.  The 
application keeps chugging along.  The nextexecution of SQL will use 
the published tables.HTHTony 
Aponte** pseudo-attachment 
**SQL> create table x(x1 number,x2 
varchar2(50));Table created.SQL> create index xi1 on 
x(x1);Index created.SQL> create table y(x1 number,x2 
varchar2(50))  2   partition by range (x1)  (partition y 
values less than (maxvalue));Table created.SQL> 
create index yi1 on y(x1)  2  local (partition yi1 
);Index created.SQL> insert into x values 
(1,'original data from regular table');1 row 
created.SQL> insert into y values (2,'original data from 
partitioned table');1 row created.SQL> 
commit;Commit complete.SQL> select * from 
x;    X1 X2-- 
-- 
1 original data from regular tableSQL> select * from 
y;    X1 X2-- 
-- 
2 original data from partitioned tableSQL> alter table y exchange 
partition y with table x;Table altered.SQL> select * 
from x;    X1 
X2--

RE: Problem with standby database & listener

[Aponte, Tony]

Could it be that the standby is 
auto-registering itself with the listener for the production database?  
We've had a couple of incidents where a development DBA copied the init.ora file 
from a production database configured for MTS.  But the listener 
parameters were not changed and all new connections were redirected to the 
development database.  I suspect that the standby is auto-registering 
itself with the listener and bumping of production.  Bouncing the 
production server then does it back to the standby.
 
HTH
Tony Aponte

  -Original Message-From: Stephen Andert 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 21, 
  2002 3:59 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Problem with standby database & 
  listener
  Fellow dba's, 
   
  We have come across a weird problem with a standby database. 
  Every time we issue "alter database mount standby database" on 
  the standby database, the primary database fails to connect through 
  listener. It becomes OK after primary database is shutdown and restarted — of 
  course this can is not an acceptable solution since the standby will be 
  opened every week for reporting.
   
  Anyone experience this before?  Any direction would be 
  appreciated.
   
  Thanks
   
   
   
   
   
   
  Stephen AndertScottsdale, 
Arizona

RE: How to make deletes faster.

[Aponte, Tony]

Title: RE: How to make deletes faster.






How are you selecting the rows to be deleted? Is it in one cursor driving a loop with incremental commits or is it done via batch cycles of 10,000-row delete ... from ...where commit; delete ... from ...where commit; .?

Tony Aponte


-Original Message-

From: sonia pajerowski [mailto:[EMAIL PROTECTED]]

Sent: Thursday, February 21, 2002 5:13 PM

To: Multiple recipients of list ORACLE-L

Subject: How to make deletes faster.



Hello All,

I have a non-partitioned table with 20 millions

records and growing. Every night a pl/sql stored

procedures deletes around 1 million rows 10,000 at a

time.Currently it is taking aroung 1 hour to delete 1

million messages.

Is there any way I can make deletes faster. I need

good suggestions. I have already tried all the obvious

init.ora parameters like make_delete_faster=true but

they do not seem to work.:-)


Thanks

Sonia



__

Do You Yahoo!?

Yahoo! Sports - Coverage of the 2002 Olympic Games

http://sports.yahoo.com

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: sonia pajerowski

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: URGENT Help on tracking unauthorized login to Oracle Database

[Aponte, Tony]

Title: RE: URGENT Help on tracking unauthorized login to Oracle Database






I have an idea for a poor man's version of what you are looking for.  Turn on listener logging to a level high enough where you can see the text of the net traffic.  With some creativity or a copy of Sed & Awk you'll be able to search for the "invalid username/password" text.  A visual inspection of the surrounding log file lines will give you machine name, osuser, username, etc.  Depending on your connection activity disk space may be an issue.  We've implemented jobs to cycle the log every hour to reduce the disk consumption.  I figure that you could do the same and delete any logs that don't have any violations in it.

HTH

Tony Aponte


-Original Message-

From: Mandal, Ashoke [mailto:[EMAIL PROTECTED]]

Sent: Friday, February 08, 2002 1:54 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: URGENT Help on tracking unauthorized login to Oracle

Database



Joe,


We referred the auditing option. My understanding is that you can track the oracle users using database auditing feature once the users are logged into the database. 

But my requirement is to track the users who tried to login to the database but could not login due to wrong password.


For example, somebody may know the connect string for an oracle database and trying to login to the database as system user and with various combination of password. We like to know who are these users.

Thanks,

Ashoke


-Original Message-

Sent: Friday, February 08, 2002 11:54 AM

To: Multiple recipients of list ORACLE-L

Database



Administrators guide


chapter on auditing.


joe


Mandal, Ashoke wrote:


>>Greetings,

>>

>>We have a database with very sensitive data. Our management wants me to find out the way to secure this data from unauthorized login and track these users who tried to login to this database.

>>

>>Is there any way we can track the unauthorized users, who try to login to an oracle database with invalid userid or password but with valid connect string. 

>>

>>If there is no options under oracle then is there any 3rd party software for this purpose.

>>

>>Any help is appreciated

>>

>>Thanks,

>>Ashoke

>>



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Joseph S Testa

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Mandal, Ashoke

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Help !! Password Encryption/decryption ??

[Aponte, Tony]

Title: RE: Help !! Password Encryption/decryption ??






How about right-padding the string to a multiple of 8 bytes.  I think 8-MOD(LENGTH(string),8) will give you the number of characters to use in RPAD(string,...).  E.I.. to pad with blanks:

SELECT RPAD('123456789',LENGTH('123456789') + 8 - MOD(LENGTH('123456789'),8),' ') AS "12345678901234567890" FROM DUAL


1234567890123456



123456789


HTH

Tony Aponte

-Original Message-

From: Prem J Khanna [mailto:[EMAIL PROTECTED]]

Sent: Friday, February 08, 2002 9:08 AM

To: Multiple recipients of list ORACLE-L

Subject: Help !! Password Encryption/decryption ??



Hello everybody ,


I am on 8.1.6/NT .


i need to encrypt / decrypt the password of my users for my web based

application.

I went thro' DBMS_OBFUSCATION_TOOLKIT.DESEncrypt and

DBMS_OBFUSCATION_TOOLKIT.DESDecrypt methods .

but as per the docs , this package  requires the password ( which is to

be encrypted ) to be

in multiples of 8 bytes .  i don't think this is possible always .


have i understood anything wrong ?   can anyone throw some light on this

.


how do u guys encounter this situation ? any likely scripts plz ?!!!


TIA.

Jp.


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Prem J Khanna

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Where does a DBA go from here?

[Aponte, Tony]

Title: RE: Where does a DBA go from here?






I haven't read the Tuning 101 but it sounds like it lets you know where you are waiting.  The Internals book will give you in-depth knowledge of why by diving deeper into some of the algorithms used to implement those waits.  Break out the aspirin 'cause it ain't leisure reading.

HTH

Tony Aponte


-Original Message-

From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]

Sent: Friday, February 08, 2002 12:48 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: Where does a DBA go from here?



Can anyone tell me if Steve's book contains details that would be useful in

understanding the Wait Interface that Tuning 101 covers? Or would it be more

redundant from that aspect? Thanks.

Dennis Williams

DBA

Lifetouch, Inc.

[EMAIL PROTECTED]



-Original Message-

Sent: Friday, February 08, 2002 5:18 AM

To: Multiple recipients of list ORACLE-L



I agree, get the pints in first. Worry about Oracle later. When you do come

to it, try "Oracle8i internal services for waits, latches, locks and memory"

by Steve Adams.


And of course book.


Jim


-Original Message-

Lee - lerobe

Sent: 08 February 2002 09:58

To: Multiple recipients of list ORACLE-L



Its Friday, the immediate solution is "get beer" !! As to your other problem

its text book time - take a look at Tuning 101 by the lists own Kirti and

Gaja.


Lee



-Original Message-

Sent: 07 February 2002 21:07

To: Multiple recipients of list ORACLE-L



So, there I am.  I've taken the main Oracle courses -- Intro to SQL, DBA

(Oracle 7!), Backup & Recovery, Network Admin, and Perf Tuning.  Now where

do I go for more Oracle training?


This is sparked by a recent perceived lag in one of our new databases.

We've tracked it down to a possible hot block or two, but I never used X$BH

or V$LATCH_CHILDREN in any of my Oracle classes.  And I *know* I'm far from

being ready for an Internals class.  So how do I get from here to there?


education.oracle.com doesn't seem to have a whole lot other than Internals.

Or is that where I'm at now?


Confused and no beer.


Rich Jesse    System/Database Administrator

[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com

--

Author: Jesse, Rich

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



The information contained in this communication is

confidential, is intended only for the use of the recipient

named above, and may be legally privileged. If the reader

of this message is not the intended recipient, you are

hereby notified that any dissemination, distribution or

copying of this communication is strictly prohibited.

If you have received this communication in error, please

re-send this communication to the sender and delete the

original message or any copy of it from your computer

system.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com

--

Author: Robertson Lee - lerobe

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: James McCann

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: DENNIS WILLIAMS

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California

RE: Help !! Password Encryption/decryption ??

[Aponte, Tony]

Title: RE: Help !! Password Encryption/decryption ??






Here it is for any length.


SELECT RPAD('&thestring',LENGTH('&thestring') + 8 - DECODE(MOD(length('&thestring'),8),0,8,MOD(length('&thestring'),8)),' ') ||'<' AS "12345678901234567890" FROM DUAL

Tony Aponte


-Original Message-

From: Aponte, Tony 

Sent: Friday, February 08, 2002 11:32 AM

To: '[EMAIL PROTECTED]'

Cc: '[EMAIL PROTECTED]'

Subject: RE: Help !! Password Encryption/decryption ??



How about right-padding the string to a multiple of 8 bytes.  I think 8-MOD(LENGTH(string),8) will give you the number of characters to use in RPAD(string,...).  E.I.. to pad with blanks:

SELECT RPAD('123456789',LENGTH('123456789') + 8 - mod(length('12345678'),8),' ') AS "12345678901234567890" FROM DUAL


1234567890123456



123456789


HTH

Tony Aponte

-Original Message-

From: Prem J Khanna [mailto:[EMAIL PROTECTED]]

Sent: Friday, February 08, 2002 9:08 AM

To: Multiple recipients of list ORACLE-L

Subject: Help !! Password Encryption/decryption ??



Hello everybody ,


I am on 8.1.6/NT .


i need to encrypt / decrypt the password of my users for my web based

application.

I went thro' DBMS_OBFUSCATION_TOOLKIT.DESEncrypt and

DBMS_OBFUSCATION_TOOLKIT.DESDecrypt methods .

but as per the docs , this package  requires the password ( which is to

be encrypted ) to be

in multiples of 8 bytes .  i don't think this is possible always .


have i understood anything wrong ?   can anyone throw some light on this

.


how do u guys encounter this situation ? any likely scripts plz ?!!!


TIA.

Jp.


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Prem J Khanna

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: HOW TO KNOW INDEX NOT IN USE?

[Aponte, Tony]

Title: RE: HOW TO KNOW INDEX NOT IN USE?






Nope.  Same behavior for me.


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, February 05, 2002 11:01 AM

To: Multiple recipients of list ORACLE-L

Subject: RE: HOW TO KNOW INDEX NOT IN USE?



Can anyone else get to this article? 


My browser gets redirected to 

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89852/d_metada.htm#1656


Jared






àãø éçéàì <[EMAIL PROTECTED]>

Sent by: [EMAIL PROTECTED]

02/05/02 12:45 AM

Please respond to ORACLE-L


 

    To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>

    cc: 

    Subject:    RE: HOW TO KNOW INDEX NOT IN USE?



Hello Seema


Oracle magazine for 01-02/2002 has on page 91 an article 

that gives step by step instructions on this issue for 9i.

They also have a reference to:

http://www.oracle.com/oramag/oracle/jan02/metadata.html


Yechiel Adar, Mehish Computer Services

[EMAIL PROTECTED]


> -Original Message-

> From:  Seema Singh [SMTP:[EMAIL PROTECTED]]

> Sent:  Mon, February 04, 2002 7:55 PM

> To:    Multiple recipients of list ORACLE-L

> Subject:   HOW TO KNOW INDEX NOT IN USE?

> 

> Hi

> I there any view which can tell us which indexes are not in use?

> Thx

> -Seema

> 

> 

> 

> _

> Chat with friends online, try MSN Messenger: http://messenger.msn.com

> 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> -- 

> Author: Seema Singh

>   INET: [EMAIL PROTECTED]

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California    -- Public Internet access / Mailing Lists

> 

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

>  This e-mail was scanned by the eSafe Mail Gateway 

> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




>Wš±ëzØ^¡÷âr&¥9,BÅ
m¶ŸÿÃ

(­§Ú©Ê&ëa¢²

2Z­çR¶)e­§b³+¢aZ´(­È׭ŠäIêï‰Ç¬óŸ9ßÎtçQ@_Î|ç9ӝRjpâz

jXŸ¢¹âhû›–'ž×«ëZqǬ³óŠX§€¸¬¶ÄèDCTL¨º»•÷ë¢kaŠÉšŠX§‚X¬¶Ç§u©Ä1¨¥™ë,j­ ¸¬´k«¹ö­r+rr‰§¢×„\“²—¥–)à¡òâ²Ñ®®æ§v)í…鞲Ơxƒb)ܖç^jX§yÊ'µ¨§Šx5%
9,Bè®Ø^©ž¡ùšŠX§‚X¬·*.Á©í¶†Þ­é¨½ç_®‰˜¢éšÉ©l¢Ç§vØ^BÏr‰¦jw_¢º-…êâú+™«b¢yb‘ë.nÇ+‰¸§

RE: HOW TO KNOW INDEX NOT IN USE?

[Aponte, Tony]

Title: RE: HOW TO KNOW INDEX NOT IN USE?






Another option you have if your Oracle version is high enough is to use Stored Outlines.  Enable automatic generation of stored outlines for a full processing cycle as defined by the application (full month, Qtr, etc.)  Then extract all of the indexes used during that cycle from OL$HINTS.HINT_TEXT (i.e.. WHERE HINT_TEXT LIKE 'INDEX%'). If all of your application code has been traversed in the cycle then this list will be pretty darn close to real usage.  Even if you can't wait until year-end processing, you can eliminate the bulk of code to be mined for embedded SQL and focus on those once-a-year programs.

HTH 

Tony Aponte


-Original Message-

From: Seema Singh [mailto:[EMAIL PROTECTED]]

Sent: Monday, February 04, 2002 12:55 PM

To: Multiple recipients of list ORACLE-L

Subject: HOW TO KNOW INDEX NOT IN USE?



Hi

I there any view which can tell us which indexes are not in use?

Thx

-Seema




_

Chat with friends online, try MSN Messenger: http://messenger.msn.com


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Seema Singh

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: How can i receive name of the running procedure

[Aponte, Tony]

Title: RE: How can i receive name of the running procedure






Take a look at DBMS_UTILITY.FORMAT_CALL_STACK.  You can parse out the caller from the return value.  Here's a short example of an anonymous block:

DECLARE

stack_info VARCHAR2(4096);

BEGIN

stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;

DBMS_OUTPUT.PUT_LINE(stack_info);

END;



- PL/SQL Call Stack -

  object  line  object

  handle    number  name

c1b59734 4  anonymous block



HTH

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Friday, January 25, 2002 1:07 PM

To: Multiple recipients of list ORACLE-L

Subject: How can i receive name of the running procedure



Hallo,


How can I select the name of the procedure, which is running.?

I mean I am running a procedure and I want the name of the pocedure to be inserted in a table.

 Please help me with a simple pl/sql script on this.


Thanks in advance



Roland S


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Hmmmmm

[Aponte, Tony]

Title: RE: Hm






What you are observing is the concurrency mechanism as implemented in the Oracle rdbms.  I couldn't explain it better in an email so I suggest you get a hold of Expert One-on-one Oracle by Thomas Kyte, chapter 3 on Locking and Concurrency.  Another thought I have is that it looks like you are trying to implement a sequence number.  If the application can tolerate gaps in the numbers then you can use an rdbms-managed sequence number and use NEXTVAL in place of the SELECT FOR UPDATE.

HTH.

Tony Aponte


-Original Message-

From: Stefan Jakobsson [mailto:[EMAIL PROTECTED]]

Sent: Friday, January 25, 2002 5:35 AM

To: Multiple recipients of list ORACLE-L

Subject: Hm



Having a small problem I need some help with...


The problem is trying to lock a row on a parameter table for an application.


The thing we need to do is following:


Read the value in one row. LOCK that row for other users, increase the value

with one

update the row with the new value and release if for access to other users.


We are trying to use the following SQL statement.


SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT;


But the thing is that when we try, anyone can head on in and select the same

value

and even make an update of that row...


What am I missing here?


Regards,

Stefan Jakobsson

Programmer

Arel-Data 

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Stefan Jakobsson

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Hmmmmm

[Aponte, Tony]

Title: RE: Hm






I just had another idea.  Maybe you can use UPDATE  SET =+1 RETURNING  INTO  instead of the SELECT FOR UPDATE.  This will add 1 to the current value and return the result to the caller.

Tony Aponte


-Original Message-

From: Aponte, Tony 

Sent: Friday, January 25, 2002 11:11 AM

To: '[EMAIL PROTECTED]'

Cc: '[EMAIL PROTECTED]'

Subject: RE: Hm



What you are observing is the concurrency mechanism as implemented in the Oracle rdbms.  I couldn't explain it better in an email so I suggest you get a hold of Expert One-on-one Oracle by Thomas Kyte, chapter 3 on Locking and Concurrency.  Another thought I have is that it looks like you are trying to implement a sequence number.  If the application can tolerate gaps in the numbers then you can use an rdbms-managed sequence number and use NEXTVAL in place of the SELECT FOR UPDATE.

HTH.

Tony Aponte


-Original Message-

From: Stefan Jakobsson [mailto:[EMAIL PROTECTED]]

Sent: Friday, January 25, 2002 5:35 AM

To: Multiple recipients of list ORACLE-L

Subject: Hm



Having a small problem I need some help with...


The problem is trying to lock a row on a parameter table for an application.


The thing we need to do is following:


Read the value in one row. LOCK that row for other users, increase the value

with one

update the row with the new value and release if for access to other users.


We are trying to use the following SQL statement.


SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT;


But the thing is that when we try, anyone can head on in and select the same

value

and even make an update of that row...


What am I missing here?


Regards,

Stefan Jakobsson

Programmer

Arel-Data 

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Stefan Jakobsson

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: SPACE FREE HOW?

[Aponte, Tony]

Title: RE: SPACE FREE HOW?



Nick,
I have 
a question regarding LiveReorg's method that prevents modifications to the 
original table when it's time to switch to the newly reorged one.  AS the 
mechanism to prevent DML,  LiveReorg creates a temporary trigger 
on Insert, Update and Delete.  It simply raises a user-defined 
exception.  With our 24x7 Siebel environment executing massive insert and 
updates against the base tables, we expect that the application will receive 
this error when we switch to the new table.  The problem is that the 
application is not coded to handle the exception and the user will get an error 
displayed on the client.  This is preventing us from utilizing the Live 
feature of LiveReorg and is becoming as sore spot with our internal 
clients.  They expected uninterrupted maintenance and frankly, so did 
we.  Please correct me if I'm wrong or if I'm not using the tool 
correctly.
 
Tony 
Aponte
Home 
Shopping Network

  -Original Message-From: Nick Wagner 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 12:51 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  SPACE FREE HOW?
  24x7... that does make it tough..  you might want to try 
  LiveReorg from Quest Software...  it allows you to reorganize those 
  tables while users are still accessing them.  It will even tell you which 
  tables to reorg to free up the most space... without even deleting data.  
  
  you can find out info about the products at 
  www.quest.com/livereorg 
  you can even get a free trial version to test it out, and make 
  sure it is what you need. 
  Nick 
  -Original Message- From: Seema 
  Singh [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, January 18, 2002 7:41 AM To: Multiple recipients of list ORACLE-L Subject: RE: SPACE FREE HOW? 
  Ethan The database is in production 
  and users are accesing 24x7.What will be best approach 
  to do? DO u have any scripts how to find HWM? 
  Thanks -Seema 
  >From: "Post, Ethan" <[EMAIL PROTECTED]> 
  >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L 
  <[EMAIL PROTECTED]> >Subject: RE: SPACE 
  FREE HOW? >Date: Thu, 17 Jan 2002 14:25:37 
  -0800 > >Deleting 
  records from a table does not free up space in the database. >Search for high water mark in the concepts manual and you should 
  find an >explanation of how this works.  The 
  statement below only frees up space >above the high 
  water mark on the table. > >You can... > >create table foo nologging as (select * from big_table); 
  > >truncate table 
  big_table; > >insert 
  into big_table (select * from foo); > 
  >drop table foo; > 
  > >-Ethan > >-Original Message- 
  >Sent: Thursday, January 17, 2002 3:27 PM >To: Multiple recipients of list ORACLE-L > > >ALTER 
  TABLE table DEALLOCATE UNUSED KEEP integer; >the 
  keep clause is optional. > >-sunil > >-Original Message- >Sent: 
  Thursday, January 17, 2002 2:58 PM >To: Multiple 
  recipients of list ORACLE-L > > >Hi >I 
  deleted millions of rows from diffrent tables and I have not seen any 
  >impact on database size.What I have to do to get that 
  free space? >Is it necessary to shutdown the 
  database? >Thx >-Seema > >-- >Please see the official ORACLE-L 
  FAQ: http://www.orafaq.com >-- >Author: Post, Ethan >   INET: [EMAIL PROTECTED] > >Fat City Network 
  Services    -- (858) 538-5051  FAX: (858) 538-5051 
  >San Diego, 
  California    -- Public Internet access 
  / Mailing Lists > 
  >To REMOVE yourself from this mailing list, send an E-Mail 
  message >to: [EMAIL PROTECTED] (note EXACT 
  spelling of 'ListGuru') and in >the message BODY, 
  include a line containing: UNSUB ORACLE-L >(or the 
  name of mailing list you want to be removed from).  You may 
  >also send the HELP command for other information (like 
  subscribing). 
  _ 
  Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. 
  
  -- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.com -- Author: Seema Singh   INET: [EMAIL PROTECTED] 
  Fat City Network Services    -- (858) 
  538-5051  FAX: (858) 538-5051 San Diego, 
  California    -- Public Internet access 
  / Mailing Lists  
  To REMOVE yourself from this mailing list, send an E-Mail 
  message to: [EMAIL PROTECTED] (note EXACT spelling 
  of 'ListGuru') and in the message BODY, include a line 
  containing: UNSUB ORACLE-L (or the name of mailing 
  list you want to be removed from).  You may also 
  send the HELP command for other information (like subscribing). 

RE: Freeable memory

[Aponte, Tony]

Title: RE: Freeable memory






Hi Mike,


I did a test on one of our 7.3 databases.  It seems that some (but not all) freeable chunks get coalesced by flushing the shared pool.  I tested it on a database with no other users logged in and took before/after pictures of the chunk breakdown.

SQL> select

  ksmchcom  contents,

  count(*)  chunks,

  sum(decode(ksmchcls, 'recr', ksmchsiz))  recreatable,

  sum(decode(ksmchcls, 'freeabl', ksmchsiz))  freeable,

  sum(ksmchsiz)  total

from

  sys.x$ksmsp

where

  ksmchcls not like 'R%'

group by

  ksmchcom

/

CONTENTS CHUNKS RECREATABLE   FREEABLE  TOTAL

 -- --- -- --

KGL handles 188   54776 54776

PL/SQL DIANA 72    6604 134196 140800

PL/SQL MPCODE    20    6496  25264  31760

PLS cca hp desc   1    164    164

PLS non-lib hp    1    2096  2096

character set m   5  21456  21456

dictionary cach  85 172468 172468

fixed allocatio  26 832   832

free memory  25  82956796

kzull 6    284    284

library cache   460   78924  67472 146396

multiblock rea    1   1040   1040

permanent memor   1  13179484

row cache lru    30    1320  1320

session param v   9  19764  19764

sql area    119  186336 149732 336068


16 rows selected.


SQL> alter system flush shared_pool;


System altered.


SQL> select

  ksmchcom  contents,

  count(*)  chunks,

  sum(decode(ksmchcls, 'recr', ksmchsiz))  recreatable,

  sum(decode(ksmchcls, 'freeabl', ksmchsiz))  freeable,

  sum(ksmchsiz)  total

from

  sys.x$ksmsp

where

  ksmchcls not like 'R%'

group by

  ksmchcom

/

CONTENTS CHUNKS RECREATABLE   FREEABLE  TOTAL

 -- --- -- --

KGL handles  67   19812 19812

PL/SQL DIANA 66    4508 125668 130176

PL/SQL MPCODE    12    3036  13648  16684

PLS cca hp desc   1    164    164

PLS non-lib hp    1    2096  2096

character set m   5  21456  21456

dictionary cach  78 144728 144728

fixed allocatio  26 832   832

free memory  35  83352232

kzull 6    284    284

library cache   164   27060  24940  52000

permanent memor   1  13179484

row cache lru    30    1320  1320

session param v   9  19764  19764

sql area 46   64152  60320 124472


15 rows selected.


SQL> 


As for determining the chunks per session, the only way I know is to dump the heaps via ORADEBUG DUMP commands.  The trace files will have the breakdown of type and status for each chunk.  I don't remember off the top of my head how to dump it for individual sessions but it can be researched.  Here is a sample of ORADUBG DUMP HEAPDUMP 10:

*** 2002.01.18.10.43.55.000

*** SESSION ID:(7.8650) 2002.01.18.10.43.55.000

**

HEAP DUMP heap name="sga heap"  desc=0x801c

 extent sz=0xfc4 alt=44 het=32767 rec=1 flg=2 opc=0

 parent=0 owner=0 nex=0 xsz=0xc91a64

EXTENT 0

  Chunk 85009b7c sz= 13179484    perm  "perm   "  alo=7200716

EXTENT 1

  Chunk 84009b80 sz= 15604404    free  "   "

  Chunk 84eeb634 sz=  560    recreate  "library cache  "  latch=0

 ds 84eeb870 sz=  560

  Chunk 84eeb864 sz=   96    freeable  "library cache  "

  Chunk 84eeb8c4 sz=  172    recreate  "KGL handles    "  latch=0

  Chunk 84eeb970 sz=  288    recreate  "KGL handles    "  latch=0

  Chunk 84eeba90 sz=  560    recreate  "library cache  "  latch=0

 ds 84eebccc sz=  560

  Chunk 84eebcc0 sz=   96    freeable  "library cache  "

  Chunk 84eebd20 sz=  172    recreate  "KGL handles    "  latch=0

  Chunk 84eebdcc sz=  560    recreate  "library cache  "  latch=856ca7e0

 ds 84eec008 sz=  560

  Chunk 84eebffc sz=   96    freeable  "library cache  "

  Chunk 84eec05c sz= 3952    freeable  "sql area   "  ds=84eee5e0

  Chunk 84eecfcc sz=  296    recreate  "KGL handles    "  latch=856ca7e0

  Chunk 84eed0f4 sz=  168    recreate  "library cache  "  latch=856ca7e0

 ds 84eed330 sz=  168

  Chunk 84eed19c sz=  392    freeable  "library cache  "  ds=84eef190

  Chunk 84eed324 sz= 

RE: How to calculate user load on the system

[Aponte, Tony]

Title: RE: How to calculate user load on the system



Then 
you're in luck.  I'd recommend starting with Oracle Response Time 
Analysis from www.orapub.com.  Although 
it won't give you exactly what you need, it will help you get to the next 
step.  Once you understand a session's response time components, it's a 
short hop to figuring out the V$SESSTAT statistics that make up the CPU time, 
physical/logical I/O operations and memory footprint.
 
HTH
Tony

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 16, 
  2002 5:54 PMTo: Aponte, Tony; 
  [EMAIL PROTECTED]Subject: RE: How to calculate user load on the 
  system
  Database Load ... is the main target at this time 
  ...
   
  Thanks Tony,
   
  Raj
  __
  Rajendra 
  Jamadagni  
      MIS, ESPN Inc.
  Rajendra dot Jamadagni at ESPN 
  dot com
  Any opinion expressed here is 
  personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, 
  but having an opinion is an 
  art!
  
-----Original Message-From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 16, 2002 5:53 
PMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: How to calculate user 
load on the system
Raj, sorry for me being confused.  Are you trying to 
measure what the application executable is doing outside the database (i.e.. 
host system load) or the activity inside the database (i.e.. per 
user/program/module DB stats)?
Tony 
 

RE: How to calculate user load on the system

[Aponte, Tony]

Title: RE: How to calculate user load on the system






Raj, sorry for me being confused.  Are you trying to measure what the application executable is doing outside the database (i.e.. host system load) or the activity inside the database (i.e.. per user/program/module DB stats)?

Tony


-Original Message-

From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, January 16, 2002 4:31 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: How to calculate user load on the system



Dennis,


Actually we have N users on the system where N is a variable. Of that I have

a certain group of users, I need to monitor and see how much load they put

on the system.


The idea is out on N total users on the system, X number of users use only

one part of application 95% of the time (because it is their job). So, If I

get some kind of metric (numbers ... numbers) then we might think of

spinning these users and their application to another box in their own

instance. They can talk to our application by db links, so that won't be a

problem.


I am looking for some kind of metric to either prove the need for another

box and instance or otherwise.


Hopefully this time it is little bit clear ...


TIA

Raj

__

Rajendra Jamadagni      MIS, ESPN Inc.

Rajendra dot Jamadagni at ESPN dot com

Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art!

RE: Socket directory under solaris

[Aponte, Tony]

Title: Socket directory under solaris



Try 
/ver/tmp/.oracle
 
Tony 
Aponte

  -Original Message-From: Adams, Matthew (GEA, 088130) 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, January 14, 2002 9:00 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Socket directory under solaris
  Under HP-UX, NET8 sets up  a directory called  /tmp/.oracle and creats a number of sockets there. 
  Does Oracle have a similar directory containing 
  sockets under solaris? Where? (It 
  does not appear to be /tmp). 
   Matt Adams - GE Appliances - 
  [EMAIL PROTECTED] Thus spake the master 
  programmer:  "Let the programmers by many and the 
  managers few, Then all will be productive"  - The 
  Tao of Programming 

RE: VLDB backup policy

[Aponte, Tony]

Title: RE: VLDB backup policy






What type of application is it (OLTP, DSS, mixed, etc.)?  Also, is it monolithic or can the functionality be broken down into usage profiles (i.e. mostly read, high velocity update, etc.)?

-Original Message-

From: Andrey Bronfin [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, January 02, 2002 9:50 AM

To: Multiple recipients of list ORACLE-L

Subject: VLDB backup policy



Dear list ! 

I'm reposting this , since got no replies yet.


I need to design a backup policy for a VLDB sized some 10TB, running

as close to 24X7 as possible.

I need 2 versions of the policy:

 One is the "best case" , i.e. money does not matter, the company can aquire

any software / hardware , the only goal is to have a solid backup and

ability to backup and recover as fast as possible.

 The second is the opposite case - how to achieve a good backup spending as

little money as possible, possibly tolerating a little more downtime in case

of a crash.


I just have never happened to work with 10 Terrabytes size of DB, in

particular ,i believe that my proven backup strategies that work well with

100GB DB might need some amending when it comes to 10 TB size. 


Another constraint is that i'm limited to Oracle 8.1.7 , and can not upgrade

to 9i.

 

I need to decide which hardware/software needs to be purchased/evaluated to

implement solid DRP and HA. 

People say : EMC , Veritas , Legato etc...

I'm just lost among these (and many others) buzzwords and need a "Second

opinion" from gurus, like you. 

Please share your experience and thoughts.

Thanks a lot in advance !

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Andrey Bronfin

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Install of two client versions on same box

[Aponte, Tony]

Title: Install of two client versions on same box



This 
is very easy if you follow OFA standards for software installation.  The 
different versions are installed as sub-directories of the $ORACLE_BASE 
directories (i.e... /oracle/app/oracle/product/8160, 
/oracle/app/oracle/product/8172).  You then switch versions by changing 
your ORACLE_HOME, LD_LIBRARY_PATH, etc.
 
Tony 
Aponte

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 
  13, 2001 10:10 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Install of two client versions on same 
  box
  Hi! 
  Is it possible to install two client versions (8.1.6 and 
  8.1.7) on the same Sun Solaris box? The reason for 
  that is that we are using BEA Weblogic, which requires a specific Oracle 
  client version and our application needs another one. Don't ask me about the 
  sense behind it...
  This is 8.1.6/8.1.7 on Sun Solaris. 
  Thanks, Helmut 

RE: Lookup table design thoughts needed

[Aponte, Tony]

Title: RE: Lookup table design thoughts needed






I would start by considering how the application is deployed.  For code that is easily deployed (E.I.. executables are located on a few application servers or a shared drive) I would consider compiling the rule data along with the logic.  It generally improves run-time performance because there is no database access, network traffic, etc.  Initialization of the bloated executable would be offset by the run-time savings.  You incur a penalty when adding a new codes since you have to recompile and deploy the new executable but itsanotsobad in an app. server/shared drive deployment.

Another consideration to take is the frequency that rules may change.  Your example for state codes has remained the same since the 50's.  I would pursue the hardcoded route if the code tables are more or less stable, or if the changes in the rules would involve code changes anyway.

In a 2-tier client-server deployment I would go the database route, although separate tables for each type of code.  This will simplify code table changes by not requiring a redeployment of the compiled code.

Tony Aponte


-Original Message-

From: Tracy Rahmlow [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, December 05, 2001 12:15 PM

To: Multiple recipients of list ORACLE-L

Subject: Lookup table design thoughts needed




We are currently looking at rewriting our entry system and one issue that I am

looking for some feedback involves the use of lookup tables and

populating/editing screens.  We are looking at creating a generic table that

contains all the valid entries for each drop-down list.  For example, we may

display a list of valid states for the user to select.  The proposed "edit"

table contains a row for each state with the following columns as an example:


table_name: address

column_name: state_cd

code: WI

description: Wisconsin


In addition, we have situations on the screen where a user may select option

'a' in a drop-down list, but can not choose option 'c,d or f' in a different

drop down list.

Any suggestions for designing  a flexible system that would incorporate issues

like the above.  We have been considering either "hard-coding" the edits within

the screen as well as creating a "rules/validation" table that would

incorporate these edits.  How practical is a rules table? (We do have

situations where we may have multiple entries to validate to each other).  I

realize these are very broad questions, so I am looking for generic theories

that may be applied that are flexible for adapting to changes within the

business.  What else should I consider?  It appears as if there are several

ways to skin the cat how do we go about choosing the best method for our

situation.


 In addition, does anybody know of any good websites/books that contain

relational design strategies, tips ...





-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Tracy Rahmlow

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: sql query

[Aponte, Tony]

Title: RE: sql query






Oops.  The statement was wrong.  I'll try it gain:


select

  to_number(null) as id,

  to_char(null) as car_make

from

  dual

union

select

  id,

  car_make

from

  carmake

order by

  car_make NULLS FIRST;


Tony Aponte





-Original Message-

From: Aponte, Tony 

Sent: Tuesday, December 11, 2001 12:21 PM

To: '[EMAIL PROTECTED]'

Cc: '[EMAIL PROTECTED]'

Subject: RE: sql query



add NULLS FIRST after the ORDER BY CAR_MAKE:


select

  to_number(null) as id,

  to_char(null) as car_make

from

  dual

union

select

  id,

  car_make

from

  carmake

order by

  car_make NULLS LAST;


Tony Aponte



-Original Message-

From: Steven Hovington [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, December 11, 2001 10:45 AM

To: Multiple recipients of list ORACLE-L

Subject: sql query



Hi,


I have this sql statement:


select

  to_number(null) as id,

  to_char(null) as car_make

from

  dual

union

select

  id,

  car_make

from

  carmake

order by

  car_make;


So this selects a blank record and then the records from carmake.  But I

want the blank record to

appear at the top of the list, and it must be done in the select statement.

Can this be done?


TIA,



Thanks,

Steven Hovington


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Steven Hovington

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: sql query

[Aponte, Tony]

Title: RE: sql query






add NULLS FIRST after the ORDER BY CAR_MAKE:


select

  to_number(null) as id,

  to_char(null) as car_make

from

  dual

union

select

  id,

  car_make

from

  carmake

order by

  car_make NULLS LAST;


Tony Aponte



-Original Message-

From: Steven Hovington [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, December 11, 2001 10:45 AM

To: Multiple recipients of list ORACLE-L

Subject: sql query



Hi,


I have this sql statement:


select

  to_number(null) as id,

  to_char(null) as car_make

from

  dual

union

select

  id,

  car_make

from

  carmake

order by

  car_make;


So this selects a blank record and then the records from carmake.  But I

want the blank record to

appear at the top of the list, and it must be done in the select statement.

Can this be done?


TIA,



Thanks,

Steven Hovington


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Steven Hovington

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Help in deciphering the values for flag in systemstate dump

[Aponte, Tony]

Title: Help in deciphering the values for flag in systemstate dump






I'm troubleshooting a discrepancy between v$session_wait and Precise/SQL 3.1.  Oracle reports that sessions waiting on "SQL*Net message from client" but the tool shows some of them in MTS wait.  Since Precise/SQL parses the SGA for its data I'm looking into a systemstate dump.  The only link I have found is that the circuit session object have "flag = (10100)" and "flag = (10130)".  The circuits with 10130 are tied to the sessions that are misreported by the tool.  While I'm waiting for tech. support to respond any nuggets you can provide will be greatly appreciated.

Thanks.

Tony Aponte


Environment:

Sun Solaris 8

Oracle 8.1.7.2

5/50 MTS dispatchers/servers


Partial system state dump:

PROCESS 62:

  

  SO: ceaef290, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00

  (process) Oracle pid=62, calls cur/top: 0/d0aab070, flag: (40) DISPATCHER

    int error: 0, call error: 0, sess error: 0, txn error 0

  (post info) last post received: 0 0 118

  last post received-location: kmcpdp

  last process to post me: ceae53fc 1 32

  last post sent: 2147544044 0 111

  last post sent-location: kmcmbf: not KMCVCFTOS

  last process posted by me: ceae53fc 1 32

    (latch info) wait_event=0 bits=0

    Process Group: DEFAULT, pseudo proc: ceba58e4

    O/S info: user: oracle, term: UNKNOWN, ospid: 6500

    OSD pid info: 6500

    

    SO: d08991f4, type: 41, owner: ceaef290, pt: 0, flag: INIT/-/-/0x00

    (circuit) dispatcher process id = (ceaef290, 1)

  parent process id = (62, 1)

  user session id = (152, 28431)

  connection context = 1a3c454

  user session = (cebf98fc), flag = (10100), queue = (8)   <= The flag is on this line.

  current buffer = (1), status = (0, 0)

  

  SO: cebf98fc, type: 3, owner: d08991f4, pt: 0, flag: INIT/-/-/0x00

  (session) trans: 0, creator: d08991f4, flag: (8e1) USR/- -/-/-/-/-/-

    DID: 0001-000B-0A09, short-term DID: --

    txn branch: 0

    oct: 0, prv: 0, user: 17178/CRUZDO

  O/S info: user: CRUZD, term: OMSCTX10, ospid: 4040:3800, machine: STPETE\OMSCTX10

    program: siebel.exe

  waiting for 'SQL*Net message from client' blocking sess=0x0 seq=1017 wait_time=0

  driver id=4d545300, #bytes=1, =0

    

RE: Retek ERP & ORacle

[Aponte, Tony]

Title: RE: Retek ERP & ORacle






Yes.  Still on 7.3.4.


-Original Message-

From: MRaval [mailto:[EMAIL PROTECTED]]

Sent: Friday, December 07, 2001 12:10 PM

To: Multiple recipients of list ORACLE-L

Subject: Retek ERP & ORacle



Anyone in this forum working with Retek ERP.  Thanks.

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: MRaval

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: CROSS TAB QUERY

[Aponte, Tony]

Title: RE: CROSS TAB QUERY






I gave it a whack and came up with the following solution (I needed the distraction).  For demo purposes each statement build upon the previous ones.  But first a little reality.  There are some problem boundaries that can be declared.  The first one is that there are 12 months in the year so we can adjust the SQL to accommodate them.  The second is that the table's columns are also known.  

I created a bogus table with the format you included in the example:


create table x (MONTH VARCHAR2(20),

ERLANG  NUMBER(7,2),

CONG        NUMBER(5,2),

CSETUP  NUMBER(5,2));


insert into x values('JAN',1,1,1);


insert into x values('FEB',2,2,2);


insert into x values('MAR',3,3,3);


insert into x values('DEC',4,4,4);

            

commit;



Since the table's columns are known I created an in-line view of month,column name,value:


SELECT month,colname,value 

FROM (SELECT month,'erlang' colname,erlang value from x

       UNION

      SELECT month,'cong',cong             from x

       UNION

      SELECT month,'csetup',csetup         from x);

      


With this statement I add a row number to each so that I can pivot by them:

      

SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) 

FROM (SELECT month,'erlang' colname,erlang value from x

       UNION

      SELECT month,'cong',cong             from x

       UNION

      SELECT month,'csetup',csetup         from x);


By adding the DECODE I can place the months into columns:


SELECT 

 colname

,DECODE(line_no,01,month,NULL) Month01

,DECODE(line_no,02,month,NULL) Month02

,DECODE(line_no,03,month,NULL) Month03

,DECODE(line_no,04,month,NULL) Month04

,DECODE(line_no,05,month,NULL) Month05

,DECODE(line_no,06,month,NULL) Month06

,DECODE(line_no,07,month,NULL) Month07

,DECODE(line_no,08,month,NULL) Month08

,DECODE(line_no,09,month,NULL) Month09

,DECODE(line_no,10,month,NULL) Month10

,DECODE(line_no,11,month,NULL) Month11

,DECODE(line_no,12,month,NULL) Month12

FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no 

      FROM (SELECT month,'erlang' colname,erlang value from x

         UNION

            SELECT month,'cong',cong               from x

         UNION

            SELECT month,'csetup',csetup           from x)

      );


The resultset still needs work to put it into a single row.  Here I use the MAX/GROUP functions to return a single row of month names to be used as the column heading of your report:

      

SELECT 

 colname

,MAX(DECODE(line_no,01,month,NULL)) Month01

,MAX(DECODE(line_no,02,month,NULL)) Month02

,MAX(DECODE(line_no,03,month,NULL)) Month03

,MAX(DECODE(line_no,04,month,NULL)) Month04

,MAX(DECODE(line_no,05,month,NULL)) Month05

,MAX(DECODE(line_no,06,month,NULL)) Month06

,MAX(DECODE(line_no,07,month,NULL)) Month07

,MAX(DECODE(line_no,08,month,NULL)) Month08

,MAX(DECODE(line_no,09,month,NULL)) Month09

,MAX(DECODE(line_no,10,month,NULL)) Month10

,MAX(DECODE(line_no,11,month,NULL)) Month11

,MAX(DECODE(line_no,12,month,NULL)) Month12

FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no 

      FROM (SELECT month,'erlang' colname,erlang value from x

         UNION

            SELECT month,'cong',cong               from x

         UNION

            SELECT month,'csetup',csetup           from x)

      )

GROUP BY colname;


Now change the column from month to value to get the actual row data that used to be in columns:


SELECT 

 colname

,MAX(DECODE(line_no,01,value,NULL)) value01

,MAX(DECODE(line_no,02,value,NULL)) value02

,MAX(DECODE(line_no,03,value,NULL)) value03

,MAX(DECODE(line_no,04,value,NULL)) value04

,MAX(DECODE(line_no,05,value,NULL)) value05

,MAX(DECODE(line_no,06,value,NULL)) value06

,MAX(DECODE(line_no,07,value,NULL)) value07

,MAX(DECODE(line_no,08,value,NULL)) value08

,MAX(DECODE(line_no,09,value,NULL)) value09

,MAX(DECODE(line_no,10,value,NULL)) value10

,MAX(DECODE(line_no,11,value,NULL)) value11

,MAX(DECODE(line_no,12,value,NULL)) value12

FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no 

      FROM (SELECT month,'erlang' colname,erlang value from x

         UNION

            SELECT month,'cong',cong               from x

         UNION

            SELECT month,'csetup',csetup           from x)

      )

GROUP BY colname;


I didn't handle sorting by calendar month since that's easy enough to handle with DECODES, but it would have distracted fro

RE: Email through trigger

[Aponte, Tony]

Title: RE: Email through trigger






UTL_SMTP.


-Original Message-

From: Aldi Barco [mailto:[EMAIL PROTECTED]]

Sent: Thursday, November 29, 2001 11:05 AM

To: Multiple recipients of list ORACLE-L

Subject: Email through trigger



Hi Listers,


What is the oracle procedure / package to send an email through store 

procedure or trigger ?


Tia.

Aldi



_

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Aldi Barco

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: CROSS TAB QUERY

[Aponte, Tony]

Title: RE: CROSS TAB QUERY






What Oracle version?  Also, is the requirement for pure SQL or can there be some PL/SQL?


Tony Aponte


-Original Message-

From: Moses Ngati Moya [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, November 28, 2001 3:15 AM

To: Multiple recipients of list ORACLE-L

Subject: CROSS TAB QUERY



Hi Gurus,


I have a table Monthly_Stats as below:


MONTH       VARCHAR2(20),

ERLANG  NUMBER(7,2)

CONG        NUMBER(5,2)

.

.

CSETUP  NUMBER(5,2)


A record is inserted in this table every end of month.


I would like to write an SQL query to produce output below:

            

                MONTH

        JAN FEB MAR APR MAY .   .   .

DEC

        

ERLANG  777 999 98  66  87              999

CONG        9   6   3   4   2

4

CSETUP

.

.

.


I do not know the number of months in advance, i.e. if the table has 3 months

(JAN, FEB, MAR), these are the only months I need in the report.



Any suggestions??



Moses Moya Ngati

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Moses Ngati Moya

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Oracle and MQ

[Aponte, Tony]

Title: RE: Oracle and MQ



The VB 
programs have data validation and standard error trapping for exceptions 
returned by the MQ API calls.  As for your question about 
accessing external procs, MQ provides the API to make calls from C which 
you can certainly use to code the external routines.  Another possibility 
is to use the MQ Java API to code Java stored procs.  Then you won't have 
to handle the listener issues related to external procs and other server 
dependencies.  That's what we would implement if the MQ management 
responsibilities are ever transferred to us in the DBA 
group.
 
Tony 
Aponte
 
 
 -Original Message-From: 
YTTRI Lisa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 21, 
2001 10:59 AMTo: Aponte, Tony; 
'[EMAIL PROTECTED]'Subject: RE: Oracle and 
MQ

  Tony 
  - 
   
  Do 
  you have error handling built in to your VB program, or do you just load it 
  into Oracle and then cleanse/validate the data there?
   
  Do 
  you think it would also be possible to access MQ via external stored 
  procedures?
  
-Original Message-----From: Aponte, Tony 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 20, 2001 4:02 
PMTo: [EMAIL PROTECTED]Cc: 
[EMAIL PROTECTED]Subject: RE: Oracle and MQ
I believe Oracle sells an MQ-to-AQ bridge that just moves 
messages between queuing systems.  We use VB to pull messages out of MQ 
and issue SQL to get it into the database.
HTH Tony Aponte 
-Original Message- From: 
YTTRI Lisa [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, November 20, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Subject: OT: Oracle and MQ 
Hi - 
Does anyone have experience with using MQ w/Oracle.  We 
have an application that is using MQ to get data 
from the mainframe into Oracle.  We are trying to determine what our options are for retrieving the data from the 
MQ queues. 
We know one option is to write a Pro*C program to handle 
this, but this means creating all our own error 
logic, etc.  Does AQ fit into this scenario at 
all, or is that primarily for non-middleware queues? 
Any information / insight would be greatly 
appreciated. 
Thanks - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI  Lisa   INET: [EMAIL PROTECTED] 
Fat City Network Services    -- (858) 
538-5051  FAX: (858) 538-5051 San Diego, 
California    -- Public Internet 
access / Mailing Lists  
To REMOVE yourself from this mailing list, send an E-Mail 
message to: [EMAIL PROTECTED] (note EXACT 
spelling of 'ListGuru') and in the message BODY, 
include a line containing: UNSUB ORACLE-L (or the 
name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like 
subscribing). 

RE: Oracle and MQ

[Aponte, Tony]

Title: RE: Oracle and MQ






I believe Oracle sells an MQ-to-AQ bridge that just moves messages between queuing systems.  We use VB to pull messages out of MQ and issue SQL to get it into the database.

HTH

Tony Aponte


-Original Message-

From: YTTRI Lisa [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, November 20, 2001 11:50 AM

To: Multiple recipients of list ORACLE-L

Subject: OT: Oracle and MQ



Hi - 


Does anyone have experience with using MQ w/Oracle.  We have an application

that is using MQ to get data from the mainframe into Oracle.  We are trying

to determine what our options are for retrieving the data from the MQ

queues.


We know one option is to write a Pro*C program to handle this, but this

means creating all our own error logic, etc.  Does AQ fit into this scenario

at all, or is that primarily for non-middleware queues?


Any information / insight would be greatly appreciated.


Thanks - 

Lisa

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: YTTRI  Lisa

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: exp a big tablespace

[Aponte, Tony]

Title: RE: exp a big tablespace






Here's a tip I mined many moons ago from I don't know where. It's a script to split the compressed file into smaller chunks using the standard UNIX split utility.

HTH

Tony Aponte


 

 


January 16, 1998 


Exporting a Database That's More Than 2GB When Compressed

This Tip of the Week entry comes from Devarajan Sundaravaradan, a Senior Consultant for Leading Edge Systems, Inc. in Edison, New Jersey. 

In HP-UX, there is a 2GB limit on file sizes of 2GB. Many of us have reached this limit when exporting files and the most common solution is to do a filesystem compression of the export dump using named pipes and then store the compressed file. But what if the compressed file itself passes the 2GB limit? There is solution to this, too. 




 Export Section


# Create new Named pipes.


mknod -p /dev/split_pipe


mknod -p /dev/compress_pipe    # You can use the existing named pipe 

  # itself, instead of creating new.


==

Create a shell script under a file, named Split_export.sh

==


# -b1000m indicates to split command to split the input file into every 1000 MB size. 


# As it splits, the split command will suffix aa, ab, ac, ad ... upto zz to the file name specified.


# The export file name is expfile.


nohup split -b1000m < /dev/split_pipe > /DumpDir/expfile & 


nohup compress < /dev/compress_pipe > /dev/split_pipe &


exp username/password full=y file=/dev/compress_pipe and other parameters for export.


===

After saving the above three commands in split_export.sh, execute the following.

===


chmod a+x split_export.sh


nohup split_export.sh > /tmp/split_export.log 1>&2 &


===

After a few minutes you should see files in the export dump directory.

===

-- IMPORT Section


==

Create a shell script with the following command under the file name split_import.sh.


After creating provide execution permission to this script as follows: 

==


Chmod a+x split_import.sh


# The import script assumes in this example that the above export script created 2 split files  


# called expfileaa and expfileab. The order of the file for the cat command is very important.


nohup cat /dumpdir/expfileaa /dumpdir/expfileab > /dev/split_pipe & 


# sleep 3 seconds


Sleep 3


nohup uncompress < /dev/split_pipe > /dev/compress_pipe &


#Sleep at this point is very important as some time is needed to uncompress the file and send it to the pipe.


sleep 60


imp username/password file=/dev/compress_pipe and other parameters for export.


nohup split_import.sh > /tmp/split_import.log 1>&2 &


===

Wait for the import to finish.

===









Home! | Tip & Code Archive | Subscribe & Advertise | Search? 

All specific technical questions from users of Oracle supported products should be directed to Oracle's Technical Support Department. Oracle does not provide any warranty as to the accuracy of any information provided through Oracle Magazine Interactive. Oracle shall not be liable for any damages incurred as a result of reliance on any information provided herein. 

Copyright © 1998 Oracle Corporation. All Rights Reserved. 



-Original Message-

From: Andrew Cook [mailto:[EMAIL PROTECTED]]

Sent: Thursday, November 08, 2001 3:30 AM

To: Multiple recipients of list ORACLE-L

Subject: exp a big tablespace



Hello,


I want to export a big tablespace (big is relative 9GB :) ) but I get an 

error message EXP-0002: error in writing to export file and EXP-00222: 

System error message 22 ... I think my SunOS 5.5.1 not supported large 

files .. But How to export this big tablespace? I think my SuSe 7.1 support 

big files. This is correct ?


Thx for your help!


Cook


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Andrew Cook

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: U

RE: Internal Benchmarking

[Aponte, Tony]

Title: RE: Internal Benchmarking






I've had some luck with the tools described in Scaling Oracle8i by Morle.  It has and awk script to parse out 10046 event trace sql and bind variable information and produce a tcl script suitable for dbaman.  Dbaman is an extended tcl shell sotra-likea oratcl.  I've used it to run sql that I have captured by setting event 10046.  I turn on tracing for the system and for sessions already running.  After the time period has elapsed I turn it off and proceed to collect all trace files into a working directory.  I then feed each trace file into the awk script to produce the dbaman tcl script.  I use a separate instance of dbaman to process each tcl file.  I then reload the database from a golden copy and re-run the scripts, then repeat until I reach my tuning goal.

A few gotcha's; the current dbaman works with 7.3 libraries.  And it has a quirk with the interpretation of the bind variables.  If the bind variable begins with a number then it assumes that the rest of the characters are also numbers, so it chokes on "123ABC" when it fires of an execute operation.  I dug into the dbaman code, found the IF-THEN line that was checking the first character and commented out the entire statement.  I rebuilt dbaman according to the instructions and it worked.  I'll post the changes if you are going to pursue dbaman.

The only thing I didn't try was to simulate the think/latency time.  But that time can be extracted from the tim column in the sql trace file.  I would do this in the awk script that is doing the initial translation of raw trace to dbaman tcl.  The tim value is in 100'ths of a second but I don't know how to convert it to actual time of day.  But the relative time between statements can be derived from it.

HTH.

Tony Aponte



-Original Message-

From: Orr, Steve [mailto:[EMAIL PROTECTED]]

Sent: Wednesday, November 14, 2001 4:00 PM

To: Multiple recipients of list ORACLE-L

Subject: Internal Benchmarking



Howdy All,


I want to create some database-only benchmarking scripts to reflect a

typical day in the life of a custom application. I'm thinking about using

LogMiner to get the redo and v$sqlarea to derive a representative mix of

queries. Maybe we can also sniff/parse the network traffic to the DB server.

Since I'm looking only at database activity I'm not too keen on applications

which merely record and replay end-user keystrokes and mouse clicks but I

would like to mimic delays in transaction commits due to network latency and

user indecision or whatever. I'd also like to be able to increase the load

intensity by factors of 10 to 1000. Has anyone created any application

specific benchmark routines and can you share some tips on how to do this?

Any good tools that you have used? Any comments on Mercury Interactive

stuff? Other ideas? 


AtDhVaAnNkCsE,

Steve Orr

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Orr, Steve

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Oracle Text (ConText/InterMedia) and realtime updates

[Aponte, Tony]

Title: RE: Oracle Text (ConText/InterMedia) and realtime updates






I would suggest researching an Autonomous Transaction fired from the trigger to get around the commit executed by CTX_DDL.SYNC_INDEX.

Another approach I would pursue is to evaluate the use of Catalog Indexes (8.1.7).  If your requirements fit within the catalog index limitations (more like an Intermedia Text-lite) then you are in luck since they are transactional, hence no need for periodic synchronization.

HTH

Tony Aponte


-Original Message-

From: Jesse, Rich [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, November 13, 2001 2:30 PM

To: Multiple recipients of list ORACLE-L

Subject: Oracle Text (ConText/InterMedia) and realtime updates



Hey all,


So, there we are, testing out InterMedia in 8i, in the hopes of using Oracle

Text in 9i.  Is there a way to automagically update the context index

real-time?  I tried a call to CTX_DDL.SYNC_INDEX in a trigger body, but of

course the wrapped procedure does a COMMIT.


The idea right now is to sync the index every minute via DBMS_JOB, but that

just seems too hokey to me.  Apparently, under ConText in Oracle7, the

INT_RX procedure could be used in a trigger, but it's been desupported.

(I'm guessing that locking was an issue?)


Anyone have any ideas?


TIA!

Rich Jesse  System/Database Administrator

[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Jesse, Rich

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Sockets and Pl/SQL

[Aponte, Tony]

Title: RE: Sockets and Pl/SQL






Tom Kyte's book Expert One On One Oracle has a very informative chapter on using UTL_TCP.  Well worth the $ for this chapter alone.  We had a developer waste the company's time trying to create the same functionality using external procedures.

HTH

Tony Aponte


-Original Message-

From: ehsan sinavalda [mailto:[EMAIL PROTECTED]]

Sent: Monday, November 12, 2001 6:55 AM

To: Multiple recipients of list ORACLE-L

Subject: Sockets and Pl/SQL



Hello All


I want to write a trigger for my oracle DB that can

write to a socket when a new record is inserted in to

a table,


Could anyone please help me how can I call socket

methods in PL/SQL


My OS is Linux.


Thanks

E.




__

Do You Yahoo!?

Find a job, post your resume.

http://careers.yahoo.com

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: ehsan sinavalda

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: Works interactively but not in procedure

[Aponte, Tony]

Title: RE: Works interactively but not in procedure






Can you verify the you issued "GRANT SELECT ON SHIP_LOTS TO LOAD_MGR" while connected as the owner of SHIP_LOTS?  This should be enough to make it visible to LOAD_MGR via ALL_TAB_COLUMNS in a stored proc.  Also, SELECT ANY TABLE would do but I wouldn't go that route.

-Original Message-

From: H elp_me [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, October 30, 2001 3:38 PM

To: Aponte, Tony; [EMAIL PROTECTED]

Subject: RE: Works interactively but not in procedure




Hello Tony,


I tried with 'authid current_user' and giving direct select to the user 

'LOAD_MGR' but did not work..


Any other clue ?


Thanks for the time.


Nikunj



>From: "Aponte, Tony" <[EMAIL PROTECTED]>

>To: <[EMAIL PROTECTED]>

>CC: <[EMAIL PROTECTED]>

>Subject: RE: Works interactively but not in procedure

>Date: Tue, 30 Oct 2001 15:06:46 -0500

>

>This is a privilege issue.  The stored proc runs with the security of

>the connected user except for those inherited via roles.  Try adding the

>pragma for current user as follows to see if this is what you want:

>

>CREATE OR REPLACE

>procedure LOAD_MGR.nik1_test

>authid current_user

>as ..

>

>If not, then you will need to grant Select on the objects directly to

>the account.

>

>HTH.

>Tony Aponte

>

>-Original Message-

>From: H elp_me [mailto:[EMAIL PROTECTED]]

>Sent: Tuesday, October 30, 2001 1:25 PM

>To: Multiple recipients of list ORACLE-L

>Subject: Works interactively but not in procedure

>

>

>Hi All,

>

>Here is  the code.. Please let me know. where i went wrong..

>

>SQL> set serveroutput on

>SQL> CREATE OR REPLACE

>   2  procedure LOAD_MGR.nik1_test

>   3   as

>   4  nik_COL1 VARCHAR2(32);

>   5  nik_COL2 VARCHAR2(32);

>   6  CURSOR c_col IS

>   7  --    SELECT owner,table_name FROM all_TABles

>   8  --    where rownum < 10;

>   9 SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

>10 WHERE table_name = 'SHIP_LOTS';

>11  BEGIN

>12  open c_col;

>13  LOOP

>14  fetch c_col into nik_col1,nik_col2;

>15 EXIT WHEN c_col%NOTFOUND;

>16 dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' ||

>nik_col1);

>17 END LOOP;

>18 close c_col;

>19 END;

>20  /

>

>Procedure created.

>

>SQL> exec nik1_test

>

>PL/SQL procedure successfully completed.

>

>SQL> SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

>   2  WHERE table_name = 'SHIP_LOTS';

>

>COLUMN_NAME    TABLE_NAME

>-- --

>QUANTITY   SHIP_LOTS

>QUANTITY_UOM   SHIP_LOTS

>MIX_LOT_FLAG   SHIP_LOTS

>SAP_IM_PLACE   SHIP_LOTS

>SPSID  SHIP_LOTS

>LOT_NUM    SHIP_LOTS

>PARENT_LOT_NUM SHIP_LOTS

>

>7 rows selected.

>

>SQL>  declare

>   2   nik_COL1 VARCHAR2(32);

>   3   nik_COL2 VARCHAR2(32);

>   4   CURSOR c_col IS

>   5   --    SELECT owner,table_name FROM all_TABles

>   6   --    where rownum < 10;

>   7  SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

>   8  WHERE table_name = 'SHIP_LOTS';

>   9   BEGIN

>10   open c_col;

>11   LOOP

>12   fetch c_col into nik_col1,nik_col2;

>13  EXIT WHEN c_col%NOTFOUND;

>14  dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' ||

>nik_col1);

>15  END LOOP;

>16  close c_col;

>17* END;

>SQL> /

>V_COL  :   SHIP_LOTS.QUANTITY

>V_COL  :   SHIP_LOTS.QUANTITY_UOM

>V_COL  :   SHIP_LOTS.MIX_LOT_FLAG

>V_COL  :   SHIP_LOTS.SAP_IM_PLACE

>V_COL  :   SHIP_LOTS.SPSID

>V_COL  :   SHIP_LOTS.LOT_NUM

>V_COL  :   SHIP_LOTS.PARENT_LOT_NUM

>

>PL/SQL procedure successfully completed.

>

>SQL>

>

>NOTE : I tried with ALL_TABLES and that works in procedure too... Only

>ALL_TAB_COLUMNS fails.

>

>All these output and execution are from one user, one session, in the

>sequence shown above.

>

>Thanks

>

>Nikunj

>

>

>_

>Get your FREE download of MSN Explorer at

>http://explorer.msn.com/intl.asp

>

>--

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>--

>Author: H elp_me

>   INET: [EMAIL PROTECTED]

>

>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

>San Dieg

RE: Works interactively but not in procedure

[Aponte, Tony]

Title: RE: Works interactively but not in procedure






This is a privilege issue.  The stored proc runs with the security of the connected user except for those inherited via roles.  Try adding the pragma for current user as follows to see if this is what you want:

CREATE OR REPLACE

procedure LOAD_MGR.nik1_test

authid current_user

as ..


If not, then you will need to grant Select on the objects directly to the account.


HTH.

Tony Aponte


-Original Message-

From: H elp_me [mailto:[EMAIL PROTECTED]]

Sent: Tuesday, October 30, 2001 1:25 PM

To: Multiple recipients of list ORACLE-L

Subject: Works interactively but not in procedure



Hi All,


Here is  the code.. Please let me know. where i went wrong..


SQL> set serveroutput on

SQL> CREATE OR REPLACE

  2  procedure LOAD_MGR.nik1_test

  3   as

  4  nik_COL1 VARCHAR2(32);

  5  nik_COL2 VARCHAR2(32);

  6  CURSOR c_col IS

  7  --    SELECT owner,table_name FROM all_TABles

  8  --    where rownum < 10;

  9 SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

10 WHERE table_name = 'SHIP_LOTS';

11  BEGIN

12  open c_col;

13  LOOP

14  fetch c_col into nik_col1,nik_col2;

15 EXIT WHEN c_col%NOTFOUND;

16 dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' || nik_col1);

17 END LOOP;

18 close c_col;

19 END;

20  /


Procedure created.


SQL> exec nik1_test


PL/SQL procedure successfully completed.


SQL> SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

  2  WHERE table_name = 'SHIP_LOTS';


COLUMN_NAME    TABLE_NAME

-- --

QUANTITY   SHIP_LOTS

QUANTITY_UOM   SHIP_LOTS

MIX_LOT_FLAG   SHIP_LOTS

SAP_IM_PLACE   SHIP_LOTS

SPSID  SHIP_LOTS

LOT_NUM    SHIP_LOTS

PARENT_LOT_NUM SHIP_LOTS


7 rows selected.


SQL>  declare

  2   nik_COL1 VARCHAR2(32);

  3   nik_COL2 VARCHAR2(32);

  4   CURSOR c_col IS

  5   --    SELECT owner,table_name FROM all_TABles

  6   --    where rownum < 10;

  7  SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

  8  WHERE table_name = 'SHIP_LOTS';

  9   BEGIN

10   open c_col;

11   LOOP

12   fetch c_col into nik_col1,nik_col2;

13  EXIT WHEN c_col%NOTFOUND;

14  dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' || nik_col1);

15  END LOOP;

16  close c_col;

17* END;

SQL> /

V_COL  :   SHIP_LOTS.QUANTITY

V_COL  :   SHIP_LOTS.QUANTITY_UOM

V_COL  :   SHIP_LOTS.MIX_LOT_FLAG

V_COL  :   SHIP_LOTS.SAP_IM_PLACE

V_COL  :   SHIP_LOTS.SPSID

V_COL  :   SHIP_LOTS.LOT_NUM

V_COL  :   SHIP_LOTS.PARENT_LOT_NUM


PL/SQL procedure successfully completed.


SQL>


NOTE : I tried with ALL_TABLES and that works in procedure too... Only 

ALL_TAB_COLUMNS fails.


All these output and execution are from one user, one session, in the 

sequence shown above.


Thanks


Nikunj



_

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: H elp_me

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).