from:"john"

Thanks - I wasn't sure if each session got its own instance of extproc.  The SID 
associated with an EXTPROC is not the same as a SID associated with a database.  I 
have several databases running under the same Oracle Home, and they are sharing the 
same external procedure listener - which references that Oracle Home.  If you are 
running databases under several versions of Oracle, you may be able to use the 
listener for the latest version of Oracle you have to listen for all of them, and use 
its extproc.  But it is probably a better idea to run separate listeners for databases 
and external procedures, each with its own LISTENER.ORA and TNSNAMES.ORA under its own 
Oracle Home.  Just be careful about how the TNS administration directory is set.

-Original Message-
Sent: Thursday, January 08, 2004 9:54 AM
To: Multiple recipients of list ORACLE-L


John,

On the contrary.  You do need to associate an EXTPROC with a particular SID 
otherwise running different versions of Oracle on the same box blows the EXTPROC to 
hell.  You'll notice that in listener.ora there needs to be a line "SID_NAME=" and in 
TNSNAMES.ora there is a "Connect_data = (sid = " as well.  Now a particular database 
instance/version can only have one extproc_connect_data entry, but with multiple 
versions each has it's own, and sure enough each has to have a particular sid 
otherwise they mess each other up.

BTW: Your description of the process is dead on, with one exception.  An 
instance of extproc is connected to one and only one session in the calling database.  
If two sessions each need to call an external procedure then each gets it's own 
instance of extproc.  Also if you need to update the dll or so file you have to get 
everyone to let go of extproc, namely by disconnecting from the database.  Although 
it's like a database link, closing the links does not release extproc.  Also using TCP 
to connect to extproc is not an Oracle recommended method, opens a door to hackers.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: seperate external procedure listeners for different SIDs

That's pretty much it, at least through Oracle 8.1.7 (what I'm using).  This is mostly 
because Oracle only uses one service name to make the connection, 
EXTPROC_CONNECTION_DATA, and TNSNAMES can only associate that service name with one 
IPC key.  Therefore you can only talk to the one listener that is listening for that 
IPC key.  You could start a second listener listening on another IPC key, but only the 
one referenced in TNSNAMES would ever be used.

Which brings to mind a possible work around - if you can get a different database to 
use a different TNSNAMES.ORA file to resolve service names, you might get this to work 
- maybe by setting the TNS_ADMIN environment variable to a different value before you 
bring up the database.  I haven't tried it, though, so no promises.

If you are using a later version of Oracle, however, read the docs carefully.  Seems 
to me that if they have made it possible to connect to remote external procedure 
handlers through TCP/IP - 8.1.7 REQUIRES a local handler using IPC, they may have made 
a way to control the service name it uses so you can choose which external procedure 
handler to use.  The key docs for external procedures are the Application Programmers 
Guide, the PL/SQL Guide, and the Server Guide, plus a few Technical Notes in MetaLink.

-Original Message-
Sent: Thursday, January 08, 2004 9:39 AM
To: Multiple recipients of list ORACLE-L


Hmm...so if for some reason I needed two external procedure listeners to
run, (because, for example I wanted them to run as 2 different application
users rather than oracle or use 2 different sets of .so files), I could not
do it?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: seperate external procedure listeners for different SIDs

2004-01-08 Thread John Dunn

Hmm...so if for some reason I needed two external procedure listeners to
run, (because, for example I wanted them to run as 2 different application
users rather than oracle or use 2 different sets of .so files), I could not
do it?



-Original Message-
Sent: 08 January 2004 14:20
To: Multiple recipients of list ORACLE-L


You're right - there isn't any tie between external procedures and a
particular SID in the listener.  Here's what I am guessing (educated guess
based on the docs) happens:
1.  Nothing supports external procedures until one is called.
2.  When a session in a particular database instance calls an external
procedure, it works very much like accessing a remote database through a
database link.  A call goes out on Net for a connection to the service named
EXTPROC_CONNECTION_DATA.  The naming method (TNSNAMES.ORA in my case) has
associated this service with a protocol, usually IPC (I understand a later
version of Oracle can hook to remote external procedures with TCP/IP).  IPC
identifies the service by a key.
3.  The listener process that is listening for IPC connections with that key
name, in this case your separate listener, LISTENER_EXT, looks at the
program associated with that key, which is extproc.
Since there is no instance of extproc running, the listener starts one, and
hands off the Net connection to it, just as it might hand off a connection
to a remote database.
4.  Your session now tells extproc through its Net connection which .DLL or
.so it wants loaded, and which function to call, passing the proper
parameters.  Extproc passes back output parameters and function return
values.
5.  The connection between the database session and the extproc program is
maintained for the duration of the session, so that if the session wants to
run another external procedure (or the same one again), it does not have to
go through the listener again.  This is similar to the way a connection
through a database link is maintained.
6.  I don't know whether another session that runs an external procedure
will get its own copy of extproc, or if it just gets its own connection to
the currently running copy of extproc.  In either case, the connection is
between extproc and a single database session on a single database instance.
There is no need to associate the listener with a particular SID, anymore
than there is a need to associate a database listener with a particular
database client.

-Original Message-
Sent: Thursday, January 08, 2004 7:20 AM
To: Multiple recipients of list ORACLE-L


I have created a seperate listener for external procedures for one of my
SIDs. 

My question is, if I want to do the same for another SID in the same
instance, what ensures that each external procedure listener will only
handle requests for its own SID?.

Here is my listener.ora for the first SID. The external procedure listener
is called listener_ext. I don't see anything in the configuration for the
external procedure listener that ties it to a SID.

Platform is Oracle 9.2 on AIX 5.2


# LISTENER.ORA Network Configuration File:
/usr/u01/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 1521))
  )
)
(DESCRIPTION =
  (PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
  )
  (ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 2481))
)
  )

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
  (GLOBAL_DBNAME = FW9i.DEMO)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
  (SID_NAME = FW9i)
)
  )


LISTENER_EXT =
(ADDRESS_LIST = 
 (ADDRESS = (PROTOCOL=IPC) 
 (key = extproc_key)
)
   )

SID_LIST_LISTENER_EXT = 
   (SID_LIST = 
(SID_DESC = 
 (SID_NAME = extproc_agent)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
   (PROGRAM = extproc)
   (ENV = "EXTPROC_DLLS=ANY")
 )
)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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: John Flack
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California

RE: seperate external procedure listeners for different SIDs

You're right - there isn't any tie between external procedures and a particular SID in 
the listener.  Here's what I am guessing (educated guess based on the docs) happens:
1.  Nothing supports external procedures until one is called.
2.  When a session in a particular database instance calls an external procedure, it 
works very much like accessing a remote database through a database link.  A call goes 
out on Net for a connection to the service named EXTPROC_CONNECTION_DATA.  The naming 
method (TNSNAMES.ORA in my case) has associated this service with a protocol, usually 
IPC (I understand a later version of Oracle can hook to remote external procedures 
with TCP/IP).  IPC identifies the service by a key.
3.  The listener process that is listening for IPC connections with that key name, in 
this case your separate listener, LISTENER_EXT, looks at the program associated with 
that key, which is extproc.
Since there is no instance of extproc running, the listener starts one, and hands off 
the Net connection to it, just as it might hand off a connection to a remote database.
4.  Your session now tells extproc through its Net connection which .DLL or .so it 
wants loaded, and which function to call, passing the proper parameters.  Extproc 
passes back output parameters and function return values.
5.  The connection between the database session and the extproc program is maintained 
for the duration of the session, so that if the session wants to run another external 
procedure (or the same one again), it does not have to go through the listener again.  
This is similar to the way a connection through a database link is maintained.
6.  I don't know whether another session that runs an external procedure will get its 
own copy of extproc, or if it just gets its own connection to the currently running 
copy of extproc.  In either case, the connection is between extproc and a single 
database session on a single database instance.  There is no need to associate the 
listener with a particular SID, anymore than there is a need to associate a database 
listener with a particular database client.

-Original Message-
Sent: Thursday, January 08, 2004 7:20 AM
To: Multiple recipients of list ORACLE-L


I have created a seperate listener for external procedures for one of my
SIDs. 

My question is, if I want to do the same for another SID in the same
instance, what ensures that each external procedure listener will only
handle requests for its own SID?.

Here is my listener.ora for the first SID. The external procedure listener
is called listener_ext. I don't see anything in the configuration for the
external procedure listener that ties it to a SID.

Platform is Oracle 9.2 on AIX 5.2


# LISTENER.ORA Network Configuration File:
/usr/u01/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 1521))
  )
)
(DESCRIPTION =
  (PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
  )
  (ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 2481))
)
  )

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
  (GLOBAL_DBNAME = FW9i.DEMO)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
  (SID_NAME = FW9i)
)
  )


LISTENER_EXT =
(ADDRESS_LIST = 
 (ADDRESS = (PROTOCOL=IPC) 
 (key = extproc_key)
)
   )

SID_LIST_LISTENER_EXT = 
   (SID_LIST = 
(SID_DESC = 
 (SID_NAME = extproc_agent)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
   (PROGRAM = extproc)
   (ENV = "EXTPROC_DLLS=ANY")
 )
)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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: John Flack
  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).  Y

RE: DBA tasks

I had one like that:
I created a database under our 7.1.6, because "We need an Oracle database for 
development." "When?" "Yesterday!"
I gave them usernames and passwords and made sure that TNSNAMES was configured.
In monitoring a few weeks later, I noticed no activity, so I asked them, "That 
database you wanted right away isn't being used, is there a problem."  "No problem, we 
just had a delay, we're going to be starting any day now."
Two years later, I was upgrading some databases to 8.  The database had still not been 
used, other than a few objects created.  "Should I upgrade this database?", I asked.  
"Oh no, don't bother, we're not using it."  "Can I delete it?" "No, please keep it 
around.  We're going to need it anytime now."
A year later, I backed it up with export to tape, in case I ever wanted it back, and 
deleted it (along with Oracle home for 7.1.6).  Five years later, I don't think anyone 
ever noticed that it was gone.  The tape is in a drawer somewhere.
Most troublefree database I've ever had.

-Original Message-
Sent: Wednesday, January 07, 2004 4:15 PM
To: Multiple recipients of list ORACLE-L


Patrice --

Come on!  If you don't install any software, you don't have to fight all
those fires.  Oh, and if you have no activity on the database, that sucker
just flies.  Combine the two, and you don't have to work 45 hours a week.

HTH,
Bambi.

-
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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).

seperate external procedure listeners for different SIDs

2004-01-08 Thread John Dunn

I have created a seperate listener for external procedures for one of my
SIDs. 

My question is, if I want to do the same for another SID in the same
instance, what ensures that each external procedure listener will only
handle requests for its own SID?.

Here is my listener.ora for the first SID. The external procedure listener
is called listener_ext. I don't see anything in the configuration for the
external procedure listener that ties it to a SID.

Platform is Oracle 9.2 on AIX 5.2


# LISTENER.ORA Network Configuration File:
/usr/u01/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 1521))
  )
)
(DESCRIPTION =
  (PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
  )
  (ADDRESS = (PROTOCOL = TCP)(HOST = DEMO)(PORT = 2481))
)
  )

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
  (GLOBAL_DBNAME = FW9i.DEMO)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
  (SID_NAME = FW9i)
)
  )


LISTENER_EXT =
(ADDRESS_LIST = 
 (ADDRESS = (PROTOCOL=IPC) 
 (key = extproc_key)
)
   )

SID_LIST_LISTENER_EXT = 
   (SID_LIST = 
(SID_DESC = 
 (SID_NAME = extproc_agent)
  (ORACLE_HOME = /u03/app/oracle/product/9.2.0.1.0)
   (PROGRAM = extproc)
   (ENV = "EXTPROC_DLLS=ANY")
 )
)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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).

A free sql analysis tool

2004-01-08 Thread Hatzistavrou John









Dear All,

 

I have found this Perl script that makes an analysis
of 10046 SQL trace

 

http://brainshed.com/software/

 

Kind Regards,

 

 

Hatzistavrou Yannis

RE: Suggestions Needed: Latch free - library cache

2004-01-07 Thread John Kanagaraj

Title: Message

Tracy,
This
is a very cursory answer... If this is the 'library cache' latch, then
there *should* be a number of entries in V$LATCH_CHILDREN. Are the figures
therein skewed in some way among the child latches? If so, you *may* have
an issue where a particular application or SQL is not using bind variables. A
look at V$SQL will reveal a lot. I would look at applications without bind
variables. Also, you may look for any Object stats (ANALZYE) that has spilled
over and is currently running during the daytime (overly zealous DBA starts off
ANALYZE because 'performance is bad'!) - this will invalidate SQLs resulting in
parsing (and thus latching).
Hth,

John KanagarajDB Soft IncPhone: 408-970-7002
(W)Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com** The opinions and
facts contained in this message are entirely mine and do not reflect those of my
employer or customers **

