RE: Problem with understanding Optimization methods.

[Denham Eva]

Thank You!

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


Yes.
On 2004.01.08 01:14, Denham Eva wrote:
 Mladen
 
 Thank You for this suggestion, works in that the CBO now see it this
way
 
 SELECT STATEMENT Optimizer Mode=CHOOSE
   TABLE ACCESS BY INDEX ROWID TABLENAME
 INDEX FULL SCAN   TABLENAME_NDX
 
 Can these parameters be set in the init.ora? 
 
 Many Thanks Once Again!
 Denham
 
 -Original Message-
 Sent: Wednesday, January 07, 2004 8:09 AM
 To: Multiple recipients of list ORACLE-L
 
 
 You can find out by employing  the event 10053, lev 8.  Looking from afar,
 however, it seems more
 likely that you haven't configured your CBO properly. Here is something
you
 can try:
 
 Execute the following commands:
 
 alter session set optimizer_index_caching=40;  
 alter session set optimizer_index_cost_adj=25;
 
 
 
 After that,  retry the query. If I'm correct, optimizer will now know that
 index I/O is much cheaper
 then the table one and will be much more likely to select full index scan
 over the full table scan.
 When you're really, really bored, you can read Practical Oracle 8i -
 Building Efficient Databases,
 it has a few pages about the parameters above.  Read the Gospel of
Jonathan
 and enjoy.
 
 
 
 On 2004.01.07 00:29, Denham Eva wrote:
  Hello Listers,
  
  A normal sql query from a data warehouse tool called Sagent. 
  SELECT COL1, COL2, COL3
  FROM TABLE
  ORDER BY 3;
  
  The table has approximately 2 mil records.
  table has 22 indexes.
  
  The database is set up optimizer CHOOSE.
  I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
  OS is Win2k
  ORACLE 81741
  
  OK, when doing a explain plan on the above sql, I get the following...
  SELECT STATEMENT Optimizer Mode=CHOOSE
  SORT ORDER BY
   TABLE ACCESS FULL  TABLENAME   -- Very slow and
takes
  hours!
  
  When adding the hint /*+RULE*/ for example I get
  SELECT STATEMENT Optimizer Mode=Hint:RULE
 TABLE ACCESS BY INDEX ROWID  TABLENAME
 INDEX FULL SCAN   TABLE_INDEX  --
  Much faster!!!
  
  Have I given enough info that anyone can explain why the CHOOSE mode
 insists
  on doing a TABLE ACCESS FULL?
  Is there anything I can do to improve performance? Please remember that
 this
  query comes from a Data Warehouse tool and hence does not appear to
accept
  hints.
  
  Any help will be much appreciated!
  Denham
   
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Denham Eva
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  
 
 -- 
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: 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: Denham Eva
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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

RE: Problem with understanding Optimization methods.

[Denham Eva]

Hear, hear!

Wolfgang,
Without wanting to appear really dense here. But, how about putting some
titles and surnames to that list of yours? 
As much as I would love to buy books, with our exchange rate and import
taxes, it becomes very expensive!
But I do have a To Get list that I like to update.

regards
Denham

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


On that I really, really have to disagree with you.

Jonathan's book is not something to read When you're really, really 
bored. You should read it when you're wide awake and eager to learn. Short 
of a database that's in pieces on the floor I can't think of anything that 
should have higher priority. And once you're done with it, continue with 
James (Morle's), Cary's, Steve's, Gaja's and Tom's books ( listed order is 
random ).

At 11:14 PM 1/7/2004, you wrote:
When you're really, really bored, you can read Practical Oracle 8i -
Building Efficient Databases,

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

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com 

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

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

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

RE: Problem with understanding Optimization methods.

[Denham Eva]

Mladen

Thank You for this suggestion, works in that the CBO now see it this way

SELECT STATEMENT Optimizer Mode=CHOOSE
  TABLE ACCESS BY INDEX ROWID TABLENAME
INDEX FULL SCAN   TABLENAME_NDX

Can these parameters be set in the init.ora? 

Many Thanks Once Again!
Denham

-Original Message-
Sent: Wednesday, January 07, 2004 8:09 AM
To: Multiple recipients of list ORACLE-L


You can find out by employing  the event 10053, lev 8.  Looking from afar,
however, it seems more
likely that you haven't configured your CBO properly. Here is something you
can try:

Execute the following commands:

alter session set optimizer_index_caching=40;  
alter session set optimizer_index_cost_adj=25;



After that,  retry the query. If I'm correct, optimizer will now know that
index I/O is much cheaper
then the table one and will be much more likely to select full index scan
over the full table scan.
When you're really, really bored, you can read Practical Oracle 8i -
Building Efficient Databases,
it has a few pages about the parameters above.  Read the Gospel of Jonathan
and enjoy.



On 2004.01.07 00:29, Denham Eva wrote:
 Hello Listers,
 
 A normal sql query from a data warehouse tool called Sagent. 
 SELECT COL1, COL2, COL3
 FROM TABLE
 ORDER BY 3;
 
 The table has approximately 2 mil records.
 table has 22 indexes.
 
 The database is set up optimizer CHOOSE.
 I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
 OS is Win2k
 ORACLE 81741
 
 OK, when doing a explain plan on the above sql, I get the following...
 SELECT STATEMENT Optimizer Mode=CHOOSE
 SORT ORDER BY
  TABLE ACCESS FULL  TABLENAME   -- Very slow and takes
 hours!
 
 When adding the hint /*+RULE*/ for example I get
 SELECT STATEMENT Optimizer Mode=Hint:RULE
TABLE ACCESS BY INDEX ROWID  TABLENAME
INDEX FULL SCAN   TABLE_INDEX  --
 Much faster!!!
 
 Have I given enough info that anyone can explain why the CHOOSE mode
insists
 on doing a TABLE ACCESS FULL?
 Is there anything I can do to improve performance? Please remember that
this
 query comes from a Data Warehouse tool and hence does not appear to accept
 hints.
 
 Any help will be much appreciated!
 Denham
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Denham Eva
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 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: Denham Eva
  INET: [EMAIL PROTECTED]

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

[Denham Eva]

Hello Listers,

A normal sql query from a data warehouse tool called Sagent. 
SELECT COL1, COL2, COL3
FROM TABLE
ORDER BY 3;

The table has approximately 2 mil records.
table has 22 indexes.

The database is set up optimizer CHOOSE.
I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
OS is Win2k
ORACLE 81741

OK, when doing a explain plan on the above sql, I get the following...
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT ORDER BY
 TABLE ACCESS FULL  TABLENAME   -- Very slow and takes
hours!

When adding the hint /*+RULE*/ for example I get
SELECT STATEMENT Optimizer Mode=Hint:RULE
   TABLE ACCESS BY INDEX ROWID  TABLENAME
   INDEX FULL SCAN   TABLE_INDEX  --
Much faster!!!

Have I given enough info that anyone can explain why the CHOOSE mode insists
on doing a TABLE ACCESS FULL?
Is there anything I can do to improve performance? Please remember that this
query comes from a Data Warehouse tool and hence does not appear to accept
hints.

Any help will be much appreciated!
Denham
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

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

Oracle and Active Directory

[Denham Eva]

Hello List,

The Company I work for is planning to go the Active Directory route. Does
anyone know of any issues with the following versions of Oracle 7.3.x.x and
8.1.7.x.x?

Regards
Denham Eva

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

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

MSSQL Link Server connection failure to Oracle 817

[Denham Eva]

Hello,

To my surprise I see there alot of us on this list, use MSSQL and Oracle in
the same environment.

So please forgive me for asking this but it is a huge issue here at my work.
The problem is like this, we have a MSSQL 2000 box connecting to Oracle 817,
via Linked servers using OLEDB. The jobs will run fine for awhile, but then
suddenly fail with the following error

Executed as user: TFMC\Administrator. OLE DB provider 'MSDASQL' reported an
error. [SQLSTATE 42000] (Error 7399)  Driver's SQLSetConnectAttr failed]
[SQLSTATE 01000] (Error 7312)  OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBInitialize::Initialize returned 0x80004005:   ]. [SQLSTATE 01000] (Error
7300).  The step failed.

OK, this is specifically the MS driver being used here, but when Oracle
drivers are used, we have the same issues.
Both Databases are on HP/Compaq servers, and the Windows 2000 platform.
I have loaded the newest patches for OLEDB on the MSSQL for the Oracle
Client, but nothing helps.

Has anyone experienced this issue before?

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

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

RE: MSSQL Link Server connection failure to Oracle 817

[Denham Eva]

Thank you so much, I'll get our SQL Server admin to look into your
suggestions.

Regards
Denham

 -Original Message-
 From: Grant Allen [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, October 29, 2003 12:14 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: MSSQL Link Server connection failure to Oracle 817
 
 
  -Original Message-
  From: Denham Eva [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, 28 October 2003 22:09
  To: Multiple recipients of list ORACLE-L
  Subject: MSSQL Link Server connection failure to Oracle 817
  
  
  Hello,
  
  To my surprise I see there alot of us on this list, use MSSQL 
  and Oracle in the same environment.
 
 Guilty.  (I promise I won't mention DB2 as well)
 
  So please forgive me for asking this but it is a huge issue 
  here at my work.
  The problem is like this, we have a MSSQL 2000 box connecting 
  to Oracle 817, via Linked servers using OLEDB. The jobs 
 will run fine for 
  awhile, but then suddenly fail with the following error
  
  Executed as user: TFMC\Administrator. OLE DB provider 
  'MSDASQL' reported an error. [SQLSTATE 42000] (Error 7399)  
 Driver's 
  SQLSetConnectAttr failed]
  [SQLSTATE 01000] (Error 7312)  OLE DB error trace [OLE/DB 
  Provider 'MSDASQL' IDBInitialize::Initialize returned 
 0x80004005:   ]. [SQLSTATE 
  01000] (Error 7300).  The step failed.
  
  OK, this is specifically the MS driver being used here, but 
  when Oracle drivers are used, we have the same issues.
  Both Databases are on HP/Compaq servers, and the Windows 2000 
  platform. I have loaded the newest patches for OLEDB on the 
 MSSQL for the Oracle
  Client, but nothing helps.
  
  Has anyone experienced this issue before?
 
 Denham,
 
 From my experience, you're in for a torrid time.  The 
 0x80004005 error is returned from the Win32 load library call 
 (can't remember the exact method name, but basically it's the 
 standard load this DLL call).  0x80004005 means ... wait 
 for it ... FAILED.  That's it.  That's all MS wrote for this error.
 
 As you can see from your error dump, the ::Initialize method 
 was being called, which implicitly loads the provider (and 
 thus the DLL).
 
 The other thing I notice from your error is that you are NOT 
 using the MS or Oracle native providers.  You are using the 
 OLEDB to ODBC bridge (MSDASQL ... instead of MSDAORA or 
 OraOLEDB).  This is a dog's breakfast.  Try changing to 
 whichever of the other two you haven't tried, and see if the 
 error persists.
 
 Ciao
 Fuzzy
 :-)
 
 --
 The contents of this post are my opinions only
   If swallowed seek medical advice 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Grant Allen
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (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: Denham Eva
  INET: [EMAIL PROTECTED]

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

Archivelog - Disk space issue.

[Denham Eva]

Hello Gurus,

Have some disk space issues, so looking for ways to cleanup.

Please confirm that my thinking is correct.
I run the command and example output below:-
SQL archive log list
Database log mode  Archive Mode
Automatic archival Enabled
Archive destinationC:\Oracle\oracle.
Oldest online log sequence 2334
Next log sequence to archive   2338
Current log sequence   2338

I can write a script (Perl) to remove all archive files  2334?

Much appreciate any input or confirmation.

Regards
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!

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

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

RMAN in NOCATALOG mode?

[Denham Eva]

Hello

Wondering about this. If backing up the database with RMAN in NOCATALOG
mode.
When would be the best time to do backup of the controlfile? Before backup /
after backup /before and after backup.
Does this aid your recovery from a crash? What advantage would it have?

Many Thanks
Regards
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!

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

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

Oracle not available???

[Denham Eva]

Hello Gurus

I had a weird one to day, all access to Oracle on our oracle database was
suddenly lost
When you try to connect it says that Oracle is on available.
However the DB has been up for more than 70 days. The other thing the
service was running and the Oracle process and listener was running?
No errors in the logs, nothing.
Server: Win2k, Oracle 817.


Has anyone experienced this before???

TIA
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!


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

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

[Denham Eva]

Thanks Patrice,

I'll look into this, however I find it way strange that the server has been
running no problems for almost three months. Maybe a suggestion from one of
the other listers is very pertinant - reboot bi-weekly perhaps. However I
hear from my virus admin that we were attacked by another worm yesterday and
also that it was not only my system that lost connections, the other system
was a SQLserver machine. So makes me wonder.
Anyway Patrice, I'll look into your suggestion, purely because I was did not
know of it before, and thanks for the info.

PS when you mention mem utilization stats - are thinking of the ones
generated by the windows monitoring utility?

Best Regards
Denham




Check in Task Manager, Performance Tab...

upper right corner, Physical Memory (K) Total = 
lower left corner, Commit Charge (K) Peak = 

Commit Charge Peak should be less than half physical RAM, otherwise users
may not be able to connect.

Oracle can't be swapped to disk in Windows because it updates the data block
and other headers regularly, and it's too fast for the Windows virtual
memory manager.  I don't know if this is the case only for busy databases,
but I bumped into that here.

Windows splits memory equally between kernel and user processes, that means
you get to use about half the available RAM on your machine.  

User sessions can usually be swapped to disk (I think), if they are
inactive.

I would be curious to hear what memory utilization stats you have on the
machine.

Patrice.

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

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

RE: DECODE or not to DECODE

[Denham Eva]

Thanks To all for the support!
I managed to use a combination of thesuggestions which worked great!

Denham

-Original Message-
Sent: Monday, August 04, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Hello Listers,

I need some help please,
I am trying to create a DECODE statement, on a date column and looks
something like this, but does not work.
SELECT   COL1,
COL2,
DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30,
DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days',
NULL) Days60,
..
..
FROM ACCOUNT_TBL
WHERE COL = '0'

Obviously, I have worked out that this can not be done. The problem is also
that the server is 7.3.4 and I need to use the code in a view.
Does anyone have any solutions/work arounds for this?
Any help will be appraciated.

TIA
Denham Eva
Oracle DBA


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com

_


#
Note:
This message is for the named person's use only.  It may contain
confidential,
proprietary or legally privileged information.  No confidentiality or
privilege
is waived or lost by any mistransmission.  If you receive this message in
error,
please immediately delete it and all copies of it from your system, destroy
any
hard copies of it and notify the sender.  You must not, directly or
indirectly,
use, disclose, distribute, print, or copy any part of this message if you
are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

For more information please visit www.marshalsoftware.com

_

_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

DECODE or not to DECODE

[Denham Eva]

Hello Listers,

I need some help please,
I am trying to create a DECODE statement, on a date column and looks
something like this, but does not work.
SELECT   COL1,
COL2,
DECODE((SYSDATE - COL_DATE), = 30, '30_days', NULL) Days30,
DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days',
NULL) Days60,
...
...
FROM ACCOUNT_TBL
WHERE COL = '0'

Obviously, I have worked out that this can not be done. The problem is also
that the server is 7.3.4 and I need to use the code in a view.
Does anyone have any solutions/work arounds for this?
Any help will be appraciated.

TIA
Denham Eva
Oracle DBA

_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

