RE: RMAN recovery
Title: Message Dear List, I have renamed a datafile in a production database ten days ago. I have no error to back up this database but I cannot duplicate/recover this database since. I am getting ora-19502 write error on this file. Please advise. Many thanks,
RE: RMAN recovery
Dennis, I have been admiring you from far. Thank you so much to reply to my email. The renamed data file has been tested and there is no corruption of any kind. The nightly physical and logical backups were successful completed with no error. However, I got an ora-19502 error when I tried to use these backups to restore/duplicate the database from a remote node. It might be asynch io problem. I am trying to set the 'fileperset to 1'. Do you have any other ideas? Thanks again, Anne -Original Message- Sent: Thursday, August 07, 2003 12:25 PM To: Multiple recipients of list ORACLE-L Anne What version of Oracle is this? Okay, you renamed a production database file 10 days ago. Since then, has Oracle been able to use this file? Can you export the table that is stored on this file without error? Have you examined your RMAN backup log to ensure this file is specifically listed as being backed up? Is it possible that the error you are receiving has nothing to do with the production database, but is entirely due to your backup or test database? In other words, maybe the test system has a bad drive? Another possibility, awhile back on this list several people reported that they had datafiles with errors, but RMAN did not detect these errors when it was backing them up. That is why I suggest exporting the table. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Dear List, I have renamed a datafile in a production database ten days ago. I have no error to back up this database but I cannot duplicate/recover this database since. I am getting ora-19502 write error on this file. Please advise. Many thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: URGENT: Trying to duplicate database from cold backup - auxi
Title: Message you need to : startup nomount pfile=xxx.ora -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 4:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: URGENT: Trying to duplicate database from cold backup - auxi Guys, When trying to duplicate database with this script I have my auxiliary setup as new database, target setup as old database and am using duplicate database command along with logfile command to create new logfiles. Get error: RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounte d
How to get user's IP address?
I want log changes when a user makes a change to a view, proc, func,
package, etc.
Basically, I have a trigger to capture the date, schema name and computer
name or IP address. However, I could not get the IP address or host name
out of Oracle.Can anyone help?
E.g. Query:
Select sys_context('USERENV','IP_ADDRESS') from v_$session;
Return NOTHING :(
Many thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Anne Yu
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: EXPORT FAST?
do this: exp usr/pass buffer=4096 file=xx log=xx owner=xx. It takes 2 hours for a 59GB's database. -Original Message- Sent: Tuesday, March 26, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Hi one of export for 35GB database is taking 12 hours.How to reduce this export time. Thx Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CPU usage for each oracle instance
Thanks Catherine,It might do the trick.I will create a script and
give it a try.
Thanks again.
-Original Message-
Sent: Saturday, March 23, 2002 3:48 AM
To: Multiple recipients of list ORACLE-L
Hi Anne,
Maybe you can try "/usr/ucb/ps -aux" in Unix to find the CPU usage of the
instance processes.
For example :
Thu Mar 21 15:45:00 SGT 2002
USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND
orahrms 7312 6.4 10.0421864406560 ?S 14:29:34 3:47 oracleTEST
(LOCAL=
Hope it helps.
Regds,
New Bee
-Original Message-
From: Anne Yu [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 21, 2002 12:49 AM
To: Multiple recipients of list ORACLE-L
Subject:CPU usage for each oracle instance
Hey list, In the system accounting report, I can get the
total CPU usage
for ORACLE. However, I have 7 instances on this box. How
can I get the
total CPU usage for each Oracle instance?Any ideas?
Million thanks,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anne Yu
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858)
538-5051
San Diego, California-- Public Internet access /
Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Anne Yu
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
CPU usage for each oracle instance
Hey list, In the system accounting report, I can get the total CPU usage for ORACLE. However, I have 7 instances on this box. How can I get the total CPU usage for each Oracle instance?Any ideas? Million thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
virus alert
FYI. Don't open an email with 'subject: hi'.Our mail servers were attacked by this virus. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: pass a value from a shell script to a store procedure
Thank you so much for your help. I was trying to pass 'yesterday's date' from unix thru a cron to a package. I took Guy's advise and created a procedure to call this pkg. It was easy and quick. Thank you again. -Original Message- Sent: Friday, November 09, 2001 11:51 AM To: Multiple recipients of list ORACLE-L I'm sorry, I misunderstood. Were you trying to get Oracle to tell Unix what yesterday was? #!/bin/ksh sqlplus << EOF system/incredibly_secret_password_but_this_is_probably_overkill_why_not_use_ scott_tiger select 'YESTERDAY="||sysdate-1||'"' from dual set pages 0 set sqlprompt "" spool date_def / spool off exit EOF . ./date_def echo "Yesterday was $YESTERDAY" -Original Message- Sent: Friday, November 09, 2001 11:31 AM To: Multiple recipients of list ORACLE-L Thank you so much for response to my email. I know we can do: Date= `date` but not `date -1`. -Original Message- Sent: Friday, November 09, 2001 11:14 AM To: Multiple recipients of list ORACLE-L So long as you embed your call to Oracle within your program, the program can pass variables to Oracle all day long. #!/bin/ksh DATE=`date - 1` DBA=`Anne Yu` sqlplus << EOF system/incredibly_secret_password select $DATE,$DBA from dual; exit EOF echo "Done!" -Original Message- Sent: Friday, November 09, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Hey List, I need to pass a value `date - 1` to a store procedure from a shell script. Really appreciate if anyone can help. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: pass a value from a shell script to a store procedure
Thank you so much for response to my email. I know we can do: Date= `date` but not `date -1`. -Original Message- Sent: Friday, November 09, 2001 11:14 AM To: Multiple recipients of list ORACLE-L So long as you embed your call to Oracle within your program, the program can pass variables to Oracle all day long. #!/bin/ksh DATE=`date - 1` DBA=`Anne Yu` sqlplus << EOF system/incredibly_secret_password select $DATE,$DBA from dual; exit EOF echo "Done!" -Original Message- Sent: Friday, November 09, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Hey List, I need to pass a value `date - 1` to a store procedure from a shell script. Really appreciate if anyone can help. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
pass a value from a shell script to a store procedure
Hey List, I need to pass a value `date - 1` to a store procedure from a shell script. Really appreciate if anyone can help. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: What's wrong with this query
8 nested loops 2008079 table access(full) 46193 submitted batch table access(by idx) 2008079 document index(range scan) 2008079 fk_d_batchnumber non-unique index(unique scan) 82 pk_uma_dn unique -- Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com> -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: What's wrong with this query
CCESS BY USER ROWID PLAN_TABLE 456500880TABLE ACCESS FULL PLAN_TABLE OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse7 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 4 Fetch38303 0.00 0.00 47 15524735 15562533 574511 --- -- -- -- -- -- -- total38318 0.00 0.00 47 15524735 15562533 574515 Misses in library cache during parse: 3 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse8 0.00 0.00 1 0 1 0 Execute 18 0.00 0.00 1 1 17 9 Fetch 32 0.00 0.00 10 51 0 27 --- -- -- -- -- -- -- total 58 0.00 0.00 12 52 18 36 Misses in library cache during parse: 2 9 user SQL statements in session. 7 internal SQL statements in session. 16 SQL statements in session. Trace file: texasprd_ora_24718.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 9 user SQL statements in trace file. 7 internal SQL statements in trace file. 16 SQL statements in trace file. 13 unique SQL statements in trace file. 38531 lines in trace file. -Original Message- Sent: 10 September 2001 20:15 To: Multiple recipients of list ORACLE-L Hey list, Can anyone tell me what's wrong with this query? many thanks, select /*+ INDEX(b)*/ distinct d.batch_number , d.document_number , d.entry_user_id , d.document_type_id , d.document_processed_date , b.batch_media_id, d.return_Method_Id from submitter_batch b , document d , ucc_master_amendment m where d.batch_number = b.batch_number and d.document_number = m.document_number and d.imaged = 0 and b.batch_media_id = 4 and d.document_status_id = 4 Submitter_batch - 97853 rows Document- 8043272 rows (fk_d_batchnumber index on batch_number) Ucc_master_adment -0 rows (pk_uma_dn index on document_number) Here is the explain plan: explain planexpected rows object name select statement 164662478 sort (unique) 164662478 nested loops 164662478 nested loops 2008079 table access(full) 46193 submitted batch table access(by idx) 2008079 document index(range scan) 2008079 fk_d_batchnumber non-unique index(unique scan) 82 pk_uma_dn unique -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: What's wrong with this query
Thanks Lisa,I got this explain plan from the OEM.What is the 'expected rows' ? Cardinality factor ? This query (below) should only return 1 or 0 row , not 164662478 rows. A consultant company created this application for us. I have over 37 queries like this one. Some expected rows are (8,298,736,866,720), some are(141E+1), some performs Merge join Cartesian. None of these queries take more then 1 second to run but they paused the database from time to time.Any ideas? Million thanks, By the way, I am readinng your email everyday, feel like you're one of my friends. Sent: Monday, September 10, 2001 2:38 PM To: Multiple recipients of list ORACLE-L Your hint is wrong. You have to tell it what index to use. example /*+ index (table_alias index_name) */ Remember hints, if they are wrong, will just be ignored. Your execution plan seems OK. It's returning an awful lot of rows ... Lisa Koivu Oracle Doggie Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- Sent: Monday, September 10, 2001 3:15 PM To: Multiple recipients of list ORACLE-L Hey list, Can anyone tell me what's wrong with this query? many thanks, select /*+ INDEX(b)*/ distinct d.batch_number , d.document_number , d.entry_user_id , d.document_type_id , d.document_processed_date , b.batch_media_id, d.return_Method_Id from submitter_batch b , document d , ucc_master_amendment m where d.batch_number = b.batch_number and d.document_number = m.document_number and d.imaged = 0 and b.batch_media_id = 4 and d.document_status_id = 4 Submitter_batch - 97853 rows Document- 8043272 rows (fk_d_batchnumber index on batch_number) Ucc_master_adment -0 rows (pk_uma_dn index on document_number) Here is the explain plan: explain planexpected rows object name select statement 164662478 sort (unique) 164662478 nested loops 164662478 nested loops 2008079 table access(full) 46193 submitted batch table access(by idx) 2008079 document index(range scan) 2008079 fk_d_batchnumber non-unique index(unique scan) 82 pk_uma_dn unique -- Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com> -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What's wrong with this query
Hey list, Can anyone tell me what's wrong with this query? many thanks, select /*+ INDEX(b)*/ distinct d.batch_number , d.document_number , d.entry_user_id , d.document_type_id , d.document_processed_date , b.batch_media_id, d.return_Method_Id from submitter_batch b , document d , ucc_master_amendment m where d.batch_number = b.batch_number and d.document_number = m.document_number and d.imaged = 0 and b.batch_media_id = 4 and d.document_status_id = 4 Submitter_batch - 97853 rows Document- 8043272 rows (fk_d_batchnumber index on batch_number) Ucc_master_adment -0 rows (pk_uma_dn index on document_number) Here is the explain plan: explain planexpected rows object name select statement 164662478 sort (unique) 164662478 nested loops 164662478 nested loops 2008079 table access(full) 46193 submitted batch table access(by idx) 2008079 document index(range scan) 2008079 fk_d_batchnumber non-unique index(unique scan) 82 pk_uma_dn unique -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:
Hey List , I am using the below script updating 4.3 millions rows to a
table. This table has 1500 bytes per record but actually the avgrow is 156
bytes. I did a test on NT, with same table (no index). It takes 54 minutes
to load. However, the same load, same table (no index), it takes 16 hours
to load. There is no chained_rows. What will make Oracle behavior this
way? Any idears?
Thanks,
**
create or replace PROCEDURE SOS_PROC_PARTY_NUMBER IS
CURSOR PARTYNUM IS
SELECT ROWID,ORIGINAL_FILING_NUMBER
FROM test_ufp
order by ORIGINAL_filing_number;
FNUM NUMBER;
NNUM NUMBER DEFAULT 0;
RID VARCHAR2(20);
COUNTER NUMBER DEFAULT 0;
dcounter number default 0;
BEGIN
dbms_output.put_line('Start of party Number '||to_char(sysdate,'dd-mon-
hh:m
i'));
OPEN PARTYNUM;
--commit;
--set transaction use rollback segment rb_temp1;
--generate party number sequentially for each filing number, but starting at
one
for each filing number
LOOP
FETCH PARTYNUM INTO RID,FNUM;
EXIT WHEN PARTYNUM%NOTFOUND;
IF NNUM = FNUM THEN
COUNTER := COUNTER + 1;
ELSE
COUNTER := 1;
END IF;
UPDATE test_ufp SET PARTY_NUMBER = COUNTER WHERE ROWID=RID;
NNUM := FNUM;
dcounter := dcounter + 1;
if dcounter = 1 then
commit;
set transaction use rollback segment rb_temp2;
dcounter := 0;
end if;
END LOOP;
COMMIT;
CLOSE PARTYNUM;
dbms_output.put_line('End of Party Number '||to_char(sysdate,'dd-mon-
hh:mi'
));
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anne Yu
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Why I can not create temporary tablespace?
David,Can we use Locally managed tablespace for TEMP? Thanks, -Original Message- Sent: Saturday, May 19, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Robert, Check your syntax. CREATE TEMPORARY TABLESPACE c2tmp TEMPFILE '/oracle/oradata/c2datatmp.dat' SIZE 50M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; Regards, David A. Barbour Robert Chen wrote: > > hi, I connect as sys/change_on_install and execute this: > > create TEMPORARY TABLESPACE c2tmp TEMPFILE '/oracle/oradata/c2datatmp.dat' > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M; > > Error at line 1: > ORA-01119: Error in creating database file '/oracle/oradata/c2datatmp.dat' > ORA-27037: unable to obtain file status > SVR4 Error: 2: No such file or directory > Additional information: 3 > > Please tell me why? I even change the directory to 777(anyone can read and > write) still gotta this error. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robert Chen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: David A. Barbour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 the Virginia Beach VA Area..
It works. Thank you very very much. -Original Message- Sent: Thursday, March 01, 2001 11:06 AM To: Multiple recipients of list ORACLE-L Issue Upgrade Catalog command in your recovery catalog database. -Original Message- Sent: Thursday, March 01, 2001 7:07 AM To: Multiple recipients of list ORACLE-L Hi List Friends, I have server A: Solaris 5.8, RDBMS 8.1.7.0.0 and server B: Solaris 5.8 & the RDBMS was upgraded from 8.0.5 to 8.1.7. RDBMS 8.1.7 is my recovery manager target database and the other is my catalog database. When I try to register this 8.1.7 target database to this upgraded 8.1.7 catalog database, I am getting: RMAN> connect target sys/password@target_db RMAN> connected to target database: CATALOG RMAN> connected to recovery catalog database RMAN-06186: PL/SQL package SYS.DBMS_RCVMAN version 08.00.05 in TARGET database i s too old RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-0058: error encountered while parsing input commands RMAN-01006: error signalled during parse What can I do to correct this problem, IF ANY? many thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 the Virginia Beach VA Area..
Hi List Friends, I have server A: Solaris 5.8, RDBMS 8.1.7.0.0 and server B: Solaris 5.8 & the RDBMS was upgraded from 8.0.5 to 8.1.7. RDBMS 8.1.7 is my recovery manager target database and the other is my catalog database. When I try to register this 8.1.7 target database to this upgraded 8.1.7 catalog database, I am getting: RMAN> connect target sys/password@target_db RMAN> connected to target database: CATALOG RMAN> connected to recovery catalog database RMAN-06186: PL/SQL package SYS.DBMS_RCVMAN version 08.00.05 in TARGET database i s too old RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-0058: error encountered while parsing input commands RMAN-01006: error signalled during parse What can I do to correct this problem, IF ANY? many thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent! Change Character Set from WE8IS088591 to UTF8
To change Character Set, You need to recreate the database. Correct me if I'm Wrong. -Original Message- Sent: Wednesday, February 14, 2001 4:31 AM To: Multiple recipients of list ORACLE-L i once hear someone said that: update sys.props$ set value='utf8' where name ='NLS_characterset'; and it seems it do work,but not konw whether it is legal,and no experience in production database. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, February 14, 2001 7:40 AM > I have a 8.1.6.0.0 database. > > What is the best way to change Character Set from WE8ISO8859P1 to UTF8 > > from: > NLS_CHARACTERSET WE8ISO8859P1 > > To: > NLS_CHARACTERSET UTF8 > > Thanks > > Larry > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Larry Taylor > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >Ws±ë zØ^¡÷âr&¥9,BÅm¶Yÿà (§Ú©Ê&ëa¢·!jSbz <á¹ÈZ¢(tm).ÛçzÑZ´(È×ÂSäIêï?ǬóY9ßÎtçQ@_Î|ç9ÓRjpâz jXY¢¹âhû>-'z׫ëZqǬ³óSX§EUR¸¬¶ÄèDCTL¨º» *÷ë¢kaSÉsSX§'X¬¶Ç§u©Ä1¨¥(tm)ë,j ¸¬´k«¹ör+rr?§¢×"\"²-¥-)à¡òâ²Ñ®®æ§v)í...éz²Æ xfb)Ü-ç^jX§yÊ'µ¨§Sx5%9, Bè®Ø^©z¡ùsSX§'X¬·*.Á©í¶?Þ騽ç_®?~¢ésÉ©l¢Ç§vØ^ BÏr?¦jw_¢º-...êâú+(tm)«b¢yb 'ë.nÇ+?¸§ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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-00200 (control file creation error)
You need to delete these old control files. -Original Message- Sent: Friday, February 02, 2001 8:51 AM To: Multiple recipients of list ORACLE-L I am trying to create a new database OPS database/instance. While running the script using SVRMGRL get the following messages.. ORA-01501: CREATE DATABASE failed ORA-00200: controlfile could not be created ORA-00202: controlfile: '/dev/disk/dsk19a' ORA-27038: skgfrcre: file exists Any ideas, the sys ops say everything is OK. Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anne Yu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
