High current mode buffer gets on insert
I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering almost 800,000. This is a daily warehouse load process, and I know that indexes are left in place during the load. Am I correct in concluding that the high CPU, and especially the current mode block gets numbering over 6 times the number of rows inserted, are due to index updates? Thanks! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel 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).
9IAS connections to database not resolving
About a month ago we had a small group of remote users that started using our 9IAS server to gain access to an online application. Previously these users were like the rest of our remote users using our 6i forms and reports servers. The group of users had to switch to 9ias because tunneling through their particular firewalls was not supported by Oracle's 6i forms and reports servers. Since the users have switched to 9ias I have had lots of trouble managing sessions. Every time a 9ias user prints a report(the sole purpose of the application actually) two database sessions are created. The sessions are not cleaned up. Eventually we either run out of available sessions(max_sessions) or our shared pool fills up. I already have a sqlnet.expire_time set in sqlnet.ora, and have implemented resouce_limit monitoring with an idle_time for our remote users. Neither of these have solved the problem. The reason being that sessions that are killed due to IDLE_TIME are 'sniped', and 'sniped' sessions are never cleaned up. They continue to exist, and consume resources. I have temporarily forced 9ias to connect via dedicated servers. This will allow me to clean up 'sniped' sessions by killing their server processes at the OS level. The above work around is the result of the notes Oracle Support provided. Unfortunately they have not been much help otherwise. Meaning they have not been able or willing to address the problem of 9ias creating the spurious sessions. Sorry for the long tale, but I am hoping someone else out there has run into this before. Our forms/reports configuration is way to generic for us to be the first ones to hit this. Thanks in advance, Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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: Sql*Loader problem...
JL, When you describe the field type in the SQLLoader file set the CHAR(4000) as the field description. You are correct in saying that the default is CHAR(255). As an example; col1 terminated by , , col2 CHAR(4000) terminated by , ... Ron [EMAIL PROTECTED] 03/13/03 01:44PM Hi... I would like to receive a bit of your help (I've been looking at Metalink with no luck). I'm having a sql*loader problem when I try to insert records in a table field. The problem is: my data file has a column with MORE THAN 255 characters long, and sql*loader rejects the records that exceed that limit! The data are generated by an app called NetCache. And, for auditing, we need to load the data into the oracle database. I'm having problems with just one column: URL_VISITED varchar2(4000) Any ideas, help would be appreciated!. I need to load a string like this: (A workaround could be SUBSTRac just the first 255 chars ) 'http://www.hotmail.msn.com/cgi-bin/sbox?did=1t=4zO *OzYH5DI!K5JDg2lw3GV*Z17S74coVgj5bSbi3Idhx6CPvVsK3Eh bHes!GVxjYRKdxmfIAmoF1SvuqW0Vd5rQ$$p=4wcx4xWmyGq6jd OZdt9566bmc4SziYsk6XKA60n*bkWwcK3UuKf8dzl1ykTjzYsAhK YN8wfQ6ay2rrR1FSRXj*djcyllOvUfV5H8drR6iESnR6PEig6lqH Cv7iH*opeUYbDd4IlZxxwRC6pqrKos5CLOW2iSBE5fp4L2g71b7E LgER0F!Eiw0XdUZsVKN!a17yI*bRtievO7lRyJefsNcjcA$$js=yes' Regards! JL __ 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: Jose Luis Delgado 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 Rogers 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).
NESTED_TABLE_GET_REFS hint
Hi, I'm currently defining an optimization strategy for a reporting environment. The reporting environment is refreshing 41 materialized views on a weekly basis. Some of the MV take over an 1 hour to refresh. During the MV refresh, in Statspack, I can see that the top SQL are Oraclegenerated SQL using the NESTED_TABLE_GET_REFS hint. From metalink : "If this is a DSS system,you probably have a smaller shared pool and use import/export heavily. If so,it might be a good idea to use a bigger shared pool when doing this activity." Anyone has more info on this. TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED]
RE: Excessive SQL*Net message from client waits
Jonathon et al, is it really true that every session is waiting on the others if as each session is spawned, it does its thing (i.e. issues some set of queries) and then disconnects? There are never two sessions doing something simultaneously really. The user logs in and only sees and works with one screen at a time. A session is spawned to do some initialization stuff...this one sticks around and may see a bit more activity before the logout...so I can see how this one would have the waits. But the other spawned sessions connect, do something and disconnect. These spawned sessions come from various controls on the screen...not different app occurrences or windows within the app. So, what I end up with are 10 separate trace files...one for each session connect period. Doesn't each trace file then only show that specific session's info and big spikes in SQL*Net message waits shouldn't carry over. I'll certainly try your idea about using netstat while tracing and see what I find. I feel as if I'm being thick-headed about this but I do not see this same behavior at every installation. These high SQL*Net message waits are showing up only at this one client site. Other pratically identical sites do not see this behavior. By practically identical I mean that other comparable sites have different network config. This particular site has it's database server 100 miles away from the users running the client application. WAN vs LAN. Just wish I could find a good reason why it's so different. Thanks so much, Karen Morton -Original Message- Lewis Sent: Thursday, March 13, 2003 12:19 PM To: Multiple recipients of list ORACLE-L I'd start by being doubtful about anybody being able to work so fast that the can avoid a high percentage of time in 'sql*net from client' - in fact, it the percentage was low (when the client was a person at a terminal) I would write myself a memo to check whether the client code was executing an extreme number of very small statements behind the scenes (e.g. get all keys for a drop-down, then get each drop down by key one at a time every time the user hit the field). It's always possible that the many layers of code at the client end are taking a surprising However, assuming you have a truly unreasonable loss of time on waiting for client - I would try and isolate the problem by using netstat and top. This can be hard in typical environments, though. Start up the client session - Start netstat running on the server with minimum snapshot time (usually one second). Start top (or similar) running in real time or minimum snapshot time. Start up event 10046 at the session. Then get the client to do something, and watch for: a)the peak in netstat as the request reaches the server. b)the burst from the server as the request is serviced c)the peak in netstat as the reply gets sent d)the delay before it appears on the client screen. It's crude, but simple-minded, and if the client is causing the problem it may prove it quite convincingly. Back it up with the trace file - which will record timestamps of a query coming in and results going out. The biggest problem, usually, is that it simply isn't realistic to get a system so quiet that you can get just one client running all by itself with nothing else going on. In your particular case, I have to sya that I have noticed that Java can use a surprising amount of CPU sometimes. 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: 13 March 2003 15:54 Good point, but what if each user only has a single session? Not that I've noticed this exact same situation here on one of our Engineering support databases whose clients are Java, and I'm not wondering if it has something to do with the application or if I can possibly speed it up with tweaks to SDU/TDU. I'm just wondering... ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] -- 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
RE: Corrected SQL Question...
All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: High consistent gets , 10046
Vadim, Apologies, I answered the question you didn't ask - viz why does it take so long, rather than the 'what are the CR gets'. Your second suggestion is the correct one. It seems unreasonable, but when you do the select for update, Oracle seems to go through a load of read- consistency work for the block to roll back the changes made by other transactions. The excess CR gets are accesses to the UNDO blocks need to build the CR image. Strangely, if you just slam in the 'update', rather than 'select for update' this phenomenon does not occur. 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: 13 March 2003 18:54 Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo segments in attemt to reconstruct CR block aged out from cache? Thanks Vadim -- 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: Perl Book
Programming the Perl DBI from Oreilly is really good for your need. -Original Message- From: Farnsworth, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Perl Book http://www.oreilly.com/catalog/oracleperl/ Play your cards right and you may even get an autographed copy. ;o) Dave -Original Message- From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:04 AM To: Multiple recipients of list ORACLE-L Subject: OTC: Perl Book Looks like I have a need write a Perl program to access a database. Any suggestions on a good book. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204
Re: why SAN ? why not external storage ?
Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -Aunix useridwith home directory attached to a paticular NFSdrive on NAS/SAN,will able to see allhis files when he logs into otherservers. so far I heard "Net App" is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can youshare some ofthereasons related to your decision in choosing a direct-attach storage(DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul
Is range partitioning possible on part of varchar2 column ??????
Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: DBXray anyone?
I've got installed - my boss likes BMC so we bought it. I really don't use it too much. It doesn't seem to have much different from OEM - a little better graphics, but since I have my set of scripts that I've come to rely on I really don't use the gui stuff a lot except for my freeware version of TOAD. [EMAIL PROTECTED] 03/13/03 12:00PM Anyone using BMC's DBXRay? Is it a decent product?-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Suzy Vordos INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Corrected SQL Question...
Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Corrected SQL Question... All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Stephen Lee 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
I am not suggesting that sessions are waiting for each other, or reporting each others' wait times. I am simply assuming that if the application design was daft enough to spawn multiple sessions, it probably was clever enough to have the parallel, independent threads of execution making all those sessions work concurrently at the client. Consequently, if the client 'spawns a session which then connects, does something and disconnects', I am assuming that the initial client is waiting for that session to complete and so (from Oracle's perspective) the initial client's session is waiting on (its own, and no-one else's) SQL*Net message from client. So if the initial client has spawned 9 other sessions, I would (perhaps unfairly) assume that at any one instant only one of them is actually doing anything - which is why on average I would not be surprised to see a 90% SQL*Net wait. Moving away from the SQL*Net bit though, my impression of the other stats was that the user could quite possibly see a significant turn around time between hitting a key and seeing a response. Given a limited number of messages (2,750 I think it was) to and from the client, the volume of direct reads and writes was high, and the number of log file sync waits was very high - with a surprising max wait on log file sync. The application seems to be committing over-enthusiastically - which stresses the log writer and log buffer latching anyway - but there is also a lot of stress on the I/O system from (probably) sorts or hash joins. Perhaps this site has a different data distribution, or set of indexes, that is making some execution paths very expensive, and bring into sharp relief an underlying problem with commit rates. . 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: 13 March 2003 20:29 Jonathon et al, is it really true that every session is waiting on the others if as each session is spawned, it does its thing (i.e. issues some set of queries) and then disconnects? There are never two sessions doing something simultaneously really. The user logs in and only sees and works with one screen at a time. A session is spawned to do some initialization stuff...this one sticks around and may see a bit more activity before the logout...so I can see how this one would have the waits. But the other spawned sessions connect, do something and disconnect. These spawned sessions come from various controls on the screen...not different app occurrences or windows within the app. -- 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: NESTED_TABLE_GET_REFS hint
The Metalink description of this hint seems a little obscure. My experience is that it simply allows you to reference a nested table directly without 'pseudo-joining' it to its rightful parent. Given the funny games (such as using a thoroughly spurious /*+ cardinality() */ hint, and bypassing the constraint on updateable join views) that Oracle plays for its internal purposes, I wouldn't be surprised to see them cheating like this on MV updates as well. It doesn't necessarily mean anything. 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: 13 March 2003 20:18 Hi, I'm currently defining an optimization strategy for a reporting environment. The reporting environment is refreshing 41 materialized views on a weekly basis. Some of the MV take over an 1 hour to refresh. During the MV refresh, in Statspack, I can see that the top SQL are Oracle generated SQL using the NESTED_TABLE_GET_REFS hint. From metalink : If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so,it might be a good idea to use a bigger shared pool when doing this activity. Anyone has more info on this. TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] -- 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: Corrected SQL Question...
Kirti, What about solution suggested by Stephane Faroult: select * from (select * from T connect by col1 = prior col2 and col1 col2) x where rownum = (select count(*) from T) / ? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 3:24 PM All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Igor Neyman 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[2]: why SAN ? why not external storage ?
Rahul, This is personal opinion, but it looks to me like your concerned about the database your creating for the client and may not have the total or corporate wide view your client has. We're heading down the SAN road not because of any specific database requirements but because disk storage has become an administrative nightmare as well as way too expensive. Dick Goulet Reply Separator Author: Arun Annamalai [EMAIL PROTECTED] Date: 3/13/2003 12:24 PM Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard Net App is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=iso-8859-1 META content=MSHTML 6.00.2800.1106 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff DIVFONT face=Arial size=2Usaually SAN and NAS is used for several good reasons...the two main are.../FONT/DIV DIVFONT face=Arial size=21) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front./FONT/DIV DIVFONT face=Arial size=22) reduce redundancy -/FONTFONT face=Arial size=2Anbsp;unix useridnbsp;with home directory attached to a paticular NFSnbsp;drive on NAS/SAN,nbsp;willnbsp; able to see allnbsp;his files when he logs into othernbsp;servers./FONT/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVFONT face=Arial size=2so far I heard Net App is low cost including with Raid 5./FONT/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVFONT face=Arial size=2-Arun./FONT/DIV DIVFONT face=Arial size=2Sr oracle dba/FONT/DIV BLOCKQUOTE dir=ltr style=PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #00 2px solid; MARGIN-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A [EMAIL PROTECTED] href=mailto:[EMAIL PROTECTED]Rahul/A /DIV DIV style=FONT: 10pt arialBTo:/B A [EMAIL PROTECTED] href=mailto:[EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Wednesday, March 12, 2003 9:38 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: why SAN ? why not external storage ?/DIV DIVBR/DIV DIVFONT face=Arial size=2my reasons to recommend an external storage was../FONT/DIV DIVFONT face=Arial size=21) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated /FONT/DIV DIVFONT face=Arial size=2by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? /FONT/DIV DIVnbsp;/DIV DIVFONT face=Arial size=22) NO DBA or SYS ADMIN skills to manage the SAN !! /FONT/DIV DIVnbsp;/DIV BLOCKQUOTE dir=ltr style=PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #00 2px solid; MARGIN-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV
RE: Is async IO configured on HP-UX?
On HP, I believe that async I/O is only supported for raw filesystems. See MetaLink for more details. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, March 13, 2003 1:36 PM To: Multiple recipients of list ORACLE-L The HP-UX syscall tracing tool is called 'tusc', and it's supposed to be available by visiting ftp://ftp.cup.hp.com/dist/networking/misc/. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 13, 2003 12:36 PM To: Multiple recipients of list ORACLE-L Ethan - it has been a while and we don't have an HP-UX box here, but if memory serves the missing /dev/async is a dead giveaway that your not using async io. to use async io on HP-UX we had to install something called the asyncdisk driver in SAM and then use mknod to create /dev/async directory. could the asyncdisk driver already be installed and you just need the directory? SAM should tell you. if not i think it means a kernel rebuild. -Original Message- Sent: Thursday, March 13, 2003 10:44 AM To: Multiple recipients of list ORACLE-L /dev does not have an async directory, async IO is turned on in the database, how do I determine if async IO is activated on the OS side of the house? Is the missing async directory an indicator? Also, is the database smart enough to figure out that the OS is not using async and make correct call or will I get a timeout on async call then perform standard write? Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Is range partitioning possible on part of varchar2 column ???
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax and
so am getting errors. Only TO_DATE function seems to be permitted. Since it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
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: Is range partitioning possible on part of varchar2 column ??????
No, I think you will have to add a column to store '' separately in order to partition on it. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:14 PM Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Igor Neyman 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: DBV Cannot Load Module LIBDCE.A - SOLUTION
Thanks Jared and Scott for your replies. Scott: I ran your lslpp -L |grep -i dce, but it did not produce any output. Which dbv did produce the expected output. I had opened up a TAR with Oracle this morning, and they set me straight in fairly short order. This problem is due to a bug. Solution was to use dbv from Oracle 7.3.4.5 AIX (which I do not have). So Oracle placed the file on their ftp server for me to download. It works fine now. For those interested, here is the text of the bug: Bug:1178468 - Abstract: USING DBV ON 7.3.4.5 AIX RESULT IN: COULD NOT LOAD LIBRARY LIBDCE.A[SHR.O] - RDBMS Ver: 7.3.4.5 - Status: 93,Closed, Not Verified by Filer - Fixed In Ver: 7.3.4.5 - O/S: 319 IBM RS 6000 AIX - DIAGNOSTIC ANALYSIS: This is caused by the dce protocol linked in the dbv executable but the needed library is not on the cst system. The 7.3.4.5 AIX patchset is created on a system with dce installed. It is not possible to (re)make the dbv executable because makefiles for dbv are not provided in the rdbms software. - WORKAROUND: Use the dbv executable which is in the AIx patchset 7.3.4.4. This executable does not need the library libdce.a Thanks again for your responses, Sam Bootsma George Brown College [EMAIL PROTECTED] 416-415-5000 x4933 -Original Message- From: Scott Behrens [mailto:[EMAIL PROTECTED] Sent: March 13, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBV Cannot Load Module LIBDCE.A Sam, I've never worked in a DCE environment, but here's a couple of things toverify: 1. If libdce.a is not installed in the first place, is your environment using DCE? I think this is an LPP (a separately licensed product) from IBM for AIX. Try the following from a unix prompt: $ lslpp -L |grep -i dce I think you should get hits like dce.client.rte.pthreads If you don't, then I wonder why dbv thinks it needs it... Try the following just to make sure you're running the dbv you think you are: $ which dbv It should return $ORACLE_HOME/bin/dbv, for example: $which dbv /u01/app/oracle/product/8.1.7/bin/dbv If it returns the Oracle 'dbv', then you might check the $ORACLE_HOME/rdbms/lib/sysliblist to see if it has an entry for libdce. If so, the product was linked at some point with the DCE Advanced Networking Option (see MetaLink Note 1043700.6 This docnotes the following: NOTE: You may have to manually remove the library reference from sysliblist. In Oracle 7.3.4, the Installer did not remove the library referenceduring a deinstallation.) In any case, I would be slow to add PTFs or relink or make other changes in a production environment just for the sake of dbv(if the rest of the environment is stable) until I had a better understanding of the opsys environment and the Oracle installation history. HTH, Scott [EMAIL PROTECTED] 3/12/03 5:15:58 PM Hi all, First, thanks Jared for the info. The libdce.a file does not exist on any of our AIX servers. It just AINT there! I typed libdce.a into google and from there downloaded PTF2,3, and 4 that are supposed contain DCE fixes to this file. Instructions say to rename the old file and replace it with this new file. After the download, I was not able to uncompress the files on my XP workstation. So I ftp'd the file (in binary mode) to our AIX server and used uncompress to expand the files to libdce2.exe, libdce3.exe, libdce4.exe (originally the files were named libdce2.exe.Z, etc.). The .exe extension confused me (I am told .exe is Windows only, not UNIX). I ftp'd the files back to my workstation, and tried running it from DOS. I get an error message Program too big to fit in memory. However, the exe is only 217,856 bytes (not kilobytes). My next step was to ask my neighbour if he could unzip the original libdce2.exe.Z on his W2K workstation - he was successful! However, he received the same message when he attempted to run the resultant .exe on his workstation. I am relatively new to UNIX (took a course, and did some reading, but not much hands-on). I am told by a more experience colleague that .exe's do not run on UNIX. So now I am at a loss on what to try next. Any ideas? Thanks, Sam Bootsma George Brown College [EMAIL PROTECTED] 416-415-5000 x4933 -Original Message- Sent: March 11, 2003 7:14 PM To: Multiple recipients of list ORACLE-L Just found it: on AIX ( 4.3 at least ) it's LIBPATH [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 03:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Re: DBV Cannot Load Module LIBDCE.A Make sure LD_LIBRARY_PATH includes /usr/lib. At least, I think it's LD_LIBRARY_PATH, been awhile since doing anything on AIX. In any case, libdce.a should be in
RE: Is range partitioning possible on part of varchar2 column ???
Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: 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: Excessive SQL*Net message from client waits
I think you can relatively safely argue that Oracle is spending 90% of it's time waiting for the client (by that a user pressing a button or the application processing some logic) - and therefore even if you make Oracle run infinitely fast you will only improve the application overall by 10%. Perhaps someone else can verify this. Jonathan explained, quite well, why the waits are so high... It the application spawns 10 sessions per user then each session will only be called once per approx. 10 SQL statements. Reducing the number of sessions will reduce the wait time on the report, but won't speed the application up. The stats indicate that the application fired ~3,000 queries in ~10 minutes (if I'm reading it right). That gives a stat of about 5 queries per second - it sounds like there is little you can do at the Oracle end of town. My guess is that the application is doing a lot of single row per query type statements when it should be working on a record set. It's a shame, but it looks like an application problem that Oracle can do very little to help out. Regards, Mark. Karen Morton [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lting.comcc: Sent by: Subject: RE: Excessive SQL*Net message from client waits [EMAIL PROTECTED] 13/03/2003 22:53 Please respond to ORACLE-L Not like this nor should it be the top event always as seems to be the case here I don't believe. And, I know for certain that the client did everything as quickly as possible during the trace. Minimal data entry done and OK buttons clicked without delay...no time out for getting a cup of coffee in between or anything. :) Karen -Original Message- Zanen Sent: Thursday, March 13, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Hi Isn't sql*net message from client always sort of on top, because it just means the rdbms is waiting for the client to send some query/command (user is not typing/clicking/reading fast enough) Jack -Original Message- Sent: donderdag 13 maart 2003 3:19 To: Multiple recipients of list ORACLE-L Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration # Calls AvgMin Max -- --- --- -- --- (i) SQL*Net message from client 500.98s 85.1% 2,757 0.181712s 0.00s 5.91s (i) unaccounted-for 23.03s3.9% (i) direct path write22.38s3.8% 1,373 0.016300s 0.00s 0.32s (i) log file sync20.70s3.5% 685 0.030219s 0.00s 0.52s (i) user-mode CPU12.12s2.1% 12,016 0.001009s 0.00s 1.50s (i) direct path read 6.66s1.1% 985 0.006761s 0.00s 0.09s (i) db file sequential read 1.09s0.2% 2,679 0.000407s 0.00s 0.14s (i) db file scattered read0.83s0.1% 2,158 0.000385s 0.00s 0.17s (i) SQL*Net more data to client 0.50s0.1% 1,007 0.000497s 0.00s 0.13s (i) SQL*Net more data from client 0.42s0.1% 5
java script and pl/sql again ???
Hi,
Thank you to all those answering my java script
question.
As I said, all the html and java script are generated
by pl/sql package. I'd like to know can the onClick
method below call a pl/sql procedure? If it cann't, I
suppose onClick needs to call a java script function
first, and in that java script function, how to call a
pl/sql procedure ??? (wl is a function used to
generate html)
Package pkgSth
AS
...
wl('input type=submit value=cancel
onClick=deleteData();
...
End;
Thank you!
Janet
__
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: Janet Linsy
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: Corrected SQL Question...
Igor Neyman wrote: Kirti, What about solution suggested by Stephane Faroult: select * from (select * from T connect by col1 = prior col2 and col1 col2) x where rownum = (select count(*) from T) / ? Igor Neyman, OCP DBA [EMAIL PROTECTED] Igor, I can answer that - col1 col2 worked with the first sample of data, not with the second one. The problem is with the loops in the tree - CONNECT BY doesn't like round-trips from an airport and back! And since you cannot put a subquery in a CONNECT BY, you're toast. I think, though, that you can probably use the tree walk if you do it in PL/SQL with a bulk select in an array. Previous experiments have shown to me that when the exception is raised, the data is returned anyway. Needless to say, it becomes messy :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Create rollback segment under ORACLE 9ir2 failed????
I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ 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: mike mon 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: Is range partitioning possible on part of varchar2 column ???
Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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).
log buffer space
I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
Re: Create rollback segment under ORACLE 9ir2 failed????
Mike, Check the setting for undo_management. It needs to be set to MANUAL. mike mon wrote: I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- 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 -- 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).
Fixed_date and dbms_job
Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ 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: Kader Ben 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: Create rollback segment under ORACLE 9ir2 failed????
Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL connect / as sysdba; SQL CREATE ROLLBACK SEGMENT dummy; Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ 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: mike mon 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: Pete Sharman 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: why SAN ? why not external storage ?
There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens [EMAIL PROTECTED] wrote: Rahul, This is personal opinion, but it looks to me like your concerned about the database your creating for the client and may not have the total or corporate wide view your client has. We're heading down the SAN road not because of any specific database requirements but because disk storage has become an administrative nightmare as well as way too expensive. Dick Goulet Reply Separator Author: Arun Annamalai [EMAIL PROTECTED] Date: 3/13/2003 12:24 PM Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard Net App is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=iso-8859-1 META content=MSHTML 6.00.2800.1106 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff DIVFONT face=Arial size=2Usaually SAN and NAS is used for several good reasons...the two main are.../FONT/DIV DIVFONT face=Arial size=21) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front./FONT/DIV DIVFONT face=Arial size=22) reduce redundancy -/FONTFONT face=Arial size=2Anbsp;unix useridnbsp;with home directory attached to a paticular NFSnbsp;drive on NAS/SAN,nbsp;willnbsp; able to see allnbsp;his files when he logs into othernbsp;servers./FONT/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVFONT face=Arial size=2so far I heard Net App is low cost including with Raid 5./FONT/DIV DIVFONT face=Arial size=2/FONTnbsp;/DIV DIVFONT face=Arial size=2-Arun./FONT/DIV DIVFONT face=Arial size=2Sr oracle dba/FONT/DIV BLOCKQUOTE dir=ltr style=PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #00 2px solid; MARGIN-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A [EMAIL PROTECTED] href=mailto:[EMAIL PROTECTED]Rahul/A /DIV DIV style=FONT: 10pt arialBTo:/B A [EMAIL PROTECTED] href=mailto:[EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Wednesday, March 12, 2003 9:38 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: why SAN ? why not external storage ?/DIV DIVBR/DIV DIVFONT face=Arial size=2my reasons to recommend an external storage was../FONT/DIV DIVFONT face=Arial size=21) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated /FONT/DIV DIVFONT face=Arial size=2by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? /FONT/DIV DIVnbsp;/DIV DIVFONT face=Arial size=22) NO DBA or SYS ADMIN skills to manage the SAN !! /FONT/DIV DIVnbsp;/DIV BLOCKQUOTE dir=ltr
RE: Is range partitioning possible on part of varchar2 column ???
No -Original Message- Sent: Thursday, March 13, 2003 5:49 PM To: Multiple recipients of list ORACLE-L ??? Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: 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: Fixed_date and dbms_job
Title: RE: Fixed_date and dbms_job After calling dbms_job.submit, did you issue a commit? -Original Message- From: Kader Ben [mailto:[EMAIL PROTECTED]] I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute.
RE: Create rollback segment under ORACLE 9ir2 failed????
Title: RE: Create rollback segment under ORACLE 9ir2 failed I see other people have already answered your question, but would it be presumptuous of me to ask why you are using ROLLBACK segments instead of an UNDO tablespace? -Original Message- From: mike mon [mailto:[EMAIL PROTECTED]] I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease 0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE'
Re: Create rollback segment under ORACLE 9ir2 failed????
Mike, et.al, Mea Culpa. Please ignore my previous post. I failed to properly context switch from my Automatic Undo mode. IIRC, as of 7.3, the requirement for a second rollback segment in SYSTEM was removed, with minor exceptions. I think the one that is biting you is that a second RBS must be created in SYSTEM if the rollback segment tablespace is locally managed. This is the default for 9i, where the default for 8i was dictionary. Check the allocation_type for the tablespace. If it is not dictionary, drop and recreate the tablespace as dictionary or create the second rbs in system. -- 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 Pete Sharman wrote: Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL connect / as sysdba; SQL CREATE ROLLBACK SEGMENT dummy; Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. __ 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: 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).
RE: Excessive SQL*Net message from client waits
Please ignore my silly comments about 3000 queries. My brain is waking up and realising that 3000 is the number of SQL*Net messages. In essence, ignore my message and listen to Jonathan. - Forwarded by Mark Richard/TRANSURBAN on 14/03/2003 09:56 - Mark Richard To: [EMAIL PROTECTED] 14/03/2003 cc: 08:55Subject: RE: Excessive SQL*Net message from client waits(Document link: Mark Richard) I think you can relatively safely argue that Oracle is spending 90% of it's time waiting for the client (by that a user pressing a button or the application processing some logic) - and therefore even if you make Oracle run infinitely fast you will only improve the application overall by 10%. Perhaps someone else can verify this. Jonathan explained, quite well, why the waits are so high... It the application spawns 10 sessions per user then each session will only be called once per approx. 10 SQL statements. Reducing the number of sessions will reduce the wait time on the report, but won't speed the application up. The stats indicate that the application fired ~3,000 queries in ~10 minutes (if I'm reading it right). That gives a stat of about 5 queries per second - it sounds like there is little you can do at the Oracle end of town. My guess is that the application is doing a lot of single row per query type statements when it should be working on a record set. It's a shame, but it looks like an application problem that Oracle can do very little to help out. Regards, Mark. Karen Morton [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lting.comcc: Sent by: Subject: RE: Excessive SQL*Net message from client waits [EMAIL PROTECTED] 13/03/2003 22:53 Please respond to ORACLE-L Not like this nor should it be the top event always as seems to be the case here I don't believe. And, I know for certain that the client did everything as quickly as possible during the trace. Minimal data entry done and OK buttons clicked without delay...no time out for getting a cup of coffee in between or anything. :) Karen -Original Message- Zanen Sent: Thursday, March 13, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Hi Isn't sql*net message from client always sort of on top, because it just means the rdbms is waiting for the client to send some query/command (user is not typing/clicking/reading fast enough) Jack -Original Message- Sent: donderdag 13 maart 2003 3:19 To: Multiple recipients of list ORACLE-L Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration #
RE: Create rollback segment under ORACLE 9ir2 failed????
And what Pete said does work. Here is a report from my testing of undo mode switching (AUM - MUM). Rollback tablespace was already created. SQL create rollback segment rbs01 tablespace rollback; create rollback segment rbs01 tablespace rollback * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK' SQL create rollback segment junk tablespace system; Rollback segment created. SQL create rollback segment rbs01 tablespace rollback; create rollback segment rbs01 tablespace rollback * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK' SQL alter rollback segment junk online; Rollback segment altered. SQL create rollback segment rbs01 tablespace rollback; Rollback segment created. SQL alter rollback segment rbs01 online; Rollback segment altered. SQL alter rollback segment junk offline; Rollback segment altered. SQL drop rollback segment junk; Rollback segment dropped. HTH, - Kirti -Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Mike The only way this would have worked under 8i is if you had already created a dummy rollback segment in the SYSTEM tablespace. Something like this should work (before or after the CREATE TABLESPACE rollback_space) SQL connect / as sysdba; SQL CREATE ROLLBACK SEGMENT dummy; Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, March 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am create database on ORACLE 9iR2 and fail on create rollback segment. SQL create tablespace rollback_space datafile 2 '/u4/oradata/TRAN/rbs01TRAN.dbf' size 800M 3 default storage ( 4 initial 256k 5 next 256k 6 pctincrease0 7 minextents 8 8 MAXEXTENTS 4096 9 ); Tablespace created. SQL SQL REM * Create rollback segments. SQL REM * SQL create rollback segment rollback_1 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_1 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' SQL create rollback segment rollback_2 tablespace rollback_space 2 storage (initial 256K next 256k minextents 20 optimal 5M); create rollback segment rollback_2 tablespace rollback_space * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK_SPACE' Those script used to work under ORACLE 8i. Does anyone know why? Thanks. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Corrected SQL Question...
Unfortunately, it is. - Kirti -Original Message- Sent: Thursday, March 13, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Corrected SQL Question... All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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).
Organizational Challenge - Data Management Team
Title: Message All, I would like to open a discussion to solicit information regarding the support structure you utilize in your Data Management department. We currently have a flat end-to-end approach whereby a dba adopts an application and subsequent database in the early planning stages via teaming up with the Data Architect and developers and owns that application all the way through design, development, testing, and ultimately production support. As a smaller group (3-5) dba's this model worked fine, and everyone knew their respective database quite well. As more and more applications (internal and 3rd party) continue to rollover from legacy systems into Oracle solutions, this is proving to be very challenging to provide 24x7 support and related on-call duties spanning three RDBMS platforms (Informix, Oracle, and MS SQL Server). Our challenges are two fold: One, we are (like any shop today) extremely overloaded with work requests, so this makes cross-application training to spread the knowledge nearly impossible to accomplish. Two, with everyone tied to a project, we have no resource with large enough buckets of time to take on new and imperative technologies such as java, replication, high availability, xml as examples that our development teams would like to leverage in the database. We are in the early stages of looking at organization alternatives. We are fortunate in that 90% of the database support is already centralized in our department for the company, so that allows us the ability to minimize every dba learning lessons the hard way. Specifically, we are considering some "role" divisions amongst the DBA's. That is to say a subset dedicated to "engineering" such as implementing and architecting new technologies and related best practices, a second subset for implementation of systems being developed, and a third subset for production support. I would like to hear about the organization structure you are involved with and the pro and cons of a flat structure as compared to a more "role" based structure. Thanks in advance, -Ron- Lead Oracle DBA
RE: log buffer space
Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data fileson the same disk) to othernot-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
RE: why SAN ? why not external storage ?
-Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens Disks are cheap. Reliable storage isn't, not really, not for large organizations. brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Dunbar 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).
TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor
Title: RE: monitor transactions over time Today, Oracle Support updated my TAR, stating that there won't be a patch released to fix this bug (#2506774) in 9i R2. Suggested workaround is to derive TXNCOUNT by subtracting the numbersfrom theprevious sample period. And when you write one, watch out for those -ve numbers for TXNCOUNT.. :-)) Somebody is watching this list.. seriously ;) Rajendra, youneed toput your script on e-bay ;) Regards, - Kirti -Original Message-From: Deshpande, Kirti Sent: Friday, March 07, 2003 9:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time From what IknowOracle Development folks have identified the code changes to correct thisproblem. Just do not when Oracle would issue the patch. Since the bug was logged against 9i R2, patch would be provided. This bug was originally logged in Aug 2002. There was no follow up. The other issue with v$undostat view is that it does not work in Manual Undo Mode.Forget using it while in Manual Undo Management mode to monitor your undo usageto size undo tablespace accordingly. Forget what the documents, white papers say. Some of them are 'syntactically' correct in saying, "This view is available in Automatic and Manual Undo Management mode." Yes, that is true. The view isavailable in MUM mode.But, it returns one useless row in 9i R1 and nothing in 9i R2. I was told by Oracle Development thatit did not work in 9i R1, in MUM mode,so they simply changed it to return nothing in 9i Rel 2. Hmmm... wonder if I followed this principle for some of the bugs in our Applications. ;) I will talk about this, and a few other things, in my Quick Tips Sessions, on AUM and FBQ,at the IOUG Conf next month. - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Friday, March 07, 2003 4:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time I wrote a script to fix the problem in 9202, but don't tell Oracle ... we want them to fix the bug. as soon as they know there is a workaround, the priority on the bug will go down. Log a iTar and request a patch ... the bug# is 2506744 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: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Friday, March 07, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: monitor transactions over time List, Does anybody know a way to monitor the number of transactions occurring over time, say 5 minute or 10 minute intervals? I am looking at v$undostat and it appears to have a problem accumulating transactions under txncount when it should report over a 10 minute interval ( metalink doc# 260990.995, query v$undostat) BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT - - -- -- 05-MAR-03 05-MAR-03 38 161519 05-MAR-03 05-MAR-03 24 161468 05-MAR-03 05-MAR-03 1 161227 05-MAR-03 05-MAR-03 4 161075 05-MAR-03 05-MAR-03 71 160881 05-MAR-03 05-MAR-03 6932 160748 05-MAR-03 05-MAR-03 8 160073 05-MAR-03 05-MAR-03 14545 159887 05-MAR-03 05-MAR-03 19588 159010 05-MAR-03 05-MAR-03 2333 157084 05-MAR-03 05-MAR-03 6972 152649 the undo blocks appear correct, but transactions are accumulating. Does anybody know how to use v$transaction or another view to do this? This is 9iRel2 on Unix and the application is geared toward transaction processing. thanks, David Ehresmann
RE: why SAN ? why not external storage ?
Disks are cheap until one asks for them ;) - Kirti -Original Message- Sent: Thursday, March 13, 2003 5:25 PM To: Multiple recipients of list ORACLE-L There are many things I don't get in this life. One of them is the statements about disk storage being an admin nightmare and way too expensive. Aren't disks very cheap these days?! Mogens [EMAIL PROTECTED] wrote: Rahul, This is personal opinion, but it looks to me like your concerned about the database your creating for the client and may not have the total or corporate wide view your client has. We're heading down the SAN road not because of any specific database requirements but because disk storage has become an administrative nightmare as well as way too expensive. Dick Goulet Reply Separator Author: Arun Annamalai [EMAIL PROTECTED] Date: 3/13/2003 12:24 PM Usaually SAN and NAS is used for several good reasons...the two main are... 1) High availability - When you have your database files on SAN/NAS then you can bring ur database on another server when the primary goes down. Obviously you have to use a cluster or Big IP (F5) on the front. 2) reduce redundancy -A unix userid with home directory attached to a paticular NFS drive on NAS/SAN, will able to see all his files when he logs into other servers. so far I heard Net App is low cost including with Raid 5. -Arun. Sr oracle dba - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 9:38 PM Subject: Re: why SAN ? why not external storage ? my reasons to recommend an external storage was.. 1) the database size is 36GB, and according to many documents i have read, SAN is not cost effevtive unless populated by a large numbers of drives !!, now for the client the cost is not the factor.. given the situation.. wouldnt a SAN be an overkill ? 2) NO DBA or SYS ADMIN skills to manage the SAN !! - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 8:33 PM Subject: Re: why SAN ? why not external storage ? Can you share some of the reasons related to your decision in choosing a direct-attach storage (DAS) instead of a SAN? In general, a SAN is a much smarter choice than DAS. - Original Message - From: Rahul To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 1:33 AM Subject: why SAN ? why not external storage ? list, one of our clietns are going to by SAN, the current oracle databases take around 36GB of storage i dnt understand there reason to go for SAN, i sugguested to buy an external storage box instead. How can i justify my desicion ? (cost of not the factor) TIA rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor
Title: RE: monitor transactions over time Make that bug #2506744. Sorry.. - Kirti -Original Message-From: Deshpande, Kirti Sent: Thursday, March 13, 2003 7:20 PMTo: '[EMAIL PROTECTED]'Subject: TXNCOUNT in V$UNDOSTAT (9i R2) [ Was -- RE: monitor transactions over time ] Today, Oracle Support updated my TAR, stating that there won't be a patch released to fix this bug (#2506774) in 9i R2. Suggested workaround is to derive TXNCOUNT by subtracting the numbersfrom theprevious sample period. And when you write one, watch out for those -ve numbers for TXNCOUNT.. :-)) Somebody is watching this list.. seriously ;) Rajendra, youneed toput your script on e-bay ;) Regards, - Kirti -Original Message-From: Deshpande, Kirti Sent: Friday, March 07, 2003 9:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time From what IknowOracle Development folks have identified the code changes to correct thisproblem. Just do not when Oracle would issue the patch. Since the bug was logged against 9i R2, patch would be provided. This bug was originally logged in Aug 2002. There was no follow up. The other issue with v$undostat view is that it does not work in Manual Undo Mode.Forget using it while in Manual Undo Management mode to monitor your undo usageto size undo tablespace accordingly. Forget what the documents, white papers say. Some of them are 'syntactically' correct in saying, "This view is available in Automatic and Manual Undo Management mode." Yes, that is true. The view isavailable in MUM mode.But, it returns one useless row in 9i R1 and nothing in 9i R2. I was told by Oracle Development thatit did not work in 9i R1, in MUM mode,so they simply changed it to return nothing in 9i Rel 2. Hmmm... wonder if I followed this principle for some of the bugs in our Applications. ;) I will talk about this, and a few other things, in my Quick Tips Sessions, on AUM and FBQ,at the IOUG Conf next month. - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Friday, March 07, 2003 4:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: monitor transactions over time I wrote a script to fix the problem in 9202, but don't tell Oracle ... we want them to fix the bug. as soon as they know there is a workaround, the priority on the bug will go down. Log a iTar and request a patch ... the bug# is 2506744 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: Ehresmann, David [mailto:[EMAIL PROTECTED]] Sent: Friday, March 07, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: monitor transactions over time List, Does anybody know a way to monitor the number of transactions occurring over time, say 5 minute or 10 minute intervals? I am looking at v$undostat and it appears to have a problem accumulating transactions under txncount when it should report over a 10 minute interval ( metalink doc# 260990.995, query v$undostat) BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT - - -- -- 05-MAR-03 05-MAR-03 38 161519 05-MAR-03 05-MAR-03 24 161468 05-MAR-03 05-MAR-03 1 161227 05-MAR-03 05-MAR-03 4 161075 05-MAR-03 05-MAR-03 71 160881 05-MAR-03 05-MAR-03 6932 160748 05-MAR-03 05-MAR-03 8 160073 05-MAR-03 05-MAR-03 14545 159887 05-MAR-03 05-MAR-03 19588 159010 05-MAR-03 05-MAR-03 2333 157084 05-MAR-03 05-MAR-03 6972 152649 the undo blocks appear correct, but transactions are accumulating. Does anybody know how to use v$transaction or another view to do this? This is 9iRel2 on Unix and the application is geared toward transaction processing. thanks, David Ehresmann
Re: Is range partitioning possible on part of varchar2 column ???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 5:19 PM
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table
based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax
and
so am getting errors. Only TO_DATE function seems to be permitted. Since
it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
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: Arup Nanda
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
AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data fileson the same disk) to othernot-so-busy disks. - Kirti -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: log buffer space I am finding tons of "log buffer space" waits in 10046 output . Does it necessarily means I should look for resizing log_buffer ? What else can be done or looked at to reduce these waits . Thanks, ak
Ot: Budding dba
Hello, are there any list of 5-10 questions which we can 'generally' ask to judge the 'potential' of a person to be an Oracle dba. These questions may include questions on attitude also. Cyril PS: I am seriously looking at hiring some 6months to 1 year experienced Oracle apps dba. So if someone knows anyone (!!!) please forward their cvs to [EMAIL PROTECTED] The positions are based in Bangalore, and of course I won't ask them the same 5-10 questions (!!) __ Great Travel Deals, Airfares, Hotels on http://r.rediff.com/r?www.journeymart.com/rediff/travel.aspsignjmart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cyril Thankappan 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).
Ot: Budding dba
Hello, are there any list of 5-10 questions which we can 'generally' ask to judge the 'potential' of a person to be an Oracle dba. These questions may include questions on attitude also. Cyril PS: I am seriously looking at hiring some 6months to 1 year experienced Oracle apps dba for my organisation. So if someone knows anyone (!!!) please forward their cvs to [EMAIL PROTECTED] The positions are based in Bangalore, and of course I won't ask them the same 5-10 questions (!!) __ Great Travel Deals, Airfares, Hotels on http://r.rediff.com/r?www.journeymart.com/rediff/travel.aspsignjmart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cyril Thankappan 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).