-Original Message-From: Tracy Rahmlow
[mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004
2:40 PMTo: Multiple recipients of list ORACLE-LSubject:
Suggestions Needed: Latch free - library cacheWe have experienced intermittent problems (slow
response time) with our oltp database today. There appears to be a large
number of latch free events and the p2 parameter is indicating an issue with
the library cache. Any thoughts on where to go next?
American Express made the followingannotations on 01/07/2004 03:36:25
PM--**"This
message and any attachments are solely for the intended recipient and may
contain confidential or privileged information. If you are not the intended
recipient, any disclosure, copying, use, or distribution of the information
included in this message and any attachments is prohibited. If you have
received this communication in error, please notify us by reply e-mail and
immediately and permanently delete this message and any attachments. Thank
you."**==

RE: ora1652 question...

2004-01-06 Thread John Kanagaraj

Chris,

There are two options:

1. Easier, but requires a bounce : Add the following event into init.ora

event="1652 trace name processstate level 10"

This will dump the processstate for processing that encounter an ORA-01652.

And you can even add the following to capture 1555 and 4031 errors

event="1555 trace name errorstack level 3"
event="4031 trace name errorstack level 3"

**BUT**, keep _all_ 'event' lines together in the file (just as with
utl_file_dir entries)

2. Harder (requires coding/testing), but better control and options:

Create a System-level ON SERVERERROR trigger and check for 1652 (among
others) and record all the details into either alert.log (via
dbms_system.ksdwrt call), database table, utl_file etc.

Hth,
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Chris Stephens [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, January 06, 2004 2:10 PM
>To: Multiple recipients of list ORACLE-L
>Subject: ora1652 question...
>
>
>Is there an event to set where I can identify any sql that 
>receives a 1652
>error message?
>
>There is some process running each night in a reporting 
>database that has
>been generating this error for the past week.  I figured someone would
>complain.  That didn't happen so I went and asked the 
>reporting people if
>any of the reports were blowing up.  They said no.  I just set 
>up statspack
>and will run that every 10 minutes tonight.  I also have a 
>query that will
>capture the session info on sessions currently sorting that I 
>will run every
>10 minutes.  Neither of the techniques are very direct.  I 
>would imagine
>there is an event to set so that I can generate a trace file.  
>Any other
>suggestions of nailing this down would be appreciated.
>
>..and so I don't have to ask about events anymore...where do I 
>find what
>event means what?
>
>Thanks,
>Chris 
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Chris Stephens
>  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: John Kanagaraj
  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: oracle client on PC's

2004-01-06 Thread John Blake




we have a standard desktop or laptop image 
which we install the client  and create a package containing the 
change, we then push to the pc's that require the oracle client.
 
We use onames with a primary and secondary 
name server.

  -Original 
  Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Jeffrey 
  BeckstromSent: Tuesday, January 06, 2004 1:40 PMTo: 
  Multiple recipients of list ORACLE-LSubject: oracle client on 
  PC's
  Rather than installing the Oracle client on every client PC, we have 
  been:- installing client on 1 PC
  - copying directory to a network server
  - extract the registry for "oracle" key
  - fix registry that was extracted to reference the network drive
  - load registry on client PCs
  - add the network pc as a search drive to the client pc.
   
  We are now experiencing problems over the WAN and looking at ways to 
  eliminate the Oracle dll overhead.  Short of installing Oracle on every 
  client PC, what are our options?
   
   
   
  Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
  Transit Authority1240 W. 6th StreetCleveland, Ohio 
44113

RE: undotbs01.dbf just keeps growing

2004-01-06 Thread John Dunn

Daniel

How can i use the v$undostat.maxquerylen value to configuure the undo
tablespace?

Also, how can I tell what the longest transaction is actually doing?, i.e
what sql is being run?

John

-Original Message-
Sent: 05 January 2004 17:54
To: Multiple recipients of list ORACLE-L

Jared,

It sure is nice to be missed. I'll make sure my secretary calls you about my
future vacation plans...:)

You've nailed the problem. Autoextend, automatic undo and high undo
retention is
a recipe for high disk usage. The aum algorithm is such that preference is
given
to extending over reuse (especially since expire time propogation is a
problem).

In order to find the length of the longest transaction, reference the
v$undostat.maxquerylen value. Beware as there are known bugs with this view,
so
examine the output carefully to make sure it makes sense.

Daniel Fink

Jared Still wrote:

> The data file(s) for your undo tablespace is likely set
> as autoextend with an unlimited size.
>
> Run the attached script to check it.
>
> If so, you can use this to put a limit on it:
>
> alter database datafile '' autoextend on next 200m
> maxsize 2000m;
>
> Adjust the numbers for your system.
>
> You should probably investigate why it continues to grow so large.
>
> I haven't yet converted our production databases to UNDO, having
> only recently migrated to 9i, so I don't have any useful advice
> past this.
>
> There are others that will be able to offer more for this. ( Dan
> Fink, where are you?  This might even get Kirti to take a break
> from his book for a few minutes )
>
> HTH
>
> Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  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: John Dunn
  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: freebie. Summarize Oracle Listener logs

2004-01-05 Thread Hallas, John, Tech Dev

Sorry  - meant to send just to Steve

Please ignore



-Original Message-
Hallas, John, Tech Dev
Sent: 05 January 2004 11:19
To: Multiple recipients of list ORACLE-L


Steve,
I have an awk script which does something similar - see code and example below
 
However I can put your file on my site where I have already stored your Perl script 
for getting the DDL out of an export file   ( www.hcresources.co.uk) if you wish.
 
Cheers
 
John
 
Service :Host :User :tcp:ip address

===

WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13

WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11

WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734

 

echo "Service :Host :User :tcp:ip address"

echo "==="

grep CONNECT b2tperf.log |\

awk -F= '{print $3 ":1:" $6 ":2:" $7 ":3:" $9 ":4:" $10 ":5:" $11}' |\

sed 's/).*:1:/:/' | \

sed 's/).*:2:/:/' | \

sed 's/).*:3:/:/' | \

sed 's/).*:4:/:/' | \

sed 's/).*:5:/:/' | \

sed 's/).*$//' | \

awk -F: '{printf("%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n", \

$1,$2,$3,$4,$5)}' | sort > /tmp/j.lis

grep -v PROGRAM /tmp/j.lis > /tmp/j1.lis

cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\

END{for (i = 1; i <= count; i++)print lines[i],"\t"data[lines[i]]}'\

|sort -n +4

rm /tmp/j.lis

rm /tmp/j1.lis

 

 

 

-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L


I wanted to summarize our listener logs to see who's connecting with what tools. I 
checked google and didn't see anything, so I decided to write one. It may be useful to 
somebody else.
 
It summarizes the lines of a listener log. the output is similar to 
 
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
   
PA1DBO   TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO   TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE   600
PW2DBO   TSOORA56 DLLHOST.EXE   362
ITMDBO   TSOORA52 SQLPLUS.EXE   166
SYSTEM   TSOORA56 MAPS_BIZ   88
PW3DBO   TSOORA57 TD2000.EXE 72
ASPNET   TSOORA56 ASPNET_WP.EXE  57
ORACLE   __JDBC__41
ARCDBO   SATSAP09 SQLPLUS.EXE12
2KNUPPS  SKNUPP-1 TOAD.EXE7
PERRYSM  SAIT02547SQLPLUSW.EXE6
2RILEYJ  GLEFIO01210  TOAD.EXE4
2MASHUA  GLEFIO01262  TOAD.EXE3
PA1DBO   TSOORA49 DMFILESCAN.EXE  3
PA1DBO   TSOORA49 DMCLEAN.EXE 3
LDQDBO   TSOORA24 JREW.EXE2
2SHURTN  GLEFIO01261  TOAD.EXE1
PW3DBO   TSOORA57 DBDBOIN.EXE 1
PA1DBO   TSOORA49 SQLPLUSW.EXE1
ODDDBO   TSOORD08 EXP.EXE 1
2KNUPPS  SKNUPP-1 SQLPLUSW.EXE1
PW2DBO   TSOORA56 SQLPLUSW.EXE1

 
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made 
- only that it was attempted. seperating the failed connections may be a future 
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in 
yet. 
It will process 100 meg of logs in few minutes. 
It was written for windows only. sorry, I work in a MicroSlop env... 
 
I have a  cmd file that I use to run it so it can process our current logs or older 
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work  <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
otherwise use  [EMAIL PROTECTED] and I'll get to it when I get home.
 
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
 
Steve
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hallas, John, Tech Dev
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and

RE: freebie. Summarize Oracle Listener logs

2004-01-05 Thread Hallas, John, Tech Dev

Steve,
I have an awk script which does something similar - see code and example below
 
However I can put your file on my site where I have already stored your Perl script 
for getting the DDL out of an export file   ( www.hcresources.co.uk) if you wish.
 
Cheers
 
John
 
Service :Host :User :tcp:ip address

===

WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13

WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11

WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734

 

echo "Service :Host :User :tcp:ip address"

echo "==="

grep CONNECT b2tperf.log |\

awk -F= '{print $3 ":1:" $6 ":2:" $7 ":3:" $9 ":4:" $10 ":5:" $11}' |\

sed 's/).*:1:/:/' | \

sed 's/).*:2:/:/' | \

sed 's/).*:3:/:/' | \

sed 's/).*:4:/:/' | \

sed 's/).*:5:/:/' | \

sed 's/).*$//' | \

awk -F: '{printf("%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n", \

$1,$2,$3,$4,$5)}' | sort > /tmp/j.lis

grep -v PROGRAM /tmp/j.lis > /tmp/j1.lis

cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\

END{for (i = 1; i <= count; i++)print lines[i],"\t"data[lines[i]]}'\

|sort -n +4

rm /tmp/j.lis

rm /tmp/j1.lis

 

 

 

-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L


I wanted to summarize our listener logs to see who's connecting with what tools. I 
checked google and didn't see anything, so I decided to write one. It may be useful to 
somebody else.
 
It summarizes the lines of a listener log. the output is similar to 
 
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
   
PA1DBO   TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO   TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE   600
PW2DBO   TSOORA56 DLLHOST.EXE   362
ITMDBO   TSOORA52 SQLPLUS.EXE   166
SYSTEM   TSOORA56 MAPS_BIZ   88
PW3DBO   TSOORA57 TD2000.EXE 72
ASPNET   TSOORA56 ASPNET_WP.EXE  57
ORACLE   __JDBC__41
ARCDBO   SATSAP09 SQLPLUS.EXE12
2KNUPPS  SKNUPP-1 TOAD.EXE7
PERRYSM  SAIT02547SQLPLUSW.EXE6
2RILEYJ  GLEFIO01210  TOAD.EXE4
2MASHUA  GLEFIO01262  TOAD.EXE3
PA1DBO   TSOORA49 DMFILESCAN.EXE  3
PA1DBO   TSOORA49 DMCLEAN.EXE 3
LDQDBO   TSOORA24 JREW.EXE2
2SHURTN  GLEFIO01261  TOAD.EXE1
PW3DBO   TSOORA57 DBDBOIN.EXE 1
PA1DBO   TSOORA49 SQLPLUSW.EXE1
ODDDBO   TSOORD08 EXP.EXE 1
2KNUPPS  SKNUPP-1 SQLPLUSW.EXE1
PW2DBO   TSOORA56 SQLPLUSW.EXE1

 
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made 
- only that it was attempted. seperating the failed connections may be a future 
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in 
yet. 
It will process 100 meg of logs in few minutes. 
It was written for windows only. sorry, I work in a MicroSlop env... 
 
I have a  cmd file that I use to run it so it can process our current logs or older 
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work  <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
otherwise use  [EMAIL PROTECTED] and I'll get to it when I get home.
 
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
 
Steve
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hallas, John, Tech Dev
  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 PROTE

RE: stress testing

2004-01-02 Thread John Kanagaraj

Henry,