RE: On TRUNCATE table does the indexes also get truncated.

[Denham Eva]

Title: RE: On TRUNCATE table does the indexes also get truncated.



Hello,

I 
deserved that the comments that were made, it is after all logical that they 
will be cleaned out. However in my defence, there are some funny thoughts 
thatwent through through my mind just before I truncated that table 
:-)

Thanks 
anyway for them

Regards
Denham

This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 





Note:
This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No 
confidentiality or privilege is waived or lost by any mistransmission. If 
you receive this message in error,please immediately delete it and all 
copies of it from your system, destroy any hard copies of it and notify the 
sender. You must not, directly or indirectly, use, disclose, distribute, 
print, or copy any part of this message if you are not the intended recipient. 
TFMC and any of its subsidiaries 
each reserve the right to monitor all e-mail communications through its 
networks.

Oracle to MSSQL conversion?

[Denham Eva]

Hello,

There is alot of talk about multi-skilling/reskilling going on here at my
company. We use Oracle and MSSQL dbs. 
I have been wondering if anyone is aware of a resource that shows the oracle
programming technique and what the corresponding MSSQL programming technique
is.
ie
Oracle defines variable   : MSSQL defines variable
Oracle cursor looks like this   : MSSQL cursor looks like this.
Oracle uses rollbacks: MSSQL uses MSSQL rollbacks
etc
Hope you all understand what I mean.

TIA
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

On TRUNCATE table does the indexes also get truncated.

[Denham Eva]

Hello,

When you truncate a table using:-

TRUNCATE TABLE TEMP;

does any of the indexes on the table also get truncated?

TIA
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

Another OCP slant...

[Denham Eva]

Hello Folks,

I have discovered something *very* disturbing. We have a person(x2) who has
earned their MCDBA - Microsoft DBA certifiction.
These two don't have any clue, about database issues, when pressed to how
they did it, it came out that they used the threads on a website called
www.braindumps.com
Beyond the ethical implications and all the other issues. 
Has anyone on this list used the tips/suggestions to study for their OCP
exams? Are they any good as an aid?

Regards
Denham Eva
Oracle DBA
Linux like TeePee... No Windows, No Gates and Apache inside!


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

RE: Dedicated Server problem ORA-03113

[Denham Eva]

That is the whole problem, there is no logged errors, or trace files.
Everyone can connect via the MTS servers, no problem, however no one can
connect via dedicated server. Go figure.

Regards
Denham

-Original Message-
Sent: Saturday, June 28, 2003 6:54 PM
To: Multiple recipients of list ORACLE-L


Hi!

What does your alert log say? Or event log then?
Maybe, for some reason, Oracle is unable to start new processes on your
system. For MTS connection, no new processes are needed, but for dedicated,
there are.

Tanel.

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, June 28, 2003 12:24 PM


 Hello Gurus,

 I am at wits end. The enviroment is Windows 2000 SP3. Oracle 817, MTS is
 also set up.
 The server has been up for 32 days. Everything fine and then suddenly all
 the power users using dedicated server because of their resource
intensive
 sql etc just cann't connect.
 We get this error:

 ORA-03113: end-of-file on communication channel

 I have tried reloading, stopping and starting the Listener, but to no
avail.
 There are only two dedicated sessions connected to the server and it still
 refuses any connections, and I have seen up to nine dedicated server
 connections before.

 Anyone experience this before?

 Regards
 Denham Eva
 Oracle DBA




_
 This e-mail message has been scanned for Viruses and Content and cleared
 by MailMarshal

 For more information please visit www.marshalsoftware.com


_



#
 Note:
 This message is for the named person's use only.  It may contain
confidential,
 proprietary or legally privileged information.  No confidentiality or
privilege
 is waived or lost by any mistransmission.  If you receive this message in
error,
 please immediately delete it and all copies of it from your system,
destroy any
 hard copies of it and notify the sender.  You must not, directly or
indirectly,
 use, disclose, distribute, print, or copy any part of this message if you
are not
 the intended recipient. TFMC and any of its subsidiaries each reserve
 the right to monitor all e-mail 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.

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

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

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

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those

RE: Online tech books

[Denham Eva]

Wow Bob,

That is very impressive! 
See many of my books mirrored among yours, so you must have good taste ;-)

Regards
Denham

-Original Message-
Sent: Monday, June 30, 2003 4:40 PM
To: Multiple recipients of list ORACLE-L


All..

I just returned frm vacation, but I wanted to respond to Jonathan
Gennick regarding my collection of books.( I managed to delete that
message) But, Ive taken some pictures of my collection.. Ive actually
purchaced all the books you see!
http://162.42.213.232/books/index.html


I would just like all to know that my initial recommendation to view
free books online was not an offer to circumvent actually purchacing the
books. Personally I prefer to actually have a hard copy in my hand for a
reference, on the other hand I though the online books may be handy and
hopefully spur individuals to purchace  books they otherwise may not be
aware of, and possibly maybe for a struggling fellow who doesnt have a
dime but has an interest to learn may pick up something worthwhile

Ahhh back to work

Best to all
bob




 And, more than likely, highly illegal.  I was assume the 
 authors on list list will be contacting their respective publishers?
 
 Rich
 
 Rich Jesse   System/Database Administrator
 [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
 
 
  -Original Message-
  From: Bob Metelsky [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, June 18, 2003 11:04 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Online tech books
  
  
  This was just passed on to me and I'd like to share it wit 
 the list...
  
  http://www.palaydium.net/tech/
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jesse, Rich
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
 and in the message BODY, include a line containing: UNSUB 
 ORACLE-L (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: Bob Metelsky
  INET: [EMAIL PROTECTED]

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

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

RE: Dedicated Server problem ORA-03113

[Denham Eva]

Yeah, so I have discovered when looking on metalink :-)
Nice of you to answer anyway, appreciate it.

Denham

-Original Message-
Sent: Monday, June 30, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L


ORA-3113 error is not very easy to track down. It's like ORA-600, but
relates to issues in N/W
communication with Oracle datbases. There are a number of notes/articles on
Metalink discussing
this error. Hopefully some of that information helps you. 

- Kirti 


--- Denham Eva [EMAIL PROTECTED] wrote:
 That is the whole problem, there is no logged errors, or trace files.
 Everyone can connect via the MTS servers, no problem, however no one can
 connect via dedicated server. Go figure.
 
 Regards
 Denham
 
 -Original Message-
 Sent: Saturday, June 28, 2003 6:54 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi!
 
 What does your alert log say? Or event log then?
 Maybe, for some reason, Oracle is unable to start new processes on your
 system. For MTS connection, no new processes are needed, but for
dedicated,
 there are.
 
 Tanel.
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Saturday, June 28, 2003 12:24 PM
 
 
  Hello Gurus,
 
  I am at wits end. The enviroment is Windows 2000 SP3. Oracle 817, MTS is
  also set up.
  The server has been up for 32 days. Everything fine and then suddenly
all
  the power users using dedicated server because of their resource
 intensive
  sql etc just cann't connect.
  We get this error:
 
  ORA-03113: end-of-file on communication channel
 
  I have tried reloading, stopping and starting the Listener, but to no
 avail.
  There are only two dedicated sessions connected to the server and it
still
  refuses any connections, and I have seen up to nine dedicated server
  connections before.
 
  Anyone experience this before?
 
  Regards
  Denham Eva
  Oracle DBA
 
 
 
 

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  INET: [EMAIL PROTECTED]

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

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

Dedicated Server problem ORA-03113

[Denham Eva]

Hello Gurus,

I am at wits end. The enviroment is Windows 2000 SP3. Oracle 817, MTS is
also set up.
The server has been up for 32 days. Everything fine and then suddenly all
the power users using dedicated server because of their resource intensive
sql etc just cann't connect.
We get this error:

ORA-03113: end-of-file on communication channel

I have tried reloading, stopping and starting the Listener, but to no avail.
There are only two dedicated sessions connected to the server and it still
refuses any connections, and I have seen up to nine dedicated server
connections before.

Anyone experience this before?

Regards
Denham Eva
Oracle DBA


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail 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.

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

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

Which is beter a cursor or a for loop?

[Denham Eva]

Title: Which is beter a cursor or a for loop?





Hello,


I was just asked by one of our developers which is beter to use:-
a cursor or a for loop?
I must admit I am not sure


Anyway the specific piece of code in discussion is similar to the following
 
 FOR X IN (SELECT X FROM TABLE_NAME
   WHERE COL1 = 'Something'))
 LOOP
 Do a whole lot of stuff in database here..
 LOOP END;


I would guess that the cursor would follow similar execution criteria but using 
the cursor syntax.


Any ideas?


TIA
regards
Denham Eva
Oracle DBA
The real problem is not whether machines think but whether men do.
- B. F. Skinner






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Which is beter a cursor or a for loop?

[Denham Eva]

Title: RE: Which is beter a cursor or a for loop?





Thanks everyone! 


Regards
Denham


-Original Message-
From: Connor McDonald [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 24, 2003 4:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Which is beter a cursor or a for loop?



If you are after the n'th degree performance then the:


for x in (select ... ) 


will be minisculely faster (simply because its
slightly less code and plsql is interpreted). And
unless I have a particular need for the cursor
%attributes, or the cursor needs to be passed around I
prefer the sql directly in the for-loop. I don't have
to hunt up through the procedure/package to find the
cursor definition, and (subjectively) I find it easier
to read.


hth
connor


--- Tim Gorman [EMAIL PROTECTED] wrote:  Both use
cursors, but a FOR loop is more concise
 coding. Technically, they
 are exactly equivalent; the differences are just
 stylistic...
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Sent: Monday, February 24, 2003 3:08 AM
 
 
  I would suggest that the cursor is the best way to
 go.
  -Original Message-
  Sent: 24 February 2003 08:39
  To: Multiple recipients of list ORACLE-L
 
 
 
  Hello,
 
  I was just asked by one of our developers which is
 beter to use:-
  a cursor or a for loop?
  I must admit I am not sure
 
  Anyway the specific piece of code in discussion is
 similar to the
  following
 
  FOR X IN (SELECT X FROM TABLE_NAME
  WHERE COL1 = 'Something'))
  LOOP
  Do a whole lot of stuff in
 database here..
  LOOP END;
 
  I would guess that the cursor would follow similar
 execution criteria but
  using
  the cursor syntax.
 
  Any ideas?
 
  TIA
  regards
  Denham Eva
  Oracle DBA
  The real problem is not whether machines think but
 whether men do.
  - B. F. Skinner
 
 
  _
 
  DISCLAIMER
 
  This message is for the named person's use only.
 It may contain
  confidential, proprietary or legally privileged
 information. No
  confidentiality or privilege is waived or lost by
 any mistransmission. If
  you receive this message in error, please
 immediately delete it and all
  copies of it from your system, destroy any hard
 copies of it and notify
 the
  sender. You must not, directly or indirectly, use,
 disclose, distribute,
  print, or copy any part of this message if you are
 not the intended
  recipient. TFMC, its holding company, and any of
 its subsidiaries each
  reserve the right to monitor and manage all e-mail
 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 views of any such entity.
 
  _
 
 
  _
 
  This e-mail message has been scanned for Viruses
 and Content and cleared
 by
  MailMarshal - For more information please visit
  http://www.marshalsoftware.com
 www.marshalsoftware.com
  _
 
  --
  Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
  --
  Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]
 
  Fat City Network Services -- 858-538-5051
 http://www.fatcity.com
  San Diego, California -- Mailing list and
 web hosting services
 

-
  To REMOVE yourself from this mailing list, send an
 E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
  the message BODY, include a line containing: UNSUB
 ORACLE-L
  (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: 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

-
 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from). You may
 also send the HELP command for other information
 (like subscribing).
 


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


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

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


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

RE: ORA-04098 Problem - Any Ideas??

[Denham Eva]

Title: ORA-04098 Problem - Any Ideas??



Thank 
You, Banarasi Babu that worked fine.

Regards
Denham


  -Original Message-From: BanarasiBabu Tippa 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 30, 
  2003 9:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: ORA-04098 Problem - Any 
  Ideas??
  Hi 
  Denham EVa
  
  Try
  
  SQL ALTER TRIGGER SYS.JIS$ROLE_TRIGGER$ COMPILE ; -- If recompilation fails or 
  if you are still not able to connect, disable the trigger SQL ALTER 
  TRIGGER SYS.JIS$ROLE_TRIGGER$ DISABLE ; 
  then your 
command
  
  thanks
  Banarasi Babu
  
-Original Message-From: Denham Eva 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 30, 2003 11:44 
AMTo: Multiple recipients of list ORACLE-LSubject: 
ORA-04098 Problem - Any Ideas??
Hello List, 
I get this error when trying to drop and index, 
this as the schema owner. 
ERROR at line 1: ORA-04098: trigger 'SYS.JIS$ROLE_TRIGGER$' is invalid and failed 
re-validation 
The system is Oracle 817 on Win2000 
Does anyone know how to fix this? 
Funny enough the command ALTER INDEX 
indexname REBUILD; works fine??? 
TIA Regards Denham Eva 
Oracle DBA "UNIX is basically a simple operating 
system, but you have to be a genius to understand the 
simplicity." Dennis 
Ritchie. 


DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately 
delete it and all copies of it from your system, destroy any hard copies of 
it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if you are not 
the intended recipient. TFMC, its holding company, and any of its 
subsidiaries each reserve the right to monitor and manage all e-mail 
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 views of any such 
entity. 




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - For more 
information please visit www.marshalsoftware.com 


  



DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Perl

[Denham Eva]

Title: RE: Perl



Good 
question Raj! Hey Steve give us the run down on connecting to Oracle with 
Python.
I've 
downloaded the Activestate version to try!

