Ang: RE: Access to Oracle
Can you please guide me how to do all these steps? Roland MacGregor, Ian A. [EMAIL PROTECTED]@fatcity.com den 2002-01-24 08:15 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Whose ODBC DRIVER are you using? If it is Microsoft's ,go to the workaround options for the DSN; once there, turn of muti-threaded server support. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 24, 2002 4:55 AM To: Multiple recipients of list ORACLE-L Anyone who can help me with this: I have a Microsoft routin, whic calls oracle procedure in database servername, which have a databaselink to other databases(located on computers with Linux system).That procedure is supposed to pick up data from tables which exist in the other databases. It works fine when I run the oracle procedure from oracle, but when I make the call from oracle I get the following error message: Why doesnt this work. If I make an ordinary call to rhe database servername and just picks data from the same database then it is no problem at all. SQLCODE = -2041 SQLERRM = ORA-02041 client database did not begin a transaction the query that is executed is: SELECT TABLE_NAME FROM [EMAIL PROTECTED] WHERE TABLE_NAME='ICA_ARTIKEL' AND OWNER='A111640' and that doesnt seem so strange to me. I can run this query separately from oracle too but when i run it from Access application(by the call) then I get the errormessage according above. Really appreciate if anyone could help me with this. 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: MacGregor, Ian A. 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).
Oracle Books
Greetings All !!! Could anyone tell me which book is best for the oracle developer which is compatible with Oracle 8i. Regards, Gagandeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gagandeep Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: More Rman q's
Hi,
Am I misreading all this, but should the auxiliary database be in existence
before I can Clone to it?
I created the init.ora and all directories and started TEST3 in nomount. I
created the password file and can connect to it with a connect string no
problem.
Another point is. Did you mean that the auxilliary database is the database
that needs to be cloned (source)?
Jack
Jay Hostetter [EMAIL PROTECTED] on 23-01-2002 15:41:35
To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
John and Jack,
I guess I didn't read Jack's info closely enough, since he states that
his auxiliary database is already started nomount. I wonder if the three
separate connect statements are the issue? When I duplicate a database, I
use the following commands from the OS:
rman target internal/xxx@original catalog rman/xxx@rman1 auxiliary /
rman@prod_to_test
rmanexit
My ORACLE_HOME and SID are set for the auxiliary database. I have started
the auxiliary database NOMOUNT, which basically just starts the background
processes.Password files are required for the remote login (this is on
a different server). I don't use db_file_name_convert or
log_file_name_convert, because I group the data files differently on this
server (I don't have the exact same number of file systems as production).
After I have RMAN started, I do the following:
run {
#set until time to_date('0108200220','mmddhh24miss');
set until scn 316498395;
#set until logseq 1389 thread 1;
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCST/system01.dbf';
set newname for datafile 2 to '/u03/oradata/BSCST/rbs01.dbf';
...
...yadda yadda yadda...
...
set newname for datafile 49 to '/u04/oradata/BSCST/bill_image_data06.dbf';
set newname for datafile 50 to '/u04/oradata/BSCST/process_data03.dbf';
duplicate target database to BSCST
logfile
group 1 ('/u03/oradata/BSCST/redo1a.log','/u04/oradata/BSCST/redo1b.log')
size 2m,
group 2 ('/u03/oradata/BSCST/redo2a.log','/u04/oradata/BSCST/redo2b.log')
size 2m,
group 3 ('/u03/oradata/BSCST/redo3a.log','/u04/oradata/BSCST/redo3b.log')
size 2m;
}
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 01/23/02 03:20AM
Jay,Jack
I don't understand here how RMAN connects to auxilliary when Jack seems to
be saying that the database does not exist it all.
When I tested a duplicate database session I made the following notes which
indicated I need a remote_login_password file setting up. I assume you
didn't do this then Jack as the database was not created at this point in
time
Please correct me if I have misunderstood
John
My notes follow
+++
To allow this connection a remote_login_password file needs to be in place.
This can be created running the orapwd command
$ORACLE_HOME/bin/orapwd file$ORACLE_HOME/dbs/ orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (using the initSID_excl.ora file in
the
pfile directory).
Ensure that this works by performing a sqlplus internal @tnsnames_alias
with
the correct password and ensuring that a connection has been made.
This needs to be set up on the target environment as well as on the
auxillary server. Ensure that a connection can be made both locally and
remotely
sqlplus internal/password@tnsnames_alias
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
Young, niet toegestaan. Ernst Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst Young. Ernst Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for
AW: Oracle Books
Hi, I would suggest you should take a look at Osborne Oracle Press !!! They are very good !! www.osborne.com i.e. ISBN.: 0-07-212048-7 Oracle Developer Advanced Forms Reports very powerful knowledge !! Frank Von: Gagandeep Singh [mailto:[EMAIL PROTECTED]] Greetings All !!! Could anyone tell me which book is best for the oracle developer which is compatible with Oracle 8i. Regards, Gagandeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Foelz.Frank 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).
Hmmmmm
Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Moving Files Around
Jay, I'm, not sure I understand what you mean by the instance reference to the parameter file do you mean the init.ora file that's used when the database starts up? You can override that by startup state pfile=full file specification of the parameter file --- Jay Wade [EMAIL PROTECTED] wrote: Hello: I have read and tried (sorry newbie) the procedures involved with moving control files, database files, etc. But I have never seen any reference to how to move the instance reference to the parameter file. Whenever had to move a db I've always done at least some exp/imp. I was wondering if anyone could share with me how to move the instance refernce to the parameter file. I'd very much be interested in how to do this on a Win2k box running 8.1.6. Regards, Jay __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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).
RE: Databases on Solaris: Online Forum Jan. 22-28
Does anyone know if the book is any good? I'm thinking about getting it, Thanks, Jim -Original Message- [EMAIL PROTECTED] Sent: 24 January 2002 23:25 To: Multiple recipients of list ORACLE-L That could have something to do with the questions not being pointed enough. Could be they're pointless? I was going to provide a couple of examples, but it seems there is too much traffic to this site for me to get back on right now. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/24/02 01:45 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Databases on Solaris: Online Forum Jan. 22-28 Hmmm...many (most? all?) of the answers given in the forum however, seem to be pointers to chapters in Mr. Packer's book. Just an observation. :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, January 24, 2002 1:49 PM To: Multiple recipients of list ORACLE-L This list pays for itself once again. Jared is charging the rest of you too, right? Steve -- 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: James McCann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Slight OT : Size of MS SQLServer Databases
Hi, We are evaluating databases to our web site and the candidates are Oracle and MS. We already have Oracle running on our production environments , but , mainly a cost-based decision , management don´t want to buy Oracle to the web site. So , we want to know how big are the MS databases out there. Thanks in advance, Antonio Belloni -- 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).
Sending mail using pl/sql
Hi All, I know it has been asked many times but I have changed jobs and no longer have old e-mail. Anyway I need to know how to send e-mail using pl/sql with attachments. Any real examples,links,references would be very helpful. My environment is Oracle NT 8.1.6 SE, NT 4.0, client is Win 2000 Thanks Rick -- 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: Session_wait
Thank you very much, Henry, I really appreciate the help. I'll post the
results later.
Best,
Sergey
-Original Message-
Sent: Thursday, January 24, 2002 4:25 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait
Let me try again (using expand -t 3 as suggested by Jared)
# Script for analyzing Oracle Trace files with WAIT statistics
# Usage: wait_scan.awk filename
# Written:Henry Poras
# 5/16/00
# Modified: 12/3/01 Initially assumes all wait states for a cursor are
between
# parse statements.
#
#
nawk '# need nawk,
not awk
BEGIN{N=
PARSE_FLAG=0 # PARSE_FLAG
= 0 (normal state)
printf(\n\n%-35s %-12s %-18s\n\n, # PARSE_FLAG
= 1 (previous line PARSING)
WAIT EVENT, # OF TIMES, ELAPSED TIME (sec)) # print
column headers
}
{if (PARSE_FLAG==1) # if previous
line started
{SQL[N]=$0 # with
PARSING, print
PARSE_FLAG=0 # the SQL.
N=
}
}
/^PARSING/ {FS=
N=$4
sub(#,,N)
if (N in SQL)
prinfo(N)
PARSE_FLAG=1
}
/^WAIT/ {FS=#| nam=|ela=|p1=
N=$2
sub(:,,N)
PARSE_FLAG=2
n_wait[N,$3] += 1
ela_wait[N,$3] += $4
}
END {for (N in SQL){ # Print Wait
statistics for final
printf \n\n\n%s\n\n, SQL[N] # SQL
statement in file
for (k in n_wait) {
split(k,arg,SUBSEP)
if (arg[1]==N n_wait[k]!=0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
printf \n\n
}
for (k in n_wait) {
split(k,arg,SUBSEP)
if (n_wait[k] != 0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
function prinfo(N, k) {
printf \n\n\n%s\n\n, SQL[N]
for (k in n_wait){
split(k,arg,SUBSEP)
if (arg[1]==N n_wait[k]!=0) {
printf %-35s %-12s %12.2f\n,
arg[2],n_wait[k],ela_wait[k]/100
n_wait[k]=0
ela_wait[k]=0
}
}
}
' $1
-Original Message-
Sent: Thursday, January 24, 2002 11:00 AM
To: Multiple recipients of list ORACLE-L
# Script for analyzing Oracle Trace files with WAIT statistics
# Usage: wait_scan.awk filename
# Written:Henry Poras
...
-Original Message-
Sent: Wednesday, January 23, 2002 1:56 PM
To: Multiple recipients of list ORACLE-L
Hi, Henry,
Please, post it. I am terribly sorry for asking dumb questions, but hey!
I've come a long way to be a part of this country, learned the language,
become a DBA and proud of it! Time to learn! Thanks a bunch in advance!
Sincerely,
Sergey
-Original Message-
Sent: Wednesday, January 23, 2002 1:02 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait
Couldn't hurt. Also why not do some deltas of your session statistics
(before and after snapshots)
Henry
PS: After you have the trace file, I put together an awk statement to sum up
the number and elapsed time of the wait states for each SQL statement. I can
post that if you would like.
-Original Message-
Sent: Wednesday, January 23, 2002 11:21 AM
To: Multiple recipients of list ORACLE-L
Hi, Henry,
Since I didn't expect that to happen, I had just SQL trace turned on for
that particular session. Do you suggest entering
event=10046 trace name errorstack level 12 into init.ora?
Thanks,
Regards,
Sergey
-Original Message-
Sent: Wednesday, January 23, 2002 9:50 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait
Sergey,
At what level did you trace?
Henry
-Original Message-
Sent: Wednesday, January 23, 2002 8:15 AM
To: Multiple recipients of list ORACLE-L
Thank you, guys, for your help, just got back to work. Unfortunately, it's
too late to run the query, but later on we'll run the OLTP again, and then
I'll do it and post the output. Currently I'm analyzing the trace file which
is about 130M (did not tkprof it yet), and that size does NOT sound healthy
to me.
Best regards,
Sergey Babich
-Original Message-
Sent: Tuesday, January 22, 2002 5:19 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait
Re: ROLLBACK SEGMENT?
Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the "gymnastics" of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed toaccomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that "COULD"possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot tooold error. Export does not generate any rollback, but there could be usersperforming DML operations on the table that is being exported, and theexport needs to be redirected to read from the rollback segments. Thelikelihood of the error being thrown up especially if one uses theconsistent parameter could be very high, if you dont have a large enoughrollback segment without an optimal clause.RajJeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40AMPlease respond to [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED]cc:On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... "COULD" help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that "COULD"possibly make any difference for 'snapshot too old'?--Jeremiah Wiltonhttp://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote:So what does it accomplish to "assign export [to] a particularrollback segment?"Export doesn't generate any rollback, right, so what is it supposed toaccomplish by doing this incantation?On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export aparticular rollback segment -1) Create a rollback segment tablespace with one largesegment and bring it online before export.2) Offline all existing rollback segments.3) Export the database4) Offline the large tablespace created earlier.5) Bring back the orginals rollback segments online.--- Seema Singh [EMAIL PROTECTED] wrote: Can I use one rollback segment at time of export?Isyes,thenSET TRANSACTION USE ROLLBACK SEGMENTrollbacksegmentname; --Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Jeremiah Wilton INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: time issue
Ooops.. Jared, You are right. I didn't notice the order of the dates in 'between' condition. That's why I prefer to use: where Time_Stamp ... and Time_Stamp ... in this case don't have to bother about the order. Still, 'group by' attracted my attention, because even with correct order of the dates in 'between' condition, the result will be wrong, like in: SQLWKS create table b1(c1 int, c2 varchar2(10)); Statement processed. SQLWKS insert into b1 values(1, 'aa'); 1 row processed. SQLWKS insert into b1 values(2, 'ab'); 1 row processed. SQLWKS insert into b1 values(3, 'ac'); 1 row processed. SQLWKS insert into b1 values(4, 'ad'); 1 row processed. SQLWKS insert into b1 values(5, 'ae'); 1 row processed. SQLWKS insert into b1 values(6, 'af'); 1 row processed. SQLWKS commit; Statement processed. SQLWKS select count(c2) from b1 2 where c1 between 2 and 5 3 group by c1; COUNT(C2) -- 1 1 1 1 4 rows selected. While: SQLWKS select count(c2) from b1 2 where c1 between 2 and 5; COUNT(C2) -- 4 1 row selected. gives correct answer. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 6:50 PM Igor, That SQL will always return zero rows. Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/24/02 02:45 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: time issue Just Select count(*) users from cp_license_use where Time_Stamp between SYSDATE and (sysdate - 30/1440); should do it (no 'group by'). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 4:35 PM I am using the following Statement: I am getting no records returned. Any idea what is wrong? thanks again Lance Select count(User_name) users from cp_license_use where Time_Stamp between SYSDATE and (sysdate - 30/1440) group by Time_stamp To grab data from the following table: SYSDATE = 1/24/2002 9:33:16 PM User_Name Time_stamp dsilver 1/24/2002 9:31:33 PM cnelson 1/24/2002 9:31:33 PM eho 1/24/2002 9:31:33 PM mreza 1/24/2002 9:31:33 PM kjuneja 1/24/2002 9:31:33 PM sislam 1/24/2002 9:31:33 PM dkotha 1/24/2002 9:31:33 PM mbalthrop 1/24/2002 9:31:33 PM tchung 1/24/2002 9:31:33 PM cnifong 1/24/2002 9:31:33 PM sluc 1/24/2002 9:31:33 PM dtrevino 1/24/2002 9:31:33 PM ddobson 1/24/2002 9:31:33 PM echinwub 1/24/2002 9:31:33 PM dmoses 1/24/2002 9:31:33 PM gpratt 1/24/2002 9:31:33 PM syahmed 1/24/2002 9:31:33 PM mreza 1/24/2002 9:31:33 PM -- -- 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: Igor Neyman 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: Sending mail using pl/sql
U can e-mail me @ [EMAIL PROTECTED] to remind me: I have real life examples I can send u: Today is holiday in middle east where I work: I will be at work tomorrow and I will send them Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 25, 2002 4:40 PM Hi All, I know it has been asked many times but I have changed jobs and no longer have old e-mail. Anyway I need to know how to send e-mail using pl/sql with attachments. Any real examples,links,references would be very helpful. My environment is Oracle NT 8.1.6 SE, NT 4.0, client is Win 2000 Thanks Rick -- 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: Sam Roberts INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hmmmmm
is there only one row in that table? if so, you can lock the table itself. try it without the nowait... --- Stefan Jakobsson [EMAIL PROTECTED] wrote: Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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).
RE: More Rman q's
Title: RE: More Rman q's
Jack,
TEST3 instance should be exactly as you stated, an Oracle instance with no datafiles as yet.
What I think I was meaning regarding the names of the objects is that the format is
Rman catalogue = catalogue
Source database = target
new database = auxilliary.
To my mind that is wrong and the new database should be target. It is of no importance but I do think it can be confusing.
What is even odder is requirement to connect to the target database in the first place. A live database has been copied to disk/tape. We want to make a new copy on a development server. But we still have to connect to the original live database. I once had a reason from Oracle for this in a tar dialogue however it did not sound very convincing to me at the time.
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 25 January 2002 10:10
To: Multiple recipients of list ORACLE-L
Subject: RE: More Rman q's
Hi,
Am I misreading all this, but should the auxiliary database be in existence
before I can Clone to it?
I created the init.ora and all directories and started TEST3 in nomount. I
created the password file and can connect to it with a connect string no
problem.
Another point is. Did you mean that the auxilliary database is the database
that needs to be cloned (source)?
Jack
Jay Hostetter [EMAIL PROTECTED] on 23-01-2002 15:41:35
To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
John and Jack,
I guess I didn't read Jack's info closely enough, since he states that
his auxiliary database is already started nomount. I wonder if the three
separate connect statements are the issue? When I duplicate a database, I
use the following commands from the OS:
rman target internal/xxx@original catalog rman/xxx@rman1 auxiliary /
rman@prod_to_test
rmanexit
My ORACLE_HOME and SID are set for the auxiliary database. I have started
the auxiliary database NOMOUNT, which basically just starts the background
processes. Password files are required for the remote login (this is on
a different server). I don't use db_file_name_convert or
log_file_name_convert, because I group the data files differently on this
server (I don't have the exact same number of file systems as production).
After I have RMAN started, I do the following:
run {
#set until time to_date('0108200220','mmddhh24miss');
set until scn 316498395;
#set until logseq 1389 thread 1;
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCST/system01.dbf';
set newname for datafile 2 to '/u03/oradata/BSCST/rbs01.dbf';
...
...yadda yadda yadda...
...
set newname for datafile 49 to '/u04/oradata/BSCST/bill_image_data06.dbf';
set newname for datafile 50 to '/u04/oradata/BSCST/process_data03.dbf';
duplicate target database to BSCST
logfile
group 1 ('/u03/oradata/BSCST/redo1a.log','/u04/oradata/BSCST/redo1b.log')
size 2m,
group 2 ('/u03/oradata/BSCST/redo2a.log','/u04/oradata/BSCST/redo2b.log')
size 2m,
group 3 ('/u03/oradata/BSCST/redo3a.log','/u04/oradata/BSCST/redo3b.log')
size 2m;
}
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 01/23/02 03:20AM
Jay,Jack
I don't understand here how RMAN connects to auxilliary when Jack seems to
be saying that the database does not exist it all.
When I tested a duplicate database session I made the following notes which
indicated I need a remote_login_password file setting up. I assume you
didn't do this then Jack as the database was not created at this point in
time
Please correct me if I have misunderstood
John
My notes follow
+++
To allow this connection a remote_login_password file needs to be in place.
This can be created running the orapwd command
$ORACLE_HOME/bin/orapwd file$ORACLE_HOME/dbs/ orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (using the initSID_excl.ora file in
the
pfile directory).
Ensure that this works by performing a sqlplus internal @tnsnames_alias
with
the correct password and ensuring that a connection has been made.
This needs to be set up on the target environment as well as on the
auxillary server. Ensure that a connection can be made both locally and
remotely
sqlplus internal/password@tnsnames_alias
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
Young, niet toegestaan. Ernst Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch
Re: Oracle 9i installation Java RunTime Environment was not found.
I have JRE Installed here is what I get $: java -versionjava version "1.3.0_02"Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0_02)Java HotSpot(TM) Client VM (build 1.3.0_02, mixed mode) There fore Java is installed. I think this might have something to do with Oracle not able to create /tmp/Orainstall/jre/bin/jre on its own even thought there are enough permission and disk space. Any help is appreciated could u check the value of "JRE_LOCATION" in Disk1/install/oraparam.ini from the distribution? Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. "
Off-topic Perl Question
I know that this is not the forum to ask perl questions. But is there some
one who can help me on how to read two lines at a time using
a Perl script
My script looks like this :
open(FILE_IN, $datafile) or die Cannot open $datafile...\n;
while(FILE_IN)
{ chomp;
@fields = split(/;/);
print (Processing line $. for emission...\n);
PROMPT Processing $. for emission ...
($cField1, $cField2, $cField3) = @fields[0..2];
... and so on
In this case, as you see, it takes only a line at a time and I would to
consider couple of lines at a time.
Is it possible to do it and how ?
Please help.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: ALEMU Abiy
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 DBAs Needed in Baton Rouge, Louisana
We have a client requirement for several Oracle DBAs in Baton Rouge, Louisana. *This company will provide relocation assistance, as long as there is a viable reason besides money for the candidate's desire to move there. These are full time staff positions so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. Requirements: - 3+ years Oracle DBA experience - DB2 or IMS experience is a plus - U.S. citizens or permanent residents only This position offers: * Opportunity to become a key member of the I.T. team * Base Salary -65-70K maybe more depending on experience NO sub contracting positions available. *U.S. citizenship or green card holders only PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: Email: [EMAIL PROTECTED] Ph: 1-800-549-8502 Please use job code: One/Baton Rouge/Oracle DBA/Keith All Submissions are handled in confidence. We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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).
Operating system choice
The topic of Oracle on NT vs. Oracle on UNIX has been addressed many times before, but in my searches I have not found hard statistics to support either choice. I have been running Oracle on Tru64 UNIX for six years, and it has been rock solid. I have no experience with NT other than my desktop running OEM. As our budget time approaches along with hardware replacement looming, I have been approached by a manager who feels that we should move in the direction of replacing our UNIX servers with Intel servers. His justification for this move is cost. I've seen many references to poor stability of Oracle on NT, but all opinion, not facts. This manager feels it is just Microsoft Haters who are saying this because they hate NT and want UNIX. Personally, I think adding an Oracle NT Server into the mix would give me experience in that area, always a good thing, but I also worry about the stability and availability of my database that I would be presenting to my customers. If anyone has any insights, statistics, facts or links to documentation presenting such, I'd sure appreciate the help. --- Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] -- 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: More Rman q's /SOLVED dunno how
Hi ,
I must have made a really big mess of the catalog or something.
I reinstalled the catalog, recreated TEST2 and ran the clone script again.
Guess what.?
Thx JJ
Jack
[EMAIL PROTECTED]@fatcity.com on 25-01-2002 11:10:19
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
Hi,
Am I misreading all this, but should the auxiliary database be in existence
before I can Clone to it?
I created the init.ora and all directories and started TEST3 in nomount. I
created the password file and can connect to it with a connect string no
problem.
Another point is. Did you mean that the auxilliary database is the database
that needs to be cloned (source)?
Jack
Jay Hostetter [EMAIL PROTECTED] on 23-01-2002 15:41:35
To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
John and Jack,
I guess I didn't read Jack's info closely enough, since he states that
his auxiliary database is already started nomount. I wonder if the three
separate connect statements are the issue? When I duplicate a database, I
use the following commands from the OS:
rman target internal/xxx@original catalog rman/xxx@rman1 auxiliary /
rman@prod_to_test
rmanexit
My ORACLE_HOME and SID are set for the auxiliary database. I have started
the auxiliary database NOMOUNT, which basically just starts the background
processes.Password files are required for the remote login (this is on
a different server). I don't use db_file_name_convert or
log_file_name_convert, because I group the data files differently on this
server (I don't have the exact same number of file systems as production).
After I have RMAN started, I do the following:
run {
#set until time to_date('0108200220','mmddhh24miss');
set until scn 316498395;
#set until logseq 1389 thread 1;
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCST/system01.dbf';
set newname for datafile 2 to '/u03/oradata/BSCST/rbs01.dbf';
...
...yadda yadda yadda...
...
set newname for datafile 49 to '/u04/oradata/BSCST/bill_image_data06.dbf';
set newname for datafile 50 to '/u04/oradata/BSCST/process_data03.dbf';
duplicate target database to BSCST
logfile
group 1 ('/u03/oradata/BSCST/redo1a.log','/u04/oradata/BSCST/redo1b.log')
size 2m,
group 2 ('/u03/oradata/BSCST/redo2a.log','/u04/oradata/BSCST/redo2b.log')
size 2m,
group 3 ('/u03/oradata/BSCST/redo3a.log','/u04/oradata/BSCST/redo3b.log')
size 2m;
}
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 01/23/02 03:20AM
Jay,Jack
I don't understand here how RMAN connects to auxilliary when Jack seems to
be saying that the database does not exist it all.
When I tested a duplicate database session I made the following notes which
indicated I need a remote_login_password file setting up. I assume you
didn't do this then Jack as the database was not created at this point in
time
Please correct me if I have misunderstood
John
My notes follow
+++
To allow this connection a remote_login_password file needs to be in place.
This can be created running the orapwd command
$ORACLE_HOME/bin/orapwd file$ORACLE_HOME/dbs/ orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (using the initSID_excl.ora file in
the
pfile directory).
Ensure that this works by performing a sqlplus internal @tnsnames_alias
with
the correct password and ensuring that a connection has been made.
This needs to be set up on the target environment as well as on the
auxillary server. Ensure that a connection can be made both locally and
remotely
sqlplus internal/password@tnsnames_alias
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
Young, niet toegestaan. Ernst Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
RE: Mirroring REDO logs to an nfs drive
I know that at one time, Oracle recommended that you do not put files onto an NFS file system. I think the OS needs to guarantee that the data was actually written to disk, and when you use NFS the OS thinks the data was written, when in reality it may still be transferring across the network. Somebody else may be able to shed some more current information on this. Also, even though your drives are mirrored on your production box, it is still wise to mirror your redo logs and control files to separate file systems. I have seen file systems become corrupt due to OS or firmware bugs, not to mention what an errant rm command can do. The only thing that hardware mirroring does in those cases is mirror the corruption or mistake. -Original Message- Sent: Thursday, January 24, 2002 12:50 PM To: Multiple recipients of list ORACLE-L easiest way to see the values is to do a backup controlfile to trace... they will be there --- Steve McClure [EMAIL PROTECTED] wrote: Our site is preparing to fail over to our backup server. We need to do maintenance on our production server, and will be running on the backup for about 24 hours. One issue I brought up was that our backup server is not equiped with mirrored drives, thus there was the possibility that a drive failure could destroy an online redo log. On our production box the logs are not software mirrored, because of the physical mirroring in our drive cabinet. As a result I was told to multiplex the redo logs once we had failed over to the backup server. Furthermore I would add the new members to an nfs drive, so that even a pesky controller couldn't foil our mirrored log files. I have some questions about this. First, am I just looking for problems by doing this? I would appreciate any tips or warnings on this subject. Secondly, researching this topic made me curious as to my DB's settings for MAXLOGFILES and MAXLOGMEMBERS. Where can I find these parameters? I was sure I would find them in v$parameter, but they were not there. Thanks for any response, Steve McClure Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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).
How to use a package variable in pkg1 inside of package pkg2
Hi All, I have a package pkg1 that has a variable var 1 declared Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier pkg1.var1 must be declared. What do I need to do to correct. Pkg1 compiles fine. Thanks Rick -- 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).
v$session question
In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio 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: More Rman q's
Jack,
The auxiliary database is the clone of the target database (Oracle really should
have called this parameter SOURCE instead of TARGET). It (the auxiliary) is the one
you are creating.
The auxiliary database should be started nomount. The only files needed for the
auxiliary are the init.ora and password file.
Jay
[EMAIL PROTECTED] 01/25/02 05:10AM
Hi,
Am I misreading all this, but should the auxiliary database be in existence
before I can Clone to it?
I created the init.ora and all directories and started TEST3 in nomount. I
created the password file and can connect to it with a connect string no
problem.
Another point is. Did you mean that the auxilliary database is the database
that needs to be cloned (source)?
Jack
Jay Hostetter [EMAIL PROTECTED] on 23-01-2002 15:41:35
To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
John and Jack,
I guess I didn't read Jack's info closely enough, since he states that
his auxiliary database is already started nomount. I wonder if the three
separate connect statements are the issue? When I duplicate a database, I
use the following commands from the OS:
rman target internal/xxx@original catalog rman/xxx@rman1 auxiliary /
rman@prod_to_test
rmanexit
My ORACLE_HOME and SID are set for the auxiliary database. I have started
the auxiliary database NOMOUNT, which basically just starts the background
processes.Password files are required for the remote login (this is on
a different server). I don't use db_file_name_convert or
log_file_name_convert, because I group the data files differently on this
server (I don't have the exact same number of file systems as production).
After I have RMAN started, I do the following:
run {
#set until time to_date('0108200220','mmddhh24miss');
set until scn 316498395;
#set until logseq 1389 thread 1;
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCST/system01.dbf';
set newname for datafile 2 to '/u03/oradata/BSCST/rbs01.dbf';
...
...yadda yadda yadda...
...
set newname for datafile 49 to '/u04/oradata/BSCST/bill_image_data06.dbf';
set newname for datafile 50 to '/u04/oradata/BSCST/process_data03.dbf';
duplicate target database to BSCST
logfile
group 1 ('/u03/oradata/BSCST/redo1a.log','/u04/oradata/BSCST/redo1b.log')
size 2m,
group 2 ('/u03/oradata/BSCST/redo2a.log','/u04/oradata/BSCST/redo2b.log')
size 2m,
group 3 ('/u03/oradata/BSCST/redo3a.log','/u04/oradata/BSCST/redo3b.log')
size 2m;
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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).
SV: Hmmmmm
There are more rows in that table. It's one that we use for storing sequential numbers we use... like invoice numbers, membership numbers etcetc So we have to lock row when using it, so noone else uses the same number when we add a new member or create a new invoice... But good advice tho, and I would have used lock on it if not for the fact that we have to use row locking on it... /Stefan -Ursprungligt meddelande- Från: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Skickat: den 25 januari 2002 15:00 Till: Multiple recipients of list ORACLE-L Ämne: Re: Hm is there only one row in that table? if so, you can lock the table itself. try it without the nowait... --- Stefan Jakobsson [EMAIL PROTECTED] wrote: Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Yet again more Recovery Manager questions
Hi All, I have a mixed environment 8.0.5 8.1.7 databases. I can not use the same catalog for both versions. I tried creating a new catalog for my 8.0.5 databases (different owner) in the same 8.1.7. database that my other catalog is in using the rman 805 executable. This also results in error messages (about packages/procedures). My Q: Do I really need a database matching the version of RMAN I'm using and/or is there a way to use the 8.1.7 rman executable for 8.0.5 databases? (documentation tends to go that direction) TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- 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: Operating system choice
We have both. NT is MUCH easier to manage and set up. But unless you put some money in the hardware it is not as stable. Our applications have lots of interfaces to other servers. Lots of scheduled batch jobs. This seems to cause a lot of processes to get started that never stop. They tie up resources and eventually hang up the system. I know there should be a way to prevent this but I haven't found it yet. We finally started bouncing the server once a week and now it seems to be happy almost all the time. I can see why management wants to use NT. It is a lot cheaper to purchase and support. All our apps on NT are small with only a few users. I don't know if I would be ready to put a large application on NT. Ron Smith -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 8:41 AM To: Multiple recipients of list ORACLE-L The topic of Oracle on NT vs. Oracle on UNIX has been addressed many times before, but in my searches I have not found hard statistics to support either choice. I have been running Oracle on Tru64 UNIX for six years, and it has been rock solid. I have no experience with NT other than my desktop running OEM. As our budget time approaches along with hardware replacement looming, I have been approached by a manager who feels that we should move in the direction of replacing our UNIX servers with Intel servers. His justification for this move is cost. I've seen many references to poor stability of Oracle on NT, but all opinion, not facts. This manager feels it is just Microsoft Haters who are saying this because they hate NT and want UNIX. Personally, I think adding an Oracle NT Server into the mix would give me experience in that area, always a good thing, but I also worry about the stability and availability of my database that I would be presenting to my customers. If anyone has any insights, statistics, facts or links to documentation presenting such, I'd sure appreciate the help. --- Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] -- 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: Smith, Ron L. 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: Off-topic Perl Question
Three methods: read newline delimeted two at a time, read the whole thing
and process it in pairs of lines or read twice the size of a fixed-length
line from columnar data:
while()
{
my $line = $_ . ;
...
}
or
my @linz = ;
for( my $i = 0 ; $i @linz ; $i += 2 )
{
my @twolinz = ( $linz[$i], $linz[$i+1] );
...
}
can be written more succinctly as:
my @linz = ;
while( @linz )
{
my @twolinz = splice @linz, 0, 2;
...
}
or, if they are fixed length:
my $size = 2 * $sizeofaline;
$/ = \$size;
while()
{
my $twolinz = $_;
...
}
-- ALEMU Abiy [EMAIL PROTECTED]
I know that this is not the forum to ask perl questions. But is there
some one who can help me on how to read two lines at a time using
a Perl script
My script looks like this :
open(FILE_IN, $datafile) or die Cannot open $datafile...\n;
while(FILE_IN)
{ chomp;
@fields = split(/;/);
print (Processing line $. for emission...\n);
PROMPT Processing $. for emission ...
($cField1, $cField2, $cField3) = @fields[0..2];
... and so on
In this case, as you see, it takes only a line at a time and I would to
consider couple of lines at a time.
Is it possible to do it and how ?
Please help.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: ALEMU Abiy
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).
--
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).
Oracle on NFS
Jay Hostetter wrote: I know that at one time, Oracle recommended that you do not put files onto an NFS file system. I think the OS needs to guarantee that the data was actually written to disk, and when you use NFS the OS thinks the data was written, when in reality it may still be transferring across the network. Somebody else may be able to shed some more current information on this. Try http://www.oracle.com/ip/deploy/database/storage/content.html or search www.oracle.com for 'Oracle Storage Compatibility'. The last time I looked, there were 5 Oracle-approved vendors that offer NFS-mounted storage products. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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 use a package variable in pkg1 inside of package pkg2
You have to declare this variable in package1 specification create or replace package pkg1 is v_var VARCHAR2(10) := 'Blahblah'; end; / create or replace package pkg2 is procedure showvar; end; / create or replace package pkg2 body is procedure showvar begin dbms_output.put_line(pkg1.v_var); end; end; / exec pkg2.showvar in SQLPlus prompt should do that Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Rick_Cale@team health.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: How to use a package variable in pkg1 inside of package pkg2 om 2002.01.25 16:35 Please respond to ORACLE-L Hi All, I have a package pkg1 that has a variable var 1 declared Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier pkg1.var1 must be declared. What do I need to do to correct. Pkg1 compiles fine. Thanks Rick -- 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).
ORA-04031 with Oracle 8.1.7.0.0 on HP-UX 11.0. URGENT
Getting: ORA-04031: unable to allocate 4256 bytes of shared memory (shared pool, unknown object, sga heap, library cache) and ORA-00604: error occurred at recursive SQL level 1 over and over in the alert log. This is a suddenly event on a production database that had been operational at 8.1.6 for a year, and is now at 8.1.7 for a month. Running SilverStream as a front-end, which has about 70 dedicated connections in it's pool (SilverStream does MTS on its own). SilverStream users are unable to login. I'm unable to login sqlplus sys/manager, getting end-of-communication channel. I'm unable to login sqlplus internal, getting already logged in. I'm able to login svrmgrl, but most commands end the session with a not connected. startup gives the expected already started, shutdown first. My questions are (yeah, I know, clairvoiance): - Could anyone who experienced this share solution(s)? - Will a shutdown immediate just hang? - Will a shutdown abort cause harm? (Nothing but once-a-day full export.) - What would you do? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kirsch, Walter J (Northrop Grumman) 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: v$session question
Hi, Try module in v$session (just a guess) Jack Joe LaCascio [EMAIL PROTECTED]@fatcity.com on 25-01-2002 15:35:24 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hmmmmm
Interesting. Are you sure? I have actually started reading Tom Kyte's book, Expert One-on-One, and he mentions this problem and the solution(specifically, page 32). Basically, you code.. SELECT * FROM table WHERE field = something FOR UPDATE NOWAIT; UPDATE table set field = blah WHERE field = something; this allows user A to lock and update a specific record, and prevents others from updating that record. Please note, I specified ...FOR UPDATE NOWAIT because if user B tries to update the record at the same time, then a lock error is returned which can then be coded for. If you use ...FOR UPDATE then user B simply sits there and waits to update, which is dangerous since the record was changed for a reason, and will be updated again. Bad. I know of one project that I support that is using this standard method successfully. HTH!! Chris May Oracle be with you...always --- Stefan Jakobsson [EMAIL PROTECTED] wrote: Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: Operating system choice
[EMAIL PROTECTED] wrote: thing, but I also worry about the stability and availability of my database that I would be presenting to my customers. If anyone has any insights, statistics, facts or links to documentation presenting such, I'd sure appreciate the help. i can only relate my personal experience. i had troubles with 7.3.4 on NT 4.0 when the datafiles got beyond about 1GB in size. we got lots of read errors, disk IO errors and crupted blocks. but that was on one instance and one server. that's the only experience i've had with NT. all my other experience has been on unix and VMS, and i've never had any problems other than hardware failures and user error.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. An old friend will help you move. A good friend will help you move a dead body. - Jim Haye -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: SV: Hmmmmm
you need a where clause on that select statement, so that you get the ONE row in that table that you want. and remove the nowait. Nowait tells Oracle to continue processing the next statement even if you didn't get the lock you wanted... Of course removing it has it's own drawbacks, you could be hung waiting for someone else to release the lock. --- Stefan Jakobsson [EMAIL PROTECTED] wrote: There are more rows in that table. It's one that we use for storing sequential numbers we use... like invoice numbers, membership numbers etcetc So we have to lock row when using it, so noone else uses the same number when we add a new member or create a new invoice... But good advice tho, and I would have used lock on it if not for the fact that we have to use row locking on it... /Stefan -Ursprungligt meddelande- Från: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Skickat: den 25 januari 2002 15:00 Till: Multiple recipients of list ORACLE-L Ämne: Re: Hm is there only one row in that table? if so, you can lock the table itself. try it without the nowait... --- Stefan Jakobsson [EMAIL PROTECTED] wrote: Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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).
Legato backup.
Hey, just to confirm what I think is obvious... For an oracle database to be backed up on NT with Legato: you need to either; shutdown, cold backup via legato, startup, or online backup mode and legato's Oracle database extension, [or some sort of strategy involving oracle export]. And to verify... legato's backup an in-use file extension used to backup oracle database datafiles that have not been shutdown... will not give a viable backup ??? A department that has not paid for my section's DBA services has just phoned up with restore problems with a database backed up in this manner! If someone could confirm the obvious, I would be grateful. Regards, Mike. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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: Yet again more Recovery Manager questions
It is my understanding that you can backup all lower versione databases with the highest version of Oracle you are using. Thus, you can use you 8.1.7 rman and catalog to backup all of you databases. Ruth (I have only tried it with different versionof O8, not O8i, but it worked.) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 25, 2002 10:36 AM Hi All, I have a mixed environment 8.0.5 8.1.7 databases. I can not use the same catalog for both versions. I tried creating a new catalog for my 8.0.5 databases (different owner) in the same 8.1.7. database that my other catalog is in using the rman 805 executable. This also results in error messages (about packages/procedures). My Q: Do I really need a database matching the version of RMAN I'm using and/or is there a way to use the 8.1.7 rman executable for 8.0.5 databases? (documentation tends to go that direction) TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- 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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hmmmmm
Title: RE: Hm I just had another idea. Maybe you can use UPDATE table SET column=column+1 RETURNING column INTO somevariable instead of the SELECT FOR UPDATE. This will add 1 to the current value and return the result to the caller. Tony Aponte -Original Message- From: Aponte, Tony Sent: Friday, January 25, 2002 11:11 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: Hm What you are observing is the concurrency mechanism as implemented in the Oracle rdbms. I couldn't explain it better in an email so I suggest you get a hold of Expert One-on-one Oracle by Thomas Kyte, chapter 3 on Locking and Concurrency. Another thought I have is that it looks like you are trying to implement a sequence number. If the application can tolerate gaps in the numbers then you can use an rdbms-managed sequence number and use NEXTVAL in place of the SELECT FOR UPDATE. HTH. Tony Aponte -Original Message- From: Stefan Jakobsson [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 5:35 AM To: Multiple recipients of list ORACLE-L Subject: Hm Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-01555 Mystery (Help)
Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: Hmmmmm
Title: RE: Hm What you are observing is the concurrency mechanism as implemented in the Oracle rdbms. I couldn't explain it better in an email so I suggest you get a hold of Expert One-on-one Oracle by Thomas Kyte, chapter 3 on Locking and Concurrency. Another thought I have is that it looks like you are trying to implement a sequence number. If the application can tolerate gaps in the numbers then you can use an rdbms-managed sequence number and use NEXTVAL in place of the SELECT FOR UPDATE. HTH. Tony Aponte -Original Message- From: Stefan Jakobsson [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 5:35 AM To: Multiple recipients of list ORACLE-L Subject: Hm Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: export the schema exclude two tables?
I tried a different way once - with the PL/SQL extensions to export package. It isn't described in the docs, but there is a file (dbmsexp.sql) in rdbms/admin directory. Basically for each table that needs special treatment, you insert a row in sys.expact$ and identify the PL/SQL code you want to call before or after the table is exported. I had PL/SQL functions that renamed the tables (rename emp to a_emp). It sort of worked; exp renamed the tables before copying the rows and then raised an ORA-942 error because the table wasn't found and then continued with the next table in the schema. One problem was that the exp dmp file still contained the create table statement for the excluded tables. The solution to that was to pre-create dummy tables on the target system with the same names... It was a while ago maybe in Oracle7.3. Right now I can't remember why I did it. Is there a limit on the number of tables you can list for the exp tables parameter? Maybe the issue was to speed up the exp or limit the size of the exp dump file? With a complete export, you can still create dummy tables on the target with the same names as the tables you want to exclude and run imp with ignore=n. This at least excludes certain tables from the imp (if not from the exp). Chaim [EMAIL PROTECTED]@fatcity.com on 01/24/2002 06:50:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table creation scripts, possibly some exutab tables to say obj$.name != Table1, Table2. Hic !! Nooo. I did not say that ;-) What I say is, include all the tablenames except the two that you do not need in your parfile. Regards Raj CC Harvest [EMAIL PROTECTED]@fatcity.com on 01/24/2002 04:55:22 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anyone knows how to do it in Oracle8.1.7? __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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: Backup Strategy
Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations. So, all it does is prohibiting some other program/process from opening the file. But Oracle, as I mentioned, has this file already opened, and it is perfectly capable of reading/writing this file. Of course, the image of the saved file will be fuzzy, and that's why when recovering from online backup we are applying archived RedoLog files (which getting written much more intensely during online backup). As for Peter McLarty note, that he never knew that NT copy could manage keeping the CSN number in sync, well it (NT Copy) does not have to (neither does Oracle Ocopy) keep CSN number in sync. Oracle updates file header with checkpoint SCN, when we issue alter tablespace name begin backup. Then until alter tablespace name end backup, file header will cease updating. And SCN, written in the beginning provides the info, which archived RedoLog files should be used for recovery. Now, please correct me, if I'm wrong. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 6:47 PM Wrong. NT 'COPY' has no problems copying 'opened' oracle db files. I'm using it in 'hot backup' scripts on many dozens systems, and it works fine. Igor, you sure about that? There are backup packages (such as Backup Exec ) that use their own file open copy program to avoid using copy.exe. Have you restored any of these backups made with copy? Jared Note:139327.1 Subject: Differences between Windows NT COPY and Oracle OCOPY When Doing Backups Creation Date:03-APR-2001 Last Revision Date: 04-DEC-2001 PURPOSE A comparison of the differences between the Windows NT copy commnad, and the Oracle ocopy command. Which should be used during an online backup? SCOPE APPLICATION DBAs with databases on the Windows NT platform. The Differences between Windows NT COPY and Oracle OCOPY When Doing Backups: When doing an online backup, should you use the Windows NT COPY command, or the Oracle OCOPY command? While doing online backups you should use OCOPY, or Oracle7 EBU, or Oracle8 (and later) RMAN. With the OCOPY command you could copy to a backup directory on the hard drive
Re: ROLLBACK SEGMENT?
John, I DISAGREE. The gymnastics of assigning a large rollback segment to an export could avoid the snapshot too old error. I agree with Jeremy when he says export does not generate rollback. But I was trying to impress upon him that still an export could end up with the snapshot too old message, particularly if there are plenty of active DML transactions happening while the export is in progress, or if the export uses the consistent parameter, or the rollback segments are not properly sized. To stress my point further, Note:22836.1 on Metalink. I rest my case ;-) Raj orantdba [EMAIL PROTECTED]@fatcity.com on 01/25/2002 08:20:25 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the gymnastics of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot too old error. Export does not generate any rollback, but there could be users performing DML operations on the table that is being exported, and the export needs to be redirected to read from the rollback segments. The likelihood of the error being thrown up especially if one uses the consistent parameter could be very high, if you dont have a large enough rollback segment without an optimal clause. Raj Jeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED] cc: On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... COULD help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote: So what does it accomplish to assign export [to] a particular rollback segment? Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export a particular rollback segment - 1) Create a rollback segment tablespace with one large segment and bring it online before export. 2) Offline all existing rollback segments. 3) Export the database 4) Offline the large tablespace created earlier. 5) Bring back the orginals rollback segments online. --- Seema Singh [EMAIL PROTECTED] wrote: Can I use one rollback segment at time of export?Is yes,then SET TRANSACTION USE ROLLBACK SEGMENT rollbacksegmentname; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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).
Oracle DBAs Needed in Boston
This well known and highly respected client in Boston, Mass. needs an Oracle DBA to join its' I.T.staff. A great opportunity for the right candidate with ALL the required skills listed below. * Duties and Responsibilities Work with technical and application analysts to integrate project requirements into efficient Oracle database designs/modifications in primarily 2-tier configurations. Work with senior staff and management in database strategy decisions in a complex application environment. * Requirements: -Candidates must have 5-7 years experience as an Oracle DBA -Must have managed Oracle in a production environment. -A minimum of one year work experience with Oracle 8i. -Proficient in use of Oracle advanced features such as MTS, Replication, and Partitioning. -Extensive experience with performance tuning and troubleshooting, at the OS, database, and application levels. -Candidate must be comfortable with a range of tools to support monitoring and tuning activities. -Develop clear well-written documentation. -Proficient in developing a range of documentation from high level approach to detailed specifications. -Knowledge of Veritas, OEM, and Oracle 9I a plus. U.S. citizenship or permanent residency is also required. This position offers: * Opportunity to become a key member of the team * Base Salary -in the 85K range D.O.E. * Relocation Assistance NO sub contracting positions available. *U.S. citizenship only PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Boston/DBA/DF We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, OraStaff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Legato backup.
RMAN will interface nicely with Legato. -Joe --- Jenner Mike [EMAIL PROTECTED] wrote: Hey, just to confirm what I think is obvious... For an oracle database to be backed up on NT with Legato: you need to either; shutdown, cold backup via legato, startup, or online backup mode and legato's Oracle database extension, [or some sort of strategy involving oracle export]. And to verify... legato's backup an in-use file extension used to backup oracle database datafiles that have not been shutdown... will not give a viable backup ??? A department that has not paid for my section's DBA services has just phoned up with restore problems with a database backed up in this manner! If someone could confirm the obvious, I would be grateful. Regards, Mike. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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: ORA-01555 Mystery (Help)
In Tom Kyte's book Expert 1-on-1 he says to ANALYZE the table BEFORE starting a big query. Read Chapter 5, starting on page 185 for a complete explanation. Walter K wrote: Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Our customers are part of our team. They're the test department! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: ORA-01555 Mystery (Help)
Don't believe your users. Somebody is causing oracle to read blocks in consistent mode, ie. reading them from rollback segments. If the user is right, then try locking the participating tables in the exclusive mode and see who will complain. Alternatively, go to V$ACCESS table, see who is accessing the table in question, and see who has a transaction lock (v$lock, id1=object id). -Original Message- Sent: Friday, January 25, 2002 11:15 AM To: Multiple recipients of list ORACLE-L Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: 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: Hmmmmm
Yes, this sounds like you need to use a sequence. And you should seriously consider using a sequence, since you might continue to run into locking/performance issues trying to use a table. Chris -Original Message- Sent: Friday, January 25, 2002 10:00 AM To: Multiple recipients of list ORACLE-L There are more rows in that table. It's one that we use for storing sequential numbers we use... like invoice numbers, membership numbers etcetc So we have to lock row when using it, so noone else uses the same number when we add a new member or create a new invoice... But good advice tho, and I would have used lock on it if not for the fact that we have to use row locking on it... /Stefan -Ursprungligt meddelande- Från: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Skickat: den 25 januari 2002 15:00 Till: Multiple recipients of list ORACLE-L Ämne: Re: Hm is there only one row in that table? if so, you can lock the table itself. try it without the nowait... --- Stefan Jakobsson [EMAIL PROTECTED] wrote: Having a small problem I need some help with... The problem is trying to lock a row on a parameter table for an application. The thing we need to do is following: Read the value in one row. LOCK that row for other users, increase the value with one update the row with the new value and release if for access to other users. We are trying to use the following SQL statement. SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT; But the thing is that when we try, anyone can head on in and select the same value and even make an update of that row... What am I missing here? Regards, Stefan Jakobsson Programmer Arel-Data -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.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: Stefan Jakobsson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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).
Add mod_ldap to 9iAS Apache
DBA's Does anyone have instructions for re-compiling apache to include mod_ldap? We need to integrate our existing iplanet ldap into 9iAS for reports and portal. Metalink states re-compiling apache is not supported and the only manuals I've found so far only address OID. TIA ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Howerton 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: ORA-01555 Mystery (Help)
I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle DBAs Needed in Boston
You're right - you're picky.. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 25, 2002 11:55 AM To: Multiple recipients of list ORACLE-L I am picky perhaps, but why is there an apostrophe between its and I.T. staff? This is like store fronts that don't put apostrophes anywhere. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Friday, January 25, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Subject:Oracle DBAs Needed in Boston This well known and highly respected client in Boston, Mass. needs an Oracle DBA to join its' I.T.staff. A great opportunity for the right candidate with ALL the required skills listed below. * Duties and Responsibilities Work with technical and application analysts to integrate project requirements into efficient Oracle database designs/modifications in primarily 2-tier configurations. Work with senior staff and management in database strategy decisions in a complex application environment. * Requirements: -Candidates must have 5-7 years experience as an Oracle DBA -Must have managed Oracle in a production environment. -A minimum of one year work experience with Oracle 8i. -Proficient in use of Oracle advanced features such as MTS, Replication, and Partitioning. -Extensive experience with performance tuning and troubleshooting, at the OS, database, and application levels. -Candidate must be comfortable with a range of tools to support monitoring and tuning activities. -Develop clear well-written documentation. -Proficient in developing a range of documentation from high level approach to detailed specifications. -Knowledge of Veritas, OEM, and Oracle 9I a plus. U.S. citizenship or permanent residency is also required. This position offers: * Opportunity to become a key member of the team * Base Salary -in the 85K range D.O.E. * Relocation Assistance NO sub contracting positions available. *U.S. citizenship only PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Boston/DBA/DF We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, OraStaff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Boivin, Patrice J 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: Mercadante, Thomas F 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: Yet again more Recovery Manager questions
Jack, Generally, you can backup any database to a higher-level Rman repository by using the Rman version of the database you are backing up. So, in your case, use the 8.0.5 version of Rman, connecting to the 8.1.7 repository. Optionally, you could create an Rman 805 catalog in an additional 805 database someplace - even on the same box as the 817 catalog. I know there is a matrix someplace in Oracle land that shows what version of Rman work in what version of the Rman catalog. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 25, 2002 10:36 AM To: Multiple recipients of list ORACLE-L Hi All, I have a mixed environment 8.0.5 8.1.7 databases. I can not use the same catalog for both versions. I tried creating a new catalog for my 8.0.5 databases (different owner) in the same 8.1.7. database that my other catalog is in using the rman 805 executable. This also results in error messages (about packages/procedures). My Q: Do I really need a database matching the version of RMAN I'm using and/or is there a way to use the 8.1.7 rman executable for 8.0.5 databases? (documentation tends to go that direction) TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- 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: Mercadante, Thomas F 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 DBAs Needed in Boston
I am picky perhaps, but why is there an apostrophe between its and I.T. staff? This is like store fronts that don't put apostrophes anywhere. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Friday, January 25, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Subject:Oracle DBAs Needed in Boston This well known and highly respected client in Boston, Mass. needs an Oracle DBA to join its' I.T.staff. A great opportunity for the right candidate with ALL the required skills listed below. * Duties and Responsibilities Work with technical and application analysts to integrate project requirements into efficient Oracle database designs/modifications in primarily 2-tier configurations. Work with senior staff and management in database strategy decisions in a complex application environment. * Requirements: -Candidates must have 5-7 years experience as an Oracle DBA -Must have managed Oracle in a production environment. -A minimum of one year work experience with Oracle 8i. -Proficient in use of Oracle advanced features such as MTS, Replication, and Partitioning. -Extensive experience with performance tuning and troubleshooting, at the OS, database, and application levels. -Candidate must be comfortable with a range of tools to support monitoring and tuning activities. -Develop clear well-written documentation. -Proficient in developing a range of documentation from high level approach to detailed specifications. -Knowledge of Veritas, OEM, and Oracle 9I a plus. U.S. citizenship or permanent residency is also required. This position offers: * Opportunity to become a key member of the team * Base Salary -in the 85K range D.O.E. * Relocation Assistance NO sub contracting positions available. *U.S. citizenship only PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Boston/DBA/DF We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, OraStaff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Boivin, Patrice J 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: ORA-01555 Mystery (Help)
Is it a simple select statement, or is it a cursor select in an PL/SQL block? Does her transaction itself perform any DML on those tables? Raj Walter K [EMAIL PROTECTED]@fatcity.com on 01/25/2002 11:15:26 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: 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: export the schema exclude two tables?
Claim,Jared,Peter,Rajesh: Thanks for the reply. I just try a different method. exp the two tables to a dump, truncate the tables, exp the rest to another dump. imp the two tables back to the database, ship the secpdn dump to the customer. The customer does not like that two tables, because there are two big and the data is useless. Thanks all. Chris --- [EMAIL PROTECTED] wrote: I tried a different way once - with the PL/SQL extensions to export package. It isn't described in the docs, but there is a file (dbmsexp.sql) in rdbms/admin directory. Basically for each table that needs special treatment, you insert a row in sys.expact$ and identify the PL/SQL code you want to call before or after the table is exported. I had PL/SQL functions that renamed the tables (rename emp to a_emp). It sort of worked; exp renamed the tables before copying the rows and then raised an ORA-942 error because the table wasn't found and then continued with the next table in the schema. One problem was that the exp dmp file still contained the create table statement for the excluded tables. The solution to that was to pre-create dummy tables on the target system with the same names... It was a while ago maybe in Oracle7.3. Right now I can't remember why I did it. Is there a limit on the number of tables you can list for the exp tables parameter? Maybe the issue was to speed up the exp or limit the size of the exp dump file? With a complete export, you can still create dummy tables on the target with the same names as the tables you want to exclude and run imp with ignore=n. This at least excludes certain tables from the imp (if not from the exp). Chaim [EMAIL PROTECTED]@fatcity.com on 01/24/2002 06:50:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table creation scripts, possibly some exutab tables to say obj$.name != Table1, Table2. Hic !! Nooo. I did not say that ;-) What I say is, include all the tablenames except the two that you do not need in your parfile. Regards Raj CC Harvest [EMAIL PROTECTED]@fatcity.com on 01/24/2002 04:55:22 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anyone knows how to do it in Oracle8.1.7? __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858)
RE: How to use a package variable in pkg1 inside of package pkg2
I'm not sure why you want to do this. Why not have the package that you call return the value back to the calling package. I would not guarantee that the value you expect to be stored in the variable would exist when you think it will be there. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 25, 2002 10:31 AM To: Multiple recipients of list ORACLE-L pkg2 You have to declare this variable in package1 specification create or replace package pkg1 is end; / create or replace package pkg2 is procedure showvar; end; / create or replace package pkg2 body is procedure showvar begin dbms_output.put_line(pkg1.v_var); end; end; / exec pkg2.showvar in SQLPlus prompt should do that Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Rick_Cale@team health.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: How to use a package variable in pkg1 inside of package pkg2 om 2002.01.25 16:35 Please respond to ORACLE-L Hi All, I have a package pkg1 that has a variable var 1 declared Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier pkg1.var1 must be declared. What do I need to do to correct. Pkg1 compiles fine. Thanks Rick -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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 DBAs Needed in Baton Rouge, Louisana
So, the client will only relocate people who have already decided to move to Louisiana? --- OraStaff [EMAIL PROTECTED] wrote: We have a client requirement for several Oracle DBAs in Baton Rouge, Louisana. *This company will provide relocation assistance, as long as there is a viable reason besides money for the candidate's desire to move there. __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: ROLLBACK SEGMENT?
HI Raj, I hope you feel better :-). John [EMAIL PROTECTED] wrote: John, I DISAGREE. The gymnastics of assigning a large rollback segment to an export could avoid the snapshot too old error. I agree with Jeremy when he says export does not generate rollback. But I was trying to impress upon him that still an export could end up with the snapshot too old message, particularly if there are plenty of active DML transactions happening while the export is in progress, or if the export uses the consistent parameter, or the rollback segments are not properly sized. To stress my point further, Note:22836.1 on Metalink. I rest my case ;-) Raj orantdba [EMAIL PROTECTED]@fatcity.com on 01/25/2002 08:20:25 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the gymnastics of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot too old error. Export does not generate any rollback, but there could be users performing DML operations on the table that is being exported, and the export needs to be redirected to read from the rollback segments. The likelihood of the error being thrown up especially if one uses the consistent parameter could be very high, if you dont have a large enough rollback segment without an optimal clause. Raj Jeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED] cc: On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... COULD help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote: So what does it accomplish to assign export [to] a particular rollback segment? Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export a particular rollback segment - 1) Create a rollback segment tablespace with one large segment and bring it online before export. 2) Offline all existing rollback segments. 3) Export the database 4) Offline the large tablespace created earlier. 5) Bring back the orginals rollback segments online. --- Seema Singh [EMAIL PROTECTED] wrote: Can I use one rollback segment at time of export?Is yes,then SET TRANSACTION USE ROLLBACK SEGMENT rollbacksegmentname; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: orantdba 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: ORA-01555 Mystery (Help)
Precisely the point I was trying to make, when I put the question if it was a normal select, or if it was within a PL/SQL block? The myth is that snapshot too old happens only when some other transaction was in the process of performing an DML on a table, when you did a select on it. It can happen for other reasons too. Search on Metalink for Delayed block cleanouts and fetch across commits. Raj Baker, Barbara [EMAIL PROTECTED]@fatcity.com on 01/25/2002 11:52:05 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
OT: RE: Oracle DBAs Needed in Boston
Not only shouldn't there be an apostophe, but it's in the wrong place. Geeze. ;-) --- Boivin, Patrice J [EMAIL PROTECTED] wrote: I am picky perhaps, but why is there an apostrophe between its and I.T. staff? This is like store fronts that don't put apostrophes anywhere. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Friday, January 25, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Subject: Oracle DBAs Needed in Boston This well known and highly respected client in Boston, Mass. needs an Oracle DBA to join its' I.T.staff. A great opportunity for the right candidate with ALL the required skills listed below. * Duties and Responsibilities Work with technical and application analysts to integrate project requirements into efficient Oracle database designs/modifications in primarily 2-tier configurations. Work with senior staff and management in database strategy decisions in a complex application environment. * Requirements: -Candidates must have 5-7 years experience as an Oracle DBA -Must have managed Oracle in a production environment. -A minimum of one year work experience with Oracle 8i. -Proficient in use of Oracle advanced features such as MTS, Replication, and Partitioning. -Extensive experience with performance tuning and troubleshooting, at the OS, database, and application levels. -Candidate must be comfortable with a range of tools to support monitoring and tuning activities. -Develop clear well-written documentation. -Proficient in developing a range of documentation from high level approach to detailed specifications. -Knowledge of Veritas, OEM, and Oracle 9I a plus. U.S. citizenship or permanent residency is also required. This position offers: * Opportunity to become a key member of the team * Base Salary -in the 85K range D.O.E. * Relocation Assistance NO sub contracting positions available. *U.S. citizenship only PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Boston/DBA/DF We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, OraStaff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Boivin, Patrice J 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Backup Strategy
Hi Igor... I have a couple questions to you... What do you think will happen the first time you have ANY problem recovering a backup that was taken with the COPY utility and you contact Oracle support... Will Oracle support you or immediately blame it on you backup method since it is documented that you should not use it... -Original Message- Sent: Friday, January 25, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations. So, all it does is prohibiting some other program/process from opening the file. But Oracle, as I mentioned, has this file already opened, and it is perfectly capable of reading/writing this file. Of course, the image of the saved file will be fuzzy, and that's why when recovering from online backup we are applying archived RedoLog files (which getting written much more intensely during online backup). As for Peter McLarty note, that he never knew that NT copy could manage keeping the CSN number in sync, well it (NT Copy) does not have to (neither does Oracle Ocopy) keep CSN number in sync. Oracle updates file header with checkpoint SCN, when we issue alter tablespace name begin backup. Then until alter tablespace name end backup, file header will cease updating. And SCN, written in the beginning provides the info, which archived RedoLog files should be used for recovery. Now, please correct me, if I'm wrong. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 6:47 PM Wrong. NT 'COPY' has no problems copying 'opened' oracle db files. I'm using it in 'hot backup' scripts on many dozens systems, and it works fine. Igor, you sure about that? There are backup packages (such as Backup Exec ) that use their own file open copy program to avoid using copy.exe. Have you restored any of these backups made with copy? Jared Note:139327.1 Subject: Differences between Windows NT COPY and Oracle OCOPY When Doing Backups Creation Date:03-APR-2001 Last Revision Date: 04-DEC-2001 PURPOSE A comparison of the differences between the Windows NT copy commnad, and the Oracle ocopy command. Which should be used during an online backup? SCOPE APPLICATION DBAs with databases on the Windows NT platform. The
RE: Session_wait
Eventually... real: 0 SQL select 2 name, value 3 from 4 v$sesstat vs, v$statname sn 5 where 6 vs.statistic#=sn.statistic# and 7 value is NOT NULL and 8 value0 and 9 sid=11; NAME VALUE - logons cumulative 1 logons current1 opened cursors cumulative 1072 opened cursors current 11 user commits 26 user calls 341 recursive calls 198492 recursive cpu usage4089 session logical reads 77233609 CPU used when call started 696253 CPU used by this session 696253 session connect time 20654909 process last non-idle time 20654909 session uga memory 1347972 session uga memory max 2872124 messages sent 2154 session pga memory 6752520 session pga memory max 6752520 enqueue requests 11878 enqueue releases 11876 total file opens 13 db block gets 2642119 consistent gets74591490 physical reads 148822 db block changes3005410 consistent changes 141 physical writes2728 physical writes non checkpoint 2728 change write time 29633 redo synch writes40 redo synch time 305 free buffer requested311344 dirty buffers inspected 63544 pinned buffers inspected 2 hot buffers moved to head of LRU 13576 free buffer inspected 63546 commit cleanout failures: block lost860 commit cleanout failures: callback failure 12 commit cleanouts 79286 commit cleanouts successfully completed 78414 CR blocks created 141 switch current to new buffer 54870 write clones created in foreground 198 prefetched blocks108149 physical reads direct 3594 physical writes direct 2728 calls to kcmgcs 56343 calls to kcmgas4130 calls to get snapshot scn: kcmgss 82845 redo entries1506007 redo size 1.184E+09 redo buffer allocation retries 886 redo log space requests 3 redo log space wait time122 redo ordering marks 4 data blocks consistent reads - undo records applied 141 no work - consistent read gets 72058049 cleanouts only - consistent read gets 31927 rollbacks only - consistent read gets 141 immediate (CURRENT) block cleanout applications 12274 immediate (CR) block cleanout applications31927
RE: ORA-01555 Mystery (Help)
Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject:ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Backup Strategy
Igor, That sounds good, but what is Oracle Support going to say when you call them for support on a database recovery and you mention that you used NT COPY?? Your hosed, if there attitude is you should have used OCOPY so we can't help you. Chris -Original Message- Sent: Friday, January 25, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations. So, all it does is prohibiting some other program/process from opening the file. But Oracle, as I mentioned, has this file already opened, and it is perfectly capable of reading/writing this file. Of course, the image of the saved file will be fuzzy, and that's why when recovering from online backup we are applying archived RedoLog files (which getting written much more intensely during online backup). As for Peter McLarty note, that he never knew that NT copy could manage keeping the CSN number in sync, well it (NT Copy) does not have to (neither does Oracle Ocopy) keep CSN number in sync. Oracle updates file header with checkpoint SCN, when we issue alter tablespace name begin backup. Then until alter tablespace name end backup, file header will cease updating. And SCN, written in the beginning provides the info, which archived RedoLog files should be used for recovery. Now, please correct me, if I'm wrong. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 6:47 PM Wrong. NT 'COPY' has no problems copying 'opened' oracle db files. I'm using it in 'hot backup' scripts on many dozens systems, and it works fine. Igor, you sure about that? There are backup packages (such as Backup Exec ) that use their own file open copy program to avoid using copy.exe. Have you restored any of these backups made with copy? Jared Note:139327.1 Subject: Differences between Windows NT COPY and Oracle OCOPY When Doing Backups Creation Date:03-APR-2001 Last Revision Date: 04-DEC-2001 PURPOSE A comparison of the differences between the Windows NT copy commnad, and the Oracle ocopy command. Which should be used during an online backup? SCOPE APPLICATION DBAs with databases on the Windows NT platform. The Differences between Windows NT COPY and Oracle OCOPY When Doing Backups:
Re: Add mod_ldap to 9iAS Apache
Here's some places to start: http://httpd.apache.org/docs/sitemap.html http://www.kie.berkeley.edu/people/jmorrow/mod_ldap/ Jared James Howerton [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 08:55 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Add mod_ldap to 9iAS Apache DBA's Does anyone have instructions for re-compiling apache to include mod_ldap? We need to integrate our existing iplanet ldap into 9iAS for reports and portal. Metalink states re-compiling apache is not supported and the only manuals I've found so far only address OID. TIA ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Howerton 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: ROLLBACK SEGMENT?
Guys, in the next round, please correct Mr. Wilton's first name to - Jeremiah. Cut paste is a wonderful thing ;-) - Kirti -Original Message- Sent: Friday, January 25, 2002 10:35 AM To: Multiple recipients of list ORACLE-L John, I DISAGREE. The gymnastics of assigning a large rollback segment to an export could avoid the snapshot too old error. I agree with Jeremy when he says export does not generate rollback. But I was trying to impress upon him that still an export could end up with the snapshot too old message, particularly if there are plenty of active DML transactions happening while the export is in progress, or if the export uses the consistent parameter, or the rollback segments are not properly sized. To stress my point further, Note:22836.1 on Metalink. I rest my case ;-) Raj orantdba [EMAIL PROTECTED]@fatcity.com on 01/25/2002 08:20:25 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the gymnastics of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot too old error. Export does not generate any rollback, but there could be users performing DML operations on the table that is being exported, and the export needs to be redirected to read from the rollback segments. The likelihood of the error being thrown up especially if one uses the consistent parameter could be very high, if you dont have a large enough rollback segment without an optimal clause. Raj Jeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED] cc: On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... COULD help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote: So what does it accomplish to assign export [to] a particular rollback segment? Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export a particular rollback segment - 1) Create a rollback segment tablespace with one large segment and bring it online before export. 2) Offline all existing rollback segments. 3) Export the database 4) Offline the large tablespace created earlier. 5) Bring back the orginals rollback segments online. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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 DBAs Needed in Boston
Becauses, he needs ... HELP ;-) -Original Message- Sent: Friday, January 25, 2002 10:55 AM To: Multiple recipients of list ORACLE-L I am picky perhaps, but why is there an apostrophe between its and I.T. staff? This is like store fronts that don't put apostrophes anywhere. : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Friday, January 25, 2002 12:40 PM To: Multiple recipients of list ORACLE-L Subject:Oracle DBAs Needed in Boston This well known and highly respected client in Boston, Mass. needs an Oracle DBA to join its' I.T.staff. A great opportunity for the right candidate with ALL the required skills listed below. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Session_wait
This is a busy little beaver...how long you say this runs? opened cursors cumulative 1072 session logical reads 77233609 db block gets 2642119 consistent gets74591490 physical reads 148822 db block changes3005410 consistent changes 141 no work - consistent read gets 72058049 table scans (short tables) 210918 table scans (long tables)36 table scan rows gotten798264962 table scan blocks gotten 71788386 table fetch by rowid1074164 leaf node splits 4018 execute count 74445 bytes sent via SQL*Net to client 59650 bytes received via SQL*Net from client84233 SQL*Net roundtrips to/from client 342 buffer is not pinned count 73513922 I see some updates (or inserts) and heavy reads...at first glance, though, you don't appear to be I/O throttled but likely have inefficiencies in buffer cache. Pin some small tables? Reexamine access paths for fts, even if on small tables (generally defined to be around 5% in blocks of buffer cache size?)and consider seeking out and destroying nested loops joins in favor of hash joins. There's more CPU, but less buffer cache splashing arounddon't forget to review init.ora settings for hash, buffer pools, and query planning. (...be happy to do a flyover of those as well, if you like.) That's about all i can get in a one minute glance, but there are alot of people on the list who'll see more. Look to them for longer posts with more explanation. Good Luck! - Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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: v$session question
What platform? On HP-UX 11.0 and Oracle 8.1.6.2.0 and w/ Clients on Win/NT 4 Workstations, here is what I get: (last few lines) SQL select username, program from v$session; USERNAMEPROGRAM --- --- X468Y02 C:\orant\bin\ifrun60.exe X0C0AJF C:\orant\bin\ifrun60.exe X020C7P C:\orant\bin\ifrun60.exe XFZGBMX C:\orant\bin\ifrun60.exe X225D64 C:\orant\bin\ifrun60.exe XDTF9GR C:\orant\bin\ifrun60.exe - Kirti -Original Message- Sent: Friday, January 25, 2002 8:35 AM To: Multiple recipients of list ORACLE-L In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio 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: Deshpande, Kirti 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).
How can i receive name of the running procedure
Hallo, How can I select the name of the procedure, which is running.? I mean I am running a procedure and I want the name of the pocedure to be inserted in a table. Please help me with a simple pl/sql script on this. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Backup Stragedy
I already did that, about a year ago. btw, there is a (very low-traffic) oracle-nt list: http://groups.yahoo.com - http://groups.yahoo.com/group/oracle-on-nt a collection links to this kind of stuff could be put there? regards, ep ORACLE-L Digest -- Volume 2002, Number 025 -- From: C.S.Venkata Subramanian [EMAIL PROTECTED] Date: Thu, 24 Jan 2002 18:04:02 +0530 Subject: Re: Backup Strategy Hi All, http://www.geocities.com/tbcox23/ Go here and get the paper. Regards Venkat -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ROLLBACK SEGMENT?
Sorry Jeremiah! John [EMAIL PROTECTED] wrote: Guys, in the next round, please correct Mr. Wilton's first name to - Jeremiah. Cut paste is a wonderful thing ;-) - Kirti -Original Message- Sent: Friday, January 25, 2002 10:35 AM To: Multiple recipients of list ORACLE-L John, I DISAGREE. The gymnastics of assigning a large rollback segment to an export could avoid the snapshot too old error. I agree with Jeremy when he says export does not generate rollback. But I was trying to impress upon him that still an export could end up with the snapshot too old message, particularly if there are plenty of active DML transactions happening while the export is in progress, or if the export uses the consistent parameter, or the rollback segments are not properly sized. To stress my point further, Note:22836.1 on Metalink. I rest my case ;-) Raj orantdba [EMAIL PROTECTED]@fatcity.com on 01/25/2002 08:20:25 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the gymnastics of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot too old error. Export does not generate any rollback, but there could be users performing DML operations on the table that is being exported, and the export needs to be redirected to read from the rollback segments. The likelihood of the error being thrown up especially if one uses the consistent parameter could be very high, if you dont have a large enough rollback segment without an optimal clause. Raj Jeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED] cc: On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... COULD help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote: So what does it accomplish to assign export [to] a particular rollback segment? Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export a particular rollback segment - 1) Create a rollback segment tablespace with one large segment and bring it online before export. 2) Offline all existing rollback segments. 3) Export the database 4) Offline the large tablespace created earlier. 5) Bring back the orginals rollback segments online. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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).
Number_of_rows
Hallo all you gurus, How can I write in the pl/sql code if I want to insert in a table the number of rows that are inserted in the select statement in the procedure? Give me a good example, please. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Session_wait
Hi, Ross, This has been running for about 4 hours now. I got an SQL trace file, and looked at the execution plans. So here's the deal. There's an interesting join condition ...where A.col1=B.col1.. However, A has a few hundred distinct values in that column, none of them being NULL, and B, which has a few hundred thousand rows, has ALL NULLs in the corresponding column, and that column is not indexed, too. That's the query where it sits for a couple of hours. Guess what the optimizer is doing (8i)? I think internal effects are secondary in this scenario. It is the production (including the database) designed by the company named DELTEK, so nobody can change the code. Anyway, I reported my findings... Thank you very much for your help, it's always appreciated. Best, Sergey -Original Message- Sent: Friday, January 25, 2002 12:56 PM To: Multiple recipients of list ORACLE-L Subject:RE: Session_wait This is a busy little beaver...how long you say this runs? opened cursors cumulative 1072 session logical reads 77233609 db block gets 2642119 consistent gets74591490 physical reads 148822 db block changes3005410 consistent changes 141 no work - consistent read gets 72058049 table scans (short tables) 210918 table scans (long tables)36 table scan rows gotten798264962 table scan blocks gotten 71788386 table fetch by rowid1074164 leaf node splits 4018 execute count 74445 bytes sent via SQL*Net to client 59650 bytes received via SQL*Net from client84233 SQL*Net roundtrips to/from client 342 buffer is not pinned count 73513922 I see some updates (or inserts) and heavy reads...at first glance, though, you don't appear to be I/O throttled but likely have inefficiencies in buffer cache. Pin some small tables? Reexamine access paths for fts, even if on small tables (generally defined to be around 5% in blocks of buffer cache size?)and consider seeking out and destroying nested loops joins in favor of hash joins. There's more CPU, but less buffer cache splashing arounddon't forget to review init.ora settings for hash, buffer pools, and query planning. (...be happy to do a flyover of those as well, if you like.) That's about all i can get in a one minute glance, but there are alot of people on the list who'll see more. Look to them for longer posts with more explanation. Good Luck! - Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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: Babich , Sergey 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: Mirroring REDO logs to an nfs drive
I have experienced problems in the past when using NFS drives to dump large amounts of data. No sure if having the redo logs there will cause the same problem. I am running a tru64 4.0f environment, and what I was doing ( needed the disk space at the time) was export my production databases to a NFS mounted drive. From what I understand, due to the amount of data being dumped to the NFS drive, I basically over-saturated the connection, which caused the automount daemon on my other systems to drop the NFS drive periodically and without warning. To fix the problem was to simply stop the export processes going to the NFS drives (which were running every 2nd night) and reboot the NFS master. Darren -Original Message- Sent: January 24, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Our site is preparing to fail over to our backup server. We need to do maintenance on our production server, and will be running on the backup for about 24 hours. One issue I brought up was that our backup server is not equiped with mirrored drives, thus there was the possibility that a drive failure could destroy an online redo log. On our production box the logs are not software mirrored, because of the physical mirroring in our drive cabinet. As a result I was told to multiplex the redo logs once we had failed over to the backup server. Furthermore I would add the new members to an nfs drive, so that even a pesky controller couldn't foil our mirrored log files. I have some questions about this. First, am I just looking for problems by doing this? I would appreciate any tips or warnings on this subject. Secondly, researching this topic made me curious as to my DB's settings for MAXLOGFILES and MAXLOGMEMBERS. Where can I find these parameters? I was sure I would find them in v$parameter, but they were not there. Thanks for any response, Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure 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: Browett, Darren 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: ROLLBACK SEGMENT?
Jeremiah, it is. Thanks, Kirit ;-) Raj Deshpande, Kirti [EMAIL PROTECTED]@fatcity.com on 01/25/2002 12:55:27 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Guys, in the next round, please correct Mr. Wilton's first name to - Jeremiah. Cut paste is a wonderful thing ;-) - Kirti -Original Message- Sent: Friday, January 25, 2002 10:35 AM To: Multiple recipients of list ORACLE-L John, I DISAGREE. The gymnastics of assigning a large rollback segment to an export could avoid the snapshot too old error. I agree with Jeremy when he says export does not generate rollback. But I was trying to impress upon him that still an export could end up with the snapshot too old message, particularly if there are plenty of active DML transactions happening while the export is in progress, or if the export uses the consistent parameter, or the rollback segments are not properly sized. To stress my point further, Note:22836.1 on Metalink. I rest my case ;-) Raj orantdba [EMAIL PROTECTED]@fatcity.com on 01/25/2002 08:20:25 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Raj, Interesting that you first agree with Jeremy and then argue with him. It is precisely because export does not generate rollback that the gymnastics of taking all of the other rbs's offline will not help anything. It might make you feel better, however :-). John [EMAIL PROTECTED] wrote: Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? For the same reason, any other transaction could end up with a snapshot too old error. Export does not generate any rollback, but there could be users performing DML operations on the table that is being exported, and the export needs to be redirected to read from the rollback segments. The likelihood of the error being thrown up especially if one uses the consistent parameter could be very high, if you dont have a large enough rollback segment without an optimal clause. Raj Jeremiah Wilton [EMAIL PROTECTED]@fatcity.com on 01/23/2002 11:20:40 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipien ts of list ORACLE-L [EMAIL PROTECTED] cc: On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote: H ... COULD help in avoiding snapshot too old errors. Sorry to press the point, but could you elaborate on how that COULD possibly make any difference for 'snapshot too old'? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton Jeremiah Wilton [EMAIL PROTECTED] wrote: So what does it accomplish to assign export [to] a particular rollback segment? Export doesn't generate any rollback, right, so what is it supposed to accomplish by doing this incantation? On Tue, 22 Jan 2002, Jason Rowski wrote: ... you can use the following trick to assign export a particular rollback segment - 1) Create a rollback segment tablespace with one large segment and bring it online before export. 2) Offline all existing rollback segments. 3) Export the database 4) Offline the large tablespace created earlier. 5) Bring back the orginals rollback segments online. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
OT - this means ??? MS Exchange runs on SQL Server
There have been turf wars between central (mainframe/unix oriented) and departmental (NT oriented) SysAdmns here for 5+ years (administration promulgated a decentralization policy, and changed IT funding to support it). Now, some of the departmental SysAdmns are finally getting sick of some of the overhead involved in running the own little fiefdoms. At the same time, central LAN gurus are taking on AD implementation. With reference to the above, yesterday I heard a central SysAdmn/LAN guru saying: MS Exchange runs on SQL Server what does that mean? Is the SQL Server that Exchange runs on pretty much the same as the off-the-shelf version that one would install for standard development purposes, or is it pre-tuned, specially configured, etc? thanks, ep ORACLE-L Digest -- Volume 2002, Number 025 -- From: bill thater [EMAIL PROTECTED] Date: Thu, 24 Jan 2002 14:14:51 -0500 Subject: Re: Backup Strategy [EMAIL PROTECTED] wrote: JoJo -- Sure, but be aware that Unix abaci are better than NT abaci. don't forget the VMS abaci.;-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01555 Mystery (Help)
I was almost ready to subscribe to the idea of delayed cleanout, but I cannot understand why really. The necessity for reading a block from the rollback segments comes from encountering during the course of the SELECT a block the SCN of which is higher than the SCN when the query started. I have of course no certainty about it, but it would be logical to expect the block's SCN to be properly set irrespectively of the clean-out being immediate or delayed. In other words, even if a SELECT physically writes blocks, it should not have anything to do with rollback segments anyway. I share Mladen's opinion, somebody must be economical with the truth somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you really sure that the code contains no 'just in case' commit ou rollback which would release the lock? And by the way, 5 hours look to me like an awfully long time, even for a 20 million row mega-select of death. [EMAIL PROTECTED] wrote: Precisely the point I was trying to make, when I put the question if it was a normal select, or if it was within a PL/SQL block? The myth is that snapshot too old happens only when some other transaction was in the process of performing an DML on a table, when you did a select on it. It can happen for other reasons too. Search on Metalink for Delayed block cleanouts and fetch across commits. Raj Baker, Barbara [EMAIL PROTECTED]@fatcity.com on 01/25/2002 11:52:05 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w -- 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: Number_of_rows
Use SQL%ROWCOUNT. BEGIN insert into table1 select * from table2; dbms_output.put_line(SQL%ROWCOUNT); END; / Executing this PL/SQL block should display you the number of rows that were inserted into table1. Raj [EMAIL PROTECTED]@fatcity.com on 01/25/2002 01:20:40 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hallo all you gurus, How can I write in the pl/sql code if I want to insert in a table the number of rows that are inserted in the select statement in the procedure? Give me a good example, please. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: ORA-01555 Mystery (Help)
Dick: This makes the assumption that Walter can get to the code, find out what it's doing, and make modifications. (In our case, we can't. The code is vendor-supplied, unchangeable, and is written in Cobol). It also sounds like this might be happening in the middle of the night. I'd guess there's a limit to how much information Walter can gather about what happened 5 hours into the job at 3:00 am If he's desperate to get the data loaded and he can't change the sql, then his options are limited. I believe all the possible causes for 1555 errors have been listed in this thread. Hopefully he can identify which is causing the grief and find a resolution. Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:09 AM To: Baker; Barbara; Multiple recipients of list ORACLE-L Subject:Re:RE: ORA-01555 Mystery (Help) Barb, I've tried Steve's idea in the past and although it sorta fixed the problem with the large batch job, it created problems elsewhere. It also did not totally fix the problem when other applications updated parts of the table(s) and committed their transaction. Many folks believe that by allocating a large rollback segment to their session they have fixed the problem. Wrong, this particular issue can be caused by your own application plus anyone else who is using the database and other rollback segments. The real issue here is to either find out who or what is updating the underlying table or else speeding up the process. There were two points that I found easy to implement that fixed 90% of our errors. 1) Don't commit across a cursor. In this scenario look for cases where your pulling data from a table, updating that table, and then continuing to read data from the cursor. This one will pop a 1555 very regularly since the cursor depends on a read consistent view, but you just released the rollback segments. 2) Use an order or group by in the select statement. This one sounds odd, but it does work. By placing either an order by or group by clause in the select statement you force Oracle to read all of the data at one time, place it in a temp segment, and then hand it over. The end result is that when the first row of data appears in your application you no longer need any rollback to create a read consistent view. If your just pulling from the table, then Oracle hands over a row as it satisfies the query criteria. OH, did you just update and commit a change? Well that is NOT going to be included in your result set since it is already locked in concrete. Try one of these see if it fixes your problem. Dick Goulet Reply Separator Subject:RE: ORA-01555 Mystery (Help) Author: Baker; Barbara [EMAIL PROTECTED] Date: 1/25/2002 8:52 AM I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else
Re: Number_of_rows
RTFM on SQL%ROWCOUNT and %ROWCOUNT Jared Sorry, all exampled out today. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 10:20 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Number_of_rows Hallo all you gurus, How can I write in the pl/sql code if I want to insert in a table the number of rows that are inserted in the select statement in the procedure? Give me a good example, please. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: ORA-01555 Mystery (Help)
Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From:Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ . -- 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: ORACLE-L Digest -- Volume 2002, Number 025
Yes, and if one buys enterprise version with a special support plan, there is an option for an onsite proctologist. ORACLE-L Digest -- Volume 2002, Number 025 -- From: Loughmiller, Greg [EMAIL PROTECTED] Date: Thu, 24 Jan 2002 14:55:44 -0500 Subject: RE: SCOTT/TIGER And there is documentation that comes with Oracle? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ang: Re: Number_of_rows
Yes But I want thatthat number is inserted into the table. [EMAIL PROTECTED]@fatcity.com den 2002-01-25 10:39 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Use SQL%ROWCOUNT. BEGIN insert into table1 select * from table2; dbms_output.put_line(SQL%ROWCOUNT); END; / Executing this PL/SQL block should display you the number of rows that were inserted into table1. Raj [EMAIL PROTECTED]@fatcity.com on 01/25/2002 01:20:40 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hallo all you gurus, How can I write in the pl/sql code if I want to insert in a table the number of rows that are inserted in the select statement in the procedure? Give me a good example, please. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
Standby database question
Hi DBAs, One of the co-workers has a hot standby database. Logs are applied at some interval. He has to add a tablespace. What is necessay to make standby database aware of this? Thanks Rick -- 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).
OraJava function vs. procedure
So, there I am. 8.1.7.2 with JVM loaded in Oracle. I need to be able to
access the Unix shell from within a procedure, so naturally, I plagiarize
and modify a very simple Java class from somewhere in Metalink:
--- Java code start
import java.lang.Runtime;
import java.lang.Process;
import java.io.IOException;
import java.lang.InterruptedException;
class QT_Exec_OS {
public static int main(String args[]) {
int retval = 0;
try {
String ftpCommand;
ftpCommand = /usr/bin/ls + args[0];
Process p = Runtime.getRuntime().exec(ftpCommand);
try {
p.waitFor();
} catch (InterruptedException intexc) {
retval = 700;
}
retval = p.exitValue();
} catch (IOException e) {
e.printStackTrace();
retval = 701;
}
return retval;
}
}
--- Java code end
And then, the PL/SQL wrapper:
--- PL/SQL code start
CREATE OR REPLACE PROCEDURE qt_rjtest (S1 IN VARCHAR2)
AS LANGUAGE JAVA
name 'QT_Exec_OS.main(java.lang.String[])';
/
--- PL/SQL code end
This works fine, but I'm not sure why. According to Metalink, I should be
getting a PLS-311 error because the Java code is returning a value. H.
But when I try to create a PL/SQL function to make use of the Java code's
return value:
--- PL/SQL code start
CREATE OR REPLACE FUNCTION qt_rjtest_f (S1 IN VARCHAR2)
RETURN NUMBER
AS LANGUAGE JAVA
name 'QT_Exec_OS.main(java.lang.String[]) return int';
/
--- PL/SQL code end
...I get the PLS-311 the declaration of
QT_Exec_OS.main(java.lang.String[]) return int is incomplete or malformed
error.
So, I'm guessing that the Java doesn't actually return a value, but I can't
figure out why.
Anyone?
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: ORA-01555 Mystery (Help)
Jared, would you elaborate more on this? Does this need to be a 'compute' or can it be an 'estimate' on the analyze? I read the info on Steve's site as suggested by Barb and it sounds like block cleanout may be the issue but I'm still trying to digest the concept/issue as it relates to my circumstance. For the others that have contributed to the thread, yes, the table is definitely locked in exclusive mode (via a different session) before the SELECT is performed and the lock is not released until the following day. I too was suspicious that the lock was accidentally being released. -w --- [EMAIL PROTECTED] wrote: Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From:Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ . -- 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). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- 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
Re: Backup Strategy
Well, I know what you mean. But, I'll take my chances, because as I said before, MetaLink Note:139327.1 didn't convince me at all that OCOPY is any better than regular NT Copy command. Besides, I am keeping two generations of backup (the latest and the one prior to that), so if anything goes wrong with the latest backup image of the db file, I can always recover, using older backup and archived RedoLogs from both backups. Also, our customers wouldn't wait for Oracle support , and having two generations of backups, I can resolve potential issues much faster than ... Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 25, 2002 12:30 PM Igor, That sounds good, but what is Oracle Support going to say when you call them for support on a database recovery and you mention that you used NT COPY?? Your hosed, if there attitude is you should have used OCOPY so we can't help you. Chris -Original Message- Sent: Friday, January 25, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations. So, all it does is prohibiting some other program/process from opening the file. But Oracle, as I mentioned, has this file already opened, and it is perfectly capable of reading/writing this file. Of course, the image of the saved file will be fuzzy, and that's why when recovering from online backup we are applying archived RedoLog files (which getting written much more intensely during online backup). As for Peter McLarty note, that he never knew that NT copy could manage keeping the CSN number in sync, well it (NT Copy) does not have to (neither does Oracle Ocopy) keep CSN number in sync. Oracle updates file header with checkpoint SCN, when we issue alter tablespace name begin backup. Then until alter tablespace name end backup, file header will cease updating. And SCN, written in the beginning provides the info, which archived RedoLog files should be used for recovery. Now, please correct me, if I'm wrong. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 6:47 PM Wrong. NT 'COPY' has no problems copying 'opened' oracle db
RE: RE: ORA-01555 Mystery (Help)
How about doing a set transaction to a large rollback before running this query if the analyze doesn't resolve the problem. Kathy -Original Message- Sent: Friday, January 25, 2002 10:40 AM To: Multiple recipients of list ORACLE-L Dick: This makes the assumption that Walter can get to the code, find out what it's doing, and make modifications. (In our case, we can't. The code is vendor-supplied, unchangeable, and is written in Cobol). It also sounds like this might be happening in the middle of the night. I'd guess there's a limit to how much information Walter can gather about what happened 5 hours into the job at 3:00 am If he's desperate to get the data loaded and he can't change the sql, then his options are limited. I believe all the possible causes for 1555 errors have been listed in this thread. Hopefully he can identify which is causing the grief and find a resolution. Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:09 AM To: Baker; Barbara; Multiple recipients of list ORACLE-L Subject:Re:RE: ORA-01555 Mystery (Help) Barb, I've tried Steve's idea in the past and although it sorta fixed the problem with the large batch job, it created problems elsewhere. It also did not totally fix the problem when other applications updated parts of the table(s) and committed their transaction. Many folks believe that by allocating a large rollback segment to their session they have fixed the problem. Wrong, this particular issue can be caused by your own application plus anyone else who is using the database and other rollback segments. The real issue here is to either find out who or what is updating the underlying table or else speeding up the process. There were two points that I found easy to implement that fixed 90% of our errors. 1) Don't commit across a cursor. In this scenario look for cases where your pulling data from a table, updating that table, and then continuing to read data from the cursor. This one will pop a 1555 very regularly since the cursor depends on a read consistent view, but you just released the rollback segments. 2) Use an order or group by in the select statement. This one sounds odd, but it does work. By placing either an order by or group by clause in the select statement you force Oracle to read all of the data at one time, place it in a temp segment, and then hand it over. The end result is that when the first row of data appears in your application you no longer need any rollback to create a read consistent view. If your just pulling from the table, then Oracle hands over a row as it satisfies the query criteria. OH, did you just update and commit a change? Well that is NOT going to be included in your result set since it is already locked in concrete. Try one of these see if it fixes your problem. Dick Goulet Reply Separator Subject:RE: ORA-01555 Mystery (Help) Author: Baker; Barbara [EMAIL PROTECTED] Date: 1/25/2002 8:52 AM I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table.
Re: Backup Strategy (NT)
(fwding in case TBC's cc: doesn't make it to the list.) --- Forwarded message follows --- Date sent: Fri, 25 Jan 2002 11:12:11 -0800 (PST) 025 To: [EMAIL PROTECTED] Copies to: [EMAIL PROTECTED] I gave the right advice for the wrong reasons. My bad. I'll revise the next release of the paper and credit you -- thanks. Here's the word from Oracle Support: Doc ID: Note:139327.1 The Differences between Windows NT COPY and Oracle OCOPY When Doing Backups: == == When doing an online backup, should you use the Windows NT COPY command, or the Oracle OCOPY command? While doing online backups you should use OCOPY, or Oracle7 EBU, or Oracle8 (and later) RMAN. With the OCOPY command you could copy to a backup directory on the hard drive but cannot use OCOPY to copy a file to tape. The other option if you do not want to use ocopy to perform your backup as this does require a lot of disk space is EBU/RMAN that comes with Oracle. Depending on your Oracle version, the distribution includes a utility called EBU (Oracle7) or RMAN (Oracle8 and later) that can be used for online recovery as well. You will need to use a media management product to move the data from RMAN to tape. Legato Storage Manager is provided however there are other products that are supported to be used with this tool. To backup you will need to use the utility delivered by Oracle, the ocopy command. Utilities like the NT commands copy, xcopy CANNOT be used to back up. The Windows NT feature to be aware of is that NT Backup does not allow files in use to be copied, so you must use the OCOPY utility that Oracle provides to copy the open database files to another disk location. Since OCOPY cannot copy files directly to tape, you will then need to use NT Backup or copy or a similar utility to copy the files to tape, as required. OCOPY allows writing to continue while the backup is running. The NT COPY is a closed copy and the files may be marked either as fuzzy or corrupt. Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. REFERENCES [NOTE:41946.1] NT Online Backups Oracle Backup and Recovery Guide --- Eric D. Pierce [EMAIL PROTECTED] wrote: fyi: On 25 Jan 2002 at 1:05, Oracle RDBMS Community Forum [EMAIL PROTECTED] wrote: -- From: Igor Neyman [EMAIL PROTECTED] Date: Thu, 24 Jan 2002 16:14:25 -0500 Subject: Re: Backup Strategy I took a quick look at this paper, and found right away, that it's not very accurate, at least in one issue. i.e., it states : quote The Windows NT command COPY can be used to create a cold backup of a database. It cannot be used to make a hot backup. Attempting to perform a hot backup with COPY will usually result in an error message being generated as the COPY command fails - during a hot backup the database is running and thus the database files are locked by the Oracle database process, and COPY cannot work on a file that is so locked. /quote Wrong. NT 'COPY' has no problems copying 'opened' oracle db files. I'm using it in 'hot backup' scripts on many dozens systems, and it works fine. Don't know about the accuracy of the rest of the paper, didn't have time to read it all. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 7:35 AM Hi All, http://www.geocities.com/tbcox23/ Go here and get the paper. Regards Venkat -- = Thomas B. Cox Saepe in errore sed numquam in dubito [EMAIL PROTECTED] http://www.geocities.com/tbcox23/ The whole aim of practical politics is to keep the populace alarmed (and hence clamorous to be led to safety) by menacing it with an endless series of hobgoblins, all of them imaginary. --H.L. Mencken --- End of forwarded message --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
RE: Backup Strategy
Igor, I think your missing the point... You state... But, I'll take my chances, because as I said before, MetaLink Note:139327.1 didn't convince me at all that OCOPY is any better than regular NT Copy command. But, I assume that the opposite is also true? NT Copy isn't any better then OCOPY? My assumption is that they are functionality equivalent and perform about the same? But, the big difference is that OCOPY is the supported way to perform backups on NT while NT Copy is not... So why take a chance when it doesn't gain you any benefit? If NT Copy is significantly better then OCOPY for some reason then let me know... Then maybe you have a risk/reward argument that I can understand... If not, why gamble for zero gain? Tim -Original Message- Sent: Friday, January 25, 2002 2:07 PM To: Multiple recipients of list ORACLE-L Well, I know what you mean. But, I'll take my chances, because as I said before, MetaLink Note:139327.1 didn't convince me at all that OCOPY is any better than regular NT Copy command. Besides, I am keeping two generations of backup (the latest and the one prior to that), so if anything goes wrong with the latest backup image of the db file, I can always recover, using older backup and archived RedoLogs from both backups. Also, our customers wouldn't wait for Oracle support , and having two generations of backups, I can resolve potential issues much faster than ... Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 25, 2002 12:30 PM Igor, That sounds good, but what is Oracle Support going to say when you call them for support on a database recovery and you mention that you used NT COPY?? Your hosed, if there attitude is you should have used OCOPY so we can't help you. Chris -Original Message- Sent: Friday, January 25, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations. So, all it does is prohibiting some other program/process from opening the file. But Oracle, as I mentioned, has this file already opened, and it is perfectly capable of reading/writing this file. Of course, the image of the saved file will be fuzzy, and that's why when recovering from online backup we are
RE: RE: ORA-01555 Mystery (Help)
On Fri, 25 Jan 2002, Kathy Duret wrote: How about doing a set transaction to a large rollback before running this query if the analyze doesn't resolve the problem. That will have no effect. http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: ORA-01555 Mystery (Help)
Another fact, that should be mentioned, is that the table in question was built (loaded) two days ago. The nightly ETL processes for the warehouse are pretty substantial and the likelyhood of a block not getting cleaned/flushed out for a couple days should be nil. To summarize: 1. Tuesday Night: -truncate/load table 'A' (24 million rows) -Perform massive select from 'A', fails 5 hours later with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 2. Wednesday Night: -Perform massive select against 'A', fails 5 hours later with ORA-1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 3. Thursday night: -'lock table A in exclusive mode;' via session 123 -perform massive select against 'A', fails 5 hours later with ORA-1555 via session 124. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION -session 123 still has exclusive lock on table 'A' the following morning 4. Friday morning: -Walter is stumped but still trying to figure out a solution! :) -w --- [EMAIL PROTECTED] wrote: Delayed block cleanouts can still cause the ORA-1555, even after locking the table in exlusive mode. That's the purpose of the analyze, to force the block cleanouts. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- Baker, Barbara [EMAIL PROTECTED] wrote: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb -- From:Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ . -- 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). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET:
RE: How to use a package variable in pkg1 inside of package pkg2
I use this techinique when I build PL/SQL applications that span packages. I almost always create a global package with nothing but the specification filled with variables. Usually these variables are established upon entry to the app, and are applicable for the length of the run. As for guaranteeing the value to be what I expect, the fact that the package variables are session specific takes care of that nicely. In regards to the original post, The specification of a package is public, the body is private to the package itself. If you want something to be available outside of a package, it needs to be declared in the specification. Steve -Original Message- Thomas F Sent: Friday, January 25, 2002 9:01 AM To: Multiple recipients of list ORACLE-L I'm not sure why you want to do this. Why not have the package that you call return the value back to the calling package. I would not guarantee that the value you expect to be stored in the variable would exist when you think it will be there. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 25, 2002 10:31 AM To: Multiple recipients of list ORACLE-L pkg2 You have to declare this variable in package1 specification create or replace package pkg1 is end; / create or replace package pkg2 is procedure showvar; end; / create or replace package pkg2 body is procedure showvar begin dbms_output.put_line(pkg1.v_var); end; end; / exec pkg2.showvar in SQLPlus prompt should do that Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Rick_Cale@team health.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: How to use a package variable in pkg1 inside of package pkg2 om 2002.01.25 16:35 Please respond to ORACLE-L Hi All, I have a package pkg1 that has a variable var 1 declared Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier pkg1.var1 must be declared. What do I need to do to correct. Pkg1 compiles fine. Thanks Rick -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Steve McClure 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: ORA-01555 Mystery (Help)
I was going to write this myself, but this explanation fron MetaLink Note 45895.1 means I can just cut and paste, and Rachel won't get after me for typos. :) Jared Delayed block cleanout on old committed updates. An update operation completes and commits; the updated blocks are not touched again until a long-running query begins. Delayed Block Cleanout (DBC) has never been done on the blocks. This can result in a scenario which happens only under specific circumstances in VLDB, causing ORA-01555 errors when NO updates or inserts are being committed on the same blocks a query is retrieving. All of the following must be true for an ORA-01555 to occur in this case: (i) An update completes and commits and the blocks are not touched again until... (ii) A long query begins against the previously updated blocks. (iii) During the query, a considerable amount of DML takes place, though not on the previously updated blocks which the query is currently fetching. (iv) Under condition (iii) there is so much DML relative to available rollback space that the rollback segment used in the first update wraps around, probably several times. (v) Under condition (iv), the commit SCN of the first update is cycled out of the rollback segment. (vi) Under condition (iv) the lowest SCN in the rollback segment is pushed higher than the read consistent SCN in the query. (Note: The read consistent SCN is what the query uses to construct a read consistent view. Any block which has an SCN higher than this was obviously updated after the query started and requires rollback). The above conditions imply that when a query reaches a block that has been updated but not cleaned out, the query quickly learns that the update committed, and accordingly cleans out the block. But because the update SCN is no longer in the rollback segment (condition (v)), the query doesn't know WHEN the update committed. This is important because if the commit happened before the query began, the current value in the block can be used by the query; but if the commit happened after, the old value must be fetched from the rollback segment. Now, because the rollback segment wrapped in (iv), we know that the update SCN can't be higher than the lowest SCN in the rollback segment, which gives us a nice upper bound. If we only knew that the read consistent SCN was higher than this upper bound, we would know that the update committed before the query started. But we don't know this because of condition (vi), so we can't even accurately estimate the update SCN. Hence, we get an ORA-01555. Stephane Faroult [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 10:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: ORA-01555 Mystery (Help) I was almost ready to subscribe to the idea of delayed cleanout, but I cannot understand why really. The necessity for reading a block from the rollback segments comes from encountering during the course of the SELECT a block the SCN of which is higher than the SCN when the query started. I have of course no certainty about it, but it would be logical to expect the block's SCN to be properly set irrespectively of the clean-out being immediate or delayed. In other words, even if a SELECT physically writes blocks, it should not have anything to do with rollback segments anyway. I share Mladen's opinion, somebody must be economical with the truth somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you really sure that the code contains no 'just in case' commit ou rollback which would release the lock? And by the way, 5 hours look to me like an awfully long time, even for a 20 million row mega-select of death. [EMAIL PROTECTED] wrote: Precisely the point I was trying to make, when I put the question if it was a normal select, or if it was within a PL/SQL block? The myth is that snapshot too old happens only when some other transaction was in the process of performing an DML on a table, when you did a select on it. It can happen for other reasons too. Search on Metalink for Delayed block cleanouts and fetch across commits. Raj Baker, Barbara [EMAIL PROTECTED]@fatcity.com on -- 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
Re: performance problem with partitioned table query.
Strange, I'd expect, that dropping 12 partitions should speed up the query. Still partitioning helps only if column, used for partitioning, is specified as one your search criteria, or if you do full table scan in parallel, or in maintenance when you can quickly drop a partition instead of deleting rows. Otherwise, it can only slow down your retrievals. Why did you partition your table at all? And, why did you partition by this particular column poid_id0? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 3:20 PM Thank you Igor. But only 1 of the 14 partitions contains data during all the tests. Why should the extra 13 empty partitions slows down the query? I also tried to drop 12 of the empty partitions. Results didn't change. -Jessica -Original Message- Sent: Thursday, January 24, 2002 5:37 AM To: Multiple recipients of list ORACLE-L Jessica, It looks like your query has to deal with all 14 partitions, because the column 'poid_id0', which your table partitioned on, is not in 'where' clause. That's why Oracle can not eliminate other (not populated) 13 partitions. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 23, 2002 6:15 PM Oracle 8.1.7.0.0 table event_t range partitioned by column poid_id0. only 1 partition called p_1 out of the 14 contains data. A query on event_t became significantly slow after rows increase: select poid_DB, poid_ID0, poid_TYPE, poid_REV, start_t, end_t, sys_descr from event_t where event_t.end_t = :1 and event_t.end_t :2 and event_t.poid_TYPE like :3 and (event_t.account_obj_ID0 = :4 and event_t.account_obj_DB = 1 ) order by event_t.end_t desc Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (ORDER BY) 0PARTITION RANGE (ALL) PARTITION: START=1 STOP=14 0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'EVENT_T' PARTITION: START=1 STOP=14 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_EVENT__ACCTOBJ_END_T' (NON-UNIQUE) PARTITION: START=1 STOP=14 Index I_EVENT__ACCTOBJ_END_T was created on event_t ( account_obj_id0, end_t ) using LOCAL. Other 2 columns involved in the where clause have either only one distinct value or a few. So are not indexed. column account_obj_id0 has 1 million unique values in event_t and remain unchanged during the tests. when rows insert, average rows per account_obj_id0 value increase as well. Trace shows always the same execution plan but elapsed time increased enormously! I did 2 rounds of tests, every round I dropped and recreated event_t empty: In test round 1: 1.) inserted 1 million rows into event_t with same end_t value. Query returned: call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 23 0.02 0.09 0 0 0 0 Execute156 0.02 0.29 0 0 0 0 Fetch 156 0.14 1.09 8 2698 0 195 --- -- -- -- -- -- -- total 335 0.18 1.47 8 2698 0 195 2.) inserted ANOTHER 1.5 million rows into event_t with 10,000+ different end_t values. Query returned: Parse 36 0.00 0.04 0 0 0 0 Execute118 0.01 0.01 0 0 0 0 Fetch 118 0.61 86.71 1385 5045 0 587 --- -- -- -- -- -- -- total 272 0.62 86.76 1385 5045 0 587 In test round 2: 1.) inserted 1 million rows into event_t with same end_t value. Query returned as round1 step 1.) 2.) inserted ANOTHER 5 million rows into event_t with ANOTHER end_t value. Query returned: Parse 40 0.00 0.11 0 0 0 0 Execute139 0.02 0.12 0 0 0 0 Fetch 139 0.25 4.66303 2868 0 761 --- -- -- -- -- -- -- total 318 0.27 4.89303 2868 0 761 3.) inserted ANOTHER 2 million rows into event_t with 12,000+ different end_t values. Query returned: Parse 34 0.01 0.01 0 0 0 0 Execute 97 0.00 0.06 0 0 0 0 Fetch 97 0.58 89.93 1257 4260
Re: ORACLE-L Digest -- Volume 2002, Number 025
Thomas: Here is the person to credit: Igor Neyman [EMAIL PROTECTED] btw, any advice on libertarians to support in the election for california governor? thanks, ep On 25 Jan 2002 at 11:12, Thomas B. Cox [EMAIL PROTECTED] wrote: Date sent: Fri, 25 Jan 2002 11:12:11 -0800 (PST) Number 025 To: [EMAIL PROTECTED] Copies to: [EMAIL PROTECTED] I gave the right advice for the wrong reasons. My bad. I'll revise the next release of the paper and credit you -- thanks. Here's the word from Oracle Support: Doc ID: Note:139327.1 The Differences between Windows NT COPY and Oracle OCOPY When Doing Backups: ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Session_wait
Sergey, Have you considered adding an index to that queried column in table B? Many third-party vendors allow the DBA to add indexes even when they won't allow them to alter the code. Something to consider. Cherie Machler Oracle DBA Gelco Information Network Babich , Sergey To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] SBabich@hande cc: xmail.com Subject: RE: Session_wait Sent by: [EMAIL PROTECTED] om 01/25/02 12:31 PM Please respond to ORACLE-L Hi, Ross, This has been running for about 4 hours now. I got an SQL trace file, and looked at the execution plans. So here's the deal. There's an interesting join condition ...where A.col1=B.col1.. However, A has a few hundred distinct values in that column, none of them being NULL, and B, which has a few hundred thousand rows, has ALL NULLs in the corresponding column, and that column is not indexed, too. That's the query where it sits for a couple of hours. Guess what the optimizer is doing (8i)? I think internal effects are secondary in this scenario. It is the production (including the database) designed by the company named DELTEK, so nobody can change the code. Anyway, I reported my findings... Thank you very much for your help, it's always appreciated. Best, Sergey -Original Message- Sent: Friday, January 25, 2002 12:56 PM To:Multiple recipients of list ORACLE-L This is a busy little beaver...how long you say this runs? opened cursors cumulative 1072 session logical reads 77233609 db block gets 2642119 consistent gets74591490 physical reads 148822 db block changes3005410 consistent changes 141 no work - consistent read gets 72058049 table scans (short tables) 210918 table scans (long tables)36 table scan rows gotten798264962 table scan blocks gotten 71788386 table fetch by rowid1074164 leaf node splits 4018 execute count 74445 bytes sent via SQL*Net to client 59650 bytes received via SQL*Net from client84233 SQL*Net roundtrips to/from client 342 buffer is not pinned count 73513922 I see some updates (or inserts) and heavy reads...at first glance, though, you don't appear to be I/O throttled but likely have inefficiencies in buffer cache. Pin some small tables? Reexamine access paths for fts, even if on small tables (generally defined to be around 5% in blocks of buffer cache size?)and consider seeking out and destroying nested loops joins in favor of hash joins. There's more CPU, but less buffer cache splashing arounddon't
Re: Standby database question
On Fri, 25 Jan 2002, [EMAIL PROTECTED] wrote: One of the co-workers has a hot standby database. Logs are applied at some interval. He has to add a tablespace. What is necessay to make standby database aware of this? This is clearly documented in the Oracle8i Standby Database Concepts and Administration Manual. http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76995/standbys.htm#27363 In short, you just add the tablespace to the primary, wait for the standby to fail with ORA-01157, then issue the following command on the standby: SQL alter database create datafile 'foo' as 'bar'; Where foo is the location of the datafile on the primary, and bar is the location on the standby (usually the same). If you create a tablespace with several datafiles, you will have to issue this command a few times after recovering the standby and waiting for the ORA-01157 each time. Don't fall into the trap some people do where they think they have to copy the new file over to the standby every time they create a datafile. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Backup Strategy
Ditto. Tim, I think we should save all these emails, so when Igor posts a message asking for help on recovering a database that won't recover for some reason then... Sorry, Igor I couldn't resist. Chris -Original Message- Sent: Friday, January 25, 2002 2:39 PM To: Multiple recipients of list ORACLE-L Igor, I think your missing the point... You state... But, I'll take my chances, because as I said before, MetaLink Note:139327.1 didn't convince me at all that OCOPY is any better than regular NT Copy command. But, I assume that the opposite is also true? NT Copy isn't any better then OCOPY? My assumption is that they are functionality equivalent and perform about the same? But, the big difference is that OCOPY is the supported way to perform backups on NT while NT Copy is not... So why take a chance when it doesn't gain you any benefit? If NT Copy is significantly better then OCOPY for some reason then let me know... Then maybe you have a risk/reward argument that I can understand... If not, why gamble for zero gain? Tim -Original Message- Sent: Friday, January 25, 2002 2:07 PM To: Multiple recipients of list ORACLE-L Well, I know what you mean. But, I'll take my chances, because as I said before, MetaLink Note:139327.1 didn't convince me at all that OCOPY is any better than regular NT Copy command. Besides, I am keeping two generations of backup (the latest and the one prior to that), so if anything goes wrong with the latest backup image of the db file, I can always recover, using older backup and archived RedoLogs from both backups. Also, our customers wouldn't wait for Oracle support , and having two generations of backups, I can resolve potential issues much faster than ... Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 25, 2002 12:30 PM Igor, That sounds good, but what is Oracle Support going to say when you call them for support on a database recovery and you mention that you used NT COPY?? Your hosed, if there attitude is you should have used OCOPY so we can't help you. Chris -Original Message- Sent: Friday, January 25, 2002 11:26 AM To: Multiple recipients of list ORACLE-L Ok, I guess, I owe some explanation here, since I've got a lot of spanking (replies, some rather sarcastic) regarding this issue. My original note comes from my real life experience, so I'm still standing behind it. Sorry, it'll be kind of long, but if you are really interested... Couple years ago, when we were preparing first release of our product, I read of course about NT Copy versus Oracle Ocopy. Still I decided to test it, because not always I trust what I read, and I like to get proof myself. Testing of online (hot) backup/recovery scenario showed, that using NT Copy command in backup scripts is perfectly fine, when creating backup set of files on the disk. And there is no problem restoring from this backup. Now this disk backup set of files could be saved on tape, using NTBACKUP (that's the one, that really can not copy file, if it's opened by some other program. But that's not the case with prepared in advance disk backup). NT Copy has no problems copying files opened already by Oracle, and backup is consistent, as long of course as I am using alter tablespace name begin backup before copying relevant files and alter tablespace name end backup after finishing files copy. So, those scripts (using NT Copy) were put into production, and now have been used for more than two years on more than hundred installations/sites (the number keeps growing). From time to time, our field engineers are bringing back to me sets of online (can not use cold backup - our systems should run 24*7, I'm not saying they are, but we are trying to minimize downtime) backed up files (db files and archived RedoLog files), and I recover them with no problem (we need this, to test how the upgrade to next release of our product will run against real customers data). Now, about MetaLink Note:139327.1 It says: quote Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. /quote I don't think, it's very accurate, and here is why: When during online backup I run NT copy against db file, the file is already opened by Oracle (at moment, when I open the database). So, even if NT copy opens file without FILE_SHARE_READ and FILE_SHARE_WRITE flags, all it means is that Subsequent open operations on the object will fail (quote from NT docs). I want you to notice, it says Subsequent open operations not Subsequent write/read operations.