Sar is a better tool than vmstat/iostat as it collects a broad range of
information. Specifically, sar -q should show up CPU queueing and swapping,
and sar -v will show up file/process table overflow issues that may occur
during stress testing. IMHO, sar is quite underutilized ( had a paper on
this last IOUG, but couldn't go and present it :(

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Poras, Henry R. [mailto:[EMAIL PROTECTED] 
>Sent: Friday, January 02, 2004 10:54 AM
>To: Multiple recipients of list ORACLE-L
>Subject: stress testing
>
>
>We are planning on running some stress tests on a 
>PeopleSoft/Oracle/Solaris
>system starting next week (using LoadRunner). I have never 
>gone through a
>formalized stress test before (most of my stress is brought 
>about informally).
>So far I am planning to gather statspack information, and 
>periodically get
>vmstat from the OS. Is there anything else that I should 
>collect? Thanks for the
>help.
>
>Henry
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Poras, Henry R.
>  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: John Kanagaraj
  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: Hardware for RAC?

2003-12-31 Thread John Kanagaraj

Title: Message



Chinedu/Chandra,
 
Although I agree that you can use different OEM vendors as long as the OS 
is the same, be aware of the increased chances for some cross-vendor problems. 
You necessarily don't want finger-pointing between vendors when problems occur 
(they will!) in a complex RAC environment.
 
Chinedu, before you launch into using/testing RAC, you should (or ITS 
should) ask itself the question 'Do I need RAC?'. Mogens Nørgaard, a 
guru from this list has an excellent article in IOUG's SELECT magazine on 
this topic. If you don't have IOUG membership, maybe Shell ITS can get one. 
Alternately, you may ask Mogens for a copy.
 

John KanagarajDB Soft IncPhone: 408-970-7002 
(W)Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com** The opinions and 
facts contained in this message are entirely mine and do not reflect those of my 
employer or customers **

  
  -Original Message-From: Chandra Pabba 
  [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 31, 
  2003 7:04 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Hardware for RAC?
  
  
Yes, you can use nodes from 
different OEM vendors for RAC. 
You will for sure need a private 
network or interconnect between the nodes for maintaining the 
heart-beat. 
   
  HTH
  Chandra
   
   
   
   
  -Original 
  Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ikediugwu, Chinedu 
  SITI-ITPSIESent: Wednesday, 
  December 31, 2003 7:00 AMTo: 
  Multiple recipients of list ORACLE-LSubject: Hardware for 
  RAC?
   
  
  Hello,
  
   
  
  I have 
  been asked to setup a test environment for RAC. However, I don't 
  know so much about hardware.  
  
   
  
  My 
  questions may appear dumb, please take no offence, I'm a beginning DBA and I 
  really want to know. 
  
   
  
  1. Can I 
  use 2 nodes of different makes (one IBM and one Compaq), but using same 
  Oracle and OS versions (9.2.0.4 & Linux 
  respectively)?
  
  2. Can I 
  set RAC up, using only the public network?
  
   
  
  Thanks 
  in advance
  
   
  
  Regards
  
  Chinedu

RE: undotbs01.dbf just keeps growing

2003-12-31 Thread John Dunn

Thanks Jared

<>

Whats the best way to go about identifying any large transactions?

John

-Original Message-
Sent: 31 December 2003 04:34
To: Multiple recipients of list ORACLE-L

The data file(s) for your undo tablespace is likely set
as autoextend with an unlimited size.

Run the attached script to check it.

If so, you can use this to put a limit on it:

alter database datafile '' autoextend on next 200m
maxsize 2000m;

Adjust the numbers for your system.

You should probably investigate why it continues to grow so large.

I haven't yet converted our production databases to UNDO, having
only recently migrated to 9i, so I don't have any useful advice
past this.  

There are others that will be able to offer more for this. ( Dan 
Fink, where are you?  This might even get Kirti to take a break
from his book for a few minutes )

HTH

Jared

On Tue, 2003-12-30 at 03:29, John Dunn wrote:
> I have recently installed Standard Engine 9.2 on AIX 5.2 and notice that
the
> undotbs01.dbf file just keeps on growing. It is now over 1 GB.
> 
> What could be the reason for this? Can I limit it's size and would this
> cause a problem too?
> 
> John
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: John Dunn
>   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: John Dunn
  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).

SQL*net message from client severly impact the Parse call of an insert statement

2003-12-31 Thread Hatzistavrou John









Dear All,

 

I am faced with the following situation.

Oracle 8.1.7.4. 64 bit , Solaris 8

 

There is a loader java process that when is executed
against a test database(dwdsa)the response time is as expected to be. However
when it is executed against the production instance (dwods) it is 2,5 to 3
times slower.

I have traced the session on both occasions and reading
the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from
client is very high for the PARSE call of an INSERT statement, whereas for the
test instance there is no delay.

I cannot however explain what might be the cause of
this. Can somebody sched some light into this problem.

Attached please find the SQL 10046 trace with level 8

 

a)  
Production trace (parser_dwods.zip)

b)  
Test trace parser_dwdsa.zip

 

 

Kind Regards,

 

 

Hatzistavrou Yannis

 








parser_dwods.zip
Description: Zip compressed data
The previous attachment was filtered out by the ListGuru mailing
software at fatcity.com because binary attachments are not appropriate
for mailing lists.  If you want a copy of the attachment which was
removed, contact the sender directly and ask for it to be sent to
you by private E-mail.

This warning is inserted into all messages containing binary
attachments which have been removed by ListGuru.  If you have questions
about this message, contact [EMAIL PROTECTED] for clarification.


parser_dwdsa.zip
Description: Zip compressed data

RE: Apps 11.5.9 Disater Recovery Site

2003-12-30 Thread John Kanagaraj

Ron,

I entirely agree with Stephen. Apps is a different beast when it comes to
such stuff. Overall, the issues with incomplete recovery (i.e. recovery to a
previous point in time), especially in a complex, integrated ERP system such
as Oracle Apps 11i are many. You should only restore to a point in time as a
very last resort. Depending on modules and interfaces that were active at
the time of recovery, you would probably have system generated numbers (PO's
,Invoices, etc) that have been created and _already_ sent to customers and
suppliers. As well, many ERP systems send out (and receive) EDI data from
other external systems. Coordination of this could be a logistical nightmare
when you perform an incomplete recovery. This needs to be understood and
documented, otherwise you might end up having to "fix" complex data issues.
The key words are "Documentation/Understanding of Processes" and "Change
Control"

Happy New Year all!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Karniotis, Stephen [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, December 30, 2003 4:09 PM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Apps 11.5.9 Disater Recovery Site
>
>
>Ron:
>
>  I would definitely not suggest ignoring adpatch.  Should you 
>actually need
>to use this DR site and something went wrong, Oracle may not 
>offer you any
>help.  If rsync can offer you 100% replication than I would try it and
>validate it.  However, applications is a much different beast 
>than just the
>database.  File system names, node names, database names, tns names,
>userids, passwords, etc. are embedded within the code and are 
>very difficult
>to change.  It would be best to take a complete image copy of 
>the database
>environment, the apps environment and then use adpatch and 
>adadmin to verify
>the environment once completed.
>
>Thank You
>
>Stephen P. Karniotis
>Technical Alliance Manager
>Compuware Corporation
>Direct:(313) 227-4350
>Mobile:(248) 408-2918
>Email: [EMAIL PROTECTED] 
>Web:   www.compuware.com 
>
> -Original Message-
>Ron Thomas
>Sent:  Tuesday, December 30, 2003 6:35 PM
>To:Multiple recipients of list ORACLE-L
>Subject:   Apps 11.5.9 Disater Recovery Site
>
>I've been charged with bringing up a disaster recovery site, 
>so time to hit
>the books again as a lot
>has changed since the last time I did this. Looking for resource
>recommendations (FM to read, white
>papers, etc).
>
>Sticky part of this is it is an Applications 11.5.9 installation.  The
>database end of it should not
>be too difficult (8.1.7.4, soon to be 9.2.0.4), but the 
>applications file
>system is modified by the
>adpatch utility which adpatch requires a database connection 
>to function.  I
>can think of 2 ways to
>get around this requirement.
>1. set the two_task to point to a live test system, and run 
>adpatch force
>using the c and g drivers.
>The d driver would not need to be run since the changes will 
>come over via
>the archive logs.
>2. ignore adpatch utility completely and use rsync.
>
>Suggestion, comments?
>
>Thanks,.
>Ron Thomas
>Hypercom, Inc
>[EMAIL PROTECTED]
>Each new user of a new system uncovers a new class of bugs. -- 
>Kernighan
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Ron Thomas
>  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).
>
>
>
>The contents of this e-mail are intended for the named 
>addressee only. It
>contains information that may be confidential. Unless you are the named
>addressee or an authorized designee, you may not copy or use 
>it, or disclose
>it to anyone else. If you received it in error please notify 
>us immediately
>and then destroy it. 
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author:

RE: Should we stop analyzing?

2003-12-30 Thread John Kanagaraj

I am surprised no one raised the issue of invalidations in the shared pool
caused by Stats gathering, and the parsing/reloading load that is caused
_after_ the extra I/O and changed plans due to ANALYZEs 

I have this 250Gb Apps database that is analyzed once a month and we have
not suffered due to incorrect or stale statistics. Projects in the new year
include revisting the Stats gathering schedules of all our 90+ databases,
some of which are analyzed daily :(

Have a happy, blessed new year all!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Jonathan Lewis [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, December 30, 2003 8:44 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Should we stop analyzing?
>
>
>
>That's (partly) what the 9i  dynamic sampling
>feature is for.  And such tables are, of course,
>going to be GTTs.
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>  The educated person is not the person
>  who can answer the questions, but the
>  person who can question the answers -- T. Schick Jr
>
>
>One-day tutorials:
>http://www.jlcomp.demon.co.uk/tutorial.html
>
>
>Three-day seminar:
>see http://www.jlcomp.demon.co.uk/seminar.html
>UK___November
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>- Original Message - 
>To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>Sent: Tuesday, December 30, 2003 4:09 PM
>
>
>I'll see your 'analyzed every 4 hours' and raise you one. We have some
>tables that are analyzed every time they are used! They are 
>'work' tables
>that are sometimes empty, very full, or somewhere in between. Running
>something when the statistics say the table is full but 
>actually is empty
>takes a little longer when CBO says use indexes; however, if 
>CBO thinks the
>table is empty and does a FTS when there's actually a million 
>records, well
>let's just say it takes a while. Hints work sometimes; 
>however,  analyzing
>these table after they are populated and letting CBO do it's 
>job usually
>works best.
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Jonathan Lewis
>  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: John Kanagaraj
  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).

undotbs01.dbf just keeps growing

2003-12-30 Thread John Dunn

I have recently installed Standard Engine 9.2 on AIX 5.2 and notice that the
undotbs01.dbf file just keeps on growing. It is now over 1 GB.

What could be the reason for this? Can I limit it's size and would this
cause a problem too?

John

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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 performance problem

2003-12-29 Thread John Kanagaraj

Venu,

You can work out the trace file name for Conc jobs. The OS process for a CM
job is stored in the ORACLE_PROCESS_ID in FND_CONCURRENT_REQUESTS for that
particular REQUEST_ID. You can then use this process number to generate the
trace file in udump (normally
$ORACLE_HOME/admin//udump/**.trc in the case of a UNIX based
11i DB server). Although this would have been just a SQL_TRACE (10046 Level
1), you can *still* run a tkprof on it to determine which SQL consumed the
most time

Hth,
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] 
>Sent: Monday, December 29, 2003 10:15 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: A performance problem
>
>
>John,
>
>I can run this in our development environment and trace the 
>job. But, the data is quite a bit larger in production. I 
>can't really take on a refresh/clone now and the prodcution 
>database is over 600GB
>in size. We do have trace for the job which was available 
>because the program definition for this custom feed job has 
>trace enabled in Apps. That trace file doesn't have any wait 
>event information.
>This job does use db link. We know that for sure. I advised 
>the developer who wrote this custom feed job to tune it but 
>that is never a satisfactory answer for them.
>
>
>Venu Potluri
>
>-Original Message-
>John Kanagaraj
>Sent: Monday, December 29, 2003 12:35 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Venu,
>
>Trying to solve the performance issue with a *single* job with 
>Statspack is
>like searching for a needle in a haystack, especially in an Oracle Apps
>environment. You will need to trace the program *as it runs*, 
>and if you
>cannot do that right now, see if you can clone the database to 
>a test system
>and rerun it again. Btw, was this concurrent job an Oracle 
>standard job or
>was it a custom program? Any recent changes or patches to the 
>environment?
>Note that you *can* set trace (albeit just the plain vanilla 
>level 1) on a
>Concurrent job in 11i... As for the DB Link, can you determine if this
>indeed does use a Dblink or it is from somewhere else... [See 
>the problem
>with Statspack?!]
>
>John Kanagaraj
>DB Soft Inc
>Phone: 408-970-7002 (W)
>
>Grace - Getting something we do NOT deserve
>Mercy - NOT getting something we DO deserve
>Click on 'http://www.needhim.org' for Grace and Mercy that is freely
>available!
>
>** The opinions and facts contained in this message are 
>entirely mine and do
>not reflect those of my employer or customers **
>
>>-Original Message-
>>From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] 
>>Sent: Monday, December 29, 2003 8:44 AM
>>To: Multiple recipients of list ORACLE-L
>>Subject: A performance problem
>>
>>
>>I have a performance issue in our 11.5.5 Oracle Apps 
>>production environment (Oracle 8.1.7.4). A concurrent job that 
>>feeds into another production envrironment (Oracle 9.2) and 
>>runs less than an hour
>>typically suddenly took almost 20 hours to finish. The users 
>>are as expected up in arms calling my head on a platter. I 
>>looked at the statspack report for the database this job ran on.
>>
>>The Top5 Wait events were:
>>
>>Top 5 Wait Events
>>~ 
>>  
>>Wait EventWaits   
>>Time (cs) % Total Wt Time
>>---
>>
>>db file sequential read   15,978,336  
>> 5,809,27757.28
>>SQL*Net message from dblink   3,868   
>>1,960,168 19.33
>>db file scattered read  2,460,279  
>>943,252 9.30
>>control file sequential read 907,148   
>>   300,572  2.96
>>pipe put2,033  
>>208,850 2.06
>>  
>-
>>-> cs - centisecond -  100th of a second
>>-> ms - millisecond - 1000th of a second
>>-> ordered by wait time desc, waits desc (idle events last)
>>
>>   
>>

RE: A performance problem

2003-12-29 Thread John Kanagaraj

Venu,