Denham

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 29, 
  2003 4:04 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Perl
  Okay ... a question from a colleague ... 
  How do you get python to work with Oracle ... for perl there 
  DBD: and DBI: anything similar in Python? My knowledge of Perl is as 
  good as my knowledge of Python ... /dev/null
  Raj __ Rajendra Jamadagni 
   MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: Glenn 
  Stauffer [mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 28, 2003 5:17 PM To: Multiple recipients of list ORACLE-L Subject: Re: Perl 
  I tinkered with Perl, but could never really get used to the 
  syntax. I basically gave up (still maintain 
  familiarity since Perl is very common) and started 
  using Python. I've grown to enjoy coding in Python and use it now for all of the system maintenance and monitoring 
  scripts I write as well as for my web programming 
  work. 
  I'm not qualified to compare the two languages, but I will say 
  that Perl's Oracle support is better developed and the 
  CPAN archives are a very useful thing. In my 
  opinion, Python is a better designed language and it 
  is perfectly viable for production-level applications in an Oracle environment. 



DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

ORA-04098 Problem - Any Ideas??

[Denham Eva]

Title: ORA-04098 Problem - Any Ideas??





Hello List,


I get this error when trying to drop and index, this as the schema owner.


ERROR at line 1:
ORA-04098: trigger 'SYS.JIS$ROLE_TRIGGER$' is invalid and failed re-validation


The system is Oracle 817 on Win2000


Does anyone know how to fix this? 
Funny enough the command ALTER INDEX indexname REBUILD; works fine???


TIA
Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Error extensions in the alrtXXX.log

[Denham Eva]

Title: Error extensions in the alrtXXX.log



Thanks 
for your input.



  -Original Message-From: Robert Freeman 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, January 24, 2003 
  3:12 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Error extensions in the alrtXXX.log
  Look 
  in the Oracle Errors Manual, it will give you a list of all the prefixes for 
  the different kinds of errors.
  
  RF
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of Denham EvaSent: 
Friday, January 24, 2003 3:04 AMTo: Multiple recipients of list 
ORACLE-LSubject: Error extensions in the 
alrtXXX.log
Hello List, 
Pls help, I am busy playing with a Perl script 
which I want to read through the error log file each day and mail me when an 
error has occurred.
I have already created the part that will be 
backing up the alrtXXX.log to another file. So that it starts afresh each 
morning.
OK here are the obvious ones I personally can 
remember, please let me know of others that I can scan for. 
ORA- PLS- Errors IMP- 
Jared would probably have a far better script. 
Anyway how else dose one learn? without the proverbial "Roll up the sleeves 
and climb in under the hood" right? Hey, Jared I still intend to buy your 
book this year, perhaps in the next few months when I hopefully will get 
something back from the Taxman.
Regards 
    Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you 
have to be a genius to understand the simplicity." Dennis Ritchie. 


DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately 
delete it and all copies of it from your system, destroy any hard copies of 
it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if you are not 
the intended recipient. TFMC, its holding company, and any of its 
subsidiaries each reserve the right to monitor and manage all e-mail 
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 views of any such 
entity. 




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - For more 
information please visit www.marshalsoftware.com 


  



DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Error extensions in the alrtXXX.log

[Denham Eva]

Title: Error extensions in the alrtXXX.log





Hello List,


Pls help, I am busy playing with a Perl script which I want to read through the error log file each day and mail me when an error has occurred.

I have already created the part that will be backing up the alrtXXX.log to another file. So that it starts afresh each morning.

OK here are the obvious ones I personally can remember, please let me know of others that I can scan for.


ORA-
PLS-
Errors
IMP-


Jared would probably have a far better script. Anyway how else dose one learn? without the proverbial Roll up the sleeves and climb in under the hood right? Hey, Jared I still intend to buy your book this year, perhaps in the next few months when I hopefully will get something back from the Taxman.

Regards


Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: slowish query causing problems...

[Denham Eva]

Title: RE: slowish query causing problems...





Hello List,


Thanks Mark, the rownum = 1 did the trick, it chopped a full six seconds off most of the trials. 
Thank You to very one who contributed, the optimizer still insists to run use FULL rather than the indexes, even after analyzing the table.

Anyway the performance is substantially enhanced.


Regards
Denham


-Original Message-
From: Mark Richard [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 12:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: slowish query causing problems...



Denham,


Some others have already made good suggestions - including Thomas' reply
which I would like to extend upon...


Thomas suggested:


SELECT DISTINCT (1)
 FROM fwepcode1
 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2')
 OR not exists(select 1 FROM valuelist
 WHERE listname = 'STATUS'
 AND MAXVALUE = 'A'
 AND VALUE='INPRG' )


I would change it slightly to:


select 1
from fwepcode1
where ((wottype = 'TST' and func = 'CONEPRF' and exp = '2')
 or not exists (select 1 from valuelist
 where listname = 'STATUS'
 and maxvalue = 'A'
 and value = 'INPRG'))
and rownum = 1;


Using and rownum = 1 will cause it to use COUNT STOPKEY instead of SORT
AGGREGATE. The sort is an unnecessarily large piece of work to test for
existence. Perhaps this will also cause the optimisor to use an index -
NDX9 seems the ideal candidate. If it still doesn't use the index then
have you analyzed the table? Perhaps even try analyze table fwepcode1
compute statistics for table for all indexes for all indexed columns If
the indexes are all analyzed then try the hint  /*+ index( fewpcode1
fwepcode1_ndx9 ) */ . Let me know if it still refuses to use the index -
although if you reach this point then perhaps a full table scan is the best
approach, and the COUNT STOPKEY may be able to stop the FTS very quickly as
soon as it finds 1 matching row anyway.


Also, I know you mentioned that the indexes are defined by somebody else
but some of them look redundant - particularly NDX2 and NDX4 and maybe NDX9
if the columns are rearranged. Are a lot of updates/inserts/deletes
performed on fwepcode1? If so you might get some gain by removing some
indexes.


Regards,
 Mark.




 
 Denham Eva 
 [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
 za cc: 
 Sent by: Subject: RE: slowish query causing problems... 
 [EMAIL PROTECTED] 
 om 
 
 
 15/01/2003 
 01:49 
 Please respond 
 to ORACLE-L 
 
 





Hello,


Thanks for the replies
Here is the description of the table and the indexes, pls remember I have
removed and tested each index seperately, still insisted on a Full search.
9 indexes is not my idea of a perfect situation these are created by the
developers ( another company) so politics plays a big roll here.


CREATE TABLE FWEPCODE1 (
 RECORDID INTEGER NOT NULL,
 FUNC VARCHAR2 (20),
 WOTYPE VARCHAR2 (20),
 EXP VARCHAR2 (20),
 PIK VARCHAR2 (20),
 FUNCDESC VARCHAR2 (80),
 EXPDESC VARCHAR2 (80),
 PIKDESC VARCHAR2 (80),
 EX1 VARCHAR2 (1),
 EX2 VARCHAR2 (10),
 EX3 VARCHAR2 (10),
 EX4 VARCHAR2 (10),
 EX5 VARCHAR2 (10),
 EX6 VARCHAR2 (10),
 EX7 VARCHAR2 (10),
 EX8 VARCHAR2 (10),
 EX9 VARCHAR2 (10),
 EX10 VARCHAR2 (10) ) ;


FWEPCODE1_NDX1 ON FWEPCODE1(FUNC, WOTYPE, EXP, PIK);
FWEPCODE1_NDX2 ON FWEPCODE1(FUNC);
FWEPCODE1_NDX3 ON FWEPCODE1(EXP);
FWEPCODE1_NDX4 ON FWEPCODE1(FUNC, WOTYPE);
FWEPCODE1_NDX5 ON FWEPCODE1(FUNC, EX2);
FWEPCODE1_NDX6 ON FWEPCODE1(EXPDESC);
FWEPCODE1_NDX7 ON FWEPCODE1(FUNC, WOTYPE, PIK);
FWEPCODE1_NDX8 ON FWEPCODE1(RECORDID);
FWEPCODE1_NDX9 ON FWEPCODE1(WOTYPE, FUNC, EXP);


I have added a CSV file as an attachment as requested by one lister of the
plan_table.


Once again appreciation for all the help.
Regards
Denham
 -Original Message-
 From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
 Sent: Tuesday, January 14, 2003 3:24 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: slowish query causing problems...


 Eva,


 Is there an index on the fwepcode1 table with the three columns used
 in the where clause? Are the three columns varchar or varchar2? Make
 sure the EXP column is not a number!


 Secondly, I think I would change the query as follows:



 SELECT DISTINCT (1)
 FROM fwepcode1
 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP
 = '2')
 OR not exists(select 1 FROM valuelist
 WHERE listname = 'STATUS'
 AND MAXVALUE = 'A'
 AND VALUE='INPRG' )






 Tom Mercadante
 Oracle Certified Professional



 -Original Message-
 From: Denham Eva [mailto:[EMAIL PROTECTED]]
 Sent: Tuesday, January 14, 2003 6:29 AM
 To: Multiple recipients of list ORACLE-L
 Subject: slowish query causing problems...




 Hello List,



 Pls help me on this problem. Our application does a validation
 when it uses a certain screen, as it so happens this screen is
 used very intensively. The performance is very slow, I have
 isolated the main culprit. I have tried the following.



 I have dropped

slowish query causing problems...

[Denham Eva]

Title: slowish query causing problems...





Hello List,


Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following.

I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL.

This is very frustrating indeed.


SELECT DISTINCT (1)
 FROM fwepcode1
 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2')
 OR 'INPRG' NOT IN (SELECT VALUE
 FROM valuelist
 WHERE listname = 'STATUS'
 AND MAXVALUE = 'A')


Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)?
I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan.
Funny enough the sub query on valuelist table does use a index.
The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834.


The system is a Oracle 817 on Win2k.


Pls advise, any options or help will be appreciated.
Many Thanks
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: slowish query causing problems...

[Denham Eva]

Title: slowish query causing problems...



Hello,

Thanks 
for the replies
Here 
is the description of the table and the indexes, pls remember I have removed and 
tested each index seperately, still insisted on a Full search. 9 indexes is not 
my idea of a perfect situation these are created by the developers ( another 
company) so politics plays a big roll here.

CREATE 
TABLE FWEPCODE1 (  RECORDID 
INTEGER NOT NULL,  
FUNC VARCHAR2 (20),  
WOTYPE VARCHAR2 (20),  
EXP VARCHAR2 (20),  
PIK VARCHAR2 (20),  FUNCDESC 
VARCHAR2 (80),  EXPDESC VARCHAR2 (80),  
PIKDESC VARCHAR2 (80),  
EX1 VARCHAR2 (1),  
EX2 VARCHAR2 (10),  
EX3 VARCHAR2 (10),  
EX4 VARCHAR2 (10),  
EX5 VARCHAR2 (10),  
EX6 VARCHAR2 (10),  
EX7 VARCHAR2 (10),  
EX8 VARCHAR2 (10),  
EX9 VARCHAR2 (10),  
EX10 VARCHAR2 (10) ) ; 

FWEPCODE1_NDX1 ON FWEPCODE1(FUNC, WOTYPE, EXP, 
PIK);FWEPCODE1_NDX2 ON FWEPCODE1(FUNC); FWEPCODE1_NDX3 ON 
FWEPCODE1(EXP); FWEPCODE1_NDX4 ON FWEPCODE1(FUNC, WOTYPE); 
FWEPCODE1_NDX5 ON FWEPCODE1(FUNC, EX2); FWEPCODE1_NDX6 ON 
FWEPCODE1(EXPDESC); FWEPCODE1_NDX7 ON FWEPCODE1(FUNC, WOTYPE, PIK); 
FWEPCODE1_NDX8 ON FWEPCODE1(RECORDID); FWEPCODE1_NDX9 ON 
FWEPCODE1(WOTYPE, FUNC, EXP); 

I have 
added a CSV file as an attachment as requested by one lister of the 
plan_table.

Once 
again appreciation for all the help.
Regards
Denham

  -Original Message-From: Mercadante, Thomas F 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 
  3:24 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: slowish query causing problems...
  Eva,
  
  Is 
  there an index on the fwepcode1 table with the three 
  columns used in the where clause? Are the three columns varchar or 
  varchar2? Make sure the EXP column is not a 
  number!
  
  Secondly, I think I would change the query as 
  follows:
  
  
  SELECT DISTINCT (1) 
   FROM 
  fwepcode1  WHERE 
  (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') 
   
  ORnot exists(select 1 FROM 
  valuelist 
   
  WHERE listname = 'STATUS' 
   
  AND MAXVALUE = 'A'
   
  AND VALUE='INPRG' ) 
  
  Tom Mercadante Oracle Certified Professional 
  
-Original Message-From: Denham Eva 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:29 
AMTo: Multiple recipients of list ORACLE-LSubject: 
slowish query causing problems...
Hello List, 
Pls help me on this problem. Our application does 
a validation when it uses a certain screen, as it so happens this screen is 
used very intensively. The performance is very slow, I have isolated the 
main culprit. I have tried the following.
I have dropped all the indexes and tried 
recreating them individually. Each time I have run an explain plan on the 
query, the optimizer (both rule and Choose) have chosen to do a FULL table 
scan on the fwepcode table. Even when using a hint to explicitly use the 
index it still uses FULL.
This is very frustrating indeed. 
SELECT DISTINCT (1)  FROM 
fwepcode1  WHERE (wotype 
= 'TST' AND func = 'C0NEPRF' AND EXP = '2')  
OR 'INPRG' NOT IN (SELECT VALUE  
FROM valuelist  
WHERE listname = 'STATUS'  
AND MAXVALUE = 'A') 
Is the reason that the optimizer does not use any 
of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this 
and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a 
index. The table contains 8920 rows. The 
cost according to the explain plan is 703 and bytes 9834. 
The system is a Oracle 817 on Win2k. 
Pls advise, any options or help will be 
appreciated. Many Thanks 
Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a 
genius to understand the simplicity." Dennis Ritchie. 


DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately 
delete it and all copies of it from your system, destroy any hard copies of 
it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if you are not 
the intended recipient. TFMC, its holding company, and any of its 
subsidiaries each reserve the right to monitor and manage all e-mail 
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 views of any such 
entity. 




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - For more 
information please visit www.marshalsoftware.com 


  

RE: Setting profile limits?

[Denham Eva]

Title: Setting profile limits?



Thanks 
for the help, or should I say the lack of help :)
I took 
the disdainful silence from all of you, to mean that I had not done enough 
RTFM.
Anyway 
a little experimentation on the test system and Rachel's 101book managed 
to clear that problem.
Thanks 
Bernardus for your input.

Regards
Denham

  -Original Message-From: Bernardus Deddy Hoeydiono 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 09, 2003 10:29 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Setting profile limits?
  Dear 
  Denham Eva,
  
  Can 
  explain more detail about the profile limit ?
  
  Because, based on my experiance The profile limit on 
  oracle is wworking properly.
  Everything you have to create one profile first using 
  command line or you can create the profile using DBA studio or TOAD (third 
  party of Oracle from Quest Software).
  
   CREATE 
  PROFILE test LIMIT
   
  SESSIONS_PER_USERXXX
   CPU_PER_SESSIONXXX
   CPU_PER_CALLXXX
   CONNECT_TIMEXXX
   IDLE_TIMEXXX
  
  After you create the profile you just alter the 
  corresponding user to theprofile that you have set and 
  created.
  alter 
  userusername profile profile_name;
  After you finish all the things above, you can test. 
  And I believe it's working, becuase I have used 
  before.
  
  Thanks,
  Bernardus Deddy Hoeydiono.
  -Original 
  Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On 
  Behalf Of Denham EvaSent: Thursday, January 09, 2003 1:44 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  Setting profile limits?
  
Hello List, 
I am not sure I understand the documentation 
correctly, please help out here. Lets 
assume that I have set RESOURCE_LIMIT to TRUE. Now I set the parameter IDLE_TIME for the profile DEFAULT. 

Here is the question: Does that mean that all the other parameters ie CONNECT_TIME, 
SESSION_PER_USER, CPU_PER_SESSION etc remain the same as before the 
changes?
Or will I be forced to change or set all the 
parameters? 
Thanks in advance for the help. Regards Denham 
Eva Oracle 
DBA "UNIX is 
basically a simple operating system, but you have to be a genius to 
understand the simplicity." Dennis Ritchie. 


DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately 
delete it and all copies of it from your system, destroy any hard copies of 
it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if you are not 
the intended recipient. TFMC, its holding company, and any of its 
subsidiaries each reserve the right to monitor and manage all e-mail 
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 views of any such 
entity. 




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - For more 
information please visit www.marshalsoftware.com 


  



DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Setting profile limits?

[Denham Eva]

Title: Setting profile limits?





Hello List,


I am not sure I understand the documentation correctly, please help out here.
Lets assume that I have set RESOURCE_LIMIT to TRUE.
Now I set the parameter IDLE_TIME for the profile DEFAULT.


Here is the question:
Does that mean that all the other parameters ie CONNECT_TIME, SESSION_PER_USER, CPU_PER_SESSION etc remain the same as before the changes?

Or will I be forced to change or set all the parameters?


Thanks in advance for the help.
Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Finding User sessions = idle 30 min??

[Denham Eva]

Title: Finding User sessions = idle  30 min??





Hello All,


Please help - I am trying to find/create a script that will return all user sessions whose Idle time is greater than 30 minutes.

ie
 SELECT SID, SERIAL# 
 FROM V$SESSION
  WHERE IDLE_TIME  30 min;


My forays into the Documentation and searches have not been very successful. 
I don't really want to do this via the roles IDLE_TIME setting, I very much would like to be able to query directly to the database.

Based on the information I would then make the decision to kill the user process etc.
Just in case you might be interested it is Oracle 817 DB on Windows 2k. 


Best Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Pro*C for Oracle 817 on Win2000?

[Denham Eva]

Title: Pro*C for Oracle 817 on Win2000?





Hello,


Apologies if this is the wrong list to write to, but some guidelines would be nice.
We have a legacy software which requires changing. I would like to achieve this but as far as I can see there is no pro*c on otn.oracle.com for Win2k?

Is it possible to load the libraries from somewhere? I have tried loading the Programmer option of the client, however from the errors received I don't have all the libraries. ie sqlca.h.

I am trying to achieve this feat by using Bloodshed's DevC++. Am I being overly optimistic?
I see there are libraries for VC++ and Borland, under the c:\oracle\ora81\oci directories. Unfortunately I don't have access to these programs?

Any help will appreciated.


Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

SPIN_COUNT - 8i obsolete? What now?

[Denham Eva]

Title: SPIN_COUNT - 8i obsolete? What now?





Hello Guru's


I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. 

When trying to ALTER SYSTEM SET SPIN_COUNT = 3000;
Present system is set as 2000. I get the following error.
*
ERROR at line 1:
ORA-25138: SPIN_COUNT initialization parameter has been made obsolete


Now what? what can I do to overcome the sleeps issues?


Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: SPIN_COUNT - 8i obsolete? What now?

[Denham Eva]

Title: RE: SPIN_COUNT - 8i obsolete? What now?





Already running on 100% caffeine_level saturation :)
Maybe your right, the contention could be elsewhere, the other issue I have been investigating is LGWR process? With increasing the LOG_BUFFER from 512 to a Meg in size, as I have at times very high redo log switch, however they average about 3-4 an hour?

