RE: High values
John, Look at the DECODE statement. You can use it in the ORDER BY clause. I would *never* insert a garbage value into a database column as you are talking about. How do you determine if it is truely garbage? You know of course, if you selected a high values value today, that next month that value would be valid, so you would end up updating the column to something else. An order by using DECODE could look like: ORDER BY DECODE(number_column,null,999,number_column) or ORDER BY DECODE(char_column,null,'',char_column) hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 5:18 AM To: Multiple recipients of list ORACLE-L Basically what I want to do is put a value in a VARCHAR2 column that will ensure it appears as the last row when selected using an ORDER BY on the column John -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: 19 September 2002 21:09 To: Multiple recipients of list ORACLE-L Subject: RE: High values John - Since nobody has replied to your question, I discussed it with a fellow COBOL programmer. I think that in COBOL you normally would use this in an iterative loop, as a comparison. In SQL itself, you rarely iterate, so you probably don't have that much need for a HIGH-VALUE. In PL/SQL you might be more likely to need it. Myself, I have used such a thing in the C language before, and there was usually a precompiler value that you could include. For Oracle, the maximum integer that can be represented is 38 9's times 10 to the 125th. power. Here is a web address that lists a lot of the Oracle limits as of 8.0.5. http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58242/ch5.htm Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 19, 2002 12:21 PM To: Multiple recipients of list ORACLE-L Is there a Oracle equivalent to the Cobol HIGH-VALUES value? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lost ofall redo logs
Tom, that statement will open a can of worms(like a religion following) about if/when to backup redo logs. :) joe Mercadante, Thomas F wrote: I agree with you Joe. I would *never* use an undocumented parameter unless Oracle told me to (or unless I was testing something that I could recreate). Using these params on the advice of someone from this list is very dangerous. The larger question is, why are the redo logs not being backed up? Arun, you need to establish a backup policy *immediately* so that you are not being put into this position again. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 7:48 AM To: Multiple recipients of list ORACLE-L KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 4:30 PM Hi people... What to do if I have LOST ALL REDO LOGS and i don4t have any backup? Thanks in advance! : Gilberto Gampert Universidade de Passo Fundo Administrador de Banco de Dados Passo Fundo - RS - Bra5il [EMAIL PROTECTED]http://www.upf.br : -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilberto Gampert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
Re: Consulting Position Available-Oracle DBA/Developer/NYC
Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best method to move Filesystems to RAW Devices.
the first question is WHY go to raw devices?, OPS/RAC? other than those 2 reasons(and i'm not sure RAC requires it like OPS did), there is very little performance gain with the advances in filesystem types. just curious. joe Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lost ofall redo logs
Well, it didn't sound like he was using Rman (which does not backup redo logs). So I assumed that he was/should be using a cold-backup strategy - which, if it were me, would backup the redo logs. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L Tom, that statement will open a can of worms(like a religion following) about if/when to backup redo logs. :) joe Mercadante, Thomas F wrote: I agree with you Joe. I would *never* use an undocumented parameter unless Oracle told me to (or unless I was testing something that I could recreate). Using these params on the advice of someone from this list is very dangerous. The larger question is, why are the redo logs not being backed up? Arun, you need to establish a backup policy *immediately* so that you are not being put into this position again. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 7:48 AM To: Multiple recipients of list ORACLE-L KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 4:30 PM Hi people... What to do if I have LOST ALL REDO LOGS and i don4t have any backup? Thanks in advance! : Gilberto Gampert Universidade de Passo Fundo Administrador de Banco de Dados Passo Fundo - RS - Bra5il [EMAIL PROTECTED]http://www.upf.br : -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilberto Gampert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: count(*)
What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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 - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: Consulting Position Available-Oracle DBA/Developer/NYC
Hallo, What is dual? ;o) I don't think there is such a beast in SQL Server Dave -Original Message- Sent: Friday, September 20, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my $OR
can anyone tell me why this happened? Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Backup Strategy - Informal Survey
I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my
can you better explain what the question is? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 9:48 AM To: Multiple recipients of list ORACLE-L $OR can anyone tell me why this happened? Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Best method to move Filesystems to RAW Devices.
Dennis, Thanks for the reply. We have not done any benchmarks to find out if there is any performance gain. The main reason for moving to raw devices is to convert our existing database to function on OPS environment and then eventually to 9i RAC. After we move on to raw (without OPS), may be I will post to the list what is the performance increase/decrease, problems encountered.. etc..etc.. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:23 PM Nat - I'm assuming you can connect raw devices to your existing system. Myself, I would create new tablespaces and datafiles on the raw devices. Preferably you will use LMT with uniform extents. Then I would use CREATE TABLE AS SELECT NOLOGGING to move the data. Personally I prefer to first rename the original table to something like table1_sav and then create table1 as select * from table1. Eventually when you've checked everything out (taken a backup, backed up the control files) you will drop the table1_sav. But your applications can immediately use the new table with no changes. Then you'll have to recreate indexes, but I don't know any way around that. What performance increase is your benchmarks telling you that you will experience? Oracle had an interesting white paper on their site. In effect it discussed the question why do all benchmarks show raw much faster than cooked, but nobody sees that sort of performance in production?. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 19, 2002 1:39 PM To: Multiple recipients of list ORACLE-L We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
Now I know. I assume that it's enough like SQLPlus that it seems familiar and different enough that you can really foul yourself up. Farnsworth, DaveTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DFarnsworth cc: @AshleyfurnitSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC ure.com Sent by: root 09/20/2002 08:53 AM Please respond to ORACLE-L T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
Re: Best method to move Filesystems to RAW Devices.
Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:28 PM Hi Nat, As long as you are not changing hardware platforms, you can use dd. (Of course, I assume you're on some flavor of unix, since you didn't mention OS.) You can just do 'dd if=/path/to/filsystem/datafile of=/path/to/raw/volume'. Of course, your database must be down. -Mark On Thu, 2002-09-19 at 14:38, Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
Jan Pruner wrote: Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP SQL Server does not have a dual table, pos... -- Give a man a fish and he eats for a day, show a man the net and he'll be preoccupied for month's. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: rob INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: count(*)
Tom, The sequences are transaction independant, so the trick with 'select sequence_name,last_number from user_sequences' will only work if there are no rollbacks after insert as well as no use of cache in sequence as you mentioned. Moreover, what if records get deleted? I guess this may be solved using another trigger on delete selecting another sequence. Than count(*) may be determined as a difference between two sequences. Again there should be no rollbacks. Rishi, I would also join the point of redesigning of the logic without use of count(*). Regards, Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 2:53 PM Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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 - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the
Re: listener problem ???
Do you have a service like Oracle_HOMETNSListener? I assume that you must. You don't need a separate listener for each instance. What does the TNSNames.ora entry for LMANAGER look like? Leslie Lu leslie_y_lu To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @yahoo.com cc: Sent by: rootSubject: listener problem ??? 09/19/2002 03:18 PM Please respond to ORACLE-L Hi all, I have a database LMANAGER (817 on win2000), it's on my local machine. I can log into it using svrmgrl. But when I connect using sqlplus, I got ORA-12541: TNS:no listener. LMANAGER is already in listener.ora file, and lsnrctl status shows LMANAGER is there: (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))) STATUS of the LISTENER Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produ tion Start Date19-SEP-2002 12:01:44 Uptime0 days 0 hr. 5 min. 1 sec Trace Level off Security OFF SNMP OFF Listener Parameter File C:\oracle\oracle81\network\admin\listener.ora Listener Log File C:\oracle\oracle81\network\log\listener.log Services Summary... LMANAGER has 1 service handler(s) prod has 1 service handler(s) The command completed successfully However, I don't see a service in NT services like Oracle_HOMETNSListenerLMANAGER. How do I create it? Thank you in advance! Leslie __ 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: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
Jan, There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 9:43 AM Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Job postings was:Re: Lost ofall redo logsOTHER...
JT., Do you have any awareness of www.ORAFAQ.com Oracle-job.net? Thanx, Bill --- Joe Testa [EMAIL PROTECTED] wrote: Bill I'm kinda surprised no one has replied. I'm seeing pockets of oracle work in cols most of it is short-term(ie: 3 months). This is the best place to find what I'd consider the brightest people in the oracle world(of course the brightest are also the most expensive) :) joe Bill Christison wrote: Joe T., When we'd recently posted positions to FATCITY you'd replyed;..the economy must be getting better (based on the job(s)posting. Yes,I agree.However,I'd never tried posting to Fcity.I've been placing SAS/Statisticians since 1993 and use SAS-L. *FYI., I didn't obtain any response,other than yours.If you've any thoughts(why)or could suggest another location to post/to make ORACLE people aware of career alternatives,I'd appreciate your insights, Bill Christison --- Joe Testa [EMAIL PROTECTED] wrote: KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] === message truncated === = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ 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: Bill Christison INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-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:Backup Strategy - Informal Survey
Robert, I don't do exports of our large production systems mainly due to the fact that I'd have to export them to tape and getting that much quite time, so as not to bump in to an ORA-01555 or have someone else hit a rollback segment issue, is impossible. Consequently two hot backups a week are the norm around here we guard out archived redo logs very well. Now I do take exports of selected tables before they get modified or mass changed so that we have a point in time to go back to if all hell breaks loose. Dick Goulet Reply Separator Author: Freeman; Robert [EMAIL PROTECTED] Date: 9/20/2002 6:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
there is no such beast as DUAL in MS SQLServer. if what you want is the result of a function just leave off the from clause like so: select getdate() -Original Message- Sent: Friday, September 20, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my
When I upgraded and migrated from 8.1.5 to 8.1.7 the Universal Installer/Migration Assistant moved my $ORACLE_BASE from /disk03/app/oracle to /disk03/app/oracle/product/8.1.5 (which is my $ORACLE_HOME) without prompting me for the move. SD -Original Message- Sent: Friday, September 20, 2002 10:19 AM To: Multiple recipients of list ORACLE-L my can you better explain what the question is? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 9:48 AM To: Multiple recipients of list ORACLE-L $OR can anyone tell me why this happened? Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Query Tool That Reads LOBs, CLOBs, etc.
We have a development project that is using third party software. The tables include several clob datatypes. The project needs to identify a tool that allows a knowledgeable user can use to browse data in the CLOBs. Does anyone have any suggestions of tools to do this? Thanks, = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best method to move Filesystems to RAW Devices.
Joe, We are planning to move to RAC. Per Oracle RAW devices is must for RAC. As you said, Yes there is little performance gain just moving to RAC and more work to DBA's/SA's. But this seems to be the main requirement for RAC. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 8:53 AM the first question is WHY go to raw devices?, OPS/RAC? other than those 2 reasons(and i'm not sure RAC requires it like OPS did), there is very little performance gain with the advances in filesystem types. just curious. joe Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Database Performance Tuning and Optimization: With Examples f
If sales from my Mastering Oracle8i book are any indication, the market for 8i specific books is long gone. :-( I'm thinking that the 9i specific book market will probably start to slow down (if it hasn't already) pretty soon. 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: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L from I would kinda wonder(with the exception that most people are probably still on 8i) that an 8i book would be publisghed this late in the game into the 9i world. joe Grabowy, Chris wrote: Anyone know anything about this book that just showed up on Amazon's website?? Database Performance Tuning and Optimization: With Examples from Oracle 8I by Sitansu S. Mittra Publication date: October 2002 Publisher: Springer Verlag Pub (Computer Bks) Binding:Hardcover Subjects: Database management; Oracle (Computer file); Relational Databases http://www.amazon.com/exec/obidos/ASIN/0387953930/ref%3Ds%5Fe9/002-3914453- 4659241 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Query Tool That Reads LOBs, CLOBs, etc.
Title: RE: Query Tool That Reads LOBs, CLOBs, etc. I think latest version of TORA and TOAD can read it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Peter Barnett [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Subject: Query Tool That Reads LOBs, CLOBs, etc. We have a development project that is using third party software. The tables include several clob datatypes. The project needs to identify a tool that allows a knowledgeable user can use to browse data in the CLOBs. Does anyone have any suggestions of tools to do this? Thanks, = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Consulting Position Available-Oracle DBA/Developer/NYC
Tom - actually, the SQLPlus like thingie in sybase/m$ land is called isql (interactive sql). T SQL is the SQL dialect understood by those RDBMSs. so you type yout T SQL queries into isql. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Now I know. I assume that it's enough like SQLPlus that it seems familiar and different enough that you can really foul yourself up. Farnsworth, DaveTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DFarnsworth cc: @AshleyfurnitSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC ure.com Sent by: root 09/20/2002 08:53 AM Please respond to ORACLE-L T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing
Re: Backup Strategy - Informal Survey
I do nightly exports of my large databases with ROWS=N. This way I can restore users, grants, indexes, table definitions, etc. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 09/20/02 10:13AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. **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: Consulting Position Available-Oracle DBA/Developer/NYC
So Oracle statement SELECT TO_CHAR(33) FROM DUAL is in TSQL only SELECT CHAR(33); ? I was searching docs, but didn't find it. I know on Sybase SQL Anywhere is it DUMMY, but it doesn't work on MSSQL (and DUMMY is on MS SQL reserved word!!) JP On Friday 20 September 2002 16:18, you wrote: Jan Pruner wrote: Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP SQL Server does not have a dual table, pos... -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Backup Strategy - Informal Survey
Our db size is around 350Gig. We have stopped full logical exports since our database size has grown above 200 Gigs. It is just not feasible for us to do the full exports anymore, time it takes to export is too much. We use EMC/EDM bcv splits to do a hot backup every night. We shutdown our database once a week for half an hour for cold bcv splits. So far it has worked very well. So we do not feel logical exports an important part of our backup/recovery strategy. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 10:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
Thanks. I tried it and it works, but I was not sure if it is right or not. JP On Friday 20 September 2002 16:18, you wrote: Jan, There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 9:43 AM Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best method to move Filesystems to RAW Devices.
Nat, totally understand. joe Nat wrote: Joe, We are planning to move to RAC. Per Oracle RAW devices is must for RAC. As you said, Yes there is little performance gain just moving to RAC and more work to DBA's/SA's. But this seems to be the main requirement for RAC. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 8:53 AM the first question is WHY go to raw devices?, OPS/RAC? other than those 2 reasons(and i'm not sure RAC requires it like OPS did), there is very little performance gain with the advances in filesystem types. just curious. joe Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my
Title: RE: migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my i had the same issue where all my data files were moved to the same slice which fortunately i had a large slice. i ended up doing the manual upgrade steps through the Migration manual. -Original Message- From: DiFelice, Sebastian [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Subject: RE: migrated from 8.1.5 to 8.1.7 and Migration Assistant moved my When I upgraded and migrated from 8.1.5 to 8.1.7 the Universal Installer/Migration Assistant moved my $ORACLE_BASE from /disk03/app/oracle to /disk03/app/oracle/product/8.1.5 (which is my $ORACLE_HOME) without prompting me for the move. SD -Original Message- Sent: Friday, September 20, 2002 10:19 AM To: Multiple recipients of list ORACLE-L my can you better explain what the question is? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 9:48 AM To: Multiple recipients of list ORACLE-L $OR can anyone tell me why this happened? Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Database Performance Tuning and Optimization: With Examples f
Robert - Maybe this argues for the book being really, really good, if a publisher decided to release it at this stage. If anyone has a chance to browse it, I would be interested. Say Robert, as a noted author maybe you could get your publisher to request a complimentary professional review copy?? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Examples f If sales from my Mastering Oracle8i book are any indication, the market for 8i specific books is long gone. :-( I'm thinking that the 9i specific book market will probably start to slow down (if it hasn't already) pretty soon. 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: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L from I would kinda wonder(with the exception that most people are probably still on 8i) that an 8i book would be publisghed this late in the game into the 9i world. joe Grabowy, Chris wrote: Anyone know anything about this book that just showed up on Amazon's website?? Database Performance Tuning and Optimization: With Examples from Oracle 8I by Sitansu S. Mittra Publication date: October 2002 Publisher: Springer Verlag Pub (Computer Bks) Binding:Hardcover Subjects: Database management; Oracle (Computer file); Relational Databases http://www.amazon.com/exec/obidos/ASIN/0387953930/ref%3Ds%5Fe9/002-3914453- 4659241 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:Backup Strategy - Informal Survey
Robert - My info: Oracle 8.1.7 E.E. Largest DB is 900gb Hot Backups 2x week Cold Backups 1x month Export Backups 1x week on selective schemas I do export backups for creation of test db's, recover data validation blunders, i.e. single object restores. Future plans are RMAN, RMAN, RMAN on 9i. I need incremental backups fast :) Gene [EMAIL PROTECTED] 09/20/02 10:53AM Robert, I don't do exports of our large production systems mainly due to the fact that I'd have to export them to tape and getting that much quite time, so as not to bump in to an ORA-01555 or have someone else hit a rollback segment issue, is impossible. Consequently two hot backups a week are the norm around here we guard out archived redo logs very well. Now I do take exports of selected tables before they get modified or mass changed so that we have a point in time to go back to if all hell breaks loose. Dick Goulet Reply Separator Author: Freeman; Robert [EMAIL PROTECTED] Date: 9/20/2002 6:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- 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 send the HELP command for other information (like subscribing).
RE: Lost ofall redo logs
I'm not an expert so pardon me if i'm wrong. Why to copy the log files in case of a cold backup if the instance was shutdown cleanly(NOT ABORT)?? The d/b will be consistent so we don't need the redo-log files, isn't it? Isn't it that the DB update all the Datafiles headers with the checkpoint information at the time of CLEAN SHUTDOWN so redo-logs are not required for a restore from cold backup? Though no harm in taking the log files backup too but they are not really needed? Regards, Naveen -Original Message- Sent: Friday, September 20, 2002 7:18 PM To: Multiple recipients of list ORACLE-L Well, it didn't sound like he was using Rman (which does not backup redo logs). So I assumed that he was/should be using a cold-backup strategy - which, if it were me, would backup the redo logs. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L Tom, that statement will open a can of worms(like a religion following) about if/when to backup redo logs. :) joe Mercadante, Thomas F wrote: I agree with you Joe. I would *never* use an undocumented parameter unless Oracle told me to (or unless I was testing something that I could recreate). Using these params on the advice of someone from this list is very dangerous. The larger question is, why are the redo logs not being backed up? Arun, you need to establish a backup policy *immediately* so that you are not being put into this position again. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 7:48 AM To: Multiple recipients of list ORACLE-L KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 4:30 PM Hi people... What to do if I have LOST ALL REDO LOGS and i don4t have any backup? Thanks in advance! : Gilberto Gampert Universidade de Passo Fundo Administrador de Banco de Dados Passo Fundo - RS - Bra5il [EMAIL PROTECTED]
DUAL Was: Consulting Position Available-Oracle DBA/Developer/NYC
As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. 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: count(*)
There is a difference between 10 and 120 minutes, especially on Friday. -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 3:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: count(*) If you can live with 10 minutes, why not 60 minutes or 120 minutes ? Examine why this app feels so insecure that it needs to know the number of rows every 10 minutes. On Thursday 19 September 2002 20:28, you wrote: Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Backup Strategy - Informal Survey
An export with data will probably cause more headaches than it solves in large DBs. A logical export without data will also have limited use. It is quick to do and small but I suggest may be invaluable for various tasks. Mike Jenner Database Administrator -Original Message- Sent: 20 September 2002 15:53 To: Multiple recipients of list ORACLE-L Robert, I don't do exports of our large production systems mainly due to the fact that I'd have to export them to tape and getting that much quite time, so as not to bump in to an ORA-01555 or have someone else hit a rollback segment issue, is impossible. Consequently two hot backups a week are the norm around here we guard out archived redo logs very well. Now I do take exports of selected tables before they get modified or mass changed so that we have a point in time to go back to if all hell breaks loose. Dick Goulet Reply Separator Author: Freeman; Robert [EMAIL PROTECTED] Date: 9/20/2002 6:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Stupid Bind Variable question
In any given SQL statement, do I need (or should I :^) ) create a bind variable for every literal value even if it is one of the following cases: 1) substring in a substr function 2) date/time format mask used in to_char or to_date function 3) comparison value in a decode/case statement (not in the where clause) I've seen it mentioned to use bind variables when comparing a column to a literal in the where clause, but nothing specific about the above scenarios. Basically what I am asking is, do all literals need to be made bind variables? Thanks. -- Alan Davey [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
It is a reserve word but has no purpose that I can find. I'll dig deeper -Original Message- Sent: Friday, September 20, 2002 10:34 AM To: Multiple recipients of list ORACLE-L ARE YOU AN IDIOT? But MS SQL has DUMMY. I don't know why {if they don't have DUAL). Maybe it's a future feature in case MS will buy Larry's child :-)) JP On Friday 20 September 2002 16:08, you wrote: Hallo, What is dual? ;o) I don't think there is such a beast in SQL Server Dave -Original Message- Sent: Friday, September 20, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP On Friday 20 September 2002 14:53, you wrote: T is for trasnsactional SQL which is SQL Server and Sybase terminology. -Original Message- Sent: Thursday, September 19, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Datawarehouse meta data. But what the heck is T SQL? Miller, Jay JayMiller To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @TDWaterhousecc: .comSubject: RE: Consulting Position Available-Oracle DBA/Developer/NYC Sent by: root 09/19/2002 02:08 PM Please respond to ORACLE-L But 4th normal form? Does anyone really use this? -Original Message- Sent: Tuesday, September 17, 2002 3:35 PM To: Multiple recipients of list ORACLE-L well the market must be picking up some, been definitely more reqs posted here a/o recent. joe Bill Christison wrote: Interested candidates reply to: [EMAIL PROTECTED] * Consulting Position Title: Oracle DBA/Developer Duties and Responsibilities: This position is a conversion to an Oracle Data warehouse. Excellent verbal skills are needed to obtain technical specification from the architect and users then to transform into written specifications. Experience Required: *Must have experience in PL SQL and T SQL *Must have 3rd and 4th form data normalization *Must have done business systems analysis Consulting Assignment Duration: 6 months to 12 months Assignment Location: New York City = Bill Christison Knowlton Group, LLC 845-258-5129 www.knowltongroup.com __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
I think DUMMY is MS reserved word :-) And yes, SELECT CHAR(33) -- no semicolon will work. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 11:18 AM So Oracle statement SELECT TO_CHAR(33) FROM DUAL is in TSQL only SELECT CHAR(33); ? I was searching docs, but didn't find it. I know on Sybase SQL Anywhere is it DUMMY, but it doesn't work on MSSQL (and DUMMY is on MS SQL reserved word!!) JP On Friday 20 September 2002 16:18, you wrote: Jan Pruner wrote: Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP SQL Server does not have a dual table, pos... -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to load text file into database table
I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best method to move Filesystems to RAW Devices.
Nat- You may want to check out a product from Veritas.. *Supposedly* their clustered file system product is *approved* with 9i RAC on a SUN platform.. This is strictly from the rumor mill and I have no solid documentation to confirm (CYA). I am actually in the beginning phases of a project to build out a pair of SUN 6800's for a RAC environment. And we want to explore all possibilities for configuration options... greg -Original Message- Sent: Friday, September 20, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Joe, We are planning to move to RAC. Per Oracle RAW devices is must for RAC. As you said, Yes there is little performance gain just moving to RAC and more work to DBA's/SA's. But this seems to be the main requirement for RAC. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 8:53 AM the first question is WHY go to raw devices?, OPS/RAC? other than those 2 reasons(and i'm not sure RAC requires it like OPS did), there is very little performance gain with the advances in filesystem types. just curious. joe Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Loughmiller, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: count(*)
Title: RE: count(*) That is the best advice yet. Duh for the rest of us. -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 10:53 AM To: Multiple recipients of list ORACLE-L Subject: RE: count(*) this is what Oracle invented SEQUENCES for! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 10:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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 - To REMOVE yourself from this mailing list, send an E-Mail message
RE: Best method to move Filesystems to RAW Devices.
Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:28 PM Hi Nat, As long as you are not changing hardware platforms, you can use dd. (Of course, I assume you're on some flavor of unix, since you didn't mention OS.) You can just do 'dd if=/path/to/filsystem/datafile of=/path/to/raw/volume'. Of course, your database must be down. -Mark On Thu, 2002-09-19 at 14:38, Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nat
RE: How to load text file into database table
David - If the file is in just the format you show, my first impulse would be to use the perl script language to get it into a form that sqlldr could easily read. Perl is available on all platforms, including Windows. If you get ambitious, Perl can even directly insert the values into Oracle. Jared Still, the manager of this list has just published a book on that aspect. http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0W 9isbn=0596002106 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: DUAL Was: Consulting Position Available-Oracle DBA/Developer/
Ditto for PostgreSQL. For portability you can create a dual table but you have to ensure that it only has one row. ;-) I finding that portability between PostgreSQL and Oracle is easier than most... same to_date, to_char functions, and stuff. Seems like that's part of Postgres' strategy. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L DBA/Developer/NYC As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy - Informal Survey
Robert I don't think a full export has a place since your largest tables are probably too large to reimport in a reasonable amount of time. I like Jay's suggestion though. But your original question wasn't about a full export, you just said export. My answer is a definite yes. Following Pareto's rule (80/20), of the hundreds of tables, most will be relatively small, where import would be feasible. I find that developers often need to tinker with small codes tables, for example. Since they don't want to write a screen for an infrequent task, there is a higher-than-average chance they will bollix the table. Being able to quickly produce a week-old copy of the table can be a real butt-saver. And a heck of a lot easier to do from an export than a TSPITR. I also try to impress on the developers that before they monkey with a table to ask me to do a special export, and that gives me the chance to innocently ask what they are up to. Previously I did exports of large tables because that checked each block, but with RMAN, that is not necessary. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:13 AM To: Multiple recipients of list ORACLE-L I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Consulting Position Available-Oracle DBA/Developer/NYC
I think Dummy is a MS synonym April Wells Oracle DBA Keep yourself well oiled with life, laughter, new ideas and action. Otherwise you will rust out. _Anonymous -Original Message- Sent: Friday, September 20, 2002 11:13 AM To: Multiple recipients of list ORACLE-L I think DUMMY is MS reserved word :-) And yes, SELECT CHAR(33) -- no semicolon will work. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 11:18 AM So Oracle statement SELECT TO_CHAR(33) FROM DUAL is in TSQL only SELECT CHAR(33); ? I was searching docs, but didn't find it. I know on Sybase SQL Anywhere is it DUMMY, but it doesn't work on MSSQL (and DUMMY is on MS SQL reserved word!!) JP On Friday 20 September 2002 16:18, you wrote: Jan Pruner wrote: Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP SQL Server does not have a dual table, pos... -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin 666 InterScan_Disclaimer.txt M0U-502 R,# R.B @26UA9VEN92!T:4@4]SVEB:6QI=EER$-D-OG!O MF%T92!37-T96US($%N;G5A;!5V5RR!!W-O8VEA=EO;B!#;VYF97)E M;F-E#0I7:5N.B!/8W1O8F5R(#(M-P@,C P,@T*5VAEF4Z($-AFEB92!2 M;WEA;4@4F5S;W)T( @3W)L86YD;RP@1DP@(%5300T*1F]R(UOF4@:6YF M;W)M871I;VX@9V\@=\@=W=W+F-S961G92YC;VT-@T*#0H-E1H92!I;F9O MFUA=EO;B!C;VYT86EN960@:6X@=AIR!E+6UA:6P@:7,@W1R:6-T;'D@ M8V]N9FED96YT:6%L(%N9!F;W(@=AE(EN=5N95D('5S92!O9B!T:4@ M861DF5SV5E(]N;'D[(ET(UA2!A;'-O()E(QE9V%L;'D@')I=FEL M96=E9!A;F0O;W(@')I8V4@V5NVET:79E+B @3F]T:6-E(ES(AEF5B M2!G:79E;B!T:%T(%N2!D:7-C;]S=7)E+!UV4@;W(@8V]P6EN9R!O M9B!T:4@:6YF;W)M871I;VX@8GD@86YY;VYE(]T:5R('1H86X@=AE(EN M=5N95D(')E8VEP:65N=!IR!PF]H:6)I=5D(%N9!M87D@8F4@:6QL M96=A;X@($EF('EO=2!H879E(')E8V5I=F5D('1H:7,@;65SV%G92!I;B!E MG)OBP@QE87-E(YO=EF2!T:4@V5N95R(EM;65D:6%T96QY()Y M(')E='5R;B!E+6UA:6PN@I#;W)P;W)A=4@4WES=5MRP@26YC+B!H87,@ M=%K96X@979EGD@F5AV]N86)L92!PF5C875T:6]N('1O(5NW5R92!T M:%T(%N2!A='1A8VAM96YT('1O('1H:7,@92UM86EL(AAR!B965N('-W M97!T(9OB!V:7)UV5S+B @5V4@86-C97!T(YO(QI86)I;ET2!F;W(@ M86YY(1A;6%G92!S=7-T86EN960@87,@82!R97-U;'0@;V8@V]F='=AF4@ M=FER=7-ER!A;F0@861V:7-E('EO=2!C87)R2!O=70@6]UB!O=VX@=FER M=7,@8VAE8VMS()E9F]R92!O5N:6YG(%N2!A='1A8VAM96YT+@T*#0H- #@T* end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: count(*)
but sequences cannot be decremented by PREVVAL too, what about DELETIONS?! -Original Message- Sent: Friday, September 20, 2002 8:23 PM To: Multiple recipients of list ORACLE-L this is what Oracle invented SEQUENCES for! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 10:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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 - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
Re: Consulting Position Available-Oracle DBA/Developer/NYC
exactly, what I meant :-) just today I had to deal with the stupid SQL Server problem: once in a while it cannot find a stored procedure on the server, that nobody touches, and then (in 5min) it finds it - no problem. yes I'm lucky to work with both Oracle and SQL Server :-) (the second was not my choice) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:48 PM I think Dummy is a MS synonym April Wells Oracle DBA Keep yourself well oiled with life, laughter, new ideas and action. Otherwise you will rust out. _Anonymous -Original Message- Sent: Friday, September 20, 2002 11:13 AM To: Multiple recipients of list ORACLE-L I think DUMMY is MS reserved word :-) And yes, SELECT CHAR(33) -- no semicolon will work. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 11:18 AM So Oracle statement SELECT TO_CHAR(33) FROM DUAL is in TSQL only SELECT CHAR(33); ? I was searching docs, but didn't find it. I know on Sybase SQL Anywhere is it DUMMY, but it doesn't work on MSSQL (and DUMMY is on MS SQL reserved word!!) JP On Friday 20 September 2002 16:18, you wrote: Jan Pruner wrote: Does anybody know what table is in MS SQL instead of Oracle DUAL? Thanks JP SQL Server does not have a dual table, pos... -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: count(*)
Naveen - This approach would probably work fine as long as only a single process was running. If multiple processes were inserting rows (likely at the scale of millions of rows/hour), this new table would probably be the bottleneck. Each process must acquire a lock on this row of this table, so the other processes must wait unnecessarily. Been there, done that. Very difficult to do what the user asks without degrading performance, which is probably why Oracle scales higher that Informix. Okay, cheap shot but worth mentioning again. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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]
RE: Lost ofall redo logs
Naveen, Why NOT take the redo log files in a cold backup? If you restore these cold backup files and intend to use them, you would then have to open the database with the RESET LOGS option to re-create the redo logs. This would then invalidate all of your archive log files. And Joe - thanks! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 11:29 AM To: Multiple recipients of list ORACLE-L I'm not an expert so pardon me if i'm wrong. Why to copy the log files in case of a cold backup if the instance was shutdown cleanly(NOT ABORT)?? The d/b will be consistent so we don't need the redo-log files, isn't it? Isn't it that the DB update all the Datafiles headers with the checkpoint information at the time of CLEAN SHUTDOWN so redo-logs are not required for a restore from cold backup? Though no harm in taking the log files backup too but they are not really needed? Regards, Naveen -Original Message- Sent: Friday, September 20, 2002 7:18 PM To: Multiple recipients of list ORACLE-L Well, it didn't sound like he was using Rman (which does not backup redo logs). So I assumed that he was/should be using a cold-backup strategy - which, if it were me, would backup the redo logs. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L Tom, that statement will open a can of worms(like a religion following) about if/when to backup redo logs. :) joe Mercadante, Thomas F wrote: I agree with you Joe. I would *never* use an undocumented parameter unless Oracle told me to (or unless I was testing something that I could recreate). Using these params on the advice of someone from this list is very dangerous. The larger question is, why are the redo logs not being backed up? Arun, you need to establish a backup policy *immediately* so that you are not being put into this position again. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 7:48 AM To: Multiple recipients of list ORACLE-L KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL PROTECTED]
Re: Backup Strategy - Informal Survey
On Fri, 20 Sep 2002, Nat wrote: We use EMC/EDM bcv splits to do a hot backup every night. We shutdown our database once a week for half an hour for cold bcv splits. So far it has worked very well. Just curious, why do you do a cold backup weekly? Do you not trust your hot backups? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Original Message - I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How can I change Oracle as well as NT Domain passwords
Hi All How can I change passwords of a user of Oracle application as well as W2K/NT Login? Any utility in 8/8i can do this form me? Thanks in Advance Shiva B -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baswannappa, Shiva INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DUAL Was: Consulting Position Available-Oracle DBA/Developer/
We are using PostgreSQL, but still have problems with statistics. Sometimes after VACUUM ANALYZE optimizer doesn't use indexes and performance goes down. JP On Friday 20 September 2002 18:43, you wrote: Ditto for PostgreSQL. For portability you can create a dual table but you have to ensure that it only has one row. ;-) I finding that portability between PostgreSQL and Oracle is easier than most... same to_date, to_char functions, and stuff. Seems like that's part of Postgres' strategy. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L DBA/Developer/NYC As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: count(*)
Naveen, Please read all the posts. That is the first question I asked. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 1:04 PM To: Multiple recipients of list ORACLE-L but sequences cannot be decremented by PREVVAL too, what about DELETIONS?! -Original Message- Sent: Friday, September 20, 2002 8:23 PM To: Multiple recipients of list ORACLE-L this is what Oracle invented SEQUENCES for! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 10:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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: 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
Re: count(*)
Do you really need it? In case of huge DELETE statement you can always recreate sequence with START WITH primary_key+1. JP On Friday 20 September 2002 19:04, you wrote: but sequences cannot be decremented by PREVVAL too, what about DELETIONS?! -Original Message- Sent: Friday, September 20, 2002 8:23 PM To: Multiple recipients of list ORACLE-L this is what Oracle invented SEQUENCES for! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 10:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Staspack Grapher/Viewer ?
Ah, that makes much more sense to me. That's more the idea I had in mind when I installed STATSPACK. That, and the ability to be able to look back in time to potentially see why our nightly jobs are still running at 8:00 AM. (insert system-stats-vs-session-stats argument here) :) Thanks, Dennis! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: Staspack Grapher/Viewer ? Sent: Thursday, September 19, 2002 2:55 PM To: '[EMAIL PROTECTED]' Rich - I think the original poster mentioned Don Burleson's book. Don suggests you take multiple STATSPACK snapshots over time, say every hour for a week, then query the STATSPACK tables directly to, say graph database transactions over time. From this you can identify the peak times your database is being used, either day of week, or time of day. Once you identify the peak usage times, you can drill in deeper to identify the problems in that area, like maybe the waits. You may perform some small-interval STATSPACK snapshots in that time area. Don presents a simple idea of doing a simple query and then cutting an pasting that into MS Excel. I've done this and you can create some pretty impressive-looking charts very quickly. The drawback is that method is pretty labor-intensive and it is easy to make an error and produce an erroneous chart. I would highly recommend it as a prototype method, or if you are a consultant that needs to quickly produce some impressive charts for your clients. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Backup Strategy - Informal Survey
No we do trust our hot backups. Our databases are mostly idle during early ours of Sunday between 12 AM - 1 AM. So we thought lets go for a cold backup on weekends. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:28 PM On Fri, 20 Sep 2002, Nat wrote: We use EMC/EDM bcv splits to do a hot backup every night. We shutdown our database once a week for half an hour for cold bcv splits. So far it has worked very well. Just curious, why do you do a cold backup weekly? Do you not trust your hot backups? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Original Message - I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Another Case Insensitive Question
Hello Everyone, Access 2000 on Windows NT Oracle 8.1.7.4.0 on HP-UX 11.0 64-bit Currently both the front end and back end are in Access 2000 and we are moving the back end to Oracle. I've got a situation here I'm not sure how to deal with. Does anyone know how to do case insensitive queries using an Access front end and Oracle back end? We can't change the Access front end due to the sheer number of select statements in queries/reports, so I need to somehow get the database to handle this. We've got an application that does queries such as: select * from table where name like 'smith'; in one query in Access. and select * from table where name like 'Smith'; in another query in Access. and select * from table where name like 'SMITH'; in some other report or query. And the data in table.name could be SMITH,smith,Smith,sMith,smITH, so on and so forth. Is there anyway to get the database to return all records where the name field contains smith,Smith,SMITH, etc. no matter which of the above queries is used?? Can't user InterMedia/Oracle Text since we can't change the queries on the front end. Function based indexes??? Some how make ODBC convert mixed case like statements to upper or lower case? TIA, Julie Julie Lynch Sandia National Laboratories Oracle 8i DBA - OCP8i Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lynch, Julie K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL help
David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AMcc: Please respond toSubject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
RE: Lost ofall redo logs
My long standing position on this is that you take backups so that Just in case something fails, you have the ability to recover. If something has failed, I want backups of EVERYTHING. I know I dont' need the redo logs, but I would feel awful silly if I needed them at some point but didn't back them up because I understood backup and recovery principles. -Original Message- Nahata Sent: Friday, September 20, 2002 8:29 AM To: Multiple recipients of list ORACLE-L I'm not an expert so pardon me if i'm wrong. Why to copy the log files in case of a cold backup if the instance was shutdown cleanly(NOT ABORT)?? The d/b will be consistent so we don't need the redo-log files, isn't it? Isn't it that the DB update all the Datafiles headers with the checkpoint information at the time of CLEAN SHUTDOWN so redo-logs are not required for a restore from cold backup? Though no harm in taking the log files backup too but they are not really needed? Regards, Naveen -Original Message- Sent: Friday, September 20, 2002 7:18 PM To: Multiple recipients of list ORACLE-L Well, it didn't sound like he was using Rman (which does not backup redo logs). So I assumed that he was/should be using a cold-backup strategy - which, if it were me, would backup the redo logs. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 20, 2002 8:48 AM To: Multiple recipients of list ORACLE-L Tom, that statement will open a can of worms(like a religion following) about if/when to backup redo logs. :) joe Mercadante, Thomas F wrote: I agree with you Joe. I would *never* use an undocumented parameter unless Oracle told me to (or unless I was testing something that I could recreate). Using these params on the advice of someone from this list is very dangerous. The larger question is, why are the redo logs not being backed up? Arun, you need to establish a backup policy *immediately* so that you are not being put into this position again. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 7:48 AM To: Multiple recipients of list ORACLE-L KG, doesn't anyone think not having a backup is a bad thing. You can go down that route if you wish but as I remember undocumented parameters are best used with the assistance of OWS and not just used at will. joe K Gopalakrishnan wrote: Arun: You are right. The original poster *clearly* said LOST ALL REDO LOGS NO BACKUP. In the above situation, you have to open the databases using the some special tricks and I don't think you need to go back and refer BAckup and Recovery Manuals (You can not find anything for this situation, that is another story) Those parameters will bring up the database if the database is cleanly shudowned (read: Shutdown Normal/Immediate) and you will have to do some more tricks to get the database up if it is crashed (read: shutdown ABORT). I don't think it is a bad advice.. YMMV KG -Original Message- Chakrapanirao Sent: Wednesday, September 18, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Well one I do want to make clear I have never tried to give bad advice to people. Since the user in his mail had said that he does not have a backup and does not have any online redo logs can you please let me know how else can he open the database, when he said he does not have a back it assumes that he does not have a os backup and also the rman backup. This might be the undocumented thing but this Is the only way it works And this had happened in one our case and the only way we could open the database is to force open the database in a corrupt mode take an export and import back into the new database. If you still think I am trying to give a bad advice well sorry that I am giving bad advices. -Original Message- Sent: Thursday, September 19, 2002 12:33 AM To: Multiple recipients of list ORACLE-L Arun, since WHEN has been using undocumented parameters the normal way of doing things, I think you need to go back and lookup backup/recovery concepts. Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new. joe Arun Chakrapanirao wrote: The only way u can open your database is to add a parameter file as _allow_resetlogs_corruption=TRUE _corrupted_rollback_segments= TRUE _offline_rollback_segments=(the rollback segment names) activate the database and then immediately take an export of the whole database. Create a new database and then import all the data to this new database. -Original Message- Sent: Wednesday, September 18, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Nop, all redo logs gone away... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 5:43 PM What about mirrored redo group members? Igor Neyman, OCP DBA [EMAIL
RE: Backup Strategy - Informal Survey
Well I just happen to know of this great Oracle Press RMAN book due out in October! 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: Friday, September 20, 2002 12:14 PM To: Multiple recipients of list ORACLE-L Robert - My info: Oracle 8.1.7 E.E. Largest DB is 900gb Hot Backups 2x week Cold Backups 1x month Export Backups 1x week on selective schemas I do export backups for creation of test db's, recover data validation blunders, i.e. single object restores. Future plans are RMAN, RMAN, RMAN on 9i. I need incremental backups fast :) Gene [EMAIL PROTECTED] 09/20/02 10:53AM Robert, I don't do exports of our large production systems mainly due to the fact that I'd have to export them to tape and getting that much quite time, so as not to bump in to an ORA-01555 or have someone else hit a rollback segment issue, is impossible. Consequently two hot backups a week are the norm around here we guard out archived redo logs very well. Now I do take exports of selected tables before they get modified or mass changed so that we have a point in time to go back to if all hell breaks loose. Dick Goulet Reply Separator Author: Freeman; Robert [EMAIL PROTECTED] Date: 9/20/2002 6:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- 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 send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: copying all schemas except sys
Hi Well my import choked on the package SYS.DBMS_REPCAT_UTL so I assumed that there was SYS stuff in the full export. -Original Message- Gopalakrishnan Sent: September 19, 2002 3:43 PM To: Multiple recipients of list ORACLE-L Export program **never** exports the contents of SYS. WHy do you need a parameter IGNORE_SYS when it is ignored already? KG -Original Message- Sent: Thursday, September 19, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hi Starting with Oracle8i there is a problem with using full export and full import to re-create a database. The import coughs up a lot of errors due to the SYS schema (replication and help objects). How do people go about moving all the schemas from one instance to another without manually creating all the schema owners in the new instance and then exporting/importing every schema by name. What I want is an ignore sys schema parameter in the export utility. Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: copying all schemas except sys
Hi Well my import choked on SYS.DBMS_REPCAT_UTL so I just assumed that there was SYS schema stuff in the full export. I see now that the import must execute this package. This package was having a problem with the fact that the exported global name did not match that of the instance I was importing into. How do I avoid this problem? I don't want to have to delete the replications stuff out of the instance before I export. Ben -Original Message- Gopalakrishnan Sent: September 19, 2002 3:43 PM To: Multiple recipients of list ORACLE-L Export program **never** exports the contents of SYS. WHy do you need a parameter IGNORE_SYS when it is ignored already? KG -Original Message- Sent: Thursday, September 19, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hi Starting with Oracle8i there is a problem with using full export and full import to re-create a database. The import coughs up a lot of errors due to the SYS schema (replication and help objects). How do people go about moving all the schemas from one instance to another without manually creating all the schema owners in the new instance and then exporting/importing every schema by name. What I want is an ignore sys schema parameter in the export utility. Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DUAL Was: Consulting Position Available-Oracle DBA/Developer/
Jan, Which version of PostgrSQL are you using? --Walt Weaver Bozeman, Montana -Original Message- Sent: Friday, September 20, 2002 11:35 AM To: Multiple recipients of list ORACLE-L DBA/Developer/ We are using PostgreSQL, but still have problems with statistics. Sometimes after VACUUM ANALYZE optimizer doesn't use indexes and performance goes down. JP On Friday 20 September 2002 18:43, you wrote: Ditto for PostgreSQL. For portability you can create a dual table but you have to ensure that it only has one row. ;-) I finding that portability between PostgreSQL and Oracle is easier than most... same to_date, to_char functions, and stuff. Seems like that's part of Postgres' strategy. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L DBA/Developer/NYC As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy - Informal Survey
On our larger database we do exports of some of the smaller users which also have frequent changes. I wish I had some of the larger tables also, it would have saved a big headache last week getting one table restored (7 days to get the files restored from tape, 1.5 hours to modify the control trace file, do a partial recovery, export the table, and import it). Jay Miller -Original Message- Sent: Friday, September 20, 2002 2:15 PM To: Multiple recipients of list ORACLE-L Well I just happen to know of this great Oracle Press RMAN book due out in October! 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: Friday, September 20, 2002 12:14 PM To: Multiple recipients of list ORACLE-L Robert - My info: Oracle 8.1.7 E.E. Largest DB is 900gb Hot Backups 2x week Cold Backups 1x month Export Backups 1x week on selective schemas I do export backups for creation of test db's, recover data validation blunders, i.e. single object restores. Future plans are RMAN, RMAN, RMAN on 9i. I need incremental backups fast :) Gene [EMAIL PROTECTED] 09/20/02 10:53AM Robert, I don't do exports of our large production systems mainly due to the fact that I'd have to export them to tape and getting that much quite time, so as not to bump in to an ORA-01555 or have someone else hit a rollback segment issue, is impossible. Consequently two hot backups a week are the norm around here we guard out archived redo logs very well. Now I do take exports of selected tables before they get modified or mass changed so that we have a point in time to go back to if all hell breaks loose. Dick Goulet Reply Separator Author: Freeman; Robert [EMAIL PROTECTED] Date: 9/20/2002 6:13 AM I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- 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 send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
Re: Lost ofall redo logs
I agree, there is never a problem because you backed up redo log files. There could be a problem with inadvertently restoring them, covering up needed information in the current online redo logs. John [EMAIL PROTECTED] wrote: My long standing position on this is that you take backups so that Just incase something fails, you have the ability to recover. If something hasfailed, I want backups of EVERYTHING. I know I dont' need the redo logs,but I would feel awful silly if I needed them at some point but didn't backthem up because I "understood" backup and recovery principles.-Original Message-NahataSent: Friday, September 20, 2002 8:29 AMTo: Multiple recipients of list ORACLE-LI'm not an expert so pardon me if i'm wrong.Why to copy the log files in case of a cold backup if the instance wasshutdown cleanly(NOT ABORT)??The d/b will be consistent so we don't need the redo-log files, isn't it?Isn't it that the DB update all the Datafiles headers with the checkpointinformation at the time of CLEAN SHUTDOWN so redo-logs are not required forarestore from cold backup?Though no harm in taking the log files backup too but they are not reallyneeded?Regards,Naveen-Original Message-Sent: Friday, September 20, 2002 7:18 PMTo: Multiple recipients of list ORACLE-LWell, it didn't sound like he was using Rman (which does not backup redologs). So I assumed that he was/should be using a cold-backup strategy -which, if it were me, would backup the redo logs.Tom MercadanteOracle Certified Professional-Original Message-Sent: Friday, September 20, 2002 8:48 AMTo: Multiple recipients of list ORACLE-LTom, that statement will open a can of worms(like a religion following)about if/when to backup redo logs. :)joeMercadante, Thomas F wrote: I agree with you Joe.I would *never* use an undocumented parameter unless Oracle told me to (orunless I was testing something that I could recreate).Using these params on the advice of someone from this list is verydangerous.The larger question is, why are the redo logs not being backed up?Arun, you need to establish a backup policy *immediately* so that you arenot being put into this position again.Tom MercadanteOracle Certified Professional-Original Message-Sent: Thursday, September 19, 2002 7:48 AMTo: Multiple recipients of list ORACLE-LKG, doesn't anyone think not having a backup is a bad thing.You can go down that route if you wish but as I remember undocumentedparameters are best used with the assistance of OWS and not just used atwill.joeK Gopalakrishnan wrote: Arun:You are right. The original poster *clearly* saidLOST ALL REDO LOGSNO BACKUP.In the above situation, you have to open thedatabases using the some special tricks andI don't think you need to go back and referBAckup and Recovery Manuals (You can notfind anything for this situation, that isanother story)Those parameters will bring up the databaseif the database is cleanly shudowned (read:Shutdown Normal/Immediate) and you willhave to do some more tricks to get thedatabase up if it is crashed (read: shutdownABORT).I don't think it is a bad advice..YMMVKG-Original Message-ChakrapaniraoSent: Wednesday, September 18, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LWell one I do want to make clear I have never tried to give bad advice topeople.Since the user in his mail had said that he does not have a ba ckup and does not have any online redo logs can you please let me know how else can heopen the database, when he said he does not have a back it assumes that hedoes not have a os backup and also the rman backup.This might be the undocumented thing but this Is the only way it worksAnd this had happened in one our case and the only way we could open thedatabase is to force open the database in a corrupt mode take an export and import back into the new database.If you still think I am trying to give a bad advice well sorry that I amgiving bad advices.-Original Message-Sent: Thursday, September 19, 2002 12:33 AMTo: Multiple recipients of list ORACLE-LArun, since WHEN has been using undocumented parameters the normal wayof doing things, I think you need to go back and lookup backup/recoveryconcepts.Feel free to read both user managed and RMAN backup recovery docs/scenarios. You are giving bad advice to people on the list who might be new.joeArun Chakrapanirao wrote: The only way u can open your database is to add a parameter fileas_allow_resetlogs_corruption=TRUE_corrupted_rollback_segments= TRUE_offline_rollback_segments=(the rollback segment names)activate the database and then immediately take an export of the wholedatabase.Create a new database and then import all the data to this new database.-Original Message-Sent: Wednesday, September 18,
RE: Another Case Insensitive Question
Title: RE: Another Case Insensitive Question Julie, Get a copy of Find and Replace by Rick Fisher. The url is below. With it you CAN change all those queries and reports in the Access FE. It's one of the best $37 an Access developer can spend. http://www.rickworld.com/products.html You can even download a very functional trial version. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Lynch, Julie K [SMTP:[EMAIL PROTECTED]] Hello Everyone, Access 2000 on Windows NT Oracle 8.1.7.4.0 on HP-UX 11.0 64-bit Currently both the front end and back end are in Access 2000 and we are moving the back end to Oracle. I've got a situation here I'm not sure how to deal with. Does anyone know how to do case insensitive queries using an Access front end and Oracle back end? We can't change the Access front end due to the sheer number of select statements in queries/reports, so I need to somehow get the database to handle this. We've got an application that does queries such as: select * from table where name like 'smith'; in one query in Access. and select * from table where name like 'Smith'; in another query in Access. and select * from table where name like 'SMITH'; in some other report or query. And the data in table.name could be SMITH,smith,Smith,sMith,smITH, so on and so forth. Is there anyway to get the database to return all records where the name field contains smith,Smith,SMITH, etc. no matter which of the above queries is used?? Can't user InterMedia/Oracle Text since we can't change the queries on the front end. Function based indexes??? Some how make ODBC convert mixed case like statements to upper or lower case? TIA, Julie Julie Lynch Sandia National Laboratories Oracle 8i DBA - OCP8i Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator
RE: count(*)
I think maintaining counts in other table (Naveen's approach) is more of an application issue. To avoid multiple processes waiting for a lock to update records in seperate table, you could have each process its dedicated row in a seperate table with the current count in it. A slight modified version of Naveen's approach to the problem is described below. I'm interested in knowing any pitfalls with the following approach, Please don't hesitate to take a shot at this. Thanks, Viral Lets say, P1, P2, P3, P4 ... Pn processes would insert large number of rows in large_table. -- Create a table called rcd_cnter or something like that. create table rcd_cnter ( prcss_name varchar2(30) primary key, recd_count number ); -- Each Pi would drop and create its own seq. at the beginining of the process. -- At the beginning the Pi, it would use dbms_application_info.set_module to set the process name i.e. Pi, to identify itself. This will be later available in v$session.module column to the trigger on the large_table. -- Create an after insert trigger on the large_table. The trigger would query v$seesion.module to identify the module/seq number to query, and then use that number and v$session.module to insert or update record in rcd_cntr; -- At any given time the number of records in the table would be arrived by following sql select sum(nvl(recd_cnter,0)) from rcd_cntr; Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: count(*) Date: Fri, 20 Sep 2002 08:33:37 -0800 Naveen - This approach would probably work fine as long as only a single process was running. If multiple processes were inserting rows (likely at the scale of millions of rows/hour), this new table would probably be the bottleneck. Each process must acquire a lock on this row of this table, so the other processes must wait unnecessarily. Been there, done that. Very difficult to do what the user asks without degrading performance, which is probably why Oracle scales higher that Informix. Okay, cheap shot but worth mentioning again. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:04 AM To: Multiple recipients of list ORACLE-L What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions
Re: Best method to move Filesystems to RAW Devices.
Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Bring up the database in restricted mode. 4. Bring tablespace offline 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafile 7. Bring the tablespace online 8. repeat this process till you move all you datafiles. We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:28 PM Hi Nat, As long as you are not changing hardware platforms, you can use dd. (Of course, I assume you're on some flavor of unix, since you didn't mention OS.) You can just do 'dd if=/path/to/filsystem/datafile of=/path/to/raw/volume'. Of course, your database must be down. -Mark On Thu, 2002-09-19 at 14:38, Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use
RE: count(*)
Title: RE: count(*) Hmmm ... everyone is throwing their ideas around ... so here is mine ... Hopefully on this large table you have a PK. 1. First time when you do the count(*) (I hope it will be 10 minutes), do it as follows ... select count(*), max(pk_column) from my ludicrously_large_table / Somehow, remember the max(pk_column) value. Next time onwards do ... select count(*), max(pk_column) from my ludicrously_large_table where pk_column previously_saved_max_pk_column_value / Then add this count(*) plus last count(*) to get the total number. How is that ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Backup Strategy - Informal Survey
Nat Just a plug for always testing your backups, regardless of whether you are doing hot or cold. We do cold backups, and we test them from time to time by restoring them on a test system so the developers have some data to test against. Several times over the years we've found that a file was being skipped on the backup due to an oversight on the people side, or some other issue that developed over time and impacted the backup process. Right now I am both using RMAN and cold backups simply because I haven't had time to complete the qualification process to finally say absolutely that our RMAN backups will recover us from any situation. Just that this has been a lower priority than all the other tasks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 12:52 PM To: Multiple recipients of list ORACLE-L No we do trust our hot backups. Our databases are mostly idle during early ours of Sunday between 12 AM - 1 AM. So we thought lets go for a cold backup on weekends. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:28 PM On Fri, 20 Sep 2002, Nat wrote: We use EMC/EDM bcv splits to do a hot backup every night. We shutdown our database once a week for half an hour for cold bcv splits. So far it has worked very well. Just curious, why do you do a cold backup weekly? Do you not trust your hot backups? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Original Message - I'd like to pose a question to you all and get your response. If you are running a database that is larger than 250GB, what place in your backup strategy does a logical export have? Do you do logical exports at all, and if so with what frequency? Do you feel that logical exports are an important part of your backup/recovery strategy? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Stupid Bind Variable question
Alan Davey wrote: In any given SQL statement, do I need (or should I :^) ) create a bind variable for every literal value even if it is one of the following cases: 1) substring in a substr function 2) date/time format mask used in to_char or to_date function 3) comparison value in a decode/case statement (not in the where clause) I've seen it mentioned to use bind variables when comparing a column to a literal in the where clause, but nothing specific about the above scenarios. Basically what I am asking is, do all literals need to be made bind variables? Thanks. -- Alan Davey [EMAIL PROTECTED] No. Bind variables are toa SQL statement what parameters are to a procedure. If the value is always the same, you shouldn't use a bind variable. -- 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: Lost ofall redo logs
John, seeing as your e-mail name is Ora NT DBA, you probably should have qualified your reply with as long as the DB is down. Backing up live Oracle files on Windows (even NTFS) is a recipe for disaster due to locking problems (with the OS/filesystem or the backup software, I'm not sure). We had this problem long ago and was such a pain that we dumped Oracle7 from Winders to Unix and haven't looked back. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, September 20, 2002 1:30 PM To: Multiple recipients of list ORACLE-L I agree, there is never a problem because you backed up redo log files. There could be a problem with inadvertently restoring them, covering up needed information in the current online redo logs. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to load text file into database table
Oracle sql*loader has many many features...at two of them are applicable in your specific case. You could use CONCATENATE or CONTINUEIF clauses of control file. These clauses help you wherever you have multiple physical records form one logical record. Cheers Viral Desai. From: DENNIS WILLIAMS [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to load text file into database table Date: Fri, 20 Sep 2002 08:53:34 -0800 David - If the file is in just the format you show, my first impulse would be to use the perl script language to get it into a form that sqlldr could easily read. Perl is available on all platforms, including Windows. If you get ambitious, Perl can even directly insert the values into Oracle. Jared Still, the manager of this list has just published a book on that aspect. http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0W 9isbn=0596002106 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best method to move Filesystems to RAW Devices.
I would change to the following: [EMAIL PROTECTED] 09/20/02 03:09PM Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Mount DB 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafiles 7. Open DB We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:28 PM Hi Nat, As long as you are not changing hardware platforms, you can use dd. (Of course, I assume you're on some flavor of unix, since you didn't mention OS.) You can just do 'dd if=/path/to/filsystem/datafile of=/path/to/raw/volume'. Of course, your database must be down. -Mark On Thu, 2002-09-19 at 14:38, Nat wrote: We are planning to move to raw devices for all our existing file systems. Our database size is around 400 Gig. What is the recommended method that you guys feel is best as far as time required to convert and ease of conversion. We feel we cannot use export - import as this may take more time for conversion.. I checked many documents to find out the best method, there are few suggestions to use RMAN to convert to raw. seems it is fastest. At this point we have not configured RMAN on our databases so this suggestion seems to be of no use for us. Please let me know, if any of you went through this exercise and any suggestions and tips will be more beneficial, Thanks in advance, -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official
RE: Best method to move Filesystems to RAW Devices.
Nat - I think your process looks fine, bearing in mind that I haven't used raw devices in years. Others on the list use raw on a daily basis, so may offer some suggestions. My question was much simpler. Suppose I have a 80-gig. device, and a 20-gig. datafile. Okay, my recollection is that I can use dd to copy that datafile onto that device. But that leaves 60-gig. (give or take a few megs) unused. If I copy another datafile to that device, since this isn't a file system, it will simply overwrite the first file. So I have 60-gig of wasted space, unless I issue an Oracle command to expand the datafile to use the rest of the device. You are right, that copying datafiles is MUCH faster than moving data within Oracle, I'm just curious how you plan to deal with size mismatches. Am I missing something here? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Bring up the database in restricted mode. 4. Bring tablespace offline 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafile 7. Bring the tablespace online 8. repeat this process till you move all you datafiles. We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 5:28 PM Hi Nat, As long as you are not changing hardware platforms, you can use dd. (Of course, I assume you're on some flavor of unix, since you didn't
Re: DUAL Was: Consulting Position Available-Oracle DBA/Developer/
7.0 JP On Friday 20 September 2002 20:29, you wrote: Jan, Which version of PostgrSQL are you using? --Walt Weaver Bozeman, Montana -Original Message- Sent: Friday, September 20, 2002 11:35 AM To: Multiple recipients of list ORACLE-L DBA/Developer/ We are using PostgreSQL, but still have problems with statistics. Sometimes after VACUUM ANALYZE optimizer doesn't use indexes and performance goes down. JP On Friday 20 September 2002 18:43, you wrote: Ditto for PostgreSQL. For portability you can create a dual table but you have to ensure that it only has one row. ;-) I finding that portability between PostgreSQL and Oracle is easier than most... same to_date, to_char functions, and stuff. Seems like that's part of Postgres' strategy. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L DBA/Developer/NYC As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 load text file into database table
Viral - I'm glad that someone a lot more familiar than I with SQL*Loader replied, and hopefully that will handle David's problem. I'm just curious about one thing, having encountered this sort of thing before. Can SQL*Loader handle the situation where some lines may not be present? For example, suppose the REASON line is sometimes two or three lines. When I've had situations like this, I've often seen a variable number of lines. That is why I suggested Perl, which can easily handle this sort of thing. Of course, Perl is so handy that I think every DBA should learn it, for tasks just like this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Oracle sql*loader has many many features...at two of them are applicable in your specific case. You could use CONCATENATE or CONTINUEIF clauses of control file. These clauses help you wherever you have multiple physical records form one logical record. Cheers Viral Desai. From: DENNIS WILLIAMS [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to load text file into database table Date: Fri, 20 Sep 2002 08:53:34 -0800 David - If the file is in just the format you show, my first impulse would be to use the perl script language to get it into a form that sqlldr could easily read. Perl is available on all platforms, including Windows. If you get ambitious, Perl can even directly insert the values into Oracle. Jared Still, the manager of this list has just published a book on that aspect. http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0 W 9isbn=0596002106 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Best method to move Filesystems to RAW Devices.
Just keep in mind that you can not offline system tablespace ;) - Kirti -Original Message- Sent: Friday, September 20, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Nat - I think your process looks fine, bearing in mind that I haven't used raw devices in years. Others on the list use raw on a daily basis, so may offer some suggestions. My question was much simpler. Suppose I have a 80-gig. device, and a 20-gig. datafile. Okay, my recollection is that I can use dd to copy that datafile onto that device. But that leaves 60-gig. (give or take a few megs) unused. If I copy another datafile to that device, since this isn't a file system, it will simply overwrite the first file. So I have 60-gig of wasted space, unless I issue an Oracle command to expand the datafile to use the rest of the device. You are right, that copying datafiles is MUCH faster than moving data within Oracle, I'm just curious how you plan to deal with size mismatches. Am I missing something here? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Bring up the database in restricted mode. 4. Bring tablespace offline 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafile 7. Bring the tablespace online 8. repeat this process till you move all you datafiles. We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command. Using dd is the fastest method to accomplish it. However, it is necessary to know how many blocks to skip in the raw device (e.g. it is necessary to skip 64K on Tru64 Unix), so that the information necessary for the Operating System is not overwritten. The information on how many blocks to skip is different on the different platforms. Using RMAN there's no necessity to know such platform specific information. . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent:
Re: How to load text file into database table
David, I don't know sqlldr that well, but a possible quick and dirty solution - is to load the text file into a one column oracle table, and then in a subsequent step, use SQL to move the data to the proper columns of the proper Oracle table. The ctl file from the first step would be something like this: 1) LOAD DATA INFILE useraccnt.txt APPEND concatentate 7 INTO TABLE temp (txt char(2000) ) In the second step you could use a bunch of substr and instr to find the column data, or use PL/SQL to loop through the text , or you could use a function in the owa_pattern package Chaim Nguyen, David M [EMAIL PROTECTED]@fatcity.com on 09/20/2002 11:53:29 AM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: DUAL Was: Consulting Position Available-Oracle DBA/Developer/
Thanks, Jan. Are you still at work? --Walt (1.85254E-16ly tall) Weaver -Original Message- Sent: Friday, September 20, 2002 1:59 PM To: Multiple recipients of list ORACLE-L DBA/Developer/ 7.0 JP On Friday 20 September 2002 20:29, you wrote: Jan, Which version of PostgrSQL are you using? --Walt Weaver Bozeman, Montana -Original Message- Sent: Friday, September 20, 2002 11:35 AM To: Multiple recipients of list ORACLE-L DBA/Developer/ We are using PostgreSQL, but still have problems with statistics. Sometimes after VACUUM ANALYZE optimizer doesn't use indexes and performance goes down. JP On Friday 20 September 2002 18:43, you wrote: Ditto for PostgreSQL. For portability you can create a dual table but you have to ensure that it only has one row. ;-) I finding that portability between PostgreSQL and Oracle is easier than most... same to_date, to_char functions, and stuff. Seems like that's part of Postgres' strategy. -Original Message- Sent: Friday, September 20, 2002 9:24 AM To: Multiple recipients of list ORACLE-L DBA/Developer/NYC As well as in MySQL There is no need for such table in SQL Server. If you need, you do just: select 'whatever' without from clause. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best method to move Filesystems to RAW Devices.
Denis, Sorry I missed your main question last time. Each datafile in our database will represent a separate raw device. so question of overwriting files will never come. If I have 30 datafiles in 5 files systems, I will be creating 30 raw devices with properly sized devices to match my datafile size. As of now I have created a spreadsheet which is mapping each of datafile to new raw device. I have taken current size of my datafile + added a buffer size + growth for next 2 months. Hope this works without any problem. Let me know what you think about this. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 3:43 PM Nat - I think your process looks fine, bearing in mind that I haven't used raw devices in years. Others on the list use raw on a daily basis, so may offer some suggestions. My question was much simpler. Suppose I have a 80-gig. device, and a 20-gig. datafile. Okay, my recollection is that I can use dd to copy that datafile onto that device. But that leaves 60-gig. (give or take a few megs) unused. If I copy another datafile to that device, since this isn't a file system, it will simply overwrite the first file. So I have 60-gig of wasted space, unless I issue an Oracle command to expand the datafile to use the rest of the device. You are right, that copying datafiles is MUCH faster than moving data within Oracle, I'm just curious how you plan to deal with size mismatches. Am I missing something here? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Bring up the database in restricted mode. 4. Bring tablespace offline 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafile 7. Bring the tablespace online 8. repeat this process till you move all you datafiles. We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter tablespace test_ts online; Notes: == 1. If you are using RMAN as the backup tool then a backup after the performed steps is recommended, because otherwise RMAN treats the copied file as a backup. 2. Usually Oracle datafiles are moved from filesystem to raw devices using the dd command.
RE: PL/SQL help
Title: RE: PL/SQL help Go to Tom Kyte's sight and look for contexts - he explains a way to build sql statements using native dynamic sql and refcursors and contexts where the select statement and predicate is variable and the output might also be variable. I get there by searching www.yahoo.com for asktom -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 2:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQL help David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AM cc: Please respond to Subject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California
Spool Oracle Tables into Excel Format
All, Is it possible to create Oracle reports into Excel format ? Is it possible to spool Oracle tables into Excel format? Thanks in Advance Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 load text file into database table
Dennis, Absolutely, I agree. Perl is very handy and easy to learn..., You could use awk also on unix, pretty nifty stuff... To answer to your situation where the REASON line could be more than one/two/three or n lines- Yes, you could use CONTINUEIF. It provides a comparison operators (= and !=) in each physical line to look for certain tokens in certain positions. You can look/peek at current, previous or next physical lines before deciding break of lof=gical record, while loading Pretty cool stuff. I think you can load any free-format as long as you can define logical record by some rule. Again, not to discount your suggestion, there are many ways, I just thought passing my idea. Cheers, Viral. From: DENNIS WILLIAMS [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to load text file into database table Date: Fri, 20 Sep 2002 12:05:43 -0800 Viral - I'm glad that someone a lot more familiar than I with SQL*Loader replied, and hopefully that will handle David's problem. I'm just curious about one thing, having encountered this sort of thing before. Can SQL*Loader handle the situation where some lines may not be present? For example, suppose the REASON line is sometimes two or three lines. When I've had situations like this, I've often seen a variable number of lines. That is why I suggested Perl, which can easily handle this sort of thing. Of course, Perl is so handy that I think every DBA should learn it, for tasks just like this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Oracle sql*loader has many many features...at two of them are applicable in your specific case. You could use CONCATENATE or CONTINUEIF clauses of control file. These clauses help you wherever you have multiple physical records form one logical record. Cheers Viral Desai. From: DENNIS WILLIAMS [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to load text file into database table Date: Fri, 20 Sep 2002 08:53:34 -0800 David - If the file is in just the format you show, my first impulse would be to use the perl script language to get it into a form that sqlldr could easily read. Perl is available on all platforms, including Windows. If you get ambitious, Perl can even directly insert the values into Oracle. Jared Still, the manager of this list has just published a book on that aspect. http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=0E2CIPD0 W 9isbn=0596002106 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a text file and need to load it into database table using sqlldr utility. Is there a way to load a text file? I know how to load csv file but not text file. *** Below is text file format. Date: Wed Aug 29 10:43:53 CDT 2001 Name: Paris By Night Email: [EMAIL PROTECTED] Phone: 202-333- Location: Washington, DC Equipment needing to access: EMS, , , , , Reason: Not working I want to above data into useraccount table which has following columns. date username email phone location equipment reason Thanks in advance, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). _ MSN Photos is the easiest way to share and print your photos:
Re: Best method to move Filesystems to RAW Devices.
Another option is to shutdown db, backup the files to tape, remove filesystems, create symbolic links named the same as your datafile names linked to the raw devices, restore files to symbolic links (i.e. raw devices), startup db. Just a thought. Gene [EMAIL PROTECTED] 09/20/02 04:20PM Denis, Sorry I missed your main question last time. Each datafile in our database will represent a separate raw device. so question of overwriting files will never come. If I have 30 datafiles in 5 files systems, I will be creating 30 raw devices with properly sized devices to match my datafile size. As of now I have created a spreadsheet which is mapping each of datafile to new raw device. I have taken current size of my datafile + added a buffer size + growth for next 2 months. Hope this works without any problem. Let me know what you think about this. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 3:43 PM Nat - I think your process looks fine, bearing in mind that I haven't used raw devices in years. Others on the list use raw on a daily basis, so may offer some suggestions. My question was much simpler. Suppose I have a 80-gig. device, and a 20-gig. datafile. Okay, my recollection is that I can use dd to copy that datafile onto that device. But that leaves 60-gig. (give or take a few megs) unused. If I copy another datafile to that device, since this isn't a file system, it will simply overwrite the first file. So I have 60-gig of wasted space, unless I issue an Oracle command to expand the datafile to use the rest of the device. You are right, that copying datafiles is MUCH faster than moving data within Oracle, I'm just curious how you plan to deal with size mismatches. Am I missing something here? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Dennis, We are thinking of doing following steps for our RAW conversions. Let me know whether it makes sense, 1. Shutdown Database. 2. Take Full Backup 3. Setup all our raw devices ( properly corresponding to our original filesystem datafiles.) 4. Bring up the database in restricted mode. 4. Bring tablespace offline 5. Use the dd command to move the datafiles to the raw device. 6. Rename the moved datafile 7. Bring the tablespace online 8. repeat this process till you move all you datafiles. We feel creating table as select * or export /import is not feasible as our database is big (around 400 Gigs ) with very large tables. Thanks, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 20, 2002 12:18 PM Nat - I haven't worked with raw for a few years, but from my dim recollection, since you are managing what is on the device, first the system administrator had to tell me how many blocks I could write to. I also dimly recall the AIX issue, but certainly couldn't recall the answer. Anyway, this means that you have only a single file on the device. Based on that, my assumption was that you would want to use the entire disk and it would be unlikely that your file system file would coincidentally the same size as your raw device, so you would want to precreate a new tablespace with a datafile of just the right size. That means that you would need to move the data itself, not just the datafile. Am I missing something here, or just brain-dead on Friday? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, September 20, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Mark, Thanks for the reply. We are on AIX 4.3. We are not changing hardware when we move on to RAW devices. Our entire hardware is going to remain same. We feel dd is the fastest method of copying the files. But we need to figure out how many blocks we should skip in raw devices.(point 2 in NOTES below). We still do not know how to do that. Metalink says if we use RMAN we do not need to do any header calculation . Below is the article from metalink.. Thanks again for your suggestions. goal: How to convert datafile from raw device to file system a.. fact: Oracle Server - Enterprise Edition a.. fix: Use RMAN to move datafiles from raw devices to file system. 1. Connect to the database: $ sqlplus system/manager@orcl 2. Put the tablespace with the datafile, which should be converted, offline: SQL alter tablespace test_ts offline; 3. Start rman and connect it to the database: $ rman nocatalog target rman/rman@orcl 4. Move the datafile to file system: RMAN run { 2 allocate channel c1 type disk; 3 copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf'; 4 } 5. Rename the moved datafile: SQL alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts. dbf'; 6. Put the tablespace back online: SQL alter
Re:Spool Oracle Tables into Excel Format
Bob, You can create a CSV file from SQL*Plus with the following statement: select column_name||','||column_name||','||etc. from table_name where ..; Dick Goulet Reply Separator Author: Bob Robert [EMAIL PROTECTED] Date: 9/20/2002 12:33 PM All, Is it possible to create Oracle reports into Excel format ? Is it possible to spool Oracle tables into Excel format? Thanks in Advance Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Spool Oracle Tables into Excel Format
Sure. Use ',' as colsep, and have file extension csv. Jun -Original Message- Sent: Friday, September 20, 2002 4:33 PM To: Multiple recipients of list ORACLE-L All, Is it possible to create Oracle reports into Excel format ? Is it possible to spool Oracle tables into Excel format? Thanks in Advance Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Feng, Jun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
shared_pool error
Hi All! We get ora-04031 on Oracle816 on Unix. I'm aware that this is a bug about memory leak and we planing upgrade to Oracle 8173 to fix it. In a mean time I increased the shared_pool and shared_pool_reserved_size , and also ping some packages in. Now error sad that: enable to allocate 4096 bytes of shared memory ('shared pool,BEGIN DBMS_OUTPUT.ENABLE; END;. Oracle can't allocate memory for the object which is already in memory? Maybe I missing something.??? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Spool Oracle Tables into Excel Format
Title: RE: Spool Oracle Tables into Excel Format There were some posts recently suggesting the spooling of the columns using a comma as a separator. But check out this article in XML Journal (http://www.syntelinc.com/syntel/english/0072/SYNT_XMLjrnl.pdf). It shows how to build preformatted Excel reports with XML, but it requires Excel 2002. HTH Tony Aponte -Original Message- From: Bob Robert [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 4:33 PM To: Multiple recipients of list ORACLE-L Subject: Spool Oracle Tables into Excel Format All, Is it possible to create Oracle reports into Excel format ? Is it possible to spool Oracle tables into Excel format? Thanks in Advance Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:Spool Oracle Tables into Excel Format
By the way, would you use replace to get rid of the commas withiin fields so that Excel reads it properly? [EMAIL PROTECTED] wrote: Bob,You can create a CSV file from SQL*Plus with the following statement:selectDate: 9/20/2002 12:33 PMAll,Is it possible to create Oracle reports into Excelformat ? Is it possible to spool Oracle tables into Excelformat?Thanks in AdvanceBob__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: Bob RobertINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego! ! , California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB! ! ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do you Yahoo!? New DSL Internet Access from SBC & Yahoo!
Re: Spool Oracle Tables into Excel Format
You can spool Oracle output into text files using SQL*Plus. Excel can handle plain text and Comma Delimitted Text files. RWB Bob Robert [EMAIL PROTECTED]@fatcity.com on 09/20/2002 03:33:24 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: All, Is it possible to create Oracle reports into Excel format ? Is it possible to spool Oracle tables into Excel format? Thanks in Advance Bob __ 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: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
OEM - Change Management Pack - opinions, please
We're planning to revisit change management pack to see whether it's useful or not. Versions 2.0 and 2.1 were rejected as buggy and useless. Is anybody here using the Change Management Pack and what are the experiences? Please, do not use expletives in your replies.
RE: copying all schemas except sys
Try setting the GLOBAL_NAMES parameter in the init.ora file to FALSE for the target database. RWB Ben [EMAIL PROTECTED]@fatcity.com on 09/20/2002 01:21:34 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Well my import choked on SYS.DBMS_REPCAT_UTL so I just assumed that there was SYS schema stuff in the full export. I see now that the import must execute this package. This package was having a problem with the fact that the exported global name did not match that of the instance I was importing into. How do I avoid this problem? I don't want to have to delete the replications stuff out of the instance before I export. Ben -Original Message- Gopalakrishnan Sent: September 19, 2002 3:43 PM To: Multiple recipients of list ORACLE-L Export program **never** exports the contents of SYS. WHy do you need a parameter IGNORE_SYS when it is ignored already? KG -Original Message- Sent: Thursday, September 19, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hi Starting with Oracle8i there is a problem with using full export and full import to re-create a database. The import coughs up a lot of errors due to the SYS schema (replication and help objects). How do people go about moving all the schemas from one instance to another without manually creating all the schema owners in the new instance and then exporting/importing every schema by name. What I want is an ignore sys schema parameter in the export utility. Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: Authentication with Active Directory
Henry, I've been looking into something similar at a client. I think you want to look at external authentication. This will use your OS authentication, which in turn uses AD. The problem is that OS authentication is not very secure when using PC clients, so we are using Kerberos as well, which does make it secure. In order to use Kerberos, you'll need the Advanced Security Option. I think you'll also need to upgrade your NT's to W2K, as W2K uses Kerberos by default. I don't know what the Enterprise Login Asst. is, I assume it assists you in creating Enterpise Users. Enterpise Users allow you to create a user once and have it shared by multiple instances, and only works if you have external authentication. Haven't tried that yet, but it's on the list. OID is Oracle's version of AD. We're not using it here - using MS AD instead. HTH, Gary Gary Kirsh Next Extent Consulting -Original Message- Sent: Wednesday, September 18, 2002 5:58 PM To: Multiple recipients of list ORACLE-L We have several databases spread out over Unix and NT Servers. I have been given the task to find out what it will take for our users to log in to the databases with their AD Id/password. Has anybody out there done this? Here are some questions I have: 1) Do I need to have Advanced Security Option? 2) Does Enterprise Login Asst. factor into this? 3) Does Oracle internet directory factor into this? Keith H. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry, Keith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kirsh, Gary INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 can I change Oracle as well as NT Domain passwords
Are you trying to change the Oracle and NT Domain accounts which are the same and you want the same password ??? If not, write a script or request that one be sent to you. -Original Message- Sent: Friday, September 20, 2002 10:31 AM To: Multiple recipients of list ORACLE-L Hi All How can I change passwords of a user of Oracle application as well as W2K/NT Login? Any utility in 8/8i can do this form me? Thanks in Advance Shiva B -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baswannappa, Shiva INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: shared_pool error
Greg: If the error is coming from DBMS_OUTPUT, then it is probably PL/SQL related. Try increasing the LARGE_POOL_SIZE parameter in the init.ora file. I would suggest setting it to at least 2MB. By the way, what are the shared_pool_size and large_pool_size parameters set to now? RWB Greg Faktor [EMAIL PROTECTED]@fatcity.com on 09/20/2002 03:59:21 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi All! We get ora-04031 on Oracle816 on Unix. I'm aware that this is a bug about memory leak and we planing upgrade to Oracle 8173 to fix it. In a mean time I increased the shared_pool and shared_pool_reserved_size , and also ping some packages in. Now error sad that: enable to allocate 4096 bytes of shared memory ('shared pool,BEGIN DBMS_OUTPUT.ENABLE; END;. Oracle can't allocate memory for the object which is already in memory? Maybe I missing something.??? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).