Trying to solve the performance issue with a *single* job with Statspack is
like searching for a needle in a haystack, especially in an Oracle Apps
environment. You will need to trace the program *as it runs*, and if you
cannot do that right now, see if you can clone the database to a test system
and rerun it again. Btw, was this concurrent job an Oracle standard job or
was it a custom program? Any recent changes or patches to the environment?
Note that you *can* set trace (albeit just the plain vanilla level 1) on a
Concurrent job in 11i... As for the DB Link, can you determine if this
indeed does use a Dblink or it is from somewhere else... [See the problem
with Statspack?!]

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] 
>Sent: Monday, December 29, 2003 8:44 AM
>To: Multiple recipients of list ORACLE-L
>Subject: A performance problem
>
>
>I have a performance issue in our 11.5.5 Oracle Apps 
>production environment (Oracle 8.1.7.4). A concurrent job that 
>feeds into another production envrironment (Oracle 9.2) and 
>runs less than an hour
>typically suddenly took almost 20 hours to finish. The users 
>are as expected up in arms calling my head on a platter. I 
>looked at the statspack report for the database this job ran on.
>
>The Top5 Wait events were:
>
>Top 5 Wait Events
>~ 
>   
>Wait Event Waits   
>Time (cs)  % Total Wt Time
>---
>
>db file sequential read15,978,336  
> 5,809,277 57.28
>SQL*Net message from dblink3,868   
>1,960,168  19.33
>db file scattered read  2,460,279  
>943,252  9.30
>control file sequential read 907,148   
>   300,572   2.96
>pipe put2,033  
>208,850  2.06
>  -
>-> cs - centisecond -  100th of a second
>-> ms - millisecond - 1000th of a second
>-> ordered by wait time desc, waits desc (idle events last)
>
>   
>   Avg
>   
>   Total Waitwait  Waits
>Event  WaitsTimeouts   
>Time (cs)(ms)  /txn
>  -- 
>--- -- -
>db file sequential read15,978,336   0  
>   5,809,277  4970.3
>SQL*Net message from dblink 3,868  0   
>1,960,168   5068   0.2
>db file scattered read 2,460,279 0 
>   943,2524149.4
>control file sequential read   907,1480
>   300,572355.1
>pipe put   2,033   2,032   
> 208,850  1027 0.1
>
>
>
>Breakdown of Wait time
>
>Event  TimePercentage  Avg. 
>Wait   Per Execute Per User Call   Per Transaction 
>db file sequential read5809277 60.16%  
>0.36   0.688.228762.11 
>SQL*Net message from dblink 196016820.30%  506.77  
>   0.232.772956.51 
>db file scattered read 943252  9.77%   
>0.38   0.111.341422.70 
>control file sequential read 3005723.11%   0.33
>   0.040.43453.35 
>pipe put   208850  2.16%   102.73  
>   0.020.30315.01
>
>Here are the top SQL statements ordered by physical reads per 
>execute: (these two happen to belong to this long running job)
>Statement  ExecutesPhysical Reads  
>Reads/Execute  Hashs Value % of Total
>INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL 
>ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) 
>*/SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.G

Slow process

2003-12-24 Thread Hatzistavrou John









Dear All,

Season’s Greetings….

 

I have the following problem:

 

My system is a Oracle EE 8.1.7.4 64bit on Solaris 8 .

The last days the datawarehouse people complain that
their load process which inserts data into tables is half time as fast as it
was two days ago.

 

From v$session_wait I can see that the process is
spending time on SQL*Net message
from client.

The process is running on
server and it is using PROTOCOL=BEQ and not TCP.

 

May somebody help me clear
this mess.

 

Kind Regards,

 

 

Hatzistavrou Yannis

Database Administrator

SchlumbergerSema

Phone ext.  478

Email: [EMAIL PROTECTED]

RE: Who fired the trigger

2003-12-19 Thread John Weatherman

Would this help?

CREATE OR REPLACE TRIGGER [schema].[trigger_name]
BEFORE [CONDITIONS] ON [table_name]
FOR EACH ROW WHEN ( ( USER != '[USERNAME]' )
AND ...


John P Weatherman
Oracle Database Administrator
Replacements, Ltd.



-Original Message-
Sent: Friday, December 19, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L


To All,

I'm feeling in a LAZY mood this afternoon so I'm going to ask the list if 
someone has an answer to this.  Otherwise I guess it will wait till Monday.

We have a before update trigger on a table to prevent assemblies on the line 
from being unscrapped.  But we also have a need o periodically unscrap stuff.  The 
question is can a trigger recognize who fired it & abort if that is a particular user?

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

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

problem with mult datafiles and local mng tblspaces

2003-12-19 Thread John Blake

I have a 9.2.0.3 DB running on AIX 4.3.3
An application running on W2K using SQLLDR to load approx 6g of data
using local managed tablespaces
ie..
CREATE TABLESPACE PARENTDAT
DATAFILE '/vol01/oradata/e450dev/parentdat01.dbf' SIZE 2000M REUSE,
'/vol01/oradata/e450dev/parentdat02.dbf' SIZE 2000M REUSE,
'/vol01/oradata/e450dev/parentdat03.dbf' SIZE 2000M REUSE,
'/vol01/oradata/e450dev/parentdat04.dbf' SIZE 2000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
NOLOGGING
ONLINE;

It was taking 8 hours to load this data
1) copied the data to the AIX box and ran SQLLDR local to the DB and
received similar results
2) changed the tablespace to contain 1 8g datafile
3) ran SQLLDR from the W2K machine ..completed in 30minutes

Also
ran the same test on AIX 5.1 running oracle 9.2.0.3 and obtained the same
results.


I really would like to be able to create the tablespaces with multiple data
files but it seems whenever I add a datafile the
performance of the load degrades.

Any ideas??

Thanks in advance
John

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

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

RE: How to refresh

2003-12-16 Thread John Flack

The methodology we developed for one client goes like this:
There are three databases - dev, test, and prod.
dev starts as a clone of prod, and fixes, changes and enhancements are developed and 
unit tested there.  One of the REQUIRED products of development is a script that will 
upgrade prod to the new version.
When we are ready for complete testing of a new version of the entire application, 
test is recreated as a clone of prod.
We run the upgrade script against test.  If there are any problems, errors, missing 
stored procedures, or other missing or incorrect versions of database objects after 
running this script, the script must be corrected, and we start over with a fresh 
clone of prod.  By the way, VERY IMPORTANT: Data changes, such as new, updated or 
deleted rows in code or control tables are part of the upgrade, not just DDL changes.
Once the upgrade script has been run, test is a version beyond prod, and can be tested 
thoroughly.  If errors are found they are corrected and unit tested in DEV, not test, 
and then put into a corrected upgrade script, we may then correct test from the 
script, but testing is NOT complete until test is recreated as a clone of prod yet one 
more time, and upgraded with the script, and run through a battery of tests again.
By the time we finish testing, all we should have to do is run the upgrade script 
against prod and bring prod live in the new version.  Then we'll often recreate dev as 
a clone of prod.

-Original Message-
Sent: Tuesday, December 16, 2003 9:55 AM
To: Multiple recipients of list ORACLE-L


I need to do a refresh of a test database using production data.  We use
import for this.  In the past we have always dropped the schemas (4 or
5), recreated the schemas, and then did a full import with ignore=n.
This time the user does not want to lose any of the new functions and
procedures that are in test, but not in prod.  Instead, they would like
to just refresh the table data.  Last time we tried this we had all
kinds of trouble with functions, triggers, constraints, etc... and ended
up doing a full import.

Is there a tried and true way to just refresh the table data without
losing anything else and without having all the problems with triggers
and constraints?

Thanks!
Ron 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: 8i pl/sql question

2003-12-16 Thread John Flack

Does myFunction1 often get called with the same arguments?  In your example, the 
second argument is never repeated, but in the real thing, would the same second 
argument be likely to repeat?  If myFunction1 gets the same arguments, will it always 
return the same value?  If so, then it is a deterministic function, and you can 
declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. 
 This way the optimiser will know not to recalculate the function if it is called 
again with the same arguments, but will reuse the value it calculated before.

-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L


can you return multiple values from a modified version of myfunction(1) ?? If so, then 
you can replace multiple calls by only one. And no, bulk binds is only within 
dml/select statements.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same package 
and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using "bulk bind"?
I am brain-dead now and can not seem to find if it can be done and/or how it can be 
done.

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: automate backup and export

2003-12-13 Thread John

Any reference concerning the rman?

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, December 12, 2003 10:19 PM

> Yes. It's called "RMAN".
> On 12/12/2003 02:04:46 PM, John wrote:
> > Do you know any script for doing export and backup of Oracle 9i R2
server?
> >
> > My oracle is on a SuSE linux.
>
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John
  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).

automate backup and export

2003-12-12 Thread John




Do you know any script for doing export and backup of Oracle 9i R2 
server?My oracle is on a SuSE linux.

IT at Walmart and Kmart - keeping it On-Topic

2003-12-12 Thread John Kanagaraj

Just trying to keep this On-topic! See extract of Wal-Mart's IT approach vs
K-Mart 

http://searchcio.techtarget.com/originalContent/0,289142,sid19_gci938869,00.
html?track=NL-35

Wal-Mart's "step change" approach to IT investment during the 1990s is a
great example. First, the company installed software to manage the flow and
storage of products through its far-flung network of suppliers, warehouses
and distribution centers. Once it had automated product flow, it focused on
using IT to coordinate its operations more tightly with those of its
suppliers, leveraging its greater efficiency. With that smoother
coordination, Wal-Mart could invest effectively in technology to plan the
mix and replenishment of its goods. Finally, after integrating all these
capabilities, the company built a data warehouse that uses information
pulled from a range of sources to handle complex queries.

Kmart, by contrast, made a misstep in its IT investments that undermined
their effectiveness. It invested in systems to improve promotions management
before it had installed the supply chain systems necessary to handle
fluctuations in sales volume. As a result, it was unable to capitalize on
the more precisely targeted promotions. Many retail banks also made errors
in sequencing. They invested in popular customer relationship management
systems before they had built repositories of consistent and reliable
customer data. Not surprisingly, the CRM investments fell well short of
expectations.

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] 
>Sent: Friday, December 12, 2003 8:14 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Re[2]: 
>http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT
>
>
>Mladen
>   I'm pretty confident of one thing -- if they weren't 
>selling, Wal-Mart
>would quickly stop selling them at that store. Most people 
>don't think about
>it, but Sam Walton figured out a couple of things early on:
>  1. If you don't have the item on the shelf, people can't buy it.
>  2. Hire a smart computer systems manager.
>  3. Your control of your own data is a competitive weapon.
>The system they created was flexible enough to be expanded to 
>many, many
>stores.
>In his book "Sam Walton: Made in America", Sam lavishes praise on his
>systems people. The Kmart leadership, on the other hand, was 
>often quoted in
>the press about how they were able to reduce their I.T. expense.
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] 
>
>-Original Message-
>Sent: Friday, December 12, 2003 9:54 AM
>To: Multiple recipients of list ORACLE-L
>http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT
>
>
>
>>  
>> Jonathan is correct - WalMart uses Teradata.
>
>And they're selling gallon-sized Vlasic pickles. I always wondered who
>was buying such a monstrosity. It's a bi-annual pickles supply 
>in a single
>package.
>
>Mladen Gogala
>Oracle DBA
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Mladen Gogala
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: 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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

RE: Performance tuning in complex environment

2003-12-11 Thread John Kanagaraj

Reminds me of the day when a third-party developed app (main batch program)
ran *very* slowly - the user department went out and bought this app and
server on their own without IT's blessing or support (a different story).
Dialogue below:

Third-party Developer (TPD): This same batch program which runs 1hr 30 min
on your box completes under 30 min at our Office with *your* data. We
suggest obtaining an IBM S80 because it is 3 times faster than your current
box

(IBM On-site person: Yes! Yes!!!)

User Department Manager (UDM): Ok - we have a $100,000 budget for this -
lets go out and buy this h/w (We need to go through IT for this purchase)

My Manager, when approached with this issue (MM): I know your TPD has this
view, but can my Sr. DBA look at this problem?

UDM: Ok, but I doubt anything can be done since my TPD says so...

TPD: Hey, your DBA can't mess with our code!

Sr.DBA (Me!): Ok - let's take a look at V$SYSTEM_EVENT, V$SESSION_EVENT and
V$SESSION_WAIT when your program runs...

Me: Hey - what's this session doing with 'SQL*Net Message from dblink'? This
is the top wait (more than 99% of TIME_WAITED in V$SESSION_EVENT)

TPD: Yeah - we have a view that makes a call to your employee table sitting
on your prod box to fetch the Emp name, once for every row in the loop
(1000s of rows, 3300 rows a pop)

Me: Haven't you guys heard of Replicated Tables? 

TPD: What's that? 

Me: (after creating a local copy and replacing the view with an indexed
table) Run your program now...

TPD: Hey - it finished in 5 minutes!!! We don't need to buy any other box!

UDM: I like that!!! Thanks!!

MM: Well done - I knew my DBA could do it!