Anyway the idea is to keep investigating.


Regards
Denham


-Original Message-
From: Stephane Faroult [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 07, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: SPIN_COUNT - 8i obsolete? What now?



 Denham Eva wrote:
 
 Hello Guru's
 
 I have run a script which indicates that of the three redo log latches
 two had occurences of sleeps. To overcome this usually you increase
 SPIN_COUNT by 50% or so.
 
 When trying to ALTER SYSTEM SET SPIN_COUNT = 3000;
 Present system is set as 2000. I get the following error.
 *
 ERROR at line 1:
 ORA-25138: SPIN_COUNT initialization parameter has been made obsolete
 
 Now what? what can I do to overcome the sleeps issues?
 


Increase caffeine_level. Seriously, is it a problem? A real bottleneck?
You should begin to worry if you do not have worse contention elsewhere.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
 INET: [EMAIL PROTECTED]


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






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: SPIN_COUNT - 8i obsolete? What now?

[Denham Eva]

Title: RE: SPIN_COUNT - 8i obsolete? What now?





Ouch! :) 


Rachel,


It's not a case of me looking for problems. I was surprised to find this occuring so maybe doing something about it would improve the system alittle. I am looking into the redo log performance because I am unsure that the redo log switches are normal as they can be very high during office hrs as high as 60 switches in an hr, but having said that the switches avrg out at 3-4 switches an hour. So all the obvious are to be looked into ie checkpoints etc.

From your line of thought in the email below, I am reading into it that you don't find this to be an issue, which is great. I will then just carry on checking all the other possible parameters and stats, until I am happy that the redo logs are as healthy as they can be.

In my defence I did try the to change the parameter on my test system first.


Hope that explains my line of thought and modus operandi.
Regards
Denham


-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 07, 2002 3:18 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: SPIN_COUNT - 8i obsolete? What now?



there is a point at which you begin to tune for the sake of tuning and
not because you are relieving a problem.


if you are not getting reports of performance problems, why are you
trying to fix it? the old adage if it ain't broke, don't fix it
applies here.



--- Denham Eva [EMAIL PROTECTED] wrote:
 Hello Guru's
 
 I have run a script which indicates that of the three redo log
 latches two
 had occurences of sleeps. To overcome this usually you increase
 SPIN_COUNT
 by 50% or so. 
 When trying to ALTER SYSTEM SET SPIN_COUNT = 3000;
 Present system is set as 2000. I get the following error.
 *
 ERROR at line 1:
 ORA-25138: SPIN_COUNT initialization parameter has been made obsolete
 
 Now what? what can I do to overcome the sleeps issues?
 
 Regards
 Denham Eva
 Oracle DBA
 UNIX is basically a simple operating system, but you have to be a
 genius to
 understand the simplicity.
 Dennis Ritchie.
 
 

_
 DISCLAIMER
 This message is for the named person's use only. It may contain
 confidential,
 proprietary or legally privileged information. No confidentiality 
 or privilege is waived or lost by any mistransmission. If you receive
 
 this message in error, please immediately delete it and all copies 
 of it from your system, destroy any hard copies of it and notify the 
 sender. You must not, directly or indirectly, use, disclose, 
 distribute, print, or copy any part of this message if you are not 
 the intended recipient. TFMC, its holding company, and any of its 
 subsidiaries each reserve the right to monitor and manage all e-mail 
 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.


 

_
 This e-mail message has been scanned for Viruses and Content and
 cleared 
 by MailMarshal
 
 For more information please visit www.marshalsoftware.com

_
 



__
Do you Yahoo!?
U2 on LAUNCH - Exclusive greatest hits videos
http://launch.yahoo.com/u2
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
 INET: [EMAIL PROTECTED]


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






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail communications through its 
networks. 
Any views expressed in this message are those 
of the individual sender

RE: SPIN_COUNT - 8i obsolete? What now?

[Denham Eva]

Title: RE: SPIN_COUNT - 8i obsolete? What now?





Thanks Cary and all for the imput. Now I atleast have something to work with.
Many Thanks
Denham


-Original Message-
From: Cary Millsap [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 07, 2002 6:42 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SPIN_COUNT - 8i obsolete? What now?



Thanks Rachel.


For Denham: Changing SPIN_COUNT from its default value is usually a Very
Bad Thing. Your sleeps are caused by lots of competition for one or more
latches. The best way to stop those sleeps from impacting response time
is to cut out the unnecessary competition. Because of how we're all
taught to tune SQL (by eliminating physical I/Os and then quitting),
almost every system in the world suffers from unnecessarily excessive
numbers of latch acquisition attempts.


If the competition is for the shared pool or a library cache latch,
then your application parses too much. For more details, see Scaling
applications to massive user counts at www.hotsos.com/catalog. If the
competition is for a cache buffers chains or cache buffers lru chain
latch, then your application reads too many blocks from the buffer
cache. For more details, see Why you should focus on LIOs instead of
PIOs at www.hotsos.com/catalog.


Yes, increasing SPIN_COUNT can reduce the number of sleeps, but it does
so by wasting more user-mode CPU doing an operation called a busy
wait. See the LIO/PIO paper mentioned earlier for details about what
increasing SPIN_COUNT does to Oracle's latch acquisition algorithm.


Rachel's right: Instead of worrying whether there are too many sleeps on
your system, decide whether the business benefit of improving the
performance of one or more specific business functions will be worth the
effort. Direct performance improvement decisions by what's important to
the BUSINESS, not by what some V$ table says might be wrong with your
system.



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com


Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas



-Original Message-
Carmichael
Sent: Thursday, November 07, 2002 9:09 AM
To: Multiple recipients of list ORACLE-L


Denham,


Sorry, I didn't mean to make you wince! It's just a case of seeing way
too many DBAs spending way too much time tuning things that really
aren't a problem.


Cary Millsap talks about this often. Find the business problem and tune
that. It's possible that something that shows up as very slow when
you look at the overall system is actually something that runs rarely
and tuning it will give you no noticeable improvement. 


If the log switches are happening that frequently, you might look into
resizing them. I'd first look at what is going on during office hours
that's causing them to fill so fast. 


Rachel


--- Denham Eva [EMAIL PROTECTED] wrote:
 Ouch! :) 
 
 Rachel,
 
 It's not a case of me looking for problems. I was surprised to find
 this
 occuring so maybe doing something about it would improve the system
 alittle.
 I am looking into the redo log performance because I am unsure that
 the redo
 log switches are normal as they can be very high during office hrs as
 high
 as 60 switches in an hr, but having said that the switches avrg out
 at 3-4
 switches an hour. So all the obvious are to be looked into ie
 checkpoints
 etc.
 
 From your line of thought in the email below, I am reading into it
 that you
 don't find this to be an issue, which is great. I will then just
 carry on
 checking all the other possible parameters and stats, until I am
 happy that
 the redo logs are as healthy as they can be.
 
 In my defence I did try the to change the parameter on my test system
 first.
 
 Hope that explains my line of thought and modus operandi.
 Regards
 Denham
 
 -Original Message-
 Sent: Thursday, November 07, 2002 3:18 PM
 To: Multiple recipients of list ORACLE-L
 
 
 there is a point at which you begin to tune for the sake of tuning
 and
 not because you are relieving a problem.
 
 if you are not getting reports of performance problems, why are you
 trying to fix it? the old adage if it ain't broke, don't fix it
 applies here.
 
 
 --- Denham Eva [EMAIL PROTECTED] wrote:
  Hello Guru's
  
  I have run a script which indicates that of the three redo log
  latches two
  had occurences of sleeps. To overcome this usually you increase
  SPIN_COUNT
  by 50% or so. 
  When trying to ALTER SYSTEM SET SPIN_COUNT = 3000;
  Present system is set as 2000. I get the following error.
  *
  ERROR at line 1:
  ORA-25138: SPIN_COUNT initialization parameter has been made
 obsolete
  
  Now what? what can I do to overcome the sleeps issues?
  
  Regards
  Denham Eva
  Oracle DBA
  UNIX is basically a simple operating system, but you have to be a
  genius to
  understand the simplicity.
  Dennis Ritchie

RE: CPU Problem

[Denham Eva]

Title: RE: CPU Problem





Cary,


From the league of learners and eager apprentices. We eagerly devour any repetition on your part or that matter any other of the guru's. :)

But seriously I for one don't have an in house guru to whom I can lookup to for gems of wisdom and guidance. I am sure I am not alone in this predicament. So anything is far better than nothing!

Thanks for your contributions - all of you!
Denham


-Original Message-
From: Cary Millsap [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 08, 2002 12:44 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: CPU Problem



SQL that does excessively many reads from memory. Trace it. Read Why
you should focus on LIOs instead of PIOs at www.hotsos.com/catalog.


My apologies to the list for repeating myself like a broken record. :)



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com


Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas



-Original Message-
Alavi
Sent: Thursday, November 07, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L


Hi List,


We are facing with some CPU problem (cpu 100% usage!), Just want to get
some
idea from you guys, any clue what can cause this problem:
Here is a copy of some part of ora.ini
This happen during usage of some long query with subquery, this can be
the
cause of problem or just some wrong setting in ini file.
I appreciate your help.


compatible = 8.1.7.4
db_block_buffers = 1
db_block_lru_latches = 4
db_block_size = 8192
db_file_multiblock_read_count = 16
hash_area_size = 2048000
java_pool_size = 20971520
large_pool_size = 614400
log_buffer = 262144
log_checkpoint_interval = 1
log_checkpoint_timeout = 1800
max_enabled_roles = 30
open_cursors = 300
optimizer_index_caching = 90
optimizer_index_cost_adj = 35
os_authent_prefix = 
processes = 100
session_cached_cursors = 100
shared_pool_size = 134217728
sort_area_retained_size = 262144
sort_area_size = 262144
timed_statistics = TRUE






Hamid Alavi
Office 818 737-0526
Cell 818 416-5095







=== Confidentiality Statement
=== 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law. If you have received 
this message in error, you are prohibited from copying, distributing, or


using the information. Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement
= 



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


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


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


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






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses 

OCP Exams - What to study out of?

[Denham Eva]

Title: OCP Exams - What to study out of?





Hello Everyone,


1. I have now made the humungous decision to start studying and to write the OCP exams.
2. Do I study out of the Oracle Manuals? 
3. I do have the Sybex Study Guides, would studying these be all that is needed?
4. Or do the questions come out of the Course material ( Which I have not attended ).
5. I do have some of the Oreilly Insect Books - would these be any help?


Any views, opinions etc appreciated.
Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: OCP Exams - What to study out of?

[Denham Eva]

Title: OCP Exams - What to study out of?



Thanks 
to everyone for their input. I am considering the 8i track.
a. 
Because I work mainly with the 8i DB.
b. I 
hear that for the 9i a attendance of a course is a 
prerequisite.

Regards
Denham

  -Original Message-From: Denham Eva 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05, 2002 2:09 
  PMTo: Multiple recipients of list ORACLE-LSubject: OCP 
  Exams - What to study out of?
  Hello Everyone, 
  1. I have now made the humungous decision to start 
  studying and to write the OCP exams. 2. Do 
  I study out of the Oracle Manuals? 3. I do 
  have the Sybex Study Guides, would studying these be all that is 
  needed? 4. Or do the questions come out of 
  the Course material ( Which I have not attended ). 5. I do have some of the Oreilly "Insect" Books - would these be any 
  help? 
  Any views, opinions etc appreciated. 
  Regards Denham 
  Eva Oracle DBA 
  "UNIX is basically a simple 
  operating system, but you have to be a genius to understand the 
  simplicity." Dennis 
  Ritchie. 
  
  
  DISCLAIMER 
  
  This message is for the named person's use 
  only. It may contain confidential, proprietary or legally privileged 
  information. No confidentiality or privilege is waived or lost by any 
  mistransmission. If you receive this message in error, please immediately 
  delete it and all copies of it from your system, destroy any hard copies of it 
  and notify the sender. You must not, directly or indirectly, use, disclose, 
  distribute, print, or copy any part of this message if you are not the 
  intended recipient. TFMC, its holding company, and any of its subsidiaries 
  each reserve the right to monitor and manage all e-mail 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 views of any such entity. 
  
  

  
  
  This e-mail message has been scanned for Viruses and Content and cleared by 
  MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  
  
  
  
  
  DISCLAIMER 
  
  This message is for the named person's use 
  only. It may contain confidential, proprietary or legally privileged 
  information. No confidentiality or privilege is waived or lost by any 
  mistransmission. If you receive this message in error, please immediately 
  delete it and all copies of it from your system, destroy any hard copies of it 
  and notify the sender. You must not, directly or indirectly, use, disclose, 
  distribute, print, or copy any part of this message if you are not the 
  intended recipient. TFMC, its holding company, and any of its subsidiaries 
  each reserve the right to monitor and manage all e-mail 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 views of any such entity. 
  
  

  
  
  This e-mail message has been scanned for Viruses and Content and cleared by 
  MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  
  




DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Win2k Perl accessing Oracle Parameters?

[Denham Eva]

Title: RE: Win2k Perl accessing Oracle Parameters?





Thanks for the input, both of you!!!


-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 29, 2002 5:35 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Win2k Perl accessing Oracle Parameters?




In addition to BIshop's excellent response, be sure to install
the latest version of Tie:Registry. The one that comes with
ActiveState Perl doesn't work properly.


