Antwort: RE: Antwort: RE: Buffer Busy Waits -- Sanity check please
But what if they are aging out, isnt that one of reasons for buffer busy and they are - if there is a scattered read >From docus: As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in. If the table s not too big, than I would put it to the cache to avoid reloads from harddisk. Ivo ________ Ivo Libal, Bc. Design & Development, Warehousemanagementsystems Dept. KNAPP Systemsintegration GmbH Waltenbachstraße 9 A-8700 Leoben, Austria Phone: ++43/3842/805-0 e-Mail: [EMAIL PROTECTED] "Thorns, Malcolm (NESL-IT)" ric.co.uk>Kopie: Gesendet von: [EMAIL PROTECTED]Thema: RE: Antwort: RE: Buffer Busy Waits -- Sanity check please 11/27/2001 11:10 AM Bitte antworten an ORACLE-L This will not resolve the 'buffer busy wait' scenario. The contention is for blocks that are already in the SGA. Regards, Malcolm. -Original Message- Sent: Tuesday, November 27, 2001 9:15 AM To: Multiple recipients of list ORACLE-L Hi I would try to set the table to cache mode. It seams that the blocks are aged out from buffer - which is default for FTS. alter table BBN_SRVvignette cache; to avoid reloading into cache. Ivo ________ Ivo Libal, Bc. Design & Development, Warehousemanagementsystems Dept. KNAPP Systemsintegration GmbH Waltenbachstraße 9 A-8700 Leoben, Austria Phone: ++43/3842/805-0 e-Mail: [EMAIL PROTECTED] "Thorns, Malcolm (NESL-IT)" ric.co.uk>Kopie: Gesendet von: [EMAIL PROTECTED]Thema: RE: Buffer Busy Waits -- Sanity check please 11/27/2001 09:25 AM Bitte antworten an ORACLE-L Jeff, The 3 sessions are doing the same (or similar) queries. In this case count(*) which is forcing a full table scan of the table in each session. The 3 sessions are thus trying to access the same blocks from the SGA, in the same order. Only 1 session can access a block in the SGA at a time - this is the session showing 'db file scattered read'. The other 2 sessions need to wait for the block (these waits show as 'buffer busy waits' - ie waiting for the block in the SGA). You will see the block id (and perhaps the file id) changing as the FTS's progress. Thus the sessions are 'chasing' each other through the blocks - holding each other up with SGA block contention - which shows up as 'buffer busy waits'. Hope that explains things. Regards, Malcolm -Original Message- Sent: Monday, November 26, 2001 11:21 PM To: Multiple recipients of list ORACLE-L We recently had a new website go live. Since then, I'm seeing constant buffer busy waits and after a period of time, I see sessions hung on the same block#.The SQL query is always a COUNT(*) (below). It's almost as though one session has a lock of some sort in the buffer cache and other sessions are blocked. Although, I've checked and there&#
Antwort: RE: Buffer Busy Waits -- Sanity check please
Hi I would try to set the table to cache mode. It seams that the blocks are aged out from buffer - which is default for FTS. alter table BBN_SRVvignette cache; to avoid reloading into cache. Ivo Ivo Libal, Bc. Design & Development, Warehousemanagementsystems Dept. KNAPP Systemsintegration GmbH Waltenbachstraße 9 A-8700 Leoben, Austria Phone: ++43/3842/805-0 e-Mail: [EMAIL PROTECTED] "Thorns, Malcolm (NESL-IT)" ric.co.uk>Kopie: Gesendet von: [EMAIL PROTECTED]Thema: RE: Buffer Busy Waits -- Sanity check please 11/27/2001 09:25 AM Bitte antworten an ORACLE-L Jeff, The 3 sessions are doing the same (or similar) queries. In this case count(*) which is forcing a full table scan of the table in each session. The 3 sessions are thus trying to access the same blocks from the SGA, in the same order. Only 1 session can access a block in the SGA at a time - this is the session showing 'db file scattered read'. The other 2 sessions need to wait for the block (these waits show as 'buffer busy waits' - ie waiting for the block in the SGA). You will see the block id (and perhaps the file id) changing as the FTS's progress. Thus the sessions are 'chasing' each other through the blocks - holding each other up with SGA block contention - which shows up as 'buffer busy waits'. Hope that explains things. Regards, Malcolm -Original Message- Sent: Monday, November 26, 2001 11:21 PM To: Multiple recipients of list ORACLE-L We recently had a new website go live. Since then, I'm seeing constant buffer busy waits and after a period of time, I see sessions hung on the same block#.The SQL query is always a COUNT(*) (below). It's almost as though one session has a lock of some sort in the buffer cache and other sessions are blocked. Although, I've checked and there's no DML ongoing, so I'm unsure as to why we would see this. Note that v$session shows 78 and 393 to be INACTIVE, while 159 is ACTIVE.So it's like 159 can't write to the buffer cache because 78 and 393 have a lock there. Note that these are all defined as persistent connections, via the Vignette front-end. I'm sure all the clues are there but my brain is too fuzzed to piece it together. SID SQL_TEXT O/S User - --- 159 SELECT COUNT(*) NUM,SUM(TOTAL_CHARGE_AMT) TOT FROM BBN.BBN_SRV vignette 159 _PAID_WARR_CLAIM WHERE CUSTOMER_ID = :b1 AND ENTERPRISE_CD = : vignette 159 b2 AND (CHECK_ID IS NOT NULL AND CHECK_ID != 'PENDING' ) vignette SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- --- -- - -- 78 buffer busy waitsfile# 72 block# 109177 id 130 393 buffer busy waitsfile# 72 block# 109177 id 130 159 db file scattered read file# 72 block# 109177 blocks 8 Jeffery D Thomas DBA Thomson Information Services Thomson multimedia Inc. Email: [EMAIL PROTECTED] DBA Quickplace: http://gkmqp.tce.com/tis_dba <http://gkmqp.tce.com/tis_dba> E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addres
Antwort: RE: Oracle 9.0.1.1.1 on Win2k: stalls during installation: 85% "D
Hi try to remove lock_sga from init.ora file Ivo Nirmal Kumar Muthu KumaranAn: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Thema: RE: Oracle 9.0.1.1.1 on Win2k: stalls during installation: 85% "D Gesendet von: root@fatcity. com 11/27/2001 07:30 AM Bitte antworten an ORACLE-L Hi Tom, A week before i was tried to install oracle 9i on WinNT4(20GB Hard Disk, 128MB RAM). The software has been downloaded from Net. In my pc, i already have oracle731. I had tried to install oracle9i in another home. I had been installed oracle server sucessfully, but while creating the DB it failed by throwing our error message "OUT of memory", there is no log, nothing. I cann't able to identify why is was happening. Even i tried to increase the virtual memory too (I want to know is it helpful if we were in out of memory state?). Then what i did is, uninstalled Oracle731, and cleared all relavant registry entries, restart the PC, started to install ORa9i. This time, it has done nicely, no problem nothing. But i was confused why installer refering other versions of oracle to install ora9i, anyway we are using different home, is't it?... Ok, ur attachment is in binary format, so it's NOT useful for us. I didn't find any similar type of doc file in ora9i installer CD. Can you post it in text format, if you don't mind. Another thing is, "I would go into the registry and remove all traces of Oracle." -- How can do this, do you mean that, just search for oracle9i or something similar like this and remove from the registery? Thanks in advance Rgds, Nirmal. -Original Message- From: Mercadante, Thomas F [SMTP:[EMAIL PROTECTED]] Sent: Monday, November 26, 2001 10:21 PM To: Multiple recipients of list ORACLE-L Subject:RE: Oracle 9.0.1.1.1 on Win2k: stalls during installation: 85% "D Dan, It sounds like the 817 install left some trash around. If I were you, [Nirmal Kumar Muthu Kumaran] " I would go into the registry and remove all traces of Oracle. Then, reboot the win2k box and try your install again. Attached is the "Create a clean machine" document that I obtained a few years back from an Oracle install disk. I have used it many times, and it *always* solves install problems on NT for me. The trick is to remove all traces, and reboot to make sure that all dll's have been purged from the system. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 26, 2001 1:55 PM To: Multiple recipients of list ORACLE-L "Database Configuration Assistant" For that past few weeks, I have not for life of me been able to get Oracle 9.0.1.1.1 (free download) installed on my Win2k box. I can install it just fine on the same box inside of a Vmware WinXP Pro instance, or any other box, but not mine. Mine had Oracle 8.1.7 (Enterprise - not the free download) but I have uninstalled it. When I install Oracle 9.0.1.1.1 (using "Personal"), it goes fine until the screen where you select your database (General, Transactions, none, etc.). (No matter what I select) I click next, and it runs some
Antwort:
Hi use in mount state of database: alter database datafile '/data/db/database/core.dbf' offline drop; Regards Ivo Libal Moučka Otakar .cz> Kopie: Gesendet von:Thema: root@fatcity. com 11/21/2001 02:40 PM Bitte antworten an ORACLE-L Hello I need a help with one problem. In the Oracle version 8.1.7 I´ve deleted a "tablespace" in the Linux, but before this I did´t use a function " Drop". After reboot a database, there was a error: ERROR at line 1: ORA-01116: error in opening database file 7 ORA-01110: data file 7: '/data/db/database/core.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 Is there a some re-step to drop "tablespace" and then regulary delete this object and restart the database ? Thanx a lot for your answer. Ota Moucka -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?q?Mou=E8ka=20Otakar?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
IDL_UB1$
Hello All I found in our database, that some of the sys tables have many extents (up to 700). These are IDL_* tables and some other tables related to our heavy use of plsql packages (like sources$, dependency$ etc). On metalink I found that it is possible to change the "next" parameter of the table, but I would like to ask you if you know about a way how to recreate the existing fragmented tables? What option would be possible? (sorted by preference) 1.rebuild the table online without need to do anything else 2.rebuild the table online with a need to disconnect users 3.drop all packages/functions ..., truncate the tables (idl_ub1$ etc.) and load all sources from export (+catalog etc.) 4.recreate database + change next extent on the tables + load everything from export Or is there other posibility? The database is 8.1.7 on Linux Kind regards Ivo Libal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Antwort: komplex sql
maybe something like this: SELECT DECODE(mxurval_namn,'No Info','No Info','Missing Info','Missing Info',ltrim(substr(mxurval_namn,instr(mxurval_namn,' ' FROM mxurval; I dint try it Ivo Roland.Skoldb [EMAIL PROTECTED] An: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Gesendet von:Kopie: root@fatcity.Thema: komplex sql com 11/14/2001 11:50 AM Bitte antworten an ORACLE-L Hallo, How can I do a select statement that creates this: I have the field1 Jimmy Y1000 Timmy L3 No Info Missing Info and I want the select to give me this: Jimmy Timmy No Info Missing Info You see It should still be 'No info' and 'Missing Info'after the select statement. How can I change this statement? SELECT ltrim(substr(mxurval_namn,instr(mxurval_namn,' '))) FROM mxurval; Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Antwort: Re: Failures in creating Oracle database file.
Oracle does not support files larger than 2GB on Linux Doesnt matter if linux itself support it or not. Ivo Byron Pearce re.com> Kopie: Gesendet von:Thema: Re: Failures in creating Oracle database file. root@fatcity. com 11/14/2001 05:40 AM Bitte antworten an ORACLE-L Jesse: It sounds as if you may have run into the 2GB file size limit. Has your filesystem been created and mounted with the "largefiles" option? An easy way to check would be to try and create the tablepace with a 1GB or 2GB datafile and see if it works. "Jesse W. Asher" wrote: I'm attempting to create a 3GB file on an XFS filesystem using Oracle 8i running on RedHat 7.1 with a generic 2.4.10 kernel and I'm getting some errors. Here's the command and output I'm getting: CREATE TABLESPACE ALTUSER DATAFILE '/export/4/oracle/oradata/alt/data01.dbf' SIZE 3000M REUSE AUTOEXTEND ON NEXT 1280K MINIMUM EXTENT 128K DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0); CREATE TABLESPACE ALTUSER DATAFILE '/export/4/oracle/oradata/alt/data01.dbf' SIZE 3000M REUSE * ORA-01119: error in creating database file '/export/4/oracle/oradata/alt/data01.dbf' ORA-27037: unable to obtain file status Linux Error: 75: Value too large for defined data type = Any ideas as to what could be causing this? I thought the file size limit is 4GB which is why I made the file 3GB. Is this correct or am I missing something?? Many thanks!! -- Jesse W. Asher [EMAIL PROTECTED] -- Byron Pearce mailto:[EMAIL PROTECTED] Tenure Systems, Inc. Arlington, Texas "It's hard to be a ninja when you wear a beeper." -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Antwort: ORA - 12560: TNS: protocol adapter error
Hi did you set ORACLE_SID in your environment or registry? Ivo "S.Jyotinaray an" An: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Thema: ORA - 12560: TNS: protocol adapter error Gesendet von: root@fatcity. com 11/12/2001 10:30 AM Bitte antworten an ORACLE-L Hi, I have installed your Oracle 8i EE trial software. When i am trying to connect through SQLPLUS with username: SCOTT and password: TIGER i am getting the following error :- "ORA - 12560: TNS: protocol adapter error". Could you tell why i am getting this error? What other information should i provide you? Thanx in advance Jyotinarayan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