(IBM On-site person: [EMAIL PROTECTED]@#&*()+__@)

Me: (Hitting myself on the head, and thinking to myself: I should have asked
for just 1% of the $$ that would have otherwise been unnecessarily spent on
that great big H/w box :(

Moral of the story:

(a) Never ass*u*me anything - ask for stats to prove any 'assumption'
(b) Get the right tools to determine the problem area (and use it correctly)

Afterthought (c) - Follow Gary Goodman's principle: Ask for 10% of the $$
allocated for the h/w that would have otherwise been spent on *trying* to
solve the problem by throwing h/w at it! (Cary - correct me if I erred
here!)

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] 
>Sent: Thursday, December 11, 2003 11:29 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Performance tuning in complex environment
>
>
>Not really sure what happened and why we decided to that. I 
>was involved in the beginning of project and remembered that 
>PM was mentioning about talking to another Logician client who 
>were facing same issues. 
>
>-Original Message-
>Jamadagni, Rajendra
>Sent: Thursday, December 11, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Ummm ... what was the problem that prompted you guys to 
>replace citrix servers? 
>
>Raj
>---
>-
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-Original Message-
>[mailto:[EMAIL PROTECTED]
>Sent: Thursday, December 11, 2003 1:35 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello Everyone, I am trying to get some help/suggestions reg. 
>how to troubleshoot performance issues.
>
>Little back ground about our environment. Its third party 
>application (Logician) from GE. There are total 11 databases, 
>all on oracle 8174 H-UX 11i in cluster environment. All the 
>databases are on EMC Symmetrix using 6 disks. All the clients 
>are connecting to database thru Citrix terminal servers. 
>In last one year we spend lots of time/money in tuning 
>databases, replacing Citrix servers but end result is same. I 
>was wondering if anybody out there has ran into same kind of 
>situation. Our (DBAs) guess is the disk layout is not optimal 
>but we also dont have any data to prove that disks are the 
>bottleneck. Is there any way to collect these kinds of stats 
>in Oracle. We aren't getting much help from our SAN administrator.
>
>
>
>DISCLAIMER:
>This message is intended for the sole use of the individual to 
>whom it is addressed, and may contain information that is 
>privileged, confidential and exempt f

RE: Code Conversion from MSSQL into Oracle

2003-12-11 Thread Hallas, John, Tech Dev

Steve Perry from this list produced a perl script to read a import file (rows=n) and 
produce formatted DDL.
Whilst it is not perfect (as Steve agrees) it does give a good basis of where to start 
with processing and transforming a text input stream into a text output stream whilst 
making a small no of changes)  - or ETL as it is called these days, using PERL as the 
mechanism.
 
I host the zip file on my site at http://www.hcresources.co.uk/perlscript.shtml
 
I hope someone finds it useful.   If they do thank Steve.
 
HTH 
 
John

-Original Message-
Sent: 11 December 2003 12:55
To: Multiple recipients of list ORACLE-L


10 years ago or so, I wrote a 105 line script for the UNIX "sed" (a.k.a. "stream 
editor") command to convert Teradata "BTEQ" scripts into Oracle SQL*Plus.  Painful, 
yet thrilling, and it took only about a day of concentration with the O'Reilly "Awk 
and Sed" book at hand.

I'll bet you can write something (whether in "sed" or "awk" or Perl or Java) faster 
than it takes you find a TransactSQL-to-PL/SQL converter...?  And if you don't know 
Perl (probably the best choice) yet, this exercise could be the opportunity to put a 
huge new skill into the old skillset...

Just a thought...



on 12/11/03 4:49 AM, VIVEK_SHARMA at [EMAIL PROTECTED] wrote:





Are there any TOOLs for converting Sample Code (like the following) from MSSQL into 
Oracle?



SAMPLE :-



DECLARE @entity_id char(32), @branch_id char(9)



DECLARE  cur_temp_GEMT CURSOR FOR select

branch_id,entity_id from GEMT where other_party_name='' and entity_type='D'



OPEN cur_temp_GEMT   



FETCH NEXT FROM cur_temp_GEMT INTO

 @branch_id,@entity_id

 

WHILE @@FETCH_STATUS = 0



BEGIN





UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL 
PROTECTED] and [EMAIL PROTECTED] and  addr_type='1' and entity_type='D') where [EMAIL 
PROTECTED] and branch_id = @branch_id



FETCH NEXT FROM cur_temp_GEMT INTO

 @branch_id,

 @entity_id





END



CLOSE cur_temp_GEMT

DEALLOCATE cur_temp_GEMT










-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hallas, John, Tech Dev
  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: long raw risk

2003-12-10 Thread John Flack

The reason that vendors often use LONG RAW is that that datatype meets the ANSI SQL 
standard, while BLOB doesn't.  That way, they can use the same DDL against most SQL 
databases.  Sometimes you can modify the vendor's DDL before or during installation.  
Sometimes you can re-create a table right after installation, as long as the columns 
have the same name and a compatible datatype (and BLOB is somewhat compatible with 
LONG RAW) - but I'd test this thoroughly before I'd go production.

There is a performance risk, but it can be minimized, especially if your vendor lets 
you change what tablespace will be used for the table and its indexes.  I'd put it in 
a tablespace away from the rest of the tables.

-Original Message-
Sent: Wednesday, December 10, 2003 1:30 PM
To: Multiple recipients of list ORACLE-L


Group, 

I have just been given a project / database
where a vendor will implement a table 
with a LONG RAW field in it.

Oracle manuals state clearly that this datatype 
is outdated and should be replaced by BLOB,
I quoted the manuals to vendor-support, but they
will not move on this. 

>From the looks of it, the table with the LR field
will become the largest table in the system, with
well over a billion records in it after the 1st yr.

My main worry is inefficiency in retrieving 
records from the table, and most importantly, 
I cannot partition a table with long/longraw 
columns in it.

On first tests, the LRs are >1K, whereas
the record-without-LR is avg 66 bytes.
In real-life, the LR is probably bigger still.

Quesions:
 - Is there a real performance-risk ?
   Up to now, I always managed to offload LONG/BLOBs
   into separate tables or into LOB-storage clauses,  
   but I see now way to do that here.
 - Given the LongRaw datatype, what are my best 
   defences against (potential) performance problems.

Anyone been-there-done-that ?

Regards, 

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

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

RE: can't see a SQL generated by Crystal Report

2003-12-09 Thread John Flack

I happen to know that you have to work hard to keep Crystal from doing a SELECT * and 
doing its own filtering and grouping.  It can be done, but you have to KNOW that that 
is what you want to do, i.e. novices will let Crystal do what it wants.

-Original Message-
Sent: Tuesday, December 09, 2003 1:45 PM
To: Multiple recipients of list ORACLE-L

I'm not sure. I'm talking to the application team to
see what exactly is being done behind the scene. I do
know that I can't capture queries running via
ColdFusion server and thought that there may be
something like that with Crystal.

Gene
--- Yechiel Adar <[EMAIL PROTECTED]> wrote:
> Are you sure that Crystal does not do any local
> processing on the data?
> 
> Yechiel Adar
> Mehish
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  INET: [EMAIL PROTECTED]

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

RE: A brief detour....;-)

2003-12-09 Thread John Flack

My memories of PL/1 are more unusual - I programmed a pre-CPM Z80 based machine in its 
own dialect of PL/1 in the late 70s early 80s.

-Original Message-
Sent: Tuesday, December 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L


You are bringing up old memories.
I had both PL/1 and assembler (IBM mainframe) in the early 70's.

Yechiel Adar
Mehish
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: OpenLDAP instead of OID

2003-12-09 Thread Hatzistavrou John

l communications through its
networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized
to state them to be the views of any such entity.

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

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

2003-12-09 Thread John Flack

I'm working from memory here, but there is a procedure in a built-in package, I think 
it is DBMS_JAVA, that redirects standard output from a Java Stored Procedure to the 
same buffer that DBMS_OUTPUT uses.  You call this procedure, then your Java method, 
and then you can SET SERVEROUTPUT ON to see the output in SQL*Plus or you can read it 
with DBMS_OUTPUT.GET and DBMS_OUTPUT.GET_LINE.

-Original Message-
Sent: Tuesday, December 09, 2003 8:35 AM
To: Multiple recipients of list ORACLE-L


Im playing with the example in tom kytes book. we have alot of korn shell scripts that 
we use as functions. 

We 'echo' out values to standard out. is there anyway to catch this echo with a java 
stored procedure? I thought about redirecting it to a file and reading it in with 
utl_file, but that makes it more complex. 

any other way to do this? 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: tis a puzzlement...

2003-12-05 Thread John Weatherman

9i imp handles 8i dmp files.  9i exp does not like to connect to 8i databases though.

At least that has been my experience migrating one of our 3rd party DBs.


John P Weatherman
Oracle Database Administrator
Replacements, Ltd.



-Original Message-
Sent: Friday, December 05, 2003 1:34 PM
To: Multiple recipients of list ORACLE-L


Jared Still  scribbled on the wall in glitter crayon:

> Bill, why are you trying to export an 8i database with 9i exp?

because i thought i read somewhere that it would work.  maybe i'm confusing it with 
imp?  will 9i imp read an 81 exp file?

it's either that, or i've experienced an ORA 99 - brain burnt out.;-)

--
Bill "Shrek" Thater ORACLE DBA  
"I'm going to work my ticket if I can..." -- Gilwell song
[EMAIL PROTECTED]

Great spirits have always found violent opposition from mediocrities. The latter 
cannot understand it when a man does not thoughtlessly submit to hereditary prejudices 
but honestly and courageously uses his intelligence. - Albert Einstein
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  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: John Weatherman
  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: tis a puzzlement...

2003-12-05 Thread John Flack

Usual rule of thumb - export using the oldest RDBMS version in the transfer, import 
using the imp for the database to which you are importing.  So export with your 8.1.7 
version of exp, and if you are moving to 9.2 import with the 9.2 version of imp.

-Original Message-
Sent: Friday, December 05, 2003 1:00 PM
To: Multiple recipients of list ORACLE-L

Bill, why are you trying to export an 8i database with 9i exp?

The export views are different, it shouldn't be expected to work.

I don't believe there is any equivalent of catexp7 for 8i/9i.

Jared

On Fri, 2003-12-05 at 09:29, Thater, William wrote:
> database 8.1.7.2  Solaris 64 bit
> 
> exp 9.2.0.1/8.1.7
> 
> same user, same database, schema export, same command line options
> 
> exp user/[EMAIL PROTECTED] compress=n rows=y file=export.dmp log=log.txt
> 
> 8.1.7 exports fine. 9.2.0.1 gives an ORA 942.
> 
> OK so what am i missing here?  which FM do i RT?  i thought the 9 would
> export an 8.1.7 database or am i misunderstanding what i've read?
> 
> --
> Bill "Shrek" Thater ORACLE DBA  
> "I'm going to work my ticket if I can..." -- Gilwell song
> [EMAIL PROTECTED]
> 
> 1916 General theory of relativity. Gravity is a warping of space-time. -
> Albert Einstein
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: java package to run OS command

2003-12-05 Thread John Dunn

Thanks for all the replies...I'm wading through them!

One more question...Do I always need to specify the full path of a Unix
command e.g /usr/bin/mv rather then just mv

That is something I don't currently need to do when using an external
procedure.

John


-Original Message-
Sent: 04 December 2003 20:15
To: Multiple recipients of list ORACLE-L


No, but it disallows command chains - only single commands are permitted.
If you want to run more than one command you have to write it as a shell
script, and the full path to that shell script must be approved for the
current user in the PRODUCT_PROFILE table.  Normally, we will only allow
scripts to run from certain controlled directories.  But we need to be able
to permit commands that we didn't think about when we wrote the procedure,
and prefer to do this table driven rather than by changing programs.

-Original Message-
Sent: Thursday, December 04, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L


will it catch following command apart from "rm -rf" ???

find /var/opt/oracle/logs -mtime +1 -type f -name "*.trc"|perl -nle unlink

Probably not ... and that's why it is dangerous ...  basically you should
have a set of fixed programs that can be called and accept only arguments
from calling programs. That will give at-least more control.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Thursday, December 04, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L


Dick, harsh words, hmmm?  Powerful tools can also be powerful weapons in the
wrong hands.  But don't blame the toolmaker.

John, the reason that running an OS command is such a hassle is that it can
be horribly destructive to your server.  An OS command that runs from a Java
Stored Procedure such as the one from www.oracle-base.com that I gave you or
the one that Tom Kyte wrote and published on Ask Tom will have all the
permissions of the oracle database.  Which means that it can be abused to
absolutely destroy the database, just as in Dick's example.

Doing this with an external procedure as we do is also dangerous.  If you
use a separate Oracle Net listener for them, instead of LISTENER, and have
another user besides the database owner (usually oracle) start that
listener, and password protect the listener, you can at least have some
control over the permissions, which will be those of the user that starts
the listener.  People who run Oracle under Windows may be out of luck here -
it is harder to get this running under a less privileged account in Windows.

We do one more thing for security.  We have a special schema in the database
called COMMON that owns tables and stored procedures that are usable by all
applications.  We put the stub program for the external procedure that
executes OS commands in a package as a private procedure.  The public
procedure that calls this private procedure can examine the OS command
first.  Certain commands, like "rm -fr" are absolutely forbidden, and raise
an exception.  Other commands are checked against the PRODUCT_PROFILE table
which we set up much as for restrictions for what commands certain users may
run in SQL*Plus.  If the current user (or schema) does not have the explicit
privilege to run that OS command, we raise an exception.

You could easily put a similar protective shell around the Java version of
the same thing.


**
This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, attorney
work product or exempt from disclosure under applicable law. If you have
received this message in error, or are not the named recipient(s), please
immediately notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.

**4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  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: John Flack
  INET

RE: java package to run OS command

2003-12-04 Thread John Flack

No, but it disallows command chains - only single commands are permitted.  If you want 
to run more than one command you have to write it as a shell script, and the full path 
to that shell script must be approved for the current user in the PRODUCT_PROFILE 
table.  Normally, we will only allow scripts to run from certain controlled 
directories.  But we need to be able to permit commands that we didn't think about 
when we wrote the procedure, and prefer to do this table driven rather than by 
changing programs.

-Original Message-
Sent: Thursday, December 04, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L


will it catch following command apart from "rm -rf" ???

find /var/opt/oracle/logs -mtime +1 -type f -name "*.trc"|perl -nle unlink

Probably not ... and that's why it is dangerous ...  basically you should have a set 
of fixed programs that can be called and accept only arguments from calling programs. 
That will give at-least more control.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Thursday, December 04, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L


Dick, harsh words, hmmm?  Powerful tools can also be powerful weapons in the wrong 
hands.  But don't blame the toolmaker.

John, the reason that running an OS command is such a hassle is that it can be 
horribly destructive to your server.  An OS command that runs from a Java Stored 
Procedure such as the one from www.oracle-base.com that I gave you or the one that Tom 
Kyte wrote and published on Ask Tom will have all the permissions of the oracle 
database.  Which means that it can be abused to absolutely destroy the database, just 
as in Dick's example.