The current version (as installed from ActiveState via PPM) 
is I believe 1.24. Just reinstall it.


Jared


On Tuesday 29 October 2002 01:53, Bishop Lewis wrote:
 Here's something to read registry keys I used a while back. Obviously needs
 the win32 module installed.

 use Win32::Registry;

 $terminal = $ARGV[0];

 $HKEY_LOCAL_MACHINE-Connect ($terminal, $hNode) || die Cannot connect to
 $node;
 $hNode-Open (SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion\\Winlogon,
 $hKey) || die Cannot open registry !;
 $hKey-GetValues (\%values);
 $hKey-Close ();
 $hNode-Close ();

 foreach $value (keys(%values)) {
 $RegType = $values{$value}-[1];
 $RegValue = $values{$value}-[2];
 $RegKey = $values{$value}-[0];
 if ($RegKey eq DefaultUserName) {$username = $RegValue;
 print $RegValue\n;
 }
 }


 exit;

 Modify accordingly. HTH.

 Lewis Bishop
 ---
 Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
 Oracle Database Consultant
 Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
 Phone : 020 8298 3418
 Mobile: 07950 380857
 Email : [EMAIL PROTECTED]
 Enabling Competitive Advantage for Barclays in IT and Business Processing

 -Original Message-
 Sent: 29 October 2002 08:43
 To: Multiple recipients of list ORACLE-L

 This header confirms that this email message has been swept for the
 presence of computer viruses.

 Corporate IT
 THE WOOLWICH
 --
 Hello All,
 I am writing an perl script. I cann't figure out and have been going
 through the docs, but cann't find exactly what I need.
 A way to access Oracle Parameters as they are set up in the Win2k
 registary. I am specifically thinking of ORACLE_HOME. I do know how to
 obtain an environmental variable/parameter with $ENV{}.
 But the ORACLE_HOME is not an environmental parameter per se.
 I have a workaround by setting it up in the enviroment, however I would
 like to write this script with the aim at it being machine unspecific.
 (Jared, hope you can answer me on this. Want to get a copy of your book,
 unfortunately with the exchange rate and import taxes etc it will cost me
 R590,00. Quiet a chunk of the ole budget, maybe early next year.)
 Regards
 Denham Eva
 Oracle DBA
 UNIX is basically a simple operating system, but you have to be a genius
 to understand the simplicity.
 Dennis Ritchie.
 _

 DISCLAIMER
 This message is for the named person's use only. It may contain
 confidential, proprietary or legally privileged information. No
 confidentiality or privilege is waived or lost by any mistransmission. If
 you receive this message in error, please immediately delete it and all
 copies of it from your system, destroy any hard copies of it and notify the
 sender. You must not, directly or indirectly, use, disclose, distribute,
 print, or copy any part of this message if you are not the intended
 recipient. TFMC, its holding company, and any of its subsidiaries each
 reserve the right to monitor and manage all e-mail 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 views of any such entity.
 _

 _

 This e-mail message has been scanned for Viruses and Content and cleared by
 MailMarshal - For more information please visit
 http://www.marshalsoftware.com www.marshalsoftware.com
 _


 --_=_NextPart_001_01C27F2F.5CF0F180
 Content-Type: text/html
 Content-Transfer-Encoding: quoted-printable

 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN
 html xmlns:v=3Durn:schemas-microsoft-com:vml =
 xmlns:o=3Durn:schemas-microsoft-com:office:office =
 xmlns:w=3Durn:schemas-microsoft-com:office:word =
 xmlns:st1=3Durn:schemas-microsoft-com:office:smarttags =
 xmlns=3Dhttp://www.w3.org/TR/REC-html40

 head
 META HTTP-EQUIV=3DContent-Type CONTENT=3Dtext/html; =
 charset=3Dus-ascii


 meta name=3DProgId content=3DWord.Document
 meta name=3DGenerator content=3DMicrosoft Word 10
 meta name=3DOriginator content=3DMicrosoft Word 10
 link rel=3DFile-List href="3Dcid:[EMAIL PROTECTED]
 link rel=3DEdit-Time-Data href="3Dcid:[EMAIL PROTECTED]
 !--[if !mso]
 style
 v\:* {behavior:url(#default#VML);}
 o\:* {behavior:url(#default#VML);}
 w\:* {behavior:url(#default#VML);}
 .shape {behavior:url(#default#VML);}
 /style
 ![endif]--
 titleWin2k Perl accessing Oracle Parameters?/title
 o:SmartTagType =
 namespaceuri=3Durn:schemas-microsoft-com:office:smarttags
 name=3Ddate/
 o:Sm

Win2k Perl accessing Oracle Parameters?

[Denham Eva]

Title: Win2k Perl accessing Oracle Parameters?





Hello All,


I am writing an perl script. I cann't figure out and have been going through the docs, but cann't find exactly what I need.

A way to access Oracle Parameters as they are set up in the Win2k registary. I am specifically thinking of ORACLE_HOME. I do know how to obtain an environmental variable/parameter with $ENV{}.

But the ORACLE_HOME is not an environmental parameter per se.
I have a workaround by setting it up in the enviroment, however I would like to write this script with the aim at it being machine unspecific.

(Jared, hope you can answer me on this. Want to get a copy of your book, unfortunately with the exchange rate and import taxes etc it will cost me R590,00. Quiet a chunk of the ole budget, maybe early next year.)

Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: ROWID question?

[Denham Eva]

Title: ROWID question?



Thanks 
for everyones input! 

  -Original Message-From: Denham Eva 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 22, 2002 4:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: ROWID 
  question?
  Hello Gurus 
  Does Oracle reuse ROWID "numbers", once a delete of 
  a row has been done? 
  The reason I ask is because, I need to delete 
  duplicate records from a very large table. However I must keep the newest date in one of the date columns. 
  
  If oracle does not reuse the ROWID this would be 
  very simple. Here is my sql: 
  DELETE FROM tablename a WHERE ROWID  (SELECT MAX(ROWID) 
   
   
   FROM 
  tablename b  
   
   WHERE 
  a.col1 = b.col1  
   
   AND a.col2 
  = b.col2  
   
   AND a.col3 
  = b.col3); 
  However if Oracle does reuse ROWID's then I have a 
  serious sql problem :) All the examples I 
  have of deleteing duplicates use the ROWID. If anyone has a suggestion on how else to do this I would be most 
  grateful. 
  Otherwise answer on my main question would be 
  fine. Many Thanks Denham Eva 
  
  
  DISCLAIMER 
  
  This message is for the named person's use 
  only. It may contain confidential, proprietary or legally privileged 
  information. No confidentiality or privilege is waived or lost by any 
  mistransmission. If you receive this message in error, please immediately 
  delete it and all copies of it from your system, destroy any hard copies of it 
  and notify the sender. You must not, directly or indirectly, use, disclose, 
  distribute, print, or copy any part of this message if you are not the 
  intended recipient. TFMC, its holding company, and any of its subsidiaries 
  each reserve the right to monitor and manage all e-mail 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 views of any such entity. 
  
  

  
  
  This e-mail message has been scanned for Viruses and Content and cleared by 
  MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  
  
  
  
  
  DISCLAIMER 
  
  This message is for the named person's use 
  only. It may contain confidential, proprietary or legally privileged 
  information. No confidentiality or privilege is waived or lost by any 
  mistransmission. If you receive this message in error, please immediately 
  delete it and all copies of it from your system, destroy any hard copies of it 
  and notify the sender. You must not, directly or indirectly, use, disclose, 
  distribute, print, or copy any part of this message if you are not the 
  intended recipient. TFMC, its holding company, and any of its subsidiaries 
  each reserve the right to monitor and manage all e-mail 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 views of any such entity. 
  
  

  
  
  This e-mail message has been scanned for Viruses and Content and cleared by 
  MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  
  




DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

ROWID question?

[Denham Eva]

Title: ROWID question?





Hello Gurus


Does Oracle reuse ROWID numbers, once a delete of a row has been done?


The reason I ask is because, I need to delete duplicate records from a very large table.
However I must keep the newest date in one of the date columns.


If oracle does not reuse the ROWID this would be very simple. Here is my sql:


DELETE FROM tablename a
WHERE ROWID  (SELECT MAX(ROWID) 
   FROM tablename b
   WHERE a.col1 = b.col1
   AND a.col2 = b.col2
   AND a.col3 = b.col3);


However if Oracle does reuse ROWID's then I have a serious sql problem :)
All the examples I have of deleteing duplicates use the ROWID. 
If anyone has a suggestion on how else to do this I would be most grateful.


Otherwise answer on my main question would be fine.
Many Thanks
Denham Eva






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Archive files and their Management

[Denham Eva]

Title: Archive files and their Management





Greetings Gurus


Just wondering...
Our archive log's directory has grown substantially and space is becoming an issue.
How do you know which archive files is safe to delete? 
In other words...
Do you delete all archive files older than the last backup?
Should you keep all archive files until it is obviously pointless?


Please advise.
Many thanks
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Double Take and Oracle

[Denham Eva]

Title:  Double Take and Oracle





Hello Dick and All who contributed to this thread.


Amazingly this software Double-Take(DT) worked over night. Basically what the DT Technician did over the evening was to re-replicate the files and re-mirror the files. This morning we restarted the database and she came up as if nothing had occurred.

I for one was impressed. The database that I left last night was a very unhappy little creature. Apparently the techie decided to re-replicate as he had not done that since the first day. That is his explanation.

Once again thanks for your advice.
Damagement are smiling again, Thank God it was not an Oracle problem.
Regards
Denham Eva


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 9:19 PM
To: Denham Eva; Multiple recipients of list ORACLE-L
Subject: Re:RE: RE: RE: Double Take and Oracle



Eva,


 Your welcome. Please let all of us know how things work out for future
reference. And don't be afraid to ask for more.


Dick Goulet






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Double Take and Oracle

[Denham Eva]

Title: Double Take and Oracle 





Hello Esteemed Gurus


Pls advise...
We are trying to get Double Take to work on our site. Does anyone actually have this product working???


Salesperson claims Double Take is certified by Oracle - does anyone know? Where could I find out, other than the company itself?

For those of you who don't know this product pls give your opinion. Double Take is a software that copies your database files and parameter files etc across to a disaster recovery machine, bit for bit. At failure of the production the recovery becomes the production system and starts up the database. Basicly the datafiles are still open??

We get the following error when we start up the database on the recovery system in tests:
ORA-01172: recovery of thread 1 stuck at bloack 176624 of file 2.
I would just like to add that when this happens we resync the databases and try again to test and guess what - it is the turn of file 3 to give the above error, and so back and forth we go.

To say the very least I am frustrated beyond comprihension. To make this worse there is no one else in South Africa that uses this software with Oracle, apparently in France. Convienant - I'd say. So I have no one to turn to about this. (Apparently it works well with MSSQL). Anyone any ideas?

The Enviroment is Windows 2K SP3.
Oracle 817


Regards
Denham Eva
Oracle DBA
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.
Dennis Ritchie.






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: RE: Double Take and Oracle

[Denham Eva]

Title: RE: RE: Double Take and Oracle





Thanks for the input so far. Double-Take works real time so the database is never down.
I also followed their setup direction to the letter. Problem on our site is that this product is being used for SQL Server and Exchange (Appears to work fine there, but Oracle is a beast of another color compared to these Access on Steroids databases), management are insisting for the sake of conformity that this must work. At ever attempted startup another file gives an error, most frustrating of all is that there is almost no consistency to the errors.

Denham Eva 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 4:04 PM
To: Multiple recipients of list ORACLE-L
Subject: Re:RE: Double Take and Oracle



Lewis,


 I can understand where Eva is coming from. I had a demo of a similar
product some time ago. Their BIG selling point is that you have a production
server and a backup that is close in time with your production server at half
(or less) the cost of a hot standby. Their reasoning is that you don't have
Oracle running on the standby and consequently don't have to pay Oracle a
license fee for the second server since only one server is running at any point
in time. Logically their point is well taken. Regrettably and understandably
that is NOT Oracle's point of view.


Dick Goulet


Reply Separator


Author: Bishop Lewis [EMAIL PROTECTED]
Date: 10/15/2002 2:23 AM


I'm always dubious of these types of products (especially when there are no
reference sites available) and would opt for a batched up standby database
solution (syncing every 5/10/15 minutes or so) myself. I'm not saying
Double-Take is not good - I've got no experience of it and am sorry it's not
any help but you have an option should Double-Take not work correctly.


Lewis Bishop
---
Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
Oracle Database Consultant
Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
Phone : 020 8298 3418
Mobile: 07950 380857
Email : [EMAIL PROTECTED]
Enabling Competitive Advantage for Barclays in IT and Business Processing
 
-Original Message-
Sent: 15 October 2002 08:28
To: Multiple recipients of list ORACLE-L


This header confirms that this email message has been swept for the
presence of computer viruses. 


Corporate IT
THE WOOLWICH
--
Hello Esteemed Gurus 
Pls advise... 
We are trying to get Double Take to work on our site. Does anyone actually
have this product working??? 
Salesperson claims Double Take is certified by Oracle - does anyone know?
Where could I find out, other than the company itself?
For those of you who don't know this product pls give your opinion. Double
Take is a software that copies your database files and parameter files etc
across to a disaster recovery machine, bit for bit. At failure of the
production the recovery becomes the production system and starts up the
database. Basicly the datafiles are still open??
We get the following error when we start up the database on the recovery
system in tests: 
ORA-01172: recovery of thread 1 stuck at bloack 176624 of file 2. 
I would just like to add that when this happens we resync the databases and
try again to test and guess what - it is the turn of file 3 to give the
above error, and so back and forth we go.
To say the very least I am frustrated beyond comprihension. To make this
worse there is no one else in South Africa that uses this software with
Oracle, apparently in France. Convienant - I'd say. So I have no one to turn
to about this. (Apparently it works well with MSSQL). Anyone any ideas?
The Enviroment is Windows 2K SP3. 
Oracle 817 
Regards 
Denham Eva 
Oracle DBA 
UNIX is basically a simple operating system, but you have to be a genius to
understand the simplicity. 
Dennis Ritchie. 


DISCLAIMER 
This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mistransmission. If
you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender. You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient. TFMC, its holding company, and any of its subsidiaries each
reserve the right to monitor and manage all e-mail 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 views of any such entity. 



This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal - For more information please visit www.marshalsoftware.com 


-- 
Please see the official ORACLE-L FAQ: http

RE: RE: RE: Double Take and Oracle

[Denham Eva]

Title: RE: RE: RE: Double Take and Oracle





Dick,


Thank you, atleast you have given me something to work with. I had already began making plans of approaching management for some serious downtime. I have been holding back as I am awaiting response from Double-Take's Source - Sunbelt Software on this issue, I have logged a call with them about 8hrs ago already!

But you have given me alot to chew on until I do that - Many Thanks!!!


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 6:44 PM
To: Multiple recipients of list ORACLE-L
Subject: Re:RE: RE: Double Take and Oracle



Eva,


 First things first, Oracle gets to be a bear when your trying to move a
database as you are while it's running. The first point to settle is that
Double Take is not doing something that is incompatible with Oracle, like making
an ASCII vs. BINARY copy of the datafiles. To determine that you need to do a
move with the source/production database shutdown. Yeah, I know that can be
hard to schedule but it's necessary. If the copy won't start at that point then
Double Take is completely incompatible with Oracle and you can't go much
further. Now if that passes, allow Double Take to copy the production DB to the
backup server and while it's doing so check the hotbackup status of the database
using the following:


 SELECT COUNT(*)
 FROM DBA_DATA_FILES DF, SYS.V_$BACKUP B
 WHERE FILE_ID = FILE#
 AND DF.STATUS = 'AVAILABLE'
 AND B.STATUS = 'ACTIVE';


The number you get back should equal the number of datafiles in your database. 
If it comes back as 0 then the database has not switched and the backup is
worthless. You may have to do this manually before running Double Take. Also
your production/source DB has to be in archive log mode or else hotbackup does
not work.


The reasons for this are wrapped around Oracle's write as little as needed and
do so as seldomly as possible idea. With this idea data block changes are not
sent to the datafiles until there is a need to. MicroSlop and Exchange work on
the exact opposite idea which makes them IO hogs, although Oracle can be the
same. In your case I would suspect that the hotbackup is the problem.


Dick Goulet


Reply Separator
Author: Denham Eva [EMAIL PROTECTED]
Date: 10/15/2002 7:08 AM


Thanks for the input so far. Double-Take works real time so the database is
never down.
I also followed their setup direction to the letter. Problem on our site is
that this product is being used for SQL Server and Exchange (Appears to work
fine there, but Oracle is a beast of another color compared to these Access
on Steroids databases), management are insisting for the sake of conformity
that this must work. At ever attempted startup another file gives an error,
most frustrating of all is that there is almost no consistency to the
errors.


Denham Eva 


-Original Message-
Sent: Tuesday, October 15, 2002 4:04 PM
To: Multiple recipients of list ORACLE-L



Lewis,


 I can understand where Eva is coming from. I had a demo of a similar
product some time ago. Their BIG selling point is that you have a
production
server and a backup that is close in time with your production server at
half
(or less) the cost of a hot standby. Their reasoning is that you don't have
Oracle running on the standby and consequently don't have to pay Oracle a
license fee for the second server since only one server is running at any
point
in time. Logically their point is well taken. Regrettably and
understandably
that is NOT Oracle's point of view.


Dick Goulet


Reply Separator


Author: Bishop Lewis [EMAIL PROTECTED]
Date: 10/15/2002 2:23 AM


I'm always dubious of these types of products (especially when there are no
reference sites available) and would opt for a batched up standby database
solution (syncing every 5/10/15 minutes or so) myself. I'm not saying
Double-Take is not good - I've got no experience of it and am sorry it's not
any help but you have an option should Double-Take not work correctly.


Lewis Bishop
---
Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
Oracle Database Consultant
Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
Phone : 020 8298 3418
Mobile: 07950 380857
Email : [EMAIL PROTECTED]
Enabling Competitive Advantage for Barclays in IT and Business Processing
 
-Original Message-
Sent: 15 October 2002 08:28
To: Multiple recipients of list ORACLE-L


This header confirms that this email message has been swept for the
presence of computer viruses. 


Corporate IT
THE WOOLWICH
--
Hello Esteemed Gurus 
Pls advise... 
We are trying to get Double Take to work on our site. Does anyone actually
have this product working??? 
Salesperson claims Double Take is certified by Oracle - does anyone know?
Where could I find out, other than the company itself?
For those of you

RE: RE: Double Take and Oracle

[Denham Eva]

Title: RE: RE: Double Take and Oracle





Yes I believe that I have all the files necessary. The database does not complain about missing files but corrupt files ie the redolog etc.

I'll have to check on the transactional applications options though, if you mean Oracle they are exact, but I'll take it up with the Double-Take techie.

Thanks


-Original Message-
From: Bishop Lewis [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 6:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RE: Double Take and Oracle



Eva - just checking - from their docs have you verified that the
transactional applications option is selected on the optimisations screen?
This seems the most likely omission - and are you also sure that you are
including all necessary files in the selected copy?


Lewis Bishop
---
Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
Oracle Database Consultant
Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
Phone : 020 8298 3418
Mobile: 07950 380857
Email : [EMAIL PROTECTED]
Enabling Competitive Advantage for Barclays in IT and Business Processing
 
-Original Message-
Sent: 15 October 2002 16:09
To: Multiple recipients of list ORACLE-L


Thanks for the input so far. Double-Take works real time so the database is
never down. 
I also followed their setup direction to the letter. Problem on our site is
that this product is being used for SQL Server and Exchange (Appears to work
fine there, but Oracle is a beast of another color compared to these Access
on Steroids databases), management are insisting for the sake of conformity
that this must work. At ever attempted startup another file gives an error,
most frustrating of all is that there is almost no consistency to the
errors.
Denham Eva 
-Original Message- 
Sent: Tuesday, October 15, 2002 4:04 PM 
To: Multiple recipients of list ORACLE-L 


Lewis, 
    I can understand where Eva is coming from.  I had a demo of a similar 
product some time ago.  Their BIG selling point is that you have a
production 
server and a backup that is close in time with your production server at
half 
(or less) the cost of a hot standby.  Their reasoning is that you don't have


Oracle running on the standby and consequently don't have to pay Oracle a 
license fee for the second server since only one server is running at any
point 
in time.  Logically their point is well taken.  Regrettably and
understandably 
that is NOT Oracle's point of view. 
Dick Goulet 


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






DISCLAIMER 

This message is for the named person's use 
only. It may contain confidential, proprietary or legally privileged 
information. No confidentiality or privilege is waived or lost by any 
mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies of it and 
notify the sender. You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. TFMC, its holding company, and any of its subsidiaries each reserve 
the right to monitor and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

normal user obtaining program info?

[Denham Eva]

Title: normal user obtaining program info?





Hello Gurus,


I would like to try and build in some security onto a certain schemas tables, using for instance from system, I can do a select from v$session to obtain program name etc. ie,

SELECT PROGRAM FROM V$SESSION;


An extention of this a user with the rights can do :-
SELECT PROGRAM FROM V$SESSION
WHERE SID IN (SELECT SID FROM V$MYSTAT);


I really don't want to give all the users these rights.


Is there any query that a normal user can run which returns the same information as the above? ie program name.
Using auditing is a rather impractical option, as the growth would be huge, the idea is to capture only the changes and users that update/insert/delete using undesignated software ie MS Access.

Regards
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 






DISCLAIMER 

This message is for the named person's use only. It 
may contain confidential, proprietary or legally privileged information. No 
confidentiality or privilege is waived or lost by any mistransmission. If you 
receive this message in error, please immediately delete it and all copies of it 
from your system, destroy any hard copies of it and notify the sender. You must 
not, directly or indirectly, use, disclose, distribute, print, or copy any part 
of this message if you are not the intended recipient. Contract Forwarding, its 
holding company, and any of its subsidiaries each reserve the right to monitor 
and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Can DB tell if connection from odbc?

[Denham Eva]

Title: Can DB tell if connection from odbc?





Hello Gurus


Is there anyway that Oracle 817/Win2K can pick up whether the user is connecting via ODBC at all?
As I understand it the ODBC comes in through the Net8 and therefore the above is not possible.


Regards
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 






DISCLAIMER 

This message is for the named person's use only. It 
may contain confidential, proprietary or legally privileged information. No 
confidentiality or privilege is waived or lost by any mistransmission. If you 
receive this message in error, please immediately delete it and all copies of it 
from your system, destroy any hard copies of it and notify the sender. You must 
not, directly or indirectly, use, disclose, distribute, print, or copy any part 
of this message if you are not the intended recipient. Contract Forwarding, its 
holding company, and any of its subsidiaries each reserve the right to monitor 
and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Configuring 734 and 817 on same machine.

[Denham Eva]

Title: Configuring 734 and 817 on same machine.





Hello Gurus,


Does anyone have a link to a resource which describes the Installation and configuration of Oracle 734 and Oracle 817 on the same Windows 2000 machine.

The configuration of the listeners is my main concern. 


Any help on this is very much appreciated.
Regards
Denham Eva






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 






DISCLAIMER 

This message is for the named person's use only. It 
may contain confidential, proprietary or legally privileged information. No 
confidentiality or privilege is waived or lost by any mistransmission. If you 
receive this message in error, please immediately delete it and all copies of it 
from your system, destroy any hard copies of it and notify the sender. You must 
not, directly or indirectly, use, disclose, distribute, print, or copy any part 
of this message if you are not the intended recipient. Contract Forwarding, its 
holding company, and any of its subsidiaries each reserve the right to monitor 
and manage all e-mail 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 views of any such entity. 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Strange Data issue.....

[Denham Eva]

Title: Strange Data issue.





Hello Gurus,


I have a problem which I hope someone can help me with.
I am trying to clean up some data on a table. This table happens to be a personnel table.
In this table there are two columns RACE and GENDER.
The select used is :
 select race||','||gender||','||count(*)
 from pers
 group by race, gender;
The result is as follows:


A,F,62
A,M,613
A,M,35
A,,15
C,F,34
C,M,198
C,M,4
C,,2
I,F,13
I,M,69
I,M,1
W,F,205
W,M,972
W,M,28
W,,15


The problem is that the result returns double Ms, M indicating Male. As highligthed above
I have tried converting to ascii to see if they are different in anyway but both Ms return ascii 77.
Does anyone have any thoughts on this?


TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Exportable Users?

[Denham Eva]

Title: Exportable Users?





Hello Guru's


Oracle 817 and Win2K
I'am experimenting with a script - hopefully in the right direction.
The idea is to create a list of users who own any object and thus should/could be exported.


-Script so far -
SELECT owner FROM all_objects, all_users
WHERE owner = username;
 Script End---


Problem is I am getting in this list the users ORDSYS, MDSYS for instance.
When you run an export on any of these you get the following error
EXP-00010: ORDSYS is not a valid username or not an exportable username
Export command is as follows:
exp system/@sid grants=y indexes=y owner=ORDSYS file=ORDSYS log=ORDSYS


OK what I would like to know is there a way to run a similar script which only produces a list of exportable users??


Many Thanks 
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Listener Configuration - Oracle 7.3.4.5.2 on Windows 2000

[Denham Eva]

Title: Listener Configuration - Oracle 7.3.4.5.2 on Windows 2000





Hello Guru's,


Any ideas? I have installed Oracle 7.3.4.5.2 (7.3.4.0.0 + patchset 7.3.4.5.2) workgroup onto Windows 2000. Everything appears to have gone well, except that the Listener Service was not created automatically as it is usually done on NT. When I tried starting the listerner from command line the exe execs for what appears like a few seconds and returns to prompt. There is no listener.log file generated at all.

Anyone got any ideas?


Rgds
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

To create a new datafile or to increase a existing datafile.

[Denham Eva]

Title: To create a new datafile or to increase a existing datafile.





Hello Gurus


Which is the better policy:
Say you have five datafiles of 1GB in size each, now the total freespace is less than 10%.
Would it be wise to increase the datafiles to 2GB or would it be better for the system to add a sixth datafile?


Would appreciate any views on this point.
Rgds
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.






 DISCLAIMER 

  
 

  This message is for the named person's use only. It may contain 
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by 
any mistransmission. If you receive this message in error, please immediately delete 
it and all copies of it from your system, destroy any hard copies 
of it and notify the sender. You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message if 
you are not the intended recipient. TFMC, its holding company, and any of 
its subsidiaries each reserve the right to monitor and manage all e-mail 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 views of any such entity.  







This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: AW: OSUSER in V$SESSION capture in procedure?

[Denham Eva]

Thanks Jared. Works great.

-Original Message-
Sent: Thursday, May 23, 2002 9:55 PM
To: Multiple recipients of list ORACLE-L


you can also do:

select osuser
fromv$session s
where sys_context('userenv', 'SESSIONID') = s.audsid;

This requires a direct grant to v_$session only.

Jared






Denham Eva [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/22/2002 11:23 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: AW: OSUSER in V$SESSION capture in procedure?


Wow, this worked exactly as I hoped.
Many Thanks.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L



Try:

 select osuser
   fromv$session
   where   sid in (select sid
from   v$mystat);

Chk




[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM

Please respond to [EMAIL PROTECTED]

Sent by:[EMAIL PROTECTED]


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



Hi Eva,

1. If there is always the same osuser, why don't you use a default value.
2. Your proc couldn't work, because
Select uid from dual;
UID Gives you the USER_ID (number) from all_users.
Then you compare OSUSER (char) with UID(number)?

This should work:

CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
  V_UID NUMBER;
   V_OSUSER
BEGIN

   BEGIN
   SELECT UID
  INTO V_UID
   FROM DUAL;
   END;

  BEGIN
   SELECT USERNAME
 INTO V_OSUSER
   FROM ALL_USERS
   WHERE USER_ID := V_UID;
  END

rest of procedure.Includes insert etc
END TEST

HTH

Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de



-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 22. Mai 2002 17:34
An: Multiple recipients of list ORACLE-L
Betreff: OSUSER in V$SESSION capture in procedure?


Hello Listers,

I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough. But I want to capture the OSUSER value from v$session so
that
the there is a history of changes to the table and by whom. Problem with
using USER function is that all the users access the server via a third
party app and therefore have one username. Pretty pointless for this 
effort
then, as I could update the column in the history table with that user and
be done with it. But the use of UID also does not work because that brings
back a whole list of all the OSUSER value.

ie

CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
  V_UID NUMBER;
   V_OSUSER
BEGIN

   BEGIN
   SELECT UID
  INTO V_UID
   FROM DUAL;
   END;

  BEGIN
   SELECT OSUSER
 INTO V_OSUSER
   FROM V$SESSION
   WHERE OSUSER := V_UID;
  END

rest of procedure.Includes insert etc
END TEST

Now obviously this returns more than one row as all the users use the same
username through  the app. Any suggestion?

Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.




#
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
For more information please visit www.marshalsoftware.com


#
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public

OSUSER in V$SESSION capture in procedure?

[Denham Eva]

Hello Listers,

I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough.
But I want to capture the OSUSER value from v$session so that the there is a
history of changes to the table and by whom.
Problem with using USER function is that all the users access the server via
a third party app and therefore have one username.
Pretty pointless for this effort then, as I could update the column in the
history table with that user and be done with it.
But the use of UID also does not work because that brings back a whole list
of all the OSUSER value.

ie 

CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE)
IS
  V_UID NUMBER;
   V_OSUSER
BEGIN
  
   BEGIN
   SELECT UID 
INTO V_UID
   FROM DUAL;
   END;

  BEGIN
   SELECT OSUSER
 INTO V_OSUSER
   FROM V$SESSION
   WHERE OSUSER := V_UID;
  END

rest of procedure.Includes insert etc
END TEST

Now obviously this returns more than one row as all the users use the same
username through  the app.
Any suggestion?

Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.


#
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal
For more information please visit www.marshalsoftware.com
#
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: AW: OSUSER in V$SESSION capture in procedure?

[Denham Eva]

Wow, this worked exactly as I hoped.
Many Thanks.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L



Try:

 select osuser
   fromv$session
   where   sid in (select sid
from   v$mystat);

Chk




[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM

Please respond to [EMAIL PROTECTED]

Sent by:[EMAIL PROTECTED]


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



Hi Eva,

1. If there is always the same osuser, why don't you use a default value.
2. Your proc couldn't work, because
Select uid from dual;
UID Gives you the USER_ID (number) from all_users.
Then you compare OSUSER (char) with UID(number)?

This should work:

CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
  V_UID NUMBER;
   V_OSUSER
BEGIN

   BEGIN
   SELECT UID
  INTO V_UID
   FROM DUAL;
   END;

  BEGIN
   SELECT USERNAME
 INTO V_OSUSER
   FROM ALL_USERS
   WHERE USER_ID := V_UID;
  END

rest of procedure.Includes insert etc
END TEST

HTH

Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de



-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 22. Mai 2002 17:34
An: Multiple recipients of list ORACLE-L
Betreff: OSUSER in V$SESSION capture in procedure?


Hello Listers,

I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough. But I want to capture the OSUSER value from v$session so
that
the there is a history of changes to the table and by whom. Problem with
using USER function is that all the users access the server via a third
party app and therefore have one username. Pretty pointless for this effort
then, as I could update the column in the history table with that user and
be done with it. But the use of UID also does not work because that brings
back a whole list of all the OSUSER value.

ie

CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
  V_UID NUMBER;
   V_OSUSER
BEGIN

   BEGIN
   SELECT UID
  INTO V_UID
   FROM DUAL;
   END;

  BEGIN
   SELECT OSUSER
 INTO V_OSUSER
   FROM V$SESSION
   WHERE OSUSER := V_UID;
  END

rest of procedure.Includes insert etc
END TEST

Now obviously this returns more than one row as all the users use the same
username through  the app. Any suggestion?

Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.




#
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
For more information please visit www.marshalsoftware.com


#
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
#
This e-mail message has been scanned for Viruses and Content

RE: LOG_BUFFER Parameter Question

[Denham Eva]

Thanks 
to all of you on this issue...


  -Original Message-From: Gogala, Mladen 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 24, 2002 8:12 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  LOG_BUFFER Parameter Question
  Alternatively, you size it until redo_log_space_wait 
  goes away from v$system_events.
  
-Original Message-From: Anjo Kolk 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 24, 2002 4:59 
AMTo: Multiple recipients of list ORACLE-LSubject: Re: 
LOG_BUFFER Parameter QuestionYep, 
wrong tree. size it like: redo blocks per tx * tx/sec * 3/2 *physical 
blocksize 
Anjo.  
Denham Eva wrote: 
 
  Hello, 
  I have been wondering about how to work 
  out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG 
  file, for the best optimization and writes from buffer to 
  log. Or am I barking up 
  the wrong tree? Is there some other way of doing this. 
  i.e. redolog file size is 10M and 
  LOG_BUFFER is 512000. Can 
  this be optimized better? 
  Denham Eva Oracle DBA In UNIX 
  Land On a quiet Night, you can hear the Windows 
  machines reboot. 
  
  This e-mail message has been scanned for Viruses and Content and cleared 
  by MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  


This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

LOG_BUFFER Parameter Question

[Denham Eva]

Title: LOG_BUFFER Parameter Question





Hello,


I have been wondering about how to work out what the optimal LOG_BUFFER parameter,
compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log.
Or am I barking up the wrong tree? Is there some other way of doing this.


i.e. redolog file size is 10M and LOG_BUFFER is 512000.
Can this be optimized better?



Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Long running SQL Problem?

[Denham Eva]

Title: Long running SQL Problem?





Hello List


Is there anyone who can give me a solution to this problem.
It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows.
Is there perhaps a quicker more effecient way of doing this.



SELECT col1,col2 
FROM Table_1 
WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2);



TIA
Denham Eva
Oracle DBA




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Long running SQL Problem?

[Denham Eva]

Title: Long running SQL Problem?



Hi 
List

Thank 
you to everyone who took the time to answer, I never realised that there could 
be so many solutions :)

Rgds
Denham

  -Original Message-From: Denham Eva 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 27, 2002 10:53 
  AMTo: Multiple recipients of list ORACLE-LSubject: Long 
  running SQL Problem?
  Hello List 
  Is there anyone who can give me a solution to this 
  problem. It is a sql that runs forever and 
  I eventually have to kill it, both tables are large 50 + rows. 
  Is there perhaps a quicker more effecient way of 
  doing this. 
  SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT 
  IN (SELECT col3,col4 FROM Table_2); 
  TIA Denham 
  Eva Oracle 
  DBA 
  
  This e-mail message has been scanned for Viruses and Content and cleared by 
  MailMarshal - For more information 
  please visit www.marshalsoftware.com 
  
  


This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Windows NT and Virtual Memory

[Denham Eva]

Title: RE: Windows NT and Virtual Memory





Rajesh,


I had the same problem when we migrated to Oracle817. The solution was to setup MTS.
Our server runs about 450 sessions/connections at a time now, no problems.
I have the server set up to mts_servers = 15 and an initial dispatchers = 10.
This has been working fine for our system for approx 1yr now.


Hope this helps
Rgds
Denham


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 22, 2002 5:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Windows NT and Virtual Memory



Operating System: Windows NT 4.0 with SP 6a
Oracle Version : 8.1.7


We recently migrated an Oracle database from one Windoze NT box to another.
The NT administrators claim that the NT boxes are indentical in all
respects, except that the old one had Service Pack 4. We have recreated the
database on the new box with exactly indentical initialization paremeters,
file sizes, extent sizes, et all.


The database gleefully accepts all connections upto about 220 users
(V$license, v$session). When the 221st user tries to logon, the TNS-12500:
Unable to start a dedicated server process is thrown. After all the
troubleshooting (Connect timeouts, Disable otracing, tracing client), we
discovered that as soon as the Virtual memory comes approaches 1.7Gb, this
problem starts happening. I read articles on Metalink which suggested that
this is a limitation with Windows NT, and suggests some recommendation to
delay this problem.


So, we recommended the same to business. But now they wish to know why
these errors never occured in the old NT server. And to further worsen
matters, the old server has been cleaned up. So, I have no way of going
back and Checking.


Has anyone else on this list encountered the same problem? Possible
workarounds, if any?


Thanks
Raj


I was an Atheist, until I started working with NT.


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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Tools for Scurity and Access to the Server?

[Denham Eva]

Title: Tools for Scurity and Access to the Server?





Hello List,


Management would like to know if there is a tool that can monitor who accesses the server, what tool do they use and what they do.

Also is there a tool that can prevent users from accessing Oracle via any other tool other than the Application.


I know I could probably write scripts/triggers to handle these issues, but I fear management balks at the idea of me having this amount of control. I did suggest it, but it was waved off immediately. I think they have the idea of having third party monitor the access to the DB.

TIA
Denham Eva
Oracle DBA




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

DB Link Problem

[Denham Eva]

Title: DB Link Problem





Hello List,


Have a problem, 
Oracle 817
Win3K sp6
Trying to create a DB link between the production and the test dbs.


The db_link creates fine ( so it appears).
However when you try to run a script to reference the other, I get


select count(*) from workorder@maxie
 *
ERROR at line 1:
ORA-02085: database link MAXIE connects to MAXTEST.TFMC.CO.ZA


I checked out the manual, but it seems as thou I have created the link
correctly.
I also checked out metalink, which suggested changing the global_name with
ALTER DATABASE RENAME global_name TO MAXTEST
But this causes the following error:
ORA-02019: connection description for remote database not found.


Has anyone got any ideas? Is this a bug?


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Database Link Problem

[Denham Eva]

Title: Database Link Problem





Hello Folks


Just a resounding Thank You to all of you for your tips
The Database Link now works :)
Apologisies we use Win2k, maybe Windoze ;P will have something worth working with by Win3k:)


Rgds
Denham




Hello List, 
Have a problem,
Oracle 817
Win3K sp6
Trying to create a DB link between the production and the test dbs. 
The db_link creates fine ( so it appears).
However when you try to run a script to reference the other, I get 
select count(*) from workorder@maxie
*
ERROR at line 1:
ORA-02085: database link MAXIE connects to MAXTEST.TFMC.CO.ZA 
I checked out the manual, but it seems as thou I have created the link
correctly.
I also checked out metalink, which suggested changing the global_name with
ALTER DATABASE RENAME global_name TO MAXTEST
But this causes the following error:
ORA-02019: connection description for remote database not found. 
Has anyone got any ideas? Is this a bug? 
TIA
Denham 





This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

DataBase Modelling Tools

[Denham Eva]

Title: DataBase Modelling Tools





Hello List


Apologise for the Off - Topic, but pretty desperate.
Has anyone any suggestions as to a Database Modelling Tool, any preferences?
I am looking for one for linux and preferably free, but does not have to be either.


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

ORA-01406

[Denham Eva]

Title: ORA-01406





Hi List,


Please can anyone give me some pointers on this issue. 
We have third party app called Maximo with reports that run from within it. 
We are receiving an error on one of these reports.
The Oracle error that comes out of it is
ORA-01406 fetched column value was truncated.
I am not sure of what exactly is happening as I don't have access to their source.
However I would like to know if there is a general resolution for this that can be implemented on the server,
or is it perhaps a bug?


OS = Win2K SP6
ORACLE = 8.1.7.0.0


Rgds
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Oracle8i - Oracle9i issue??????

[Denham Eva]

Title: RE: Oracle8i - Oracle9i issue??





Yeah they refer to it as C Sharp as in the music # symbol.
It is Microsoft's C.Net - I think.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, February 22, 2002 10:08 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Oracle8i - Oracle9i issue??



C# ?


Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd



Denham Eva [EMAIL PROTECTED] 22/02/2002 03:13 PM
Sent by: [EMAIL PROTECTED]


Please respond to ORACLE-L
 
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
 cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) 
 Subject: Oracle8i - Oracle9i issue?? 
 
 
 






Hello Guru's



I wonder if any of you have had this problem, we have a external company
developing a software package in C#.
On their Oracle9i test system, the software works fine, however once
brought accross to our systems,
which is an Oracle8i ( 817) system the software gives a (ORA - 00933 SQL
command not properly ended) error, but funny enough only on the one window.
I am told by them that they do straight select from a table, saving the
returned columns in variables etc.



The only difference is they don't have any semi-colons (;) at the end of
the their queries. But catch 22 it works on the other screens(windows).



Hope you folks can help
TIA
Denham



This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal - For more information please visit www.marshalsoftware.com









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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Oracle8i - Oracle9i issue??????

[Denham Eva]

Title: Oracle8i - Oracle9i issue??





Hello Guru's


I wonder if any of you have had this problem, we have a external company developing a software package in C#.
On their Oracle9i test system, the software works fine, however once brought accross to our systems,
which is an Oracle8i ( 817) system the software gives a (ORA - 00933 SQL command not properly ended) error, but funny enough only on the one window. I am told by them that they do straight select from a table, saving the returned columns in variables etc.

The only difference is they don't have any semi-colons (;) at the end of the their queries. But catch 22 it works on the other screens(windows).

Hope you folks can help
TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Optimize Method Q?

[Denham Eva]

Title: Optimize Method Q?





Hello Folks,


Ok, I know that I have alot to learn, but when you do something believing that it will speed up things and it does but only for some queries and turns others into instant snales, a newbie like me really wonders.

The situation is like this, I analyzed all the relevant schema's tables and indexes. Now one of the more important queries( a long one) just turned from a tortois into a snale. I have been forced to set the database optimizer method to rule. Which has had the effect of turning the snale back to a tortois.

The system is a 8.1.7
on a Windows 2000 machine.
Optimization was CHOOSE, but because the tables were not analyzed it invariably chose rule.


The only strange observation I can make is that the developers have three indexes on this table, with three columns in all three indexes, but in different positions.

Any comments on why this would happen/how this happens. 




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

jdbc connection to db don't die.

[Denham Eva]

Title: jdbc connection to db don't die.





Hi Guru's


Please help, I am running Oracle 8.1.7 on Windows 2000.
The problem is a third party product runs a jdbclient connection to the database, but it does not seem to disconnect.
So over time we have a build up of connected sessions using jdbcuser (Osuser). I have tried killing the sessions with TOAD, but the remain connected with a status of KILLED. They never drop - until the database is shutdown and restarted.

As you can imagine the database quickly reaches MAX NUMMBER OF SESSIONS EXCEEDED and I really cann't keep stopping starting the db.

I am managing to hold out by killing the idle users.


Please has anyone else experienced this and is there some solution? 


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

XML implementation

[Denham Eva]

Title: XML implementation





Hello Gurus,


Management has now picked up the catch word XML. I now have to present a implementations plan - thursday. I naturally have no knowledge of the subject, but have spent hours now studying the docs.

However there are so many possible choices and combinations of how this works. 
Has anyone implemented XML, and is it working/does it fill it's purpose?
What pitfalls did you experience?


What we intend to try is a B2B (business to business) implementation. Receive invoices from suppliers etc.


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Oracle Future???

[Denham Eva]

Title: Oracle Future???





Hi Folks,


Wonder if anyone would like to comment, my MSSQL DBA sent me a couple of emails from a MSSQL mailing list, they were discussing the future of MSSQL.

Some of the comments included comments about our DB - Oracle.
I've added two here... 


...With Hyperion been seriously challenged and overrun by Analysis Services, oracle will completely die out as they integrate their current offering into Oracle9i. 

...Oracle will become another Apple: 5% market share. ...


Do you guys agree? Is Oracle doing anything about the very serious threat that MSSQL is placing on their market share? 


No, plans of changing personally, but wondering if cross education won't be beneficial:)


Regards
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: XP vs. W2000

[Denham Eva]

Title: RE: XP vs. W2000





So, Jared, I have heard of VmWare which is not as far as I know Open Source(Free).
I don't know of Win4Lin, is it Open Source? and won't you email me a link to the site.


Regards
Denham


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 26, 2001 7:45 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: XP vs. W2000




Forget it.


Get Suse 7.2 or 7.3, or the latest version of RedHat.


If you want to run Windoze too, get VmWare or Win4Lin.


Win4Lin works with win95/98.


Vmware is more flexible and will work with Win2k.


I've found Win98 with Win4Lin to be more stable
than Win98 by itself.


Jared





 
 Ken Janusz 
 ken.janusz@su To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
 fsys.com cc: 
 Sent by: Subject: XP vs. W2000 
 [EMAIL PROTECTED] 
 om 
 
 
 12/26/01 07:50 
 AM 
 Please respond 
 to ORACLE-L 
 
 





This question is a little off the mark. I will be ordering a new Dell PC
(8200) in the near future. I would like to know which OS I should go with.
XP Professional or W2000? Any suggestions? Anyone have any experience
with
XP Prof.? Pluses / minuses?


Thanks,


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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).





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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Internal Password

[Denham Eva]

Title: Internal Password



Thanks 
Ganesh, I actually thought I had done this, but obviously the stress of a downed 
system must have clouded my brain. It now works as described 
below.

Thanks
Denham

  -Original Message-From: Ganesh Raja 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 13, 2001 9:35 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Internal Password
  Set 
  ORACLE_SID Environment Variable to the instance u want to Connect to 
  .
  
  HTH
  
  Best Regards, Ganesh R Tel : +971 (4) 
  397 3337 Ext 420 Fax : +971 (4) 397 
  6262
  HP :+971(50) 
  7456019
  
-Original Message-From: Denham Eva 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 13, 2001 10:35 
AMTo: Multiple recipients of list ORACLE-LSubject: 
Internal Password
Hello Gurus 
I have a problem in that I was always able to 
login to svrmgrl on the consol and connect to internal, without having to 
give a password.
Yesterday however we had a massive mulfunction of 
the UPS and all power was lost to the server room. Anyway the Win2k, compaq 8500R server recovered fine and 
so did the Oracle DB 817. BUT, now 
connecting internal demands a password, why? Also all known passwords do not 
work including the default one. Can 
anyone shed some light on what happened? and how can I fix it? 
TIA Denham 
Eva 

This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - For more 
information please visit www.marshalsoftware.com 


  

This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Internal Password

[Denham Eva]

Title: Internal Password





Hello Gurus


I have a problem in that I was always able to login to svrmgrl on the consol and connect to internal, without having to give a password.

Yesterday however we had a massive mulfunction of the UPS and all power was lost to the server room.
Anyway the Win2k, compaq 8500R server recovered fine and so did the Oracle DB 817.
BUT, now connecting internal demands a password, why? Also all known passwords do not work including the default one.
Can anyone shed some light on what happened? and how can I fix it?


TIA
Denham Eva




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

The Oracle List Archive?

[Denham Eva]

Title: The Oracle List Archive?





Hi,


Forgive the rather dumb newbie question.
However I regularly see people on this list refer to the archive.
How can I access this? I have poked around at fatcity.com and not found much help!


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Killed session - does it rollback?

[Denham Eva]

Title: Killed session - does it rollback?





Hi Gurus


Just wondering, as DBA if I kill a session on the server. Does the server rollback the transaction automatically?


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Problems with SQL Server Link Server to ORACLE

[Denham Eva]

Title: Problems with SQL Server Link Server to ORACLE





Hi List,


Problem receiving this error on a Link Server from SQL Server 7.0 and 2000 to Oracle 817


INSERTED INTO IR TABLE IR_MAXIMO_WORKORDER [SQLSTATE 01000] (Message 0) INSERTED INTO MAXIMO TABLE MAXTFMC..MAXIMO.IR_WO_IN [SQLSTATE 01000] (Message 0) OLE DB provider 'MSDAORA' could not INSERT INTO table '[MAXTFMC]..[MAXIMO].[IR_WO_IN]'. Unknown provider error. [SQLSTATE 42000] (Error 7343) [SQLSTATE 01000] (Error 7312). The step failed.

The ODBC that has been setup on both these SQL Servers is Oracle ODBC driver, I have personally created the connections and tested them - thus it can be assumed that the ODBC links work.

The enviroments are as follows:
Sql Server 1
Sql Server 7.0
Win NT 4.0 Service Packs 6a.
Oracle ODBC Driver


Sql Server 2
Sql Server 2000
Win NT 4.0 Service Packs 6a
Oracle ODBC Driver


Oracle System
Oracle 8.1.7
Win 2k


The problem appears to be resolved when the Link Server is rebuild using Microsoft OLE DB Provider for ODBC Drivers, which is substantial slower that the Microsoft OLS DB Provider to Oracle which has worked for a couple of weeks and yesterday suddenly decided not to work anymore.

Does anyone have any idea of what is happening here?


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

Number of Transactions per 24hr period? - Urgent

[Denham Eva]

Title: Number of Transactions per 24hr period? - Urgent





Hello List,


Please help, I would like to determine the number of transactions processed by Oracle during a 24 hr period.
Is this possible?


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Number of Transactions per 24hr period? - Urgent

[Denham Eva]

Title: RE: Number of Transactions per 24hr period? - Urgent





Thanks,


I have followed this line of thought, however, What parameter do I use as a yard stick?
Perhaps execute count, parse(hard), OR parse(total) - This is what I am not sure of:)


Thanks
Denham


-Original Message-
From: Stephane Faroult [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 27, 2001 1:50 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Number of Transactions per 24hr period? - Urgent



 Denham Eva wrote:
 
 Hello List,
 
 Please help, I would like to determine the number of transactions
 processed by Oracle during a 24 hr period.
 Is this possible?
 
 TIA
 Denham
 
 --
 This e-mail message has been scanned for Viruses and Content and
 cleared by MailMarshal - For more information please visit
 www.marshalsoftware.com
 --


This is typically the kind of information you find in V$SYSSTAT. Look at
V$INSTANCE to get the exact time when your instance was started,
V$SYSSTAT holds (mostly) cumulated values.
-- 
Regards,


Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269 
Fax: +44 (0) 7050-696-449 
Performance Tools  Free Scripts
--
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
 INET: [EMAIL PROTECTED]


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

row creation or update date??

[Denham Eva]

Title: row creation or update date??





Hi 


This may be an easy question or mentioned before, apologize if so,
But is there some way of finding a row in a table's creation/update date, without there being a date column specific for that purpose. Something like rowid?

TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

close_cached_open_cursors error

[Denham Eva]

Title: close_cached_open_cursors error





Hello Guru's


I hope someone can shed light on this problem, more a nuisance really.
The below Alter Session statement has been appearing in the *alrt.log file.


ALTER SESSION SET close_cached_open_cursors specifies an obsolete parameter
ALTER SESSION SET close_cached_open_cursors specifies an obsolete parameter
ALTER SESSION SET close_cached_open_cursors specifies an obsolete parameter
ALTER SESSION SET close_cached_open_cursors specifies an obsolete parameter
ALTER SESSION SET close_cached_open_cursors specifies an obsolete parameter


The system is a Compaq 8500R system, Win2K and Oracle 817.
Does anyone have an idea of what this is as everyone I have approached here at my work claims not to be using it, I suspect a third party product. 

Absolutely any info will be appreciated.


TIA
Denham




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: IP Address Change

[Denham Eva]

Title: RE: IP Address Change





Sean,


I've been there, we use the same sort of enviroment as you do.
The only problem I experienced was network resolution were the servers could not resolve the DNS name etc.
Unfortunately if the user cannot connect to Oracle! It must be a database problem!:) 
(Which in my case never was.)
Something to watchout for, Clever users who have set their own clients via ip addresses.


Tools I used when we did the crossover was ping and tnsping, used together they give you an excelent idea of what is going on. Most importantly, if it is your problem or a network issue.

Hope it goes well
Regards
Denham


-Original Message-
From: O'Neill, Sean [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 14, 2001 2:05 PM
To: Multiple recipients of list ORACLE-L
Subject: IP Address Change



Our network folk have informed me that they are going to have to change the
scope of IP addresses at our site and hance the IP addresses of our servers
will change. We don't use Oracle names or MTS. We're all NT/W2K servers
and use DNS. Are there any gothca's I need to be aware of. Our TNSnames
and Listener files use the host name and not specific IP addresses. I've
had a browse around MetaLink and have not ID'ed any problems thus far but
more experienced feedback would be appreciated.


-
Sean O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode] 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: O'Neill, Sean
 INET: [EMAIL PROTECTED]


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




