Problem with netasst dbastudio
recently i posted mail here about problem installing oracle 8.1.7 on mandrake 9.0. well i managed to install it - it was a mising file when installing the glibs patch. now i have created a database, created listener, configured naming methods and everything. finally i imported a entire database from another server. the db is mounted and works fine. the problem now is i can't start netasst or dbastudio. everything else works - the dbaasst, workheet, netca etc. when i write in the console 'oemapp dbastudio' or 'netasst' it just holds on and nothing till i press Ctrl+C. i think there may be some problem with the JRE. i didn't manage to run the installer in the begining of the installation and i downloaded a JRE from IBM - it was theonly way it worked. may be now the problem is with the ibm's jre - i don't know. any sugestions?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Milen Pankov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
error configuration database oracle 9.2.0 on Redhat 7.3
hi all, I need your help, I have a problem while configuration database progress. while progress until 46% I got a error message like this : ORA-12705: invalid or unknown NLS parameter value specified after that I can continue the configuration. anybody could help me? Thank you Regards, pomin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: pomin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Buffer_Pool KEEP
Title: Buffer_Pool KEEP Hi all, Is there any way / script to find whether the tables kept in KEEP Buffer pool performing fine i.e. do they really need to be in the KEEP buffer pool or should they be removed. Tables that I want to check are in the buffer pool for over 6 months and I want to know if I should let them be there or be removed from there. Regards, Hussain DBA SKMCHRC
Re: can't create database
Am Mit, 2003-03-12 um 21.14 schrieb [EMAIL PROTECTED]: I've had this problem on linux. Best and fastest solution: reboot the server. There is a memory issue preventing you from starting oracle. I just had it happen about 2 minutes ago on an upgrade to 9i. Reboot, and now I can start the db. -Candi On Wed, 2003-03-12 at 12:07, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 17.20 schrieb Ray Stell: On Wed, Mar 12, 2003 at 07:19:53AM -0800, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 10.28 schrieb Christoph Schwerdtfeger: Am Die, 2003-03-11 um 19.47 schrieb Alan Davey: I had the same error message on NT with Oracle 9.2 recently. The first problem was that the init_sid.ora file was created a directory different from where the db create scripts were looking for it. The second problem was that one of the init parameters was for Enterprise Edition and I was installing Standard Edition. Both problems resulted in the message of 'not connected to oracle'. Come to think of it, I don't think I've ever had a version of the DB Creation wizard work without getting some error. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/11/2003 12:44 PM, Stahlke, Mark [EMAIL PROTECTED] wrote: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message- From: Milen Pankov [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: can't create database can't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Hi there. I have a similiar problem with Oracle 9.2.0.1.0 under Linux (Debian, Woody, Kernel 2.4.20-686-smp). The installation worked fine - at least no error message was shown, but when I start the dbca to create a database, I get an ORA-03113. I've searched over the internet (newsgroups and webpages), but I didn't find a solution for my problem. I searched for the libc-patch, but I think it's deprecated for Oracle 9i. Many ppl said, like you, I just should generate the scripts with the wizard and create the database manually, so I did - or at least I tried. So, I just wanted to test, if the login works, but it didn't. Is startet sqlplus /nolog and tried connect SYS/change_on_install as SYSDBA (conn / as SYSDBA has the same effect, ORACLE_SID set as said in the script) and I get: Connected to an idle instance. Ok, I think this is just normal and I tried to start the database using startup nomount pfile=Correct path to init.ora; And then, all I get is an ORA-03113: end-of-file on communication channel If I try to do a conn / afterwards, I get ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Any suggestions, what could be wrong? I installed the Enterprise Version, so it can't be an Enterprise setting ... it has to be a problem in the init.ora, right? System specs if required: Pentium II, 800 (2x - multi processor) 1024MB RAM 4GB Swap 19GB diskspace free Debian Linux (woody) Kernel 2.4.20-686-smp Any help would be appreciated. -- Christoph Schwerdtfeger [EMAIL PROTECTED] SoftConcept GmbH Borriesstrasse 35 D-32257 B?nde Tel:(05223) 4970-20 E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Ok, I just deinstalled Oracle 9i and installed 8.1.7 which ran - after applying the patch - without any problems ... dunno why. Maybe I should try to apply the 8.1.7-glibc patch to the 9i? Try Oracle 9.2.0. I have installed it on many linux platforms without any tricks. I think you said you were using 9.0.x. I never had any desire to go there, so I don't know what may be at issue. I AM using Oracle 9.2.0 - or trying to use it. As I wrote, Oracle 8i (8.1.7) worked without any problems, but 9i (9.2.0.1.0 - EE) is a pain in the a$$. I do it the same way as with the 8.1.7. I don't have any problems during the installation (ok, a problem with the ins_ctx.mk, but this is fixed by adding $(LDLIBFLAG)dl). I use the dbca to create a script for the database and login via sqlplus /nolog and
Ftp command
Hallo, anyone whom can tell me how to write ftp command to connect to a port at an ip-address? Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Using the /*+ append */ insert hint
Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy
So what are you doing this afternoon after you have mastered the SQL Server gui this morning? -Original Message- Sent: 15 March 2003 09:44 To: Multiple recipients of list ORACLE-L I put them on http://www.vanzanen.com/rman.zip They are oracle 8.0 (if memory serves me right) so they won't work with 9i. I'll see if I can find the time to do the same for 9i one of these days (have to learn SQL Server first) Jack -Original Message- Sent: vrijdag 14 maart 2003 20:24 To: Multiple recipients of list ORACLE-L I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Using the /*+ append */ insert hint
I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported. For example: (this is the exact text of a query against an 8.0.6 instance) SQL select /*+ BADHINT */ * from dual; D - X SQL Darrell [EMAIL PROTECTED] 03/17/03 04:23AM Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy
Good one. :-) Jack -Original Message- Sent: maandag 17 maart 2003 11:29 To: Multiple recipients of list ORACLE-L So what are you doing this afternoon after you have mastered the SQL Server gui this morning? -Original Message- Sent: 15 March 2003 09:44 To: Multiple recipients of list ORACLE-L I put them on http://www.vanzanen.com/rman.zip They are oracle 8.0 (if memory serves me right) so they won't work with 9i. I'll see if I can find the time to do the same for 9i one of these days (have to learn SQL Server first) Jack -Original Message- Sent: vrijdag 14 maart 2003 20:24 To: Multiple recipients of list ORACLE-L I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
RE: Comparing strings whilst ignoring some characters
Title: RE: Comparing strings whilst ignoring some characters Mark, you should look at replace() function ... not translate ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED]] Sent: Monday, March 17, 2003 12:40 AM To: Multiple recipients of list ORACLE-L Subject: Comparing strings whilst ignoring some characters Hi Everyone, I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time... I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that the cat equals thecat. I thought I used the translate function, but a quick inspection didn't reveal how. Thanks in advance, Mark. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Comparing strings whilst ignoring some characters
Mark, Use TRANSLATE to convert the characters you don't want to whitespace and then use REPLACE to remove the whitespace. 1 select 'the 9 cats', replace(translate('the 9 cats', '0123456789', ' '), ' ') 2* from dual SQL / 'THE9CATS' REPLACE -- --- the 9 cats thecats -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Mark Richard wrote: Hi Everyone, I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time... I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that the cat equals thecat. I thought I used the translate function, but a quick inspection didn't reveal how. Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: Backup Strategy
John, teh scripts are still there (with a small correction) :) http://www.oracle-dba.com/rman/ Milen -Ursprüngliche Nachricht- Von: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED] Gesendet: Montag, 17. März 2003 11:29 An: Multiple recipients of list ORACLE-L Betreff: RE: Backup Strategy So what are you doing this afternoon after you have mastered the SQL Server gui this morning? -Original Message- Sent: 15 March 2003 09:44 To: Multiple recipients of list ORACLE-L I put them on http://www.vanzanen.com/rman.zip They are oracle 8.0 (if memory serves me right) so they won't work with 9i. I'll see if I can find the time to do the same for 9i one of these days (have to learn SQL Server first) Jack -Original Message- Sent: vrijdag 14 maart 2003 20:24 To: Multiple recipients of list ORACLE-L I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kulev, Milen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
AW: Problem with netasst dbastudio
Hi Milen. I had the same problemson my Linux box. On the console type ps -ef | grep jre . Normally I see a lot of zombie jre's [defunc]. Kill them all wich killall -9 jre (I hope that no other application using JRE is running at that time ;) ). Then try to start netasst or dbastudio again. It worked for me. HTH. Another Milen ;) -Ursprüngliche Nachricht- Von: Milen Pankov [mailto:[EMAIL PROTECTED] Gesendet: Montag, 17. März 2003 08:49 An: Multiple recipients of list ORACLE-L Betreff: Problem with netasst dbastudio recently i posted mail here about problem installing oracle 8.1.7 on mandrake 9.0. well i managed to install it - it was a mising file when installing the glibs patch. now i have created a database, created listener, configured naming methods and everything. finally i imported a entire database from another server. the db is mounted and works fine. the problem now is i can't start netasst or dbastudio. everything else works - the dbaasst, workheet, netca etc. when i write in the console 'oemapp dbastudio' or 'netasst' it just holds on and nothing till i press Ctrl+C. i think there may be some problem with the JRE. i didn't manage to run the installer in the begining of the installation and i downloaded a JRE from IBM - it was theonly way it worked. may be now the problem is with the ibm's jre - i don't know. any sugestions?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Milen Pankov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kulev, Milen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Comparing strings whilst ignoring some characters
How about: REPLACE('the cat', ' ', '') Istifad -Original Message- Sent: 17 March 2003 05:40 To: Multiple recipients of list ORACLE-L Hi Everyone, I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time... I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that the cat equals thecat. I thought I used the translate function, but a quick inspection didn't reveal how. Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Virk, Istifad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using the /*+ append */ insert hint
From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint, I've seen as much as a 50% reduction in execution time. Adding the append hint to single-row inserts not only wastes space but generally slows things down. Kevin -Original Message- Sent: Monday, March 17, 2003 7:29 AM To: Multiple recipients of list ORACLE-L I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported. For example: (this is the exact text of a query against an 8.0.6 instance) SQL select /*+ BADHINT */ * from dual; D - X SQL Darrell [EMAIL PROTECTED] 03/17/03 04:23AM Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sizing Indexes - URGENT
Jai I couldn't follow all the details of your calculation. I would have expected to see a calculation for the number of rows per block. Once you know how many rows you can pack into a block, then you can estimate the number of blocks you will need. Maybe you have it in there and I just couldn't see it this early on a Monday morning. As a sanity check, do the simple bytes/row * no. of rows and make sure you are reasonably close. Looking at the bigger picture, I wouldn't get too picky about this. Usually your number of rows is only a guess anyway. Spend your time looking at LMT and uniform extents. Oracle is trying to save us from time-consuming tasks like this. :-) Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:09 PM To: Multiple recipients of list ORACLE-L Dear All, I have a task on hand to size a database for our application. I have used the Oracle rule to find out the rowsize of rows in a table. 1.Space available for the data (SAD) = data block size - block header size = db_block_size - kcbh - ub4 - ktbbh - (initrans -1)* ktbit - kdbh 2.Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) - kdbt 3.Space used per row (SPR) = (Total size of the columns data length) + (1 byte per column with length 250 )+ (3 * 1 byte per column with length = 250) 4.Row Size (ROWSZ) = 3 * ub1 + SPR 5.Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2 6.Total Size of the table = Expected Number of records * SPROW I hope this formula is correct. How can I do the sizing of indexes. Shouldn't I take into account the 10 bytes consumed by the ROWID pseudocolumn apart from the column(s) that are indexed ? TIA for all your help. Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Buffer_Pool KEEP
Hussain I wish there was. I hope someone will reply with a good method. First, add up all the blocks for the tables and their indexes you have assigned to the KEEP pool. Some of them may have grown over the past months and you may not have enough space allocated for all of them. Check the BHR for the KEEP pool to see what hit ratio you are getting. It should be 100%. Past that, you can consider the tables you have assigned to the KEEP pool and other small tables that you haven't assigned to the KEEP pool to decide if you have the best candidates assigned. You are looking for small tables that are accessed a lot. Based on your analysis and the available memory, you may decide to increase or reduce the size of the KEEP pool. Again, I really hope you (and I) receive a better reply. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 2:14 AM To: Multiple recipients of list ORACLE-L Hi all, Is there any way / script to find whether the tables kept in KEEP Buffer pool performing fine i.e. do they really need to be in the KEEP buffer pool or should they be removed. Tables that I want to check are in the buffer pool for over 6 months and I want to know if I should let them be there or be removed from there. Regards, Hussain DBA SKMCHRC -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using the /*+ append */ insert hint
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Connor McDonald Sent: Monday, March 17, 2003 13:34 To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor Thanks Connor (and Darrell in the previous post). Connor, I saw a post that suggested you might be heading back to Oz - is that true? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Comparing strings whilst ignoring some characters
translate the chars you want to ignore to (say) '@' then replace the @ with null hth connor --- Mark Richard [EMAIL PROTECTED] wrote: Hi Everyone, I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time... I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that the cat equals thecat. I thought I used the translate function, but a quick inspection didn't reveal how. Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: db file scattered read
If 14706+3 is one extent and another extent begins at 14710, it will NOT read 14706+8. A DB_FILE_MULTIBLOCK_READ will not span extents. Hemant At 09:04 AM 14-03-03 -0800, you wrote: Here is a part of trace file . I am finding that oracle is trying to read 8 or 3 or 7 blocks at a time . But block numbers are all sequential i.e. it will read 3 blocks starting from 14706 and then 8 blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks always it multi_block_read is set to 8 ? Any Idea . Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 WAIT #32: nam='db file scattered read' ela= 2 p1=4 p2=14718 p3=8 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14727 p3=3 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14731 p3=7 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14739 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14744 p3=8 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=129784 p3=8 Thanks -ak Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Using the /*+ append */ insert hint
APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor --- Grant Allen [EMAIL PROTECTED] wrote: Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using the /*+ append */ insert hint
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Toepke, Kevin M Sent: Monday, March 17, 2003 13:24 To: Multiple recipients of list ORACLE-L Subject: RE: Using the /*+ append */ insert hint From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint, I've seen as much as a 50% reduction in execution time. That's pretty much what we're targeting. Bulk inserts using insert ... select ... , possibly in the millions of rows. That kind of speed increase would be good. Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sizing Indexes - URGENT
Are you actually going to use the formula ? And I thought that Oracle had dropped the formua from recent versions of its RDBMS documentation [8.1 and upwards]. The only real way you can get an approximation [not the exact projection] of the size is to load some data and then analyze the tables. Else, use some fudge factors. Hemant At 08:08 PM 16-03-03 -0800, you wrote: Dear All, I have a task on hand to size a database for our application. I have used the Oracle rule to find out the rowsize of rows in a table. 1.Space available for the data (SAD) = data block size block header size = db_block_size kcbh ub4 ktbbh (initrans -1)* ktbit kdbh 2.Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) kdbt 3.Space used per row (SPR) = (Total size of the columns data length) + (1 byte per column with length 250 )+ (3 * 1 byte per column with length = 250) 4.Row Size (ROWSZ) = 3 * ub1 + SPR 5.Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2 6.Total Size of the table = Expected Number of records * SPROW I hope this formula is correct. How can I do the sizing of indexes. Shouldn't I take into account the 10 bytes consumed by the ROWID pseudocolumn apart from the column(s) that are indexed ? TIA for all your help. Best Regards Jai Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-27100: shared memory realm already exists
I am getting the message shown below when running the cron job which restarts the database after shutdown for a cold backup: SVRMGR ORA-27100: shared memory realm already exists IBM AIX RISC System/6000 Error: 17: File exists Operating system is AIX 4.3 Oracle version is 8.1.7. When I list my shared memory segments I get: IPC status from /dev/mem as of Mon Mar 17 09:57:11 EST 2003 TID KEYMODE OWNERGROUP ATIMEDTIME CTIME Shared Memory: m 0 0x5805091f --rw-rw-rw- root system 6:31:15 no-entry 6:31:15 m 1 0x47041181 --rw-r--r-- imnadm imnadm 6:32:20 6:32:20 6:32:20 m 2 0x58041181 --rw-r--r-- imnadm imnadm 6:32:20 6:32:20 6:32:20 m 3 0x4d041181 --rw-r--r-- imnadm imnadm 6:32:20 6:32:20 6:32:20 m 4 0x49041181 --rw-r--r-- imnadm imnadm 6:32:20 6:32:20 6:32:20 m 5 0x50041181 --rw-r--r-- imnadm imnadm 6:32:20 6:32:20 6:32:20 m 6 0x45041187 --rw-rw-rw- imnadm imnadm 6:32:24 6:32:25 6:32:23 m 7 0x43041187 --rw-rw-rw- imnadm imnadm 6:32:24 6:32:25 6:32:23 m 8 0x42041187 --rw-rw-rw- imnadm imnadm 6:32:24 6:32:25 6:32:23 m 9 0x41041187 --rw-rw-rw- imnadm imnadm 6:32:24 6:32:25 6:32:23 m10 0x44041187 --rw-rw-rw- imnadm imnadm 6:32:24 6:32:25 6:32:23 m 1048587 0x843c4ab5 --rw-r- oracle oinstall 9:48:58 9:48:58 0:30:09 m131084 0x843c4ab6 --rw-r- oracle oinstall 9:48:58 9:48:58 0:30:09 m 1441805 0x0003a68c --rw-r- oracle oinstall 9:57:01 9:57:02 23:30:02 m262158 0x843c4ab4 --rw-r- oracle oinstall 9:48:58 9:48:58 0:30:06 m15 0x0d052983 --rw-rw-rw- root system 9:51:17 9:51:18 8:21:18 I have one other Oracle instance running on this box. I believe this instance is using the segment with key = 0x0003a68c, based on the fact that it's cron job restarts it at 23:30 while the other data base is supposed to restart at 0:30. So my questions are: 1. What is causing this problem and how can I prevent it? 2. Is there any way I can tell for certain which segment is being used by which instance? (The other instance on the box is production and a mistake might cost me a few friends.) Thanks, Peter Schauss Northrop Grumman Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-27100: shared memory realm already exists
Title: RE: ORA-27100: shared memory realm already exists Firstly , make sure that when you shutdown, the database is actually shutdown ... my guess is the db isn't shutting down. (Thanks to Kyle) use http://oraperf.sourceforge.net/SGA/sgaid.sh to see which shared memory segment is used by which instance. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Schauss, Peter [mailto:[EMAIL PROTECTED]] Sent: Monday, March 17, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Subject: ORA-27100: shared memory realm already exists So my questions are: 1. What is causing this problem and how can I prevent it? 2. Is there any way I can tell for certain which segment is being used by which instance? (The other instance on the box is production and a mistake might cost me a few friends.) Thanks, Peter Schauss Northrop Grumman Corporation *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Sizing Indexes - URGENT
Jai, The formula you are using is reasonably accurate, but not very useful. One of the main problems I have found with this approach is that the number of rows is not usually known. The business side and designers might have an idea of data sizes, but the reality is that most times they are not accurate. This makes a fine-grained sizing approach ultimately innacurate. Use locally managed tablespaces and create estimated sizes. Add in enough space for variances and keep an eye on them. When in doubt, err on the side of overallocation. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals [EMAIL PROTECTED] wrote: Dear All, I have a task on hand to size a database for our application. I have used the Oracle rule to find out the rowsize of rows in a table. 1.Space available for the data (SAD) = data block size block header size = db_block_size kcbh ub4 ktbbh (initrans -1)* ktbit kdbh 2.Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) kdbt 3.Space used per row (SPR) = (Total size of the columns data length) + (1 byte per column with length 250 )+ (3 * 1 byte per column with length = 250) 4.Row Size (ROWSZ) = 3 * ub1 + SPR 5.Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2 6.Total Size of the table = Expected Number of records * SPROW I hope this formula is correct. How can I do the sizing of indexes. Shouldn't I take into account the 10 bytes consumed by the ROWID pseudocolumn apart from the column(s) that are indexed ? TIA for all your help. Best Regards Jai
keep pool
Hi List , I have a 8.1.6 db running on hp-ux 2 cpu . currently it has default db block lru latches ( i guess this should be 1 since oracle sets it to cpu/2 ) . Now I want to configure keep pool . Which will requires at least 1 lru latches . So I need to specify db block lru latches 1 ( rite ? ) . Now my question is what should be a good value for db block lru latches and keep pool lru lactes . I have 5 blocks for db buffers (8K) in total , out of which i am initiallly planning to take 8000 for keep pool . what other consideration i should keep in mind before i set keep pool . -thanks ak
RE: Excessive SQL*Net message from client waits
Title: RE: Excessive SQL*Net message from client waits I recently worked on tracing and tuning a process where developer retrieved one row, did a bunch of pl/sql stuff and update ... lather, rinse and repeat. There were a lot of SQL*Net message from/to client. I finally opened up the procedure, found that 90% plus stuff, which was being done in pl/sql could be done in the main SQL (select statement) itself, we tested it and the developers is happy since then. It cut down a lot of wait time ... we didn't go for a bulk process as it was a lot of XML and CLOB processing involved in there. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Standby errors
Rather than doing an ALTER SYSTEM SWITCH LOGFILE only, issue an ALTER SYSTEM ARCHIVE LOG NEXT I actually do an ARCHIVE LOG NEXT, SWITCH LOGFILE and also have a sleep in the backup-script, which then proceeds to copy all but the *last* archivelog out to tape ! Hematn At 06:19 AM 14-03-03 -0800, you wrote: Oracle 8.1.6 on Solaris 5.8. We have a read-only standby database for one of our production databases. Each night, the standby is shutdown and the previous days archive logs from production are applied. Then the database is brought backup in read-only mode. To get the archive logs, a hot backup is done on the production database. As the last step is the hot backup, a log switch is done and then the archive logs are ftp'd to the server where the standby is. After the hot backup completed yesterday, the log switch occurred, and the logs sent, but when an attempt was made to apply the archive logs we got an error: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '/orabackup/archive/TBSPRD/arch1352.arc' ORA-332 signalled during: ALTER DATABASE RECOVER In looking at the archive log, both on the production and standby servers, they are the same size - 16k (the block size for the db is 8k). The next log is 8k in size and then there is another that is 16k before we see any that are normal sized. These would have been the first logs _after_ the hot backup the night before. In the alert log for the production db, it appears the log 1353 was archived _before_ 1352. Has anyone seen this behavior before? Does anybody have any idea why it happened in the first place? Is there something we can do to make sure it never happens again? P.S. We are upgrading to 9.2 this weekend, if that makes any difference. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Client Software Version
You can still use the 8i client. You _should_ plan and start upgrading your clients to 9i. If you were running TAF in 8iOPS, you should test TAF between the 8iclients and 9iRAC -- although it does work. Hemant At 01:15 PM 10-03-03 -0800, you wrote: We are in the process of moving from 8/8i to 9i RAC, and we have an 8i client installed on all our P.C's (600+) I have tested the 8i client against the 9i database and for a quick login/browse through the application(s) it seems to work with no problems. My question is, should I stay with the 8i client, or should I move to the 9i client before I upgrade/migrate the databases. Is there issues with regards to the client being out of sync with the database. Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using the /*+ append */ insert hint
The append hint works since Oracle 8 if my memory is good. I've used the append hint with tables in nologging mode to load a lot of data very fast. It works well. You must do a backup afterwards. If the hint is not supported, it should be ignored, like when you make a typo in the hint's name. Stephane -Original Message- Sent: Monday, March 17, 2003 5:24 AM To: Multiple recipients of list ORACLE-L Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Buffer_Pool KEEP
Title: Buffer_Pool KEEP how about looking at v$bh and match the objectid with dba_tables (objects) . Also look at v$sgastat for keep pool . -ak - Original Message - From: Hussain Ahmed Qadri To: Multiple recipients of list ORACLE-L Sent: Monday, March 17, 2003 12:13 AM Subject: Buffer_Pool KEEP Hi all, Is there any way / script to find whether the tables kept in KEEP Buffer pool performing fine i.e. do they really need to be in the KEEP buffer pool or should they be removed. Tables that I want to check are in the buffer pool for over 6 months and I want to know if I should let them be there or be removed from there. Regards, Hussain DBA SKMCHRC
Re: AW: Backup Strategy
Not entirely(as the owner of that website). there is a .zip file there, it has a key, it was placed there for someone and right now, i'm not ready to give out the key, so dont bother downloading the .zip file. The work is still in progress and is not quite ready for prime time. joe John, teh scripts are still there (with a small correction) :) http://www.oracle-dba.com/rman/ Milen -Ursprüngliche Nachricht- Von: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED] Gesendet: Montag, 17. März 2003 11:29 An: Multiple recipients of list ORACLE-L Betreff: RE: Backup Strategy So what are you doing this afternoon after you have mastered the SQL Server gui this morning? -Original Message- Sent: 15 March 2003 09:44 To: Multiple recipients of list ORACLE-L I put them on http://www.vanzanen.com/rman.zip They are oracle 8.0 (if memory serves me right) so they won't work with 9i. I'll see if I can find the time to do the same for 9i one of these days (have to learn SQL Server first) Jack -Original Message- Sent: vrijdag 14 maart 2003 20:24 To: Multiple recipients of list ORACLE-L I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an
RE: can't create database
Hi, This site has a good description of how to use the ipcs and ipcrm commands to clean up these left-over memory segments following the shutdown. I've found that using the technique shown on this site minimizes the need to reboot when this problem occurs. Hope this helps, Joe Johnson, OCP Senior Database Administrator Greenbrier Russel, Inc. Madison, Wisconsin USA Let us linger here a while in the foolishness of things -Unknown -Original Message- From: Schwerdtfeger, Christoph [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Subject: Re: can't create database Am Mit, 2003-03-12 um 21.14 schrieb [EMAIL PROTECTED]: I've had this problem on linux. Best and fastest solution: reboot the server. There is a memory issue preventing you from starting oracle. I just had it happen about 2 minutes ago on an upgrade to 9i. Reboot, and now I can start the db. -Candi On Wed, 2003-03-12 at 12:07, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 17.20 schrieb Ray Stell: On Wed, Mar 12, 2003 at 07:19:53AM -0800, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 10.28 schrieb Christoph Schwerdtfeger: Am Die, 2003-03-11 um 19.47 schrieb Alan Davey: I had the same error message on NT with Oracle 9.2 recently. The first problem was that the init_sid.ora file was created a directory different from where the db create scripts were looking for it. The second problem was that one of the init parameters was for Enterprise Edition and I was installing Standard Edition. Both problems resulted in the message of 'not connected to oracle'. Come to think of it, I don't think I've ever had a version of the DB Creation wizard work without getting some error. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/11/2003 12:44 PM, Stahlke, Mark [EMAIL PROTECTED] wrote: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message- From: Milen Pankov [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: can't create database can't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Hi there. I have a similiar problem with Oracle 9.2.0.1.0 under Linux (Debian, Woody, Kernel 2.4.20-686-smp). The installation worked fine - at least no error message was shown, but when I start the dbca to create a database, I get an ORA-03113. I've searched over the internet (newsgroups and webpages), but I didn't find a solution for my problem. I searched for the libc-patch, but I think it's deprecated for Oracle 9i. Many ppl said, like you, I just should generate the scripts with the wizard and create the database manually, so I did - or at least I tried. So, I just wanted to test, if the login works, but it didn't. Is startet sqlplus /nolog and tried connect SYS/change_on_install as SYSDBA (conn / as SYSDBA has the same effect, ORACLE_SID set as said in the script) and I get: Connected to an idle instance. Ok, I think this is just normal and I tried to start the database using startup nomount pfile=Correct path to init.ora; And then, all I get is an ORA-03113: end-of-file on communication channel If I try to do a conn / afterwards, I get ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Any suggestions, what could be wrong? I installed the Enterprise Version, so it can't be an Enterprise setting ... it has to be a problem in the init.ora, right? System specs if required: Pentium II, 800 (2x - multi processor) 1024MB RAM 4GB Swap 19GB diskspace free Debian Linux (woody) Kernel 2.4.20-686-smp Any help would be appreciated. -- Christoph Schwerdtfeger [EMAIL PROTECTED] SoftConcept GmbH Borriesstrasse 35 D-32257 B?nde Tel:(05223) 4970-20 E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Ok, I just deinstalled Oracle
Re: log buffer space
I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full. Set log_buffer to an easy number such as 600K. Create table with one column of a nice large size, e.g. varchar2(1000); Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat. Insert N rows into table. Taks snapshot and find changes. Vary the number of rows inserted until M rows does not result in a redo write M+1 rows results in a redo write. Check the redo size for M and M+1 rows. Under both 8.1.7.4 and 9.2.0.2, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer. (One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 16 March 2003 13:28 Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Excessive SQL*Net message from client waits
Bear in mind that when you are talking about a load process, your client is another computer program, and should not (you hope) need any think time. This is the one case where the SQL*Net message from client is a threat rather than (as statspack puts it, I think) an idle event. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 15 March 2003 06:13 I noticed a similar problem on my last contract assignment. I was lucky as the loading process took 4 hours but only 1 hour was spent by oracle. So we knew it was application that was taking the time. running the application on a faster processor cut the time to 1/3. We also could not find any problem with the network or sqlnet either. Hope this helps Mohammed Shakir -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Autoallocate (was Re: LMT monitoring)
Yet another reason for avoiding Automatic Undo - one little accident can haunt you for ages. It's also a major pain to find out exactly what does go on in extreme cases because of the massive delay between UNDO becoming redundant and smon dropping it. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 04:58 How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file scattered read
How can you tell that DB_FILE_MULTIBLOCK_READ will not span extents? I was unable to confitrm that on Metalink. -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: db file scattered read If 14706+3 is one extent and another extent begins at 14710, it will NOT read 14706+8. A DB_FILE_MULTIBLOCK_READ will not span extents. Hemant At 09:04 AM 14-03-03 -0800, you wrote: Here is a part of trace file . I am finding that oracle is trying to read 8 or 3 or 7 blocks at a time . But block numbers are all sequential i.e. it will read 3 blocks starting from 14706 and then 8 blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks always it multi_block_read is set to 8 ? Any Idea . Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 WAIT #32: nam='db file scattered read' ela= 2 p1=4 p2=14718 p3=8 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14727 p3=3 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14731 p3=7 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14739 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14744 p3=8 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=129784 p3=8 Thanks -ak Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
empty blocks
Does blocks and empty_blocks gives correct info even if I dont run statistics ? thanks, -ak
RE: Sizing Indexes - URGENT
The voice of reason! Thanks Dan. How many times have I been asked for an estimate of an index size (including every tiny overhead and spare byte) without knowing how many rows will be inserted. So they need an exact calculation based on a very rough estimate. As a result my calculation method has evolved into: - calculate the average index entry size. - multiply by the estimated number of rows. - factor in the PCTFREE setting. - add up to half as much again depending on the faith you have in the figures you've been given. Then use the time saved to get on with better value work. Your mileage may vary. Mike Hately -Original Message- Sent: 17 March 2003 14:14 To: Multiple recipients of list ORACLE-L Jai, The formula you are using is reasonably accurate, but not very useful. One of the main problems I have found with this approach is that the number of rows is not usually known. The business side and designers might have an idea of data sizes, but the reality is that most times they are not accurate. This makes a fine-grained sizing approach ultimately innacurate. Use locally managed tablespaces and create estimated sizes. Add in enough space for variances and keep an eye on them. When in doubt, err on the side of overallocation. -- Daniel W. Fink http://www.optimaldba.com http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Dear All, I have a task on hand to size a database for our application. I have used the Oracle rule to find out the rowsize of rows in a table. 1.Space available for the data (SAD) = data block size - block header size = db_block_size - kcbh - ub4 - ktbbh - (initrans -1)* ktbit - kdbh 2.Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) - kdbt 3.Space used per row (SPR) = (Total size of the columns data length) + (1 byte per column with length 250 )+ (3 * 1 byte per column with length = 250) 4.Row Size (ROWSZ) = 3 * ub1 + SPR 5.Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2 6.Total Size of the table = Expected Number of records * SPROW I hope this formula is correct. How can I do the sizing of indexes. Shouldn't I take into account the 10 bytes consumed by the ROWID pseudocolumn apart from the column(s) that are indexed ? TIA for all your help. Best Regards Jai ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. ** ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses. Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277. This e-mail may be sent on behalf of a member of the Innogy group of companies. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
insert stmt disk reads
In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
RE: Sizing Indexes - URGENT
Daniel - Excellently put. The other factor you can usually only estimate is the row size, until you get some test data. The old engineer in me says that if you can only guess at the row size and number of rows, then don't pretend the exact formula is going to do much for you. I started with a slide rule which was only accurate to 2-1/2 significant digits. When people started using calculators, they thought they had 10 significant digits. But if even one of your input variables is only accurate to 2 digits, then that is probably all the accuracy you're going to wind up with. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Jai, The formula you are using is reasonably accurate, but not very useful. One of the main problems I have found with this approach is that the number of rows is not usually known. The business side and designers might have an idea of data sizes, but the reality is that most times they are not accurate. This makes a fine-grained sizing approach ultimately innacurate. Use locally managed tablespaces and create estimated sizes. Add in enough space for variances and keep an eye on them. When in doubt, err on the side of overallocation. -- Daniel W. Fink http://www.optimaldba.com http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Dear All, I have a task on hand to size a database for our application. I have used the Oracle rule to find out the rowsize of rows in a table. 1.Space available for the data (SAD) = data block size - block header size = db_block_size - kcbh - ub4 - ktbbh - (initrans -1)* ktbit - kdbh 2.Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) - kdbt 3.Space used per row (SPR) = (Total size of the columns data length) + (1 byte per column with length 250 )+ (3 * 1 byte per column with length = 250) 4.Row Size (ROWSZ) = 3 * ub1 + SPR 5.Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2 6.Total Size of the table = Expected Number of records * SPROW I hope this formula is correct. How can I do the sizing of indexes. Shouldn't I take into account the 10 bytes consumed by the ROWID pseudocolumn apart from the column(s) that are indexed ? TIA for all your help. Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Jonathan: I have just sent a mail which has the test statistics. I would appreciate your comments on that.. Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8. KG Best Regards, K Gopalakrishnan -Original Message- Lewis Sent: Monday, March 17, 2003 7:14 AM To: Multiple recipients of list ORACLE-L I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full. Set log_buffer to an easy number such as 600K. Create table with one column of a nice large size, e.g. varchar2(1000); Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat. Insert N rows into table. Taks snapshot and find changes. Vary the number of rows inserted until M rows does not result in a redo write M+1 rows results in a redo write. Check the redo size for M and M+1 rows. Under both 8.1.7.4 and 9.2.0.2, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer. (One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 16 March 2003 13:28 Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Urgent!!! Web Form and Report
Hi All, I would really appreciate if someone can help me. I am calling a report from a form. It works well on Client/Server but on the Web it takes lots of time and hangs. Unfortunately it doesn't display any errors. This is how I built the When-button-pressed code: (This is a sample test code..) declare plid PARAMLIST; vRep_server varchar2(30); vRep_format varchar2(30); vForceRunProd varchar2(10); vRep varchar2(30); vConnect varchar2(200); vUrl varchar2(200); vParamTitle varchar2(500); repid report_object; vTomaxKey varchar2(8) := 'ToMaX123'; rep_no number; p_l varchar2(200); v_report_id Report_Object; vc_report_job_id varchar2(100); vc_rep_status varchar2(100); begin if form_success then -- v_report_id := FIND_REPORT_OBJECT('REPORT19'); -- SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_COMM_MODE,SYNCHRONOUS); -- SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESTYPE,CACHE); -- SET_REPORT_OBJECT_PROPERTY(v_REPORT_id,REPORT_DESFORMAT,'PDF'); plid := get_parameter_List('testrep'); if not id_null(plid) then destroy_parameter_list(plid); end if; plid := create_parameter_list('testrep'); rep_no := 32; add_print_params(plid, 'P_TITLE', TEXT_PARAMETER, 'ACCOUNT REPORT'); IF get_application_property(user_interface) = 'WEB' THEN --tchar := b_security.encrypt('ToMaX123', visp.user_id||','||dbuser.encrypt('d3cR',visp.passwd)||','||to_char(tmxgbl.my_site)); --tmx_msg('tchar='||tchar); --tchar := b_security.decrypt('ToMaX123',tchar); --tmx_msg('decrypted tchar='||tchar); add_print_params(plid, 'HHMISS', TEXT_PARAMETER, to_char(sysdate,'HH24MISS')); -- This will enforce no caching. add_print_params(plid, 'RNI',TEXT_PARAMETER, b_security.encrypt(vTomaxKey, visp.user_id||','||dbuser.encrypt('d3cR',visp.passwd)||','||to_char(tmxgbl.my_site))); add_print_params(plid, 'RNRNI',TEXT_PARAMETER, b_security.encrypt(vTomaxKey, to_char(rep_no))); --add_print_params(plid, 'P_REPORT_NO', TEXT_PARAMETER, to_char(rep_no)); -- Removed for Security (mww 3/1/01) --add_print_params(plid, 'P_RNET_USER_ID', TEXT_PARAMETER, visp.user_id); --add_print_params(plid, 'P_RNET_my_site', TEXT_PARAMETER, tmxgbl.my_site); --add_print_params(plid, 'P_RNET_passwd', TEXT_PARAMETER, visp.opaque(visp.passwd)); add_print_params(plid, 'P_USER_INTERFACE', TEXT_PARAMETER, 'WEB'); ELSE --add_print_params(plid, 'RNI',TEXT_PARAMETER, b_security.encrypt('ToMaX123', visp.user_id||','||visp.passwd||','||to_char(tmxgbl.my_site)||','||to_char(rep_no))); if rep_no 0 then add_print_params(plid, 'P_REPORT_NO', TEXT_PARAMETER, to_char(rep_no)); end if; add_print_params(plid, 'P_RNET_USER_ID', TEXT_PARAMETER, visp.user_id); add_print_params(plid, 'P_RNET_my_site', TEXT_PARAMETER, tmxgbl.my_site); add_print_params(plid, 'P_RNET_passwd', TEXT_PARAMETER, visp.passwd); add_print_params(plid, 'P_USER_INTERFACE', TEXT_PARAMETER, 'CS'); END IF; add_parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO'); add_parameter(plid, 'DESTYPE', TEXT_PARAMETER, 'PREVIEW'); add_parameter(plid, 'DESFORMAT', TEXT_PARAMETER, 'PDF'); -- vForceRunProd := get_sys_env('FORCE_RUN_PRODUCT'); TOOL_env.getvar('FORCE_RUN_PRODUCT',vForceRunProd); :parameter.param_list := rtrim(:parameter.param_list, '+'); IF get_application_property(user_interface) != 'WEB' OR vForceRunProd = 'Y' THEN Run_Product(REPORTS,'testrep.rep', SYNCHRONOUS, RUNTIME, FILESYSTEM, plid, NULL); ELSE -- IF Running on the Web read the Report Server name and the report format from the Registry TOOL_env.getvar('WEB_REPSERVER_NAME',vRep_server); TOOL_env.getvar('FORMS60_REPFORMAT',vRep_format); -- Read the first part of the URL from the Registry --vUrl := get_sys_env('WEB_CGIREPORT_LOC'); TOOL_env.getvar('WEB_CGIREPORT_LOC',vUrl); -- Format the connect string vConnect := upper(get_application_property(username))||'/'|| --upper(get_application_property(password)); IF get_application_property(connect_string) is not null then vConnect := vConnect||'@'||upper(get_application_property(connect_string)); END IF; -- Run the report
Re: can't create database
On Mon, Mar 17, 2003 at 07:03:43AM -0800, Joe Johnson wrote: Hi, This site has a good description of how to use the ipcs and ipcrm commands to clean up these left-over memory segments following the shutdown. I've found that using the technique shown on this site minimizes the need to reboot when this problem occurs. site unseen! Hope this helps, Joe Johnson, OCP Senior Database Administrator Greenbrier Russel, Inc. Madison, Wisconsin USA Let us linger here a while in the foolishness of things -Unknown -Original Message- From: Schwerdtfeger, Christoph [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Subject: Re: can't create database Am Mit, 2003-03-12 um 21.14 schrieb [EMAIL PROTECTED]: I've had this problem on linux. Best and fastest solution: reboot the server. There is a memory issue preventing you from starting oracle. I just had it happen about 2 minutes ago on an upgrade to 9i. Reboot, and now I can start the db. -Candi On Wed, 2003-03-12 at 12:07, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 17.20 schrieb Ray Stell: On Wed, Mar 12, 2003 at 07:19:53AM -0800, Schwerdtfeger, Christoph wrote: Am Mit, 2003-03-12 um 10.28 schrieb Christoph Schwerdtfeger: Am Die, 2003-03-11 um 19.47 schrieb Alan Davey: I had the same error message on NT with Oracle 9.2 recently. The first problem was that the init_sid.ora file was created a directory different from where the db create scripts were looking for it. The second problem was that one of the init parameters was for Enterprise Edition and I was installing Standard Edition. Both problems resulted in the message of 'not connected to oracle'. Come to think of it, I don't think I've ever had a version of the DB Creation wizard work without getting some error. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/11/2003 12:44 PM, Stahlke, Mark [EMAIL PROTECTED] wrote: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message- From: Milen Pankov [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: can't create database can't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Hi there. I have a similiar problem with Oracle 9.2.0.1.0 under Linux (Debian, Woody, Kernel 2.4.20-686-smp). The installation worked fine - at least no error message was shown, but when I start the dbca to create a database, I get an ORA-03113. I've searched over the internet (newsgroups and webpages), but I didn't find a solution for my problem. I searched for the libc-patch, but I think it's deprecated for Oracle 9i. Many ppl said, like you, I just should generate the scripts with the wizard and create the database manually, so I did - or at least I tried. So, I just wanted to test, if the login works, but it didn't. Is startet sqlplus /nolog and tried connect SYS/change_on_install as SYSDBA (conn / as SYSDBA has the same effect, ORACLE_SID set as said in the script) and I get: Connected to an idle instance. Ok, I think this is just normal and I tried to start the database using startup nomount pfile=Correct path to init.ora; And then, all I get is an ORA-03113: end-of-file on communication channel If I try to do a conn / afterwards, I get ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Any suggestions, what could be wrong? I installed the Enterprise Version, so it can't be an Enterprise setting ... it has to be a problem in the init.ora, right? System specs if required: Pentium II, 800 (2x - multi processor) 1024MB RAM 4GB Swap 19GB diskspace free Debian Linux (woody) Kernel 2.4.20-686-smp Any help would be appreciated. -- Christoph Schwerdtfeger [EMAIL PROTECTED] SoftConcept
Re: Standby errors
Hi I would recommend that to use the 'alter system archive log current;' since this command don't return until the database has finished to archive the redo logfile. Hemant K Chitale wrote: Rather than doing an ALTER SYSTEM SWITCH LOGFILE only, issue an ALTER SYSTEM ARCHIVE LOG NEXT I actually do an ARCHIVE LOG NEXT, SWITCH LOGFILE and also have a sleep in the backup-script, which then proceeds to copy all but the *last* archivelog out to tape ! Hematn At 06:19 AM 14-03-03 -0800, you wrote: Oracle 8.1.6 on Solaris 5.8. We have a read-only standby database for one of our production databases. Each night, the standby is shutdown and the previous days archive logs from production are applied. Then the database is brought backup in read-only mode. To get the archive logs, a hot backup is done on the production database. As the last step is the hot backup, a log switch is done and then the archive logs are ftp'd to the server where the standby is. After the hot backup completed yesterday, the log switch occurred, and the logs sent, but when an attempt was made to apply the archive logs we got an error: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '/orabackup/archive/TBSPRD/arch1352.arc' ORA-332 signalled during: ALTER DATABASE RECOVER In looking at the archive log, both on the production and standby servers, they are the same size - 16k (the block size for the db is 8k). The next log is 8k in size and then there is another that is 16k before we see any that are normal sized. These would have been the first logs _after_ the hot backup the night before. In the alert log for the production db, it appears the log 1353 was archived _before_ 1352. Has anyone seen this behavior before? Does anybody have any idea why it happened in the first place? Is there something we can do to make sure it never happens again? P.S. We are upgrading to 9.2 this weekend, if that makes any difference. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Peter Gram, Miracle A/S Phone : +45 2527 7107, Fax : +45 4466 8856 mailto:[EMAIL PROTECTED] - http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Comparing strings whilst ignoring some characters
This is a job for regular expressions, which make this job easy. Run $ORACLE_HOME/rdbms/admin/pubpat.sql and privpat.sql to create the owa_pattern packages. Here are some examples I keep around. Jared declare tstr varchar2(100) := 'this contains tabs multiple spaces and single spaces'; begin dbms_output.put_line( tstr); owa_pattern.change( tstr, '\s', '', 'g'); dbms_output.put_line( tstr); end; / declare tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882 dig2its'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\d', '', 'g'); dbms_output.put_line( tstr); -- remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end; / drop table owatest; create table owatest ( test varchar2(20) ) / insert into owatest values('non numeric row'); insert into owatest values('numeric 23423 row'); commit; select * from owatest where owa_pattern.amatch(test,1,'^.*\d') 0 / drop table regex; create table regex ( test varchar2(20) ); create or replace function strip_str ( data_in varchar2 --, regex_in varchar2 ) return varchar2 is test_str varchar2(4000); begin test_str := data_in; --owa_pattern.change(test_str, regex_in, '', 'g'); owa_pattern.change(test_str, '\x0a', '', 'g'); owa_pattern.change(test_str, '\x0c', '', 'g'); owa_pattern.change(test_str, '\x0d', '', 'g'); return test_str; end; / show error function strip_str insert into regex values( 'carriage' || chr(13) || 'return'); insert into regex values( 'line' || chr(10) || 'feeds' || chr(10)); insert into regex values( 'form feed' || chr(12)); commit; select test from regex; select strip_str(test) test from regex / Mark Richard [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/16/2003 09:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Comparing strings whilst ignoring some characters Hi Everyone, I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time... I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that the cat equals thecat. I thought I used the translate function, but a quick inspection didn't reveal how. Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file scattered read
Mladen, I guess the rdbms kernel will be passing the startblock-stop block addresses and will be passing to the readv (or pread?) system calls. A single multiblock read can not read two different sets (!) of contiguos blocks.. Or I am thinking in the different direction?? Best Regards, K Gopalakrishnan -Original Message- Mladen Sent: Monday, March 17, 2003 7:45 AM To: Multiple recipients of list ORACLE-L How can you tell that DB_FILE_MULTIBLOCK_READ will not span extents? I was unable to confitrm that on Metalink. -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: db file scattered read If 14706+3 is one extent and another extent begins at 14710, it will NOT read 14706+8. A DB_FILE_MULTIBLOCK_READ will not span extents. Hemant At 09:04 AM 14-03-03 -0800, you wrote: Here is a part of trace file . I am finding that oracle is trying to read 8 or 3 or 7 blocks at a time . But block numbers are all sequential i.e. it will read 3 blocks starting from 14706 and then 8 blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks always it multi_block_read is set to 8 ? Any Idea . Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 WAIT #32: nam='db file scattered read' ela= 2 p1=4 p2=14718 p3=8 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14727 p3=3 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14731 p3=7 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14739 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14744 p3=8 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=129784 p3=8 Thanks -ak Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file scattered read
Title: RE: db file scattered read it does not. Try it out. The blocks in a multi-block read have to be contiguous and Oracle can not guarantee that if the read would span an extent boundary. I suppose Oracle could check, but that would likely incur more overhead than you'd possibly save over issuing two scattered reads. At 07:44 AM 3/17/2003 -0800, you wrote: How can you tell that DB_FILE_MULTIBLOCK_READ will not span extents? I was unable to confitrm that on Metalink. Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
Re: insert stmt disk reads
Partial List Foreign Key Validation Primary Key Validation Reading blocks on the freelist for insert Before Insert/After Insert Triggers -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals AK wrote: In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
RE: empty blocks
AK Nope. Only analyzing the table sets these columns. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 10:27 AM To: Multiple recipients of list ORACLE-L Does blocks and empty_blocks gives correct info even if I dont run statistics ? thanks, -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-01578 data block corrupted on standby
Hi, all. I'm trying to manually create a standby database on Solaris 2.6 Oracle 8.0.5.2.1 The vendor for our application provided a script that uses the command alter index idx rebuild tablespace tblspace NOLOGGING; for all of their application indexes. When I test the standby (copy datafiles, copy and apply redo logs, bring up database) and execute a query, I get the error message ORA-01578: ORACLE data block corrupted (file # 17, block # 150586) ORA-01110: data file 17: '/orastandby/u12/oradata/ent/adv_indexes01.dbf' Metalink doc 164836.1 says that this can be fixed by executing the command alter index idx logging; against all the indexes that were set nologging and rebuilding the standby. I tried this, and got the same corrupt block error message on my query. Here's the order of things: (1) alter indexes rebuild with nologging; (2) immediately after rebuild, alter index logging; (3) shut down database, cold backup to disk, rcp files to standby (database comes up without errors; redo logs are applied successfully. There are no errors in alert log) Analyzing the indexes without the nolog option increases the time considerably, and we have a short maintenance window. Any ideas? TIA! Barb __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
IME the 'trouble' of managing rollback segments is grossly exaggerated. They rarely cause me any trouble. Only errant programmers cause me problems. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 06:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: insert stmt disk reads
How about foreign key constraints? - going to the parent table to see if the value exists. Tom Mercadante Oracle Certified Professional -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Monday, March 17, 2003 12:22 PMTo: Multiple recipients of list ORACLE-LSubject: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
RE: insert stmt disk reads
Constraint validations? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file scattered read
When I come to think of it, my question was not very smart. RDBMS can use multiblock read only to read contiguous blocks. If the blocks are in two different extents, they're not contiguous. Sorry for asking, hopefully the fact that it's Monday can explain my somewhat diminished capabilities. -Original Message- From: K Gopalakrishnan [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 12:42 PM To: Multiple recipients of list ORACLE-L Subject: RE: db file scattered read Mladen, I guess the rdbms kernel will be passing the startblock-stop block addresses and will be passing to the readv (or pread?) system calls. A single multiblock read can not read two different sets (!) of contiguos blocks.. Or I am thinking in the different direction?? Best Regards, K Gopalakrishnan -Original Message- Mladen Sent: Monday, March 17, 2003 7:45 AM To: Multiple recipients of list ORACLE-L How can you tell that DB_FILE_MULTIBLOCK_READ will not span extents? I was unable to confitrm that on Metalink. -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: db file scattered read If 14706+3 is one extent and another extent begins at 14710, it will NOT read 14706+8. A DB_FILE_MULTIBLOCK_READ will not span extents. Hemant At 09:04 AM 14-03-03 -0800, you wrote: Here is a part of trace file . I am finding that oracle is trying to read 8 or 3 or 7 blocks at a time . But block numbers are all sequential i.e. it will read 3 blocks starting from 14706 and then 8 blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks always it multi_block_read is set to 8 ? Any Idea . Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 WAIT #32: nam='db file scattered read' ela= 2 p1=4 p2=14718 p3=8 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14727 p3=3 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14731 p3=7 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14739 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14744 p3=8 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=129784 p3=8 Thanks -ak Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
Re: log buffer space
KG, I got the direct mail much quicker than the list mail - but I'll just echo the doubt I raised in the original. If you use 10046 level 8 to watch for log writer writes, I would expect you to see writes that could be of an almost arbitrary size. Assume a 3MB log buffer - we 'know' that Oracle triggers on 1MB: but what if the system is busy when a write is triggered and the users are generating lots of work ? LGWR writes 1MB - and in that time interval the users fill the other 2MB of the log buffer. The next write that LGWR does is 2MB. You could even argue that if the largest write you regularly see is 2/3 of the log buffer size, then the trigger is probably 1/3 of the log buffer size. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 17:42 Jonathan: I have just sent a mail which has the test statistics. I would appreciate your comments on that.. Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8. KG Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
I agree. once they are established, and you determine the size of them, you hardly ever have to touch them again. (rollback segments - not errant programmers!) :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, March 17, 2003 12:59 PM To: Multiple recipients of list ORACLE-L IME the 'trouble' of managing rollback segments is grossly exaggerated. They rarely cause me any trouble. Only errant programmers cause me problems. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 06:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list,
Re: insert stmt disk reads
Do you have many indexes on your system ? A table insert often results in index updates; and for large tables with many indexes you usually find that some of the index leaf blocks have to be read from disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 17:22 In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
Jared - When it comes to comparing databases, a lot comes down to perception. Oracle would like to market itself to small sites that don't even have a DBA, otherwise if forfeits those accounts to Microsoft. Now, when the MS salesperson says Oracle takes a lot more maintenance, and nobody knows how to size rollback segments, the Oracle rep can reply Oh we automated that. Of course, if you feed your family by understanding how Oracle works, you aren't so eager for them to make it so simple that it takes no attention ;-) -Original Message- Sent: Monday, March 17, 2003 11:59 AM To: Multiple recipients of list ORACLE-L IME the 'trouble' of managing rollback segments is grossly exaggerated. They rarely cause me any trouble. Only errant programmers cause me problems. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 06:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Oracle in the news
From the Robert X. Cringely column in InfoWorld: Oracle slammed and exposed Although Microsoft catches a lot of heat for using rival software within its own walls, it was discovered recently Oracle uses Microsoft's SQL Server database, among other things. That's right, and chief Larry Ellison was forced to suck it up and admit this, albeit internally, when Oracle was struck by the SQL Slammer virus. In a memo that I obtained on the down-low, Ellison wrote that Slammer slowed Oracle's global network. It seems we have a few copies of the Microsoft SQL Server database on our network, Larry wrote. We need the SQL Server machines to test the Oracle database and make certain it interoperates properly with the Microsoft database. However, we do not need to attach the Microsoft machines to our network and will not do so in the future. That is not all, though. A spy of mine was using the Oracle Development Tools User Group management facility to change his member-name password. All of a sudden he got an error regarding the Microsoft Jet database engine and some related difficulty. Shocked, he wired the user group administrative listserver and was told that the work is contracted out because retaining an Oracle-based shop for the management of the service would be prohibitively expensive. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
With power comes complexity. Personally, I can't think of exceptions to that rule. Maybe someone else can. Sizing rollback segments is usually a non-issue, but there's always plenty of other stuff going on to keep me busy. :) ( Watch out, here comes another car analogy, though I haven't used one in a while ) Driving a car around a race track comes to mind. Anyone can do it, it's really easy. Doing so as quickly as a powerful car is capable of is something else entirely. It takes coordination, skill, knowledge of the vehicle, the track conditions, the cars ahead of you and behind you and a slew of other things. Slow is simple and easy. Fast is hard and complex. Anyone can setup SQL Server. ;) Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 11:15 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Jared - When it comes to comparing databases, a lot comes down to perception. Oracle would like to market itself to small sites that don't even have a DBA, otherwise if forfeits those accounts to Microsoft. Now, when the MS salesperson says Oracle takes a lot more maintenance, and nobody knows how to size rollback segments, the Oracle rep can reply Oh we automated that. Of course, if you feed your family by understanding how Oracle works, you aren't so eager for them to make it so simple that it takes no attention ;-) -Original Message- Sent: Monday, March 17, 2003 11:59 AM To: Multiple recipients of list ORACLE-L IME the 'trouble' of managing rollback segments is grossly exaggerated. They rarely cause me any trouble. Only errant programmers cause me problems. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 06:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing
Re: Oracle in the news
Oh, this is just too much. Oracle contracting out to a SQL Server shop to save $$. Hell just froze over. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 11:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Oracle in the news From the Robert X. Cringely column in InfoWorld: Oracle slammed and exposed Although Microsoft catches a lot of heat for using rival software within its own walls, it was discovered recently Oracle uses Microsoft's SQL Server database, among other things. That's right, and chief Larry Ellison was forced to suck it up and admit this, albeit internally, when Oracle was struck by the SQL Slammer virus. In a memo that I obtained on the down-low, Ellison wrote that Slammer slowed Oracle's global network. It seems we have a few copies of the Microsoft SQL Server database on our network, Larry wrote. We need the SQL Server machines to test the Oracle database and make certain it interoperates properly with the Microsoft database. However, we do not need to attach the Microsoft machines to our network and will not do so in the future. That is not all, though. A spy of mine was using the Oracle Development Tools User Group management facility to change his member-name password. All of a sudden he got an error regarding the Microsoft Jet database engine and some related difficulty. Shocked, he wired the user group administrative listserver and was told that the work is contracted out because retaining an Oracle-based shop for the management of the service would be prohibitively expensive. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Autoallocate (was Re: LMT monitoring)
Excellent analogy Jared! Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 1:36 PM To: [EMAIL PROTECTED] Cc: DENNIS WILLIAMS Importance: High With power comes complexity. Personally, I can't think of exceptions to that rule. Maybe someone else can. Sizing rollback segments is usually a non-issue, but there's always plenty of other stuff going on to keep me busy. :) ( Watch out, here comes another car analogy, though I haven't used one in a while ) Driving a car around a race track comes to mind. Anyone can do it, it's really easy. Doing so as quickly as a powerful car is capable of is something else entirely. It takes coordination, skill, knowledge of the vehicle, the track conditions, the cars ahead of you and behind you and a slew of other things. Slow is simple and easy. Fast is hard and complex. Anyone can setup SQL Server. ;) Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 11:15 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Jared - When it comes to comparing databases, a lot comes down to perception. Oracle would like to market itself to small sites that don't even have a DBA, otherwise if forfeits those accounts to Microsoft. Now, when the MS salesperson says Oracle takes a lot more maintenance, and nobody knows how to size rollback segments, the Oracle rep can reply Oh we automated that. Of course, if you feed your family by understanding how Oracle works, you aren't so eager for them to make it so simple that it takes no attention ;-) -Original Message- Sent: Monday, March 17, 2003 11:59 AM To: Multiple recipients of list ORACLE-L IME the 'trouble' of managing rollback segments is grossly exaggerated. They rarely cause me any trouble. Only errant programmers cause me problems. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 06:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Autoallocate (was Re: LMT monitoring) Alex If you do not specify the UNDO TABLESPACE when creating the database then AUTOEXTEND is set to ON. I was able to alter that to OFF. The point of the UNDO is that it is automatically managed by Oracle. If you can't cope with that, or you decide that doesn't work well for you, then you can revert to the old manual ROLLBACK segments. I think that few sites had their rollback segments properly configured, and Oracle has been criticized for requiring a lot of expert attention compared to other databases. This is Oracle's attempt to reduce the TCO (Total Cost of Ownership). Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 16, 2003 10:59 PM To: Multiple recipients of list ORACLE-L How about UNDO tablespace in 9.2? It gets created with autoallocate, and there is no way to change it or specify any parameters for undo segments. Each segment extended as needed, and when shrinked deallocated some extents not necessary the last, than allocate new extent. Alex. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:22 PM You do get odd results. The last time I tested on a clean tablespace, an initial of 65MB gave me a consistent result which I recall as: Extent 0 at 8MB Extents 1 - 56 at 1MB each Extent 57at 8MB - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 6:23 AM case when initial_extent 1m then case when extents 16 then next = 64k, when extents 80 then next = 1m, when extents 200 then next = 8m, else next = 64m when initial_extent = 1m then case when extents 64 then next = 1m, when extents 184 then next = 8m, else next = 64m ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Feinstein INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network
Best way to secure underlying tables in a view
Hi All, I have a view made of 4 tables with different owners. I want to grant users select privs on that view but I do not want them to select on underlying tables. What is best approach? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: AW: Problem with netasst dbastudio
This didn't help. no processes were running, no processes were killed. maybe it's something else. now i've noticed (i don't know if it was there before) the dbastudio returns this: java.lang.reflect.InvocationTargetExceptionjava.lang.StackOverflowError Cannot launch application oracle.sysman.vth.VthDdmgrApp To Milen Kulev: thanks for help. are you from Bulgaria? From: Kulev, Milen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: AW: Problem with netasst dbastudio Date: Mon, 17 Mar 2003 05:48:40 -0800 Hi Milen. I had the same problemson my Linux box. On the console type ps -ef | grep jre . Normally I see a lot of zombie jre's [defunc]. Kill them all wich killall -9 jre (I hope that no other application using JRE is running at that time ;) ). Then try to start netasst or dbastudio again. It worked for me. HTH. Another Milen ;) -Ursprüngliche Nachricht- Von: Milen Pankov [mailto:[EMAIL PROTECTED] Gesendet: Montag, 17. März 2003 08:49 An: Multiple recipients of list ORACLE-L Betreff: Problem with netasst dbastudio recently i posted mail here about problem installing oracle 8.1.7 on mandrake 9.0. well i managed to install it - it was a mising file when installing the glibs patch. now i have created a database, created listener, configured naming methods and everything. finally i imported a entire database from another server. the db is mounted and works fine. the problem now is i can't start netasst or dbastudio. everything else works - the dbaasst, workheet, netca etc. when i write in the console 'oemapp dbastudio' or 'netasst' it just holds on and nothing till i press Ctrl+C. i think there may be some problem with the JRE. i didn't manage to run the installer in the begining of the installation and i downloaded a JRE from IBM - it was theonly way it worked. may be now the problem is with the ibm's jre - i don't know. any sugestions?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Milen Pankov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kulev, Milen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Milen Pankov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Autoallocate (was Re: LMT monitoring)
No.. that should be: With great power comes great responsibility. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 19:34 With power comes complexity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Standby errors
Actually, the command to force the switch is: alter system switch logfile; alter system archive log all; I did not set the script up, I just inherited it. THe thinking is, since a hot backup and log switch is going to be done anyway, why not ftp the archive logs after the switch, but as part of the same process. Unfortunately, we did not get the upgrade completed this weekend. The log files were the same size on the production database as on the standby (i.e. too small). We did another hot backup as soon as it was discovered, so that we would not be without a recoverable backup, but wanted to know what had caused the archive log process to create logs that it couldn't read. -Original Message- Sent: Friday, March 14, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Why does the copying of archive logs to the standby have anything to do with a hot backup on the primary? They seem like unrelated events. The reason you are getting an incomplete log is that you are using 'alter system switch logfile'. This command returns before archival, so your copy can commence and finish before archival is complete. The correct command to use is 'alter system archive log current'. This command will not return until the current log is fully archived. Than your copy can proceed safely. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 14 Mar 2003, Ball, Terry wrote: Oracle 8.1.6 on Solaris 5.8. We have a read-only standby database for one of our production databases. Each night, the standby is shutdown and the previous days archive logs from production are applied. Then the database is brought backup in read-only mode. To get the archive logs, a hot backup is done on the production database. As the last step is the hot backup, a log switch is done and then the archive logs are ftp'd to the server where the standby is. After the hot backup completed yesterday, the log switch occurred, and the logs sent, but when an attempt was made to apply the archive logs we got an error: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '/orabackup/archive/TBSPRD/arch1352.arc' ORA-332 signalled during: ALTER DATABASE RECOVER In looking at the archive log, both on the production and standby servers, they are the same size - 16k (the block size for the db is 8k). The next log is 8k in size and then there is another that is 16k before we see any that are normal sized. These would have been the first logs _after_ the hot backup the night before. In the alert log for the production db, it appears the log 1353 was archived _before_ 1352. Has anyone seen this behavior before? Does anybody have any idea why it happened in the first place? Is there something we can do to make sure it never happens again? P.S. We are upgrading to 9.2 this weekend, if that makes any difference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
optimizer_mode=FIRST_ROWS
All, I've run into the following queries hanging when ran on a database with the optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no problems. When set to FIRST_ROWS both queries show never-ending wait events for direct path read. I killed the sessions before they finished after waiting for almost an hour for the queries to complete. I re-wrote the 1st query against dba_tables to use EXISTS (also shown below) and that seemed to work fine. But I'm not sure why or how to re-write the 2nd query to also be able to workI've tried a couple of things with no luck. Any ideas? Thanks for the help, Karen Morton select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tables, dba_tab_columns where dba_tables.owner = 'XYZDBA' and dba_tables.table_name = dba_tab_columns.table_name order by dba_tab_columns.table_name, dba_tab_columns.column_id ; select dba_indexes.table_name, dba_indexes.index_name, dba_indexes.uniqueness, dba_ind_columns.column_name, dba_ind_columns.column_position from dba_indexes, dba_ind_columns where dba_indexes.owner = 'XYZDBA' and dba_indexes.index_name = dba_ind_columns.index_name order by dba_indexes.table_name, dba_indexes.index_name, dba_ind_columns.column_position ; -- Rewritten dba_tables query that works select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tab_columns where EXISTS (SELECT * FROM dba_tables WHERE owner = 'XYZDBA' AND table_name = dba_tab_columns.table_name) order by dba_tab_columns.table_name, dba_tab_columns.column_id ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karen Morton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Autoallocate (was Re: LMT monitoring)
Which is followed by: Absolute power corrupts absolutely. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] emon.co.uk To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Autoallocate (was Re: LMT monitoring) 03/17/2003 01:28 PM Please respond to ORACLE-L No.. that should be: With great power comes great responsibility. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 19:34 With power comes complexity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer_mode=FIRST_ROWS
Karen, Are you on version 8? I imagine so given the problem you are seeing. By using FIRST_ROWS you are forcing the optimizer to use CBO even when there are no statistics. Most likely you have no stats on your sys objects (and this is a good thing) and thus the execution plan the CBO is providing will be a bad one. There a few options: rewrite the query - as you have done hint the query with specific hints to cause the correct execution path For DBA queries like this, the easiest may be to hint to use RULE base optimisation- eg select /*+RULE*/ Some notes suggested by Anita Bardeen when I posted on a similar topic in Nov 2001. Note: 35272.1 Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)? Note: 35934.1 TECH: Cost Based Optimizer - Common Misconceptions and Issues Note: 66484.1 Which Optimizer is used Some other points I have found: In first_rows mode you will encounter some very bad queries against the data dictionary. An example of 1 which has been fixed by Oracle is catblock.sql - there is an updated version available on Metalink - see note 122567.1 titled Poor Performance in Query onDBA_WAITERS Whilst searching for the notes suggested by Anita, I came across a good forum discussion (see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FORp_id=279251.999 ) This describes how the ODBC driver 8.1.7.4 has been fixed / improved to use rule hints when accessing the data dictionary. Before this if you used the Oracle ODBC driver and were in first_rows mode we had to wait 5 - 10 minutes just to link a table in Access HTH, Bruce Reardon -Original Message- Sent: Tuesday, 18 March 2003 7:39 AM To: Multiple recipients of list ORACLE-L All, I've run into the following queries hanging when ran on a database with the optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no problems. When set to FIRST_ROWS both queries show never-ending wait events for direct path read. I killed the sessions before they finished after waiting for almost an hour for the queries to complete. I re-wrote the 1st query against dba_tables to use EXISTS (also shown below) and that seemed to work fine. But I'm not sure why or how to re-write the 2nd query to also be able to workI've tried a couple of things with no luck. Any ideas? Thanks for the help, Karen Morton select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tables, dba_tab_columns where dba_tables.owner = 'XYZDBA' and dba_tables.table_name = dba_tab_columns.table_name order by dba_tab_columns.table_name, dba_tab_columns.column_id ; select dba_indexes.table_name, dba_indexes.index_name, dba_indexes.uniqueness, dba_ind_columns.column_name, dba_ind_columns.column_position from dba_indexes, dba_ind_columns where dba_indexes.owner = 'XYZDBA' and dba_indexes.index_name = dba_ind_columns.index_name order by dba_indexes.table_name, dba_indexes.index_name, dba_ind_columns.column_position ; -- Rewritten dba_tables query that works select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tab_columns where EXISTS (SELECT * FROM dba_tables WHERE owner = 'XYZDBA' AND table_name = dba_tab_columns.table_name) order by dba_tab_columns.table_name, dba_tab_columns.column_id ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer_mode=FIRST_ROWS
Karen It sounds as if these queries don't work well with the optimizer goal set to FIRST_ROWS. Essentially you are forcing the optimizer goal. Is there a particular reason you have FIRST_ROWS in your init.ora as your optimizer goal? My guess is that when you allow the optimizer to CHOOSE, it switches to ALL for these queries. Why not set the init.ora to CHOOSE? If that won't work for you, you could add the ALL_ROWS hint to these queries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 2:39 PM To: Multiple recipients of list ORACLE-L All, I've run into the following queries hanging when ran on a database with the optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no problems. When set to FIRST_ROWS both queries show never-ending wait events for direct path read. I killed the sessions before they finished after waiting for almost an hour for the queries to complete. I re-wrote the 1st query against dba_tables to use EXISTS (also shown below) and that seemed to work fine. But I'm not sure why or how to re-write the 2nd query to also be able to workI've tried a couple of things with no luck. Any ideas? Thanks for the help, Karen Morton select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tables, dba_tab_columns where dba_tables.owner = 'XYZDBA' and dba_tables.table_name = dba_tab_columns.table_name order by dba_tab_columns.table_name, dba_tab_columns.column_id ; select dba_indexes.table_name, dba_indexes.index_name, dba_indexes.uniqueness, dba_ind_columns.column_name, dba_ind_columns.column_position from dba_indexes, dba_ind_columns where dba_indexes.owner = 'XYZDBA' and dba_indexes.index_name = dba_ind_columns.index_name order by dba_indexes.table_name, dba_indexes.index_name, dba_ind_columns.column_position ; -- Rewritten dba_tables query that works select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tab_columns where EXISTS (SELECT * FROM dba_tables WHERE owner = 'XYZDBA' AND table_name = dba_tab_columns.table_name) order by dba_tab_columns.table_name, dba_tab_columns.column_id ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karen Morton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: insert stmt disk reads
insert into ... select * from .. -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Monday, March 17, 2003 12:22 PMTo: Multiple recipients of list ORACLE-LSubject: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
Re: Autoallocate (was Re: LMT monitoring)
yes, well, that too. :) Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 12:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Autoallocate (was Re: LMT monitoring) No.. that should be: With great power comes great responsibility. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 19:34 With power comes complexity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert stmt disk reads
I dont have any foreign key const on the table . Yes but there are two indexes on this table . Would that cause this high disk reads ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 17, 2003 10:14 AM Constraint validations? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: keep pool
AK Since I don't see where anyone posted a reply to your question, here are the lines I added to my init.ora to implement the keep and recycle pools. This is a 4-CPU system. It took me awhile to understand that you need to increase the latches, but not by much. db_block_buffers = 20 db_block_lru_latches = 6 buffer_pool_keep= (buffers:9, lru_latches:2) buffer_pool_recycle = (buffers:1, lru_latches:1) Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 17, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hi List , I have a 8.1.6 db running on hp-ux 2 cpu . currently it has default db block lru latches ( i guess this should be 1 since oracle sets it to cpu/2 ) . Now I want to configure keep pool . Which will requires at least 1 lru latches . So I need to specify db block lru latches 1 ( rite ? ) . Now my question is what should be a good value for db block lru latches and keep pool lru lactes . I have 5 blocks for db buffers (8K) in total , out of which i am initiallly planning to take 8000 for keep pool . what other consideration i should keep in mind before i set keep pool . -thanks ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best way to secure underlying tables in a view
One way: Create user view_owner... as all 4 table owners: grant select on table_name to view_owner with grant option; as view_owner create view whatever as 4 table join here grant select on whatever to whomever HTH Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 12:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Best way to secure underlying tables in a view Hi All, I have a view made of 4 tables with different owners. I want to grant users select privs on that view but I do not want them to select on underlying tables. What is best approach? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
migration
Hi Friends, Iam migrating data from 7.3.2 to 8.1.7.4 with exp/imp, when Iam importing into 8.1.7.4 Iam getting silly errors like IMP-00017: following statement failed with ORACLE error 1452: CREATE UNIQUE INDEX TTIITM009001$IDX1 ON TTIITM009001 I checked in metalink and other sites for help, I got sense that its all about NLS_LANG parameter!! But my both databases are US7ASCII character set!! I have no clue what to do?? I tried to set nls_lang and exported it!! But no use!! I got the sense its all about char set!! Any help will be appreciated!! TIA Peter. _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OCFS/Linux/RHAS problem
Playing with RAC on Linux. load_ocfs fails with many unresolved symbols Linux RHAS (developer version) uname -a returns 2.4.9-e.5 #1 Metalink states that it's certified RAC O/S When I run load_ocfs it fails and complains about many unresolved symbols. It could be due to the downloaded version for ocfs does not match the O/S I have. I tried e.3, e.8, e.9 and e.10, none of them worked. There is no version of ocfs for 2.4.9-e.5. Any ideas? Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: migration
How about checking out the part of the import log where it tells you which character sets are being used? Jared Peter R [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 03:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:migration Hi Friends, Iam migrating data from 7.3.2 to 8.1.7.4 with exp/imp, when Iam importing into 8.1.7.4 Iam getting silly errors like IMP-00017: following statement failed with ORACLE error 1452: CREATE UNIQUE INDEX TTIITM009001$IDX1 ON TTIITM009001 I checked in metalink and other sites for help, I got sense that its all about NLS_LANG parameter!! But my both databases are US7ASCII character set!! I have no clue what to do?? I tried to set nls_lang and exported it!! But no use!! I got the sense its all about char set!! Any help will be appreciated!! TIA Peter. _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Autoallocate (was Re: LMT monitoring)
Stepping back into reality briefly: With great power comes great licensing fees, great amounts of advertising literature, great demands from superiors to reduce costs by migrating to SQL Server, great loads of paperwork-stuff unrelated to the job at hand... Gee, great! :-) --- [EMAIL PROTECTED] wrote: yes, well, that too. :) Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/17/2003 12:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Autoallocate (was Re: LMT monitoring) No.. that should be: With great power comes great responsibility. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 19:34 With power comes complexity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using the /*+ append */ insert hint
Greetings from sunny Perth, Western Australia This is why the www.oracledba.co.uk hasn't been updated for a while, the UK ISP won't let me dialup from abroad, so I'm trying to find a workaround :-( Left UK in Jan, spent a month in Canada, got back to Perth in Feb, got married in 110 degree heat, and now scanning the barren wilderness otherwise known as the Perth job market :-( Cheers Connor --- Grant Allen [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Connor McDonald Sent: Monday, March 17, 2003 13:34 To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor Thanks Connor (and Darrell in the previous post). Connor, I saw a post that suggested you might be heading back to Oz - is that true? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer_mode=FIRST_ROWS
Thanks Bruce Dennis for your replies. The init.ora is set to FIRST_ROWS because statistics are always current for the application tables and testing showed that the plans generated in that optimizer mode offered better overall response times than with CHOOSE. I think the idea about hinting the sys views will likely fix the problems although we wanted to avoid hints if possible and just re-write the queries. I just couldn't figure out a way to re-write the one query for dba_indexes that made it any better.. Thanks, Karen -Original Message- Bruce (CALBBAY) Sent: Monday, March 17, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Karen, Are you on version 8? I imagine so given the problem you are seeing. By using FIRST_ROWS you are forcing the optimizer to use CBO even when there are no statistics. Most likely you have no stats on your sys objects (and this is a good thing) and thus the execution plan the CBO is providing will be a bad one. There a few options: rewrite the query - as you have done hint the query with specific hints to cause the correct execution path For DBA queries like this, the easiest may be to hint to use RULE base optimisation- eg select /*+RULE*/ Some notes suggested by Anita Bardeen when I posted on a similar topic in Nov 2001. Note: 35272.1 Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)? Note: 35934.1 TECH: Cost Based Optimizer - Common Misconceptions and Issues Note: 66484.1 Which Optimizer is used Some other points I have found: In first_rows mode you will encounter some very bad queries against the data dictionary. An example of 1 which has been fixed by Oracle is catblock.sql - there is an updated version available on Metalink - see note 122567.1 titled Poor Performance in Query onDBA_WAITERS Whilst searching for the notes suggested by Anita, I came across a good forum discussion (see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FORp_id=279251.999 ) This describes how the ODBC driver 8.1.7.4 has been fixed / improved to use rule hints when accessing the data dictionary. Before this if you used the Oracle ODBC driver and were in first_rows mode we had to wait 5 - 10 minutes just to link a table in Access HTH, Bruce Reardon -Original Message- Sent: Tuesday, 18 March 2003 7:39 AM To: Multiple recipients of list ORACLE-L All, I've run into the following queries hanging when ran on a database with the optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no problems. When set to FIRST_ROWS both queries show never-ending wait events for direct path read. I killed the sessions before they finished after waiting for almost an hour for the queries to complete. I re-wrote the 1st query against dba_tables to use EXISTS (also shown below) and that seemed to work fine. But I'm not sure why or how to re-write the 2nd query to also be able to workI've tried a couple of things with no luck. Any ideas? Thanks for the help, Karen Morton select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tables, dba_tab_columns where dba_tables.owner = 'XYZDBA' and dba_tables.table_name = dba_tab_columns.table_name order by dba_tab_columns.table_name, dba_tab_columns.column_id ; select dba_indexes.table_name, dba_indexes.index_name, dba_indexes.uniqueness, dba_ind_columns.column_name, dba_ind_columns.column_position from dba_indexes, dba_ind_columns where dba_indexes.owner = 'XYZDBA' and dba_indexes.index_name = dba_ind_columns.index_name order by dba_indexes.table_name, dba_indexes.index_name, dba_ind_columns.column_position ; -- Rewritten dba_tables query that works select dba_tab_columns.table_name, dba_tab_columns.column_name, dba_tab_columns.column_id, dba_tab_columns.data_length, dba_tab_columns.data_type, dba_tab_columns.nullable, dba_tab_columns.data_precision from dba_tab_columns where EXISTS (SELECT * FROM dba_tables WHERE owner = 'XYZDBA' AND table_name = dba_tab_columns.table_name) order by dba_tab_columns.table_name, dba_tab_columns.column_id ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
DB crashed with ORA-4030 on DBW0 process
Our database crashed over the weekend with the following lines from the trace. ORA-00449: background process 'DBW0' unexpectedly terminated with error 4030 ORA-04030: out of process memory when trying to allocate bytes (,) ORA-04030: out of process memory when trying to allocate 8512 bytes (pga heap,ksm stack) The statement was insert into /BIC/EZPCA_C03 ... select ... from ... the table /BIC/EZPCA_C03 has 73 partitions and 13 bitmap indexes. I found this on metalink (bug 1428288) and it looks like it abended due to a combination of large sort_area_size, DML against a partitioned table with abnormally high number of bitmap indexes on it. Oracle will defer index maintenance, for bitmap indexes, until the dml operation is complete. It buffers the index entries in the session's PGA memory and the formula for memory usage is sort_area_size x #of indexes x #of partitions. According to that, then I need 19,901,972,480 bytes for the job to run. ( 20 meg (sort_area_size * 73 partitions * 13 bitmap indexes) . I'm sure I missed something. This formula won't be used for insert into.. values ( ), but will for insert into ... select * from Is this correct? Any other conditions that would cause this formula to be used. The system is an SAP BW on 8.1.7.4.1/Win2K Can somebody add to this? Thanks, Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how to get metalink user name ??
Hello, my client has oracle v8.1.6 and v9.0.1. we need to access metalink now. at http://metalink.oracle.com, Customer Support Identifier (CSI) is required to register. where how can we get a CSI ? can any customer having a licensed copy of oracle access metalink ? if so , what is the procedure to get it ? OR is it restricted to customers with gold/silver support ? help me guys. Cheers, Scott. _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oracle ora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how to get metalink user name ??
(my understanding is that) if you are paying any kind of support contract with oracle (bronze etc) you have access to metalink. Contact support on the phone and they should be able to tell you the various details hth connor --- oracle ora [EMAIL PROTECTED] wrote: Hello, my client has oracle v8.1.6 and v9.0.1. we need to access metalink now. at http://metalink.oracle.com, Customer Support Identifier (CSI) is required to register. where how can we get a CSI ? can any customer having a licensed copy of oracle access metalink ? if so , what is the procedure to get it ? OR is it restricted to customers with gold/silver support ? help me guys. Cheers, Scott. _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oracle ora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
From Microsoft Access to Oracle
Hello list, I want to create a new form in Access where user input will be thereand the data entered i have to store it in Oracle database. Can anyone of you guide me in doing this..How ??? Thanks and Regards, Santosh
Inband and Outband Net8 question
Hi all, I have question about Inband break, please help me understand this Oracle said: There are 2 types of breaks: - Inband breaks: are transmitted as part of regular data traffic using the normal protocol read and write functions. These breaks are symply queued. - Outband breaks: are faster as it is send during urgent data messages. These messages cause signals and take a lot of load of the server. These are my questions: What is the purpose of Inband break? when we use it during our day to day activities? is this break trigger automatically (something like end of some activity)? Thanks, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how to get metalink user name ??
hello scott, You will get the metalink support in the following ways: 1. your organization should be partner to Oracle 2. your organization should take Annual support contract from Oracle If you have the any of the above , then go to metalink.oracle.com and find out the CSI number to register for an account to the metalink web site. rukmini - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 11:08 AM (my understanding is that) if you are paying any kind of support contract with oracle (bronze etc) you have access to metalink. Contact support on the phone and they should be able to tell you the various details hth connor --- oracle ora [EMAIL PROTECTED] wrote: Hello, my client has oracle v8.1.6 and v9.0.1. we need to access metalink now. at http://metalink.oracle.com, Customer Support Identifier (CSI) is required to register. where how can we get a CSI ? can any customer having a licensed copy of oracle access metalink ? if so , what is the procedure to get it ? OR is it restricted to customers with gold/silver support ? help me guys. Cheers, Scott. _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oracle ora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rukmini Devi N INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORacle Applications mailing list
Hi all, Can any one tell about the Oracle Applications(E-Business suite) mailing list. Thanks rukmini