Doing this with an external procedure as we do is also dangerous.  If you use a 
separate Oracle Net listener for them, instead of LISTENER, and have another user 
besides the database owner (usually oracle) start that listener, and password protect 
the listener, you can at least have some control over the permissions, which will be 
those of the user that starts the listener.  People who run Oracle under Windows may 
be out of luck here - it is harder to get this running under a less privileged account 
in Windows.

We do one more thing for security.  We have a special schema in the database called 
COMMON that owns tables and stored procedures that are usable by all applications.  We 
put the stub program for the external procedure that executes OS commands in a package 
as a private procedure.  The public procedure that calls this private procedure can 
examine the OS command first.  Certain commands, like "rm -fr" are absolutely 
forbidden, and raise an exception.  Other commands are checked against the 
PRODUCT_PROFILE table which we set up much as for restrictions for what commands 
certain users may run in SQL*Plus.  If the current user (or schema) does not have the 
explicit privilege to run that OS command, we raise an exception.

You could easily put a similar protective shell around the Java version of the same 
thing.

**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  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: John Flack
  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 &#

RE: java package to run OS command

2003-12-04 Thread John Flack

Dick, harsh words, hmmm?  Powerful tools can also be powerful weapons in the wrong 
hands.  But don't blame the toolmaker.

John, the reason that running an OS command is such a hassle is that it can be 
horribly destructive to your server.  An OS command that runs from a Java Stored 
Procedure such as the one from www.oracle-base.com that I gave you or the one that Tom 
Kyte wrote and published on Ask Tom will have all the permissions of the oracle 
database.  Which means that it can be abused to absolutely destroy the database, just 
as in Dick's example.

Doing this with an external procedure as we do is also dangerous.  If you use a 
separate Oracle Net listener for them, instead of LISTENER, and have another user 
besides the database owner (usually oracle) start that listener, and password protect 
the listener, you can at least have some control over the permissions, which will be 
those of the user that starts the listener.  People who run Oracle under Windows may 
be out of luck here - it is harder to get this running under a less privileged account 
in Windows.

We do one more thing for security.  We have a special schema in the database called 
COMMON that owns tables and stored procedures that are usable by all applications.  We 
put the stub program for the external procedure that executes OS commands in a package 
as a private procedure.  The public procedure that calls this private procedure can 
examine the OS command first.  Certain commands, like "rm -fr" are absolutely 
forbidden, and raise an exception.  Other commands are checked against the 
PRODUCT_PROFILE table which we set up much as for restrictions for what commands 
certain users may run in SQL*Plus.  If the current user (or schema) does not have the 
explicit privilege to run that OS command, we raise an exception.

You could easily put a similar protective shell around the Java version of the same 
thing.

-Original Message-
Sent: Thursday, December 04, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


John,

I agree and am encouraging the external C procedures since their simpler, and 
can create log files as well.  BTW: Whoever authored the Java procedure on that web 
page should be shot.  I can just see someone passing 'rm -fr $ORACLE_HOME' to it.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Thursday, December 04, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


yeah, I'm trying to get away from C external procedures ...but java is
beginning to look just as much a hassle.

Why is running a OS command such hassle?


-Original Message-
Sent: 04 December 2003 15:40
To: Multiple recipients of list ORACLE-L


Here's another link to a good Java Stored Procedure for this:
http://www.oracle-base.com/Articles/8i/ShellCommandsFromPLSQL.asp

But the same caveats apply.  We're using an external procedure written in C
for this instead of Java, and I use a shell that checks PRODUCT_PROFILE for
authority to run the command, before it will call the extproc.  I'm happy to
share source code with anyone interested it doing it this way, but external
procedures are a bit harder to set up than Java Stored Procedures and open
you to a few security hazards.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: java package to run OS command

yeah, I'm trying to get away from C external procedures ...but java is
beginning to look just as much a hassle.

Why is running a OS command such hassle?


-Original Message-
Sent: 04 December 2003 15:40
To: Multiple recipients of list ORACLE-L


Here's another link to a good Java Stored Procedure for this:
http://www.oracle-base.com/Articles/8i/ShellCommandsFromPLSQL.asp

But the same caveats apply.  We're using an external procedure written in C
for this instead of Java, and I use a shell that checks PRODUCT_PROFILE for
authority to run the command, before it will call the extproc.  I'm happy to
share source code with anyone interested it doing it this way, but external
procedures are a bit harder to set up than Java Stored Procedures and open
you to a few security hazards.

-Original Message-
Sent: Thursday, December 04, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


John, it is available on asktom as well, but read the caution Tom explains.
Restrict it to only executables you want. Asktom has example.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Thursday, December 04, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L


Which book is that?



-Original Message-
Sent: 04 December 2003 14:35
To: Multiple recipients of list ORACLE-L


its in tom kytes first book. might be on his webpage. 
> 
> From: John Dunn <[EMAIL PROTECTED]>
> Date: 2003/12/04 Thu AM 08:49:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: java package to run OS command
> 
> I need a java package that will allow me to run OS commands(Unix) from a
> stored procedure.
> 
> Anyone got one?
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: John Dunn
>   INET: [EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: John Dunn
  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: java package to run OS command

Which book is that?



-Original Message-
Sent: 04 December 2003 14:35
To: Multiple recipients of list ORACLE-L


its in tom kytes first book. might be on his webpage. 
> 
> From: John Dunn <[EMAIL PROTECTED]>
> Date: 2003/12/04 Thu AM 08:49:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: java package to run OS command
> 
> I need a java package that will allow me to run OS commands(Unix) from a
> stored procedure.
> 
> Anyone got one?
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: John Dunn
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

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

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

2003-12-04 Thread John Flack

Here's another link to a good Java Stored Procedure for this:
http://www.oracle-base.com/Articles/8i/ShellCommandsFromPLSQL.asp

But the same caveats apply.  We're using an external procedure written in C for this 
instead of Java, and I use a shell that checks PRODUCT_PROFILE for authority to run 
the command, before it will call the extproc.  I'm happy to share source code with 
anyone interested it doing it this way, but external procedures are a bit harder to 
set up than Java Stored Procedures and open you to a few security hazards.

-Original Message-
Sent: Thursday, December 04, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


John, it is available on asktom as well, but read the caution Tom explains. Restrict 
it to only executables you want. Asktom has example.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Thursday, December 04, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L


Which book is that?



-Original Message-
Sent: 04 December 2003 14:35
To: Multiple recipients of list ORACLE-L


its in tom kytes first book. might be on his webpage. 
> 
> From: John Dunn <[EMAIL PROTECTED]>
> Date: 2003/12/04 Thu AM 08:49:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: java package to run OS command
> 
> I need a java package that will allow me to run OS commands(Unix) from a
> stored procedure.
> 
> Anyone got one?
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: John Dunn
>   INET: [EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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).

background process "LGWR" did not start

Anyone know what might be causing this error? Oracle 8.1.7 on Solaris.

 background process "LGWR" did not start


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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).

java package to run OS command

I need a java package that will allow me to run OS commands(Unix) from a
stored procedure.

Anyone got one?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  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: Verifying success of dbms_repcat.execute_ddl

2003-12-02 Thread John Kanagaraj

Paul,

How about an ON DDL system trigger on the source (and possibly target)
databases? This trigger can log any and every detail of the who/what/when
whenever *any* DDL is performed by whoever...

Let me know if you need more details.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Paul Baumgartel [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, December 02, 2003 4:29 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Verifying success of dbms_repcat.execute_ddl
>
>
>I'm trying to determine when execution of DDL via
>DBMS_REPCAT.EXECUTE_DDL completes successfully on all master sites. 
>I'm not sure how the DDL is propagated, but it doesn't appear to be via
>the replication administrator's scheduled jobs:  I've removed the
>dbms_defer_sys.push job (via dbms_defer_sys.unschedule_push), and
>broken the dbms_repcat.do_deferred_repcat_admin job, then run
>dbms_repcat.execute_ddl.  The results show up on the other master
>database within a couple of seconds.
>
>Anyway:  If there is an error produced by the EXECUTE_DDL call, Oracle
>returns an error message immediately, and the error is also logged in
>dba_repcatlog (this led me to believe that execute_ddl calls were
>handled by dbms_repcat.do_deferred_repcat_admin, but that doesn't
>appear to be the case), so error detection is easy.  What I need,
>though, is to be able to tell _when_ the DDL has been run on each
>master database (I am running a batch job that disables all FKs, then
>runs an import; the first attempt produced failures because the row
>insertions caused by the import arrived at the other master DB _before_
>the FK disablement).
>
>Any help appreciated.  TIA.
>
>
>
>
>=
>Paul Baumgartel
>Transcentive, Inc.
>www.transcentive.com
>
>__
>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: Paul Baumgartel
>  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: John Kanagaraj
  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: FRM-41072

2003-11-30 Thread John




I have no access to metalink.
So far, i had no problem get connected to the 
Oracle 9i R2 with a 7.3.3. client (ORANT).
 
If i try to connect with an Oracle Client 9i r2 to 
an Oracle 7.3.3. then it fails
 
 
- Original Message - 

  From: 
  Yechiel 
  Adar 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Sunday, November 30, 2003 1:04 
  PM
  Subject: Re: FRM-41072
  
  Hello John
   
  From you description of the problem I suspect that you 
  use 7.3 client.
  See note 172179.1 on metalink about which client connect 
  to which server.
  Yechiel AdarMehish
  
- Original Message - 
From: 
John 
To: Multiple recipients of list ORACLE-L 

Sent: Saturday, November 29, 2003 8:14 
PM
Subject: FRM-41072

Do you know how to overcome this 
problem?
 
Currently my forms work good with a 7.3.3 
Database Server.
While i get connected to the 9.2.0.1 
database i receive this error "Cannot create the Group"
My forms have been created by the 
Developer/2000 which i don;t own.
 
I feel that i have something missed to migrate 
to the new DB Server.
 
Could you help me a little?
 
John

Re: FRM-41072

2003-11-29 Thread John

How to set SQL_TRACE on on my server? I am quite novice.


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, November 29, 2003 9:54 PM


> John,
>
> This sounds like an application specific error.
>
> Was there an 'ORA-' error?
>
> If not, you will need to do some digging to find
> out what the app is attempting to do.
>
> Perhaps setting SQL_TRACE on for the session would
> provide some clues, especially if you don't have
> source for the app.
>
> Jared
>
> On Sat, 2003-11-29 at 10:14, John wrote:
> > Do you know how to overcome this problem?
> >
> > Currently my forms work good with a 7.3.3 Database Server.
> > While i get connected to the 9.2.0.1 database i receive this error
"Cannot create the Group"
> > My forms have been created by the Developer/2000 which i don;t own.
> >
> > I feel that i have something missed to migrate to the new DB Server.
> >
> > Could you help me a little?
> >
> > John
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
>   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: John
  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).

FRM-41072

2003-11-29 Thread John




Do you know how to overcome this 
problem?
 
Currently my forms work good with a 7.3.3 Database 
Server.
While i get connected to the 9.2.0.1 database 
i receive this error "Cannot create the Group"
My forms have been created by the Developer/2000 
which i don;t own.
 
I feel that i have something missed to migrate to 
the new DB Server.
 
Could you help me a little?
 
John

RE: RBO to CBO migration books/ material

2003-11-13 Thread John Kanagaraj

Suhen,

>Any good books available to convert applications from RBO to CBO.
>References to Oracle 9i.

I am not aware of any book, but there is a limited number of articles/papers
(most well-known is Tim Gorman's 'Search for intelligent life in the CBO' at
http://www.evdbt.com) that can point you in the right direction. My humble
addition is the one at http://www.geocities.com/john_sharmila/links.htm -
there are a number of ML articles on the CBO itself that will help: Doc ID:
35934.1 is a good one to start. I would personally do the following:

* Trace all SQL coming into a live RBO-only system 
* Identify any code that uses the RULE Hint (in spite of being in a RULE
based DB)
* Create a clone of prod on a server of the same or similar capacity
* Collect Statistics (COMPUTE if you can)
* Set the OPTIMIZER_MODE to CHOOSE; review/reset other CBO related
parameters (see my paper)
* Let the Developers and UA testers loose on that Db
* Use Cary's method to identity the top set of business processes and
determine if the performance is Ok
* If not Ok, then tune it...

All the best!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  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: Multi-threaded server - will it help in this case

2003-11-11 Thread John Kanagaraj

Jared,

I don't think that is what Tim meant. You can use something akin to the
following:

For an MTS connection/client:

MYDB_MTS.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID)))

For a dedicated connection/client:

MYDB_DEDICATED.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID)(SERVER=DEDI
CATED)))

The only difference is in the TNS handles and the entry they point to which
differs in content. The SERVER=DEDICATED will bypass the MTS configured
default connection.