This e-mail message has been scanned for Viruses and Content and cleared by 
MailMarshal - 
For more information please visit  
  www.marshalsoftware.com 

RE: Oracle on NT startup question

[Denham Eva]

Title: RE: Oracle on NT startup question





In UNIX there is not service process, but Win NT/2K requires it, something I discovered when I started to working on WIN env. Confusing if you do come from a UNIX env. Look at it this way the Service + the Database( Datafiles, redologs, controlfiles etc) are the instance. The Service is a process which is started in preparation for the mounting of the instance, in otherwords setting up the memory and so on. If you start up the service and not the instance(Database) you will get an error from the listener(also a service) stating that Oracle is not available. You will then be required to start the instance(Database) from svrmgrl/Sqlplus.

But as you have already discovered, the service can be setup to automatically start the instance(Database) once the service has started, making it almost UNIX like in that sense.

Come to think of it, if you open the services facility in the ControlPanel, right click on the oracle service, properties, you can change the startup mode from automatic to manual. Which will then not start the service or the instance at startup of the server. It will require you to start it manually very time.

HTH
Denham


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 01, 2001 4:20 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Oracle on NT startup question



I'm sorry. I thought that you mounted a database and that the instance was
the area in memory (corresponding to the SGA and PGA) that Oracle used. So
what is the instance?



 
 Denham Eva 
 EvaD To: Multiple recipients of list ORACLE-L 
 @TFMC.co.za [EMAIL PROTECTED] 
 Sent by: root cc: 
 Subject: RE: Oracle on NT startup question 
 
 11/01/2001 
 12:25 AM 
 Please 
 respond to 
 ORACLE-L 
 
 







