RE: Transferring data from one table to another
Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject:RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: E business Suite 11i - for Apps DBA's
AFAIK it is only available if you have bought the Suite. If you have purchased it, then the Hard Copy of the documentation is provided to you. In that case you can also view the documentation at etrm.oracle.com, there you will need the metalink id. HTH Naveen -Original Message- Sent: Monday, August 12, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hello Application DBA's I'd like to know where I can get/buy E-business Suite 11i documentation with the ERD and data dictionary of the tables. Thank you so much. -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Trial License
John, I have quoted OTN(Oracle Tech Net). That's the same. If you take a further look down at few other things, you'll see the text I quoted. Two conditions together limit the usage only for development and prototyping and only for period of 30 days. Btw, the download site of www.oracle.com is technet.oracle.com (otn.oracle.com). Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 09, 2002 11:38 PM When you said OTN, I thought you meant Oracle Technet. If you look at the this site: http://technet.oracle.com/software/htdocs/devlic.html?/software/products/ oracle9i/htdocs/winsoft.html You will find the licence asks you to agree you're not someone the US doesn't like and then to accept the Technet licence agreement. Which includes the following: License Rights We grant you a nonexclusive, nontransferable limited license to use the programs only for purposes of developing and prototyping your applications, and not for any other purpose. If you use the applications you develop under this license for any internal data processing or for any commercial or production purposes, or you want to use the programs for any purpose other than as permitted under this agreement, you must contact us, or an Oracle reseller, to obtain the appropriate license. We may audit your use of the programs. Program documentation is either shipped with the programs, or documentation may accessed online at http://otn.oracle.com/docs. Ownership and Restrictions We retain all ownership and intellectual property rights in the programs. The programs may be installed on one computer only, and used by one person in the operating environment identified by us. You may make one copy of the programs for backup purposes. You may not: ·use the programs for your own internal data processing or for any commercial or production purposes, or use the programs for any purpose except the development and prototyping of your applications; ·use the applications you develop with the programs for any internal data processing or commercial or production purposes without securing an appropriate license from us; ·remove or modify any program markings or any notice of our proprietary rights; ·make the programs available in any manner to any third party; ·use the programs to provide third party training; ·assign this agreement or give or transfer the programs or an interest in them to another individual or entity; ·cause or permit reverse engineering or decompilation of the programs; ·disclose results of any program benchmark tests without our prior consent; or, ·use any Oracle name, trademark or logo. And a few other things... So basically you can use it for development, free of charge, as long as you don't put applications you develop into production use at your site or anyone elses. I think the terms you were quoting refer to Trial licences from www.oracle.com's shop, not OTN. Cheers, John Thomas In message [EMAIL PROTECTED], Alexandre Gorbatchev [EMAIL PROTECTED] writes Here is the quotes: . Trial Programs Included With Orders We may include additional programs with an order which may be used for trial purposes only. You will have 30 days from the delivery date to evaluate these programs. Any use of these programs after the 30 day trial period requires you to obtain the applicable license. Programs licensed for trial purposes are provided as is and we do not provide technical support or any warranties for these programs. . Doesn't that mean that any use (limited to development and prototyping in other paragraph above this one) is for 30 days only? Or this limitation is only for additional programs? What does additional programs mean? Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 08, 2002 9:04 PM Unless I misread something, or the licence has changed, a Technet download licence is an indefinite development licence for use on one computer. It is only when your app goes into production that you must buy a full licence. Also check out Technet Tracks. For $199 Oracle send you a year's worth of releases of the DB, 9iAS and the full development suite (Forms, Designer etc) and give you limited Metalink access for a year. Again under the Technet development licence. See https://www.oracle.com/jsp/otntt/index.jsp Cheers, John In message [EMAIL PROTECTED], Alexandre Gorbatchev [EMAIL PROTECTED] writes Ken, I guess you are still supposed to buy it to continue using it at home after 30 days trial. Larry trusts you. :) Alexandre - Original Message - From: KENNETH JANUSZ To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 06, 2002 6:38 PM Subject: Re: Oracle Trial License Mark: That's what I thought, but I wanted to confirm
Re: /etc/system on Sun Solaris 8 for 8i and 9i
When I start the 9i2 instance, the 8i3 instance could only establish 2 max. Unfortunetly, I forgot to log the error message. Yesterday, when I checked the error message on Oracle Doc, that sayed to be change the /etc/system. Please advice.. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 12, 2002 11:48 AM How do you know it ran out of resource? What's the error message and when does this appear? During startup, during heavy use or when it's just idle? ltiu On Sunday 11 August 2002 20:08, you wrote: Dear All, I've Sun machine that run Oracle 8.1.7. Recently I installed new oracle 9i2 64-bit on the same machine. But unfortunetly the old instance (8.1.7) is run out resource. I must set the /etc/system to higher value. But it is still the same. Could you please someone give me advice about it, and what is the impact for my Sun Machine if I increase the /etc/system value?? Below is my /etc/system. thanks Ahmadsyah.Alghozi.Nugroho ps: I'm feel sorry for my english.. :( === /etc/system === set hme:hme_adv_autoneg_cap=0 set hme:hme_adv_100fdx_cap=1 set hme:hme_adv_100hdx_cap=0 set hme:hme_adv_10hdx_cap=0 set hme:hme_adv_10hdx_cap=0 set ge:ge_adv_1000autoneg_cap=0 set ge:ge_adv_1000fdx_cap=1 set ge:ge_adv_1000hdx_cap=0 forceload: drv/vxdmp forceload: drv/vxio forceload: drv/vxspec forceload: sys/semsys set semsys:seminfo_semmsl = 500 set semsys:seminfo_semmap = 10 set semsys:seminfo_semmni = 1200 set semsys:seminfo_semmns = 5000 set semsys:seminfo_semmnu = 30 set semsys:seminfo_semopm = 100 set semsys:seminfo_semume = 10 set semsys:seminfo_semusz = 96 set semsys:seminfo_semvmx = 32767 set semsys:seminfo_semaem = 16384 forceload: sys/shmsys set shmsys:shminfo_shmmax = 4294967295 set shmsys:shminfo_shmmni = 800 set shmsys:shminfo_shmmin = 1 set shmsys:shminfo_shmseg = 400 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: TO_CHAR got one space in front
check out the 'fm' format clause ie to_char(x,'fm'); hth connor --- shuan [EMAIL PROTECTED] wrote: Hi all, Have you guys ever try this before: 1* select to_char(1.6,'0.') from dual SQL / TO_CHAR(1.6 --- 1.6000 Notice that got one space in front of 1.6000? I'm using Oracle 8.0.5 in Linux. Thanks in advance. = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
Hi Aleem, well cant u pass the table name dynamically to the procedure for all the tables in the schema within an outer loop... n e ways iam not sure abt the database setting but logically speaking there shld b some property setting for some autocommit... rgds, Ams. -Original Message- Sent: Monday, August 12, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject:RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 check the existence of a dir on remote server
__ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 tocheck the dir existence on a remote server
Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo existing else echo not existing fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
SET ORACLE-L NOMAIL __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 check the existence of a dir on remote server
UNIX or Windows?
What protocol to connect?
etc.
J{
On Monday 12 August 2002 12:48, you wrote:
__
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Pruner Jan
[EMAIL PROTECTED]
http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: /etc/system on Sun Solaris 8 for 8i and 9i
try reducing the number of processes in the 9i init.ora to see if you can start the database. if the error message is about semaphore or semaphore sets you will need to increase the semmni, semmns and semmnu parameters. Then you need to reboot the Sub box. The OS-specific documentation will explain what the semaphores are used for. --- [EMAIL PROTECTED] wrote: When I start the 9i2 instance, the 8i3 instance could only establish 2 max. Unfortunetly, I forgot to log the error message. Yesterday, when I checked the error message on Oracle Doc, that sayed to be change the /etc/system. Please advice.. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 12, 2002 11:48 AM How do you know it ran out of resource? What's the error message and when does this appear? During startup, during heavy use or when it's just idle? ltiu On Sunday 11 August 2002 20:08, you wrote: Dear All, I've Sun machine that run Oracle 8.1.7. Recently I installed new oracle 9i2 64-bit on the same machine. But unfortunetly the old instance (8.1.7) is run out resource. I must set the /etc/system to higher value. But it is still the same. Could you please someone give me advice about it, and what is the impact for my Sun Machine if I increase the /etc/system value?? Below is my /etc/system. thanks Ahmadsyah.Alghozi.Nugroho ps: I'm feel sorry for my english.. :( === /etc/system === set hme:hme_adv_autoneg_cap=0 set hme:hme_adv_100fdx_cap=1 set hme:hme_adv_100hdx_cap=0 set hme:hme_adv_10hdx_cap=0 set hme:hme_adv_10hdx_cap=0 set ge:ge_adv_1000autoneg_cap=0 set ge:ge_adv_1000fdx_cap=1 set ge:ge_adv_1000hdx_cap=0 forceload: drv/vxdmp forceload: drv/vxio forceload: drv/vxspec forceload: sys/semsys set semsys:seminfo_semmsl = 500 set semsys:seminfo_semmap = 10 set semsys:seminfo_semmni = 1200 set semsys:seminfo_semmns = 5000 set semsys:seminfo_semmnu = 30 set semsys:seminfo_semopm = 100 set semsys:seminfo_semume = 10 set semsys:seminfo_semusz = 96 set semsys:seminfo_semvmx = 32767 set semsys:seminfo_semaem = 16384 forceload: sys/shmsys set shmsys:shminfo_shmmax = 4294967295 set shmsys:shminfo_shmmni = 800 set shmsys:shminfo_shmmin = 1 set shmsys:shminfo_shmseg = 400 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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: How tocheck the dir existence on a remote server
Try this rsh server_name [[ -d /tmp ]] echo Is there || echo Is not there Works for me HTH Lee -Original Message- Sent: 12 August 2002 11:53 To: Multiple recipients of list ORACLE-L Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo existing else echo not existing fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bitmap Header in a Uniform LMT?
I have been looking at LMT recently I created a 10001M datafile at 8K block size for a 64K locally managed extents Noticed from dba_free_space that the largest contiguous free space was 3968M Dumped the file header and got the same results as Paul / Jeremiah but I did note that for a 8k block size there is a file header block, and then a space header block followed by 6 lmt bitmap blocks before the data blocks start I assume the figure of 3968M is the maximum no of bits that can be stored in single block and therefore the datafile for the sizes listed above could be a maximum of 3968M * 6? Am I on the correct track? John -Original Message- Sent: 09 August 2002 21:54 To: Multiple recipients of list ORACLE-L Raj - My apologies for being late with a reply. Your latest message prompted me to recall that Jeremiah Wilton investigated this (or a similar aspect) back in April. I went into Google and typed LMT bitmap headers (without the quotes), and it retrieved the discussion. I have inserted it below for your review in case you were not aware of that discussion. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g38Lpc327439 for [EMAIL PROTECTED]; Mon, 8 Apr 2002 17:51:38 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA08313; Mon, 8 Apr 2002 14:58:27 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0043EF61; Mon, 08 Apr 2002 13:03:20 -0800 Message-ID: [EMAIL PROTECTED] Date: Mon, 08 Apr 2002 13:03:20 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Following on from my previous note: Jeremiah, From your bitmap control, You have FF occurring 3 times followed by 3F which is 255, 255, 255, 63 which is 0011 So, least signficant bit first, 1100 which is used, used, ... (30 times) , free, free This corresponds with the first: 30 (the bit before the first free bit) Paul -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: '[EMAIL PROTECTED]' From the 'Data Management and Storage Internal notes, Bitmapped Tablespace File Structure A new bitmapped tablespace file has the following structure: File Header 1 block Bitmapped File Space Header 1 block Head portion of of Bitmap BlocksN blocks Useful file blocks U units (A unit is a number of blocks) Tail portion of Bitmap Blocks M blocks If a Unit = B blocks, then the total file size = 1 + 1 + N + U*B + M Bitmapped File Space Header .. (lots to type, I can if you really need it) Bitmap blocks have 2 parts : Bitmap control structure Vector Dump The fields in the bitmap control structure are: RelFNo: Relative file number to which the bitmap belongs BeginBlock: Which block number does the first bit represent Flag: Zero for permanent files, one for temp files First: Where to start looking for the free space (bit before first free bit) Free: Number of free slots (bits) in the bitmap (not the file) To read the bitmap, take each two-byte pair, least significant bit first. If there are not eight bits, pad to eight bits with zeroes. Hence 0x0F = 15 = . When written least significant bit first, the bitmap looks like this -- used, used, used, used, free, free, free, free Scanning for the first free extent will start at the 4th bit. HTH Paul -Original Message- Sent: Monday, April 08, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3, Tail: 524292,
interMedia Text
Dear List, We have :- Solaris SunOs 5.8, Oracle 8.1.7 I have created three databases using the dbassist tool and I included, as one of the installation options, InterMedia. Having read the installation guides (including post installation for Oracle InterMedia) and the Oracle interMedia Text - 8.1.5 Overview Post Installation Setup I have the following questions :- 1) Having amended the listener.ora and tnsnames.ora files I'm not sure if I have done this correctly because the Post Installation Setup refers to adding ONE entry (for extproc_connection_data) in the tnsnames.ora file for A database. But I have three, so I have entered this SID specific info. three times, is this correct ? 2) Having amended the listener.ora file to contain three extra entries is this correct ? 3) I remember one of the NET8 classes I took, where the instructor insisted that we amend these files with great caution and in particular to the layout of entries. e.g. the number of spaces etc. etc. Well the new entries I added don't conform exactly to the already existing entries, here is a snip of my listener.ora file SID_LIST_LISTENER (SID_LIST (SID_DESC (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (PROGRAM = extproc) ) (SID_DESC (GLOBAL_DBNAME = RATREP) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = RATREP) ) (SID_DESC (GLOBAL_DBNAME = CQPROD01) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = CQPROD01) ) (SID_DESC (GLOBAL_DBNAME = CQTEST01) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = CQTEST01) ) (SID_DESC = (SID_NAME = ep_agt1) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = ep_agt2) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = ep_agt3) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) ) You can see that the SID_DESC entries layout are slightly different that those earlier, is this OK? Thanks in advance, and sorry in advance if any of the above questions are dumb, but I'm a bit stuck on this! best regards, Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MCUK INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Physical Database Layout
Hi Manav, Sorry for a late reply. Actually I had left it for gurus to reply, and moreover I was enjoying the weekends! Since you are working on enterprise application, surely the organization which will use u application will have a DBA. So you only need to be concerned about the table structure, because all other things(relatd to physical layout of the DB) can be changed during installation time. Still just for ur knowledge a few pointers(a bit simplified). Again not an exhaustive list, just a few things which immediately come to my mind: * Always use Locally Managed Tablespaces(saves a lot of headache) * Try to spread ur data evenly to different tablespaces, so that those tablespaces can be on different disks * Keep index and data seperate * DB block size depends upon the data the queries are supposed to fetch. Larger the data more the DB Block size, lesser the data, smaller the block size * Don't worry about things like rollback segments because they can be added later by the production DBA * If your DB is going to be used more for insert/updates rather than for queries, use less indexes as indexes have an overhead while insert/update/delete * Write queries carefully, Use bind variables as much as possible. Read a bit about SQL tuning to know more abt the art of writing queries HTH Naveen -Original Message- Sent: Saturday, August 10, 2002 10:04 PM To: Multiple recipients of list ORACLE-L Hi Naveen, Thanks for taking the time out to reply. Yes I agree most of the programmers do not bother even to optimize the queries they write, but things get a tad complicated when you are responsible for an enterprise application and you are concerned with each aspect of the system. I understand optimization is an iterative process, but unless the physical layout of the DB itself is not right, the iterative cycle of optimization will not result in too much of an improvement. What i am looking for is a set of directives that need to be considered during the physical design, such as whether to use local tablespaces, the number of rollback segments, db block size, etc.. Thanks, Manav. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 09, 2002 10:58 AM Hi Manav, I'm not a guru, but a programmer turned DBA. I appreciate your concern for optimum DB design, because most of the programmers don't even care for the optimization of SQL Queries they write. Oracle gives you a lot of control over the way you want to optimize your DB, but that also means that you have to learn a lot of things(a lot!). The gurus in this list are so experienced that they know the Paramemters and other things like people like you and me remember our friends' phone numbers. It all comes with experience. As for you, I'll suggest that if you can't get a DBA and want to do a few things yourself(which a DBA should be doing), you read some material on Oracle Architecture and basics. As a programmer, all you can do is 1. Have a good normalized design 2. Write your SQL queries properly Apart from that, most of the optimization is a DBAs job and you should stick to whatever programming language you work on and learn more about that. There is lots of tuning material available on the net also. Just read it and skip whatever you don't understand(you'll not understand most of them, and so do I ) Good Luck, Naveen -Original Message- Sent: Friday, August 09, 2002 6:53 PM To: Multiple recipients of list ORACLE-L Hi, Unlike the gurus on the list, I'm just a programmer who happens to ensure the DB structure is correct/valid, should hold its ground if an external (read customer's) DBA goes through it with a microscope. Its not always possible to have a full time DBA suggesting the layout, and moreover, most of the programmers are expected to 'know' RBDMS anyway! I was hoping if the gurus here can provide me a list of parameters to be taken into consideration (while doing the database design) to ensure that the phsycial database layout itself is optimized for performance. Or if anyone can just point me in the right direction, it'll help me a lot. Btw, Oracle has more than 200 initialization parameters, all affecting its working in some or the other way. Do the gurus keep all of them at their finger-tips? TIA, Manav. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manavendra Gupta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include 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: Houston, do I have a problem?
Cary, Your last paragraph is *GREAT* advice. Every DBA should take it out, print it large, and paste it to the top of their screen. Focusing first on the highest priority item in the business model will win you the biggest supporters in the organization. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, August 12, 2002 2:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the wait interface as they are from v$sysanything-else.) Don't lose hope if you look at Kirti's note and wonder, so what's the point? You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the secret constants (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than normal systems at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is heinously longer than normal. 2. If you know the secret list of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it shouldn't be doing. Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming heinously longer-than-normal elapsed times, or even if it is called heinously too often, does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the right kinds of service in the right proportions? How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a normal, healthy profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's worst performance problems and then find out that their work really didn't make a noticeable end-user impact. (I'm confident that Kirti won't end up in this trap, but that's because I trust him to exercise intuition and experience far beyond the scope of what can be learned from his v$system_event data.) The wait interface is an important tool, because it finally (well, since over ten years ago) allows us to see where a program spends its time. But to use that tool to see how a whole system has spent its time since instance startup has the same limitations as any other method that relies upon system-wide aggregated data. So, what should you look at to avoid performance improvement project ambiguities? Session-level data. Which session? As I mentioned last week, I believe the analyst should focus first upon sessions whose performance improvement would most significantly improve the business. That, in my opinion, is The Big Secret. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Kirti Sent: Sunday, August 11, 2002 5:43 PM To: Multiple recipients of list
RE: Lock table table_name in exclusive mode - Performance gain?
case, the app is very bad and we (me and another member on the list) were tasked to pull it out of the ditch (my 42nd day straight on this on back to back to back, etc 100/hr weeks, I want to hear the violins!). If we were to have written it, there wouldn't be the need for some of these large updates, violins only if you are NOT getting paid an hourly consulting fee. Otherwise, yea, you are working hard but getting paid for your time :) and yeah, knowing you and that other member of the list, it would have been done properly the first time as for that methodology on the update -- VERY slick! I like it, will keep this note to remind me when it's my turn to have to deal with someone else's code ... --- Larry Elkins [EMAIL PROTECTED] wrote: All my comments are with regards to 8i. Might do things differently with 9i ;-) Familiar with the technique for doing large deletes? For example, you want to delete 40 million rows from a 100 million row table. It can often times be much more effective to do a CTAS (or insert append into an existing object) in parallel excluding the rows you want to delete. You can then truncate the source and throw the rows back in, or drop and rename (taking care of priv's and possible synonyms), or exchange partition, whatever. The same technique can be applied to updates. Numerous examples where this approach has been used with great success, I'll use one. In this particular example, we have a partitioned table, 162 million rows in a partition, and need to update 30 million rows in that partition with values from another table (bad, bad app, if designed correctly such a step wouldn't even be needed). We also have a holding table with the same structure. We'll do an insert append in parallel (implying append) outer joining to the table providing the values (using HJ). Use a decode to know whether or not to retain the value or if it should be updated if you found a matching row. Then, simply do an exchange partition no validate swapping your hold table with the partition that was to be updated. With the no validate it's basically a dictionary operation not even having to verify the values. Boom, there you go, a big update done very quickly. And then truncate the hold table (paying attention to next extent issues after parallel insert and ways around them). In another recent example, we had to update a column with a constant for all rows in a 109 million row table (don't ask). This type insert and swap approach allowed it to be done in 10 to 12 minutes. So you might be able to apply similar techniques to your situation. In our case, the app is very bad and we (me and another member on the list) were tasked to pull it out of the ditch (my 42nd day straight on this on back to back to back, etc 100/hr weeks, I want to hear the violins!). If we were to have written it, there wouldn't be the need for some of these large updates, etc. But we don't have the luxury of completely rewriting the whole thing right now, so we apply the update / delete becomes an insert and exchange partition approach to selected areas experiencing severe performance issues. And it works well. We had one process (cursor based of course in the coder's infinite wisdom updating 1 row at a time and committing every 1000 rows) that projected, by the rate of rows updated, to take 52.4 years to complete ;-). Now it takes 15 minutes. Just an idea that might be applicable in your situation. It's a little different, but not really much different than the CTAS (or insert append) approach that folks use for mass deletes. It's the same concept just applied to updates. And you can extend it to inserts / deletes. Don't know you situation, but maybe you do it in one statement. Seriously, I took a few thousand lines package doing multiple updates/deletes down to a single insert statement outer joining some tables and an exchange partition. Oh well, I'm delirious from a lack of sleep so the above might be a bit rambling. But I hope you get the idea. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Saturday, August 10, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: Lock table table_name in exclusive mode - Performance gain? Anyone do any bench marking, know of any papers, or using lock table table_name in exclusive mode to get a performance boost. I'm trying to figure out how to do 90,000,000 operations (add/change/delete) on the same table/partitions in a 4 hour period, and it looks like lighting will have to strike twice in the same place for it to happen. Any other suggestions on how to cut down on the cost of a transaction. I know about dropping indexes, using hash keys, partitions, unrecoverable, multi-process/threading, sql loader direct,
CLOB columns
Hi Guys, I am using a CLOB column in my database. Is there any way to fetch and see the entire content of the CLOB field? For (eg) If I have stored my resume in the CLOB colum (in a text/HTML format) then is there any way to display the entire content in the database or in some other editor? I tried DBMS_LOB.READ(locator_var,amount_var,offset_var,output_var) and DBMS_LOB.OPEN(locator_var,1); K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: karthikeyan S INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: E business Suite 11i - for Apps DBA's
Maria, Unless things really changed with 11i, Oracle has never HAD an ERD for the Apps. That's one of the reasons customization is such an incredible chore. As for the data dictionary, the Technical Reference Manuals can be ordered from Oracle. Be prepared, they are hideously expensive. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Monday, August 12, 2002 2:48 AM To: Multiple recipients of list ORACLE-L Hello Application DBA's I'd like to know where I can get/buy E-business Suite 11i documentation with the ERD and data dictionary of the tables. Thank you so much. -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Oracle versions are supported on MS2000?
Can anyone tell me what Oracle versions are supported on MS2000? Ron Smith -- 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: Transferring data from one table to another
Abdul In terms of committing every so many records, investigate the SQL*Net COPY command if you are not familiar with it. For transferring this many records, you may also want to investigate CREATE TABLE AS SELECT . . . . NOLOGGING Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, August 11, 2002 11:23 PM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Unix Solaris forum.
...it's www.sunmanagers.org (rather than a forum for well-lit livestock feeders :-) )... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 11, 2002 10:18 PM www.sunmangers.org will do it for you Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Chuan Zhang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12-08-2002 01:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:Unix solaris forum. Hi, DBAs, Could anyone recommend a good unix solaris discussion/news group for me? Thanks, Chuan -- 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Statspack archiving
Just a quick note to thank everyone for the contributions to this and the
ODBC stuff I posted recently.
Regards
Lee
-Original Message-
Sent: 10 August 2002 20:53
To: Multiple recipients of list ORACLE-L
The original question on this thread was for an automated purge for
STATSPACK. I wrote this stored procedure based on the v8.1.7 version of the
standard sppurge.sql script. I'd use that script, except I don't like the
way it is called (i.e. range of SNAP_IDs). This stored procedure figures
out the range of SNAP_IDs based on the parameter indicating the number of
days of data to retain...
Hope this helps -- as always, no warranties!
--- begin included SQL*Plus
script --
/**
* File: sppurpkg.sql
* Type: SQL*Plus script
* Author: Tim Gorman (Evergreen Database Technologies, Inc.)
* Date: 18Oct01
*
* Description:
* SQL*Plus script containing DDL commands to create the package
* SPPURPKG, intended for use with STATSPACK from Oracle database
* versions 8.1.7 and above. Adapted from the sppurge.sql script
* which is included with standard STATSPACK v8.1.7, it is easier
* to use because it can be called automatedly from the DBMS_JOB
* package (instead of interactively as with sppurge.sql) and it
* takes only the number of days of STATSPACK data to retain
* (instead of prompting for a begin/end range of SNAP_IDs, like
* sppurge.sql)
*
* After the package is created, then this script will submit the
* procedure SPPURPKG.RUN(14) (i.e. purge data older than 14
* days) to run once per day. You may want to modify this,
* depending on the volume of activity on the database(s) being
* monitored by STATSPACK and the amount of storage you are
* prepared to allocate to the PERFSTAT schema...
*
* Modifications:
*/
set echo on feedback on timing on verify on
spool sppurpkg
connect perfstat
show user
show release
set termout off
create or replace package SPPURPKG
is
--
procedure PURGE(in_days_older_than IN INTEGER);
--
end SPPURPKG;
/
set termout on
show errors
set termout off
create or replace package body SPPURPKG
is
--
procedure PURGE(in_days_older_than IN INTEGER)
is
--
cursor get_snaps(in_days IN INTEGER)
is
select s.rowid,
s.snap_id,
s.dbid,
s.instance_number
from stats$snapshot s,
sys.v_$database d,
sys.v_$instance i
where s.dbid = d.dbid
and s.instance_number = i.instance_number
and s.snap_time trunc(sysdate) - in_days;
--
errcontext VARCHAR2(100);
errmsg VARCHAR2(1000);
save_module VARCHAR2(48);
save_action VARCHAR2(32);
--
begin
--
errcontext := 'save settings of DBMS_APPLICATION_INFO';
dbms_application_info.read_module(save_module, save_action);
dbms_application_info.set_module('SPPURPKG.PURGE', 'begin');
--
errcontext := 'open/fetch get_snaps';
dbms_application_info.set_action(errcontext);
for x in get_snaps(in_days_older_than) loop
--
errcontext := 'delete (cascade) STATS$SNAPSHOT';
dbms_application_info.set_action(errcontext);
delete
from stats$snapshot
where rowid = x.rowid;
--
errcontext := 'delete dangling STATS$SQLTEXT rows';
dbms_application_info.set_action(errcontext);
delete
from stats$sqltext
where (hash_value, text_subset) not in
(select /*+ hash_aj(ss) */ hash_value, text_subset
from stats$sql_summary ss
);
--
errcontext := 'delete dangling STATS$DATABASE_INSTANCE rows';
dbms_application_info.set_action(errcontext);
delete
from stats$database_instance i
where i.instance_number = x.instance_number
and i.dbid= x.dbid
and not exists
(select 1
from stats$snapshot s
where s.dbid= i.dbid
and s.instance_number = i.instance_number
and s.startup_time= i.startup_time
);
--
errcontext := 'delete dangling STATS$STATSPACK_PARAMETER rows';
dbms_application_info.set_action(errcontext);
delete
from stats$statspack_parameter p
where p.instance_number = x.instance_number
and p.dbid= x.dbid
and not exists
(select 1
from stats$snapshot s
where s.dbid= p.dbid
and s.instance_number = p.instance_number
);
--
errcontext := 'fetch/close get_snaps';
dbms_application_info.set_action(errcontext);
--
end loop;
--
errcontext := 'restore saved settings of DBMS_APPLICATION_INFO';
dbms_application_info.set_module(save_module, save_action);
--
exception
--
when OTHERS then
errmsg := sqlerrm;
dbms_application_info.set_module(save_module, save_action);
raise_application_error(-2, errcontext || ': ' || errmsg);
--
end PURGE;
--
end SPPURPKG;
/
set termout on
show errors
variable jobno number;
begin
dbms_job.submit(:jobno, 'sppurpkg.purge(14);', sysdate+(1/1440),
'SYSDATE+1', TRUE);
commit;
end;
process size limitation on HP-UX
Title: process size limitation on HP-UX When 32 bit Oracle (8.0.5) is running under hp-ux 11 (64 bit), is the shadow process size limited by the 'maxdsiz' kernal parameter (defaults to 64 Meg) or the 'maxdsiz_64' kernel parameter, which default to 1 Gig? Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
SQL*Loader : How can I load the Line Feed as part of data
Greetings, We have one column in a table and this column need to store data with Line Feed, denoted by \x0d\x0a in the following row. This data comes from Sybase database. If \x0d\x0a cannot be loaded as Line Feed through the SQL*Loader then how can we do it? Any help is appreciated. Thanks, Ashoke Data file contains the following one record : crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction Atrial Pacing Minimum Interval and x0d0a Manual Therapy Atrial 50 Hz Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is conducted in the Ventricle, this parameter encodes to Manual Therapy 50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and 5.|373441192 Sample Control File : LOAD DATA INFILE param.dat str X'0d0a' BADFILE param.bad append into TABLE param_ashoke TRAILING NULLCOLS (DEVICE CHAR(80) TERMINATED BY | ,PARAM CHAR(80) TERMINATED BY | ,PARAM_TYPE CHAR(20) TERMINATED BY | ,SW_NOTE CHAR(4000) TERMINATED BY | ,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY | ,FW_VAR_BASE CHAR(80) TERMINATED BY | ,FW_VAR_MEMBER CHAR(80) TERMINATED BY | ,BIT_OFFSET CHAR(38) TERMINATED BY | ,BIT_SIZE CHAR(38) TERMINATED BY | ,FW_ENCODE_DECODE CHAR(20) TERMINATED BY | ,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY | ,ALIAS CHAR(80) TERMINATED BY | ,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: E business Suite 11i - for Apps DBA's
The 11.5.6 and 11.5.7 versions of the TRMs have recently been made available online at http://etrm.oracle.com/pls/etrmlatest/etrm.etrmnav.show These include the ERDs for each module. Complicated reading =) HTH, Mike Hately Oracle DBA -Original Message- Sent: 12 August 2002 14:13 To: Multiple recipients of list ORACLE-L Maria, Unless things really changed with 11i, Oracle has never HAD an ERD for the Apps. That's one of the reasons customization is such an incredible chore. As for the data dictionary, the Technical Reference Manuals can be ordered from Oracle. Be prepared, they are hideously expensive. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Monday, August 12, 2002 2:48 AM To: Multiple recipients of list ORACLE-L Hello Application DBA's I'd like to know where I can get/buy E-business Suite 11i documentation with the ERD and data dictionary of the tables. Thank you so much. -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately 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: How tocheck the dir existence on a remote server
Srinivas - how about this? #!/bin/ksh if rsh srvr27 'ls /export/home/oracle' then echo existing else echo not existing fi -Original Message- Sent: Monday, August 12, 2002 5:53 AM To: Multiple recipients of list ORACLE-L Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo existing else echo not existing fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Unix Solaris forum.
I looked for www.sunmangers.org but it doesnt work? Can you please re-check the reference. Thanks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 11, 2002 9:18 PM www.sunmangers.org will do it for you Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Chuan Zhang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12-08-2002 01:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:Unix solaris forum. Hi, DBAs, Could anyone recommend a good unix solaris discussion/news group for me? Thanks, Chuan -- 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: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DB Link Error - More Info
Thanks Jared. Looks like I will have to use some other method besides copy. It would have been good since it had the auto-commit on it. Oh well. -Original Message- Sent: Friday, August 09, 2002 8:18 PM To: Multiple recipients of list ORACLE-L SQL set arraysize 1 SQL set copycommit 1000 SQL copy from kgel/vinotamu@nxtp - insert nxtp.temp_mgh - using - select * from rrs$.temp_mgh Array fetch/bind size is 1. (arraysize is 1) Will commit after every 1000 array binds. (copycommit is 1000) Maximum long size is 80. (long is 80) select * from rrs$.temp_mgh * Error in SELECT statement: ORA-01002: fetch out of sequence Kevin, This appears to be a well known problem. Two entries from MetaLink: SQL*Plus Technical Forum From: Gorm Heilskov 12-Jun-01 19:58 Subject: ORA-01002: fetch out of sequence for Copy statement ORA-01002: fetch out of sequence for Copy statement I receive an ORA-01002 when trying to use the copy statement on an 8.0.4 database on Netware from an 8.1.7 client. The copy statement runs fine on an 8.1.7 database on Windows 2000 using an 8.1.7 client. It also works fine using an 8.0.5 client. What is preventing the copy statement from working? From: Oracle, Anil Shenoy 15-Jun-01 07:32 Subject: Re : ORA-01002: fetch out of sequence for Copy statement Hi, A bug with no 644413 has been filed on this and has been fixed in 8.1.5 and 8.0.6.1. I cannot file a backport request as 8.0.4 is desupported. However you can use the workaround as below 1) Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE' ie: SQL create database link linkName connect to UserId identified by 2) Create a new table using 'CREATE TABLE' ie: SQL create table TableName as select * from RemoteTableName@linkName or 2) Insert data into an existing table using 'INSERT' ie: SQL insert into TableName select * from RemoteTableName@linkName Regards, Anil Oracle Support Services Bookmark Fixed font Go to End Doc ID: Note:110364.1 Subject: Workaround for ORA-1002 on COPY COMMAND from 8.Xto 8.X Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 26-MAY-2000 Last Revision Date: 22-JAN-2002 Problem Description: You are using the COPY command to copy data from one 8.x database to another 8.x database. You receive an ORA-01002 error. ORA-01002: fetch out of sequence Cause: This may be caused by fetching from a 'select for update' cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error. You see there are several bugs on the issue but not all of them are included in patchsets or have fixes. In this example you are using COPY from 8.1.6 to 8.0.6: Testcase: SQL select INSTANCE_NAME from v$instance; INSTANCE_NAME V816 SQL select INSTANCE_NAME from v$instance@V806; INSTANCE_NAME V806 SQL copy from [EMAIL PROTECTED] - insert copy2 using select * from copy1; select * from copy1 * Error in SELECT statement: ORA-1002: fetch out of sequence Solution Description: = Use the following Workaround: 1. Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE' SQL create database link linkName connect to UserId identified by @ 'Password' using 'SID'; 2. Create a new table using 'CREATE TABLE' SQL create table TableName as select * from RemoteTableName@linkName - OR - 2. Insert data into an existing table using 'INSERT' SQL insert into TableName select * from RemoteTableName@linkName Example: SQL select INSTANCE_NAME from v$instance@V806; INSTANCE_NAME V806 SQL create table copy806 as select * from copy1@V806; Table created. SQL insert into copy806 select * from copy1@V806; 64 rows created. References: === [BUG:903258] ORA-1002 COPYING FROM A REMOTE DATABASE Search Words: = ORA-1002 SQL*Plus . Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX:
Re: Transferring data from one table to another
Did you check out the SQL*Plus COPY command? Specifically in conjunction with SET ARRAYSIZE and SET COPYCOMMIT settings... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 12, 2002 1:23 AM Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000) then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. From: Abdul Aleem [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Transferring data from one table to another Date: Sun, 11 Aug 2002 23:23:19 -0800 Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To:Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: interMedia Text
As you are running 8.1.7 you should not need the extproc_connection_data entry. What you need is that the LD_LIBRARY_PATH includes $ORACLE_HOME/ctx/lib in the environment *before* you start the Listener. The listener.ora entries are the regular entries for the database SIDs. Hemant At 03:48 AM 12-08-02 -0800, you wrote: Dear List, We have :- Solaris SunOs 5.8, Oracle 8.1.7 I have created three databases using the dbassist tool and I included, as one of the installation options, InterMedia. Having read the installation guides (including post installation for Oracle InterMedia) and the Oracle interMedia Text - 8.1.5 Overview Post Installation Setup I have the following questions :- 1) Having amended the listener.ora and tnsnames.ora files I'm not sure if I have done this correctly because the Post Installation Setup refers to adding ONE entry (for extproc_connection_data) in the tnsnames.ora file for A database. But I have three, so I have entered this SID specific info. three times, is this correct ? 2) Having amended the listener.ora file to contain three extra entries is this correct ? 3) I remember one of the NET8 classes I took, where the instructor insisted that we amend these files with great caution and in particular to the layout of entries. e.g. the number of spaces etc. etc. Well the new entries I added don't conform exactly to the already existing entries, here is a snip of my listener.ora file SID_LIST_LISTENER (SID_LIST (SID_DESC (SID_NAME LSExtProc) (ORACLE_HOME u01/app/oracle/product/8.1.7) (PROGRAM xtproc) ) (SID_DESC (GLOBAL_DBNAME ATREP) (ORACLE_HOME u01/app/oracle/product/8.1.7) (SID_NAME ATREP) ) (SID_DESC (GLOBAL_DBNAME QPROD01) (ORACLE_HOME u01/app/oracle/product/8.1.7) (SID_NAME QPROD01) ) (SID_DESC (GLOBAL_DBNAME QTEST01) (ORACLE_HOME u01/app/oracle/product/8.1.7) (SID_NAME QTEST01) ) (SID_DESC SID_NAME p_agt1) (ORACLE_HOME u01/app/oracle/product/8.1.7) (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM xtproc) ) (SID_DESC SID_NAME p_agt2) (ORACLE_HOME u01/app/oracle/product/8.1.7) (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM xtproc) ) (SID_DESC SID_NAME p_agt3) (ORACLE_HOME u01/app/oracle/product/8.1.7) (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM xtproc) ) ) You can see that the SID_DESC entries layout are slightly different that those earlier, is this OK? Thanks in advance, and sorry in advance if any of the above questions are dumb, but I'm a bit stuck on this! best regards, Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MCUK INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: process size limitation on HP-UX
Title: process size limitation on HP-UX maxdsiz kernel parameter (default 64 Meg). Regards -Original Message-From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: segunda-feira, 12 de Agosto de 2002 15:48To: Multiple recipients of list ORACLE-LSubject: process size limitation on HP-UX When 32 bit Oracle (8.0.5) is running under hp-ux 11 (64 bit), is the shadow process size limited by the 'maxdsiz' kernal parameter (defaults to 64 Meg) or the 'maxdsiz_64' kernel parameter, which default to 1 Gig? Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
RMAN: How to restore backup to a different box
Title: RMAN: How to restore backup to a different box Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut
RE: Transferring data from one table to another
How about turn off logging and drop indexes on the target table. Do insert with the APPEND hint. Re-create index. -Original Message- Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. From: Abdul Aleem [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Transferring data from one table to another Date: Sun, 11 Aug 2002 23:23:19 -0800 Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To:Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include 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: SQL*Loader : How can I load the Line Feed as part of data
check the utilities documentation, specifically the Field List section and the terminated by clause.. you can override the default terminator of line feed. You can also concatenate multiple lines together --- Mandal, Ashoke [EMAIL PROTECTED] wrote: Greetings, We have one column in a table and this column need to store data with Line Feed, denoted by \x0d\x0a in the following row. This data comes from Sybase database. If \x0d\x0a cannot be loaded as Line Feed through the SQL*Loader then how can we do it? Any help is appreciated. Thanks, Ashoke Data file contains the following one record : crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction Atrial Pacing Minimum Interval and x0d0a Manual Therapy Atrial 50 Hz Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is conducted in the Ventricle, this parameter encodes to Manual Therapy 50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and 5.|373441192 Sample Control File : LOAD DATA INFILE param.dat str X'0d0a' BADFILE param.bad append into TABLE param_ashoke TRAILING NULLCOLS (DEVICE CHAR(80) TERMINATED BY | ,PARAM CHAR(80) TERMINATED BY | ,PARAM_TYPE CHAR(20) TERMINATED BY | ,SW_NOTE CHAR(4000) TERMINATED BY | ,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY | ,FW_VAR_BASE CHAR(80) TERMINATED BY | ,FW_VAR_MEMBER CHAR(80) TERMINATED BY | ,BIT_OFFSET CHAR(38) TERMINATED BY | ,BIT_SIZE CHAR(38) TERMINATED BY | ,FW_ENCODE_DECODE CHAR(20) TERMINATED BY | ,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY | ,ALIAS CHAR(80) TERMINATED BY | ,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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).
Error while invoking Email from PL/SQL
Hi All,
I am trying to send an Email using PL/SQL. (Oracle 8.1.7 and Solaris 5.8).
I tried to run the initplsj.sql and got the following error.
Can you guys tell me what should I do to correct this?
SQL @initplsj.sql
call dbms_java.set_output(1)
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
call dbms_java.loadjava('-resolve plsql/jlib/plsql.jar')
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
And then I tried to run
loadjava -user sys/*** plsql.jar, again to no avail
Thanks in advance
K.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: karthikeyan S
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: RMAN: How to restore backup to a different box
Check the RMAN docs; the process for doing this is well described there. Basically, you either use a parameter in the init file (when you can do wildcard path changes) or use set commands in the rman recover script. Glenn Stauffer On Monday 12 August 2002 11:48 am, you wrote: Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
Title: RE: Transferring data from one table to another CTAS with nologging. Could create a simple script to do this. -Original Message- From: Peter R [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. From: Abdul Aleem [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Transferring data from one table to another Date: Sun, 11 Aug 2002 23:23:19 -0800 Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000) then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
Do you want to copy or move ? If move then partition the target table and do an exchange partition, is the faster way to move data. --- Ji, Richard [EMAIL PROTECTED] a écrit : How about turn off logging and drop indexes on the target table. Do insert with the APPEND hint. Re-create index. -Original Message- Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. From: Abdul Aleem [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Transferring data from one table to another Date: Sun, 11 Aug 2002 23:23:19 -0800 Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent:Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing
RE: RMAN: How to restore backup to a different box
Helmut - I am struggling with this myself. What is your goal? Are you just trying to create a test database, or trying to perform a disaster recovery test? For creating a test database, take a look at the RMAN DUPLICATE command. As to your specific question of changing the file path, the RMAN SWITCH command should be able to do what you need. Another idea is to have your Unix system administrator create the file paths you need so that you don't have to perform any changes in RMAN. For example, if you need a path /u01, create a file path or mount point with that name on the test system. One less thing to wrestle with in RMAN. I do not find that RMAN takes kindly to changes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Houston, do I have a problem?
Joe, This was just a 'review' of the top waits in the database. I can not draw any conclusions as to what the problem is, yet. But, the I/O subsystem appears to be stressed a bit. Why, who, when and how will not be visible at this level. Thanks. - Kirti -Original Message- Sent: Sunday, August 11, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Kirti, since i'm still not up to speed on the Wait event concept. What should i see as a problem in your report. thanks, joe Deshpande, Kirti wrote: This is not a joke.!!! This is from a business critical production database that I was asked to 'review' past Friday. The report is from v$system_event taken at 10:30am, Aug 9, 2002. The server (and database) was bounced on Aug 4, 2002 at 9:20am. This was the 1st time I was logging into this database. SQL / EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- --- -- --- control file parallel write 143933 0 4080356626 28349.0001 db file scattered read 12540695 0 1.2254E+10 977.107332 buffer busy waits 10740450 36 8193235928 762.839167 SQL*Net message from client 180769027 0 9.9561E+10 550.761199 db file sequential read 298968127 0 1.1839E+11 395.99129 enqueue 13500 6435 2036785 150.872963 SQL*Net more data from client 52227948 0 4093231165 78.3724294 free buffer waits16 4 795 49.6875 log file switch completion 804 43 16263 20.2276119 log buffer space977 05409 5.53633572 control file single write17 0 51 3 db file parallel write 1749695 0 2935317 1.67761638 db file parallel read 8149 0 13484 1.65468156 log file single write 1024 0 701 .684570313 latch free 20070341616763 1054137 .525221297 log file sync 1366242560 526049 .385033545 SQL*Net message from dblink 1514480 0 451351 .298023744 log file sequential read 405415 0 82877 .204425095 SQL*Net break/reset to dblink10 0 2 .2 log file parallel write 2025192 7 293332 .144841576 SQL*Net break/reset to client 28113 03221 .114573329 db file single write320 0 36 .1125 SQL*Net more data from dblink447044 0 11375 .025444923 SQL*Net more data to client11770996 0 75680 .006429362 control file sequential read 554851 03261 .005877254 SQL*Net more data to dblink1076 0 5 .00464684 buffer deadlock1045 1029 1 .000956938 SQL*Net message to dblink 1514485 0 456 .000301092 SQL*Net message to client 180769119 0 48736 .000269604 29 rows selected. SQL Here is the environment: 1)all the file systems for the database, including dump
RE: RMAN: How to restore backup to a different box
Helmut, Check out the newname command that can be used when duplicating a database set newname for datafile 1 TO '$ORACLE_HOME/dbs/newdb_data_01.f'; set newname for datafile 2 TO '$ORACLE_HOME/dbs/newdb_data_02.f'; set newname for datafile 3 TO '$ORACLE_HOME/dbs/newdb_data_11.f'; set newname for datafile 4 TO '$ORACLE_HOME/dbs/newdb_data_12.f'; set newname for datafile 5 TO '$ORACLE_HOME/dbs/newdb_data_21.f'; set newname for datafile 6 TO '$ORACLE_HOME/dbs/newdb_data_22.f'; HTH John -Original Message- Sent: 12 August 2002 17:24 To: Multiple recipients of list ORACLE-L Helmut - I am struggling with this myself. What is your goal? Are you just trying to create a test database, or trying to perform a disaster recovery test? For creating a test database, take a look at the RMAN DUPLICATE command. As to your specific question of changing the file path, the RMAN SWITCH command should be able to do what you need. Another idea is to have your Unix system administrator create the file paths you need so that you don't have to perform any changes in RMAN. For example, if you need a path /u01, create a file path or mount point with that name on the test system. One less thing to wrestle with in RMAN. I do not find that RMAN takes kindly to changes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Transferring data from one table to another
Peter, CTAS is the way to go with large datasets - CTAS in parallel (assuming multiple CPUs) and you will be good to go. Jack --- [EMAIL PROTECTED] wrote: CTAS with nologging. Could create a simple script to do this. -Original Message- Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. From: Abdul Aleem [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Transferring data from one table to another Date: Sun, 11 Aug 2002 23:23:19 -0800 Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent:Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the Pseudo code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: SQL*Loader : How can I load the Line Feed as part of data
Hi Rachel,
I have attached the sample data and controlfile, which we are using to load the data.
It is loading the line feed characters('\x0d\x0a') as it is. Oracle should store it as
Line Feed while loading through SQL*Loader.
When I select this column it should displays something like this. i.e. a line feed
instead of \x0d\x0a.
But currently it does not happen that way as SQL*Loader loads '\x0d\x0a' as it is.
I may be missing something. I searched in manual and Metalink but not able fix my
problem.
Thanks,
Ashoke
When the test is conducted in the Atrium, this parameter encodes to Manual Therapy 50
Hz Induction Atrial Pacing Minimum Interval and
Manual Therapy Atrial 50 Hz Induction Instruction Buffer Byte 7 and 9.\x0d\x0a
When the test is conducted in the Ventricle,this parameter encodes to Manual Therapy
50 Hz Induction Ventricular Pacing Minimum Interval and \x0d\x0a
Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and 5. and when we select
this column
-Original Message-
Sent: Monday, August 12, 2002 10:58 AM
To: Multiple recipients of list ORACLE-L
check the utilities documentation, specifically the Field List section
and the terminated by clause.. you can override the default
terminator of line feed. You can also concatenate multiple lines
together
--- Mandal, Ashoke [EMAIL PROTECTED] wrote:
Greetings,
We have one column in a table and this column need to store data with
Line Feed, denoted by \x0d\x0a in the following row. This data comes
from Sybase database. If \x0d\x0a cannot be loaded as Line Feed
through the SQL*Loader then how can we do it?
Any help is appreciated.
Thanks,
Ashoke
Data file contains the following one record :
crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in
the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction
Atrial Pacing Minimum Interval and \x0d\x0a Manual Therapy Atrial 50 Hz
Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is
conducted in the Ventricle, this parameter encodes to Manual Therapy
50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a
Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and
5.|373441192
Sample Control File :
LOAD DATA
INFILE param.dat str X'0d0a'
BADFILE param.bad
append into TABLE param_ashoke
TRAILING NULLCOLS
(DEVICE CHAR(80) TERMINATED BY |
,PARAM CHAR(80) TERMINATED BY |
,PARAM_TYPE CHAR(20) TERMINATED BY |
,SW_NOTE CHAR(4000) TERMINATED BY |
,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY |
,FW_VAR_BASE CHAR(80) TERMINATED BY |
,FW_VAR_MEMBER CHAR(80) TERMINATED BY |
,BIT_OFFSET CHAR(38) TERMINATED BY |
,BIT_SIZE CHAR(38) TERMINATED BY |
,FW_ENCODE_DECODE CHAR(20) TERMINATED BY |
,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY |
,ALIAS CHAR(80) TERMINATED BY |
,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.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: Mandal, Ashoke
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
OPS 7.3.4 - how to ?
Dear gurus ! I have to create an OPS 7.3.4 database (yes, there are still Oracle7 installations out there). Actually we are migrating our current OPS DB to another storage (EMC). So , the software is installed , i just need to create the DB. I can not find which scripts to run after the create database (i.e. catproc.sql , catexp.sql). Does anyone have a list of such scripts to run for OPS , please? Also , are there any known gotchas for OPS 7.3.4 setup ? I plan to export the exisiting DB, to create the new DB (with the same name of the DB and the instances as the original ones) and then to import. Is it OK ? TIA. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: E business Suite 11i - for Apps DBA's
All documentation, including trm info, is in the CD pack (UD$40), orderable from oraclestore.oracle.com. (click on CD packs, choose platform, choose the product (oracle apps r11i 7 cd pack) Venkat Somusetty Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Venkat Somusetty INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
Oracle releasing clustered file system code The move is designed to allow better management of databases on Linux server clusters. http://computerworld.com/newsletter/0%2C4902%2C73404%2C0.html?nlid=AM -- 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: OPS 7.3.4 - how to ?
What platform? What OS? Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, August 12, 2002 1:08 PM To: Multiple recipients of list ORACLE-L Dear gurus ! I have to create an OPS 7.3.4 database (yes, there are still Oracle7 installations out there). Actually we are migrating our current OPS DB to another storage (EMC). So , the software is installed , i just need to create the DB. I can not find which scripts to run after the create database (i.e. catproc.sql , catexp.sql). Does anyone have a list of such scripts to run for OPS , please? Also , are there any known gotchas for OPS 7.3.4 setup ? I plan to export the exisiting DB, to create the new DB (with the same name of the DB and the instances as the original ones) and then to import. Is it OK ? TIA. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Case Statements, Distributed Queries, ORA-22804 Errors
When one tries to use a case statement, okay it's really a function, over a database link Oracle objects: ORA-22804 remote operations not permitted on object tables or user-defined type columns Cause: An attempt was made to perform queries or DML operations on remote object tables or on remote table columns whose type is one of object, REF, nested table or VARRAY. Action: Remove the reference to remote tables in the statement === There are no object tables, varrays or anything of that ilk involved in the statement. Why the error? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- 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).
odbc/jdbc question
We are running Oracle 8.1.7 on AIX. Our users want to use ODBC JDBC products to connect to the database. Are there any security issues we should know about? Any other issues? Thanks. Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Armstrong-Champ INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
set sql*trace VB/Crystal
List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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:set sql*trace VB/Crystal
Barb, More than likely VB is spawning Crystal in a separate database session, therefore the alter session command will not work. You could have her start the report then use top sessions to extract the sql and explain plan from the DB. Or you could extract the sql from the crystal report go from there. Dick Goulet Reply Separator Author: Baker; Barbara [EMAIL PROTECTED] Date: 8/12/2002 12:23 PM List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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).
RE: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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).
Explain Plan Question
Hello: I have to looking through Metalink but am unable to find the following. Does running Explain Plan take into Account the Cost Generated By using PL/SQL Functions? For example Select * from EMP where EMPID=FUNCTION_GET_ID; I do not believe that it does since the Function SQL does not seem to be included in the Plan Output. Is this correct? Regards, Jay _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal If you want to see the trace do the following connected to the appropriate database: SQL show parameters dump; NAME TYPE VALUE --- -- background_core_dump string partial background_dump_dest string /opt/oracle/admin/ods/bdump core_dump_dest string /opt/oracle/admin/ods/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /opt/oracle/admin/ods/udump I believe it is under user_dump_dest - and is constrained by the max_dump_file_size. You will then need to use tkprof commands to format *.trc file. To check it is correct trace file can grep session id or even bit of SQL she used that would be specific to her session. Most of the Oracle references have tkprof examples, so does metalink and cdrom with oracle doc. -Original Message- From: Stankus, Paula G Sent: Monday, August 12, 2002 4:03 PM To: '[EMAIL PROTECTED]' Subject: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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: set sql*trace VB/Crystal
You can see the sql generated by the report in Crystal, so take that sql and run it in sqlplus to see the access plan. You can also check in v$sqltext the select run by the report. --- Baker, Barbara [EMAIL PROTECTED] a écrit : List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: set sql*trace VB/Crystal
What connection are they using? If they are using Oracle Object Of OLE I think there is a parameter that can be set. From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: set sql*trace VB/Crystal Date: Mon, 12 Aug 2002 13:08:23 -0800 If you want to see the trace do the following connected to the appropriate database: SQL show parameters dump; NAME TYPEVALUE --- -- background_core_dump string partial background_dump_dest string /opt/oracle/admin/ods/bdump core_dump_dest string /opt/oracle/admin/ods/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /opt/oracle/admin/ods/udump I believe it is under user_dump_dest - and is constrained by the max_dump_file_size. You will then need to use tkprof commands to format *.trc file. To check it is correct trace file can grep session id or even bit of SQL she used that would be specific to her session. Most of the Oracle references have tkprof examples, so does metalink and cdrom with oracle doc. -Original Message- Sent: Monday, August 12, 2002 4:03 PM To: '[EMAIL PROTECTED]' I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Brain cramp on analytical functions and grouping.
I pivoted the result set on the
WO column. This example works for up to 12 distinct values for the CP
column. I don't know if you need to pivot it again to get back to the
original result set but at least it gives you the sort order you
described.
HTH
Tony Aponte
Home Shopping Network,
Inc.
create table work_orders (WO
VARCHAR2(7),CP VARCHAR2(7))insert into work_orders values
('W859674','A120003')insert into work_orders values
('W859674','A120004')insert into work_orders values
('W859674','A120006')insert into work_orders values
('W838796','A12')insert into work_orders values
('W838796','A120003')insert into work_orders values
('W844656','A12')insert into work_orders values
('W844656','A120004')insert into work_orders values
('W849769','A12')insert into work_orders values
('W849769','A120004')insert into work_orders values
('W858835','A12')insert into work_orders values
('W858835','A120003')insert into work_orders values
('W880717','A120003')insert into work_orders values
('W880717','A120006')commit
SELECT
g1
,MAX(DECODE(line_no,01,value,NULL)) A,
MAX(DECODE(line_no,02,value,NULL)) B,
MAX(DECODE(line_no,03,value,NULL)) C,
MAX(DECODE(line_no,04,value,NULL)) D,
MAX(DECODE(line_no,05,value,NULL)) E,
MAX(DECODE(line_no,06,value,NULL)) F,
MAX(DECODE(line_no,07,value,NULL)) G,
MAX(DECODE(line_no,08,value,NULL)) H,
MAX(DECODE(line_no,09,value,NULL)) I,
MAX(DECODE(line_no,10,value,NULL)) J,
MAX(DECODE(line_no,11,value,NULL)) K,
MAX(DECODE(line_no,12,value,NULL))
L
FROM (SELECT g1,value,row_number() over(partition by g1
order by g1 nulls last) line_no
FROM (SELECT wo g1,cp value from work_orders)
)
GROUP BY g1
ORDER BY 2,3,4,5,6,7,8,9,10,11,12,13,1
G1ABCDEFGHIJKLW838796A12A120003W858835A12A120003W844656A12A120004W849769A12A120004W859674A120003A120004A120006W880717A120003A120006-Original Message-From: Jesse, Rich
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 07, 2002 2:35 PMTo: Multiple recipients
of list ORACLE-LSubject: Brain cramp on analytical functions and
grouping.OK, my brain hurts. A dev wants a query to return in
a peculiar sort orderon 8.1.7.2, but I'm having no luck. He needs
groups of rows sorted by thewhole of their key values. That doesn't
sound right, so maybe an example:Table
ARI WO
CP
RC RN1
W859674
A120003
3 12
W859674
A120004
3 23
W859674
A120006
3 34
W838796
A12
2 15
W838796
A120003
2 26
W844656
A12
2 17
W844656
A120004
2 28
W849769
A12
2 19
W849769
A120004
2 210
W858835
A12 2
111 W858835
A120003
2
212 W880717
A120003
2
113 W880717
A120006
2 2In an attempt to breakdown
the problem, I added columns RC and RN as"COUNT(*) OVER (PARTITION BY WO)"
and "ROW_NUMBER() OVER (PARTITION BY WOORDER BY CP)", respectively. I
also added the row spacing here for clarity.The dev would like the group
of WO W858835, rows 10 and 11, immediatelyafter WO group W838796 because the
groups have the same number of rows (RC)and same values of CP within the
groups.MIN and MAX would work in this case, but if the groups are larger
than twoit's no guarantee of order. What I was thinking is a report
column thatwould be the concatonation of all the CPs for the group, but
since it'sVARCHAR2 and not numeric, I'm not sure how that could be
accomplished.Any suggestions, including favorite beers, is more than
welcome.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-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: Houston, do I have a problem?
Cary, Thank you very much for a wonderful response. I have not yet talked to any users or developers regarding the performance of this database. I just shared with my boss (and with the list) what I found in my brief 'review' of the database. There are no reported performance related problems that need to be fixed right away. But he was concerned as we will be loading this database soon to support a couple of more Applications. One of which will have more OLTP transactions than the other. Currently the database has heavy DML activity only during the batch processes. There is very minimal DML via on-line (Intranet). From our auto-scheduler (AutoSys) system, I have found out that there are a *number* of batch jobs that run *concurrently* against this database in the batch window. That would be the next area to 'review' as to what these jobs do and how they do it. For all we know, it could just be a scheduling problem! I am not paying much attention to the stats at system level as they do not mean much, at this time. I will be watching the batch processes and most probably consider using Statspack and make use of Sparky (http://www.hotsos.com/products/sparky/) to dig deeper. I am not going to reach any conclusion based solely on what I saw in v$system_event. That was just my first step to see what kinds of waits this database had encountered, just as some check the hit ratio first ;-) But I must say, based on those stats, that the I/O subsystem is being stressed quite a bit. Regards, - Kirti -Original Message- Sent: Monday, August 12, 2002 1:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the wait interface as they are from v$sysanything-else.) Don't lose hope if you look at Kirti's note and wonder, so what's the point? You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the secret constants (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than normal systems at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is heinously longer than normal. 2. If you know the secret list of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it shouldn't be doing. Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming heinously longer-than-normal elapsed times, or even if it is called heinously too often, does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the right kinds of service in the right proportions? How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a normal, healthy profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's worst performance problems and then find out that their work really didn't make a noticeable
RE: Delete performance
Title: RE: Delete performance I would use your method to CTAS but combine it with partitioning in order to overcome the unavailability issue. The new table would be a single-partition (MAXVALUE) object that would enable the use of EXCHANGE feature. I posted a nugget a while back describing the use of a one-partition table (and indexes) and then swapped the underlying segment with a normal table on the fly. The catch is the licensing cost for partitioning. But we already had it for it's intended use and this availability feature was icing on the cake. This method is replaced by 9i's online reorg feature but we got a good 3 years out of it. Tony Aponte Home Shopping Network, Inc. -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 4:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Delete performance If the table can be unavailable for a very brief period of time while this is happening, I'd suggest performing a PARALLEL NOLOGGING CREATE TABLE AS SELECT to perform this mass deletion. Use a WHERE clause in the SELECT portion of the CTAS that picks up all the rows you want to keep, which is the logical negation of the WHERE clause you already have for the DELETE. Advantages: faster (INSERT operations are always faster than UPDATE or DELETE), using NOLOGGING is possible (faster, reduce overall impact on system), no undo is generated (faster, reduce one possible point of failure), and if you had any ambitions to re-build the table (i.e. get rid of chained rows, move to locally-managed tablespace), that gets done too. Same for the associated indices (rebuild them in parallel, nologging, compute stats). Also, the original table can be renamed and saved in case it's ever needed (for fast rollback, for example)... Disadvantages: if table cannot be unavailable for the last-second RENAME operation when the old table is swapped for the new, which would invalidate any associated PL/SQL stored objects and open cursors, then this won't work. However small that window of unavailability may be, sometimes you just can't go there... Just an idea... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 8:03 AM Hi all: Someone at my shop wants to delete about 20% of roes in a table (20 rows out of a million). He wants to set a commit frequency (like every 1000 records or so) to keep the rbs under control. I am not aware of any easy way to do it other then writing a procedure, but I may be missing something here. Is there any simple way to accomplish this? Also I have suggested instead of deleting 20% of the rows, create a new table as a select and insert the rest of the rows into it (then rebuild the indices and rename). This can be done in nologging mode, without redo logs and rbs segments. Is this a good idea to try? thanks for any info Gene __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: set sql*trace VB/Crystal
Try to find out the sid and serial# of her session. From a dba user use exec dbms_system.set_sql_trace_in_session(sid, serial#,true); Babu Baker, Barbara [EMAIL PROTECTED]@fatcity.com on 08/12/2002 03:23:23 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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).
Oracle vs. DB2
Hi Everyone! Well, there's been a lot of Oracle vs. Microsoft traffic on the list, but now my Manglement wants a similar comparison to IBM's DB2. Does anyone know of web sites or locations where there are documented objective comparisons between Oracle and DB2? I'm faced with answering buzzwords like 'Future Market Position', 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and 'Platform Compatibility'. Any references are appreciated. Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
Hello,
I have a question regarding method of index access(full vs. range) that
oracle uses when I use different join methods (Hash vs. Nested loop).
Here are the some details about the environment.
Server : Oracle Server EE ver 8.1.7.2
OS : HP UX 11.0
hash_area_size : 4194304
hash_join_enabled : TRUE
hash_multiblock_io_count: 64
The tables, indexes are all analyzed fully (no estimates) with dbms_stat.
The following two scenarios are executed back-to-back and are reproducable.
The parameter value to the SQL could be any month in a format like 200207,
200201 etc..
When I use nested loop join the index on large table is ranged scaned, but
when I use the hash join the full scan on the index is done. My question is
why is the behviour of index scan different? Why there is no partition
pruning when the table is hash-joined?
The details related to the objects is at the end of the message.
Thanks for your help.
Regards
Viral.
NESTED LOOP SQL
===
select --+use_nl(cm fd) index(fd)
fd.*
from copa_mdo_srce_sys_map cm
,fact_dmnsn_q3 fd
where fd.srce_sys_id = cm.srce_sys_id
and cm.mdo_id = 'NA'
and fd.due_perd = LAST_DAY(TO_DATE('1', 'MM'))
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1333 Card=54869 Bytes5925852)
1 0 NESTED LOOPS (Cost=1333 Card=54869 Bytes=5925852)
2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9 Bytes=54)
3 1 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3'
(Cost=148 Card=73158 Bytes=7462116)
5 4 INDEX (RANGE SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQ
UE) (Cost=30 Card=73158)
HASH JOIN SQL
===
select --+use_hash(cm fd) index(fd)
fd.*
from copa_mdo_srce_sys_map cm
,fact_dmnsn_q3 fd
where fd.srce_sys_id = cm.srce_sys_id
and cm.mdo_id = 'NA'
and fd.due_perd = LAST_DAY(TO_DATE('1', 'MM'));
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13103 Card=54869 Byt
es=5925852)
1 0 HASH JOIN (Cost=13103 Card=54869 Bytes=5925852)
2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 C
ard=9 Bytes=54)
3 1 PARTITION RANGE (ALL)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3'
(Cost=13101 Card=73158 Bytes=7462116)
5 4 INDEX (FULL SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQU
E) (Cost=11693 Card=73158)
Table/index designs
===
desc fact_dmnsn_q3
Name Null?Type
- -
FACT_ID NOT NULL NUMBER(15)
CAPTL_ID VARCHAR2(15)
CUST_IDVARCHAR2(15)
FUNC_IDVARCHAR2(15)
GENRC_DMNSN_1_ID VARCHAR2(15)
GENRC_DMNSN_1_TYPE_ID NUMBER(4)
GENRC_DMNSN_2_ID VARCHAR2(15)
GENRC_DMNSN_2_TYPE_ID NUMBER(4)
GENRC_DMNSN_3_ID VARCHAR2(15)
GENRC_DMNSN_3_TYPE_ID NUMBER(4)
GEO_ID VARCHAR2(15)
LEGAL_ENT_ID VARCHAR2(15)
MEASR_ID VARCHAR2(15)
MM_HYBRD_IDVARCHAR2(15)
ORG_ID VARCHAR2(15)
PROD_IDVARCHAR2(15)
PROFT_CTR_ID VARCHAR2(15)
SITE_IDVARCHAR2(15)
TIME_PERD_ID NOT NULL VARCHAR2(15)
TRADE_CHANL_ID VARCHAR2(15)
FACT_TYPE_CODENOT NULL VARCHAR2(2)
ISO_CRNCY_CODE_CHARVARCHAR2(3)
SRCE_SYS_ID NOT NULL NUMBER(15)
LYOUT_ID NUMBER(4)
FACT_QLTY_CODE VARCHAR2(1)
MKT_CLASS_CODE VARCHAR2(1)
DEMND_PLAN_CUST_GRP_CODE VARCHAR2(10)
TIME_PERD_TYPE_CODE NOT NULL VARCHAR2(4)
TIME_PERD_END_DATENOT NULL DATE
DUE_PERD NOT NULL DATE
ROW_ORIGN VARCHAR2(1)
PRTTN_CODE VARCHAR2(15)
PARNT_FACT_ID NUMBER(15)
DELIV_RCVD_ID NUMBER(10)
ORIG_UNIT_ID NUMBER(11)
SQL:idwsp3 - desc copa_mdo_srce_sys_map
Name Null?Type
Re: Oracle vs. DB2
For what I've read, globally the 2 databases are equal in performance, reliability and functionnalities. Larryh E as many times said that it's only competition in the database market is DB2. I guess it really depends on your environment. Of course Oracle works on more OS (used to be anyway), but which big organisation only have one DB ? All big companies I've worked have many DB. I would be interested by any non-partial comparison between Oracle and DB2. --- Vergara, Michael (TEM) [EMAIL PROTECTED] a écrit : Hi Everyone! Well, there's been a lot of Oracle vs. Microsoft traffic on the list, but now my Manglement wants a similar comparison to IBM's DB2. Does anyone know of web sites or locations where there are documented objective comparisons between Oracle and DB2? I'm faced with answering buzzwords like 'Future Market Position', 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and 'Platform Compatibility'. Any references are appreciated. Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Houston, do I have a problem?
control file parallel write 143933 0 4080356626 28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 vs. DB2
-- Vergara, Michael (TEM) [EMAIL PROTECTED] on 08/12/02 14:38:19 -0800 Hi Everyone! Well, there's been a lot of Oracle vs. Microsoft traffic on the list, but now my Manglement wants a similar comparison to IBM's DB2. Does anyone know of web sites or locations where there are documented objective comparisons between Oracle and DB2? I'm faced with answering buzzwords like 'Future Market Position', 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and 'Platform Compatibility'. www.ibm.com -- 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SAN issues
Babu, Nice comprehensive list of things to consider with a SAN, Just a couple of thoughts. ® Oracle requests DBWR-1 for IDX1 and waits. DBWR-1 makes a Unix IO call and waits for Unix to return data. Unix talks to SAN and SAN starts reading from the disk. Assume that it takes 3 seconds to read the entire IDX1. SAN starts returning data in chunks to Unix and Unix gives it back to Oracle. Data is read from Disk by server processes, not by DBWR. ® Now a slightly bigger picture. There are 6 processes trying to read the data from six different tables. This occurs regardless of the type of storage system, so I'm not sure it really belongs in a list of SAN specific concerns. ® Lets forget all this buffering, caches etc. Assume we have 10 disks in two LUNs. Both the LUNs share the 10 disks. Each of this LUN is made visible to Unix as a mountpoint. The DBA uses one mountpoint for indexes and one mountpoint for tables. You can have this same kind of configuration problem with any disk storage manager. Don't forget the management issue with SANs. SA's love them because it greatly reduces the amount of work they must do to manage storage. They can be properly configured from a database point of view, at least as far as distribuing IO is concerned, you just need to make it known that you would like some input on it's configuration. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/12/2002 01:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SAN issues All I an trying to get our management understand the issues related to SAN. These are my thoughts. Let me know what you think about it... (PS : Apologies if you recv this twice. I posted it but I never saw it come through the list and so I posted again) Babu SAN Issues SAN and Oracle ? Conflicting IO behavior ® There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files ® Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. ® SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) ® SANs do Read Ahead. If one block is requested, they read more than one blocks while at the disk hoping that the same process will request the other blocks some time soon. Here is the conflict. ® When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. ® Why read ahead will cause a conflict : ® The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+-+-+-| | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | 3.4 | | | | | | | | | | | | |-+-+-+-+-+-+-+-+-+-| ® The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) ® Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. ® The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never read ahead intelligently. Why the buffer of SAN has very little impact w.r.t Oracle read performance? ® Oracle has its own buffering for all IO types ® DBWR reads and writes uses the DB Buffer Cache ® LGWR uses the Log buffer ® Db buffer Cache is managed by a LRU
RE: set sql*trace VB/Crystal
Barb, To get all the data you might need for the session, use the 10046 level 8 tracing attribute available through the various means described at www.hotsos.com/dnloads/1/10046a. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Barbara Sent: Monday, August 12, 2002 3:23 PM To: Multiple recipients of list ORACLE-L List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the ALTER SESSION SET SQL_TRACE TRUE command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Houston, do I have a problem?
Cary, Thank you very much for a wonderful response. I have not yet talked to any users or developers regarding the performance of this database. I just shared with my boss (and with the list) what I found in my brief 'review' of the database. There are no reported performance related problems that need to be fixed right away. But he was concerned as we will be loading this database soon to support a couple of more Applications. One of which will have more OLTP transactions than the other. Currently the database has heavy DML activity only during the batch processes. There is very minimal DML via on-line (Intranet). From our auto-scheduler (AutoSys) system, I have found out that there are a *number* of batch jobs that run *concurrently* against this database in the batch window. That would be the next area to 'review' as to what these jobs do and how they do it. For all we know, it could just be a scheduling problem! I am not paying much attention to the stats at system level as they do not mean much, at this time. I will be watching the batch processes and most probably consider using Statspack and make use of Sparky (http://www.hotsos.com/products/sparky/) to dig deeper. I am not going to reach any conclusion based solely on what I saw in v$system_event. That was just my first step to see what kinds of waits this database had encountered, just as some check the hit ratio first ;-) But I must say, based on those stats, that the I/O subsystem is being stressed quite a bit. Regards, - Kirti -Original Message- Sent: Monday, August 12, 2002 1:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the wait interface as they are from v$sysanything-else.) Don't lose hope if you look at Kirti's note and wonder, so what's the point? You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the secret constants (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than normal systems at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is heinously longer than normal. 2. If you know the secret list of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it shouldn't be doing. Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming heinously longer-than-normal elapsed times, or even if it is called heinously too often, does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the right kinds of service in the right proportions? How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a normal, healthy profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's worst performance problems and then find out that their work really didn't make a noticeable
RE: Houston, do I have a problem?
Greg, That's exactly the problem--you can't tell whether it's a problem or not! If the instance has been up for a couple of days, then it's probably a big deal. If the instance has been up for several months, then it's probably far less of a big deal. ...Unless a disproportionate amount of all that waiting time has been inflicted upon a really small number of programs. You just can't tell from system-wide data! You can't extrapolate detail from an average. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Sent: Monday, August 12, 2002 6:23 PM To: Multiple recipients of list ORACLE-L control file parallel write 143933 0 4080356626 28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Houston, do I have a problem?
Greg, Yes, it's way too much. There are three control files. - Kirti -Original Message- Sent: Monday, August 12, 2002 6:23 PM To: Multiple recipients of list ORACLE-L control file parallel write 143933 0 4080356626 28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: SAN issues
Babu, If you have not already done so, please also review a paper by James Morle : Sane SAN http://www.scaleabilities.com/whitepapers.shtml - Kirti -Original Message- Sent: Monday, August 12, 2002 6:45 PM To: Multiple recipients of list ORACLE-L Babu, Nice comprehensive list of things to consider with a SAN, Just a couple of thoughts. ® Oracle requests DBWR-1 for IDX1 and waits. DBWR-1 makes a Unix IO call and waits for Unix to return data. Unix talks to SAN and SAN starts reading from the disk. Assume that it takes 3 seconds to read the entire IDX1. SAN starts returning data in chunks to Unix and Unix gives it back to Oracle. Data is read from Disk by server processes, not by DBWR. ® Now a slightly bigger picture. There are 6 processes trying to read the data from six different tables. This occurs regardless of the type of storage system, so I'm not sure it really belongs in a list of SAN specific concerns. ® Lets forget all this buffering, caches etc. Assume we have 10 disks in two LUNs. Both the LUNs share the 10 disks. Each of this LUN is made visible to Unix as a mountpoint. The DBA uses one mountpoint for indexes and one mountpoint for tables. You can have this same kind of configuration problem with any disk storage manager. Don't forget the management issue with SANs. SA's love them because it greatly reduces the amount of work they must do to manage storage. They can be properly configured from a database point of view, at least as far as distribuing IO is concerned, you just need to make it known that you would like some input on it's configuration. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/12/2002 01:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SAN issues All I an trying to get our management understand the issues related to SAN. These are my thoughts. Let me know what you think about it... (PS : Apologies if you recv this twice. I posted it but I never saw it come through the list and so I posted again) Babu SAN Issues SAN and Oracle ? Conflicting IO behavior ® There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files ® Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. ® SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) ® SANs do Read Ahead. If one block is requested, they read more than one blocks while at the disk hoping that the same process will request the other blocks some time soon. Here is the conflict. ® When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. ® Why read ahead will cause a conflict : ® The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+- +-+-| | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | 3.4 | | | | | | | | | | | | |-+-+-+-+-+-+-+- +-+-| ® The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) ® Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. ® The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never read ahead intelligently.
RE: Houston, do I have a problem?
Rachel, This has not gotten to the level of getting it in 'black white' or to the 'beating' level, yet. That's why my boss wanted to just review the database and see if this thing will scale when we add more Applications and of course, hundreds of more users. More digging will be required to find problematic areas, if any, and if some one complains that there is such-and-such problem. Currently, no one is complaining, as the batch processes finish while we are asleep and the on-line load has been pretty light. But there is no guarantee that it will be so in the near future. And looking at this database at this time was a good idea from my boss, and getting someone else, (not the primary/secondary DBA) to do that is even better (for him, that is ;) Thanks. - Kirti -Original Message- Sent: Sunday, August 11, 2002 8:38 PM To: Multiple recipients of list ORACLE-L Kirti, Get it in WRITING that you are not allowed to change anything. So that when they start to beat on you (okay, I know your boss, HE won't beat but HIS boss might) you are covered. You have my sympathies... I've worked under similar conditions (Rachel, we are giving you 750GB for your databases... oh yeah, RAID 5) Rachel --- Deshpande, Kirti [EMAIL PROTECTED] wrote: This is not a joke.!!! This is from a business critical production database that I was asked to 'review' past Friday. The report is from v$system_event taken at 10:30am, Aug 9, 2002. The server (and database) was bounced on Aug 4, 2002 at 9:20am. This was the 1st time I was logging into this database. SQL / EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- --- -- --- control file parallel write 143933 0 4080356626 28349.0001 db file scattered read 12540695 0 1.2254E+10 977.107332 buffer busy waits 10740450 36 8193235928 762.839167 SQL*Net message from client 180769027 0 9.9561E+10 550.761199 db file sequential read 298968127 0 1.1839E+11 395.99129 enqueue 13500 6435 2036785 150.872963 SQL*Net more data from client 52227948 0 4093231165 78.3724294 free buffer waits16 4 795 49.6875 log file switch completion 804 43 16263 20.2276119 log buffer space977 0 5409 5.53633572 control file single write17 0 51 3 db file parallel write 1749695 0 2935317 1.67761638 db file parallel read 8149 0 13484 1.65468156 log file single write 1024 0 701 .684570313 latch free 20070341616763 1054137 .525221297 log file sync 1366242560 526049 .385033545 SQL*Net message from dblink 1514480 0 451351 .298023744 log file sequential read 405415 0 82877 .204425095 SQL*Net break/reset to dblink10 0 2 .2 log file parallel write 2025192 7 293332 .144841576 SQL*Net break/reset to client 28113 0 3221 .114573329 db file single write320 0 36 .1125 SQL*Net more data from dblink447044 0 11375 .025444923 SQL*Net more data to client11770996 0
Testing
Gene Sais [EMAIL PROTECTED] wrote: another suggestion:disable default roles.grant create session to all users.use application to enable roles with password.hth,gene [EMAIL PROTECTED] 08/09/02 01:58PM Why to find who and when. the best thing is to restrict the access.-Original Message-Sent: Friday, August 09, 2002 10:44 PMTo: Multiple recipients of list ORACLE-LUse a logon trigger to capture everything from v$session and you can look atprogram name etc...it will be pretty easy to figure out who and when.Something like this in the trigger...select distinct sid into l_sid from v$mystat;insert into session_log (select * from v$session where sid = l_sid;Ethan Postperotdba (AIM), epost1 (Yahoo)-Original Me! ! ssage-Sent: Friday, August 09, 2002 11:49 AMTo: Multiple recipients of list ORACLE-LFolks,Before I go off re-inventing the wheel once again I'll ask the group isanyone has tried this before. What I have is a request from damanagement totell them when someone connects to our PeopleSoft database using the schemausername, but outside of PeopleTools. The reason is that there have beensome"unexplained" changes to data that have occurred over the last month that iscausing a pile of concern. It is believed that someone who has the schemapassword is using SQL*Plus or Toad to update the data when they should notbedoing so. Now auditing connects for the schema account is not a problem,butdetermining which are suspicious and which are due to the damned PeopleSoftpanel processor I can't see a way around easily from sys.aud$. Anyone elsebeenthere, done that??Dick Goulet-- 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-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).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, EthanINET: [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).--Please see the official ORACLE-L FAQ: http://www.orafaq.com --Author: Naveen NahataINET: [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).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Gene SaisINET: [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). Yahoo! Digital How To - Get the best out of your PC!
T3's, NetApps, Tuning, Wife's Opinion and other fun
Hi All, Well after 6 wekks of testing here is the basic way to operate SUN T3's as efficiently as possible. Be prepared for arguments with High priests from the cult of SAME. SUN T3's are fiber attached hardware RAID 5 arrays with a modern cache. The hardware engineers argue that if you need more I/Os/sec just add another array as a concatenated volume. The theory being the hardware is intelligent enough to use the cache to increase throughput. It actually works as they claim. Never did explain why it wasn't a single point of failure in the end though. My hardware was 3 4810's, each with 4 - 8 cpus, each with 4 - 8GB, 2 - 4 bricks per machine First insist that multiple bricks be mounted on at least 2 mount points. (D2 and D3). DO NOT USE the forcedirectio option. I don't know why but I have been unable to take less than a 40% throughput hit with it turned on. And I don't care what other people say, no matter how much respect I have for them Insist on at least one JBOD for oracle binaries and configs Insist on at least one JBOD for redo logs (D1) This a bare minimum. One set of redo on D1 One set of redo on D2 Archive logs, Rollback and Temp on D3 All data files where needed on D2 Next Level up Add another JBOD for redo and move redo on to it Move Rollback and Temp to D2 At this point to get more throughput you have to take the JBOD to raw devices. Or try forcedirectio on these devices :) If even better performance is needed, more JBOD, for rollback and redo. If more disk spaces is needed, get another brick. Which leads me to the recent discussion on proper way to tune Huh? Why make it so complex? Tuning from a blue collar DBA perspective: Assess the machine first No matter what your ratios or what your waiting for: sar to see if the machine is ever pinned vmstat to see your queues and paging iostat to see disk activity top at timed intervals to catch rogue jobs read your logs and config files Then talk to the users Is the system slow or is it specific jobs? log on run ratio reports and query v$system_event Any ratio that is out of range needs to be tuned: Especially disk sorts to memory sorts For the infamous buffer cache ratio: 10% throw memory at it 97% take memory away For wait states here's a quick drive through for those who look at the number and say Yeah but what do they mean Time WaitTotal Time Average Event # Waits Timeout In HndrdsTime -- - --- -- SQL*Net more data to client # 0 680421.005 SQL*Net message to client # 0 17590 0.000 SQL*Net message from client # 0 3953399703 35.511 - These are all communication to the client. ignore db file sequential read39562523 0 12300885.311 - Data read, 0.0003 seconds average wait, ignore. This number will climb if - IO is bottlenecked or inappropriate (ie using FTS for joins) rdbms ipc message 12440441 ### 2774129387 222.993 - Internal machine communication ignore db file scattered read 12264223 0 6202885.506 - Data read, 0.0005 seconds average wait, ignore. This is higher due to type of read. - Increase in this time indicates an IO bottleneck log file parallel write 4724477 67 2212249.468 log file sequential read2097709 0 1712615.816 - Redo logs, with 2 pure raw JBOD I have got this down to about 0.25 hundredths buffer busy waits 1548548 0 408235.264 - Memory latch contention 0.0002 seconds ignore - If Timeout or average increase, need to determine why contention is increasing control file parallel write 669234 0 376491.563 pmon timer 662092 662074 203382329 307.181 - Internal waits ignore direct path read 573442 0 423920.739 - Reads from tempfiles (sorting). Each segment is 10M in this db so ignore. log file sync551716 15 459036.832 - See redo above. db file parallel write 201166 0 610793 3.036 - writing updates and inserts 0.003 seconds ignore undo segment extension 100516 100507 27 0.000 - Don't know what this is, hope it's
Re: LMT and what is the Bitmap Header Size?
Rajesh.Rao£¬ hi, you can dump the file header and look at its contents, it can be easily found out. 2002-08-08 10:36:00 You wrote: I have been struggling to find the right answer to the question: What is the bitmap header size for a uniform extents LMT? 64K, 1 block, 2 blocks I find one note on Metalink (Note: 111666.1) that says its 64K . Mr.Jonathan Lewis says A quirky little detail about bitmap sizes also came up recently on the Oracle-L mailing list. If you define a very small tablespace - in this context 'very small' means something between 5 blocks and '64K plus one extent' then Oracle will give you a bitmap of just one block - which still allows you to grow the file quite comfortably, of course. Then another example on Metalink (Note: 109630.1) which goes: SQL create tablespace mult 2 datafile '/oracle2/OFA_base/u02/oradata/V816/mult1.dbf' size 100k, 3 '/oracle2/OFA_base/u02/oradata/V816/mult2.dbf' size 100k 4 extent management local uniform size 50K; Tablespace created. SQL select tablespace_name,file_id,block_id,blocks 2 from dba_free_space 3 where tablespace_name='MULT' ; TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS -- -- -- -- MULT 15 4 25 MULT 16 4 25 BLOCK_ID=4 : In an ordinary datafile, the first block where to store data is block 2. Therefore, two more blocks are reserved for the header bitmap of the locally managed datafile. BLOCKS=25 : The datafile has a size of 100K which equals 50 blocks in this database = There are only 25 blocks (local uniform size 50K)left for data starting at block n°4, then 22 blocks are unused. = Header bitmap = 2 blocks for each datafile Moi Confused. Whats the definite word on this? Should be something like if the datafile size is x, then its n, else its some other number. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL
Use Raw partition or LVM in rac installation?
hi, I am trying to setup a RAC database with two DELL 6650 and a disk array, with two eth card, one for public network and the other for heart beating message and sga data transfer. I have two questions: 1. For oracle ceritified combinations, there should be 2 gigabytes network card and one fiber channel switch , which is used to transfer sga data between instances. In my case, i do not have more than 2 nodes, so i directly interconnect the two nodes with general network line, is it ok to let it work with the cache fusion? 2. I am using redhat advanced server, and i am not familier with this product. In my default installation, there is no LVM manager. I read several document talking about install RAC on linux, all using LVM manager. So, shall i use logical volumn manager, or directly use raw partitions? Thanks for your advice. Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chaos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
EMC and AIX resources
I'll be starting a new job next month and consequently startingat the beginning of a learning curve for some products. Just wondering if anyone has any good resources or comments for what to watch for with AIX (most of my experience so far has been with Sun Solaris. I do know about http://bhami.com/rosetta.html), and EMC Symmetrix. I know I've seen a bunch of comments in the past of DBA vs SA preferences with EMC, but I haven't saved them (time to hit the archives). Thanks. Henry
Re: Explain Plan Question
No, it isn't correct. Explain plan gives you precisely what it should, and that is the access plan. Here is an excerpt from the documentation: The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan. It shows the following information: * An ordering of the tables referenced by the statement * An access method for each table mentioned in the statement * A join method for tables affected by join operations in the statement * Data operations like filter, sort, or aggregation In addition to the row source tree, the plan table contains information about the following: * Optimization, such as the cost and cardinality of each operation * Partitioning, such as the set of accessed partitions * Parallel execution, such as the distribution method of join inputs *** So, running the explain plan takes into account nothing. It displays the chosen access path. If you're not satisfied, you can always use subtle hints. Oracle optimizer takes everything into account and always gives you the ideal execution plan (or at least, it will, starting with the version 99i). On 2002.08.12 17:13 Jay Wade wrote: Hello: I have to looking through Metalink but am unable to find the following. Does running Explain Plan take into Account the Cost Generated By using PL/SQL Functions? For example Select * from EMP where EMPID=FUNCTION_GET_ID; I do not believe that it does since the Function SQL does not seem to be included in the Plan Output. Is this correct? Regards, Jay _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SAN
All I have a meeting tomorrow where I am going to point out why SAN and Oracle does not go very well together. Here are my thoughts. Can you pick holes in this argument, modify it or suggest any changes TIA Babu SAN and Oracle ? Conflicting IO behavior * There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files * Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. * SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) * SANs do *Read Ahead*. If one block is requested, they read more than one blocks *while at the disk* hoping that the same process will request the other blocks some time soon. Here is the conflict. * When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. * Why *read ahead* will cause a conflict : * The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+-+-+-| | | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | | 3.4 | | | | | | | | | | | | | |-+-+-+-+-+-+-+-+-+-| * The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) * Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. * The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never *read ahead* intelligently. Why the buffer of SAN has very little impact w.r.t Oracle read performance? * Oracle has its own buffering for all IO types * DBWR reads and writes uses the DB Buffer Cache * LGWR uses the Log buffer * Db buffer Cache is managed by a LRU Algorithm (Touchcount from 9I). * Bulk of the IO done by Oracle is Logical IO (LIO) and not Physical IO (PIO). * Assume the buffer cache hit ratio is 80%. This means that only 20% of the IO calls are PIO. Only 20% of the calls ever hit the SAN's cache. Since this 20% is probably the least requested/never requested data (going by Oracle's LRU algorithm) , its quite likely that the SAN's buffers don't have this either. * Given that Oracle is going to cache even this 20% in its buffers, the next PIO call is going to be for something totally different ? which is not there in the SAN's buffer. * Couple this with the read-ahead (discussed earlier), Our SAN's buffer is now populated with lots of data that Oracle might never use a PIO to retrieve. * Thus the SAN's buffer can never really provide to Oracle the data it reads most ? Its already there in Oracle. To be fair, SAN's huge buffers will come as a boon to small databases ? where the entire database can be cached in the SAN's buffers. -- 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-12571 error while EXPORT
Guys, i am on 816/win2k. while exporting a table with 6.5 lac records,i found the error below in my TRC file.The EXP process did not end. it was going on and on ksedmp: internal or fatal error ORA-12571: TNS:packet writer failure why is it so ? what causes this problem and how do i resolve this ? TIA. __ Give your Company an email address like ravi @ ravi-exports.com. Sign up for Rediffmail Pro today! Know more. http://www.rediffmailpro.com/signup/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