You can do this via ONAMES too (and I know you use one!) - see
Note:1036577.6. Btw, I am currently in the UK helping with a Name Server
rollout..

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
>-Original Message-
>From: Jared Still [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, November 11, 2003 7:29 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Multi-threaded server - will it help in this case
>
>
>Tim,
>
>This bit:
>
>> accomodate this application.  Please be aware that you can
>> mix dedicated and MTS by setting up different TNS names on
>> different ports for each, so it is not an all-or-nothing
>
>seems to imply that MTS and Dedicated will each require their
>own listener ( different ports).  Been awhile since I messed 
>with MTS, but I don't recall that as being necessary.
>
>Is that what you meant?
>
>Jared
>
>
>
>On Tue, 2003-11-11 at 07:04, Tim Gorman wrote:
>> Peter,
>> 
>> MTS (or SS in 9i onwards) is an excellent choice to
>> accomodate this application.  Please be aware that you can
>> mix dedicated and MTS by setting up different TNS names on
>> different ports for each, so it is not an all-or-nothing
>> situation.  Most connections to the database outside of this
>> CAE app will likely be better served with dedicated
>> connections, so just dole out TNS names accordingly.
>> 
>> Also, please be sure to estimate the size of your UGA by
>> tracking values (i.e. name like '%uga%') in V$SESSTAT at
>> peak periods then sizing the Large Pool to accomodate,
>> before you enable MTS.  Unless you're really constrained for
>> memory, don't be shy about this;  double the highest value
>> you sum from V$SESSSTAT to be safe.  After enabling MTS,
>> monitor the value of "free memory" where POOL = 'large pool'
>> in V$SGASTAT.  If you've oversized, you can start backing
>> down on LARGE_POOL_SIZE gently, if you need the memory
>> elsewhere...
>> 
>> Hope this helps...
>> 
>> -Tim
>> 
>> > Environment:  AIX 4.3
>> > Oracle 8.1.7
>> > 
>> > The application is a CAE tool which stores metadata for
>> > a hierarchy of 3D engineering design models.
>> > When a user opens a model at a given level in the design,
>> > the application retrieves data about that model and all of
>> > the models below it in the design try.  This often
>> > involves as many as 100 or more models. 
>> > Unfortunately, the way the application is written, it
>> > opens a new connection to the database for each model. 
>> > Thus, in the process of retrieving
>> > metadata, it may open and close as many as 100 connections
>> > to the database. Obviously, this causes some performance
>> > problems, especially for  remote users.  The number of
>> > users when the system goes fully into production
>> > is going to be in the low 100's.
>> > 
>> > The vendor is not interested in changing the way the
>> > software works. 
>> > Will use of the mult-threaded server improve performance
>> > in this situation, for
>> > example, by eliminating the overhead of starting a
>> > dedicated server for each connection?
>> > 
>> > Thanks,
>> > Peter Schauss
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> -- 
>> Author: Tim Gorman
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>> San Diego, California-- Mailing list and web hosting services
>> -
>>

RE: shareplex: datatype unsupported

2003-11-05 Thread Hallas, John, Tech Dev

Please bear in mind that there is one thing in a datatype being supported and another 
in all functions and features of Shareplex being usable when that datatype is involved.

I am thinking about datatype long specifically.

We have been replicating a 8i database (tru64) to a 9i one (sun)using Shareplex for 
months and we have tested the reverse replication and that works equally well. That is 
not to say that we have not had problems though !!


John

-Original Message-
elain he
Sent: 05 November 2003 12:04
To: Multiple recipients of list ORACLE-L


Hi,
We are evaluating using either Oracle logical standby or Quest Shareplex 
replication for reporting purposes. It appears that there are quite a few 
datatypes not supported by Logical standby. Anyone knows what datatypes are 
not supported by shareplex replication? Tried looking up at quest website 
but could not find any documentation.

Quest claimed that shareplex can replicate database of different versions, 
for eg from 9i to 8i as long as the 9i new features are not being utilized. 
Anyone has any experience with that?

Thanks.

elain

_
MSN Messenger with backgrounds, emoticons and more. 
http://www.msnmessenger-download.com/tracking/cdp_customize

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: elain he
  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: Hallas, John, Tech Dev
  INET: [EMAIL PROTECTED]

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

RE: Re[2]: Sequences in OPS/RAC

2003-11-03 Thread John Kanagaraj

All,

Just wanted to point out that 'missing' invoice numbers caused by a variety
of causes (even if they were not cached), can cause problems for
Accounting/Finance Depts in certain countries. Basically, the Govt looks on
this as being used for 'tax avoidance', unless proved otherwise. You *can*
miss uncached sequences under certain conditions when the Db restarts or a
short burst of SQL causes pressure on the DD cache... Had this occur once in
an Apps database and had to apply patches to undo and put back the
sequence...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
>Sent: Monday, November 03, 2003 10:29 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Re[2]: Sequences in OPS/RAC
>
>
>The problem is that the ORDER clause comes at the expense of
>CACHE.  You can use SQL tracing to verify that each use of
>the sequence causes an update of SYS.SEQ$ when ORDER is set,
>effectively rendering the CACHE setting a no-op.  So,
>especially in an OPS/RAC environment, the use of ORDERED
>sequences, especially heavily used ORDERED sequences, comes
>at a steep price.
>
>Think about it:  is ORDERED *really* necessary?  In some
>situations (i.e. check numbers), the ORDERED clause would be
>necessary, but unless you are pumping out thousands of
>checks an hour, perhaps a cached sequence shouldn't be used.
> But for system-generated keys, surrogate keys, etc, I don't
>think the semantics of ORDERED are necessary at all.
>
>
>
>> Hi,
>> 
>> I have RAC and I always use ORDER when I create SEQUENCE. 
>> The following information is from Oracle Manual: 
>> ORDER is necessary only to guarantee ordered generation if
>> you are using Oracle with Real Application Clusters. If
>> you are using exclusive mode, sequence numbers are always
>> generated in order. 
>> Muqthar Ahmed
>> 
>> -Original Message-
>> Sent: Monday, November 03, 2003 12:04 PM
>> To: Multiple recipients of list ORACLE-L
>> 
>> 
>> Hello Hemant,
>> 
>> Monday, November 3, 2003, 11:29:26 AM, you wrote:
>> HKC> However, the Builder.Com article quite explicity
>> asserts HKC> "Sequence generator numbers are guaranteed to
>> be unique only for a single  HKC> instance, which is
>> unsuitable for use as a primary key in parallel or  HKC>
>> remote environments, where a sequence in each environment
>> might generate  HKC> the same number and result in
>> conflicts 
>> Can you point us to the article? My guess is that the
>> author is not familiar with Oracle, and is basing the
>> above statement on his experience with some other database
>> (DB2 perhaps?). There is no problem with using sequence
>> numbers in a RAC. No conflicts will occur. I've never
>> heard of a problem in that regard.
>> 
>> Best regards,
>> 
>> Jonathan Gennick --- Brighten the corner where you are
>> http://Gennick.com * 906.387.1698 *
>> mailto:[EMAIL PROTECTED] 
>> Join the Oracle-article list and receive one
>> article on Oracle technologies per month by 
>> email. To join, visit
>> http://four.pairlist.net/mailman/listinfo/oracle-article, 
>> or send email to [EMAIL PROTECTED] and 
>> include the word "subscribe" in either the subject or
>> body. 
>> -- 
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net -- 
>> Author: Jonathan Gennick
>>   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: Muqthar Ahmed
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- 858-538-5051
>> http://www.fatcity.com San Diego, California--
>> Mailing list and web hosting services
>>

RE: Moving projects from development/test to production

2003-10-31 Thread John Kanagaraj

Dennis,

You guessed correctly that this is ITIL based. I completed a Foundation
certificate in ITIL way back in '96 when it was still UK based more than
what it is now. The principles still stand, and the organization I worked
for implemented some kind of CMDB. Unfortunately, they got into very low
level details for the Cis and the project became too big to get off the
ground One needs to find a balance. OTOH, the tools to help implement
ITIL have come a long way since and I mentioned some good ones (at least
ones that I have seen).

Most of ITIL is just common-sense distilled into a framework for IT
processes that define what an IT organization needs to get things done
properly. However, in my limited understanding, the IT scene and
organizations changes direction and in leadership so rapidly that one needs
an evolving plan (to say the least).

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] 
>Sent: Friday, October 31, 2003 8:55 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Moving projects from development/test to production
>
>
>Helmut
>   I don't think this is off-topic, but something that many of 
>us Oracle
>DBAs wrestle with.
>   If you kept John Kanagaraj's posting yesterday on the DBA Support
>Database topic, it contains the "big picture". If you didn't 
>keep it, email
>me privately and I'll send it. I believe he is referring to the ITIL
>standards body.  There is probably an ITIL user group near you 
>or a company
>that gives talks from time to time. That is the easiest way to get an
>overview of ITIL. Our organization is "evolving" in that 
>direction. Overall,
>this is a long-term commitment by your organization. You don't change
>everything overnight.
>   One immediate change you can implement is a "staging" 
>system. Most of us
>currently have a test or development system. Staging is an 
>exact copy of
>production in all respects. From the database side, cloning 
>the database or
>using RMAN DUPLICATE works very well. Then the development team makes a
>release to you. Ideally this is on a CD-ROM labeled 1.1 or 
>whatever. You
>take the CD-ROM and instructions and apply the changes to the staging
>server. Then the staging system is tested. If it is deemed 
>satisfactory,
>then you schedule a time and make the changes on production. 
>If it fails the
>testing or you are unable to apply the changes, then it gets 
>bounced back to
>the development group. The release may include application or 
>web server
>changes that must be coordinated with database changes. This is a small
>change, but it has helped our releases.
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] 
>
>-Original Message-
>Sent: Friday, October 31, 2003 1:59 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi!
>
>A somewhat off-topic question this time.
>
>I am put in charge of defining the procedure of moving projects from
>test/development into the production environment. This is to 
>be seen from
>the entire IT-perspective (i.e. not just databases, but also 
>Unix, Oracle
>and SAN). I.e. we should come up with check-lists and the 
>like; although
>having an eye on quality assurance...
>
>We urgently need to set procedures up for that since the last 
>time this was
>a nightmare...
>
>Did anybody out there work on a similar project? What are the 
>procedures
>that you are following?
>
>Any input would be appreciated.
>
>This is 9.2 on HP-UX 11.
>
>Thanks,
>Helmut
>
>
>Helmut Daiminger
>
>WWK Lebensversicherung a.G. 
>Marsstrasse 37
>80292 München
>Telefon: (0 89) 51 14 - 3490
>Fax: (0 89) 51 14 - 27 62 
>mailto:[EMAIL PROTECTED]
>http://www.wwk.de
>
>*** select 'bye for now' from sys.dual ***
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Daiminger, Helmut
>  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 info

RE: xdb and xschema

2003-10-31 Thread John Kanagaraj

M,

There are a large number of articles on XML in SELECT - IOUG's technical
journal (requires membership though). And I am sure that OTN has a ton of
XML/XDB articles as well - they may a good starting point.

J
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Friday, October 31, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


I've been tasked to create an xschema in the XDB repository.  I'm new to
xml.  Is there a good place to start?
I've run the catqm.sql script to create the XDB repository, but I'm
searching for the next steps.  Any help appreciated.
TIA
M.


Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

Re: Client Search Info Needed

2003-10-31 Thread John Shaw




In order to find all your clients with wild cards you would have to use a 
function like upper(lastname) like 'MCD%' - but you really don't want to be 
doing full table scans. You could create a function based to look at your data 
based upon the function ie:
CREATE INDEX emp_name_index ON emp ( UPPER(ename)) ;As for finding 
alternative spellings of the last name you will porbably want to use a 
soundex function - you can use the default one in Oracle, it does work OK for 
basic functionality.>>> [EMAIL PROTECTED] 10/30/2003 5:14:25 
PM >>>
I am looking for an efficient solution to the following:We intend 
to capture information about a client such as:first name - Johnlast 
name - McDonaldphone numer - 222.222.zip code - 4state - 
FLclient number - 123343The names will be stored in mixed case for 
proper printing on client documents.The reps would like the flexiblity to 
enter the search criteria in a number offormats such as:1)  
last name like mcdon* (wildcard) and first name = john2)  client number 
= 123343 (note: some clients do not always have their clientnumber handy so 
it can not be the only available search mechanism)3)  last name = mac 
gregor (and locate both macgregor and mac gregor)4)  last name = 
kinney-jones (and locate both kinney-jones and kinney jones)How many 
indexes and of what type are required?  Does the leading the column 
ofan index have to be specified for the index to be used?  I thought I 
rememberhearing that that was a limitation of an older release, but that is 
no longerthe case with 8 and up.   Are there any white papers 
available that address thetopic of client search and best 
practices?Thanks for your help!!American Express 
made the followingannotations on 10/30/2003 04:11:07 
PM--** 
"This message and any attachments are solely for the intended recipient and may 
contain confidential or privileged information. If you are not the intended 
recipient, any disclosure, copying, use, or distribution of the information 
included in this message and any attachments is prohibited.  If you have 
received this communication in error, please notify us by reply e-mail and 
immediately and permanently delete this message and any attachments.  Thank 
you."**==-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Tracy 
Rahmlow  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
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).

Oracle Licensing

2003-10-31 Thread Hatzistavrou John


Dear All,


Oracle licensing has been a subject that often is risen by somebody.
I though I shall share this with you.


Regards,


Hatzistavrou Yannis


Oracle_SW_License_Guide.pdf
Description: Binary data

RE: Table partitioning Oracle 9.2

2003-10-31 Thread John Weatherman

Vikas,

Are you asking if deallocated extent space can be reused by the "current" partition or 
wether new dates will suddenly go into a partition that explicitly excludes them?


John P Weatherman
Oracle Database Administrator
Replacements, Ltd.



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


--=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426
Content-Type: text/plain; charset=us-ascii

 


RDBMS Version: 9.2.0.1.0
Operating System and Version: Solaris 8
Error Number (if applicable): 
Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0

Table partitioning