I am not sure I understand you correctly, but on NT Oracle runs as a
service and an instance(the mounted database). The service must be started
for the instance to be able to mount and connected to. However the service
can be started without having to mount the instance.


HTH
Denham


-Original Message-
Sent: Wednesday, October 31, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L


My understanding is that the instance is the service.  Can you start the
instance without a mounted database?


    Andrey
    Bronfin  To: Multiple recipients of
list ORACLE-L
    andreyb [EMAIL PROTECTED]
    @elrontelesof    cc:
    t.com   Subject: Oracle on NT startup
question
    Sent by: root
    10/31/2001
    04:35 AM
    Please
    respond to
    ORACLE-L





Dear list !


If my NT server (which runs an Oracle instance) gets rebooted accidentally,
how can i bring up the Oracle services without starting up the instance ?
I want to startup the instance with a script later .
I'm interested in an answer for both Oracle 8.0.5 and 8.1.7 on NT4.


Thanks in advance.


DBAndrey


* 03-9254520
* 053-464562
* mailto:[EMAIL PROTECTED]


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


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


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


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list

RE: Oracle on NT startup question

[Denham Eva]

Title: RE: Oracle on NT startup question





Hi 


You will have to use the oradim utility to remove the current service.
Then recreate the service using the parameter -STARTTYPE SRVC. This then causes the system to start only the service and not the instance.

