date:20020606


Oh yeah,

The dumped controfile script with give you all of the files name s too.  Then 
just search and replace in a text editor PCLDB1 and ROLAND,

Hope it helps at all,

Hannah

  -Original Message-
 From: Doran, Johanna  
 Sent: Thursday, June 06, 2002 11:15 AM
 To:   '[EMAIL PROTECTED]'
 Subject:  RE: How to move 200 GB db from prod to dev?
 
 Dump the prod control to trace
 Copy the db files over to dev
 edit the dumped controlfile (you can change db name here) 
 replace the copied controlfile with the new eidted one and bring up dev
 
 I forget exactly what needs to change in the controlfile file script but db name and 
I believe reset logs
 
 Hannah
 
 
  -Original Message-
 From: [EMAIL PROTECTED]@SUNGARD   On Behalf Of Daiminger, Helmut 
[EMAIL PROTECTED]
 Sent: Thursday, June 06, 2002 10:28 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:  How to move 200 GB db from prod to dev?
 
 
 Hi! 
 We are supposed to clone our production database onto a new development box (both 
boxes are Sun Solaris). The db is about 200 GB in size.
 What would be the best way to achieve this? Simply copying over the files won't 
work, since the instance names are different:
 Production:  SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf 
 Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 
 So would export/import the entire db be the only way? (But writing out dump file 
that big should be a little disk space problem...)
 Renaming all the datafiles (approx. 100) would be kind of annoying... 
 Any ideas? 
 This is 8.1.7 on Sun Solaris. 
 Thanks, 
 Helmut 
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

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

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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread Mercadante, Thomas F

Title: How to move 200 GB db from prod to dev?



Helmut,

Why 
can't you simply copy the files and then re-create new control files? This 
gives you the option of renaming the database.

Not 
sure what your backup procedures are, but you could always restore a backup to 
the development box and, again,recreate the control files renaming the 
database as you go.

Hope 
this helps

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 
  2002 10:28 AMTo: Multiple recipients of list 
  ORACLE-LSubject: How to move 200 GB db from prod to 
  dev?
  Hi! 
  We are supposed to clone our production database onto a new 
  development box (both boxes are Sun Solaris). The db is about 200 GB in 
  size.
  What would be the best way to achieve this? Simply copying 
  over the files won't work, since the instance names are different:
  Production: SID=PCLDB1 = e.g. 
  /u02/oradata/PCLDB1/system01.dbf Development: 
  SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 
  So would export/import the entire db be the only way? (But 
  writing out dump file that big should be a little disk space 
  problem...)
  Renaming all the datafiles (approx. 100) would be kind of 
  annoying... 
  Any ideas? 
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

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


NB_ RESENDING in plain text - sorry, Outlook keeps seinding in html no matter what 
default i set!
Hi lists,
 
I am using Quest Shareplex product for Oracle to Oracle one way replication.  I 
have two systems (source and target) and two environments  (dev, demo).  On system 
one, the environments are setup as schemas within one oracle instance (therefore each 
schema will be a SOURCE in the replication).  My other system has each environment set 
up a separate Orace Instances (therefore each instance will become a TARGET in the 
replication).
 
I am trying to configure 2 separate replication streams (ie so that each 
replication process is SEPARATE from the other - one for DEV and one for DEMO).  I 
will accomplish this by setting up Shareplex to use mulitple processes.
 
HOWEVER, Quest technical support has told me that this will cause contention.  
However, I dont see why is would from an os/oracle point of view.  Basically Shareplex 
has a process which reads the online redo logs. tech support is suggesting 
that is there a two processes trying to access the same block in the logs that 
contention can occur.  This does not make sense to me.  Below is the blurb from 
techincal support when I questioned their initial repsonse:
 
*
The reason you might run into a contention is because multiple captue processes may be 
reading the same data block in the redo log.  Since there is only one process that can 
access a single block, the other process may have to wait.
Contention is a possibilty, and you will need to run some bench marks to find out how 
much, if any, contention you will have.
*
 
I would find it HARD to believe that only ONE process can read a block at a time.  If 
this were true, then OLTP system would FAIL miserably!
 
Anyone have any ideas/comments regarding the OS and Oracle interaction  I mean are 
not the logs at this pointa UNIX file?  and can't multiple processes read a single 
unix file without bringing the whole system to its knees?
Also,  I am NOT knocking the techincal support, but I believe that the opinion was 
formulated on an incorrect assumption on the operating system and Oracle.
Thoughts/comments?
 
Thanks in advance. 
 
Hannah
 
 
 
 
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

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

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

Re: Database link performance

2002-06-06 Thread Michael Rosenblum

Title: Message





Hi, all!

Thanks for your help and suggestions. Here is 
some feedback on what I have found on the performance problem 
of the querying data between two databases 
using DB links.

To John Kanagaraj: filter by 
ROWNUM does not make any difference  I have absolutely the same speed, when 
querying table with 5000 rows and filtering where ROWNUM 
5000.

To Tom Mercadante: creating of a 
view, that does filtering, did not help. Absolutely the same 
results!

Just remind you the 
case:
declare 
 v_record B%rowtype;

 cursor cB
 is
 select *
 from 
[EMAIL PROTECTED]
 where rownum  
5000;
begin
 for c in cB
 loop
 v_record 
:=c;
 end loop;
end;

My next step has been to analyze 
session-level statistics.

When using link we have 
physically two sessions  from a client to db_A (opened explicitly) and from 
db_A to db_B (opened by Oracle automatically). After execution of a script: 
- in the session to db_A we have 1 SQL*Net roundtrips to/from 
db_link
- in the session to db_B we have 
1 SQL*Net roundtrips to/from client.

But when I am running the same 
script directly (from client to db_B)  there is only ONE SQL*Net roundtrip 
to/from the client.

I asked Paul Dorsey to check it 
out with Tom Kyteat yesterdays NYOUG meeting.

Tom told us that Oracle 8.* does 
single record fetches when using database links. In general, database links have 
been tuned to support database replication. It means, that create table new_A 
as select  from table_A@db_B runs quickly but querying from new_A is quite 
slow.

The remaining question: has 
anybody tried to use database links in Oracle9i? Does the same single fetch 
limitation apply?

Thank you,
Michael Rosenblum
Dulcian 
Inc.

RE: Complex Integrity Checking

2002-06-06 Thread Richard Huntley

Title: RE: Complex Integrity Checking

Iulian, here is everything you need to recreate that,
table, package, row level trigger, statement level trigger and test
data.
Once
you've duplicated this, feel free to modify and hopefully you'll be able to do
this for your specific case.
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Thursday,
June 06, 2002 3:13 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: Complex Integrity
Checking
Ok
Richard, this seems to be what I want.
I read
carefully the message but I didn't find the trigger
RHUNTLEY.SINTERVAL
How
did you do that?
Thanks!

iulian

-Original Message-From: Richard Huntley
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 7:44
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
Complex Integrity Checking

Iulian, this is what you want, NO? (except this works for date
fields not number fields as you've put in your latest
posts)... This is done using two triggers.
SQL insert into interval values('01-JAN-2002','01-MAR-2002');
2 1 row created.
SQL insert into interval values('03-MAR-2002','26-MAR-2002');
2 1 row created.
SQL insert into interval values('03-FEB-2002','14-MAR-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval values('01-DEC-1999','01-JAN-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval values('05-JAN-2002','01-FEB-2002');
2 insert into interval
* ERROR at line 1: ORA-2:
date overlap 03-FEB-02 14-MAR-02 ORA-06512: at
"RHUNTLEY.SINTERVAL", line 23 ORA-04088: error during
execution of trigger 'RHUNTLEY.SINTERVAL'
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 05, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking
-Original Message- Sent:
Wednesday, June 05, 2002 4:53 PM To: Multiple
recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure
Antivirus administered by IT Network
Department. **

two questions: How many records do you insert into that
table before a commit ?
Is the whole issue simply mutating table error when running
some business logic in an insert/update trigger for
the intervals table?
Regards,
Waleed
I'm sorry bu I can't answer to your questions because I don't
see the point.
Here's a test table: CREATE TABLE
intervals ( start_time NUMBER NOT NULL,
end_time NUMBER
NOT NULL )
Here are some statemens:
INSERT INTO intervals (START_TIME,END_TIME) VALUES (3,5) / INSERT INTO
intervals (START_TIME,END_TIME) VALUES (2,3) /
INSERT INTO intervals (START_TIME,END_TIME) VALUES (7,8)
What I want is that the integrity rule (no overlapped
intervals) be operational even if i insert a new
record or more or update one or more. Think of it the
same way an unique key works. This is a simplified
table for example purpose. In fact my application is a resource scheduler, so I want a resource not to be assigned for more
than 1 client at the same time. Here the start_time and end_time are of number type just for testing,
but of course it'll be of date type.
I'm starting to think that what I want, can be done in a
simple, clean manner but using complex workarounds,
isn't it? Thanks!
iulian
-Original Message- To:
Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33
AM
First of all I want to thank you all for your answers.
Let's take'em one by one:

Attn: Mercadante, Thomas F
[[EMAIL PROTECTED]] - I cannont use "instead
of" trigger because of this error:
ORA-25002: cannot create INSTEAD OF triggers on tables
Cause: Only BEFORE or AFTER triggers can be created on a
table. Action: Change the trigger type to BEFORE or
AFTER.
I have an Oracle database version 9.0.1.1.1

Attn: Stephane Faroult
[[EMAIL PROTECTED]] - for insert your approach works
(although I have to change a bit the select
in exists condirion) but what about the update statements.
- moreover i think this will not keep my integrity
rule consistent, if someone try to simply use typical
insertupdate statements.

Re: Establishing policies, standard operating procedures, and respons

2002-06-06 Thread Peter Barnett


So, why do you need written rules?  Are these folks
not talking to each other?

I currently work in a highly proceduralized
environment.  Nothing routine gets done in less than
three weeks after being blessed by several layers of
management and a change review committee.  This is a
very inefficient way to do business.  Oddly enough, if
it really matters, the Unix group and the DBAs figure
it out together and just get the work done.

A good starting point for procedures can be found on
Microsoft's web site - the SQL Server operations
manual.  Sun's site also has some good documentation
guidelines but you will have to extrapolate for your
environment.  

Let common sense be your guide!


--- Browning, Alan [EMAIL PROTECTED]
wrote:
  Can someone please point me to in the right
 direction?
  
  We need to establish policies, standard operating
 procedures,
  and responsibilities for our Oracle DBA and Sun
 Unix Admin.
  
  Is there any literature that outline typical
 practices on how to divide
  systems and database administration
 responsibilities?
  
  
  Thanks in Advance!
  
  
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Browning, Alan
   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).


=
Pete Barnett
Lead Database Administrator
The Regence Group
[EMAIL PROTECTED]

__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Peter Barnett
  INET: [EMAIL PROTECTED]

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

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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread David Wagoner

Title: How to move 200 GB db from prod to dev?









Search the
Oracle-L archives- this question has been covered several times recently.



Also, Oracle
DBA Tips  Techniques by Sumit Sarin (2000, Oracle Press) has detailed
instructions for cloning a database in Chapter 1. Note the error in the script on page 43: create controlfile REUSE database should be create
controlfile SET database. Otherwise, the procedure works
wonderfully. 





Successful? Begun the clone wars have. Master Yoda





HTH,





david



David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide, Inc.

8000 Regency
Parkway, Suite 110

Cary, NC
27511-8582

Office (919)
466-6723

Pager
[EMAIL PROTECTED]

Fax (919)
466-6783

http://www.arsenaldigital.com/




*** NOTICE ***

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



-Original
Message-
From: Daiminger, Helmut
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 06, 2002
10:28 AM
To: Multiple recipients of list
ORACLE-L
Subject: How to move 200 GB db
from prod to dev?



Hi! 

We are supposed to clone our production
database onto a new development box (both boxes are Sun Solaris). The db is
about 200 GB in size.

What would be the best way to achieve
this? Simply copying over the files won't work, since the instance names are
different:

Production: SID=PCLDB1 = e.g.
/u02/oradata/PCLDB1/system01.dbf 
Development: SID=ROLAND = e.g.
/u02/oradata/ROLAND/system01.dbf 

So would export/import the entire db be
the only way? (But writing out dump file that big should be a little disk space
problem...)

Renaming all the datafiles (approx. 100)
would be kind of annoying... 

Any ideas? 

This is 8.1.7 on Sun Solaris. 

Thanks, 
Helmut

Is this a good upgrade path from 8.0.4 to 8.1.7.2?

2002-06-06 Thread Cherie_Machler



We are preparing to do an upgrade of our Data Warehouse on Sun Solaris from
version 8.0.4.0.0 to 8.1.7.2.

We've done a number of upgrades in the past but this is the first time we
are going from 8.0.4 to 8.1.7.

Following is the basic, high level plan:

1.  Upgrade from 8.0.4 to 8.1.7.0 (Oracle software already pre-installed in
separate Oracle Home).
2.  Apply 8.1.7.2 patchset
3.  Apply separate one-off-patch that we had created for a star
transformation bug.