Hi, 

I've a query reg. space usage in context of partitioned tables. 

I've a table with 12 partitions P_1 ... P_12. Until now data 
got populated in P_1 upto P_6 and future data will come in P_7 etc. 
If i delete some huge amount of data from P_1 (after archiving it) 
will that freed space be used by future inserts (which happens in subsequent 
partitions like P_7 etc). 
Unfortunately, we can't delete all data in partition. We have to keep some data which 
account say 5% of total data. ie, we're deleting 95% of data from a partition. So, 
will this freed blocks be put to free list and used by future inserts? 
Data is partitioned by date. So, my query is whether Oracle will put future data 
(which belongs to partition P_7 etc.) in space earlier used by P_1.

Any help from members is appreciated. 

Thanks, 
Vikas 

Get Your Private, Free E-mail from Indiatimes at  http://email.indiatimes.com Buy The 
Best In BOOKS at http://www.bestsellers.indiatimes.com
Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to 
http://airsahara.indiatimes.com and Bid Now !

--=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426
Content-Type: text/html; charset=us-ascii

 
RDBMS Version: 9.2.0.1.0Operating System and Version: 
Solaris 8Error Number (if applicable): Product (i.e. SQL*Loader, 
Import, etc.): Partitioned TableProduct Version: 9.2.0.1.0Table 
partitioningHi, I've a query reg. space usage in context of 
partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now 
data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i 
delete some huge amount of data from P_1 (after archiving it) will that freed 
space be used by future inserts (which happens in subsequent partitions like P_7 etc). 
Unfortunately, we can't delete all data in partition. We have to keep some data 
which account say 5% of total data. ie, we're deleting 95% of data from a partition. 
So, will this freed blocks be put to free list and used by future inserts? Data is 
partitioned by date. So, my query is whether Oracle will put fu!
! tu! re data (which belongs to partition P_7 etc.) in space earlier used by 
P_1.Any help from members is appreciated. Thanks, Vikas  
Get Your Private, Free E-mail from Indiatimes at  
http://email.indiatimes.com";>http://email.indiatimes.comBuy The Best In BOOKS at http://www.bestsellers.indiatimes.com";>http://www.bestsellers.indiatimes.comBid
 for Air Tickets on Air Sahara Flights. For Best Deals, log on to http://airsahara.indiatimes.com";>http://airsahara.indiatimes.com and Bid Now 
!

--=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vikas S
  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: John Weatherman
  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: Oracle Apps: Single Node or Multi-node install?

2003-10-31 Thread John Weatherman

Nahata,

My experience is a little dated, having not worked with 11i (just up through 10.5), 
however in my experience  a n-node install would be preferable.  At least when I was 
working with the Apps, it was a lot easier to size the database/concurrent processing 
server in terms of overall horsepower (memory/cpu) to get consistent performance, or 
at least know that at X time, there would be some slowdowns (usually when 1000+ 
reports got kicked off for month end).  The form/web piece was highly variable in what 
it needed however.  My isolating it on another server, it was a lot easier to 
demonstrate needing an additional forms server.  I also found that the database/cm 
tier tended to be a lot more stable than the machine where all those web connections 
were being made.  Using more than 1 web/forms server helped to work around 
unanticipated machine problems.

I was generally working with Financials (AR, AP, GL) and OE, with a little 
Manufacturing thrown in.

Just my $0.02.

John P Weatherman
Oracle Database Administrator
Replacements, Ltd.



-Original Message-
Sent: Friday, October 31, 2003 12:24 AM
To: Multiple recipients of list ORACLE-L


Hi,

We are implementing Oracle Apps version 11.5.9 on Solaris with the following
modules:
  AR, AP, GL, FA, CM, Project Billing, Project Costing, PO, iProcure, iExpense

The anticipated load is: 
20 Forms users
500 Web users

Should we go for 2 Node install or a single node install? I know the answer is "it 
depends", but what are the factors I should consider in choosing between the two?

Regards
Naveen
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen, Nahata (IE10)
  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: John Weatherman
  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: Overhead Associated with Signon Audit in Financials 11.0

For all the non-APPS DBAs out there...

Oracle Applications 10.4 onwards (lowest version I have seen) provides for a
feature called 'Signon Auditing'. This is NOT Oracle's Auditing (which goes
into SYS.AUD$). It is a parameter driven auditing that records all Users
that logged in when set to USER, Application Responsibilities that they
chose (upon login as well as subsequently switched to) when set to
RESPONSIBILITY, in addition to recording the USER level, and the Forms that
they chose to run when set to FORMS, in addition to that recorded at
RESPONSIBILITY and USER levels. Thus, when set to FORMS, a user login would
at best produce a minimum of three rows, etc. These rows are updated when
the user logged out, so all sorts of reports about who is/was logged on,
forms currently being used, etc. can be determined. In fact, for an Apps DBA
to tie back a session to an actual user, at least USER level signon auditing
should be turned on. The problem with Apps is that all users would login in
the APPS schema using the encrypted password which is  obtained using a
dummy connection... Forms and further Access is then determined by
'Responsbilities' that are in turn tied to 'Organizations' and 'Datasets'.
By default, almost all Applications tables record the last updated user and
timestamp, so there is some inbuilt auditing, albeit not a trail. Oracle
provides an additional Audit function that performs an audit trail for such
datasets, and this can produce significant overhead for data storage. 

Thus all discussions about SYS.AUD$ are not really relevant in this
particular thread, although some good ideas have been aired. Switching on
Auditing without understanding what is ultimately required would be very
counterproductive, whether this is on an APPS database or not, in any case. 

[As an aside, most of this is enabled via the AOL - Applications Object
Layer (aka FND - Foundation Layer) and is a solid example of providing
'Application' infrastructure. And don't get me started on the Concurrent
Processing - that's an excellent one too]

I am going to stop now and let Apps gurus such as Andy R, Tanel and Tim G
comment.

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **


>-Original Message-
>From: Mladen Gogala [mailto:[EMAIL PROTECTED]
>Sent: Thursday, October 30, 2003 1:39 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Overhead Associated with Signon Audit in Financials 11.0
>
>
>It is true, auditing adds significant overhead, but not 
>session auditing.
>Significant overhead is added by DML auditing because you ad 
>significant
>amount of modified blocks to every transaction you audit, you 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  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: DBA Support Database

Paul,

Kevin Loney was the original author of the CC Db - way back in the Oracle7
handbook. I used to have a schema built on that basis in a previous job, and
it served the  purpose well. However, the problem does remain that 'linking'
it to other parts of the IT infrastructure will not work on account of
*everyones* inability (dare I mention 'apathy') to keep it up to date. And
yes - Greg's expensive free comment is well taken. An enterprise IT
repository is well worth it, but it can and will be a bear to get off the
ground unless there is some serious Management committment behind it. If
executed and maintained well, it can relieve a lot of pressure and work and
add value to the 'business' [Hope I don't sound like damagement :) ]

John Kanagaraj 
Oracle Applications DBA 
DB Soft Inc 
Work : (408) 970 7002 

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com <http://www.klove.com/>  

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

-Original Message-
Sent: Thursday, October 30, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L

I recall seeing a "command center database" in the book

Oracle 8i DBA Handbook by Loney, Theriault. 
chapter 6 - Managing multiple databases.

its a start. I haven't read the 9i version.

Rachel, 

were there any improvements to it?

Paul

"Loughmiller, Greg" <[EMAIL PROTECTED]> wrote:

I'll throw in my *very expensive free* comments... 

This begins to create the corporate metadata and architecture as Peter
mentions. We are on this road, and there are several tools that can do *auto
discovery*. There are some very nice tools on the market for asset
discovery. They have *exit points* where one could write some basic code to
access those assets(sql getting v$ info and store in your metadata?).

We have built an enterprise repository to maintain infrastructure data,
application, servers, network devices, and transport layers. And now we are
going down the *yellow brick road* to begin the data acquisition process. 

We too, will also define and assign accountability to those elements within
our repository. 

greg 

-Original Message- 
Sent: Thursday, October 30, 2003 11:50 AM 
To: Multiple recipients of list ORACLE-L 

Just a quick reply to this. 

You are, in fact, formulating the sort of request which would be input to a 
corporate data architecture. We have built such a thing, and it includes the

issues you refer to. More importantly, we have identified who is responsible

for every single piece of data in the system. The management of an attribute

in a table can in fact be traced right back up to that level of senior 
management where they don't even know how to spell 'Oracle'... 

peter 
edinburgh 

> -Original Message- 
> From: Smith, Ron L. [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] 
> Sent: Thursday, October 30, 2003 3:30 PM 
> To: Multiple recipients of list ORACLE-L 
> Subject: DBA Support Database 
> 
> 
> I was thinking about putting together a database that 
> contains a list of 
> DBAs, servers, databases, and applications.  The database 
> would be used 
> by the Helpdesk and Management to see who is responsible for a given 
> application or database when problems occur.  
> 
> I thought I would check first and see if anyone has already designed 
> such a database and might be willing to share it. 
> 
> Thanks! 
> Ron Smith 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net/>  
> -- 
> Author: Smith, Ron L. 
>   INET: [EMAIL PROTECTED] 
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com/>  
> San Diego, California-- Mailing list and web hosting services 
> - 
> To REMOVE yourself from this mailing l! ist, send an E-Mail message 
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L 
> (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  message,  and  any  files  transmitted  with  it, are 
confidential  and intended  solely for the  use of the  addressee. If 
this message was not addressed to  you, you have received it in error 
and any  copying,  distribution  or  other use  of any part  of it is 
strictly prohibited. Any views or opinions presented are solely those 
of the sender and do not necessarily represent  those of the British 
Geological  Survey. The

RE: Overhead Associated with Signon Audit in Financials 11.0

Vicki,

As long as your *Purge* Audit signon data, I really do not see any
significant overhead. We have a 200 Gb DB and see no issues. What level is
your Profile set to? The advantages of Signon Audit far outweighs the load
it places - for e.g. you have no other way of seeing which user is logged on
(and depending on your audit level) what forms and what responsibility they
are using at this time... On the other hand, ask your auditors *what* they
would like to see. Oracle Apps already records Last-changed user and
date/timestamp for rows, while Signon Audit tracks sessions only when it is
switched on.

Let us know if you need more info.
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **


-Original Message-
Sent: Thursday, October 30, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L



Does anyone have any statistics about overhead associated with using the
Signon Audit in an 11.0.3/ 8.1.7.4/8.0.6.3 environment.  We are using full
installs of AP, GL, FA and CE.  Size of the production database is 100G.
Can't tell you exactly what we'd be auditing;  we are under siege by
Internal Audit at the moment - they've raised the "database audit" flag, but
have not started dictating what they want audited.  I am trying to get some
real-world statistics to arm myself with when the day comes . 

I have heard that the overhead is significant - is this true, in your
experience? 

Vicki Pierce
Database Administration
x2401
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  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: 10046 level 8 trace - help required with 'direct path

Tim,

As you have seen, this is due to writes to and reads from the TEMPORARY
tablespace of that user. This could be due to both SORT segments
(SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going
to TEMP when they overflow HASH_AREA_SIZE. This can be seen from
V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or
Hashing, I am assuming that either there are triggers that are forcing this
to occur, or this is a view and the INSTEAD OF is performing some
inefficient joins... 

Andy - just curious how a WHERE clause on a DELETE would generate Sort usage
(outside of that explained above)...

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **

>-Original Message-
>From: Yong Huang [mailto:[EMAIL PROTECTED]
>Sent: Thursday, October 30, 2003 9:10 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: 10046 level 8 trace - help required with 'direct path
>
>
>Hi, Tim,
>
>Assuming you don't have more than 1000 files, what's your 
>db_files set to and
>what's select file#, name from v$tempfile? If you do have more 
>than 1026 files,
>select file#, name from v$datafile.
>
>Also show us select * from v$sort_usage if you can run that 
>DELETE again.
>
>XCTEND rlbk=0: your transaction end marker says it's not 
>rolling back; i.e.
>it's committing.
>
>Yong Huang
>
>--- Andy Rivenes <[EMAIL PROTECTED]> wrote:
>> Looks sort spillage to disk due to the where clause.
>> 
>> Andy Rivenes
>> [EMAIL PROTECTED]
>> 
>> At 06:44 AM 10/30/2003 -0800, Tim Onions wrote:
>> >Gurus
>> >
>> >I've applied many of the things I've learnt from this list 
>over the years
>> >and today I tried a 10046 trace for the first time on a 
>reported "slow"
>> >transaction. From what I can tell the biggest offender is a 
>wait seemingly
>> >associated with rollback (see below) called 'direct path 
>write'. Is this
>> >just a traditional wait for a row lock to be released or 
>something more
>> >sinister? Any help much appreciated. Also (daft question 
>time) what units
>> >are "tim=" in? (ie how many seconds between tim=131853898 and
>> >tim=131853270).
>> >
>> >This SE 8.1.7.4.12 on Windows 2000.
>> >
>> >Thank you
>> >
>> >T¬
>> >
>> >PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270
>> >hv=2073223040 ad='8e9a2080'
>> >DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1
>> >END OF STMT
>> >PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270
>> >WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0
>> >EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270
>> >XCTEND rlbk=0, rd_only=0
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1
>> >WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1
>> >WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1
>> >WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1
>> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1
>> >...
>> >WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7
>> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1
>> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7
>> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1
>> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3
>> >FETCH 
>#14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898
>> >--
>> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> >--
>> >Author: Tim Onion

RE: DBA Support Database