[-STARTTYPE SRVC | INST | SRVC, INST] 


HTH
Denham




-Original Message-
From: Andrey Bronfin [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 31, 2001 11:35 AM
To: Multiple recipients of list ORACLE-L
Subject: Oracle on NT startup question




Dear list !


If my NT server (which runs an Oracle instance) gets rebooted accidentally,
how can i bring up the Oracle services without starting up the instance ?
I want to startup the instance with a script later .
I'm interested in an answer for both Oracle 8.0.5 and 8.1.7 on NT4.


Thanks in advance.


DBAndrey


* 03-9254520
* 053-464562
* mailto:[EMAIL PROTECTED]



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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: Oracle on NT startup question

[Denham Eva]

Title: RE: Oracle on NT startup question





I am not sure I understand you correctly, but on NT Oracle runs as a service and an instance(the mounted database). The service must be started for the instance to be able to mount and connected to. However the service can be started without having to mount the instance.

HTH
Denham


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 31, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Oracle on NT startup question



My understanding is that the instance is the service. Can you start the
instance without a mounted database?



 
 Andrey 
 Bronfin To: Multiple recipients of list ORACLE-L 
 andreyb [EMAIL PROTECTED] 
 @elrontelesof cc: 
 t.com Subject: Oracle on NT startup question 
 Sent by: root 
 
 
 10/31/2001 
 04:35 AM 
 Please 
 respond to 
 ORACLE-L 
 
 






Dear list !


If my NT server (which runs an Oracle instance) gets rebooted accidentally,
how can i bring up the Oracle services without starting up the instance ?
I want to startup the instance with a script later .
I'm interested in an answer for both Oracle 8.0.5 and 8.1.7 on NT4.


Thanks in advance.


DBAndrey


* 03-9254520
* 053-464562
* mailto:[EMAIL PROTECTED]



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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).



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


Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Moving the ALert and Dump Locations

[Denham Eva]

Title: Moving the ALert and Dump Locations





Hi,


Is it possible to move the Alert log and Dump Locations to another location?
ie - Oracle 8.1.7 on Win2K
c:\Oracle\admin\DBNAME
TO
e:\Oracle\admin\DBNAME...



Just wondering, anyone got any ideas?
TIA
Denham

Monitoring Tool with a difference

[Denham Eva]

Title: Monitoring Tool with a difference





Hi Gurus,


Ok, I urgently require recommendations for software which can monitor a Oracle Database for a 24Hr period and create 
a report on the health of the system.
Please if anyone has used or is using software for this purpose please let me know.



TIA
Denham

RMAN - Useful or not?

[Denham Eva]

Title: RMAN - Useful or not?





Hi Everyone,


I am investigating using RMAN for backup and recovery etc. What I would like to know is:
Have you found it useful? or is it much like the old Oracle 7.3 EBU (Enterprise Backup Utility) were most DBA's
created their own backup scripts and etc anyway?
I would really like to hear your ideas.


Thanks
Denham

A range function ??

[Denham Eva]

Hi,

I would like to exclude an arrange of characters.
there is for instance:
   not in ('A','B','C','D','E'...);
Is there not perhaps something easier? ie:
  not in ( A-Z, a-z) ;
Does anyone know of a function or some other more economical way of doing
this?

Many Regards
Denham
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: A range function ??

[Denham Eva]

Thanks Folks,

So far all the examples have worked wonderfully.

Thank You all
Denham Eva

-Original Message-
Sent: Wednesday, September 26, 2001 8:45 AM
To: Multiple recipients of list ORACLE-L


Hi,

I would like to exclude an arrange of characters.
there is for instance:
   not in ('A','B','C','D','E'...);
Is there not perhaps something easier? ie:
  not in ( A-Z, a-z) ;
Does anyone know of a function or some other more economical way of doing
this?

Many Regards
Denham
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Two Instances at one oracle Server

[Denham Eva]

Hi,

It looks as though you are trying to start the instance from svrmgrl?
In that case, use the SET ORACLE_SID=SIDNAME, to force the oracle name
before you start svrmgrl.
Or try in svrmgrl: connect internal @SIDNAME

Another way of doing this is re-create the services using oradim and set
starttype to srv,inst.
e.g. C:\ ORADIM - STARTUP -SID SID -USERPWD PASSWORD -STARTTYPE SRV,INST 
That way all you have to do is start the service from service manager and
both the service and the instance will start.

I hope that helps
Denham :)

-Original Message-
Sent: Wednesday, September 19, 2001 1:30 PM
To: Multiple recipients of list ORACLE-L


How?

I started first one, then i try to startup 2nd, it says, Cann't start -
already started, shut it down first.

Nirmal.

 -Original Message-
 From: Sinardy [SMTP:[EMAIL PROTECTED]]
 Sent: Wednesday, September 19, 2001 12:40 PM
 To:   Multiple recipients of list ORACLE-L
 Subject:  RE: Two Instances at one oracle Server
 
 Hi,
 
 I think you can do that ?
 
 Just start up both of them together
 
 
 
 Sinardy
 
 
 -Original Message-
 Kumar Muthu Kumaran
 Sent: Wednesday, 19 September 2001 3:55 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Guru's
 
 In my test oracle server, i created two different database with SID
 'orclsvr' and 'ora817'.
 
 Is't possisle to create two oracle database instances on the same server?.
 
 
 Presently am able to create only one instance at a time either for the sid
 'orclsvr' or 'ora817'.
 
 Oracle Server 817 on Win NT4 based.
 
 Regards,
 Nirmal.
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Nirmal Kumar  Muthu Kumaran
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Sinardy
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nirmal Kumar  Muthu Kumaran
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Replication ??

[Denham Eva]

Hi Gurus

We are considering implementing replication, on our server.
Microsoft 2000 Oracle 816. 
Does anyone know of any problems, or I wish I had known that before.
type of issues of doing this?

Many Thanks
Denham
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Copying backups across a narrow pipe.

[Denham Eva]

Hi Gurus

I have a problem in that I have to copy my backups across a wan connection
to a remote server.
However the copy ( using xcopy) in the microsoft NT environment, keeps on
dropping or times out. So the success rate is very low, the Boss is now
becoming difficult about it.
Does anyone know of any tools/Software which I could use to copy the file
across, check the integrity and recopy if it had failed?

Any suggestions will be appreciated
Many Thanks
Denham
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denham Eva
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).