We are going to use the manual migration method as outlined in the 8.1.7
migration manual.

I've already tested this approach on a full-size RMAN clone of production
that we moved to our QA box.   However, that box already had 8.1.7.2
pre-installed on it before I actually migrated the database.

Just wondering if anyone has gone directly from 8.0.4 to 8.1.7.2.Are
there any gotchas or concerns?   At one point I thought we might have to
upgrade from 8.0.4 to 8.1.5 and then from 8.1.5 to 8.1.7.   But now I can't
find anywhere in the documentation where it says that I need to do that so
we are thinking about saving a little time and going straight from 8.0.4 to
8.1.7.

This is a 200 Gig warehouse which uses Verita Quick I/O, partitioning, star
schema, etc.

Thanks to anyone who can alert me to any potential pitfalls.   I cannot
afford any glitches at all on this upgrade.

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network

-- 
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).

Yo Jared

2002-06-06 Thread Thomas Day



Twice today I've received virus alerts from my mail server.  In each case
it appeared that a list member (different members) sent me what appeared to
be a MSoft graphic.  I'm not sure if someone is getting the list traffic
and spoofing the id's or what...

I didn't keep the first one since I thought that it was a one-time fluke.
The second one came from sundar sundar
@timesgroup.com direct to me, not to the list, the subject was 1997
Microsoft Corporation. All rights and the contents was a file called
settingup_1.pif.  I have no idea what the file's contents really were.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas Day
  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 32 Bit running on Solaris 64 Bit

Title: RE: Oracle 32 Bit running on Solaris 64 Bit






Is it possible that you have vendor software that doesn't support Oracle 64-bit? Before any decisions are made, I would compile a list of all vendor software on the box and also those used specifically for the application. Then research each vendor and ask if they are compatible with Oracle 64 bit.

Quick funny story... a colleague of mine recently called a vendor to ask if their product (which we currenyly use in our system) was compatible with Oracle 9i.

Somehow, the technical support person was able to reply with a straight face:


 Our product IS compatible with Oracle 9i.. but.. only if you do not use any Oracle 9i features...

So. you really need to talk to your vendors.


Hannah





-Original Message-

From:  [EMAIL PROTECTED]@SUNGARD On Behalf Of Daiminger, Helmut [EMAIL PROTECTED]

Sent: Thursday, June 06, 2002 10:28 AM

To: Multiple recipients of list ORACLE-L

Subject: Oracle 32 Bit running on Solaris 64 Bit



Hi there! 

We are running 32-Bit Oracle Software on Sun Solaris 8 (64-Bit). 

What is the advantage of doing this? 

Why don't we use 64-Bit Orlacle on 64-Bit Solaris? 

Nobody here can answer my question and the systems were set up by a consultant. So nobody really knows why this was done... Since we are talking about productions systems, upgrading Oracle Software is not an option...

This is 8.1.7 on Sun Solaris. 

Thanks, 

Helmut 




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

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

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

RE: How to move 200 GB db from prod to dev?

Title: RE: How to move 200 GB db from prod to dev?






Dump the prod control to trace

Copy the db files over to dev

edit the dumped controlfile (you can change db name here) 

replace the copied controlfile with the new eidted one and bring up dev


I forget exactly what needs to change in the controlfile file script but db name and I believe reset logs


Hannah



-Original Message-

From:  [EMAIL PROTECTED]@SUNGARD On Behalf Of Daiminger, Helmut [EMAIL PROTECTED]

Sent: Thursday, June 06, 2002 10:28 AM

To: Multiple recipients of list ORACLE-L

Subject: How to move 200 GB db from prod to dev?



Hi! 

We are supposed to clone our production database onto a new development box (both boxes are Sun Solaris). The db is about 200 GB in size.

What would be the best way to achieve this? Simply copying over the files won't work, since the instance names are different:

Production:  SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf 

Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 

So would export/import the entire db be the only way? (But writing out dump file that big should be a little disk space problem...)

Renaming all the datafiles (approx. 100) would be kind of annoying... 

Any ideas? 

This is 8.1.7 on Sun Solaris. 

Thanks, 

Helmut 




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

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

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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread Magaliff, Bill

Title: How to move 200 GB db from prod to dev?



1) use 
RMAN to clone the db; or
2) 
create the new dev db with the required users but without all the data/index 
tablespaces, and use transportable tablepsace feature to move tablespaces/data 
files . . . export/import of meta-data is quick - only real time factor is 
copying the data files - this assumes, of course, that you can make your 
production tablespaces read-only for the time it takes to copy (or tar) the data 
files 

-bill

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 
  2002 10:28 AMTo: Multiple recipients of list 
  ORACLE-LSubject: How to move 200 GB db from prod to 
  dev?
  Hi! 
  We are supposed to clone our production database onto a new 
  development box (both boxes are Sun Solaris). The db is about 200 GB in 
  size.
  What would be the best way to achieve this? Simply copying 
  over the files won't work, since the instance names are different:
  Production: SID=PCLDB1 = e.g. 
  /u02/oradata/PCLDB1/system01.dbf Development: 
  SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 
  So would export/import the entire db be the only way? (But 
  writing out dump file that big should be a little disk space 
  problem...)
  Renaming all the datafiles (approx. 100) would be kind of 
  annoying... 
  Any ideas? 
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

RE: Oracle 32 Bit running on Solaris 64 Bit

2002-06-06 Thread John . Hallas

Title: Oracle 32 Bit running on Solaris 64 Bit



As far as I am aware there is no advantagein 
running 32 s/w on a 64 bit o/s. In fact the reverse is true, there could be 
significant disadvantages.
Any impact would be at a very low level and not 
noticeable from a user perspective (unless data became corrupted :) 


Check on Metalink or log a TAR

John

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 
  15:28To: Multiple recipients of list ORACLE-LSubject: 
  Oracle 32 Bit running on Solaris 64 Bit
  Hi there! 
  We are running 32-Bit Oracle Software on Sun 
  Solaris 8 (64-Bit). What is the advantage 
  of doing this? Why don't we use 64-Bit 
  Orlacle on 64-Bit Solaris? 
  Nobody here can answer my question and the systems 
  were set up by a consultant. So nobody really knows why this was done... Since 
  we are talking about productions systems, upgrading Oracle Software is not an 
  option...
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

Re: How to move 200 GB db from prod to dev?

2002-06-06 Thread Ramon E. Estevez

Title: How to move 200 GB db from prod to dev?



Helmut,

I have just done it right this 
morning. Not that big but worked.

Pasos para Clonar Bases de Datos

1-) Take a cold backup of the Origen DB- 
Preferiblemente frio

2-) Take a backup of the controlfile of the 
originDB

3-) Copy all thedatafiles from the origin 
DB to the copy DB. If thebackup taken is 
hot also copy the redo logs files and archives 


4-) Modify the controlfile with the new path of 
thedatafiles y redo logs files,
 remove the 
REUSE clause, theRECOVER command, 
 add theRESETLOGS to the 
ALTER DATABASE OPEN command.

The controlfile should be that way.
- El Archivo debe quedar de esta 
manera--- STARTUP NOMOUNT CREATE 
CONTROLFILE SET DATABASE "new clone 
name"NOARCHIVELOG 
MAXLOGFILES 255 MAXLOGMEMBERS 
5 MAXDATAFILES 
1022 MAXINSTANCES 
5 MAXLOGHISTORY 
100 LOGFILE 
GROUP 1  
('/u01/oradata/redo01a.log', 
'/u01/oradata/redo01b.log') SIZE 
10M, GROUP 
2 
('/u01/oradata/redo02a.log', 
'/u01/oradata/redo02b.log') SIZE 
10M, GROUP 
3 
('/u01/oradata/redo03a.log', 
'/u01/oradata/redo03b.log') SIZE 10M 
RESETLOGS 
DATAFILE '/u03/oradata/data01.dbf' 
SIZE 300M, 
'/u03/oradata/data02.dbf' SIZE 
300M, '/u03/oradata/data03.dbf' 
SIZE 300M, 
'/u03/oradata/data04.dbf' SIZE 300M;-- Save 
this file with .sql extension 

6-) Connect to the clone DB with sysdba 
privileges
 and execute the 
script

7-) Mount and open the y abrir la BD con 
Resetlogs ALTER DATABASE MOUNT; 
ALTER DATABASE OPEN RESETLOGS;

---

Think doesn't omitted something translating from 
spanish.

Luck,

Ramon



  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Thursday, June 06, 2002 9:27 
  AM
  Subject: How to move 200 GB db from prod 
  to dev?
  
  Hi! 
  We are supposed to clone our production database onto a new 
  development box (both boxes are Sun Solaris). The db is about 200 GB in 
  size.
  What would be the best way to achieve this? Simply copying 
  over the files won't work, since the instance names are different:
  Production: SID=PCLDB1 = e.g. 
  /u02/oradata/PCLDB1/system01.dbf Development: 
  SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 
  So would export/import the entire db be the only way? (But 
  writing out dump file that big should be a little disk space 
  problem...)
  Renaming all the datafiles (approx. 100) would be kind of 
  annoying... 
  Any ideas? 
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

Re: Establishing policies, standard operating procedures, and

2002-06-06 Thread Steven Lembark




-- Browning, Alan [EMAIL PROTECTED]

 Can someone please point me to in the right direction?

 We need to establish policies, standard operating procedures,
 and responsibilities for our Oracle DBA and Sun Unix Admin.

 Is there any literature that outline typical practices on how to divide
 systems and database administration responsibilities?


SAGE has some useful guidelines for these things. Also
might want to check the UNIX Sys. Admin. Handbook (Nemeth,
et al), 3rd Ed. under Politics at the end for good examples.


http://opamp.com/cf/title.cfm?SRow=1Title=Unix+System+Administration+Hand
bookAuthor=Nemeth
--
Steven Lembark   2930 W. Palmer
Workhorse Computing   Chicago, IL 60647
+1 800 762 1582
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Lembark
  INET: [EMAIL PROTECTED]

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

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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread John . Hallas

Title: How to move 200 GB db from prod to dev?



A few options. Do you use RMAN for backups? , if so 
duplicate the database and rename the files as part of that 
process.
Otherwise clone the database by copying the files at 
the o/s level and change the instance name. Several documents on metalink show 
how to do this.

If you have used an OFA setup then renaming the 
datafiles should not be that difficult, it will only be the directory named 
after the sid that willl need changing
On a database that size export would take some time but 
it is another option.

John


  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 
  15:28To: Multiple recipients of list ORACLE-LSubject: 
  How to move 200 GB db from prod to dev?
  Hi! 
  We are supposed to clone our production database onto a new 
  development box (both boxes are Sun Solaris). The db is about 200 GB in 
  size.
  What would be the best way to achieve this? Simply copying 
  over the files won't work, since the instance names are different:
  Production: SID=PCLDB1 = e.g. 
  /u02/oradata/PCLDB1/system01.dbf Development: 
  SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 
  So would export/import the entire db be the only way? (But 
  writing out dump file that big should be a little disk space 
  problem...)
  Renaming all the datafiles (approx. 100) would be kind of 
  annoying... 
  Any ideas? 
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

Re: NLS_NUMERIC_CHARACTERS

2002-06-06 Thread Jan Pruner


SELECT  
TO_NUMBER('123,67','999D99', 'NLS_NUMERIC_CHARACTERS='', ''') 
FROM dual;

