Re: PCTFREE and PCTUSED (and ASSM)
Hi Mladen, Oh, I have no doubts ;) Hurt you still don't trust me but considering our little wager (2 tickets to the Bowie world tour) I guess it's only fair you go to a neutral referee. Bet when they confirm what I say they don't give as detailed an explanation ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 09, 2003 12:19 AM Richard, I asked the question on the Metalink. The mighty Metalink will, hopefully, resolve the doubt once and for all. Tom Kyte is busy so he doesn't accept new questions right now. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Howto drop a corrupt database ?
Title: Howto drop a corrupt database ? Hi, I'm running Oracle 8.0.4 on AIX: for one of the databases, my datafiles got corrupted and I got NO BACKUP (and NOARCHIVELOG) :( SVRMGR alter database open; ORA-01122: database file 6 failed verification check ORA-01110: data file 6: '/data4/test/testdb01.dbf' ORA-01251: Unknown File Header Version read for file number 6 no problem I'm willing to restart, so I want to drop the whole database and start all over again (using the same name). How can I drop this database ??? When I try to do this from svrmgrl, I get: ORA-01109: database not open, duh ! Any ideas how to proceed ? Kind regards, Gorik
Re: Howto drop a corrupt database ?
Hi Gorik, Do you want to drop the database as a whole, i.e. including your system tablespace etc? Just delete all your controlfiles, datafiles and redologfiles, keep the initSID.ora and start creating the database all over again. (You do have a create-script, don't you?). Of course you can execute the create database command using the REUSE clause for all files, but I hate including this in a script. When someone, it might be even be you, inadvertently re-executes the script all your files might be gone. Not using the REUSE clause will simply cause the script to fail. Regards, Carel-Jan At 04:09 9-11-03 -0800, you wrote: Hi, I'm running Oracle 8.0.4 on AIX: for one of the databases, my datafiles got corrupted and I got NO BACKUP (and NOARCHIVELOG) :( SVRMGR alter database open; ORA-01122: database file 6 failed verification check ORA-01110: data file 6: '/data4/test/testdb01.dbf' ORA-01251: Unknown File Header Version read for file number 6 no problem I'm willing to restart, so I want to drop the whole database and start all over again (using the same name). How can I drop this database ??? When I try to do this from svrmgrl, I get: ORA-01109: database not open, duh ! Any ideas how to proceed ? Kind regards, Gorik DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
Re: Sequences CYCLEing -- was RE: How do you genrate primary
So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant At 08:24 AM 08-11-03 -0800, you wrote: Being sort of DBA Doubting Tom, I have a bad habit of trying and testing stuff. Here is what happens with sequences: SQL create sequence test1 start with 1 maxvalue 4 cycle nocache; Sequence created. SQL select test1.nextval from dual 2 / NEXTVAL -- 1 SQL / NEXTVAL -- 2 SQL / NEXTVAL -- 3 SQL / NEXTVAL -- 4 SQL / NEXTVAL -- 1 SQL / NEXTVAL -- 2 SQL On 2003.11.08 10:54, Hemant K Chitale wrote: Ah yes. The exception case when sequence numbers are not unique. Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the the number would never exceed 4 digits and didn't want to waste resources and space]. And I do vaguely remember that I HAVE seen a Sequence CYCLE over and restart. Can't remember the details, though this was many years ago. It takes all kinds of developers and database designers to make Oracle interesting. Hemant At 03:29 PM 05-11-03 -0800, you wrote: In theory I suppose it's possible to have overlaps, but this has nothing to do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND set MAXVALUE to something less than reasonable (the default is NOMAXVALUE which IIRC means 10 to the power 27) AND don't create a unique index on the column storing the sequence, then maybe you can end up with multiple rows having the same value? Never heard of anyone doing that, of course, but in theory ... Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, November 06, 2003 7:34 AM To: Multiple recipients of list ORACLE-L I've never heard of an Oracle sequence not generating unique id's, OPS/RAC or not. Gaps, yes. Overlaps, never. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Todd Boss Sent: Wednesday, November 05, 2003 1:09 PM To: Multiple recipients of list ORACLE-L There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your natural key. - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though.
Re: Sequences CYCLEing -- was RE: How do you genrate primary
Hemant K Chitale wrote: So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant What I have seen used are non-cycling sequences which are forced to cycle - the idea is to restart the numbering from 1 everyday. So, everyday at midnight the sequences are ALTERed so that their maximum is today's maximum, and they are forced to return to 1 - before making them NOCYCLE again. The big advantage on dropping and recreating them is that existing privileges stay in place and you don't have to GRANT SELECT to everybody ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sequences CYCLEing -- was RE: How do you genrate primary
I've seen several of them on projects I worked on: People converted older data-models - if model is the right name ;-) - and stuck to the small key-fields they had. One particlaur example got a nice :-( algorithm replaced by this nice lick Oracle feature called 'sequence'. Of course increasing marketing success caused an increasing need for unique keys, causing exact the cycling problem described. Lesson learned: make a calculation in advance: Most of the time 9 digits will do the job: it will let you generate 1 key per second, 86400 seconds a day, 11574 days. This is approx. 31 years and 8 months, and that migh be sufficient. If not, simply add some extra digits. Just get some proof that your choise is right! Of course this will not work out when you insert 6000 rows per second, all day long, but then you might have other problems ;-). It's amazing how people (calling themself programmers) are putting together some code, without any idea of some future behaviour of their code! (I remember some Y2K problem a few years ago. I hate to say this, but all my code, as from where I started working in IT back in 1982) was Y2K proof. However, I must admit, my older C-code won't survive the next 'millenium'-problem, when the unix-date-format can't follow the 'seconds + 01-01-1970' format. Maybe 64-bit compilers will resolve that problem. But, mark my words ;-) there will be some concern, dataconversion and other familiar stuff which will us, elderly and bitter software veterans give some deja-vu feelings right then! Carel-Jan At 07:04 9-11-03 -0800, you wrote: So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
Re: Uncle Larry, wake up!!!
Amen. In Miracle we'll do nicely in the years to come by being able to support VMS, PL/SQL (the COBOL of the databases - widely used, very efficient, not in vogue anymore for new projects), perhaps Oracle (if it goes the way we could fear), UNIX (which is certainly dead now - if you had any doubts before, it's obvious by now. It's over.), and other legacy products and systems. It might not be a growth area, but it will be around for many years. There are so many good ideas out there from the various database vendors. The whole MySQL thing seems very smart (and will win over PostgreSQL of course, because the marketing is better). Microsoft's idea of allowing you to write stored procedures in .Net compliant languages (I wish Oracle would make PL/SQL .Net compliant - that would be very cool indeed) which makes it possible to get rid of that #¤% TransactSQL crap. Oracle's new-found emphasis on the right performance stuff in 10g. But I wonder if databases will be something special at all in a few years time? Why not just do Google-things for selects and some not-yet-invented Google-DML on all sorts of data sources? Microsoft will make SQL Server a part of the file system in 2005, I think. Then what? It's Linux and Windows and nothing else then. Mogens Melanie Caffrey wrote: This is true, Tom. Some technologies never die ... Personally, COBOL and CICS are not my favorite skillsets, *but* knock wood if it ever comes down to going back to coding in COBOL or being unemployed then --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: it's a quiet little secret in consultant-land right now that the older technologies are in play. as the older-folks retire, there is a need for cobol-based support. especially in NY state agencies. -Original Message- Sent: Friday, November 07, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Goulet, Dick scribbled on the wall in glitter crayon: OH, ANCIENT History!! u... do i admit to getting a job hit last week because i know CICS?;-) it's still out there and still being used. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] A hundred times every day I remind myself that my inner and outer life are based on the labours of others. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Interesting PL/SQL Puzzle
I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Uncle Larry, wake up!!!
Will 10g(rid) be succeeded by 11G(oogle)? CJ DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
Re: Uncle Larry, wake up!!!
Nice thoughts Mogens. I just read an article in a Dutch magazine which states, and I fully agree, that the whole Soapy netservice thing won't work. Or will webservices be 2PC enabled? That will scale! (Use more soap) :-( I like your idea of google-dml. it's a real challenge! let's make google-2pc and google-PL/SQL! Carel-Jan At 08:59 9-11-03 -0800, you wrote: Amen. In Miracle we'll do nicely in the years to come by being able to support VMS, PL/SQL (the COBOL of the databases - widely used, very efficient, not in vogue anymore for new projects), perhaps Oracle (if it goes the way we could fear), UNIX (which is certainly dead now - if you had any doubts before, it's obvious by now. It's over.), and other legacy products and systems. It might not be a growth area, but it will be around for many years. There are so many good ideas out there from the various database vendors. The whole MySQL thing seems very smart (and will win over PostgreSQL of course, because the marketing is better). Microsoft's idea of allowing you to write stored procedures in .Net compliant languages (I wish Oracle would make PL/SQL .Net compliant - that would be very cool indeed) which makes it possible to get rid of that #¤% TransactSQL crap. Oracle's new-found emphasis on the right performance stuff in 10g. But I wonder if databases will be something special at all in a few years time? Why not just do Google-things for selects and some not-yet-invented Google-DML on all sorts of data sources? Microsoft will make SQL Server a part of the file system in 2005, I think. Then what? It's Linux and Windows and nothing else then. Mogens Melanie Caffrey wrote: This is true, Tom. Some technologies never die ... Personally, COBOL and CICS are not my favorite skillsets, *but* knock wood if it ever comes down to going back to coding in COBOL or being unemployed then ... --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: it's a quiet little secret in consultant-land right now that the older technologies are in play. as the older-folks retire, there is a need for cobol-based support. especially in NY state agencies. -Original Message- Sent: Friday, November 07, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Goulet, Dick scribbled on the wall in glitter crayon: OH, ANCIENT History!! u... do i admit to getting a job hit last week because i know CICS?;-) it's still out there and still being used. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] A hundred times every day I remind myself that my inner and outer life are based on the labours of others. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911
RE: Interesting PL/SQL Puzzle
I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Yechiel's idea reminds me of one of that 'tuning tips' of old which said 'avoid calling a routine too far away in the code' (because it could be in another page, etc.). This kind of phenomenon, reference to chunks of code which have been safely parked away on disk may also come into play. SF Khedr, Waleed wrote: I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Sequences CYCLEing -- was RE: How do you genrate primary
Sunday, November 9, 2003, 10:44:25 AM, Stephane Faroult ([EMAIL PROTECTED]) wrote: SF The big advantage on dropping and recreating them is that SF existing privileges stay in place and you don't have to GRANT SELECT to SF everybody ... Yeah, I wrote a script one to let me adjust sequences in the manner you've just described. I should try and dig that up, though I think it's long-lost. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Sequences CYCLEing -- was RE: How do you genrate
Slightly OT: Way before AR was available, we built a set of triggers to do the replication job (Oracle 7.0.something). Of course synchronisation of sequences was a hell of a job. Lucky for us, the system was more DSS than OLTP: approx. 3000 - 10.000 transactions/day. Furthermore there was a real Master/Slave database architecture, the slave would only be queried, and wasn't allowed to be updated from ordinary users. This was enforced by pre-DML triggers, which prevented any DML to be executed except those coming form the replication process. Because this was a flight information display system, running on several airports, high availabilty was the goal. All primary keys were meaningless, a 9 digit number, and generated from a sequence by a pre-insert trigger. Because of some home-grown GUI program, used in the project, the key needed to be unique in the whole set of tables, so onde qequence was used. Ordering was important, gaps were no problem. The same pre-insert-trigger would find a non-null primary key when the record came from the replication process. If a non-null primary key was found, it would start a loop hammering the sequence until it reached the same id as the id just received. So we enforced synchronisation between both sequences, without the need of having them started at different offsets, which would have violated the 'ordered constraint' needed by the GUI-stuff. Carel-Jan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PCTFREE and PCTUSED
We haven't even touched the subject of ITL entries which is also unaffected by ASSM. By the way, in 9i there is one interesting issue with ITLs, that every table datablock gets 2 ITL slots by default, even if INITRANS and MAXTRANS are set to 1. And blocks formatted due direct path inserts will have 3 ITL entries by default. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: file sizes over 32GB
The honor should go to Steve Adams, I read it from his website. Tanel. And we do need education, because we do want to be bricks in the wall. You seem to know everything, so please, don't leave us alone. On 2003.11.07 12:49, Tanel Poder wrote: For reasons why, think about it from the backup/restore perspective. Which database can be backed up or restored faster: one with 100 2Gb datafiles or one with 2 100Gb datafiles? Datafile management is just like extent management. As Roger Waters said, All in all, they're all just bricks in the wall... :-) I don't know whether file system lock contention still applies in modern file systems, but the larger the files you are writing in are, the more contention you have to file write lock. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Howto drop a corrupt database ?
Just for the record, in 10g you actually can use drop database command ;) Tanel. - Original Message - From: Carel-Jan Engel To: Multiple recipients of list ORACLE-L Sent: Sunday, November 09, 2003 3:24 PM Subject: Re: Howto drop a corrupt database ? Hi Gorik,Do you want to drop the database as a whole, i.e. including your system tablespace etc?Just delete all your controlfiles, datafiles and redologfiles, keep the initSID.ora and start creating the database all over again. (You do have a create-script, don't you?). Of course you can execute the create database command using the REUSE clause for all files, but I hate including this in a script. When someone, it might be even be you, inadvertently re-executes the script all your files might be gone. Not using the REUSE clause will simply cause the script to fail.Regards, Carel-JanAt 04:09 9-11-03 -0800, you wrote: Hi, I'm running Oracle 8.0.4 on AIX: for one of the databases, my datafiles got corrupted and I got NO BACKUP (and NOARCHIVELOG) :(SVRMGR alter database open; ORA-01122: database file 6 failed verification check ORA-01110: data file 6: '/data4/test/testdb01.dbf' ORA-01251: Unknown File Header Version read for file number 6 no problem I'm willing to restart, so I want to drop the whole database and start all over again (using the same name).How can I drop this database ??? When I try to do this from "svrmgrl", I get: "ORA-01109: database not open", duh ! Any ideas how to proceed ? Kind regards, Gorik DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C2743 BX WaddinxveenThe Netherlandstel. +31 (0) 182 640 428fax +31 (0) 182 640 429mobile+31 (0) 653 911 950e-mail [EMAIL PROTECTED]
Re: IMP using the same DMP file
With these sizes, I usually onlyexport the schema structures using rows=n and tables are transferred over dblinks in direct mode (there is no direct mode import possibility until 10g, and even then it's done using data pump, Oracle's new exp/imp toolset) Also I build indexes only after import, in nologging and parallel mode. Tanel. - Original Message - From: Gene Sais To: Multiple recipients of list ORACLE-L Sent: Thursday, November 06, 2003 3:09 PM Subject: Re: IMP using the same DMP file On the subject of export/import, has anyone exported a 1 TB db and imported it into another db? The largest I have done is 300gb w/out problems. [EMAIL PROTECTED] 11/05/03 05:04PM You can import the same file into two instances simultaneously, because"imp" doesn't lock the import file, unless the import file was producedby simultaneous export into the same file from two different instances, in which case you have something what is scientifically known as "monster mess" and is really appropriate for the Halloween time.On 11/05/2003 04:49:36 PM, Whittle Jerome Contr NCI wrote: Hi, We were just wondering if you can IMP into two instances using the same dmp file at the same time? We need to refresh both our development and test instances with data from our production database and doing both at once might save some time. 8.1.7 and Unix. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 Mladen GogalaOracle DBANote:This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mladen Gogala INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: full recovery
He probably meant, that tracefile of controlfile only contains the crucial information about data- logfile locations and only a bit of other information. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:54 PM Rich - Could you point to the place where Robert states that bit about controlfiles to trace? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 07, 2003 1:59 PM To: Multiple recipients of list ORACLE-L I believe that an 'alter database backup controlfile to trace' loses the RMAN data stored in the control files if you're not using a repository (if I remember right from RF's book). Since we're not using a repository, we've got controlfile autobackup on (in 9i use 'configure controlfile autobackup on'). We dump these disk copies via filesystem backups as a safety measure. We also do a backup database, archivelogs, and then control (but all in one step; I'm not sure I'm clear on the reason for separating them into three backup sets) Rich -- Rich Holland(913) 645-1950SAP Technical Consultant print unpack(u,92G5S\=\!A;F]T:5R(\'!EFP\@:%C:V5R\[EMAIL PROTECTED]); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mercadante, Thomas F Sent: Friday, October 24, 2003 2:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: full recovery AK, First, your normal backup should backup your database in the following order: Database, archivelogs and then control file. I actually do this in three separate Rman steps. This is so that the most recent control file is backups up after all of the data. This allows your to perform an incoimplete-recovery-restore to as late a time as possible. Also, consider adding a database trigger that, upon startup, perform an alter database backup controlfile to trace; Keep a copy of this trace file someplace safe as a sanity check. You could use it to recreate your controlfiles if all else fails. Your recovery steps are as follows: 1. restore oracle software from tape. 2. restore config files ( init.ora , listener. ora ). 3. startup instance with nomount. 4. run Rman to restore the control file from tape. 5. Alter database mount 6. run Rman to restore database files 7. alter database open resetlogs. 8. perform a brand-new Rman backup (database, logs controlfile) turn the system back to the users (with many back-pats from management). You should be testing this on a regular basis. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, October 24, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Here is a scenerio : I am taking full database backup everynight using rman to tape . which includes archive logs and control file. Not using catalog.Also have a backup of complete file system including oracle software and configuration files ( init.ora , listener.ora etc.. ) I lost the host on a particular day at 12 am afternoon. Now I want to restore this db to latest possible time to another host ( with same name ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rich Holland INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat 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: Getting Number of Rows in CTAS across DBLink
Really-really excellent suggestions in this thread. My respect, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 12:49 AM Arup, I am currently devising something I have already more or less done in the past (version 6, pre-analyse) to get a low-cost and fast estimate of the size of huge tables, which I have recently redone at a site where some of their applications are stubbornly stats-free. Restrictions : - Must be dictionary managed - May be more complicated and slower with partitioned tables. The idea is to heavily use dbms_rowid. First compute in how many blocks are, say, the first 2,000 rows. Then get the extent list in reverse order, and try to identify which is the last block to contain rows. Easy to do with a binary search, by building (dbms_rowid) the rowid of the first row in each block. Especially after a CTAS, you are sure to have a row #1. If no row at all is found, skip to the next (ie previous) extent. I have always found estimates obtained in this way pretty close to reality, and often better than ANALYZE ... ESTIMATE STATISTICS. In under one second. In your particular case, I also believe that you may find something in V$SQL - perhaps the SELECT * on the source database. You should get the number of rows processed here. HTH, SF Arup Nanda wrote: Dennis, Thanks. Sorry for not being explicit about it. Since the table created is huge, I want to avoid the count(*) if I can get the number in some other way. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 3:44 PM Arup select count(*) from table? What is your goal? Corruption detection? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 07, 2003 2:34 PM To: Multiple recipients of list ORACLE-L List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: full recovery
Btw, if you configure controlfile autobackup on, the controlfile is automatically backed up during any physical database structure change, e.g. adding a datafile, etc. The file is backed up to $ORACLE_HOME/dbs or $OH/database (depending on platfoem) by default. You just be monitoring these directories in case you're making physical changes often in your database. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 9:59 PM I believe that an 'alter database backup controlfile to trace' loses the RMAN data stored in the control files if you're not using a repository (if I remember right from RF's book). Since we're not using a repository, we've got controlfile autobackup on (in 9i use 'configure controlfile autobackup on'). We dump these disk copies via filesystem backups as a safety measure. We also do a backup database, archivelogs, and then control (but all in one step; I'm not sure I'm clear on the reason for separating them into three backup sets) Rich -- Rich Holland(913) 645-1950SAP Technical Consultant print unpack(u,92G5S\=\!A;F]T:5R(\'!EFP\@:%C:V5R\[EMAIL PROTECTED]); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mercadante, Thomas F Sent: Friday, October 24, 2003 2:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: full recovery AK, First, your normal backup should backup your database in the following order: Database, archivelogs and then control file. I actually do this in three separate Rman steps. This is so that the most recent control file is backups up after all of the data. This allows your to perform an incoimplete-recovery-restore to as late a time as possible. Also, consider adding a database trigger that, upon startup, perform an alter database backup controlfile to trace; Keep a copy of this trace file someplace safe as a sanity check. You could use it to recreate your controlfiles if all else fails. Your recovery steps are as follows: 1. restore oracle software from tape. 2. restore config files ( init.ora , listener. ora ). 3. startup instance with nomount. 4. run Rman to restore the control file from tape. 5. Alter database mount 6. run Rman to restore database files 7. alter database open resetlogs. 8. perform a brand-new Rman backup (database, logs controlfile) turn the system back to the users (with many back-pats from management). You should be testing this on a regular basis. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, October 24, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Here is a scenerio : I am taking full database backup everynight using rman to tape . which includes archive logs and control file. Not using catalog.Also have a backup of complete file system including oracle software and configuration files ( init.ora , listener.ora etc.. ) I lost the host on a particular day at 12 am afternoon. Now I want to restore this db to latest possible time to another host ( with same name ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rich Holland INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Change dedicated server to shared server
Well all, I finally checked out what I stated below this weekend, and, unfortunately, the new 9i init.ora parameters are not there in the text. :-( (New edition maybe, Jonathan? :-) ) *However*, the book is still outstanding in terms of setting up MTS initially, AND troubleshooting, AND checking out the various MTS dynamic performance views while testing. All you have to do is swap the 9i init.ora parameters from the 9i documentation set with the ones listed in the text (if you're using 9i) which is really no biggy. Melanie --- Melanie Caffrey [EMAIL PROTECTED] wrote: Hi Stephen, Check out the Shared Server/MTS chapter. The last few pages of this chapter should introduce the new 9i init.ora parameters. And, I agree. I like this book very much. Melanie --- Stephen Andert [EMAIL PROTECTED] wrote: Well, I just paged through the front part and it says nothing about 9i coverage. I don't use MTS, so I'm not a good judge on what it covers that may apply to 9i or not. The rest of the book I can judge and find it (IMHO) to be a very good book on Oracle Network communication, both with good clear overviews as well as detailed references for troubleshooting and configuration. Stephen [EMAIL PROTECTED] 11/07/03 09:19AM The actual titile is Oracle Net8: Configuration and Troubleshooting, but I remember a good start to 9i's Oracle Net features being included as well. Melanie -Original Message- Sent: Friday, November 07, 2003 10:58 AM To: '[EMAIL PROTECTED]' If you purchase Oracle Networking (something or other) :-) by Jonathan Gennick and Hugo Toledo, this should give you an excellent start. I can't recall, off the top of my head if the new MTS 9i init.ora parameters are included in this text. I believe that the latest edition of this book does include them, though ... HTH, Melanie -Original Message- Mauricio Vilez Sent: Friday, November 07, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Hi Can somebody tell me the steps for changing my server configuration from dedicated server to shared server? Thanks _ Do you Yahoo!? Protect http://antispam.yahoo.com/whatsnewfree your identity with Yahoo! Mail AddressGuard ** This email is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this e-mail message is not the intended recipient, or the employee or agent responsible for delivery of the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is prohibited. If you have received this e-mail in error, please notify us immediately by telephone at (212) 686-6004 and also indicate the sender's name. Thank You www.proximo.com [EMAIL PROTECTED] * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L
Re: Wow, Man, Flashbacks!
Hi! It was only the SCN - time mapping informatin which is aged out after 5 days, but you still can use SCN-based flashback back to your oldest undo record. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:04 PM Others have mentioned it, but I just wanted to chime in to warn you to warn your developers, that they will only have five days to use their frozen moment in time. This is the limitation imposed by the scn table mentioned in Dan's post. The reason I decided to chime in is that AFAIK, no official Oracle documentation refers to this limitation. This is just one of those odd things I wouldn't know, if it weren't for my association to this list. -Original Message- Bellow, Bambi Sent: Friday, November 07, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Melanie -- What's happening is that, in a write-intensive environment, the developers want to freeze a moment in time which can be used across developers and applications for testing to ensure consistent results. The functionality may be expanded, in time, depending on how it works. But from what I'm seeing, this seems to be the right tool for the job. Thanks, everyone, for your feedback. In my book, this goes down as Oracle's coolest feature since DECODE. Bambi. -Original Message- Sent: Thursday, November 06, 2003 11:45 AM To: Multiple recipients of list ORACLE-L Hi Bambi, I hate to sound repetitive, but, it depends. :-) It depends on how far you want to flash back to. One of my clients was being audited last year by their parent company. I put in a retention period of about a week at a time. Without divulging much from my confidentiality agreement with them, we were really trying to figure out what certain users were doing within the company, and we provided proof of such. Using the flashback query feature made this incredibly easy to do! Of course, you have to be careful with your UNDO segments, as you know. I didn't try going backwards for more than a week. That was all we needed at the time. I also turned this feature off once we were done. Other than UNDO segment growth, which I planned for in advance, I didn't have any problems with it. And it made the task at hand incredibly easy. But, I wouldn't recommend putting it in place unless you really need it. My $0.02, Melanie --- Bellow, Bambi [EMAIL PROTECTED] wrote: Hi Folks! I got waylaid(?) when I got in this morning and these user dudes were talking about using flashbacks, and I was like Woah! Dudes! Let's just chill on this for a couple of minutes. So, I went back to my desk and checked out a cool article on it... http://www.oracle-base.com/Articles/9i/FlashbackQuery.asp And it looks like it's like, killer, yknow? So, I was thinking well, we've got sufficiently large undo segments for this, I don't see a problem with it but that didn't sound terribly cool, and anyway, before I go willy-nilly saying this is the niftiest thing Oracle's done in years, I thought I would run it by you guys and see if anybody out there is running with flashbacks and whether there have been any problems with it. Yer far-out pal, Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Re: Wow, Man, Flashbacks!
Jonathan Lewis has found out that even 8i has flashback capabilities internally http://www.jlcomp.demon.co.uk/ch_01.html Btw, in 10g there is a guaranteed retention option as well, that undo_retention time is forced instead of attempted. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 8:39 PM Actually, flashback is available regardless of the undo management configuration. Flashback requires use of the smon_scn_time table, which is populated with an scn - time relationship every five minutes the instance is up. The table is restricted to a certain size (14400 entries IIRC) by the update logic, so the 5 days of flashback time is a result of the old records being 'aged' off the table. Using AUM increases the likelihood that a flashback query will succeed, but it is not a guarantee. The logic of block reuse in AUM should increase the retention of undo. Daniel Fink Melanie Caffrey wrote: Yes, this is correct. Rollback segments cannot be used. --- Stephen Andert [EMAIL PROTECTED] wrote: Bambi, Are you using automatic undo management or manual? I think that flashback can only be used with auto. Stephen [EMAIL PROTECTED] 11/06/03 10:04AM Hi Folks! I got waylaid(?) when I got in this morning and these user dudes were talking about using flashbacks, and I was like Woah! Dudes! Let's just chill on this for a couple of minutes. So, I went back to my desk and checked out a cool article on it... http://www.oracle-base.com/Articles/9i/FlashbackQuery.asp And it looks like it's like, killer, yknow? So, I was thinking well, we've got sufficiently large undo segments for this, I don't see a problem with it but that didn't sound terribly cool, and anyway, before I go willy-nilly saying this is the niftiest thing Oracle's done in years, I thought I would run it by you guys and see if anybody out there is running with flashbacks and whether there have been any problems with it. Yer far-out pal, Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
Re: Trapping Portal Login
Title: Message
These should be Portal-specific packages which
return it's internal usernames, I don't remeber any names from heart, but you
always canuse 10046 traceat level 4 to get information about which
functions/procedures are executed during authentication.
Tanel.
- Original Message -
From:
Chris
Stephens
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, November 05, 2003 5:09
PM
Subject: Trapping Portal Login
I am writing a procedure to generate a
company calendar and to allow for scheduling various resources in the
organization. This will run as a portlet and I need to verify that the
user has been authenticated through portal before I allow them to
add/edit/delete entries. So far I am unable to figure out how to get the
portal userid. ...I tried owa_util.get_cgi_env('REMOTE_USER') and
Sys_Context but they both just return the userid
from the DAD. Anyone know how to do this?
...ehem...I haven't
spent a whole lot of time trying to figure this out but I thought I would pose
the question in hopes of a quick and easy response.
Thanks
Chris
Re: Howto drop a corrupt database ?
How will that drop corrupt datafiles? Did you already try that? Carel-Jan At 13:19 9-11-03 -0800, you wrote: Just for the record, in 10g you actually can use drop database command ;) Tanel. - Original Message - From: Carel-Jan Engel To: Multiple recipients of list ORACLE-L Sent: Sunday, November 09, 2003 3:24 PM Subject: Re: Howto drop a corrupt database ? Hi Gorik, Do you want to drop the database as a whole, i.e. including your system tablespace etc? Just delete all your controlfiles, datafiles and redologfiles, keep the initSID.ora and start creating the database all over again. (You do have a create-script, don't you?). Of course you can execute the create database command using the REUSE clause for all files, but I hate including this in a script. When someone, it might be even be you, inadvertently re-executes the script all your files might be gone. Not using the REUSE clause will simply cause the script to fail. Regards, Carel-Jan At 04:09 9-11-03 -0800, you wrote: Hi, I'm running Oracle 8.0.4 on AIX: for one of the databases, my datafiles got corrupted and I got NO BACKUP (and NOARCHIVELOG) :( SVRMGR alter database open; ORA-01122: database file 6 failed verification check ORA-01110: data file 6: '/data4/test/testdb01.dbf' ORA-01251: Unknown File Header Version read for file number 6 no problem I'm willing to restart, so I want to drop the whole database and start all over again (using the same name). How can I drop this database ??? When I try to do this from svrmgrl, I get: ORA-01109: database not open, duh ! Any ideas how to proceed ? Kind regards, Gorik DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED] DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
Migration
Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? 3.. It is possible to apply the mentioned technique or some other (witch one?) in uptime, totally or partially? 4.. Witches are the main tasks to consider in a planning schedule? 5.. Witches are the time frames associated to these tasks? Thanks Arménio Teles -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: A. Teles INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: How do you genrate primary keys?
Hi! Well, if the commit is not too frequent, one natural way of generating primary key would be select max(last_change#) from v$datafile; No, you definitely don't want to do that!!! v$datafile uses x$kccf% tables which cause several physical reads into controlfiles and x$kcvfh which shows datafile header information for every datafile in your database, causing one additional physical IO per datafile! None of those IOs are cached by Oracle. So, if you used the v$datafile approach, you'd be getting number_of datafiles + about 10 physical IOs for single PK value generation! Yong already commented on the other issues with v$datafile usage. Tanel. aware, this is the natural mechanism that ensures that any change is properly enumerated and, thus, the best and most generic primary key. I understand that someone might doubt this mechanism as I would never even dream of using it, but SCN is the thing that comes naturally. Alternatively, one could produce SCN from V$TRANSACTION (base + wrap). On 11/06/2003 12:54:38 PM, Cary Millsap wrote: The implementations I've seen all did SELECT...FOR UPDATE. Works. Doesn't scale. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Jonathan Gennick Sent: Thursday, November 06, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale ([EMAIL PROTECTED]) wrote: HKC 1. Hit a table that keeps a counter. HKC Used to be a mechanism in the Oracle5 days [If I remember correctly, HKC Sequences came in Oracle6]. Issues were with locking the single HKC record used as the generator or scanning for the max(value) of the HKC key. HKC Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user modegrin. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain
Re: Point-In-Time recovery question, Non-RMAN solution
I think it is possible to enable DDL command logging in 9i, it can be used in logical standby. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 6:09 PM You guessed and hoped you were close enough. If you were wrong, you repeated the exercise until you found the point in time before the drop I'm not sure logminer will show you the drop table in any case. At least not explicitly as drop table is NOT a logged operation. You might see the effect of it on fet$ and uet$ in that extents would be released back to the tablespace but if you have locally managed tablespaces you'd have to search for the update to tab$ to find the time. --- [EMAIL PROTECTED] wrote: How was the timestamp derived prior to logminer as Point-In-Time recovery has been around a long time? Thanks Rick Scott Canaan [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Point-In-Time recovery question, Non-RMAN solution .com 11/06/2003 10:09 AM Please respond to ORACLE-L Have you looked into using logminer? Even if it can't restore your table, it can give you the exact time that it was dropped. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- Sent: Thursday, November 06, 2003 8:45 AM To: Multiple recipients of list ORACLE-L Hi DBAs, Oracle 8i, ArchiveLog, No RMAN Testing Point-In-Time Recovery I am confused on what time to substitute in the RECOVER DATABASE UNTIL TIME 'timestamp'; For example 2 days ago 11/04/2003 approximately 17:00 I drop a table. Today I decide I want that table back. I want to do an incomplete recovery to get the table back. How do I know what timestamp to use? I have an idea the I dropped the table but not exact. 1. SHUTDOWN Normal 2. BACKUP current database 3. Restore datafile that has the table in it. 4. connect internal 5. startup mount 6. recover database until time 'timestamp??'; 7. Alter database open resetlogs; 8. BACKUP current database Step 5 is my confusion. Also I assume all data is now lost since last archive restored to the present. The only way I know to get that data back is to 1. Export the table that was dropped. 2. Restore database from step2 3. Import table from step1 Is there better ways. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: Migration
A. Teles wrote: Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? No. Would take ages. 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? With imp? Could really be weeks. 3.. It is possible to apply the mentioned technique or some other (witch one?) in uptime, totally or partially? You must try to do as many things as you can in parallel, running many processes at once. If you can safely take all the network bandwidth without being fired, SQL*Plus COPY on a table-per-table basis. Which means that you have, prior to that, recreated tablespaces, users, etc. Other than SQL*Plus COPY (or CTAS across a dblink), you can download to flat files (there are tools for that), ftp, SQL*Loader direct, which assumes you already have recreated empty tables (exp ROWS=N CONSTRAINTS=N can help). 4.. Witches are the main tasks to consider in a planning schedule? The great thing is that you need not backup your database. But you must be able to switch back quickly to the old system if anything goes wrong. Be careful with your scripts (NOVALIDATE when reenabling constraints, for instance). Test a lot beforehand. Otherwise it really depends on your operational constraints. For a hospital you can't afford be offline for a long time. 5.. Witches are the time frames associated to these tasks? Can really vary. Order of magnitude is likely to be several hours in the best of cases IMHO. Also, it also depends on how much of your data is really active. You may consider quietly moving the 'dead' or archived part of it, and switching later the really active, much smaller set. Logging activity is also something which can be cntemplated for really difficult cases. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[3]: How do you generate primary keys?
I think this is a high-level design and even analysis issue in many cases. If no gaps are allowed, you probably should allocate a key value for any record just before committing the transaction (commiting has wider meaning here, e.g. accepting or permanently storing). That way you won't get problems with rollbacks, but of course in some environments you have to know your keys ID before you commit or IDs should be allocated based on transaction start time etc. In these cases you have to do some kind of tradeoff between performance and key quality and these kind of decisions should be done as early as possible, in analysis/design stage. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 4:14 PM Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Migration
Hi List, Could someone please help me? Assumption situation - Platform migration of Oracle DW on Oracle DB (data volume 3.5 TB) from HP-UX to IBM-AIX 1.. DB migration; it is correct to use Export/Import technique/method in the above assumption? Use exp/imp only for transporting the database structure, using rows=n. Then use insert /*+ APPEND */ over database links for transporting data itself. Just copying over the files or using cross-platform transportable tablespace doesn't probably work even though HP IBM Unix servers both use Big Endian byte order. (Btw, in 10g you can use RMAN to convert tablespaces datafiles to any supported platform specific format :) 2.. Witch is the time frame in a worst case for this (how many hours, days or weeks!!)? If planned well and on proper hardware, I guess you can do it in one weekend. It is possible to reduce downtime even more, but in that case you have to take special measures, like logging DML, precopying read only data, rolling upgrades, etc.. The worst case can be weeks, but of course you should be doing a lot of migration testing (with full dataset if possible), to be sure in your downtime requirement first. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Howto drop a corrupt database ?
I've never tried (actually I'm not even in beta program currently), but it works only on exclusively mounted and not open database, and with restricted session instance mode. During drop, controlfiles are scanned for datafile locations and the datafiles found from there are deleted from OS. So, if the filesare deletable in principle (on RW media and with correct permissions) the drop will succeed, how corrupt the data inside a datafile is, doesn't matter at all. Drop database, will also delete redologs, controlfiles and spfile. Dont remember about passwordfile, here we might have an exception, because sometimes a single passwordfile is used for several instances in a server. Tanel. - Original Message - From: Carel-Jan Engel To: Multiple recipients of list ORACLE-L Sent: Sunday, November 09, 2003 11:59 PM Subject: Re: Howto drop a corrupt database ? How will that drop corrupt datafiles? Did you already try that?Carel-JanAt 13:19 9-11-03 -0800, you wrote: Just for the record, in 10g you actually can use drop database command ;)Tanel. - Original Message - From: Carel-Jan Engel To: Multiple recipients of list ORACLE-L Sent: Sunday, November 09, 2003 3:24 PM Subject: Re: Howto drop a corrupt database ? Hi Gorik, Do you want to drop the database as a whole, i.e. including your system tablespace etc? Just delete all your controlfiles, datafiles and redologfiles, keep the initSID.ora and start creating the database all over again. (You do have a create-script, don't you?). Of course you can execute the create database command using the REUSE clause for all files, but I hate including this in a script. When someone, it might be even be you, inadvertently re-executes the script all your files might be gone. Not using the REUSE clause will simply cause the script to fail. Regards, Carel-Jan At 04:09 9-11-03 -0800, you wrote: Hi, I'm running Oracle 8.0.4 on AIX: for one of the databases, my datafiles got corrupted and I got NO BACKUP (and NOARCHIVELOG) :( SVRMGR alter database open; ORA-01122: database file 6 failed verification check ORA-01110: data file 6: '/data4/test/testdb01.dbf' ORA-01251: Unknown File Header Version read for file number 6 no problem I'm willing to restart, so I want to drop the whole database and start all over again (using the same name). How can I drop this database ??? When I try to do this from "svrmgrl", I get: "ORA-01109: database not open", duh ! Any ideas how to proceed ? Kind regards, Gorik DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED] DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C2743 BX WaddinxveenThe Netherlandstel. +31 (0) 182 640 428fax +31 (0) 182 640 429mobile+31 (0) 653 911 950e-mail [EMAIL PROTECTED]
Re: PCTFREE and PCTUSED
Hi! I'm not entirely in agreement with the quote (that's a different story) but notice there is no mention of PCTFREE. Yes, this is a deficiency of documentation. PCTFREE is still used and needed in ASSM segments. As you said, value for it can not be tuned automatically, since Oracle doesn't have slighest idea about nature of future data (there's still no time machine in Oracle ;) ASSM is designed to automatically determine whether or not a block should be considered for inserts. It does this by using a sequence of bitmaps to describe the fullness of a block. There are different levels of fullness empty 0-25, 25-50, 50-75 and 75-*full*. However what does *full* actually mean or at what point does Oracle no longer consider the block suitable for inserts. Note that ASSM bitmaps track freeness not fullness, to be correct in terminology. Thus the freeness statuses for a table are full, 0-25% free, 25-50% free, 50-75% free, 75-100% free and unformatted as well. (it's probably more like 0-24% and 25-49% etc, but this is not so important) There are 4 freeness bits per block in ASSM table, but for LOBs and indexes there are less. For an index, for example, you only have to state whether a block is insertable or not, there's no need for multiple different freeness bits. Hope this makes some sense :) Cheers Richard You probably don't need this information but you can download my presentation about Freelists vs. ASSM internals, which I presented at OracleWorld Paris, from my homepage http://integrid.info Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 do you genrate primary keys?
As I understand, in case of cached sequences, SEQ$ is touched only when you run out of cached values in library cache and a new sequence range has to be allocated. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:39 PM That's it. If you didn't use the cache, then it would cause the same problem as with normal table-managed sequence numbers. But with cached sequence numbers, an application can get a sequence number without touching the database (SEQ$) at all. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Security : Denial Of Service
Hi List, A secure system makes data available to authorized users, without delay. Denial-of-service attacks are attempts to block authorized users ability to access and use the system when needed. By using user-profile one can lock DB users if he/she provides wrong password 3 times. Then DBA has to unlock the users to make it work. By knowing DB userid somebody can lock the DB users (by providing wrong password 3 times) so that when the actual user try to loing it will block him/her to access the db. How does oracle address this Denial Of Service ? Any response would be highly appreciated. Thanks -Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saminathan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 rewrite doesn't work if based on a regular view
Dear All, query rewrite doesn't work on materialized view if based on a regular view which contains joins and coorelated subquery. Got the the following message: QSM-01063: query has a dictionary table or view QSM-01019: no suitable materialized view found to rewrite this query. I create a MV based on definition of the view. Anybody has clue on this or Oracle has restrictions on this kind of MV? TIA Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Security : Denial Of Service
Saminathan, How dare you post such a message with that subject line. Here is my response, which you will not appreciate highly: read the fscking manual. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/secure.htm#2193 how does oracle address it? by having it as configurable by a clueful administrator. you have a long way to go, apparently, before you'd reach that category. btw - I won't be able to read your reply, as your address is now filtered by me. PaulSaminathan [EMAIL PROTECTED] wrote: Hi List,"A secure system makes data available to authorized users, without delay.Denial-of-service attacks are attempts to block authorized users’ ability to accessand use the system when needed."By using user-profile one can lock DB users if he/she provides wrong password 3 times. Then DBA has to unlock the users to make it work. By knowing DB userid somebody can lock the DB users (by providing wrong password 3 times)so that when the actual user try to loing it will block him/her to access the db.How does oracle address this "Denial Of Service" ?Any response would be highly appreciated.Thanks-Sami-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: SaminathanINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVEE 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!? Protect your identity with Yahoo! Mail AddressGuard
RE: Interesting PL/SQL Puzzle
I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Monitor Index Usage
Hi all, is there a way to monitor index usages in oracle 8i ,like 9i v$object_usage? Thanks Arvind Kumar
RE: pattern search
Hi Huang/Jared, This is exactly what I was looking for... Thanks ShivaM -Original Message- Sent: Friday, November 07, 2003 7:40 PM To: Multiple recipients of list ORACLE-L Naveen and Shiva, Please see my article at http://www.stormloader.com/yonghuang/computer/OracleRegExp.html for a summary of the usage of owa_pattern, a very little known package since probably Oracle 7.3. It also has a link to Tom Kyte, Mark Piermarini and Daniel Savarese's external Java approach, as well as Jonathan Gennick's article on 10g regular expressions. Yong Huang --- Naveen, Nahata (IE10) [EMAIL PROTECTED] wrote: Not until 10g Regards Naveen -Original Message- From: Shiva Maran [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: pattern search Hi All, I need a means to search for a pattern (With basic wildcard characters like %, _, ^, []). How do I do this in oracle. I also need to get back the string that matches the pattern. Is there any predefined function or procedure that does this. Would like to avoid implementing this on my own. TIA, ShivaM __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This e-mail contains proprietary information some or all of which may be legally privileged. It is for the intended recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the author by replying to this e-mail. If you are not the intended recipient, you must not use, save, disclose, distribute, copy, print or relay this e-mail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shiva Maran INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Interesting PL/SQL Puzzle
Is the problem still evident when the parameters are defined as in as opposed to in out. Cheers Connor --- Cary Millsap [EMAIL PROTECTED] wrote: I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But
How does Oracle determine the materialized view eligible for text
Hi, Anybody knows how Oracle determine the materialized view eligible for textmatch or general rewrite? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? Thx -Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Sat, 08 Nov 2003 22:09:25 -0800 get length of each of those you will see which one you need. length of #4 should be 0. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: Hi, If we have table with clob column and want to findout whcih clob column rows has been updated/inilialised thru empty_clob() functions? How to do that? Like table with 2 columns ID and testcolu .ID is desc test_table Name Null? Type - ID NUMBER(16) TESTCOLU CLOB Having rows like ID TESTCOLU -- 1 2 3 4 99 Out of these 5 rows id# 4 were inilialised thru EMPTY_CLOB() function.Wondering which sql statement would pickup only those rows having id value 4 . The ID 3 was inilialised thru NULL and 99 was with ' '. thanks in advance. -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Is your computer infected with a virus? Find out with a FREE computer virus scan from McAfee. Take the FreeScan now! http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
