RE: Agnostic references for Pracle v SQl Server 2000
Thanks Peter and indeed thanks to everyone else. It's nice to know that ones gut feeling is shared by ones peers. I had better start typing up that report :-) Martin -Original Message- [EMAIL PROTECTED] Sent: 24 September 2002 01:23 To: Multiple recipients of list ORACLE-L Hi Maybe you need to talk with a Developer to quickly do a rough estimate of costs involved in taking one on the more complex form/reports systems to VB/.net platforms. make sure they don't scrimp on cost but be realistic. I don't think a 1 page report is fair or does justice to the question so develop a longer report and produce a 1 page executive summary to present, but say a development cost of 1,000,000 in that report to move only one application might be a significant hindrance to the M$ is cheaper. Also beware that a lot of things run in Win2k/sqlserver, but to get the advanced features for say clustering you then need to be running Advanced Server along with M$ clustering software and the price point then runs up somewhat higher. Remember the MSDN licenses that will need to be acquired, along with NT licenses if you don't currently have them Oh and decent Intel based hardware for the DB servers. Also consider talking to your Oracle rep and see if the issue of pricing re Charity status cant be worked over. A lesser license to Oracle for a registered charity has to be better than no license fee in the present climate. my 0.02c Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Martin Kendall [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 24-09-2002 08:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Agnostic references for Pracle v SQl Server 2000 Hi Dennis, They have 5 databases, dating back to 7.1 and up to 8.1.7 They also have some very complex Oracle Forms and Reports. What they do not realise at the moment is the amount of work they will have to put in to convert the Apps. AND retrain the 4-5 members of staff who are more scientists than DP professional types. The data extraction and migration itself should not be a problem. The Oracle costs a lot of money to licence story has worried them quite a bit. Martin -Original Message- WILLIAMS Sent: 23 September 2002 21:49 To: Multiple recipients of list ORACLE-L Martin - Thanks for the clarification, but this prompts more questions. Is there a single database involved that multiple clients share the use of, or are you the vendor of a product that sits atop a database, but each client has their own database installed at their location. If it is the latter, then the correct answer would be for them to keep ported to both Oracle and SQL Server. My experience has been that Microsoft tries to arrange it so that if you have a Microsoft front-end (.NET or VB), interface to the database will be much easier. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:27 PM To: Multiple recipients of list ORACLE-L Hi Dennis, Personally I would like them not to move. Apart from all the Platform issues, they don't have ANY experience in .NET architecture and they only think it will be easier to administer the DB because they do not have any experienced Oracle DBA's in their employ. I am struggling to understand how they can possibly think of future-proofing their systems AND at the same time become a 100% Microsoft site. Martin -Original Message- WILLIAMS Sent: 23 September 2002 20:39 To: Multiple recipients of list ORACLE-L Martin - If I understand your first statement, the database is now on Oracle and you are writing a paper on why they should move it to MS SQL Server. If this is true, and given your other statements about the client, I would think you could get plenty of reasons from the Microsoft web site. Or did you mean to say you are trying to give them reasons not to move? Dennis Williams DBA Lifetouch, Inc.
RE: OEM Performance Manager
Hi Patrice, We usually find that a pretty acceptable monitoring period (for background alerting etc.) is around a minute for most collections anyway. This of course depends on the stats you are collecting as well though (if monitoring for space bound objects you don't want to set this off every minute in an SAP environment for example..). Most session/SGA/system stats can be collected on a 60 second basis, and still allow enough historical/diagnostic info to get to the bottom of problems. What chart were you running? Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Patrice J Sent: 23 September 2002 17:59 To: Multiple recipients of list ORACLE-L Do you have recommendations on which chart to run, and at what interval? The default interval in OEM 2.2. is every 15 seconds, but that caused my quad-CPU Windows system to crash. I reset it for every minute... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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.com -- Author: Mark Leith 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: patch backout
Managing patches with Oracle Applications is always a difficult task. The various sites I have worked on seem to have the same general policy 1)Do not apply a patch unless absolutely necessary. A lot depends on the type of patch, ie a simple fix is different from a big patch that can have all sorts of implications. 2)Try and apply a megapatch or patchset wherever possible. 3)Also test beforehand. Invariably a copy of the database at the same level is kept and a patchset will be applied and user testing will take place. Focuse especially on areas that have been customised. 4)Proper testing requires extensive user involvement. This is costly and has to be well planned. That is why applying a megapatch takes a bit of planning. It is also why it is easier to justify the cost and work involved if a number of issues are going to be addressed at the same time. 5) Minor, single issues patches can be applied (after testing of course) where the risk of failure is small. The best way is to ensure that you have a clean backup prior to application of the patch and therefore a good point to recover to. HTH John -Original Message- Sent: 23 September 2002 22:53 To: Multiple recipients of list ORACLE-L Hi List , What strategy you guys adapt for rolling back a database patch ( I am talking about application patch not the oracle software patch ) . For example if some table updates or some stored procs are going in .. how you guys backout patch if something doesn't work after the patch . I was thinking of taking export before applying patch and keeping it but that will be time consuming ..considering data . Other strategy might be for each ddl there should be an undo ddl and for each dml there should be an undo dml . but that's complicates the life considering number of changes that might go in patch . Any other ideas ?? Bp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Is the effect of modifying PCTFREE/PCTUSED immediate ?
Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hkchital 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: Idle Connections
how about setting the parameter sqlnet.expire_time=10 in the sqlnet.ora file ? ( the figure 10 is in minutes ) --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -Original Message- Samir Sent: Tuesday, September 10, 2002 9:48 PM To: Multiple recipients of list ORACLE-L Hi All, I am facing problems with a database with some very irresponsible users who just connect to the database from their applications and simply dont logout. Apart from the usual chidings I have been giving them, could any of you please tell me whether any parameter exists which can be put either in the sqlnet.ora file or database parameter file which will timeout and close the idle connections after a particular time interval ?? Thanks, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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 the effect of modifying PCTFREE/PCTUSED immediate ?
No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hkchital 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA work load
I have been asking damagement for another DBA with no luck. I am the only DBA for 5 Oracle instances and 16 SQL Server instances. We are a 24X7 shop Nobody else here know's anything about Oracle so I am on my own there and there are 2 other prople that can do some simple tasks in SQL Server but I get called when they can't fix it. I would think that damagement would want to have another person that can maintain their critical databases. I guess that is why they are damagers. Dave -Original Message- Sent: Tuesday, September 24, 2002 12:38 AM To: Multiple recipients of list ORACLE-L I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... ...there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... ...I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7 nor is instability in those environments... But the point is that the job of database administrator is like any other critical support role. Only the medical profession is so criminally idiotic as to expect and demand 30- and 40-hour shifts from its most valuable personnel... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 7:43 PM I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Group By - Without using aggregate functions
What's ROLLUP and CUBE is it Oracle's or is it from Java. TIA! Aleem -Original Message- Sent: Tuesday, September 24, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject:Re: Group By - Without using aggregate functions Check if ROLLUP or CUBE helps. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 01:28 AM Hi Gurus, I would like to group by the result of a select statement based on a particular column. But I am not using any aggregate function in the select list. For eg. select deptno, empno from emp group by deptno, empno; I tried to do it in sqlplus by setting BREAK ON column name and it works. But I want use this query in a java program. Is there any solution for this? TIA regards, Karthik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karthikeyan S 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.com -- Author: Nikunj Gupta 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.com -- Author: Abdul Aleem 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: DBA work load
What is a rebatable SLA? -Original Message- Sent: Tuesday, September 24, 2002 1:38 AM To: Multiple recipients of list ORACLE-L I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... .there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... .I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7 nor is instability in those environments... But the point is that the job of database administrator is like any other critical support role. Only the medical profession is so criminally idiotic as to expect and demand 30- and 40-hour shifts from its most valuable personnel... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 7:43 PM I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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
RE: Group By - Without using aggregate functions
Title: RE: Group By - Without using aggregate functions Rollup and cube are sql functions that place subtotals and grand totals into the query (usually shows the other columns as NULL to inidicate a total); Check the sql reference in the oracle docs for examples of use or the iternet. \ HTH Hannah -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of Abdul Aleem [EMAIL PROTECTED] Sent: Tuesday, September 24, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: RE: Group By - Without using aggregate functions What's ROLLUP and CUBE is it Oracle's or is it from Java. TIA! Aleem also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Group By - Without using aggregate functions
Hi Abdul, It is an Oracle thing. Check-out the docs. -Original Message- Aleem Sent: 24 September 2002 13:44 To: Multiple recipients of list ORACLE-L What's ROLLUP and CUBE is it Oracle's or is it from Java. TIA! Aleem -Original Message- Sent: Tuesday, September 24, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject:Re: Group By - Without using aggregate functions Check if ROLLUP or CUBE helps. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 01:28 AM Hi Gurus, I would like to group by the result of a select statement based on a particular column. But I am not using any aggregate function in the select list. For eg. select deptno, empno from emp group by deptno, empno; I tried to do it in sqlplus by setting BREAK ON column name and it works. But I want use this query in a java program. Is there any solution for this? TIA regards, Karthik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karthikeyan S 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.com -- Author: Nikunj Gupta 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.com -- Author: Abdul Aleem 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.com -- Author: Martin Kendall 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 SMON on openvms
Ron, Is it normal for your system manger to suspend Oracle processes? John -Original Message- Sent: 23 September 2002 20:24 To: Multiple recipients of list ORACLE-L List, I have a new server that I installed Oracle 8.1.7.3 with partitions and LMT. Some of the tables are quite large( in excess of 10 GIG) and I was creating the indexes when the communication channel was lost. Of course the rollback occurred but it was calculated to take in excess of 8 hours to complete. This was determined by SELECT count(*) from dba_extents where segment_name = 'TEMP'; the answer was 104313. 5 minutes later the answer was 103849. I had thought that all would go as planned and went on vacatio--- for a week as planned. This is a new development server that I am trying to set up before creating the database for our production server. Later during the rollback I got the snapshot to old message. I'll live with it for now but the next day I received can't allocate bytes in shared memory error and SMON went to 100 % CPU and stayed that way for 4 days. The sysadmin suspended the SMON process while I was away. I returned today and shutdown the database with shutdown abort, shutdown immediate hung. I restarted the database and all appeared well. I have a script that sums values in dba_free_space by tablespace_name and that appeared to be hung( not responding). I selected * from dba_free_space and the tablespace_name ='TEMP' had thousands of extents. I decided to halt the database and STARTUP MOUNT and ALTER DATABASE DATAFILE '...' OFFLINE DROP the datafile containing the TEMP tablespace. No problem as the database is not in archivelog mode. Then I open the database and DROP the TABLESPACE TEMP INCLUDING CONTENTS. This should allow me to create a new TEMP datafile and tablespace. The DROP TABLESPACE TEMP has been running for 4 hours now. I do not have exclusive use of the CPU as this server functions as a company production server for other processes besides Oracle. My questions; About how long would you guess that the drop tablespace action should take to complete? How do I check the progress and can I stop the progress and pick up where it stopped ( the production people are nervous that it will not be done when they need the server) Does any one have an Oracle Database and other production functions on the same server using OpenVMS? Thanks, Ron ROR mô¿ôm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA work load
Tony - I agree with Tim. This reminds me of the practice years ago of measuring the productivity of COBOL programmers by measuring their LOC (lines of code) production. Number of instances, how large, the number of developers or end users all have an effect. But how this all works out depends on many factors that are hard to quantify. Take 24x7 for example. All my instances are 24x7. I support production plants that are running 3 shifts at times. But, knock on wood, Oracle is pretty reliable and I usually don't get called. Some of our Unix servers have been up over a year. But I wouldn't classify my 24x7 alongside some eCommerce sites where the company's revenue depends on that site being up every minute. There is a lot of difference between developers. An experienced developer you've worked with for many years won't need the detailed assistance that a new developer will. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 8:43 PM To: Multiple recipients of list ORACLE-L I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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.com -- 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: DBA work load
I assume it is when penalty payments come into play when SLA targets are not met. Therefore payments for provision and support of an Oracle database are rebated John -Original Message- Sent: 24 September 2002 14:03 To: Multiple recipients of list ORACLE-L What is a rebatable SLA? -Original Message- Sent: Tuesday, September 24, 2002 1:38 AM To: Multiple recipients of list ORACLE-L I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... .there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... .I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7 nor is instability in those environments... But the point is that the job of database administrator is like any other critical support role. Only the medical profession is so criminally idiotic as to expect and demand 30- and 40-hour shifts from its most valuable personnel... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 7:43 PM I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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
Re:RE: Agnostic references for Pracle v SQl Server 2000
Martin, The cost of owning Oracle is NOT something to be overlooked, but not something to be scared about. In your case since the client is somewhat adamant about using SQL*Server have the local MicroSlop sales droid come up with the acquisition and support costs for SQL. And make sure he/she quotes it with all of the add-ons that Oracle provides as part of the package, like MicroSoft's equivalent to OEM. We did that with MS and IBM some months ago. Was that ever a surprise Oracle did come out more expensive at first blush but by the time the week was over they were all just about even (Oracle was a couple thousand $ cheaper due to the way the quotes were written). Dick Goulet Reply Separator Author: Martin Kendall [EMAIL PROTECTED] Date: 9/23/2002 2:53 PM Hi Dennis, They have 5 databases, dating back to 7.1 and up to 8.1.7 They also have some very complex Oracle Forms and Reports. What they do not realise at the moment is the amount of work they will have to put in to convert the Apps. AND retrain the 4-5 members of staff who are more scientists than DP professional types. The data extraction and migration itself should not be a problem. The Oracle costs a lot of money to licence story has worried them quite a bit. Martin -Original Message- WILLIAMS Sent: 23 September 2002 21:49 To: Multiple recipients of list ORACLE-L Martin - Thanks for the clarification, but this prompts more questions. Is there a single database involved that multiple clients share the use of, or are you the vendor of a product that sits atop a database, but each client has their own database installed at their location. If it is the latter, then the correct answer would be for them to keep ported to both Oracle and SQL Server. My experience has been that Microsoft tries to arrange it so that if you have a Microsoft front-end (.NET or VB), interface to the database will be much easier. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:27 PM To: Multiple recipients of list ORACLE-L Hi Dennis, Personally I would like them not to move. Apart from all the Platform issues, they don't have ANY experience in .NET architecture and they only think it will be easier to administer the DB because they do not have any experienced Oracle DBA's in their employ. I am struggling to understand how they can possibly think of future-proofing their systems AND at the same time become a 100% Microsoft site. Martin -Original Message- WILLIAMS Sent: 23 September 2002 20:39 To: Multiple recipients of list ORACLE-L Martin - If I understand your first statement, the database is now on Oracle and you are writing a paper on why they should move it to MS SQL Server. If this is true, and given your other statements about the client, I would think you could get plenty of reasons from the Microsoft web site. Or did you mean to say you are trying to give them reasons not to move? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 1:34 PM To: Multiple recipients of list ORACLE-L Hello all. I need to provide a one page report on why it may be beneficial for an organisation with light usage, small DB to move from Oracle to SQL Server. Their request is purely due to having a recognition of their charity status by Microsoft and therefore being able to get everything at a much reduced price. It is bad enough that they do not have experienced Oracle DBA's on site, now it seems that they are attracted by the apparent ease of use / setup/ Graphical everything. They are even looking at adopting the same development technology as one of their main benefactors as this may help them win further funding from such a benefactor. I am talking .NET and VB. I am also asked to phrase my paper from the point of future proofing their business technology. I am struggling with the agnostic approach when taking into account the concept of vendor lock-in. It seems that cost is everything. But they will need to accept that cost is not just what you pay for the base product. People, I am not really interested in a religious war on this - no doubt such discussion types have appeared on this List before. All I am asking for is pointers to any ref. material that you may know of. Happy days ! Martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: Agnostic references for Pracle v SQl Server 2000
Martin - Actually, Oracle can be cheaper than MS SQL. Both are offered in Standard and Enterprise Edition. But if you review the details, MS SQL EE stacks up closer to Oracle SE, which is cheaper. Of course, with a firm like yours, it pays to keep close to your customer attitudes and to diversify, that is to have experience in both. On the other hand, there isn't much ROI for changing databases from some theoretical future possibility, especially given the amount of Oracle-specific programming you have. To say nothing of the Oracle licensing money you'd flush. Be sure to include that in your report. But then we all get assigned to this sort of duty now and again. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 5:53 PM To: Multiple recipients of list ORACLE-L Hi Dennis, They have 5 databases, dating back to 7.1 and up to 8.1.7 They also have some very complex Oracle Forms and Reports. What they do not realise at the moment is the amount of work they will have to put in to convert the Apps. AND retrain the 4-5 members of staff who are more scientists than DP professional types. The data extraction and migration itself should not be a problem. The Oracle costs a lot of money to licence story has worried them quite a bit. Martin -Original Message- WILLIAMS Sent: 23 September 2002 21:49 To: Multiple recipients of list ORACLE-L Martin - Thanks for the clarification, but this prompts more questions. Is there a single database involved that multiple clients share the use of, or are you the vendor of a product that sits atop a database, but each client has their own database installed at their location. If it is the latter, then the correct answer would be for them to keep ported to both Oracle and SQL Server. My experience has been that Microsoft tries to arrange it so that if you have a Microsoft front-end (.NET or VB), interface to the database will be much easier. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:27 PM To: Multiple recipients of list ORACLE-L Hi Dennis, Personally I would like them not to move. Apart from all the Platform issues, they don't have ANY experience in .NET architecture and they only think it will be easier to administer the DB because they do not have any experienced Oracle DBA's in their employ. I am struggling to understand how they can possibly think of future-proofing their systems AND at the same time become a 100% Microsoft site. Martin -Original Message- WILLIAMS Sent: 23 September 2002 20:39 To: Multiple recipients of list ORACLE-L Martin - If I understand your first statement, the database is now on Oracle and you are writing a paper on why they should move it to MS SQL Server. If this is true, and given your other statements about the client, I would think you could get plenty of reasons from the Microsoft web site. Or did you mean to say you are trying to give them reasons not to move? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 1:34 PM To: Multiple recipients of list ORACLE-L Hello all. I need to provide a one page report on why it may be beneficial for an organisation with light usage, small DB to move from Oracle to SQL Server. Their request is purely due to having a recognition of their charity status by Microsoft and therefore being able to get everything at a much reduced price. It is bad enough that they do not have experienced Oracle DBA's on site, now it seems that they are attracted by the apparent ease of use / setup/ Graphical everything. They are even looking at adopting the same development technology as one of their main benefactors as this may help them win further funding from such a benefactor. I am talking .NET and VB. I am also asked to phrase my paper from the point of future proofing their business technology. I am struggling with the agnostic approach when taking into account the concept of vendor lock-in. It seems that cost is everything. But they will need to accept that cost is not just what you pay for the base product. People, I am not really interested in a religious war on this - no doubt such discussion types have appeared on this List before. All I am asking for is pointers to any ref. material that you may know of. Happy days ! Martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: DBA work load
just to add into the mix. it also depends on how much development is going on and if you are involved in that as well. I have 3 new applications going live next month, all brand-new databases and one project lead who doesn't understand the concept of a design spec and who keeps handing me major changes in email. If you have that, in addition to production, in a 24x7 shop, then you need help There should be at leat 2 DBAs -- what if you get sick or (as one of my bosses used to say) what if you get hit by a truck? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Tony - I agree with Tim. This reminds me of the practice years ago of measuring the productivity of COBOL programmers by measuring their LOC (lines of code) production. Number of instances, how large, the number of developers or end users all have an effect. But how this all works out depends on many factors that are hard to quantify. Take 24x7 for example. All my instances are 24x7. I support production plants that are running 3 shifts at times. But, knock on wood, Oracle is pretty reliable and I usually don't get called. Some of our Unix servers have been up over a year. But I wouldn't classify my 24x7 alongside some eCommerce sites where the company's revenue depends on that site being up every minute. There is a lot of difference between developers. An experienced developer you've worked with for many years won't need the detailed assistance that a new developer will. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 8:43 PM To: Multiple recipients of list ORACLE-L I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: RE: Agnostic references for Pracle v SQl Server 2000
Hi Dick, I have sent my response my paper in now and await their response. It will be interesting to see what they say. Martin -Original Message- [EMAIL PROTECTED] Sent: 24 September 2002 15:49 To: Multiple recipients of list ORACLE-L Martin, The cost of owning Oracle is NOT something to be overlooked, but not something to be scared about. In your case since the client is somewhat adamant about using SQL*Server have the local MicroSlop sales droid come up with the acquisition and support costs for SQL. And make sure he/she quotes it with all of the add-ons that Oracle provides as part of the package, like MicroSoft's equivalent to OEM. We did that with MS and IBM some months ago. Was that ever a surprise Oracle did come out more expensive at first blush but by the time the week was over they were all just about even (Oracle was a couple thousand $ cheaper due to the way the quotes were written). Dick Goulet Reply Separator Author: Martin Kendall [EMAIL PROTECTED] Date: 9/23/2002 2:53 PM Hi Dennis, They have 5 databases, dating back to 7.1 and up to 8.1.7 They also have some very complex Oracle Forms and Reports. What they do not realise at the moment is the amount of work they will have to put in to convert the Apps. AND retrain the 4-5 members of staff who are more scientists than DP professional types. The data extraction and migration itself should not be a problem. The Oracle costs a lot of money to licence story has worried them quite a bit. Martin -Original Message- WILLIAMS Sent: 23 September 2002 21:49 To: Multiple recipients of list ORACLE-L Martin - Thanks for the clarification, but this prompts more questions. Is there a single database involved that multiple clients share the use of, or are you the vendor of a product that sits atop a database, but each client has their own database installed at their location. If it is the latter, then the correct answer would be for them to keep ported to both Oracle and SQL Server. My experience has been that Microsoft tries to arrange it so that if you have a Microsoft front-end (.NET or VB), interface to the database will be much easier. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:27 PM To: Multiple recipients of list ORACLE-L Hi Dennis, Personally I would like them not to move. Apart from all the Platform issues, they don't have ANY experience in .NET architecture and they only think it will be easier to administer the DB because they do not have any experienced Oracle DBA's in their employ. I am struggling to understand how they can possibly think of future-proofing their systems AND at the same time become a 100% Microsoft site. Martin -Original Message- WILLIAMS Sent: 23 September 2002 20:39 To: Multiple recipients of list ORACLE-L Martin - If I understand your first statement, the database is now on Oracle and you are writing a paper on why they should move it to MS SQL Server. If this is true, and given your other statements about the client, I would think you could get plenty of reasons from the Microsoft web site. Or did you mean to say you are trying to give them reasons not to move? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 1:34 PM To: Multiple recipients of list ORACLE-L Hello all. I need to provide a one page report on why it may be beneficial for an organisation with light usage, small DB to move from Oracle to SQL Server. Their request is purely due to having a recognition of their charity status by Microsoft and therefore being able to get everything at a much reduced price. It is bad enough that they do not have experienced Oracle DBA's on site, now it seems that they are attracted by the apparent ease of use / setup/ Graphical everything. They are even looking at adopting the same development technology as one of their main benefactors as this may help them win further funding from such a benefactor. I am talking .NET and VB. I am also asked to phrase my paper from the point of future proofing their business technology. I am struggling with the agnostic approach when taking into account the concept of vendor lock-in. It seems that cost is everything. But they will need to accept that cost is not just what you pay for the base product. People, I am not really interested in a religious war on this - no doubt such discussion types have appeared on this List before. All I am asking for is pointers to any ref. material that you may know of. Happy days ! Martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing
RE: RMAN archive log - mystery deepens
Okay, I looked carefully at the RMAN report, and it automatically issues an alter system archive log current (duh!) Then I look at the three systems I backed up with RMAN last night. On two of the three, an archive log was produced 3 minutes before RMAN said it was completed. But on the largest system, there was an archive log timestamped 28 minutes prior and another 15 minutes after RMAN said it completed. Does this sound reasonable? Is it just if no changes had occurred maybe there was no current log to archive? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:34 PM To: '[EMAIL PROTECTED]' List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: RMAN need for alter system archive log all
Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Ruth Gramolini 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: DBA work load
Oh yea, and You are the only one knowing all the passwords :-) Rachel Carmichael [EMAIL PROTECTED] There should be at leat 2 DBAs -- what if you get sick or (as one of my bosses used to say) what if you get hit by a truck? Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: DBA work load
The intent of my reply was not to bring out stories about the exceptional and the fortunate, but to aid someone who is (presumably) trying to staff responsibly. There are 168 hours in a week -- most people prefer to work 40 of them (or less). Simple math. If you factor in holiday/vacations, training, sick-time, and leave, then they only work something like 32-35 hours per week or thereabouts. Factor in the frequency of meetings; the number of available hours decreases further... Obviously, not all of those 168 hours are equally intense, requiring a conscious person to be available on-call (you *do* get compensated for being on-call during off-hours, don't you?). Maybe none of them are. Maybe all of them are. Start with the premise of four people and add or subtract as local conditions warrant... I'm sure that someone will point out that it is not just the hours expended -- it is what is accomplished during those hours, how much is automated, measuring and improving processes, etc. Yes, quite true. Consider those to be factors that decrease the number of people actually needed from the baseline of four, just as the lack of those advantages may increase the number of people required. This way, you put value on those activities (and the people who perform such activities) in a way that is tangible to management... And just think: with Oracle9i, SQL Server, and Teradata, you don't need any DBAs at all... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 11:38 PM I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... ..there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... ..I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7
RE: Agnostic references for Oracle v SQl Server 2000
Well, it might be a smart move. From what I see on www.tpc.org, benchmarks on the lower end are usually performed with SQL Server 2000. DBA for SQL Server tend to be much cheaper for the organization then an oracle DBA. You can also get quite a decent support for SQL Server from a 3rd party and the total cost of ownership is very low for one or two PC servers. It is also a decent database, having row locking, hot backups and quite a decent version of SQL. Personally, I learned to love the temporary tables and I consider that logic to be much simpler for the duhveleopers then the Zen of SQL tuning that must be learned by the oracle duhvelopers. Unfortunately, oracle didn't create particularly user-friendly database on the low end and people are baffled with the variety of options that they don't know or understand. Oracle was developed on the VMS platform and there are still remnants of the VMS influence (afiedt.buf), which means that it was, and unfortunately still is, tailored for the knowledge of an average VMS system administrator, which was, generally speaking, extremely high when compared to the knowledge necessary for successfully managing an NT server. All that said, NT and Win2k have problems with security and stability. SQL Server is tied to that platform, so I wouldn't use it for 24x7 systems or high level transactional systems which need to process thousands of OLTP transactions per second. That is still oracle/DB2 playground. -Original Message- From: Martin Kendall [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 4:27 PM To: Multiple recipients of list ORACLE-L Subject: RE: Agnostic references for Pracle v SQl Server 2000 Hi Dennis, Personally I would like them not to move. Apart from all the Platform issues, they don't have ANY experience in .NET architecture and they only think it will be easier to administer the DB because they do not have any experienced Oracle DBA's in their employ. I am struggling to understand how they can possibly think of future-proofing their systems AND at the same time become a 100% Microsoft site. Martin -Original Message- WILLIAMS Sent: 23 September 2002 20:39 To: Multiple recipients of list ORACLE-L Martin - If I understand your first statement, the database is now on Oracle and you are writing a paper on why they should move it to MS SQL Server. If this is true, and given your other statements about the client, I would think you could get plenty of reasons from the Microsoft web site. Or did you mean to say you are trying to give them reasons not to move? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 1:34 PM To: Multiple recipients of list ORACLE-L Hello all. I need to provide a one page report on why it may be beneficial for an organisation with light usage, small DB to move from Oracle to SQL Server. Their request is purely due to having a recognition of their charity status by Microsoft and therefore being able to get everything at a much reduced price. It is bad enough that they do not have experienced Oracle DBA's on site, now it seems that they are attracted by the apparent ease of use / setup/ Graphical everything. They are even looking at adopting the same development technology as one of their main benefactors as this may help them win further funding from such a benefactor. I am talking .NET and VB. I am also asked to phrase my paper from the point of future proofing their business technology. I am struggling with the agnostic approach when taking into account the concept of vendor lock-in. It seems that cost is everything. But they will need to accept that cost is not just what you pay for the base product. People, I am not really interested in a religious war on this - no doubt such discussion types have appeared on this List before. All I am asking for is pointers to any ref. material that you may know of. Happy days ! Martin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Martin Kendall INET: [EMAIL PROTECTED] Fat City Network
BUFFER OVERFLOW, LIMIT OF 2000 BYTES.
Hi All, Below is a script to retrieve data from BFILE column and its output.The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput onDECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320);BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file);END;/== %PDF-1.3%bcOS1 0 obj /Creatorfeff001b7a68001b004d006900630072006f0073006f0066007400200057006f007200640020/CreationDate (D:19991019160202)/Titlefeff001b7a68001b00360031003100370070006200630031002e005000440046/Authorfeff001b7a68001b0052006f006200650072007400630075/Producer (AcrobatPDFWriter 4.0 for Windows)/ModDate (D:20001019200402+08'00') endobj2 0obj[ /PDF /Text /ImageB ]endobj3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R/F2 10 0 R /F0 6 0R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widths [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556556 556 556 556 556 556 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278500 667 556 833 722778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556556 500 556 556 278 556 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500DECLARE*ERROR at line 1:ORA-2: ORU-10027: buffer overflow, limit of 2000 bytesORA-06512: at "SYS.DBMS_OUTPUT", line 91ORA-06512: at "SYS.DBMS_OUTPUT", line 58ORA-065! ! 12: at line 17Do you Yahoo!? New DSL Internet Access from SBC & Yahoo!
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
I'm not sure either as I am rereading a document by Craig Shallamaher where he is saying to change pctused and pctfree in order to reduce data block fragmentation. I have to test that. At my new job, the DBAs are doing massive export/import to reduce fragmentation... (with their dictionnary managed tablespace) --- Jared Still [EMAIL PROTECTED] a écrit : Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: DBA work load
I totally agree! The more development support required the more DBAs needed and you should always have a backup in case you hit lotto :-). [EMAIL PROTECTED] 09/24/02 10:58AM just to add into the mix. it also depends on how much development is going on and if you are involved in that as well. I have 3 new applications going live next month, all brand-new databases and one project lead who doesn't understand the concept of a design spec and who keeps handing me major changes in email. If you have that, in addition to production, in a 24x7 shop, then you need help There should be at leat 2 DBAs -- what if you get sick or (as one of my bosses used to say) what if you get hit by a truck? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Tony - I agree with Tim. This reminds me of the practice years ago of measuring the productivity of COBOL programmers by measuring their LOC (lines of code) production. Number of instances, how large, the number of developers or end users all have an effect. But how this all works out depends on many factors that are hard to quantify. Take 24x7 for example. All my instances are 24x7. I support production plants that are running 3 shifts at times. But, knock on wood, Oracle is pretty reliable and I usually don't get called. Some of our Unix servers have been up over a year. But I wouldn't classify my 24x7 alongside some eCommerce sites where the company's revenue depends on that site being up every minute. There is a lot of difference between developers. An experienced developer you've worked with for many years won't need the detailed assistance that a new developer will. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 8:43 PM To: Multiple recipients of list ORACLE-L I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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.com -- Author: Gene Sais 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
RE: DBA work load
Title: RE: DBA work load yea I was going to add that as much as the hardware trend has demanded redundancy for failover so it would make sense that a production shop would apply that to its personnel as well. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBA work load just to add into the mix. it also depends on how much development is going on and if you are involved in that as well. I have 3 new applications going live next month, all brand-new databases and one project lead who doesn't understand the concept of a design spec and who keeps handing me major changes in email. If you have that, in addition to production, in a 24x7 shop, then you need help There should be at leat 2 DBAs -- what if you get sick or (as one of my bosses used to say) what if you get hit by a truck? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Tony - I agree with Tim. This reminds me of the practice years ago of measuring the productivity of COBOL programmers by measuring their LOC (lines of code) production. Number of instances, how large, the number of developers or end users all have an effect. But how this all works out depends on many factors that are hard to quantify. Take 24x7 for example. All my instances are 24x7. I support production plants that are running 3 shifts at times. But, knock on wood, Oracle is pretty reliable and I usually don't get called. Some of our Unix servers have been up over a year. But I wouldn't classify my 24x7 alongside some eCommerce sites where the company's revenue depends on that site being up every minute. There is a lot of difference between developers. An experienced developer you've worked with for many years won't need the detailed assistance that a new developer will. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 8:43 PM To: Multiple recipients of list ORACLE-L I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot 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.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 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).
Process question
Do I have something wrong with this query? I thought that each oracle OS process
corresponded to one database connection (unless you are using MTS, which we aren't).
This query surprised me. It shows that two different oracle database processes each
correspond to 3 database connections. Can someone explain this?
Thanks,
Jay
SQL run
1 select vs.username,last_call_et, vp.pid,
2 vs.sid,
3 vs.serial#,
4 vs.osuser,
5 vs.machine,
6 vs.process,
7 vp.spid
8 from v$session vs, v$process vp
9 where vs.paddr = vp.addr
10* and process in ('2949917','2952943')
USERNAME LAST_CALL_ETPID SIDSERIAL# OSUSER MACHINE
PROCESS SPID
-- - --
- - -
APPS13180 1511456 applmgr curly.pcsone.com
2949917 2950637
APPS13227 15 132951 applmgr curly.pcsone.com
2949917 2950637
APPS13746 15 157198 applmgr curly.pcsone.com
2949917 2950637
APPS12761136 125961 applmgr curly.pcsone.com
2952943 2953312
APPS12840136 171 9 applmgr curly.pcsone.com
2952943 2953312
APPS12808136 174 13 applmgr curly.pcsone.com
2952943 2953312
6 rows selected.
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: DBA work load
Unfortunately, all staffing exercises are driven by budgets and headcounts. The way to get more money and increase the headcount is to show the loss of revenue due to database outages. Any other reasoning is usually fruitless. The really high cost of your overtime and being on-call, and your performance showing signs of burn-out may help. Try to propose alternatives - training for other people, a contract with a small company specializing in providing dba services, management tools. inka -Original Message- Sent: Tuesday, September 24, 2002 11:08 AM To: Multiple recipients of list ORACLE-L The intent of my reply was not to bring out stories about the exceptional and the fortunate, but to aid someone who is (presumably) trying to staff responsibly. There are 168 hours in a week -- most people prefer to work 40 of them (or less). Simple math. If you factor in holiday/vacations, training, sick-time, and leave, then they only work something like 32-35 hours per week or thereabouts. Factor in the frequency of meetings; the number of available hours decreases further... Obviously, not all of those 168 hours are equally intense, requiring a conscious person to be available on-call (you *do* get compensated for being on-call during off-hours, don't you?). Maybe none of them are. Maybe all of them are. Start with the premise of four people and add or subtract as local conditions warrant... I'm sure that someone will point out that it is not just the hours expended -- it is what is accomplished during those hours, how much is automated, measuring and improving processes, etc. Yes, quite true. Consider those to be factors that decrease the number of people actually needed from the baseline of four, just as the lack of those advantages may increase the number of people required. This way, you put value on those activities (and the people who perform such activities) in a way that is tangible to management... And just think: with Oracle9i, SQL Server, and Teradata, you don't need any DBAs at all... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 11:38 PM I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... ..there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more
RE: RMAN need for alter system archive log all
Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Ruth Gramolini 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BUFFER OVERFLOW, LIMIT OF 2000 BYTES.
set serveroutput on size 100 (I think thats the max) Iain Nicoll -Original Message- Sent: Tuesday, September 24, 2002 5:53 PM To: Multiple recipients of list ORACLE-L Hi All, Below is a script to retrieve data from BFILE column and its output. The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput on DECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320); BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file); END; / == %PDF-1.3 %bcOS 1 0 obj /Creator feff001b7a68001b004d006900630072006f0073006f006600740020005700 6f007200640020 /CreationDate (D:19991019160202) /Title feff001b7a68001b003600310031003700700062006 30031002e005000440046 /Author feff001b7a68001b0052006f006200650072007400630075 /Producer (Acrobat PDFWriter 4.0 for Windows) /ModDate (D:20001019200402+08'00') endobj 2 0 obj [ /PDF /Text /Ima geB ] endobj 3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj 4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R /F2 10 0 R /F0 6 0 R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj 5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj 6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widt hs [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556 556 556 556 556 556 55 6 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278 500 667 556 833 722 778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556 556 500 556 556 278 55 6 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500 334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 5 00 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 DECLARE *ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-065! ! 12: at line 17 _ Do you Yahoo!? New DSL Internet http://rd.yahoo.com/evt=1207/*http://sbc.yahoo.com/ Access from SBC Yahoo! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BUFFER OVERFLOW, LIMIT OF 2000 BYTES.
You need to execute DBMS_OUTPUT.ENABLE (buffer size); with a max of 100 bytes. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: Meomeo Nguyen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Subject: BUFFER OVERFLOW, LIMIT OF 2000 BYTES. Hi All, Below is a script to retrieve data from BFILE column and its output.The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput on DECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320); BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file); END; / == %PDF-1.3 %bcOS 1 0 obj /Creator feff001b7a68001b004d006900630072006f0073006f006600740020005700 6f007200640020 /CreationDate (D:19991019160202) /Title feff001b7a68001b003600310031003700700062006 30031002e005000440046 /Author feff001b7a68001b0052006f006200650072007400630075 /Producer (Acrobat PDFWriter 4.0 for Windows) /ModDate (D:20001019200402+08'00') endobj 2 0 obj [ /PDF /Text /Ima geB ] endobj 3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj 4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R /F2 10 0 R /F0 6 0 R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj 5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj 6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widt hs [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556 556 556 556 556 556 55 6 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278 500 667 556 833 722 778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556 556 500 556 556 278 55 6 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500 334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 5 00 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 DECLARE *ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06 5! ! 12: at line 17 Do you Yahoo!? New DSL Internet Access from SBC Yahoo! The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
ORA-03113 with dbms_output and sysdate
Has anyone seen this cause an error before. I can run this script on other datbases (same version) without any problems. This database was rebuilt from production. I can select the to_char... into a varchar2 variable and then print the variable and it works. I've bounced the database also, but still no help. Nothing shows up in the alert log and there are no trace files. I have the work around, but I want to find out why/how to fix it and the root cause. Can I set an event and get more information about it when the error happens? That's the direction I'm headed. Thanks, Steve oracle 8.1.7.3 on Win NT 4.0 svc pack 6 sys(38)@INS declare 2 3 begin 4 5 dbms_output.put_line(to_char(sysdate, 'DY, MON DD HH24:MI:SS YYY') ); 6 7 end; 8 / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: : end-of-file on communication channel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN need for alter system archive log all
Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Ruth Gramolini 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.com -- 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Privileges needed for truncate
delete wasn't enough for me. had to grant 'drop any table'. From the Oracle8i SQL Reference Release 3 (8.1.7) manal: To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Privileges needed for truncate To truncate you need delete privileges. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 10:23 AM Hi I need to create a user/role that among other stuff must be able to truncate a table. I can't figure out which privileges are needed (DBA is a bit OTT :-)) Try them one by one does not sound appealing at all TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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.com -- Author: Glenn Travis 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
RE: ORA-03113 with dbms_output and sysdate
Steve works fine for me on 8171 on WinNT Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production With the Partitioning option JServer Release 8.1.7.1.1 - Production SQL set serveroutput on SQL DECLARE 2 3BEGIN 4 5dbms_output.put_line(TO_CHAR(SYSDATE, 'DY, MON DD HH24:MI:SSYYY') ); 6 7END; 8 9 / TUE, SEP 24 12:59:21002 PL/SQL procedure successfully completed. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:48 PM To: Multiple recipients of list ORACLE-L Has anyone seen this cause an error before. I can run this script on other datbases (same version) without any problems. This database was rebuilt from production. I can select the to_char... into a varchar2 variable and then print the variable and it works. I've bounced the database also, but still no help. Nothing shows up in the alert log and there are no trace files. I have the work around, but I want to find out why/how to fix it and the root cause. Can I set an event and get more information about it when the error happens? That's the direction I'm headed. Thanks, Steve oracle 8.1.7.3 on Win NT 4.0 svc pack 6 sys(38)@INS declare 2 3 begin 4 5 dbms_output.put_line(to_char(sysdate, 'DY, MON DD HH24:MI:SS YYY') ); 6 7 end; 8 / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: : end-of-file on communication channel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-03113 with dbms_output and sysdate
It is neither dbms_output nor sysdate. Try a loopback. This error is related to the network and I am sure that there is a lot on MetaLink. inka -Original Message- Sent: Tuesday, September 24, 2002 2:05 PM To: Multiple recipients of list ORACLE-L Steve works fine for me on 8171 on WinNT Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production With the Partitioning option JServer Release 8.1.7.1.1 - Production SQL set serveroutput on SQL DECLARE 2 3BEGIN 4 5dbms_output.put_line(TO_CHAR(SYSDATE, 'DY, MON DD HH24:MI:SSYYY') ); 6 7END; 8 9 / TUE, SEP 24 12:59:21002 PL/SQL procedure successfully completed. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:48 PM To: Multiple recipients of list ORACLE-L Has anyone seen this cause an error before. I can run this script on other datbases (same version) without any problems. This database was rebuilt from production. I can select the to_char... into a varchar2 variable and then print the variable and it works. I've bounced the database also, but still no help. Nothing shows up in the alert log and there are no trace files. I have the work around, but I want to find out why/how to fix it and the root cause. Can I set an event and get more information about it when the error happens? That's the direction I'm headed. Thanks, Steve oracle 8.1.7.3 on Win NT 4.0 svc pack 6 sys(38)@INS declare 2 3 begin 4 5 dbms_output.put_line(to_char(sysdate, 'DY, MON DD HH24:MI:SS YYY') ); 6 7 end; 8 / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: : end-of-file on communication channel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Inka Bezdziecka 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 the effect of modifying PCTFREE/PCTUSED immediate ?
John, Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued. I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block. This was on 8.1.7 on Linux. It's in the archives if you care to look for it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Privileges needed for truncate
I agree that this is a security hole. It scares me that someone would need this. I would have expected Oracle to correct this situation, but as of now, they have not. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject:RE: Privileges needed for truncate delete wasn't enough for me. had to grant 'drop any table'. From the Oracle8i SQL Reference Release 3 (8.1.7) manal: To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Privileges needed for truncate To truncate you need delete privileges. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 10:23 AM Hi I need to create a user/role that among other stuff must be able to truncate a table. I can't figure out which privileges are needed (DBA is a bit OTT :-)) Try them one by one does not sound appealing at all TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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
rebuilding an instance from a cpio file?
I have a huge cpio file that a sys admin created when it was decided to remove an instance (8i) from a test server. I don't know for certain what to do with this file, but I'd like to get that instance back up and running long enough to do an export of the db. Can somebody help with the syntax to list out the contents of the cpio file? I have read the man pages, but I can't figure out the syntax to just list the cpio contents. Thx, in advance, for you help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to reproduce a hanging connect attempt
Have you fooled with the CONNECT_TIMEOUT_LISTENER parameter of listener.ora? Setting it to 0 won't guarantee a connection will hang, but will tell a process to wait forever to connect. Hanging connections were a problem for us with the earlier Oracle 6 releases. My solution was less elegant. It used one program which attempted to connect, wrote a timestamp, and signaled if the connection failed ; another checked the timestamp against the current time and signaled if yhe difference was too great I cannot recall seeing the hanging problem for years, but we still run the program to check for it. I've been stating that three things can happen on an Oracle connection attempt for years: it can be successful, it can fail, or it can hang and return nothing. Yet, 100% of the scripts I see which attempt to connect to the database to ensure it is functional do not consider the third possibility. Seems with your upcoming article that percentage will drop to 99.. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 10:03 PM To: Multiple recipients of list ORACLE-L Dear List, As an example for an article I'm working on, I'm showing how a hanging connect can be timed out in a Perl script via the alarm() call. By 'hanging connect' I mean a connection attempt that never connects and never returns an error code. I have one right now on my Linux box. I started a database, did kill -9 on the oracle processes, and now attempts to login to the database hang. It's been that way for 24 hours now. e.g. sqlplus scott/tiger@ts98 ... never returns an error code, never connects. Guess it isn't going to connect. This could be a problem in a ksh script written to check connectivity. ( which is why I use Perl ) The question is, why? What is a consistent way to reproduce this error? The method I used isn't consistent. This is something that I see happen from time to time on Oracle databases, both NT and Unix platforms, hence the reason for the timeout on the connect. Any thoughts on how to consistently reproduce this, on either platform? Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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.com -- Author: MacGregor, Ian A. 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 the effect of modifying PCTFREE/PCTUSED immediate ?
Jared, So, that means that to remedy a case of data block fragmentation we just need to increase the pctused for the fragmented tables. Of course, things won't change as fast as an export/import but it's certainly less work to do. --- [EMAIL PROTECTED] a écrit : John, Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued. I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block. This was on 8.1.7 on Linux. It's in the archives if you care to look for it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: Privileges needed for truncate
Truncate is not a DML privilege that you could grant easily. It's very similar to DROP the table. Regards, Waleed -Original Message- Sent: Tuesday, September 24, 2002 2:43 PM To: Multiple recipients of list ORACLE-L I agree that this is a security hole. It scares me that someone would need this. I would have expected Oracle to correct this situation, but as of now, they have not. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject:RE: Privileges needed for truncate delete wasn't enough for me. had to grant 'drop any table'. From the Oracle8i SQL Reference Release 3 (8.1.7) manal: To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Privileges needed for truncate To truncate you need delete privileges. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 10:23 AM Hi I need to create a user/role that among other stuff must be able to truncate a table. I can't figure out which privileges are needed (DBA is a bit OTT :-)) Try them one by one does not sound appealing at all TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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
Re: ORA-03113 with dbms_output and sysdate
Check your ORA_NLS_33 parameter and make sure it is valid... Steve Perry [EMAIL PROTECTED] on 09/24/2002 01:48:25 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Has anyone seen this cause an error before. I can run this script on other datbases (same version) without any problems. This database was rebuilt from production. I can select the to_char... into a varchar2 variable and then print the variable and it works. I've bounced the database also, but still no help. Nothing shows up in the alert log and there are no trace files. I have the work around, but I want to find out why/how to fix it and the root cause. Can I set an event and get more information about it when the error happens? That's the direction I'm headed. Thanks, Steve oracle 8.1.7.3 on Win NT 4.0 svc pack 6 sys(38)@INS declare 2 3 begin 4 5 dbms_output.put_line(to_char(sysdate, 'DY, MON DD HH24:MI:SS YYY') ); 6 7 end; 8 / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: : end-of-file on communication channel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: rebuilding an instance from a cpio file?
I have a huge cpio file that a sys admin created when it was decided to remove an instance (8i) from a test server. I don't know for certain what to do with this file, but I'd like to get that instance back up and running long enough to do an export of the db. Can somebody help with the syntax to list out the contents of the cpio file? man 1 cpio; cpio -it $inputfile; # short listing (like ls) or cpio -itv $inputfile; # verbose listing (like ls -l) -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: Privileges needed for truncate
Jack, I have found that if a user needs to truncate a table then make the user the owner of the table that way the user will have all privileges on the table. The user then will grant the DBA all privileges with the grant option so the DBA can have some resemblence of control over the table. If you only allow a user to delete then as the DBA you have to export the table, truncate the table, and import the data to clean it up and defrag it. Ron ROR mª¿ªm [EMAIL PROTECTED] 09/24/02 02:43PM I agree that this is a security hole. It scares me that someone would need this. I would have expected Oracle to correct this situation, but as of now, they have not. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject:RE: Privileges needed for truncate delete wasn't enough for me. had to grant 'drop any table'. From the Oracle8i SQL Reference Release 3 (8.1.7) manal: To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege. -Original Message- From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Privileges needed for truncate To truncate you need delete privileges. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 10:23 AM Hi I need to create a user/role that among other stuff must be able to truncate a table. I can't figure out which privileges are needed (DBA is a bit OTT :-)) Try them one by one does not sound appealing at all TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communica tion please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City
RE: quckways to find block corruption
Dennis, Agreed. But when working with Oracle (not MS$) it is not very common to encounter block corruption frequently and you should look for corruption all the time. Working with Oracle databases since ver 5 (atleast 14 years now), I have encountered this once, 2 years back and that was a index corruption becuase I encountered a bug in 7.3.4.3 HP-UX (Oracle Financials 10.7) while rebuilding composite index with parallel clause although I was using the same scripts on 7.3.4.0 on NCR/ATT Unix (again Oracle Financials 10.7) and never found any problem. Of course, it was tracked in alertSID.log next working day and I have to analyze table with cascade and dropping and recreating all composite indexes. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Mon, 23 Sep 2002 11:38:56 -0800 Rafiq - But by then your users have experienced the corruption and you have a crisis on your hands. I feel the idea is to find the corruption BEFORE the users find it. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 12:09 PM To: Multiple recipients of list ORACLE-L alertSID.log if you have any corrupted block in your database and data is being retrieved/accessed from that blockThis is the first place where data block corruption is reported/recorded. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Mon, 23 Sep 2002 02:33:19 -0800 Hi Doesn't full export to /dev/null do this? Jack kommareddy sreenivasa To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) om Subject: quckways to find block corruption Sent by: [EMAIL PROTECTED] 23-09-2002 12:03 Please respond to ORACLE-L Hello all, DB: 8i OS: solaris 2.7 can somebody post me reply for this. is there any quick way to find which datablocks are corrupted in my oracle database . ( other than dbverify and rman backup. ) b'coz we have BCV backup already implemented and we cannot do a dbv every week for 500 gig production database . thanks in advance, srinivas __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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). === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication
RE: How to reproduce a hanging connect attempt
Thanks for the info Ian. I've been asked to prove why sqlplus and ksh are not adequate for checking connectity. The third possibility, a hang, is exactly that reason. I'm trying to duplicate what can actually happen to cause a hanging connection. I've been burned by that in the past when my script didn't properly allow for hangs. Jared MacGregor, Ian A. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 11:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: How to reproduce a hanging connect attempt Have you fooled with the CONNECT_TIMEOUT_LISTENER parameter of listener.ora? Setting it to 0 won't guarantee a connection will hang, but will tell a process to wait forever to connect. Hanging connections were a problem for us with the earlier Oracle 6 releases. My solution was less elegant. It used one program which attempted to connect, wrote a timestamp, and signaled if the connection failed ; another checked the timestamp against the current time and signaled if yhe difference was too great I cannot recall seeing the hanging problem for years, but we still run the program to check for it. I've been stating that three things can happen on an Oracle connection attempt for years: it can be successful, it can fail, or it can hang and return nothing. Yet, 100% of the scripts I see which attempt to connect to the database to ensure it is functional do not consider the third possibility. Seems with your upcoming article that percentage will drop to 99.. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 10:03 PM To: Multiple recipients of list ORACLE-L Dear List, As an example for an article I'm working on, I'm showing how a hanging connect can be timed out in a Perl script via the alarm() call. By 'hanging connect' I mean a connection attempt that never connects and never returns an error code. I have one right now on my Linux box. I started a database, did kill -9 on the oracle processes, and now attempts to login to the database hang. It's been that way for 24 hours now. e.g. sqlplus scott/tiger@ts98 .. never returns an error code, never connects. Guess it isn't going to connect. This could be a problem in a ksh script written to check connectivity. ( which is why I use Perl ) The question is, why? What is a consistent way to reproduce this error? The method I used isn't consistent. This is something that I see happen from time to time on Oracle databases, both NT and Unix platforms, hence the reason for the timeout on the connect. Any thoughts on how to consistently reproduce this, on either platform? Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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.com -- Author: MacGregor, Ian A. 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: rebuilding an instance from a cpio file?
Don, cpio -it filename will list the contents of a valid cpio file. Ron [EMAIL PROTECTED] 09/24/02 02:53PM I have a huge cpio file that a sys admin created when it was decided to remove an instance (8i) from a test server. I don't know for certain what to do with this file, but I'd like to get that instance back up and running long enough to do an export of the db. Can somebody help with the syntax to list out the contents of the cpio file? I have read the man pages, but I can't figure out the syntax to just list the cpio contents. Thx, in advance, for you help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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.com -- 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).
RE: rebuilding an instance from a cpio file?
Title: RE: rebuilding an instance from a cpio file? here's an excellent link that will give you more than you asked for. http://www.onlamp.com/pub/a/bsd/2002/07/11/FreeBSD_Basics.html -Original Message- From: Don [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Subject: rebuilding an instance from a cpio file? I have a huge cpio file that a sys admin created when it was decided to remove an instance (8i) from a test server. I don't know for certain what to do with this file, but I'd like to get that instance back up and running long enough to do an export of the db. Can somebody help with the syntax to list out the contents of the cpio file? I have read the man pages, but I can't figure out the syntax to just list the cpio contents. Thx, in advance, for you help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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: RMAN need for alter system archive log all
Egads, I made a stupid misfire of the brain. The current log IS switched during an archive log backup (i.e. BACKUP ARCHIVELOG ALL). Sorry for the mistypo... RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 2:35 PM To: '[EMAIL PROTECTED]' What version of RMAN are you talking about. In the 9iR2 documentation for RMAN is clearly states that during backups using the PLUS ARCHIVELOG command, RMAN does force a log switch. When you do a backup using the PLUS ARCHIVELOG command RMAN will: Run ALTER SYSTEM ARCHIVE LOG CURRENT Runs the BACKUP ARCHIVELOG ALL command. Backs up the files specified in the BACKUP command. Run ALTER SYSTEM ARCHIVE LOG CURRENT again. Back up any remaining archived redo logs. A normal backup of archived redo logs such as BACKUP ARCHIVELOG ALL will not result in a log switch. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: RMAN need for alter system archive log all
Robert, Do you know - is this a new feature in 9i? I don't see this happening in 8.1.7. I think it's a good thing, however! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Egads, I made a stupid misfire of the brain. The current log IS switched during an archive log backup (i.e. BACKUP ARCHIVELOG ALL). Sorry for the mistypo... RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 2:35 PM To: '[EMAIL PROTECTED]' What version of RMAN are you talking about. In the 9iR2 documentation for RMAN is clearly states that during backups using the PLUS ARCHIVELOG command, RMAN does force a log switch. When you do a backup using the PLUS ARCHIVELOG command RMAN will: Run ALTER SYSTEM ARCHIVE LOG CURRENT Runs the BACKUP ARCHIVELOG ALL command. Backs up the files specified in the BACKUP command. Run ALTER SYSTEM ARCHIVE LOG CURRENT again. Back up any remaining archived redo logs. A normal backup of archived redo logs such as BACKUP ARCHIVELOG ALL will not result in a log switch. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: rebuilding an instance from a cpio file?
cpio -itv -I your file -Original Message- From: Don [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Subject: rebuilding an instance from a cpio file? I have a huge cpio file that a sys admin created when it was decided to remove an instance (8i) from a test server. I don't know for certain what to do with this file, but I'd like to get that instance back up and running long enough to do an export of the db. Can somebody help with the syntax to list out the contents of the cpio file? I have read the man pages, but I can't figure out the syntax to just list the cpio contents. Thx, in advance, for you help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN need for alter system archive log all
What version of RMAN are you talking about. In the 9iR2 documentation for RMAN is clearly states that during backups using the PLUS ARCHIVELOG command, RMAN does force a log switch. When you do a backup using the PLUS ARCHIVELOG command RMAN will: Run ALTER SYSTEM ARCHIVE LOG CURRENT Runs the BACKUP ARCHIVELOG ALL command. Backs up the files specified in the BACKUP command. Run ALTER SYSTEM ARCHIVE LOG CURRENT again. Back up any remaining archived redo logs. A normal backup of archived redo logs such as BACKUP ARCHIVELOG ALL will not result in a log switch. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Ruth Gramolini 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
Procedural Consistency
Okay, I know I'm being a little lazy on this one, but I'm very interested to hear the ideas/conjecture/proof. So away we go We recently encountered a bug in Oracle where a long running process attempted to execute a procedure that was within a package that had been recompiled since the process had first executed the procedure. This brings up the question as to whether the kernel requires that each time the process executesthe procedure that the procedure is exactly the same as when the process first executed it. If so, how does the process keep track of the version of the procedure that it has previously executed? Any input/thoughts are greatly appreciated... Dan
sqlplus output from query with xmlelement
I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BUFFER OVERFLOW, how to retrieve data from BFILE column
Hi Karniotis, Thanks for your response. I do appreciate it. Well, after set serveroutput on size 6 or so and executing DBMS_OUTPUT.ENABLE(6), I am still unable to view the content of the BFILE column. There are a lot of garbage in the output. Anyone knows how to write a script to view data from the BFILE column, please help. Thanks Lenka "Karniotis, Stephen" <[EMAIL PROTECTED]>wrote: You need to execute DBMS_OUTPUT.ENABLE (buffer size); with a max of 100 bytes. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message-From: Meomeo Nguyen [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 24, 2002 12:53 PMTo: Multiple recipients of list ORACLE-LSubject: BUFFER OVERFLOW, LIMIT OF 2000 BYTES. Hi All, Below is a script to retrieve data from BFILE column and its output.The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput onDECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320);BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file);END;/=! ! = %PDF-1.3%bcOS1 0 obj /Creatorfeff001b7a68001b004d006900630072006f0073006f0066007400200057006f007200640020/CreationDate (D:19991019160202)/Titlefeff001b7a68001b00360031003100370070006200630031002e005000440046/Authorfeff001b7a68001b0052006f006200650072007400630075/Producer (AcrobatPDFWriter 4.0 for Windows)/ModDate (D:20001019200402+08'00') endobj2 0obj[ /PDF /Text /ImageB ]endobj3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R/F2 10 0 R /F0 6 0R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widths [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556556 556 556 556 556 556 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278500 667 556 833 722778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556556 500 556 556 278 556 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500DECLARE*ERROR at line 1:ORA-2: ORU-10027: buffer overflow,! ! limit of 2000 bytesORA-06512: at "SYS.DBMS_OUTPUT", line 91ORA-06512: at "SYS.DBMS_OUTPUT", line 58ORA-06 5! ! 12: at line 17 Do you Yahoo!?New DSL Internet Access from SBC Yahoo! The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. Do you Yahoo!? New DSL Internet Access from SBC & Yahoo!
RE: RMAN need for alter system archive log all
H ya know, maybe it is a new feature in 9i. I went and tried it because I was thinking that it did do the log switch, but I just wasn't 100% sure... I did it in 9iR2 (backup archivelog all) and it does do the log switch (it even says in the output, switching current log, and I also had logfiles generated in the archive log destination. I was the only one on the system, so I know it wasn't a chance switch. Still, maybe in 8i it doesn't. I'll try it on 8i and see if it's different. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Egads, I made a stupid misfire of the brain. The current log IS switched during an archive log backup (i.e. BACKUP ARCHIVELOG ALL). Sorry for the mistypo... RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 2:35 PM To: '[EMAIL PROTECTED]' What version of RMAN are you talking about. In the 9iR2 documentation for RMAN is clearly states that during backups using the PLUS ARCHIVELOG command, RMAN does force a log switch. When you do a backup using the PLUS ARCHIVELOG command RMAN will: Run ALTER SYSTEM ARCHIVE LOG CURRENT Runs the BACKUP ARCHIVELOG ALL command. Backs up the files specified in the BACKUP command. Run ALTER SYSTEM ARCHIVE LOG CURRENT again. Back up any remaining archived redo logs. A normal backup of archived redo logs such as BACKUP ARCHIVELOG ALL will not result in a log switch. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and archivelog, on one tape and rman will know just what he needs to recover a database. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 5:43 PM List - I am wrapping up my RMAN procedures so I can turn off conventional backups and still sleep at night. I am on Oracle 8.1.6. Does anyone know the answer to this question: If I do an RMAN backup to disk, then copy the RMAN backup pieces to tape, and copy the archive logs to tape, do I need to execute an ALTER SYSTEM ARCHIVE LOG ALL before I copy the archive logs to tape? This has been necessary for hot backups, but I can't recall this being mentioned specifically for RMAN. I want to be able to recover the database from the contents of the backup tape alone, and I noticed during my disaster recovery tests that you definitely needed to have required archive logs available. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official
RE: sqlplus output from query with xmlelement
If it's long then use: set long big number -Original Message- Sent: Tuesday, September 24, 2002 5:09 PM To: Multiple recipients of list ORACLE-L I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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.com -- 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
John, You are right, I just find out note 1029850.6 on metalink : A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. --- [EMAIL PROTECTED] a écrit : Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: sqlplus output from query with xmlelement
Magaliff, Bill wrote: I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill col loanapp format A500 ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: Procedural Consistency
Fink, Dan wrote: Okay, I know I'm being a little lazy on this one, but I'm very interested to hear the ideas/conjecture/proof. So away we go We recently encountered a bug in Oracle where a long running process attempted to execute a procedure that was within a package that had been recompiled since the process had first executed the procedure. This brings up the question as to whether the kernel requires that each time the process executes the procedure that the procedure is exactly the same as when the process first executed it. If so, how does the process keep track of the version of the procedure that it has previously executed? Any input/thoughts are greatly appreciated... Dan Dan, Part of the answer is in the existence of the initialization section of a package. If the initialization section is modified after a session has called the package, it may look uncomfortable for this session. To keep track of everything, I would personally call time() every time I call a procedure I have not yet executed, and check that it has not been modified since then the next times I call it, but this is a pure guess. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: sqlplus output from query with xmlelement
actually I just found this in the docs: The default width of datatype columns is the width of the column in the database. The column width of a LONG, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller, so I set BOTH LONG and LONGCHUNKSIZE to 32K and it worked beautifully. thx bill -Original Message- Sent: Tuesday, September 24, 2002 5:29 PM To: Multiple recipients of list ORACLE-L If it's long then use: set long big number -Original Message- Sent: Tuesday, September 24, 2002 5:09 PM To: Multiple recipients of list ORACLE-L I've just begun playing with XML stuff in 9i (9.2.0) so please forgive what might be an obvious question . . . I have a query to select data - returns two rows, in XML format (using XMLELEMENT for each field). The results for each row get truncated, and I can't figure out which parameter controls this output - tried various SQL*Plus params (linesize, pagesize, etc) but no luck. this is the output - LOANAPP --- LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro LoanApplication LoanRec24977/LoanRec LoanIDL024977/LoanID Borro thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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.com -- 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.com -- Author: Magaliff, Bill 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: RMAN need for alter system archive log all
9i backup results. Note current log archived message. Starting backup at 24-SEP-02 current log archived channel TMSP_t1: starting archive log backupset channel TMSP_t1: specifying archive log(s) in backup set input archive log thread=1 sequence=110 recid=1 stamp=473438751 input archive log thread=1 sequence=111 recid=2 stamp=473438824 input archive log thread=1 sequence=112 recid=3 stamp=473444662 input archive log thread=1 sequence=113 recid=4 stamp=473444730 channel TMSP_t1: starting piece 1 at 24-SEP-02 released channel: TMSP_t1 8i Results RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: t1 RMAN-08500: channel t1: sid=21 devtype=SBT_TAPE RMAN-08526: channel t1: VERITAS NetBackup for Oracle8 - Release 3.4GA (030800) RMAN-03022: compiling command: backup RMAN-03023: executing command: backup RMAN-08009: channel t1: starting archivelog backupset RMAN-08502: set_count=133 set_stamp=473445972 creation_time=24-SEP-02 RMAN-08014: channel t1: specifying archivelog(s) in backup set RMAN-08504: input archivelog thread=1 sequence=3039 recid=1 stamp=473445815 RMAN-08013: channel t1: piece 1 created RMAN-08503: piece handle=45e3gdik_1_1 comment=API Version 2.0,MMS Version 3.2.0. 0 RMAN-08525: backup set complete, elapsed time: 00:01:36 RMAN-08031: released channel: t1 No sign of current log being archived, and it didn't show up in the archivelog directory. So, I'd say this is a 9i new feature. Interesting. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Egads, I made a stupid misfire of the brain. The current log IS switched during an archive log backup (i.e. BACKUP ARCHIVELOG ALL). Sorry for the mistypo... RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 2:35 PM To: '[EMAIL PROTECTED]' What version of RMAN are you talking about. In the 9iR2 documentation for RMAN is clearly states that during backups using the PLUS ARCHIVELOG command, RMAN does force a log switch. When you do a backup using the PLUS ARCHIVELOG command RMAN will: Run ALTER SYSTEM ARCHIVE LOG CURRENT Runs the BACKUP ARCHIVELOG ALL command. Backs up the files specified in the BACKUP command. Run ALTER SYSTEM ARCHIVE LOG CURRENT again. Back up any remaining archived redo logs. A normal backup of archived redo logs such as BACKUP ARCHIVELOG ALL will not result in a log switch. Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! The avalanche has begun, It is too late for the pebbles to vote. -Original Message- Sent: Tuesday, September 24, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dennis, I don't think that Rman automatically performs a SYSTEM ARCHIVE. At least. I've not seen it. In my archive Rman database backups, I issue an 'alter system archive log current' before I backup the archive files to make sure that I have the latest archive I can possible get. Unless I'm convinced that Rman is doing this for me, I have no reason to change my procedures. You could certainly include this command both within an Rman script or elsewhere to make sure that you get the same benefit. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Ruth - At this point we just RMAN to disk. Someone on this list provided that advice as start simple. I was using RMAN to store the archive logs, but that just used up twice the disk space and I could discern no benefit so I stopped it (always open to new reasons). On recovery, RMAN uses the original archive logs just fine. I just want to make sure we have the right archive logs on tape, and this morning I posted an update to my original question, namely that I found where RMAN automatically issues an ALTER SYSTEM ARCHIVE LOG CURRENT. I'm a little confused about the timestamp on the archive logs, but I guess I just have to take it on faith that Oracle and RMAN are doing the right thing. Thanks Ruth. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 24, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Rather than copy the archive logs to tape, do an archivelog backup using rman. Archive the current log when you start. Then you can put all the backupsets, database and
Perplexed
Here is one I can't figure out. I just refreshed to schema in a test database with data from production. Different exports where used about 24 hours apart. I used the exact same type of tablespaces for each 128K LMT's. After the imports I compare # of bytes and extents from dba_segments for each schema and they are just about right on, however schema A is using 7.9GB and schema B required 9.5GB. Where am I losing almost 2 GB? I did allow the import to create the objects in schema A and I used the indexfile in schema B. Indexes are contained in same tablespace as data and all tables have primary keys. Do I have duplicate storage for primary key indexes in one of the schemas? I can't figure it out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Perplexed
Ignore, I found the errors of my ways. Ethan Post perotdba (AIM), epost1 (Yahoo) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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 the effect of modifying PCTFREE/PCTUSED immediate ?
I replied too soon earlier, I think. Yes, what you state is correct. Jraed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to reproduce a hanging connect attempt
Title: RE: How to reproduce a hanging connect attempt
Jared,
It is adequate.
Below is snip from Steve Adams's script (db_check.sh) and I successfully used similar technique for some time.
-- snip
rm -f $READY
print
connect nobody/really
host touch $READY
exit |
sqlplus /nolog $SPOOL
# wait for up to 59 seconds
#
((timeout = 60))
while ((timeout -= 1)) [[ ! -r $READY ]]
do
sleep 1
done
# check for hang
#
[[ -r $READY ]] ||
{
kill $!
msg=$PROGRAM: Oracle instance $ORACLE_SID is not responding
$DEBUG logger -p oracle.err $msg
STATUS=1
$INTERACTIVE $msg
continue
}
-- snip
Alex.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 24, 2002 12:23 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to reproduce a hanging connect attempt
Thanks for the info Ian.
I've been asked to prove why sqlplus and ksh are not
adequate for checking connectity. The third possibility,
a hang, is exactly that reason.
I'm trying to duplicate what can actually happen to cause
a hanging connection. I've been burned by that in the
past when my script didn't properly allow for hangs.
Jared
MacGregor, Ian A. [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/24/2002 11:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject: RE: How to reproduce a hanging connect attempt
Have you fooled with the CONNECT_TIMEOUT_LISTENER parameter of
listener.ora? Setting it to 0 won't guarantee a connection will hang, but
will tell a process to wait forever to connect. Hanging connections were
a problem for us with the earlier Oracle 6 releases. My solution was less
elegant. It used one program which attempted to connect, wrote a
timestamp, and signaled if the connection failed ; another checked the
timestamp against the current time and signaled if yhe difference was too
great I cannot recall seeing the hanging problem for years, but we still
run the program to check for it.
I've been stating that three things can happen on an Oracle connection
attempt for years: it can be successful, it can fail, or it can hang and
return nothing. Yet, 100% of the scripts I see which attempt to connect
to the database to ensure it is functional do not consider the third
possibility.
Seems with your upcoming article that percentage will drop to 99..
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, September 23, 2002 10:03 PM
To: Multiple recipients of list ORACLE-L
Dear List,
As an example for an article I'm working on, I'm showing how
a hanging connect can be timed out in a Perl script via the
alarm() call.
By 'hanging connect' I mean a connection attempt that never
connects and never returns an error code.
I have one right now on my Linux box. I started a database, did
kill -9 on the oracle processes, and now attempts to login
to the database hang. It's been that way for 24 hours now.
e.g. sqlplus scott/tiger@ts98
.. never returns an error code, never connects.
Guess it isn't going to connect. This could be a problem in a
ksh script written to check connectivity. ( which is why I
use Perl )
The question is, why? What is a consistent way to reproduce
this error? The method I used isn't consistent.
This is something that I see happen from time to time on Oracle
databases, both NT and Unix platforms, hence the reason for
the timeout on the connect.
Any thoughts on how to consistently reproduce this, on either platform?
Thanks,
Jared
--
RE: DBA work load
Correct. -Original Message- Sent: Wednesday, 25 September 2002 12:08 AM To: Multiple recipients of list ORACLE-L I assume it is when penalty payments come into play when SLA targets are not met. Therefore payments for provision and support of an Oracle database are rebated John -Original Message- Sent: 24 September 2002 14:03 To: Multiple recipients of list ORACLE-L What is a rebatable SLA? -Original Message- Sent: Tuesday, September 24, 2002 1:38 AM To: Multiple recipients of list ORACLE-L I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... .there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... .I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7 nor is instability in those environments... But the point is that the job of database administrator is like any other critical support role. Only the medical profession is so criminally idiotic as to expect and demand 30- and 40-hour shifts from its most valuable personnel... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 7:43 PM I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: ORA-03113 with dbms_output and sysdate
I will try that. The confusing part is I can run : select TO_CHAR(SYSDATE, 'DY, MON DD HH24:MI:SSYYY') into datevar from dual; dbms_output.put_line( datevar); and it works fine. I can run all my other sql scripts without error. I'd be surprised if it was a network error, but I'm willing to look at anything. steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 24, 2002 1:23 PM It is neither dbms_output nor sysdate. Try a loopback. This error is related to the network and I am sure that there is a lot on MetaLink. inka -Original Message- Sent: Tuesday, September 24, 2002 2:05 PM To: Multiple recipients of list ORACLE-L Steve works fine for me on 8171 on WinNT Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production With the Partitioning option JServer Release 8.1.7.1.1 - Production SQL set serveroutput on SQL DECLARE 2 3BEGIN 4 5dbms_output.put_line(TO_CHAR(SYSDATE, 'DY, MON DD HH24:MI:SSYYY') ); 6 7END; 8 9 / TUE, SEP 24 12:59:21002 PL/SQL procedure successfully completed. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 24, 2002 1:48 PM To: Multiple recipients of list ORACLE-L Has anyone seen this cause an error before. I can run this script on other datbases (same version) without any problems. This database was rebuilt from production. I can select the to_char... into a varchar2 variable and then print the variable and it works. I've bounced the database also, but still no help. Nothing shows up in the alert log and there are no trace files. I have the work around, but I want to find out why/how to fix it and the root cause. Can I set an event and get more information about it when the error happens? That's the direction I'm headed. Thanks, Steve oracle 8.1.7.3 on Win NT 4.0 svc pack 6 sys(38)@INS declare 2 3 begin 4 5 dbms_output.put_line(to_char(sysdate, 'DY, MON DD HH24:MI:SS YYY') ); 6 7 end; 8 / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: : end-of-file on communication channel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Inka Bezdziecka 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.com -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BUFFER OVERFLOW, LIMIT OF 2000 BYTES.[Scanned]
Try, set serveroutput on size 100 I faced this problem sometime back and it workedwhen I set the size to 100. -Original Message-From: Meomeo Nguyen [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 24, 2002 10:23 PMTo: Multiple recipients of list ORACLE-LSubject: BUFFER OVERFLOW, LIMIT OF 2000 BYTES.[Scanned] Hi All, Below is a script to retrieve data from BFILE column and its output.The external PDF file is around 53, 435 bytes (text and picture altogether in one file). Anyone please have a fix for this script. I am unable to view the content of the external PDF file on the sreen. Any help is greatly appreciated. Thanks alot Lenka set serveroutput onDECLARE v_book_file BFILE; v_length NUMBER; v_position NUMBER; v_piece RAW (56,320);BEGIN SELECT book_file INTO v_book_file FROM my_book_text WHERE file_desc = 'testing'; dbms_lob.open (v_book_file, ); v_length := dbms_lob.getlength (v_book_file); v_position := 1; LOOP EXIT WHEN v_position v_length; v_piece := dbms_lob.substr (v_book_file, 100, v_position); dbms_output.put_line (utl_raw.cast_to_varchar2(v_piece)); v_position := v_position + 100; END LOOP; dbms_lob.close (v_book_file);END;/== %PDF-1.3%bcOS1 0 obj /Creatorfeff001b7a68001b004d006900630072006f0073006f0066007400200057006f007200640020/CreationDate (D:19991019160202)/Titlefeff001b7a68001b00360031003100370070006200630031002e005000440046/Authorfeff001b7a68001b0052006f006200650072007400630075/Producer (AcrobatPDFWriter 4.0 for Windows)/ModDate (D:20001019200402+08'00') endobj2 0obj[ /PDF /Text /ImageB ]endobj3 0 obj /Pages 5 0 R /Type /Catalog /DefaultGray 31 0 R /DefaultRGB 32 0 R endobj4 0 obj /Type /Page /Parent 5 0 R /Resources /Font /F1 8 0 R/F2 10 0 R /F0 6 0R /F3 14 0 R /F4 16 0 R /ProcSet [ /PDF /Text /ImageB ] /Contents 57 0 R endobj5 0 obj /Kids [ 4 0 R 18 0 R ] /Count 2 /Type /Pages /MediaBox [ 0 0 612 792 ] endobj6 0 obj /Type /Font /Subtype /TrueType /Name /F0 /BaseFont /Arial /FirstChar 31 /LastChar 255 /Widths [ 750 278 278 355 556 556 889 667 191 333 333 389 584 278 333 278 278 556556 556 556 556 556 556 556 556 556 278 278 584 584 584 556 1015 667 667 722 722 667 611 778 722 278500 667 556 833 722778 667 778 722 667 611 722 667 944 667 667 611 278 278 278 469 556 333 556556 500 556 556 278 556 556 222 222 500 222 833 556 556 556 556 333 500 278 556 500 722 500 500 500334 260 334 584 750 556 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500 500500 500 500 500 500DECLARE*ERROR at line 1:ORA-2: ORU-10027: buffer overflow, limit of 2000 bytesORA-06512: at "SYS.DBMS_OUTPUT", line 91ORA-06512: at "SYS.DBMS_OUTPUT", line 58ORA-065! ! 12: at line 17 Do you Yahoo!?New DSL Internet Access from SBC Yahoo!