There is empty space after ,  in ='', ''') .

JP
On Thursday 06 June 2002 16:27, you wrote:
 Hi,
  I have NLS_NUMERIC_CHARACTERS set to  '.,' .
 The numeric data appears in the char format 34566,45
 How to convert this to a number format 34566.45 using to_number function

  without replacing ',' with '.'
 using replace function.
 I dont want to use alter session.


 Thanks
 Manoj.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
  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 32 Bit running on Solaris 64 Bit

2002-06-06 Thread kkennedy

Title: Oracle 32 Bit running on Solaris 64 Bit



Seems 
to me that patches and upgrades for the 32-Bit versions come out 
quicker.One disadvantage of the 32-Bit version is if you require 
more than 2Gb of shared memory (SGA for all instances on the box). I've 
stuck with the 32-Bit versions except at one site where we had 8Gb of real 
memory and the PHB and the PH Lead DBA insisted on using as much of it as they 
could (which turned out to be an exceptionallybad idea, but that's another 
story).

Kevin KennedyFirst Point Energy Corporation 


  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 
  2002 7:28 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Oracle 32 Bit running on Solaris 64 
  Bit
  Hi there! 
  We are running 32-Bit Oracle Software on Sun 
  Solaris 8 (64-Bit). What is the advantage 
  of doing this? Why don't we use 64-Bit 
  Orlacle on 64-Bit Solaris? 
  Nobody here can answer my question and the systems 
  were set up by a consultant. So nobody really knows why this was done... Since 
  we are talking about productions systems, upgrading Oracle Software is not an 
  option...
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut

RE: Re: Textfile into oracle

2002-06-06 Thread John . Hallas


Roland,
If you are saying that you have 100 columns in the spreadsheet but you only
want to copy 9 columns into a table with 9 fields then you have 2 choices.
Reduce the spreadsheet to only 9 columns (by copying to another worksheet)
and then saving as a CSV file and importing by sqlloader. I always think it
best to limit the data at the source end before transfer rather than
afterwards.

The other option is to use sqlloader to only pull out the fields that you
want. I have had a quick look at the online manuals but cannot see how to do
that unless you have a fixed position file which I assume is not the case 
(the syntax is below just in case)

LOAD DATA
   INFILE 'ulcase5.dat'
   BADFILE 'ulcase5.bad'
   DISCARDFILE 'ulcase5.dsc'
1)   REPLACE
2)INTO TABLE emp
   (empno   POSITION(1:4) INTEGER EXTERNAL,
   enamePOSITION(6:15)CHAR,
   deptno   POSITION(17:18)   CHAR,
   mgr  POSITION(20:23)   INTEGER EXTERNAL)

There is a good sqlloader web-site (mentioned on this list a couple of
months ago). Unfortunately I have just started a new contract and do not
have internet access as yet. Do a search on Google

HTH

John

-Original Message-
Sent: 06 June 2002 15:28
To: Multiple recipients of list ORACLE-L



Could anyone please give me  an example on how the sqlloader script would
look like , How many fields would it be,?  I cant find anything ofthis in
themanual. I have 100 fields in the excel file but only 9-10 field names...


Thanks in advance


Roland





Alexandre Gorbatchev [EMAIL PROTECTED]@fatcity.com den
2002-06-06 03:03 PST

Sänd svar till [EMAIL PROTECTED]

Sänt av:  [EMAIL PROTECTED]


Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Kopia:

Hello Roland,

The easiest way is to save it from excel as flat comma-separated text file.
Then you should use Oracle SQL*Loader tool and yes you will have to
pre-create the table with all fields you need. (99,9% that's what you need)

You cannot import .xls file into Oracle database directly with standard
oracle tools. I mean with structure, not into the lob filed.

You could access file from PL/SQL and write your own import procedure. (not
a trivial task and not feasible probably :-)
Can use odbc from Excel directly.
May be there are some third-party tools to import.

Alexandre

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 06, 2002 12:03 PM


 Hallo,

 I have this excelfile. Is it possible to import this into an oracle table.
This file is supposed to be located on unix machine from the beginning and
the import into an oracle table would be done from unix. How should I name
the fields in the oracle
 table. I mean should I use the 9-10 different field names in the
excelfile.  There is at least 100 different columns in this excelfile. Is it
possible to import a file like that into oracle table,

 Please help me with some hints.
 (See attached file: try.xls)

 Thanks in advance

 Roland


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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread Sherman, Edward


I have a similar situation and here's how I  deal with it.
 
(I'm assuming your datafiles have the same name but the pathname is
different)
 
Production: SID = PROD = e.g.
/zbackup/array3/oracle8/dbs73/PROD/wds01.data.dbf
Development: SID = WDSU = e.g. /extdisk/oracle8/dbs73/PROD/wds01.data.dbf
 
In SQL*PLUS: 
 
SQL ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 
Locate the trace file and copy it to the development machine.
 
Move data with a script similar to this:
 
 
rcp -rp /zbackup/oracle1/dbs73/PROD/* george:/extdisk/oracle1/dbs73/PROD
rcp -rp /zbackup/oracle2/dbs73/PROD/* george:/extdisk/oracle2/dbs73/PROD
.
.
.
rcp -rp /zbackup/array3/oracle8/dbs73/PROD/*
george:/extdisk/oracle8/dbs83/PROD
 
Yes it's annoying but you only have to write the script once!
 
Now you can edit that trace file while you wait for your data to finish
moving:
 
In my trace file I change the line:
CREATE CONTROLFILE REUSE DATABASE PROD NORESETLOGS ARCHIVELOG
 
to
 
CREATE CONTROLFILE SET DATABASE WDSU RESETLOGS NOARCHIVELOG
 
This changes the instance name (I'm not using log archiveing on the dev
database).
 
Now I have to edit all those pathnames.
I have stuff like
 
LOGFILE
  GROUP 1 (
'/oracle4/dbs73/PROD/redoPROD07a.log',
'/oracle1/dbs73/PROD/redoPROD07b.log'
 
Need to be changed to:
 
   '/extdisk/oracle4/dbs73/PROD/redoPROD07a.log',
   '/extdisk/oracle1/dbs73/PROD/redoPROD07b.log'
 
Also, things like
 
   '/array3/oracle8/dbs73/PROD/wds01data.1.dbf',
 
Needs to be changed to
 
  '/extdisk/oracle8/dbs73/PROD/wds01data.1.dbf',
 
 
But its not really a problem if you can use the vi editor:
 
Hit ESC key
then :1,$s/\/oracle/\/extdisk\/oracle/g ENTER
 
This changes all the /oracle to /extdisk/oracle
 
This also has the side effect that all my /array3/oracle8 has changed to
/array3/extdisk/oracle8.
 
Need to get rid of the /array3
 
In vi,
 
Hit ESC Key
then :1,$s/\/array3//g
 
Now all my paths are correct for the development database.
 
At the bottom of the controlfile tracefile I comment out the lines:
 
-- RECOVER DATABASE
-- ALTER SYSTEM ARCHIVE LOG ALL
 
Change 
 
ALTER DATABASE OPEN;
 
to
 
ALTER DATABASE OPEN RESETLOGS;
 
Delete lines in the trace file from the first line until you get to the line
that says:
 
STARTUP NOMOUNT
 
Also delete or comment out any lines with a # character as the first
character.
 
Delete my old control files and run the trace file from SQL*PLUS to create
the new controlfiles and open the instance.
 
The renaming of the paths was done in two steps with vi but you can write a
shell script using sed to do the substitution for you if you like.
 
Maybe annoying but you only have to write the script once!
 
 
In your example you need to change PCLDB1 to ROLAND
 
You bring up the trace file with the CREATE CONTROLFILE script in vi and
just do:
 
Hit ESC
:1,$s/PCLDB1/ROLAND/g ENTER
 
and PCLDB1 will be substituted for ROLAND everywhere in the file.
 
If you want to write a script for this and are not familiar with sed then
maybe the UNIX sysadmin or a UNIX developer can help you with this.
 
After you get the scripts written then its really easy to move you data and
get the instance running. It's only annoying the first time!
 
Good luck,
Ed
 
 
 
 -Original Message-
Sent: Thursday, June 06, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L



Hi! 

We are supposed to clone our production database onto a new development box
(both boxes are Sun Solaris). The db is about 200 GB in size.

What would be the best way to achieve this? Simply copying over the files
won't work, since the instance names are different:

Production:  SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf 
Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 

So would export/import the entire db be the only way? (But writing out dump
file that big should be a little disk space problem...)

Renaming all the datafiles (approx. 100) would be kind of annoying... 

Any ideas? 

This is 8.1.7 on Sun Solaris. 

Thanks, 
Helmut 




* * * * * Freedom of Information Act Notice * * * * * 
The information in this email is subject to the record protection mandated
by 5 United States Code 552 (b) (4) and relevant judicial opinions.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sherman, Edward
  INET: [EMAIL PROTECTED]

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

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

RE: I/O contention with external process reading the oracle logs

2002-06-06 Thread Gogala, Mladen


If your redo log files are normal unix files (i.e. not raw devices
or Veritas quick I/O files, then the first process to read a block
will bring that block into the buffer cache. The second process will
find already in the cache, which might benefit the other guy. I/O 
contention seems unlikely. Running into an insufficiently qualified 
support person, on the other hand, seems very likely. You can always
use replication to copy things to another instance.

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, June 06, 2002 10:32 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: I/O contention with external process reading the oracle
 logs (online redo logs)
 
 
 NB_ RESENDING in plain text - sorry, Outlook keeps seinding 
 in html no matter what default i set!
 Hi lists,
  
 I am using Quest Shareplex product for Oracle to Oracle 
 one way replication.  I have two systems (source and target) 
 and two environments  (dev, demo).  On system one, the 
 environments are setup as schemas within one oracle instance 
 (therefore each schema will be a SOURCE in the replication).  
 My other system has each environment set up a separate Orace 
 Instances (therefore each instance will become a TARGET in 
 the replication).
  
 I am trying to configure 2 separate replication streams 
 (ie so that each replication process is SEPARATE from the 
 other - one for DEV and one for DEMO).  I will accomplish 
 this by setting up Shareplex to use mulitple processes.
  
 HOWEVER, Quest technical support has told me that this 
 will cause contention.  However, I dont see why is would from 
 an os/oracle point of view.  Basically Shareplex has a 
 process which reads the online redo logs. tech 
 support is suggesting that is there a two processes trying to 
 access the same block in the logs that contention can occur.  
 This does not make sense to me.  Below is the blurb from 
 techincal support when I questioned their initial repsonse:
  
 **
 ***
 The reason you might run into a contention is because 
 multiple captue processes may be reading the same data block 
 in the redo log.  Since there is only one process that can 
 access a single block, the other process may have to wait.
 Contention is a possibilty, and you will need to run some 
 bench marks to find out how much, if any, contention you will have.
 **
 ***
  
 I would find it HARD to believe that only ONE process can 
 read a block at a time.  If this were true, then OLTP system 
 would FAIL miserably!
  
 Anyone have any ideas/comments regarding the OS and Oracle 
 interaction  I mean are not the logs at this pointa UNIX 
 file?  and can't multiple processes read a single unix file 
 without bringing the whole system to its knees?
 Also,  I am NOT knocking the techincal support, but I believe 
 that the opinion was formulated on an incorrect assumption on 
 the operating system and Oracle.
 Thoughts/comments?
  
 Thanks in advance. 
  
 Hannah
  
  
  
  
  
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: 
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  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: why so much slower

2002-06-06 Thread Paula_Stankus

Title: RE: why so much slower





Tried that


-Original Message-
From: Mike Killough [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 05, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: why so much slower



Why don't you try using a leading or ordered hint to get oracle to use the 
smaller table first?



From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: why so much slower
Date: Wed, 05 Jun 2002 12:18:45 -0800

more info. It seemed just when I went from two to three tables in a join
there was a very substantial increase in elapsed time. I did join with one
large table and small codetable alone and performed like a champ. H.
Any ideas?

-Original Message-
Sent: Wednesday, June 05, 2002 3:03 PM
To: '[EMAIL PROTECTED]'



Set sort_area_size to very large as 20Gb (obscene) amount of space
available.
Doing 2 large table outer joins returns results in .341 seconds - both
partitioned on same criteria
added one small codetable equijoin with one of the larger tables. There is
a foreign key to codetable and index that is unique.
Used hash join hint
Used nested loop hint

Basically saw two large joins sort merged hash join then nested join to
smaller table - much much smaller codetable.

NO matter what it seems query is much much slower - Any ideas?








_
Chat with friends online, try MSN Messenger: http://messenger.msn.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Killough
 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: Database link performance

2002-06-06 Thread Mercadante, Thomas F

Title: Message



Mike,

you 
created the view on the db_B machine and called it from db_A, 
right?


"To Tom 
Mercadante: creating of a view, that does filtering, did not help. Absolutely 
the same results!"

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Michael Rosenblum 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 12:03 
  PMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  Database link performance 
  
  
  Hi, all!
  
  Thanks for your help and suggestions. Here is 
  some feedback on what I have found on the performance 
  problem of the querying data between two 
  databases using DB links.
  
  To John Kanagaraj: filter by 
  ROWNUM does not make any difference  I have absolutely the same speed, when 
  querying table with 5000 rows and filtering where ROWNUM 
  5000.
  
  To Tom Mercadante: creating of 
  a view, that does filtering, did not help. Absolutely the same 
  results!
  
  Just remind you the 
  case:
  declare 
   v_record 
  B%rowtype;
  
   cursor cB
   is
   select *
   from 
  [EMAIL PROTECTED]
   where rownum  
  5000;
  begin
   for c in cB
   loop
   v_record 
  :=c;
   end loop;
  end;
  
  My next step has been to 
  analyze session-level statistics.
  
  When using link we have 
  physically two sessions  from a client to db_A (opened explicitly) and from 
  db_A to db_B (opened by Oracle automatically). After execution of a script: 
  - in the session to db_A we have 1 SQL*Net roundtrips to/from 
  db_link
  - in the session to db_B we 
  have 1 SQL*Net roundtrips to/from client.
  
  But when I am running the same 
  script directly (from client to db_B)  there is only ONE SQL*Net roundtrip 
  to/from the client.
  
  I asked Paul Dorsey to check it 
  out with Tom Kyteat yesterdays NYOUG meeting.
  
  Tom told us that Oracle 8.* 
  does single record fetches when using database links. In general, database 
  links have been tuned to support database replication. It means, that create 
  table new_A as select  from table_A@db_B runs quickly but querying from 
  new_A is quite slow.
  
  The remaining question: has 
  anybody tried to use database links in Oracle9i? Does the same single fetch 
  limitation apply?
  
  Thank you,
  Michael 
Rosenblum
  Dulcian 
  Inc.

Re: Archiving in OPS

2002-06-06 Thread Bill Pass


Metalink has a note on best practices on this.

The best method of these in my opinion is to have n
unqiue archive destinations (one per instance). Then
you can NFS cross mount these destinations to the same
location from each instance in your environment. RMAN
can then backup/recover the entire database from a
single server without having to worry about moving
files around.

Note that if an instance fails, any of the surviving
instances can and will archive logs on behalf of the
failed instance to the local instances archive
destination.

Bill

--- Ramon E. Estevez [EMAIL PROTECTED]
wrote:
 Hi list,
 
 Scenario OPS 2 nodes, Oracle 8.1.7, AIX. (New
 dealing with OPS)
 
 What is the best solution for implementing archiving
 in OPS.  As far as I understand the 2 instances will
 be generating archives.  If one of the instance goes
 down, what happen with the destination of those
 archives ?
 
 I will use RMAN with Legato for the backup strategy.
 
 I have been gathering tips from the list in those
 days, but any special recomendations, documentation
 or scripts about it will be very highly apreciated.
 
 
 TIA
 
 Ramon E. Estevez
 [EMAIL PROTECTED]
 809-565-3121
 
 


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Pass
  INET: [EMAIL PROTECTED]

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

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

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

2002-06-06 Thread Aponte, Tony

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






I think they are alluding to UNIX file system contention. If the redo logs are in regular file systems (not raw, Veritas Quick I/O, etc.) then UNIX (at least in my Solaris environment) needs to lock the file for each of the Shareplex capture processes, in addition to LGWR. 

There will also be some contention inside the source database that is not mentioned in their response. Shareplex needs to query the source table to get the primary key value for the row that changed. It does it using the rowid that was scraped off the redo log. It then uses the primary key value from the source table to build the insert statement for the target. In our installation this process amounts to 5% of the CPU used by this session statistic. Although the blocks needed are still in the buffer cache, there is some serialization that has to occur to fulfill the logical I/O.

BTW, in 9i the logical standby implementation includes the primary key value in the redo stream after extended logging is activated. This relieves the source from the backwards-looking access for the primary key as done by Shareplex. I doubt that the performance gain of extended logging is totally free though.

HTH.

Tony


-Original Message-

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

Sent: Thursday, June 06, 2002 10:32 AM

To: Multiple recipients of list ORACLE-L

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

logs (online redo logs)



NB_ RESENDING in plain text - sorry, Outlook keeps seinding in html no matter what default i set!

Hi lists,



 I am using Quest Shareplex product for Oracle to Oracle one way replication. I have two systems (source and target) and two environments (dev, demo). On system one, the environments are setup as schemas within one oracle instance (therefore each schema will be a SOURCE in the replication). My other system has each environment set up a separate Orace Instances (therefore each instance will become a TARGET in the replication).



 I am trying to configure 2 separate replication streams (ie so that each replication process is SEPARATE from the other - one for DEV and one for DEMO). I will accomplish this by setting up Shareplex to use mulitple processes.



 HOWEVER, Quest technical support has told me that this will cause contention. However, I dont see why is would from an os/oracle point of view. Basically Shareplex has a process which reads the online redo logs. tech support is suggesting that is there a two processes trying to access the same block in the logs that contention can occur. This does not make sense to me. Below is the blurb from techincal support when I questioned their initial repsonse:



*

The reason you might run into a contention is because multiple captue processes may be reading the same data block in the redo log. Since there is only one process that can access a single block, the other process may have to wait.

Contention is a possibilty, and you will need to run some bench marks to find out how much, if any, contention you will have.

*



I would find it HARD to believe that only ONE process can read a block at a time. If this were true, then OLTP system would FAIL miserably!



Anyone have any ideas/comments regarding the OS and Oracle interaction  I mean are not the logs at this pointa UNIX file? and can't multiple processes read a single unix file without bringing the whole system to its knees?

Also, I am NOT knocking the techincal support, but I believe that the opinion was formulated on an incorrect assumption on the operating system and Oracle.

Thoughts/comments?



Thanks in advance. 



Hannah











-- 

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

-- 

Author: 

 INET: [EMAIL PROTECTED]


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

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



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

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

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

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

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

RE: Re: Textfile into oracle

2002-06-06 Thread Mark Leith


Roland,

At the risk of being the extremo de la broma to the OT lot.

The field names that you have specified in the excel file will need to be
deleted anyway. You will need a control file that loads the data in to a
table that has a hundred (100) fields. You will then need to save the excel
file as a .csv file, and use this new try.csv file as your infile.
Whatever the fields actually are in the csv file, it would probably be a
good idea to name the columns the same in your table..

Remember - the csv file should be data only.. No column headings are needed
as SQL*Loader will just take whatever is in the file - and squirt it in to
Oracle.. (Unless of course you use the SKIP clause - But lets not confuse
you too much in one go eh?)

No - I don't have a sample control file.. Check out the following (available
from every message that you send to this list..!)

http://www.orafaq.com/faqloadr.htm

HTH

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales  Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput  performance


-Original Message-
[EMAIL PROTECTED]
Sent: 06 June 2002 15:28
To: Multiple recipients of list ORACLE-L



Could anyone please give me  an example on how the sqlloader script would
look like , How many fields would it be,?  I cant find anything ofthis in
themanual. I have 100 fields in the excel file but only 9-10 field names...


Thanks in advance


Roland





Alexandre Gorbatchev [EMAIL PROTECTED]@fatcity.com den
2002-06-06 03:03 PST

Sänd svar till [EMAIL PROTECTED]

Sänt av:  [EMAIL PROTECTED]


Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Kopia:

Hello Roland,

The easiest way is to save it from excel as flat comma-separated text file.
Then you should use Oracle SQL*Loader tool and yes you will have to
pre-create the table with all fields you need. (99,9% that's what you need)

You cannot import .xls file into Oracle database directly with standard
oracle tools. I mean with structure, not into the lob filed.

You could access file from PL/SQL and write your own import procedure. (not
a trivial task and not feasible probably :-)
Can use odbc from Excel directly.
May be there are some third-party tools to import.

Alexandre

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 06, 2002 12:03 PM


 Hallo,

 I have this excelfile. Is it possible to import this into an oracle table.
This file is supposed to be located on unix machine from the beginning and
the import into an oracle table would be done from unix. How should I name
the fields in the oracle
 table. I mean should I use the 9-10 different field names in the
excelfile.  There is at least 100 different columns in this excelfile. Is it
possible to import a file like that into oracle table,

 Please help me with some hints.
 (See attached file: try.xls)

 Thanks in advance

 Roland


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alexandre Gorbatchev
  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: Mark Leith
  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

RE: Oracle 32 Bit running on Solaris 64 Bit

2002-06-06 Thread Kathy Duret

Title: Oracle 32 Bit running on Solaris 64 Bit



Have 
to go to Uncle Larry and ask him why they can't get there softwarerunning 
in 64bit. Oracle Appsonlyavailable 32 Bit. We had the 
same questions as well. I guess Apps 11i has so many problems and issues 
they don't have time to get a 64 Bit version out. 

We 
have the same thing here 32 bit Software on Sun 64 Bit machine. Advantage, 
maybe you have other software running 64 bit on the same machine. Maybe 
someday Oracle will have Apps and other software running at 64 
Bit.

Kathy

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 
  2002 7:28 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Oracle 32 Bit running on Solaris 64 
  Bit
  Hi there! 
  We are running 32-Bit Oracle Software on Sun 
  Solaris 8 (64-Bit). What is the advantage 
  of doing this? Why don't we use 64-Bit 
  Orlacle on 64-Bit Solaris? 
  Nobody here can answer my question and the systems 
  were set up by a consultant. So nobody really knows why this was done... Since 
  we are talking about productions systems, upgrading Oracle Software is not an 
  option...
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut 
ConfidentialThis e-mail and any 
files transmitted with it are the propertyof Belkin Components and/or its 
affiliates, are confidential,and are intended solely for the use of the 
individual orentity to whom this e-mail is addressed. If you are not 
oneof the named recipients or otherwise have reason to believethat you 
have received this e-mail in error, please notify thesender and delete this 
message immediately from your computer.Any other use, retention, 
dissemination, forwarding, printingor copying of this e-mail is strictly 
prohibited.

Archiving in OPS

2002-06-06 Thread Ramon E. Estevez




Hi list,

Scenario OPS, 2 nodes, 8.1.7, AIX using raw 
devices, EMC box.

I am new in OPS and have to implement archiving 
in an OPS environment and configure RMAN to use with LEGATO.

My question is how to organize the 
archives. What will happen if one of the nodes goes down ?

I think that the2 nodes have to see each 
other and write archive files to the same place, for instance I assume 
that the archive files have to be in the EMC box .

I have been gathering very helpful information 
from the list in the past week about RMAN, but if you have some scripts, some 
documentation, links about it will be very appreciated.

TIA

Ramon E. Estevez[EMAIL PROTECTED]809-565-3121

RE: Database link performance

2002-06-06 Thread Michael Rosenblum

Title: Message



Tom,

I 
tried both ways:
- 
creating the view on db_B and query it from db_A
- 
creating view of db_A using link to db_B

No 
difference.

Michael Rosenblum,
Dulcian Inc.

  
  -Original Message-From: Mercadante, 
  Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 
  2002 1:07 PMTo: '[EMAIL PROTECTED]'Cc: 
  '[EMAIL PROTECTED]'Subject: RE: Database link performance 
  
  Mike,
  
  you created the view on the db_B machine and called it from db_A, 
  right?
  
  
  "To Tom 
  Mercadante: creating of a view, that does filtering, did not help. Absolutely 
  the same results!"
  
  Tom Mercadante Oracle Certified Professional 
  
-Original Message-From: Michael Rosenblum 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 
12:03 PMTo: Multiple recipients of list 
ORACLE-LSubject: Re: Database link performance 



Hi, all!

Thanks for your help and suggestions. Here is 
some feedback on what I have found on the performance 
problem of the querying data between two databases using DB links.

To John Kanagaraj: filter by 
ROWNUM does not make any difference  I have absolutely the same speed, when 
querying table with 5000 rows and filtering where ROWNUM 
5000.

To Tom Mercadante: creating 
of a view, that does filtering, did not help. Absolutely the same 
results!

Just remind you the 
case:
declare 
 v_record 
B%rowtype;

 cursor cB
 is
 select *
 from 
[EMAIL PROTECTED]
 where rownum  
5000;
begin
 for c in cB
 loop
 v_record 
:=c;
 end loop;
end;

My next step has been to 
analyze session-level statistics.

When using link we have 
physically two sessions  from a client to db_A (opened explicitly) and from 
db_A to db_B (opened by Oracle automatically). After execution of a script: 
- in the session to db_A we have 1 SQL*Net roundtrips to/from 
db_link
- in the session to db_B we 
have 1 SQL*Net roundtrips to/from client.

But when I am running the 
same script directly (from client to db_B)  there is only ONE SQL*Net 
roundtrip to/from the client.

I asked Paul Dorsey to check 
it out with Tom Kyteat yesterdays NYOUG meeting.

Tom told us that Oracle 8.* 
does single record fetches when using database links. In general, database 
links have been tuned to support database replication. It means, that 
create table new_A as select  from table_A@db_B runs quickly but querying 
from new_A is quite slow.

The remaining question: has 
anybody tried to use database links in Oracle9i? Does the same single fetch 
limitation apply?

Thank you,
Michael 
Rosenblum
Dulcian 
Inc.

Re: Mysterious Deadlock

2002-06-06 Thread Rachel Carmichael


http://www.dbdomain.com/a120197.htm

it's down near the end of the article, but they talk about a deadlock
with no rows and say it appears to be a problem with the initrans or
pctfree setting on that table


--- Walter K [EMAIL PROTECTED] wrote:
 We have an application that is periodically 
 encountering what appears to be a self-deadlock. Only 
 one session is listed and it holds an exclusive (X) 
 lock and is waiting for a share (S) lock with NO ROWS 
 waited. I have pasted the deadlock graph at the bottom-
 -hopefully it will be legible.
 
 Does anyone know how such a lock could be produced? 
 I'd really like to be able to recreate such a scenario.
 
 Thanks!
 -w
 
 Current SQL statement for this session:
 update ACCOUNT set 
 ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
 MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
 _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
 CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
 NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
 IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO
 N_DATE=:ACCOUNT_ACTION_DATE where 
 ACCOUNT_ID=:key_ACCOUNT_ID
 The following deadlock is not an ORACLE error. It is a
 deadlock due to user error in the design of an 
 application
 or from issuing incorrect ad-hoc SQL. The following
 information may aid in determining the deadlock:
 Deadlock graph:
-Blocker(s)  ---
 --Waiter(s)-
 Resource Name  process session holds waits  
 process session holds waits
 TX-0007004c-26bf34  95 
 X 34  95   S
 session 95: DID 0001-0025-0002E096session 95: 
 DID 0001-0025-0002E096
 Rows waited on:
 Session 95: no row
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Walter K
   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).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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).

duplexing the archive redo log directory

2002-06-06 Thread Adams, Matthew (GEA, MABG, 088130)

Title: duplexing the archive redo log directory






Anybody encountered any bugs relating to 
the 


LOG_ARCHIVE_DUPLEX_DEST and
LOG_ARCHIVE_MIN_SUCCEED_DEST parameters


in 8.1.6 or 8.1.7?





Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly. 
It's just particular about who it makes friends with.

RE: Oracle 9iAS Rel.2 requires OID


Hmmm...but with all the problems we've been having with OiD and replication
(BUG 2369181 was opened for us), I don't forsee us being able to use it.

Interestingly enough, my co-worker has been able to get Oracle to use
iPlanet and OpenLDAP for names resolution.  It's a bit of a hack, since
Oracle doesn't follow standard LDIF rules (and as a sidenote, doesn't
enforce referential integrity because they allow an attribute to be deleted
even if it's in use!), but it seems to work great.

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


 -Original Message-
 From: Ben [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, June 06, 2002 10:59 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Oracle 9iAS Rel.2 requires OID
 
 
 Hi
 
 Just browsing through the documentation for 9iAS 9.0.2 and I am a bit
 surprised.
 It looks as though you have to install the so called Infrastructure
 (SingleSignOn,
 Oracle Internet Directory and a metadata repository) if you 
 want to use
 Portal
 or Discoverer. If you are just running web apps then you 
 don't need the
 Infrastructure.
 We are using 9iAS R1 for web apps and taking a look at Portal and
 Discoverer.
 But if they require the OID then I doubt we will use them. 
 Oracle appears to
 be forcing you to become an all-Oracle shop. Why am I surprised?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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

RE: Database link performance

2002-06-06 Thread Mercadante, Thomas F

Title: Message



just 
checking. I've had good luck with the view existing on the db_B 
machine.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Michael Rosenblum 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 2:24 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Database link performance 
  Tom,
  
  I 
  tried both ways:
  - 
  creating the view on db_B and query it from db_A
  - 
  creating view of db_A using link to db_B
  
  No 
  difference.
  
  Michael Rosenblum,
  Dulcian Inc.
  

-Original Message-From: Mercadante, 
Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 
06, 2002 1:07 PMTo: '[EMAIL PROTECTED]'Cc: 
'[EMAIL PROTECTED]'Subject: RE: Database link performance 

Mike,

you created the view on the db_B machine and 
called it from db_A, right?


"To Tom 
Mercadante: creating of a view, that does filtering, did not help. 
Absolutely the same results!"

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Michael Rosenblum 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 
  12:03 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Re: Database link performance 
  
  
  
  Hi, all!
  
  Thanks for your help and suggestions. Here 
  is some feedback on what I have found on the 
  performance problem of the querying data between two databases using DB 
links.
  
  To John Kanagaraj: filter 
  by ROWNUM does not make any difference  I have absolutely the same speed, 
  when querying table with 5000 rows and filtering where ROWNUM 
  5000.
  
  To Tom Mercadante: creating 
  of a view, that does filtering, did not help. Absolutely the same 
  results!
  
  Just remind you the 
  case:
  declare 
   v_record 
  B%rowtype;
  
   cursor cB
   is
   select *
   from 
  [EMAIL PROTECTED]
   where rownum  
  5000;
  begin
   for c in cB
   loop
   v_record 
  :=c;
   end loop;
  end;
  
  My next step has been to 
  analyze session-level statistics.
  
  When using link we have 
  physically two sessions  from a client to db_A (opened explicitly) and 
  from db_A to db_B (opened by Oracle automatically). After execution of a 
  script: - in the session to db_A we have 1 SQL*Net roundtrips 
  to/from db_link
  - in the session to db_B we 
  have 1 SQL*Net roundtrips to/from client.
  
  But when I am running the 
  same script directly (from client to db_B)  there is only ONE SQL*Net 
  roundtrip to/from the client.
  
  I asked Paul Dorsey to 
  check it out with Tom Kyteat yesterdays NYOUG 
  meeting.
  
  Tom told us that Oracle 8.* 
  does single record fetches when using database links. In general, database 
  links have been tuned to support database replication. It means, that 
  create table new_A as select  from table_A@db_B runs quickly but 
  querying from new_A is quite slow.
  
  The remaining question: has 
  anybody tried to use database links in Oracle9i? Does the same single 
  fetch limitation apply?
  
  Thank you,
  Michael 
  Rosenblum
  Dulcian 
  Inc.

RE: I/O bound on tablespace for one partition

2002-06-06 Thread Paula_Stankus

Title: Message




  


Okay 
guys,

We are using OS 
striping. However, for reasons of partition elimination, etc. I broke 
up 80 years of data into separate years. Now one of the tablespaces is 
being hit 70% of the time. Given OS striping and that I can't really 
stripe manually (very ltd.) is it worth moving partitions into multiple 
tablespaces?

RE: duplexing the archive redo log directory

2002-06-06 Thread Rodrigues, Bryan


Matt,
 
Do not alter the LOG_ARCHIVE_DUPLEX_DEST parameter online. Altering a
parameter that uses a / in its value will cause problems doing selects on
v$parameter table see document 136791.1. I ran into this issue and had to
bounce both instances to resolve the issue.
 
Bryan

-Original Message-
Sent: Thursday, June 06, 2002 3:16 PM
To: Multiple recipients of list ORACLE-L




Anybody encountered any bugs relating to 
the 

LOG_ARCHIVE_DUPLEX_DEST and 
LOG_ARCHIVE_MIN_SUCCEED_DEST parameters 

in 8.1.6 or 8.1.7? 



 
Matt Adams - GE Appliances - [EMAIL PROTECTED] 
Contrary to popular opinion, Unix is user friendly.  
It's just particular about who it makes friends with. 

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

DB authentication using Secure ID

2002-06-06 Thread Jamadagni, Rajendra


Has anyone got this working using secure id authentication for logging into
DB? If so, I'd like to hear from you.

TIA
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

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



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

Clone DB

2002-06-06 Thread Ramon E. Estevez




First of all,

Tks to all the reply about this topic 3 weeks 
ago. 

Trying to clone a DB I am getting an 


ORA-01503 CREATE CONTROLFILE failed
ORA-01159 file is not from same database as 
previous files
ORA-01110 data file 7: 
'C:\ORACLE\ORADATA\VTAS\DATOS'

TIA


Ramon E. Estevez[EMAIL PROTECTED]809-565-3121

Re: Mysterious Deadlock

2002-06-06 Thread Jack Silvey


Walter,

we had a situation where our pctfree was 0, our
initrans was 4, and we were trying to update the table
using 8 concurrent processes. Since we had no space to
grow, our ITL could not expand, and some of the
processes deadlocked with a similiar error.

What is your pctfree and initrans? how many procs are
trying to insert/delete/update it at once? are ALL
dying, or just some?


jack silvey

 --- Walter K [EMAIL PROTECTED] wrote:
  We have an application that is periodically 
  encountering what appears to be a self-deadlock.
 Only 
  one session is listed and it holds an exclusive
 (X) 
  lock and is waiting for a share (S) lock with NO
 ROWS 
  waited. I have pasted the deadlock graph at the
 bottom-
  -hopefully it will be legible.
  
  Does anyone know how such a lock could be
 produced? 
  I'd really like to be able to recreate such a
 scenario.
  
  Thanks!
  -w
  
  Current SQL statement for this session:
  update ACCOUNT set 
 

ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
 

MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
 

_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
 

CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
 

NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
 

IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 

,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO
  N_DATE=:ACCOUNT_ACTION_DATE where 
  ACCOUNT_ID=:key_ACCOUNT_ID
  The following deadlock is not an ORACLE error. It
 is a
  deadlock due to user error in the design of an 
  application
  or from issuing incorrect ad-hoc SQL. The
 following
  information may aid in determining the deadlock:
  Deadlock graph:
 -Blocker(s)
  ---
  --Waiter(s)-
  Resource Name  process session holds waits
  
  process session holds waits
  TX-0007004c-26bf34  95 
  X 34  95   S
  session 95: DID 0001-0025-0002E096  session 95: 
  DID 0001-0025-0002E096
  Rows waited on:
  Session 95: no row
  
  -- 
  Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
  -- 
  Author: Walter K
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).
 
 
 __
 Do You Yahoo!?
 Yahoo! - Official partner of 2002 FIFA World Cup
 http://fifaworldcup.yahoo.com
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Rachel Carmichael
   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).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  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: Mysterious Deadlock



Thanks for the link. The example shown at the bottom 
of the article looks like an unindexed foreign key 
issue rather than an initrans/pctfree issue. What's 
really strange about my deadlock is that it shows no 
other session waiting on the lock except itself.

Originally, I thought the deadlock we were 
encountering was a lack of ITL slots based on all the 
articles I had read at the time but when I attempt to 
reproduce the ITL shortage scenario I always get what 
looks like a traditional deadlock (X waiting for X) 
when I deliberately deprive a block of ITL slots.

I'm open to ideas, suggestions...

Thank you.
-w


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

http://www.dbdomain.com/a120197.htm

it's down near the end of the article, but they talk 
about a deadlock
with no rows and say it appears to be a problem with 
the initrans or
pctfree setting on that table


--- Walter K [EMAIL PROTECTED] wrote:
 We have an application that is periodically 
 encountering what appears to be a self-deadlock. 
Only 
 one session is listed and it holds an exclusive (X) 
 lock and is waiting for a share (S) lock with NO 
ROWS 
 waited. I have pasted the deadlock graph at the 
bottom-
 -hopefully it will be legible.
 
 Does anyone know how such a lock could be produced? 
 I'd really like to be able to recreate such a 
scenario.
 
 Thanks!
 -w
 
 Current SQL statement for this session:
 update ACCOUNT set 
 
ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
 
MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
 
_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
 
CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
 
NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
 
IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_AC
TIO
 N_DATE=:ACCOUNT_ACTION_DATE where 
 ACCOUNT_ID=:key_ACCOUNT_ID
 The following deadlock is not an ORACLE error. It 
is a
 deadlock due to user error in the design of an 
 application
 or from issuing incorrect ad-hoc SQL. The following
 information may aid in determining the deadlock:
 Deadlock graph:
-Blocker(s)
  ---
 --Waiter(s)-
 Resource Name  process session holds waits  
 process session holds waits
 TX-0007004c-26bf34  95 
 X 34  95   S
 session 95: DID 0001-0025-0002E096   session 95: 
 DID 0001-0025-0002E096
 Rows waited on:
 Session 95: no row
 
 -- 
 Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
 -- 
 Author: Walter K
   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).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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: Walter K
  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: Archiving in OPS

2002-06-06 Thread Muqthar Ahmed




Hi,

You 
can not setup your archive logs in EMC storage (RAW). Currently I am 
working on this project. Oracle recommends that the following 
setup:

 
SYSTEM / DATA / INDEX / TEMP / OTHER TBLS / REDOLOGS 
- RAW (EMC storage)
 
ARCHIVE LOGS / ROLLBACK 
(UNDO) 
- LOCAL (on each instance)

You 
can use FAILOVER to move archive location. For 
example:

 
Instance1 
Instance2
 
PROD1PROD2
 
/d01/arch1 
/d02/arch2

If 
Instance1 goes down, using FAILOVER method, /d01/arch1 will be automatically 
mounted on Instance2. Check with your UNIX Administrator about FAILOVER 
mechanism.

Muqthar Ahmed
DBA

  -Original Message-From: Ramon E. Estevez 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 
  12:59 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Archiving in OPS
  Hi list,
  
  Scenario OPS, 2 nodes, 8.1.7, AIX using raw 
  devices, EMC box.
  
  I am new in OPS and have to implement archiving 
  in an OPS environment and configure RMAN to use with LEGATO.
  
  My question is how to organize the 
  archives. What will happen if one of the nodes goes down ?
  
  I think that the2 nodes have to see each 
  other and write archive files to the same place, for instance I assume 
  that the archive files have to be in the EMC box .
  
  I have been gathering very helpful information 
  from the list in the past week about RMAN, but if you have some scripts, some 
  documentation, links about it will be very appreciated.
  
  TIA
  
  Ramon E. Estevez[EMAIL PROTECTED]809-565-3121

runaway oracle.exe thread on NT / W2K

2002-06-06 Thread Jeffrey Beckstrom




This has now happened on 3 separeate boxes. This has happened while 
putting on an Oracle Applications patch or in the last case,after starting 
the concurrent managers for 11i with a lot of requests scheduled to compile all 
of the flex fields. In every instance, the thread id does not match 
anything in oracle.

We notice that box is using 50-100% cpu even though nothing is 
running. Stop concurrent managers. Terminate web sessions. 
Exit all sqlplus sessions. 

Use pslist from sysinternals.com and it shows a running thread of 
oracle.exe using lots of user and kernal time. This thread id is not shown 
in v$session/process

Oracle has not been of much help to date.

Even after doing a shutdown immediate, cpu is still high and thread is 
running. Have to stop the service to get rid of it all.

We had been on 8.1.7.1.5 but upgraded to 8.1.7.3.2 since minimum for our 
Oracle Apps patches was 8.1.7.2.x just went to the latest and greatest since 
know eventually would be required.

Has anyone else seen anything like this.

Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
781-4204

RE: custom DD views to allow users to see source without needing

2002-06-06 Thread Jared . Still


I once rewrote the necessary system views to accomodate SQL Navigator.

Creating the users own copies did not work at that time, I had to modify 
the
actual SYS copies of the necessary ALL_* views.

It was not fun, and tends to break when Oracle is upgraded.

Jared





Miller, Jay [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/05/2002 01:03 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: custom DD views to allow users to see source without 
needing


We have the same problem with SQL Navigator.

Any suggestions would be great.

Jay Miller

-Original Message-
Sent: Tuesday, June 04, 2002 7:08 PM
To: Multiple recipients of list ORACLE-L
needing exe rights


Hi, Jack  list,

We had the same problem here and we finally resolved it in a similar way.
We created 3 views as sys : all_objects_xx, all_arguments_xx and
all_source_xx,
synonyms for both views and granted select permissions to user.

But, we still have a problem. User needs to debug packages (step by step)
and it seems that when you use dbms_debug the views are not enough.
The only way to achieve this is by granting create any procedure to user.
We want to avoid grant such permission. Do you have faced the same problem
?
If yes, how do you resolve it ?

Best regards,
Mario.





Por favor, responda a [EMAIL PROTECTED]

Enviado por:  [EMAIL PROTECTED]


Destinatarios: Multiple recipients of list ORACLE-L
   [EMAIL PROTECTED]
CC:

Asunto:   custom DD views to allow users to see source without
   needing exe rights
Clasificación:


Good afternoon co-listers,

Recently we had a problem with TOAD and I thought I
would share our solution.

TOAD looks at the views ALL_ARGUMENTS and ALL_OBJECTS
to see procedural code. Unless a user has the ability
to execute a package/procedure/function, they cannot
see the source code through these views, and can't see
the source in TOAD.

This limitiation is hard-coded in the view structure.
Upon reflection, it occured to me that I could
recreate these views in the users' schema, customized
to remove the necessity of having execute priv to see
the code, and since Oracle looks local first during
object name resolution, it would probably use these
views instead of the data dictionary views.

This worked. The two views that I customized are below
- feel free to use.

jack silvey



ALL_ARGUMENTS:

select
u.name owner, /* OWNER */
nvl(a.procedure$,o.name) object_name, /*
OBJECT_NAME */
decode(a.procedure$,null,null, o.name)
package_name, /*PACKAGE_NAME */
o.obj# object_id, /* OBJECT_ID */
decode(a.overload#,0,null,a.overload#) overload,
/*OVERLOAD */
a.argument argument_name, /* ARGUMENT_NAME */
a.position# position, /* POSITION */
a.sequence# sequence, /* SEQUENCE */
a.level# data_level, /* DATA_LEVEL */
decode(a.type#,  /* DATA_TYPE */
0, null,
1, decode(a.charsetform, 2, 'NVARCHAR2',
'VARCHAR2'),
2, decode(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.charsetform, 2, 'NCHAR VARYING',
'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'),
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED') data_type,
default$ default_value, /* DEFAULT_VALUE */
deflength default_length, /* DEFAULT_LENGTH */
decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefi
ned') in_out, /* IN_OUT */
length data_length, /* DATA_LENGTH */
precision# data_precision, /* DATA_PRECISION */
scale data_scale, /* DATA_SCALE */
radix radix, /* RADIX */
decode(a.charsetform, 1, 'CHAR_CS',   /*
CHARACTER_SET_NAME */
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid) char_cs,
a.type_owner type_owner, /* TYPE_OWNER */
a.type_name type_name, /* TYPE_NAME */
a.type_subname type_subname, /* TYPE_SUBNAME */
a.type_linkname type_link, /* TYPE_LINK */
a.pls_type pls_type /* PLS_TYPE */
from sys.obj$ o,sys.argument$ a,sys.user$ u
where o.obj# = a.obj#
and o.owner# = u.user#

ALL_OBJECTS:

select u.name owner,
o.name object_name,
o.subname subobject_name,
o.obj# object_id,
o.dataobj# data_object_id,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2,
'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21,
'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA

RE: Sqlloader

2002-06-06 Thread Scott . Shafer


Typing on a keyboard can accomplish this.

Scott Shafer
San Antonio, TX
210-581-6217


 -Original Message-
 From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
 Sent: Thursday, June 06, 2002 9:48 AM
 To:   Multiple recipients of list ORACLE-L
 Subject:  Sqlloader
 
 Hallo,
 
 Anyone whom can tellme how to rename the field names everytime a text file
 is inserted in a table. The new field names are specified in the sqlloader
 script.
 
 Thanks in advance
 
 
 Roland
 
 -- 
 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).

Re: Mysterious Deadlock

2002-06-06 Thread Henry Poras


I remember having this problem a long time ago and finding an explanation on
www.ixora.com.au I don't have time to search my notes at the moment but if I
can, I'll do it a bit later.

Henry

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 06, 2002 2:48 PM


 http://www.dbdomain.com/a120197.htm

 it's down near the end of the article, but they talk about a deadlock
 with no rows and say it appears to be a problem with the initrans or
 pctfree setting on that table


 --- Walter K [EMAIL PROTECTED] wrote:
  We have an application that is periodically
  encountering what appears to be a self-deadlock. Only
  one session is listed and it holds an exclusive (X)
  lock and is waiting for a share (S) lock with NO ROWS
  waited. I have pasted the deadlock graph at the bottom-
  -hopefully it will be legible.
 
  Does anyone know how such a lock could be produced?
  I'd really like to be able to recreate such a scenario.
 
  Thanks!
  -w
 
  Current SQL statement for this session:
  update ACCOUNT set
  ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
  MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
  _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
  CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
  NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
  IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
  ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO
  N_DATE=:ACCOUNT_ACTION_DATE where
  ACCOUNT_ID=:key_ACCOUNT_ID
  The following deadlock is not an ORACLE error. It is a
  deadlock due to user error in the design of an
  application
  or from issuing incorrect ad-hoc SQL. The following
  information may aid in determining the deadlock:
  Deadlock graph:
 -Blocker(s)  ---
  --Waiter(s)-
  Resource Name  process session holds waits
  process session holds waits
  TX-0007004c-26bf34  95
  X 34  95   S
  session 95: DID 0001-0025-0002E096 session 95:
  DID 0001-0025-0002E096
  Rows waited on:
  Session 95: no row
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Walter K
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).


 __
 Do You Yahoo!?
 Yahoo! - Official partner of 2002 FIFA World Cup
 http://fifaworldcup.yahoo.com
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Rachel Carmichael
   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: Henry Poras
  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 9iAS Rel.2 requires OID

2002-06-06 Thread Jared . Still


Rich,

Has your coworker perhaps done a writeup on this?

Jared





Jesse, Rich [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/06/2002 12:09 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: Oracle 9iAS Rel.2 requires OID


Hmmm...but with all the problems we've been having with OiD and 
replication
(BUG 2369181 was opened for us), I don't forsee us being able to use it.

Interestingly enough, my co-worker has been able to get Oracle to use
iPlanet and OpenLDAP for names resolution.  It's a bit of a hack, since
Oracle doesn't follow standard LDIF rules (and as a sidenote, doesn't
enforce referential integrity because they allow an attribute to be 
deleted
even if it's in use!), but it seems to work great.

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI 
USA


 -Original Message-
 From: Ben [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, June 06, 2002 10:59 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Oracle 9iAS Rel.2 requires OID
 
 
 Hi
 
 Just browsing through the documentation for 9iAS 9.0.2 and I am a bit
 surprised.
 It looks as though you have to install the so called Infrastructure
 (SingleSignOn,
 Oracle Internet Directory and a metadata repository) if you 
 want to use
 Portal
 or Discoverer. If you are just running web apps then you 
 don't need the
 Infrastructure.
 We are using 9iAS R1 for web apps and taking a look at Portal and
 Discoverer.
 But if they require the OID then I doubt we will use them. 
 Oracle appears to
 be forcing you to become an all-Oracle shop. Why am I surprised?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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



-- 
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).

Security vulnerability in Oracle Net (Oracle9i Database Server)

2002-06-06 Thread Gautam_Reddy









Oracle Security Alert #34

Dated: 5 June 2002

Security
vulnerability in Oracle Net (Oracle9i Database
Server)

Description

A
potential security vulnerability has been discovered in Oracle Net for Oracle9i Database that

may
result in a potential of denial of service attack against Oracle Net Listener.
A knowledgeable and

malicious
user can send a small amount of data to the configured listening endpoint (for
Oracle Net

Listener)
that will cause the Oracle Net Listener to consume the available CPU of the
host machine.

Products affected

Oracle9i Database Release 9.0.x (all releases)

Platforms affected

MS
Windows and VM only. (Note: Unix, VMS, OS/390 are not affected)

Workarounds

None

Patch Information

Oracle
has fixed the potential vulnerability identified above under patch number
2367681 for supported

releases
of Oracle9i, Release 9.0.x on Windows and VM.

Download
currently available patches for your platform from Oracle s Worldwide
Support web site,

Metalink,
http://metalink.oracle.com. Activate the Patches button to get to the patches
Web page. Enter

2367681
as indicated above and activate the Submit button.

Please
check with Metalink or Oracle Worldwide Support Services for patch availability
if the patch for

your
platform is not available.

Oracle
strongly recommends that you comprehensively test the stability of your system
upon application

of any
patch prior to deleting any of the original file(s) that are replaced by the
patch.

RE: why so much slower

2002-06-06 Thread Paula_Stankus

Title: RE: why so much slower



Problem is that fast refresh of materialized view I 
don't believe is compatible with in-line view. Okay, I am wanting 
everything and might have to use complete batch refresh to recreate materialized 
view. 

  -Original Message-

Re: why so much slower

2002-06-06 Thread Igor Neyman

Title: RE: why so much slower



You didn't mention 'materialized view' in 
your original message.

Igor Neyman, OCP DBA[EMAIL PROTECTED] 


  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Thursday, June 06, 2002 4:13 
  PM
  Subject: RE: why so much slower
  
  Problem is that fast refresh of materialized view I 
  don't believe is compatible with in-line view. Okay, I am wanting 
  everything and might have to use complete batch refresh to recreate 
  materialized view. 
  
-Original 
Message-

RE: duplexing the archive redo log directory

2002-06-06 Thread Adams, Matthew (GEA, MABG, 088130)

Title: RE: duplexing the archive redo log directory





I had already done it online, but had to bounce
the database 30 minutes later for another
unrelated reason, so we should be OK.


Any other 'gotchas'?



Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly. 
It's just particular about who it makes friends with.


-Original Message-
From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 06, 2002 3:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: duplexing the archive redo log directory



Matt,

Do not alter the LOG_ARCHIVE_DUPLEX_DEST parameter online. Altering a
parameter that uses a / in its value will cause problems doing selects on
v$parameter table see document 136791.1. I ran into this issue and had to
bounce both instances to resolve the issue.

Bryan


-Original Message-
Sent: Thursday, June 06, 2002 3:16 PM
To: Multiple recipients of list ORACLE-L





Anybody encountered any bugs relating to 
the 


LOG_ARCHIVE_DUPLEX_DEST and 
LOG_ARCHIVE_MIN_SUCCEED_DEST parameters 


in 8.1.6 or 8.1.7? 




 
Matt Adams - GE Appliances - [EMAIL PROTECTED] 
Contrary to popular opinion, Unix is user friendly. 
It's just particular about who it makes friends with. 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rodrigues, Bryan
 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 9iAS Rel.2 requires OID


I've asked him about it, and he'll be putting it on the web soon.  From
what I've seen, it's definitely not for the faint of heart.  I'll post to
ORACLE-L (with his permission) when the doc's completed.

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, June 06, 2002 1:53 PM
 To: [EMAIL PROTECTED]
 Cc: Jesse, Rich
 Subject: RE: Oracle 9iAS Rel.2 requires OID
 
 
 Rich,
 
 Has your coworker perhaps done a writeup on this?
 
 Jared
 
 
 
 
 
 Jesse, Rich [EMAIL PROTECTED]
 Sent by: [EMAIL PROTECTED]
 06/06/2002 12:09 PM
 Please respond to ORACLE-L
 
  
 To: Multiple recipients of list ORACLE-L 
 [EMAIL PROTECTED]
 cc: 
 Subject:RE: Oracle 9iAS Rel.2 requires OID
 
 
 Hmmm...but with all the problems we've been having with OiD and 
 replication
 (BUG 2369181 was opened for us), I don't forsee us being able 
 to use it.
 
 Interestingly enough, my co-worker has been able to get Oracle to use
 iPlanet and OpenLDAP for names resolution.  It's a bit of a 
 hack, since
 Oracle doesn't follow standard LDIF rules (and as a sidenote, doesn't
 enforce referential integrity because they allow an attribute to be 
 deleted
 even if it's in use!), but it seems to work great.
 
 Rich Jesse   System/Database Administrator
 [EMAIL PROTECTED]  Quad/Tech International, 
 Sussex, WI 
 USA
 
 
  -Original Message-
  From: Ben [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, June 06, 2002 10:59 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Oracle 9iAS Rel.2 requires OID
  
  
  Hi
  
  Just browsing through the documentation for 9iAS 9.0.2 and 
 I am a bit
  surprised.
  It looks as though you have to install the so called Infrastructure
  (SingleSignOn,
  Oracle Internet Directory and a metadata repository) if you 
  want to use
  Portal
  or Discoverer. If you are just running web apps then you 
  don't need the
  Infrastructure.
  We are using 9iAS R1 for web apps and taking a look at Portal and
  Discoverer.
  But if they require the OID then I doubt we will use them. 
  Oracle appears to
  be forcing you to become an all-Oracle shop. Why am I surprised?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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

Re: Mysterious Deadlock


Jack,

Thanks for the reply. The table is defined as:
   INI_TRANS = 1
   PCT_FREE = 10
I meant to mention in my original posting that the 
platform is Solaris 8, Oracle 8.1.7.0/32-bit.

I don't know what the exact number of concurrent 
transactions is, probably no more than a few (1-3). 
The deadlock does not occur all the time. What's even 
stranger, is that the error can show up in the alert 
log at different times yet with the SAME pid for the 
trace file name--for example, today might be 
prod_ora_12345.trc showing up at different times in 
the alert log and next week Monday it might show 
prod_ora_67890.trc multiple times.

Did your deadlocks show up as no rows and only one 
line (S waiting for X) in the deadlock graph?

Thanks again.
-w

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

Walter,

we had a situation where our pctfree was 0, our
initrans was 4, and we were trying to update the table
using 8 concurrent processes. Since we had no space to
grow, our ITL could not expand, and some of the
processes deadlocked with a similiar error.

What is your pctfree and initrans? how many procs are
trying to insert/delete/update it at once? are ALL
dying, or just some?


jack silvey

 --- Walter K [EMAIL PROTECTED] wrote:
  We have an application that is periodically 
  encountering what appears to be a self-deadlock.
 Only 
  one session is listed and it holds an exclusive
 (X) 
  lock and is waiting for a share (S) lock with NO
 ROWS 
  waited. I have pasted the deadlock graph at the
 bottom-
  -hopefully it will be legible.
  
  Does anyone know how such a lock could be
 produced? 
  I'd really like to be able to recreate such a
 scenario.
  
  Thanks!
  -w
  
  Current SQL statement for this session:
  update ACCOUNT set 
 

ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST
_
 

MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NOD
E
 

_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:
A
 

CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:
I
 

NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRE
D
 

IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUN
T
 

,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTI
O
  N_DATE=:ACCOUNT_ACTION_DATE where 
  ACCOUNT_ID=:key_ACCOUNT_ID
  The following deadlock is not an ORACLE error. It
 is a
  deadlock due to user error in the design of an 
  application
  or from issuing incorrect ad-hoc SQL. The
 following
  information may aid in determining the deadlock:
  Deadlock graph:
 -Blocker(s)
  ---
  --Waiter(s)-
  Resource Name  process session holds waits
  
  process session holds waits
  TX-0007004c-26bf34  95 
  X 34  95   S
  session 95: DID 0001-0025-0002E096 session 95: 
  DID 0001-0025-0002E096
  Rows waited on:
  Session 95: no row
  
  -- 
  Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
  -- 
  Author: Walter K
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).
 
 
 __
 Do You Yahoo!?
 Yahoo! - Official partner of 2002 FIFA World Cup
 http://fifaworldcup.yahoo.com
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Rachel Carmichael
   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).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
-- 
Author: Jack Silvey
  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

RE: How to move 200 GB db from prod to dev?

2002-06-06 Thread Richard Huntley

Title: RE: How to move 200 GB db from prod to dev?





Helmut,


This works perfectly for cloning a DB where the instance names are different,
but the directory structure is exactly the same, which looks to be the case from
your original post for the /u02 file system. We do this in order to clone on the same
server, but should work just fine on a different box as well. Add the following to the
init.ora file for the cloned DB. All we do is copy the files, then mount the DB followed
by opening it...works like a charm!


lock_name_space = ROLAND
db_name = PCLDB1
instance_name = ROLAND
service_names = ROLAND


#convert file names and handle lock file
db_file_name_convert = '/PCLDB1/','/ROLAND/'
log_file_name_convert = '/PCLDB1/','/ROLAND/'


-Original Message-
From: Sherman, Edward [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 06, 2002 1:53 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to move 200 GB db from prod to dev?



I have a similar situation and here's how I deal with it.

(I'm assuming your datafiles have the same name but the pathname is
different)

Production: SID = PROD = e.g.
/zbackup/array3/oracle8/dbs73/PROD/wds01.data.dbf
Development: SID = WDSU = e.g. /extdisk/oracle8/dbs73/PROD/wds01.data.dbf

In SQL*PLUS: 

SQL ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Locate the trace file and copy it to the development machine.

Move data with a script similar to this:


rcp -rp /zbackup/oracle1/dbs73/PROD/* george:/extdisk/oracle1/dbs73/PROD
rcp -rp /zbackup/oracle2/dbs73/PROD/* george:/extdisk/oracle2/dbs73/PROD
 .
 .
 .
rcp -rp /zbackup/array3/oracle8/dbs73/PROD/*
george:/extdisk/oracle8/dbs83/PROD

Yes it's annoying but you only have to write the script once!

Now you can edit that trace file while you wait for your data to finish
moving:

In my trace file I change the line:
CREATE CONTROLFILE REUSE DATABASE PROD NORESETLOGS ARCHIVELOG

to

CREATE CONTROLFILE SET DATABASE WDSU RESETLOGS NOARCHIVELOG

This changes the instance name (I'm not using log archiveing on the dev
database).

Now I have to edit all those pathnames.
I have stuff like

LOGFILE
 GROUP 1 (
 '/oracle4/dbs73/PROD/redoPROD07a.log',
 '/oracle1/dbs73/PROD/redoPROD07b.log'

Need to be changed to:

 '/extdisk/oracle4/dbs73/PROD/redoPROD07a.log',
 '/extdisk/oracle1/dbs73/PROD/redoPROD07b.log'

Also, things like

 '/array3/oracle8/dbs73/PROD/wds01data.1.dbf',

Needs to be changed to

 '/extdisk/oracle8/dbs73/PROD/wds01data.1.dbf',


But its not really a problem if you can use the vi editor:

Hit ESC key
then :1,$s/\/oracle/\/extdisk\/oracle/g ENTER

This changes all the /oracle to /extdisk/oracle

This also has the side effect that all my /array3/oracle8 has changed to
/array3/extdisk/oracle8.

Need to get rid of the /array3

In vi,

Hit ESC Key
then :1,$s/\/array3//g

Now all my paths are correct for the development database.

At the bottom of the controlfile tracefile I comment out the lines:

-- RECOVER DATABASE
-- ALTER SYSTEM ARCHIVE LOG ALL

Change 

ALTER DATABASE OPEN;

to

ALTER DATABASE OPEN RESETLOGS;

Delete lines in the trace file from the first line until you get to the line
that says:

STARTUP NOMOUNT

Also delete or comment out any lines with a # character as the first
character.

Delete my old control files and run the trace file from SQL*PLUS to create
the new controlfiles and open the instance.

The renaming of the paths was done in two steps with vi but you can write a
shell script using sed to do the substitution for you if you like.

Maybe annoying but you only have to write the script once!


In your example you need to change PCLDB1 to ROLAND

You bring up the trace file with the CREATE CONTROLFILE script in vi and
just do:

Hit ESC
:1,$s/PCLDB1/ROLAND/g ENTER

and PCLDB1 will be substituted for ROLAND everywhere in the file.

If you want to write a script for this and are not familiar with sed then
maybe the UNIX sysadmin or a UNIX developer can help you with this.

After you get the scripts written then its really easy to move you data and
get the instance running. It's only annoying the first time!

Good luck,
Ed



-Original Message-
Sent: Thursday, June 06, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L




Hi! 


We are supposed to clone our production database onto a new development box
(both boxes are Sun Solaris). The db is about 200 GB in size.


What would be the best way to achieve this? Simply copying over the files
won't work, since the instance names are different:


Production: SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf 
Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf 


So would export/import the entire db be the only way? (But writing out dump
file that big should be a little disk space problem...)


Renaming all the datafiles (approx. 100) would be kind of annoying... 


Any ideas? 


This is 8.1.7 on Sun Solaris. 


Thanks, 
Helmut 





* * * * * Freedom of Information Act Notice * * * * * 
The information in this email is

Re: Mysterious Deadlock

2002-06-06 Thread Anjo Kolk


What's the initrans for the indexes involved ?

Anjo.


Walter K wrote:

 We have an application that is periodically
 encountering what appears to be a self-deadlock. Only
 one session is listed and it holds an exclusive (X)
 lock and is waiting for a share (S) lock with NO ROWS
 waited. I have pasted the deadlock graph at the bottom-
 -hopefully it will be legible.

 Does anyone know how such a lock could be produced?
 I'd really like to be able to recreate such a scenario.

 Thanks!
 -w

 Current SQL statement for this session:
 update ACCOUNT set
 ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
 MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
 _ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
 CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
 NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
 IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO
 N_DATE=:ACCOUNT_ACTION_DATE where
 ACCOUNT_ID=:key_ACCOUNT_ID
 The following deadlock is not an ORACLE error. It is a
 deadlock due to user error in the design of an
 application
 or from issuing incorrect ad-hoc SQL. The following
 information may aid in determining the deadlock:
 Deadlock graph:
-Blocker(s)  ---
 --Waiter(s)-
 Resource Name  process session holds waits
 process session holds waits
 TX-0007004c-26bf34  95
 X 34  95   S
 session 95: DID 0001-0025-0002E096  session 95:
 DID 0001-0025-0002E096
 Rows waited on:
 Session 95: no row

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

Oracle Workload Generator


Hey all,

While at a Users Group meet yesterday, HPaq demo'd 9iRAC.  While the demo
was highly impressive and all, I was intrigued by one of the tools used to
demonstrate the transparency of the RAC to a client.  The tool's window was
labeled Oracle Workload Generator and was supposedly a Windows 95/98 tool.

Has anybody heard of this?  And where can I snag a copy?

TIA!
Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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

Re: Archiving in OPS

2002-06-06 Thread Tim Gorman




If I understand you correctly, you are referring to 
"RAW" EMC storage as the shared storage array accessible from all nodes in the 
cluster, while "local" is local storage that is only accessible from the node to 
which it is directly attached?

If so, then putting the tablespaces on that "local" 
storage will never work. Likewise, if the rollback tablespaces were also 
not in "raw" devices; it would never work (unless you are using a 
clustered file-system such as available on Tru64). In both cases, OPS/RAC 
would fail to startup...

  - Original Message - 
  From: 
  Muqthar Ahmed 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Thursday, June 06, 2002 1:48 
  PM
  Subject: RE: Archiving in OPS
  
  Hi,
  
  You 
  can not setup your archive logs in EMC storage (RAW). Currently I am 
  working on this project. Oracle recommends that the following 
  setup:
  
   
  SYSTEM / DATA / INDEX / TEMP / OTHER TBLS / REDOLOGS 
  - RAW (EMC storage)
   
  ARCHIVE LOGS / ROLLBACK 
  (UNDO) 
  - LOCAL (on each instance)
  
  You 
  can use FAILOVER to move archive location. For 
  example:
  
   
  Instance1 
  Instance2
   
  PROD1PROD2
   
  /d01/arch1 
  /d02/arch2
  
  If 
  Instance1 goes down, using FAILOVER method, /d01/arch1 will be automatically 
  mounted on Instance2. Check with your UNIX Administrator about FAILOVER 
  mechanism.
  
  Muqthar Ahmed
  DBA
  
-Original Message-From: Ramon E. Estevez 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 
2002 12:59 PMTo: Multiple recipients of list 
ORACLE-LSubject: Archiving in OPS
Hi list,

Scenario OPS, 2 nodes, 8.1.7, AIX using raw 
devices, EMC box.

I am new in OPS and have to implement 
archiving in an OPS environment and configure RMAN to use with 
LEGATO.

My question is how to organize the 
archives. What will happen if one of the nodes goes down 
?

I think that the2 nodes have to see 
each other and write archive files to the same place, for instance I 
assume that the archive files have to be in the EMC box .

I have been gathering very helpful 
information from the list in the past week about RMAN, but if you have some 
scripts, some documentation, links about it will be very 
appreciated.

TIA

Ramon E. Estevez[EMAIL PROTECTED]809-565-3121

Re: Sqlloader

2002-06-06 Thread Stephane Faroult


[EMAIL PROTECTED] wrote:
 
 Typing on a keyboard can accomplish this.
 
 Scott Shafer
 San Antonio, TX
 210-581-6217
 

Can you give an example of how to type on a keyboard ? The Oracle
manuals are unclear on this.

TIA.

Stephane Faroult
Oriole Software
-- 
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).

RE: why so much slower

2002-06-06 Thread Toepke, Kevin M





I'll betyou are running...Oracle...8.1.6. 
Right?

If so, 
the solution is

SELECT /*+ NO_MERGE(x) HASH(st) */
  *
FROM (your 2 table join) x
 ,small_table st
WHERE x.fk = st.pk;

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 
  2002 4:09 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: why so much slower
  
Set sort_area_size to very large as 20Gb (obscene) 
amount of space available.
Doing 2 large table outer joins returns results in 
.341 seconds - both partitioned on same criteria
added one small codetable equijoin with one of the 
larger tables. There is a foreign key to codetable and index that is 
unique.
Used hash join hint
Used nested loop hint

Basically saw two large joins sort merged hash join 
then nested join to smaller table - much much smaller 
codetable.

NO 
matter what it seems query is much much slower - Any 
ideas?

Re: Mysterious Deadlock


I checked out ixora a few weeks ago when this problem 
surfaced and saw the article you are probably 
referring to but that was a library cache lock and 
doesn't look to be the same as what I'm experiencing.

I sure wish I knew how to read the trace files like 
Steve Adams can as well as I wish I knew how to use 
the various events and levels like he does... :(

-w

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

I remember having this problem a long time ago and 
finding an explanation on
www.ixora.com.au I don't have time to search my notes 
at the moment but if I
can, I'll do it a bit later.

Henry

- Original Message -
To: Multiple recipients of list ORACLE-L ORACLE-
[EMAIL PROTECTED]
Sent: Thursday, June 06, 2002 2:48 PM


 http://www.dbdomain.com/a120197.htm

 it's down near the end of the article, but they 
talk about a deadlock
 with no rows and say it appears to be a problem 
with the initrans or
 pctfree setting on that table


 --- Walter K [EMAIL PROTECTED] wrote:
  We have an application that is periodically
  encountering what appears to be a self-deadlock. 
Only
  one session is listed and it holds an exclusive 
(X)
  lock and is waiting for a share (S) lock with NO 
ROWS
  waited. I have pasted the deadlock graph at the 
bottom-
  -hopefully it will be legible.
 
  Does anyone know how such a lock could be 
produced?
  I'd really like to be able to recreate such a 
scenario.
 
  Thanks!
  -w
 
  Current SQL statement for this session:
  update ACCOUNT set
  
ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
  
MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
  
_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
  
CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
  
NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
  
IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 
 ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACT
IO
  N_DATE=:ACCOUNT_ACTION_DATE where
  ACCOUNT_ID=:key_ACCOUNT_ID
  The following deadlock is not an ORACLE error. It 
is a
  deadlock due to user error in the design of an
  application
  or from issuing incorrect ad-hoc SQL. The 
following
  information may aid in determining the deadlock:
  Deadlock graph:
 -Blocker(s)
  ---
  --Waiter(s)-
  Resource Name  process session holds waits
  process session holds waits
  TX-0007004c-26bf34  95
  X 34  95   S
  session 95: DID 0001-0025-0002E096 session 95:
  DID 0001-0025-0002E096
  Rows waited on:
  Session 95: no row
 
  --
  Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
  --
  Author: Walter K
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).


 __
 Do You Yahoo!?
 Yahoo! - Official partner of 2002 FIFA World Cup
 http://fifaworldcup.yahoo.com
 --
 Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
 --
 Author: Rachel Carmichael
   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: Henry Poras
  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: Walter K
  INET: [EMAIL PROTECTED]

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

Re: Mysterious Deadlock

2002-06-06 Thread Jack Silvey


Seems that the no rows message was the identifying
criteria of our problem, however, I have both slept
and drank since then. Not at the same time, of course.
Well, maybe a little overlap.

How about your indexes - initrans? pctfree? any bitmap
indexes involved?


--- Walter K [EMAIL PROTECTED] wrote:
 Jack,
 
 Thanks for the reply. The table is defined as:
INI_TRANS = 1
PCT_FREE = 10
 I meant to mention in my original posting that the 
 platform is Solaris 8, Oracle 8.1.7.0/32-bit.
 
 I don't know what the exact number of concurrent 
 transactions is, probably no more than a few (1-3). 
 The deadlock does not occur all the time. What's
 even 
 stranger, is that the error can show up in the alert
 
 log at different times yet with the SAME pid for the
 
 trace file name--for example, today might be 
 prod_ora_12345.trc showing up at different times in 
 the alert log and next week Monday it might show 
 prod_ora_67890.trc multiple times.
 
 Did your deadlocks show up as no rows and only one
 
 line (S waiting for X) in the deadlock graph?
 
 Thanks again.
 -w
 
 --- Original Message ---
 To: Multiple recipients of list ORACLE-L ORACLE-
 [EMAIL PROTECTED]
 
 Walter,
 
 we had a situation where our pctfree was 0, our
 initrans was 4, and we were trying to update the
 table
 using 8 concurrent processes. Since we had no space
 to
 grow, our ITL could not expand, and some of the
 processes deadlocked with a similiar error.
 
 What is your pctfree and initrans? how many procs
 are
 trying to insert/delete/update it at once? are ALL
 dying, or just some?
 
 
 jack silvey
 
  --- Walter K [EMAIL PROTECTED] wrote:
   We have an application that is periodically 
   encountering what appears to be a
 self-deadlock.
  Only 
   one session is listed and it holds an exclusive
  (X) 
   lock and is waiting for a share (S) lock with
 NO
  ROWS 
   waited. I have pasted the deadlock graph at the
  bottom-
   -hopefully it will be legible.
   
   Does anyone know how such a lock could be
  produced? 
   I'd really like to be able to recreate such a
  scenario.
   
   Thanks!
   -w
   
   Current SQL statement for this session:
   update ACCOUNT set 
  
 

ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST
 _
  
 

MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NOD
 E
  
 

_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:
 A
  
 

CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:
 I
  
 

NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRE
 D
  
 

IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUN
 T
  
 

,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTI
 O
   N_DATE=:ACCOUNT_ACTION_DATE where 
   ACCOUNT_ID=:key_ACCOUNT_ID
   The following deadlock is not an ORACLE error.
 It
  is a
   deadlock due to user error in the design of an 
   application
   or from issuing incorrect ad-hoc SQL. The
  following
   information may aid in determining the
 deadlock:
   Deadlock graph:
 
 -Blocker(s)
   ---
   --Waiter(s)-
   Resource Name  process session holds
 waits
   
   process session holds waits
   TX-0007004c-26bf34  95 
   X 34  95   S
   session 95: DID 0001-0025-0002E096   session 95: 
   DID 0001-0025-0002E096
   Rows waited on:
   Session 95: no row
   
   -- 
   Please see the official ORACLE-L FAQ:
  http://www.orafaq.com
   -- 
   Author: Walter K
 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).
  
  
 
 __
  Do You Yahoo!?
  Yahoo! - Official partner of 2002 FIFA World Cup
  http://fifaworldcup.yahoo.com
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.com
  -- 
  Author: Rachel Carmichael
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).
 
 
 __
 Do You

Case tool

2002-06-06 Thread Igor Neyman




Anyone used CaseStudio from:

http://www.casestudio..com/enu/default.html 
?

Your opinion, please?

Igor Neyman, OCP DBA[EMAIL PROTECTED]

How do YOU use Java in the DB?


So, we've finally taken the jump and are using JSPs (erm...Java Stored
Procedures, not Java Server Pages) on 8.1.7.2.0.  

Informal Poll:  How do you use Java Stored Procedures?

1) Easy interface from PL/SQL to lp and other OS commands.
2)
3)
4)
...

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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

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

RE: Permissions on user trace files

2002-06-06 Thread Miller, Jay


Thanks to everyone who helped with this.

It's working perfectly.

Jay


-Original Message-
Sent: Thursday, June 06, 2002 5:38 AM
To: Multiple recipients of list ORACLE-L


_trace_files_public   = true 

in init.ora

Iain Nicoll

-Original Message-
Sent: Wednesday, June 05, 2002 8:55 PM
To: Multiple recipients of list ORACLE-L


Hi all,

User Trace files are currently created as 
-rw-r-

Is there an easy way to change the permissions when they are created to
-rw-r--r--

The developers would like to be able to run Sql Trace on queries on the
development box and then run tkprof on the resulting file.  I'm perfectly
happy giving them permission to do so, since it means I won't need to run it
for them several times a day.

I'm on Solaris 2.6, Oracle 8.1.7.2


TIA,
Jay Miller
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Miller, Jay
  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: Nicoll, Iain (Calanais)
  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: Miller, Jay
  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: Mysterious Deadlock


All indexes on the table involved have INI_TRANS=2 and 
PCT_FREE=10.

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

What's the initrans for the indexes involved ?

Anjo.


Walter K wrote:

 We have an application that is periodically
 encountering what appears to be a self-deadlock. 
Only
 one session is listed and it holds an exclusive (X)
 lock and is waiting for a share (S) lock with NO 
ROWS
 waited. I have pasted the deadlock graph at the 
bottom-
 -hopefully it will be legible.

 Does anyone know how such a lock could be produced?
 I'd really like to be able to recreate such a 
scenario.

 Thanks!
 -w

 Current SQL statement for this session:
 update ACCOUNT set
 
ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST_
 
MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NODE
 
_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=:A
 
CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=:I
 
NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRED
 
IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
 ,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_AC
TIO
 N_DATE=:ACCOUNT_ACTION_DATE where
 ACCOUNT_ID=:key_ACCOUNT_ID
 The following deadlock is not an ORACLE error. It 
is a
 deadlock due to user error in the design of an
 application
 or from issuing incorrect ad-hoc SQL. The following
 information may aid in determining the deadlock:
 Deadlock graph:
-Blocker(s)
  ---
 --Waiter(s)-
 Resource Name  process session holds waits
 process session holds waits
 TX-0007004c-26bf34  95
 X 34  95   S
 session 95: DID 0001-0025-0002E096  session 95:
 DID 0001-0025-0002E096
 Rows waited on:
 Session 95: no row

 --
 Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
 --
 Author: Walter K
   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: Anjo Kolk
  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: Walter K
  INET: [EMAIL PROTECTED]

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

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

RE: Exp/Imp Errors: Tablespace Specific?


Hi,
 
I have One db instance containing three schemas to represent development, stage, 
and demo environments.  I occassionally need to *roll* the schemas as a release occurs.
 
We have been using import/export to accomplish this.  In the past, another dba has 
done this task (so this is why I ask the question now).
 
 
Schema1 (dev)
Schema2 (stage)
Schema3 (demo)
 
In order to roll out I did the following (after backing up all three of course:):
 
1) Create export file of schema1
2) Create export file of schema2
 
3) Drop all schema objects in Schema2
4) Drop all schema objects in Schema3
 
5) Import Schema1 dmp file into Schema2
6) Import Schema2 dmp file into Schema3
 
 
Everything went fine except for step 5.  I received errors on ALL of the PK 
constraints :
 
. . importing table  ACCOUNT  17972 rows imported
IMP-00015: following statement failed because the object already exists:
 ALTER TABLE ACCOUNT ADD  CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCOUNT_ID
 ,GENERATION) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INIT
 IAL 8388608) TABLESPACE AURDEV_TS ENABLE 
 
Also, had it added one trigger twice (may NOT be a related issue) - I deleted the 
extra trigger and recompiled it and it was fine (but it was weird to see two with same 
name).
 
Also, received errors on ALL of the FKs (as the referencing PKs did not exist).
 
IMP-3: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
 ALTER TABLE COMMODITY ADD CONSTRAINT FK_COMMODITY_SECURITY FOREIGN KEY 
 (SECURITY_ID,GENERATION) REFERENCES SECURITY (SECURITY_ID,GENERATI
 ON) ENABLE NOVALIDATE
 
Then, err'd out on enabling all constraints (Of course.)
 
My main question is why does it think that the object already exists?  I imported 
using FromUser Touser.  The only thing I can see is that the error also is containing 
the tablespace that is assigned to Schema1.  Schema2 and Schema3 use the same 
tablespace which differs from the tablespace assigned to Schema1. ie:
 
 
SchemaDefault Tablespace
***
Schema1tablespace1
Schema2tablespace2
Schema3tablespace2
 
I am wondering if the error is inaccurate and what the error should read is that the 
current user is not assigned as a resource to the named tablespace.   So, I am 
wondering how to strip the tablespace specification out of the import.  
 
Actually I am not understanding why it is there at all as I dont believe that the 
storage info should be there?
 
Any clues?
 
Thanks,
 
Hannah
 
 
 
 
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

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

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

Exp/Imp Errors: Tablespace Specific?