RE: Experiences with Automatic Undo Management
I would like to be able to have multiple undo tspaces simply as an aid to IO balancing... Connor, you can... Oooo... You meant you want them active at the same TIME :-) They are saving that as a new feature for some later release I'm sure. It's probably on some marketing guys white board right now... Cheers! Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Perl - Was unix time conversion function
Title: unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my "learning Perl" book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary MillsapSent: Friday, January 24, 2003 4:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str("%T %A %d %B %Y", $t), "\n"; Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas- RMOUG Training Days 2003, Mar 56 Denver- Hotsos Clinic101, Mar 2628 London -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, EthanSent: Friday, January 24, 2003 3:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +"%Y" ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +"%j" ) MIN_DAYS=$( expr "${MIN_DAYS}" - 1 ) MIN_DAYS=$( expr "${MIN_DAYS}" \* 1440 ) MIN_HOURS=$( date +"%H" ) MIN_HOURS=$( expr "${MIN_HOURS}" \* 60 ) MIN_MINS=$( date +"%M" ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL}} -Original Message-From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: Friday, January 24, 2003 1:14 PMTo: Multiple recipients of list ORACLE-LSubject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
SQL Question
Hi, My brain is slow today Can someone help me ? I can do : select idu+1 from user_group_members where fk_user in(44541,41402,41813) ; IDU+1 -- 41411 41821 44546 But I can't do : select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; I've got on IDU+1 : ERROR at line 1: ORA-01722: invalid number Best Regards Henrik -- --- There's fun in being serious. -- Wynton Marsalis Henrik EkenbergAnoto AB -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: tim column in trace output
Hi We could always use the old programmer trick. nm oracle | grep time This will give os all external function with a name like time after some man / google work gethrtime() look like interesting. Assuming that gethrtime() is the correct function it will be random depending on the hardware platform / OS implementation if it it possible to map the tim= value NAME gethrtime, gethrvtime - get high resolution time SYNOPSIS #include sys/time.h hrtime_t gethrtime(void); hrtime_t gethrvtime(void); DESCRIPTION The gethrtime() function returns the current high-resolution real time. Time is expressed as nanoseconds since some arbitrary time in the past; it is not correlated in any way to the time of day, and thus is not subject to resetting or drifting by way of adjtime(2) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?adjtime+2 or settimeofday(3C) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?settimeofday+3. The hi- res timer is ideally suited to performance measurement tasks, where cheap, accurate interval timing is required. The gethrvtime() function returns the current high- resolution LWP virtual time, expressed as total nanoseconds of execution time. This function requires that micro state accounting be enabled with the ptime utility (see proc(1) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?proc+1). The gethrtime() and gethrvtime() functions both return an hrtime_t, which is a 64-bit (long long) signed integer. EXAMPLE The following code fragment measures the average cost of getpid(2) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?getpid+2: hrtime_t start, end; int i, iters = 100; start = gethrtime(); for (i = 0; i iters; i++) getpid(); end = gethrtime(); printf(Avg getpid() time = %lld nsec\n, (end - start) / iters); ATTRIBUTES See attributes(5) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?attributes+5 for descriptions of the following attri- butes: __ | ATTRIBUTE TYPE| ATTRIBUTE VALUE| |___|__|_ | MT-Level | MT-Safe| |___|_| C Library Functions gethrtime(3C) SEE ALSO proc(1) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?proc+1, adjtime(2) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?adjtime+2, gettimeofday(3C) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?gettimeofday+3, settimeofday(3C) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?settimeofday+3, attributes(5) http://mirrors.ccs.neu.edu/cgi-bin/unixhelp/man-cgi?attributes+5 NOTES Although the units of hi-res time are always the same (nanoseconds), the actual resolution is hardware dependent. Hi-res time is guaranteed to be monotonic (it won't go back- ward, it won't periodically wrap) and linear (it won't occa- sionally speed up or slow down for adjustment, like the time of day can), but not necessarily unique: two sufficiently proximate calls may return the same value. SunOS 5.6 Last change: 10 Apr 19971 nm oracle | grep time 0a1c8020 T CD_time 0a109cf0 T LdiDateIntertimeAdd 0a10c7c0 T LdiDateIntertimeSubtract 0a055a10 T Osecs2time 0a0558f0 T Otime2secs 09f5d570 T T_time U asctime@@GLIBC_2.0 5001a800 A atbdatetime_ U ctime@@GLIBC_2.0 U difftime@@GLIBC_2.0 0a130240 T gethrtime U gettimeofday@@GLIBC_2.0 U gmtime@@GLIBC_2.0 U gmtime_r@@GLIBC_2.0 09373ec0 T hoclctf_change_timestamp_format 0939dcf0 T hokpgt_get_timestamp 0939d770 T hokpst_set_timestamp 0937b240 T holebl_bind_timestamp_ltz 0937b3a0 T holebnl_bind_name_timestamp_ltz 0937ad00 T holebnt_bind_name_timestamp 0937b050 T holebnz_bind_name_timestamp_tz 0937aba0 T holebt_bind_timestamp 0937aef0 T holebz_bind_timestamp_tz 0937e900 T holedbl_define_bind_variable_timestamp_ltz 0937e200 T holedbt_define_bind_variable_timestamp 0937e580 T holedbz_define_bind_variable_timestamp_tz 0937ccf0 T holedl_define_variable_timestamp_ltz 0937cdc0 T holednl_define_variable_name_timestamp_ltz 0937c940 T holednt_define_variable_name_timestamp 0937cb80 T holednz_define_variable_name_timestamp_tz 0937c870 T holedt_define_variable_timestamp 0937cab0 T holedz_define_variable_timestamp_tz 09381420 T holegvl_get_value_timestamp_ltz 093811a0 T holegvt_get_value_timestamp 093812e0 T holegvz_get_value_timestamp_tz 0937ea70 T holeldbn_timestamp_ltz_define_bind_variable_name 0937e370 T holetdbn_timestamp_define_bind_variable_name 0937e6f0 T holezdbn_timestamp_tz_define_bind_variable_name 094000d0 T kdtimestamp 0964ba20 T kgavgpts_get_poll_time_suggestion 098189c0 T kgskbtime 09818900 T kgskcallesttime 09818a50 T kgsketime 09818fb0 T kgskqtime 0893c2e0 T kkpolci_load_compiletime_info 08a3ecb0 T
Re: Re: Experiences with Automatic Undo Management
the only time i've had the need for a regular shutdown was when there was that lovely old solaris problem where an os clock wrapped and put junk into the controlfile (and thus made a little bit of a mess of the database) so a db had to be bounced at least once every (I think) 248 days, but other than that, i view shutting the database as wasting money... I say money because your license fee's or part thereof goes into building trendy little things like buffer cache management, library cache management etc etc...when you shutdown, its like throwing away some of your license dollars there of course are some (i would contend rare) occasions when a regular shutdown policy can be justified (eg memory leak accumulation etc), but i try to avoid it at all costs if I can cheers connor --- chao_ping [EMAIL PROTECTED] wrote: Connor McDonald, Hi, for IO balance , I think in most case, we put datafiles on raid so it is not a problem? Or you can use multiple datafiles, which will give you solution when no raid or multiple raid is used and you want to balance io? I used AUM too in my rac system, but my system is most read only so did not see anything different , performance is pretty good. By the way, do friends in this list also suggest never shutdown database? I prefer to shutdown db/unix every several monthes, to give out a stable os enviroment. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-27 15:33:00 ,you wrote£º=== Maybe I'm just a cynic but I view AUM as being akin the SAME disk layout policy. That is, (and this is subjective figures) it gives 90 optimal performance in about 90 of databases out there. Since going to aum on a significant (read: reasonable number of users and workload) database I used to manage in the UK, I found aum gave ever so slightly increased undo figures in v$waitstat, but not really enough to discount it. Similarly, file IO was slight increased (which I have put down to its fairly aggressive nature of enforcing retention time). One thing that is good about aum, is that it encourages people to never shut their databases (a policy I advocate strongly). I would like to be able to have multiple undo tspaces simply as an aid to IO balancing Cheers Connor --- Fink, Dan [EMAIL PROTECTED] wrote: This is more of a survey than a question about problems. For those of you using automatic undo management, what have been your experiences, both good and bad. Are you also using flashback query and what are your experiences? Thank you for all the responses, Dan Fink = 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 TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts
RE: SQL Question
Try select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU + 1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813); -Original Message-From: Henrik Ekenberg [EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 28, 2003 12:14 PMTo: Multiple recipients of list ORACLE-LSubject: SQL QuestionHi,My brain is slow today Can someone help me ?I can do :select idu+1 from user_group_members where fk_userin(44541,41402,41813) ; IDU+1-- 41411 41821 44546But I can't do :select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_userswhere pen_id in (44541,41402,41813) ;I've got on IDU+1 :ERROR at line 1:ORA-01722: invalid numberBest RegardsHenrik-There's fun in being serious.-- Wynton MarsalisHenrik Ekenberg Anoto AB--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Henrik Ekenber INET: [EMAIL PROTECTED]" [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).DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
SV: SQL Question
You might want to try using () around the idu+1 part! like: select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU + 1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; That should do it! Best Regards Johan -Ursprungligt meddelande- Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg [EMAIL PROTECTED] Skickat: den 28 januari 2003 07:44 Till: Multiple recipients of list ORACLE-L Amne: SQL Question Hi, My brain is slow today Can someone help me ? I can do : select idu+1 from user_group_members where fk_user in(44541,41402,41813) ; IDU+1 -- 41411 41821 44546 But I can't do : select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; I've got on IDU+1 : ERROR at line 1: ORA-01722: invalid number Best Regards Henrik -- -- - There's fun in being serious. -- Wynton Marsalis Henrik EkenbergAnoto AB -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Johan Malmberg INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 9iAS hardware requirement
Title: Message hi, here are more info for our actual config. Server for 9iAS : OS=win2000 db=Oracle9i Each db is on a separate server. App 1 : 8 users Database server 1 Oracle 9i / unix NCR App 2 : 3 users Database server 2 Oracle9i / NT App 3 : 87 users Database server3 Oracle9i / NT App 4 : 20 users Database server4 Oracle9i / NT multisite network configuration with optique fiber. We want to useOEM for monitoring purpose. thanks, - Original Message - From: Jeremy Pulcifer To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 5:19 PM Subject: RE: 9iAS hardware requirement Nobody can. As with just about anything, it will depend on what size the app will be, what the access pattern will be, and how available it has to be. -Original Message-From: Amos KABORE [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 25, 2003 9:04 AMTo: Multiple recipients of list ORACLE-LSubject: 9iAS hardware requirement Can somebody tells me the MUST hardware requirement forOracle 9iAS installation. We are planning to useWin 2000 thanks
RE: SQL Question
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU + 1 )||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; IDU + 1 must be replaced by (IDU + 1). HTH. Nirmal., -Original Message- Sent: Tuesday, January 28, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Hi, My brain is slow today Can someone help me ? I can do : select idu+1 from user_group_members where fk_user in(44541,41402,41813) ; IDU+1 -- 41411 41821 44546 But I can't do : select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; I've got on IDU+1 : ERROR at line 1: ORA-01722: invalid number Best Regards Henrik -- --- There's fun in being serious. -- Wynton Marsalis Henrik EkenbergAnoto AB -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Question
try this ((IDU + 1)): select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values (' || (IDU + 1) ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; HEheac Hi, HEheac My brain is slow today Can someone help me ? HEheac I can do : HEheac select idu+1 from user_group_members where fk_user HEheac in(44541,41402,41813) ; HEheac IDU+1 HEheac -- HEheac 41411 HEheac 41821 HEheac 44546 HEheac But I can't do : HEheac select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users HEheac where pen_id in (44541,41402,41813) ; HEheac I've got on IDU+1 : HEheac ERROR at line 1: HEheac ORA-01722: invalid number HEheac Best Regards HEheac Henrik HEheac -- HEheac --- HEheac There's fun in being serious. HEheac -- Wynton Marsalis HEheac Henrik EkenbergAnoto AB HEheac -- HEheac Please see the official ORACLE-L FAQ: http://www.orafaq.net Best regards, Dmitrii [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dmitrii CRETU INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Slightly OT: Development Vs. Production DBA
Hi Listers, I would like to know the differences between Development and Production DBA w.r.t. Roles and Responsibilities, Scope etc. Is there any difference in the role(s) played by a DBA in OLTP and DSS environments? Your invaluable viewpoints in this regard is most welcome. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Case of the Missing Rows
Whoops! Sorry about that... ...grumble...over-40 eyes.over-40 brain..grumble...mutter - Original Message - To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED] Sent: Monday, January 27, 2003 11:27 PM If that were the case, he would not have seen 88640 rows from sqlplus, as he reported. Jared On Monday 27 January 2003 16:43, Tim Gorman wrote: Case of the Missing RowsMy guess is that SQL*Loader didn't really load 88,640 rows, but rejected or discarded about 1400 of them? - Original Message - From: Weiss, Rick To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 4:53 PM Subject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Title: unix time conversion function I think its like almost any subjective idea: its beautiful if you love it, heinous if you hate it. I love Perl; it does what I mean. The only thing I really dont like about Perl 5 is its yucky way of supporting complex data structures. Im eager to have some spare time someday to study the Perl 6 spec in more detail. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - RMOUG Training Days 2003, Mar 56 Denver - Hotsos Clinic101, Mar 2628 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Robert Freeman Sent: Tuesday, January 28, 2003 12:40 AM To: Multiple recipients of list ORACLE-L Subject: Perl - Was unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - RMOUG Training Days 2003, Mar 56 Denver - Hotsos Clinic101, Mar 2628 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
Writing efficient SQL queries
Hi Listers, Are there any websites and/or documents on Writing Efficient SQL queries? I know there are numerous tips in Guy Harrison's book. But I need some kind of Power point presentation or PDF document for the same. Can anybody point me some good URLs and/or douments please? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Question
The first query says where FK_USER in (44541,41402,41813) and the second query says where PEN_ID in (44541,41402,41813)... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 27, 2003 11:43 PM Hi, My brain is slow today Can someone help me ? I can do : select idu+1 from user_group_members where fk_user in(44541,41402,41813) ; IDU+1 -- 41411 41821 44546 But I can't do : select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; I've got on IDU+1 : ERROR at line 1: ORA-01722: invalid number Best Regards Henrik -- -- - There's fun in being serious. -- Wynton Marsalis Henrik EkenbergAnoto AB -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl - Was unix time conversion function
I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Follow-up: It's NOT possible to set role in db's logon trigger
Roy, I missed the first run of the question, so you've probably had this answer already. You can set a role inside a procedure if the procedure is declared with invoker rights (authid current_user) although the procedure cannot then be run in a logon trigger. However, in Oracle 9, you could define the role as an application role protected by a package. If COTS attaches to the database using OCI, then you could consider using the PROXY_USER features. In this case, COTS connects as itself, then becomes the end-user, without knowing the end-user password. Your package could then set the role based on the fact that sys_context('userenv','proxy_user') was 'COTS'. When the user logs in normally, their 'proxy_user' value will be null. The only way that they could switch on the role would be to write their own OCI program that logged on as COTS first - which means they'd have to know the COTS password anyway, so your data would have been unprotected anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 27 January 2003 23:48 trigger In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p _datab ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Personally... I think that if I had the same teacher to help me through Perl that REALLY taught me what C was doing in memory and got me to REALLY understand the power and elegance of pointers, I would code more in Perl. I can, and occasionally do, code in Perl, but nothing fancy or real creative. ajw April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein -Original Message- Sent: Tuesday, January 28, 2003 4:59 AM To: Multiple recipients of list ORACLE-L I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone
Re: SV: SQL Question
Thanks that solved the problem Regards Henrik -- --- Henrik EkenbergAnoto AB On Tue, 28 Jan 2003, Johan Malmberg wrote: -!-You might want to try using () around the idu+1 part! -!- -!-like: -!- -!-select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) -!-values ('||(IDU + 1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' -!-from app_users -!-where pen_id in (44541,41402,41813) ; -!- -!-That should do it! -!- -!-Best Regards -!-Johan -!- -!- -!- -!- -Ursprungligt meddelande- -!- Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg -!- [EMAIL PROTECTED] -!- Skickat: den 28 januari 2003 07:44 -!- Till: Multiple recipients of list ORACLE-L -!- Amne: SQL Question -!- -!- -!- Hi, -!- -!- My brain is slow today Can someone help me ? -!- -!- I can do : -!- -!- select idu+1 from user_group_members where fk_user -!- in(44541,41402,41813) ; -!- -!- IDU+1 -!- -- -!- 41411 -!- 41821 -!- 44546 -!- -!- But I can't do : -!- select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) -!- values ('||IDU + 1 -!- ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users -!- where pen_id in (44541,41402,41813) ; -!- -!- I've got on IDU+1 : -!- -!- ERROR at line 1: -!- ORA-01722: invalid number -!- -!- -!- Best Regards -!- Henrik -!- -!- -- -!- -- -!- - -!- There's fun in being serious. -!- -!- -- Wynton Marsalis -!- -!- Henrik EkenbergAnoto AB -!- -!- -!- -- -!- Please see the official ORACLE-L FAQ: http://www.orafaq.net -!- -- -!- Author: Henrik Ekenber -!- INET: [EMAIL PROTECTED] [EMAIL PROTECTED] -!- -!- Fat City Network Services-- 858-538-5051 http://www.fatcity.com -!- San Diego, California-- Mailing list and web hosting services -!- - -!- To REMOVE yourself from this mailing list, send an E-Mail message -!- to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in -!- the message BODY, include a line containing: UNSUB ORACLE-L -!- (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: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SQL Question
The first query also says 'from user_group_members' and the second one 'from app_users' ... I am not sure that the comparison is anything but confusing ... Looks like the implicitly converted varchar2() column which contains '***', 'N/A' or the like ... The first query says where FK_USER in (44541,41402,41813) and the second query says where PEN_ID in (44541,41402,41813)... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 27, 2003 11:43 PM Hi, My brain is slow today Can someone help me ? I can do : select idu+1 from user_group_members where fk_user in(44541,41402,41813) ; IDU+1 -- 41411 41821 44546 But I can't do : select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users where pen_id in (44541,41402,41813) ; I've got on IDU+1 : ERROR at line 1: ORA-01722: invalid number Best Regards Henrik -- - There's fun in being serious. -- Wynton Marsalis Henrik Ekenberg Anoto AB -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- --- --- -- --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle doesn't start up with domain user
Hi, I installed Oracle 9i under Windows 2000 professional with a domain user who has admin rights. After installing I couldn't use it: - If I log in with Administrator/Local Computer then the message of Net Manager (when testing the service) I got the error message Oracle not available After that if I start Oracle Database Configuration Assistant then modify database, go ahead with next, next etc. and finish it then the Oracle becomes available in Net Manager. After restarting the computer the same problem occurs. -- If I log in with Domain user /Domain then the message of Net Manager (when testing the service) I got the error message Initializing first test to use userid: scott, password: tiger Attempting to connect using userid: scott The test did not succeed. ORA-03113: end-of-file on communication channel After that if I start Oracle Database Configuration Assistant to modify database the error message is: ORA-01041: internal error.hostdef extension doesn't exist Both the Administrator and the Domain User are member of : administrators, oradba. I checked the services, they seem to start. Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Slightly OT: Development Vs. Production DBA
A development DBA is a developer who wants to design the schemas his/her application will rely on. I prefer calling them application designers, because that's what they are. Sometimes you have another role, that of Application Administrator. This second group is for larger applications that sometimes require constant attention, esp. if user accounts have to be created, or custom views etc. ... or if the application wasn't ready for production and was placed into production anyway -- then it will require constant babysitting. Consultants come in usually to implement new projects, or to add features to an existing system. That makes them application designers or application developers. Sometimes (rarely) consultants are hired to tune systems, that would be a blend of DBA and application designer. This is rare though, usually the database layer is working properly it seems to me, if the DBA has been there for more than a year, has read a book or two, and has at least the echo of a conscience. A production DBA is responsible for ensuring that the structure beneath the application stays up and is tuned properly. He/she works with the system administrator(s) to ensure that the hardware and the Oracle software (rdbms, developer server, iAS, networking,...) are all working properly and as expected. I don't fully understand why developers (some developers) strive to be called a DBA. Here is my guess: Perhaps this distinction stays fuzzy in organizations because there is a constant tug-of-war for control over resources between the development and production groups. If an overlap can be created, then there is an opportunity to take over some of the other group's resources. Also, when responsibilities are not delineated clearly, there is an opportunity for one side to blame the other and management can never figure out who is doing what. I worked in a lab where we were implementing Good Laboratory Practice (GLP) for the Food and Drug Administration (FDA), there was supposed to be no overlap between positions. I noticed that the managers who played games and only thought about their own advancement didn't like GLP at all, they fought it tooth and nail. I liked the idea of separate each person's circle of responsibility myself. Why can't IT shops strive to do the same? Speaking as a DBA, it is my perception that developers tend to be project-oriented. That's fine, it's why they are there. But that tendency also means, when they see their deadlines coming, that they sometimes aren't keen on thinking long term. Perhaps it's not their fault, it's because of the way projects are funded. Which client wants to hear that for every project, money will have to be allocated for ongoing costs of maintenance, operation, upgrades every 2-3 years? No one wants to think about that when they only want to think about the great new things they will be able to do with the new application. Also, no one wants to spend more money than necessary, so there is a tendency to try to cut corners to get to the end of the project. That is probably why projects tend to be rushed into production. Once the projects are in production mode funding to finish the product dries up. That sometimes leaves the application designer off the hook and leaves the production DBA holding the bag. Finally, if you are designing a new project, the tendency is to try to retain control over as much of it as possible. If you declare yourself to be a development DBA, then people are less likely to insist that you consult the DBA(s) during the design phase of a project. What a bother that is, having to listen to other people -- it's my project! It will only slow us down... Worse, I will have to share the credit once the application works properly. That won't be as good for my career. If you know that the DBA in the organization is stubborn and intractable, then this is the route the application designers will try to take. I could draw up a list of things that can go wrong when DBAs are not involved in the design phase of a project, but I think all people need to do is brainstorm for ten minutes to get a list of 10 or more things that can go wrong... Then try to put a cost value to each of these items. Can you think of any examples from your work place? ; ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) [this is my opinion, not the opinion of my employer... etc. etc.] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 4:29 AM To: Multiple recipients of list ORACLE-L Hi Listers, I would like to know the differences between Development and Production DBA w.r.t. Roles and Responsibilities, Scope etc. Is there any difference in the role(s) played by a DBA in OLTP and DSS environments? Your invaluable viewpoints in this regard is most welcome. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended
RE: Oracle doesn't start up with domain user
Check the file and folder permissions of installation files as well as database files. Give full permissions to local administrator on all the files (from root level) , things should work fine after that. HTH, Rajesh -Original Message- Csillag Sent: Tuesday, January 28, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Hi, I installed Oracle 9i under Windows 2000 professional with a domain user who has admin rights. After installing I couldn't use it: - If I log in with Administrator/Local Computer then the message of Net Manager (when testing the service) I got the error message Oracle not available After that if I start Oracle Database Configuration Assistant then modify database, go ahead with next, next etc. and finish it then the Oracle becomes available in Net Manager. After restarting the computer the same problem occurs. -- If I log in with Domain user /Domain then the message of Net Manager (when testing the service) I got the error message Initializing first test to use userid: scott, password: tiger Attempting to connect using userid: scott The test did not succeed. ORA-03113: end-of-file on communication channel After that if I start Oracle Database Configuration Assistant to modify database the error message is: ORA-01041: internal error.hostdef extension doesn't exist Both the Administrator and the Domain User are member of : administrators, oradba. I checked the services, they seem to start. Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rajesh Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was Unix time conversion function
Title: RE: Perl - Was Unix time conversion function I used to be, but I finally bit the bullet ... I ordered my copy of Jared's book from Amazon.com yesterday !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Robert Freeman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Subject: Perl - Was Unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Case of the Missing Rows
Rick, Might there be a Scheduled Task or DBMS_Job that runs shortly after the load and deletes rows? Just a thought - such automagic processes have bitten me in the past. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Weiss, Rick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L us [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Case of the Missing Rows 01/27/2003 05:53 PM Please respond to ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss -- 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: Perl - Was unix time conversion function
Perl is great for getting the job done fast. It's harder to write a large maintainable system in perl than other languages but you use the right tool for the job. And, IMO, if you are ever writing fancy or real creative code rewrite it. K.I.S.S. On Tue, 28 Jan 2003, April Wells wrote: Personally... I think that if I had the same teacher to help me through Perl that REALLY taught me what C was doing in memory and got me to REALLY understand the power and elegance of pointers, I would code more in Perl. I can, and occasionally do, code in Perl, but nothing fancy or real creative. ajw April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein -Original Message- Sent: Tuesday, January 28, 2003 4:59 AM To: Multiple recipients of list ORACLE-L I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
Re: Perl - Was unix time conversion function
The only problem I had was differentiating between the actual code and the encrypted version of the same ;) But we will get there eventually. Denny Quoting Robert Freeman [EMAIL PROTECTED]: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Slightly OT: Development Vs. Production DBA
It's not quite so hard and fast I'm considered a development DBA here. I design the schemas, the database layouts, the initialization parameters that are set etc I work with a hosting company to manage the staging and production databases. I create scripts for ALL changes to any of these environments. Because we use a hosting company for our production data center, I'm also involved in creating and maintaining runbooks. I control all emergency fixes to the databases, no matter what the environment. I handle data problems (yea, I know, I don't WANT to but for the ecommerce website, sometimes we can't wait for the corrections to flow through the system) I work with all developers, helping to tune their code. I monitor and help tune the production databases. I manage the move of databases from one data center to another. --- Boivin, Patrice J [EMAIL PROTECTED] wrote: A development DBA is a developer who wants to design the schemas his/her application will rely on. I prefer calling them application designers, because that's what they are. Sometimes you have another role, that of Application Administrator. This second group is for larger applications that sometimes require constant attention, esp. if user accounts have to be created, or custom views etc. ... or if the application wasn't ready for production and was placed into production anyway -- then it will require constant babysitting. Consultants come in usually to implement new projects, or to add features to an existing system. That makes them application designers or application developers. Sometimes (rarely) consultants are hired to tune systems, that would be a blend of DBA and application designer. This is rare though, usually the database layer is working properly it seems to me, if the DBA has been there for more than a year, has read a book or two, and has at least the echo of a conscience. A production DBA is responsible for ensuring that the structure beneath the application stays up and is tuned properly. He/she works with the system administrator(s) to ensure that the hardware and the Oracle software (rdbms, developer server, iAS, networking,...) are all working properly and as expected. I don't fully understand why developers (some developers) strive to be called a DBA. Here is my guess: Perhaps this distinction stays fuzzy in organizations because there is a constant tug-of-war for control over resources between the development and production groups. If an overlap can be created, then there is an opportunity to take over some of the other group's resources. Also, when responsibilities are not delineated clearly, there is an opportunity for one side to blame the other and management can never figure out who is doing what. I worked in a lab where we were implementing Good Laboratory Practice (GLP) for the Food and Drug Administration (FDA), there was supposed to be no overlap between positions. I noticed that the managers who played games and only thought about their own advancement didn't like GLP at all, they fought it tooth and nail. I liked the idea of separate each person's circle of responsibility myself. Why can't IT shops strive to do the same? Speaking as a DBA, it is my perception that developers tend to be project-oriented. That's fine, it's why they are there. But that tendency also means, when they see their deadlines coming, that they sometimes aren't keen on thinking long term. Perhaps it's not their fault, it's because of the way projects are funded. Which client wants to hear that for every project, money will have to be allocated for ongoing costs of maintenance, operation, upgrades every 2-3 years? No one wants to think about that when they only want to think about the great new things they will be able to do with the new application. Also, no one wants to spend more money than necessary, so there is a tendency to try to cut corners to get to the end of the project. That is probably why projects tend to be rushed into production. Once the projects are in production mode funding to finish the product dries up. That sometimes leaves the application designer off the hook and leaves the production DBA holding the bag. Finally, if you are designing a new project, the tendency is to try to retain control over as much of it as possible. If you declare yourself to be a development DBA, then people are less likely to insist that you consult the DBA(s) during the design phase of a project. What a bother that is, having to listen to other people -- it's my project! It will only slow us down... Worse, I will have to share the credit once the application works properly. That won't be as good for my career. If you know that the DBA in the organization is stubborn and intractable, then this is the route the application designers will try to take. I could draw up a list of things that
RE: Case of the Missing Rows
Title: Case of the Missing Rows Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message-From: Weiss, Rick [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
RE: Perl - Was Unix time conversion function
Perl kind of makes sense but I haven't reached the point where it clicks and becomes natural. I still need to think about it very hard when I'm writing it. Hence, a lot of the time I fall back on shell scripts supplemented by pre-written (some would say shamelessly ripped off) perl code for the stuff that would get really messy is ksh. Regards, Mike Hately -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses. Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277 This e-mail may be sent on behalf of a member of the Innogy group of companies. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT - Raid
Hi All, Does anybody have any good resources that discuss the pros and cons of each RAID level, and their respective set up procedures? Thanks in advance for any pointers. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - Raid
-Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Mark Leith Enviado el: martes, 28 de enero de 2003 15:34 Para: Multiple recipients of list ORACLE-L Asunto: OT - Raid Hi All, Does anybody have any good resources that discuss the pros and cons of each RAID level, and their respective set up procedures? Thanks in advance for any pointers. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Raid1.zip Description: Zip compressed data
Re: Connexion between 9iAS and Oracle 7.3.4
9iAS Release1 uses 8.1.7 libraries. 9iAS Release2 users 9i libraries. 9i [either client or server] connections to 7.3 aren't supported and may well give errors. Hemant At 07:59 AM 27-01-03 -0800, you wrote: It works here for both 9iAS R1 R2! Amos KABORE akabore@experco.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] com cc: Sent by: Subject: Connexion between 9iAS and Oracle 7.3.4 [EMAIL PROTECTED] 01/27/2003 06:29 AM Please respond to ORACLE-L hi all I just want to know if Oracle 9iAS can connect to Oracle 7.3.4. If yes, is it recommended ? thanks in advance. Amos K -- 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT - Raid
Hi Juan, Thanks for the reply! Unfortunately Oracle-L doesn't allow attachments, would you be so kind as to forward the original to [EMAIL PROTECTED]? Many thanks! Mark -Original Message- Miranda Sent: 28 January 2003 15:04 To: Multiple recipients of list ORACLE-L -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Mark Leith Enviado el: martes, 28 de enero de 2003 15:34 Para: Multiple recipients of list ORACLE-L Asunto: OT - Raid Hi All, Does anybody have any good resources that discuss the pros and cons of each RAID level, and their respective set up procedures? Thanks in advance for any pointers. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Title: unix time conversion function Now that's what I call a "Perl Breakdown!!"...A nervous breakdown brought on by pathological eclecticism. The cure for this is a healthy dose of Python. It is truly refreshing!! Steve Orr Oracle DBA and part-time Python Evangelist. -Original Message-From: Robert Freeman [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 11:40 PMTo: Multiple recipients of list ORACLE-LSubject: Perl - Was unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my "learning Perl" book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF
RE: Perl - Was unix time conversion function
Original Message In my mind there is nothing obvious about Perl, Anyone else feel that way about Perl or am I a lone wolf in a Perl world? I think the correct progression is to start with ksh; then mix in sed, awk, and maybe a dash of egrep. When you finally are comfortable with co-processes (aaaugh!!), then you are ready to start on Perl. Not that Perl has anything as goofy as co-processes; but if you can do that, you should be ready for Perl. The ksh function definition and calling as vaguely similar to C, so you won't be on completely unfamiliar turf. When you have gotten the hang of sed, awk, and egrep, than at least some of Perl will be deja vu all over again. KSH! PERL?! We don't need no stinkin' Perl! (except the ksh you get with Linux is probably the lame, piece-of-crap version. Download and build the REAL ksh93.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Base conversion
For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-) Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Case of the Missing Rows
Title: Case of the Missing Rows Could they be in the .bad file? I have had stuff go there if a column was too long or something. Ruth - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 7:43 PM Subject: Re: Case of the Missing Rows My guess is that SQL*Loader didn't really load 88,640 rows, but rejected or discarded about 1400 of them? - Original Message - From: Weiss, Rick To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 4:53 PM Subject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
RE: Perl - Was Unix time conversion function
For me, it was either Perl or an icky bass-ackward pipe-laden awk/sed/regex unmaintainable bastion. OK, I couldn't get rid of the regex. While I'll not be entering the Obfuscated Perl contest anytime soon, I think Perl is much easier to understand for a traditional programmer (Assembly, BASIC, COBOL, FORTRAN, and a little C). I bought O'Reilly's Learning Perl, and most of what I needed to do was in the book as an example. Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl - Was unix time conversion function
please don't take umbridge, but I feel enticed to quote what you don't know, dosn't (really) matter Larry Wall, programming with perl, O'Reilly. just for a giggle. sorry apologies for any typos overlooked kr mr [EMAIL PROTECTED] 01/28/03 12:22 PM I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Markus Reger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: Case of the Missing Rows
Title: Case of the Missing Rows Rick, Another idea is to check the explain plans of each of the queries. If there is a difference it could indicate that the data is there, but a particular access path is invalid. Dan Fink -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 28, 2003 7:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Case of the Missing Rows Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message-From: Weiss, Rick [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
RE: Base conversion
Just happen to have this lying around CREATE OR REPLACE function base_x2y ( input varchar2, basein integer, baseout integer ) Return varchar2 is output varchar2(255); output_val integer := 0; char_valvarchar2(1) := null; number_val integer := 0; input_size integer := 0; pos integer := 0; begin select length(input) into input_size from dual; pos := 1; while pos = input_size loop select decode(substr(input,pos,1),'0', 0,'1', 1,'2', 2,'3', 3,'4', 4,'5', 5, '6', 6,'7', 7,'8', 8,'9', 9,'A',10,'B',11, 'C',12,'D',13,'E',14,'F',15,'G',16,'H',17, 'I',18,'J',19,'K',20,'L',21,'M',22,'N',23, 'O',24,'P',25,'Q',26,'R',27,'S',28,'T',29, 'U',30,'V',31,'W',32,'X',33,'Y',34,'Z',35, 0) into number_val from dual; select (output_val + number_val * power(basein,(input_size-pos))) into output_val from dual; pos := pos + 1; end loop; while output_val 0 loop number_val := baseout * ((output_val/baseout) - trunc(output_val/baseout)); output_val := trunc(output_val/baseout); select decode(number_val, 0,'0', 1,'1', 2,'2', 3,'3', 4,'4', 5,'5', 6,'6', 7,'7', 8,'8', 9,'9',10,'A',11,'B', 12,'C',13,'D',14,'E',15,'F',16,'G',17,'H', 18,'I',19,'J',20,'K',21,'L',22,'M',23,'N', 24,'O',25,'P',26,'Q',27,'R',28,'S',29,'T', 30,'U',31,'V',32,'W',33,'X',34,'Y',35,'Z',' ') into char_val from dual; output := char_val||output; end loop; return output; end; / -Original Message- Sent: Tuesday, January 28, 2003 9:45 AM To: Multiple recipients of list ORACLE-L For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-) Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Base conversion
Try this: CREATE FUNCTION to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number AS l_num number default 0; --l_hex varchar2(16) default '0123456789ABCDEF'; l_base varchar2(16) default '0123456789ABCDEFYZ';-- fill in the blanks begin for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_base,upper(substr(p_str,i,1)))-1; end loop; return l_num; end / HTH, don't forget to fill in l_base local variable. Obviosly, in your case p_from_base parameter will be 36. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 10:45 AM For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-) Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Case of the Missing Rows
Title: Message Nope, .BAD file is empty, I have been experimenting this morning with cloning the table as suggested earlier, but every DDL statement against the table drops the rows (#'s 70-1417) of the ID PK Column. Still working on that angle Rick Weiss -Original Message-From: Ruth Gramolini [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:55 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Case of the Missing Rows Could they be in the .bad file? I have had stuff go there if a column was too long or something. Ruth - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 7:43 PM Subject: Re: Case of the Missing Rows My guess is that SQL*Loader didn't really load 88,640 rows, but rejected or discarded about 1400 of them? - Original Message - From: Weiss, Rick To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 4:53 PM Subject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
Re:Base conversion
Sounds like something the bright spark or Duhveloper should fix. It's better when they experience their own pain. Reply Separator Author: Grant Allen [EMAIL PROTECTED] Date: 1/28/2003 7:45 AM For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-) Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Slightly OT: Development Vs. Production DBA
Ranganath You are getting some excellent responses to your question (which I consider very on topic). We had a good discussion on this list previously. I went to Google and entered fatcity production dba and was able to pick up the thread. As to your question of OLTP vs. DSS situations, I think the differences are more subtle. I thing OLTP environments are more standard than DSS environments. Our DSS is more relaxed since it is a weekly load. Sunday and Monday are the critical days. Some of it is attitude, not being a normalization bigot, realizing that marketing people are a different species that will never know their needs in advance. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 2:29 AM To: Multiple recipients of list ORACLE-L Hi Listers, I would like to know the differences between Development and Production DBA w.r.t. Roles and Responsibilities, Scope etc. Is there any difference in the role(s) played by a DBA in OLTP and DSS environments? Your invaluable viewpoints in this regard is most welcome. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - Raid
Mark You could try the following links for details about the various RAID configurations. http://www.solumedia.com/raidinfo.htm http://www.webopedia.com/TERM/R/RAID.html http://www.acnc.com/raid.html Eddie Watkins The Moray Council email: [EMAIL PROTECTED] tel: 01343 563247 Hi All, Does anybody have any good resources that discuss the pros and cons of each RAID level, and their respective set up procedures? Thanks in advance for any pointers. Mark The Moray Council: Internet E-mail Notice The contents of this e-mail and any attachments ('this e-mail') are confidential and intended solely for the addressee. If this e-mail has been sent to you by mistake, please notify [EMAIL PROTECTED] as soon as possible; you should then delete this e-mail from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eddie Watkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl - Was unix time conversion function
I've started writing some perl and it is hard to learn, but once you learn, it can do some great things. And if you learned it from the Larry Wall book like I did, then it's even harder. The thing I've discovered about perl it that it may be the only language (computer or otherwise) that is easier to write than it is to read. I'm sure a perl expert would gasp at my code, it's far too readable. Keith - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 10:09 AM please don't take umbridge, but I feel enticed to quote what you don't know, dosn't (really) matter Larry Wall, programming with perl, O'Reilly. just for a giggle. sorry apologies for any typos overlooked kr mr [EMAIL PROTECTED] 01/28/03 12:22 PM I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cary Millsap Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Post, Ethan Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: unix time conversion function Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- From: Adams, Matthew (GECP, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: unix time conversion function Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: Slightly OT: Development Vs. Production DBA
Title: RE: Slightly OT: Development Vs. Production DBA I agree 100%. I am fighting this battle as we speak. Many Duhvelopers think they can do it all until something goes wrong then guess who they call to bail them out. Then Damagement is breathing down your neck to get it fixed when you have no idea what happened neither does the Duhveloper! I think an organization needs to have clear policies in place and enforce them to the end. Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Slightly OT: Development Vs. Production DBA A development DBA is a developer who wants to design the schemas his/her application will rely on. I prefer calling them application designers, because that's what they are. Sometimes you have another role, that of Application Administrator. This second group is for larger applications that sometimes require constant attention, esp. if user accounts have to be created, or custom views etc. ... or if the application wasn't ready for production and was placed into production anyway -- then it will require constant babysitting. Consultants come in usually to implement new projects, or to add features to an existing system. That makes them application designers or application developers. Sometimes (rarely) consultants are hired to tune systems, that would be a blend of DBA and application designer. This is rare though, usually the database layer is working properly it seems to me, if the DBA has been there for more than a year, has read a book or two, and has at least the echo of a conscience. A production DBA is responsible for ensuring that the structure beneath the application stays up and is tuned properly. He/she works with the system administrator(s) to ensure that the hardware and the Oracle software (rdbms, developer server, iAS, networking,...) are all working properly and as expected. I don't fully understand why developers (some developers) strive to be called a DBA. Here is my guess: Perhaps this distinction stays fuzzy in organizations because there is a constant tug-of-war for control over resources between the development and production groups. If an overlap can be created, then there is an opportunity to take over some of the other group's resources. Also, when responsibilities are not delineated clearly, there is an opportunity for one side to blame the other and management can never figure out who is doing what. I worked in a lab where we were implementing Good Laboratory Practice (GLP) for the Food and Drug Administration (FDA), there was supposed to be no overlap between positions. I noticed that the managers who played games and only thought about their own advancement didn't like GLP at all, they fought it tooth and nail. I liked the idea of separate each person's circle of responsibility myself. Why can't IT shops strive to do the same? Speaking as a DBA, it is my perception that developers tend to be project-oriented. That's fine, it's why they are there. But that tendency also means, when they see their deadlines coming, that they sometimes aren't keen on thinking long term. Perhaps it's not their fault, it's because of the way projects are funded. Which client wants to hear that for every project, money will have to be allocated for ongoing costs of maintenance, operation, upgrades every 2-3 years? No one wants to think about that when they only want to think about the great new things they will be able to do with the new application. Also, no one wants to spend more money than necessary, so there is a tendency to try to cut corners to get to the end of the project. That is probably why projects tend to be rushed into production. Once the projects are in production mode funding to finish the product dries up. That sometimes leaves the application designer off the hook and leaves the production DBA holding the bag. Finally, if you are designing a new project, the tendency is to try to retain control over as much of it as possible. If you declare yourself to be a development DBA, then people are less likely to insist that you consult the DBA(s) during the design phase of a project. What a bother that is, having to listen to other people -- it's my project! It will only slow us down... Worse, I will have to share the credit once the application works properly. That won't be as good for my career. If you know that the DBA in the organization is stubborn and intractable, then this is the route the application designers will try to take. I could draw up a list of things that can go wrong when DBAs are not involved in the design phase of a project, but I think all people need to do is brainstorm for ten minutes to get a list of 10 or more things that can go wrong... Then try to put a cost value to each of
RE: Case of the Missing Rows
Title: Message Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 7:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Case of the Missing Rows Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message-From: Weiss, Rick [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
RE: Re: Case of the Missing Rows
If SQL*Loader had not loaded all the stuff, then the first count(*) would return the same thing as the second one. My (wild) guess would rather be some mishandling of the high-water mark or something similar. Really looks like a bug. Things I would try : - Analyzing the table (compute) after the load, to check what Oracle finds - Checking the number of extents, etc. If the missing rows happen to be in a separate extent, does recreating the table in a big enough initial extent (heresy!) change anything ? - SQL*Loader, count(*), deallocate unused, count(*) - Playing with the various SQL*Loader option (INSERT/TRUNCATE/APPEND (in an emptied table) to see whether it makes a difference. HTH, Stephane Faroult - Original Message - From: Ruth Gramolini [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 28 Jan 2003 07:54:35 Case of the Missing RowsCould they be in the .bad file? I have had stuff go there if a column was too long or something. Ruth - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 7:43 PM Subject: Re: Case of the Missing Rows My guess is that SQL*Loader didn't really load 88,640 rows, but rejected or discarded about 1400 of them? - Original Message - From: Weiss, Rick To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 4:53 PM Subject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
My impression of Programming Perl was that it was primarily intended as an ego trip for the author. I found Learning Perl 2nd Ed. and Perl Core Language / Little Black Book to be much more useful. Yes, I have the Perl for Oracle DBA's too, but haven't had the time to get into it yet. -Original Message- I've started writing some perl and it is hard to learn, but once you learn, it can do some great things. And if you learned it from the Larry Wall book like I did, then it's even harder. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Base conversion
Oracle made rowids base 64. Try to identify a corrupt block number from that when you do select rowid, last_column_of_table from table; to see where the thing breaks. Does anyone, perchance, know if they provided a base 64 converter to go with the base 64 rowids? -Original Message- Sounds like something the bright spark or Duhveloper should fix. It's better when they experience their own pain. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
tuning forms/reports application
1. Is there any way we can tune the forms/reports application ? 2. How can we check which form/report is actually the bottleneck. We have a large no. of forms/reports and almost all are very big/complex. Is there any third party utility to do this ? TIA Shuja -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M. Shuja Uddin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was Unix time conversion function
What's the title of Jared's book? -Scott At 05:03 AM 1/28/03 -0800, you wrote: I used to be, but I finally bit the bullet ... I ordered my copy of Jared's book from Amazon.com yesterday !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Robert Freeman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Subject: Perl - Was Unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF ** Scott Stefick UNIX Systems Administrator Oracle Certified Professional DBA Wm. Rainey Harper College 847.925.6130 **
RE: Case of the Missing Rows
sounds like a bug to me. nice investigative work. Jared Weiss, Rick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Case of the Missing Rows Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -Original Message- Sent: Tuesday, January 28, 2003 7:45 AM To: Multiple recipients of list ORACLE-L Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message- Sent: Monday, January 27, 2003 4:54 PM To: Multiple recipients of list ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss -- 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).
Windows 2000 Cluster on oracle
Hi I wanted to migrate my database from SUN solaris to WINDOWS 2000 platform.Curetly I am having sun cluster as failover with shared disk. I wanted to setup similar kind of setup with windows 2000. Is any failover option available in Windows2000? Let me know if anyone does such kind of setup earlier ? Thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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).
Re: Case of the Missing Rows
Is the table in an autoallocate tablespace - is the tablespace using assm ? Are the appropriate list of extents apparently present in the table. Can you dump the segment header block to see if the extent list looks sensible. Is the tablespace free space consistent with the tablespace used space ? Have you tried to run validate structure on the table, and the various dbms_space_admin bitmap routines in the table and tablespace ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 January 2003 17:10 Nope, .BAD file is empty, I have been experimenting this morning with cloning the table as suggested earlier, but every DDL statement against the table drops the rows (#'s 70-1417) of the ID PK Column. Still working on that angle Rick Weiss -Original Message- Sent: Tuesday, January 28, 2003 8:55 AM To: Multiple recipients of list ORACLE-L Could they be in the .bad file? I have had stuff go there if a column was too long or something. Ruth - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Monday, January 27, 2003 7:43 PM My guess is that SQL*Loader didn't really load 88,640 rows, but rejected or discarded about 1400 of them? - Original Message - To: Multiple recipients of list mailto:[EMAIL PROTECTED] ORACLE-L Sent: Monday, January 27, 2003 4:53 PM I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was Unix time conversion function
Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Why? Look up OraC and OracleTool on google. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Perl - Was Unix time conversion function For me, it was either Perl or an icky bass-ackward pipe-laden awk/sed/regex unmaintainable bastion. OK, I couldn't get rid of the regex. While I'll not be entering the Obfuscated Perl contest anytime soon, I think Perl is much easier to understand for a traditional programmer (Assembly, BASIC, COBOL, FORTRAN, and a little C). I bought O'Reilly's Learning Perl, and most of what I needed to do was in the book as an example. Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: tuning forms/reports application
Title: RE: tuning forms/reports application 1. you sure can, but it depends on what you want to tune for ... 2. Usually in my case, I trust my user, if they say form xyz is running slow like a drunk snail, there is a good chance that it is, so I work on that. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: M. Shuja Uddin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:34 PM To: Multiple recipients of list ORACLE-L Subject: tuning forms/reports application 1. Is there any way we can tune the forms/reports application ? ** you sure can ... but it depends 2. How can we check which form/report is actually the bottleneck. We have a large no. of forms/reports and almost all are very big/complex. Is there any third party utility to do this ? TIA Shuja This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Base conversion
Here's a packaged and modified version of Tom Kyte's conversion routines. --- the package create or replace package radix is /* base code courtesy of Thomas Kyte */ function to_base( p_dec in number, p_base in number ) return varchar2; function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number; function to_hex( p_dec in number ) return varchar2; function to_bin( p_dec in number ) return varchar2; function to_oct( p_dec in number ) return varchar2; function to_36( p_dec in number ) return varchar2; pragma restrict_references( to_base, wnds, rnds, wnps, rnps ); pragma restrict_references( to_dec, wnds, rnds, wnps, rnps ); pragma restrict_references( to_hex, wnds, rnds, wnps, rnps ); pragma restrict_references( to_bin, wnds, rnds, wnps, rnps ); pragma restrict_references( to_oct, wnds, rnds, wnps, rnps ); pragma restrict_references( to_36, wnds, rnds, wnps, rnps ); end radix; / show errors create or replace package body radix is function to_base( p_dec in number, p_base in number ) return varchar2 is l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; begin if ( trunc(p_dec) p_dec OR p_dec 0 ) then raise INVALID_NUMBER; end if; loop l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; l_num := trunc( l_num/p_base ); exit when ( l_num = 0 ); end loop; return l_str; end to_base; function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number is l_num number default 0; l_hex varchar2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; begin for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num; end to_dec; function to_hex( p_dec in number ) return varchar2 is begin return to_base( p_dec, 16 ); end to_hex; function to_bin( p_dec in number ) return varchar2 is begin return to_base( p_dec, 2 ); end to_bin; function to_oct( p_dec in number ) return varchar2 is begin return to_base( p_dec, 8 ); end to_oct; function to_36( p_dec in number ) return varchar2 is begin return to_base( p_dec, 36 ); end to_36; end radix; / show errors -- -- the test select radix.to_36(100) from dual; select radix.to_dec(radix.to_36(100),36) from dual; select radix.to_36(255) from dual; select radix.to_dec(radix.to_36(255),36) from dual; - Jared Grant Allen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 07:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Base conversion For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-) Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Case of the Missing Rows
swag -- is there a trigger on either of those tables? sounds like the possibility of an AFTER STATEMENT trigger this is of course, total guesswork but it only occurs when you create the table from scratch, or at least that's how I'm reading what you wrote --- Weiss, Rick [EMAIL PROTECTED] wrote: Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -Original Message- Sent: Tuesday, January 28, 2003 7:45 AM To: Multiple recipients of list ORACLE-L Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message- Sent: Monday, January 27, 2003 4:54 PM To: Multiple recipients of list ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl - Was unix time conversion function
Robert, Perl isn't really that hard. As with any language, it can be difficult to grasp at first, but once you begin to understand it, it can be extremely powerful. Regarding Cary's comments about the difficulty of complex Perl data structures, I would have to say that they aren't any more difficult than complex C data structures. Data::Dumper is your friend. It can be used to print the contents and structure of any Perl data structure so that it is more easily understood. As for Stephen Lee's comments that there's no need for Perl if you have ksh: I'll bet Stephen has a toolbox in his garage that contains a single flat blade screwdriver and no phillips screwdrivers nor any wrenches. ;) You can do most anything in ksh: I know, I've done it. Then I learned Perl. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2003 10:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Perl - Was unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9?12 Dallas - RMOUG Training Days 2003, Mar 5?6 Denver - Hotsos Clinic 101, Mar 26?28 London -Original Message- Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. -- 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).
DataGuard
I am going to be taking over the implementation of DataGuard for 9.2. Before I do, I'd like to learn as much about it as I can. Have any books been written about DataGuard? Or do any books out there have portions addressing DataGuard? Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Slightly OT: Development Vs. Production DBA
Title: RE: Slightly OT: Development Vs. Production DBA -realizing that marketing people are a different species -that will never know their needs in advance. ... and don't actually care what *our* needs are ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Slightly OT: Development Vs. Production DBA
-realizing that marketing people are a different species -that will never know their needs in advance. Amen brother, Amen -Original Message- Sent: Tuesday, January 28, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Ranganath You are getting some excellent responses to your question (which I consider very on topic). We had a good discussion on this list previously. I went to Google and entered fatcity production dba and was able to pick up the thread. As to your question of OLTP vs. DSS situations, I think the differences are more subtle. I thing OLTP environments are more standard than DSS environments. Our DSS is more relaxed since it is a weekly load. Sunday and Monday are the critical days. Some of it is attitude, not being a normalization bigot, realizing that marketing people are a different species that will never know their needs in advance. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 2:29 AM To: Multiple recipients of list ORACLE-L Hi Listers, I would like to know the differences between Development and Production DBA w.r.t. Roles and Responsibilities, Scope etc. Is there any difference in the role(s) played by a DBA in OLTP and DSS environments? Your invaluable viewpoints in this regard is most welcome. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Title: RE: Perl - Was unix time conversion function If you can do it in Perl chances are you can do it better in Python and someone can actually read and understand it without a PHD... AKA Perl Helper Dweeb. :-) In Perl it takes effort to write readable code but it comes naturally in Python. Check it out: www.python.org/doc/essays/blurb.html http://www.orbtech.com/web/python/why http://www.networkcomputing.com/unixworld/tutorial/005/005.html#Others http://www.developer.com/open/print.php/610691 Steve Orr Oracle DBA and part-time Python Evangelist ;-) -Original Message- From: Keith Moore [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Perl - Was unix time conversion function I've started writing some perl and it is hard to learn, but once you learn, it can do some great things. And if you learned it from the Larry Wall book like I did, then it's even harder. The thing I've discovered about perl it that it may be the only language (computer or otherwise) that is easier to write than it is to read. I'm sure a perl expert would gasp at my code, it's far too readable. Keith - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 10:09 AM please don't take umbridge, but I feel enticed to quote what you don't know, dosn't (really) matter Larry Wall, programming with perl, O'Reilly. just for a giggle. sorry apologies for any typos overlooked kr mr [EMAIL PROTECTED] 01/28/03 12:22 PM I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF
RE: Windows 2000 Cluster on oracle
-Original Message- I wanted to migrate my database from SUN solaris to WINDOWS 2000 platform. - ... and there was a time when I thought it would be neat to put splinters in my bathroom tissue. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Title: RE: Perl - Was unix time conversion function I haven't done anything with Python but my problem with Python or Ruby is that they don't come with many OS variants. Perl comes with any OS and there is a huge repository of ready made scripts. I have to confess stealing some from Jared's PDBA collection. I cannot do that with Python and then run them on both HP-UX and AIX. Therefore, I'll stick tothe pathologically eclectic rubbish lister. -Original Message-From: Orr, Steve [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 28, 2003 1:00 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Perl - Was unix time conversion function If you can do it in Perl chances are you can do it better in Python and someone can actually read and understand it without a PHD... AKA Perl Helper Dweeb. :-) In Perl it takes effort to write readable code but it comes naturally in Python. Check it out: www.python.org/doc/essays/blurb.html http://www.orbtech.com/web/python/why http://www.networkcomputing.com/unixworld/tutorial/005/005.html#Others http://www.developer.com/open/print.php/610691 Steve Orr Oracle DBA and part-time Python Evangelist ;-) -Original Message- From: Keith Moore [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Perl - Was unix time conversion function I've started writing some perl and it is hard to learn, but once you learn, it can do some great things. And if you learned it from the Larry Wall book like I did, then it's even harder. The thing I've discovered about perl it that it may be the only language (computer or otherwise) that is easier to write than it is to read. I'm sure a "perl expert" would gasp at my code, it's far too readable. Keith - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 10:09 AM please don't take umbridge, but I feel enticed to quote "what you don't know, dosn't (really) matter" Larry Wall, programming with perl, O'Reilly. just for a giggle. sorry apologies for any typos overlooked kr mr [EMAIL PROTECTED] 01/28/03 12:22 PM I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :)--- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my "learning Perl" book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF
RE: Windows 2000 Cluster on oracle
HP has such a software. It used to be know as Compaq TrueCluster. As for migrating from Solaris to Windows,it's a good decision. Many software packages do not work at all with Solaris or any Unix version. One that has become very popular lately is called slammer. It only works with Win2k and SQL Server. That should be motivation enough to migrate. Your life is about to get interesting. -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:40 PM To: Multiple recipients of list ORACLE-L Subject: Windows 2000 Cluster on oracle Hi I wanted to migrate my database from SUN solaris to WINDOWS 2000 platform.Curetly I am having sun cluster as failover with shared disk. I wanted to setup similar kind of setup with windows 2000. Is any failover option available in Windows2000? Let me know if anyone does such kind of setup earlier ? Thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
I'll add a clarification. With its object interface, Perl actually lets one define much more easily accessible data structures than one can in C. The problem with that is that the extra code path consumed by the accessor methods is too slow to actually *use* them in some of our code (think tens of millions of trace file lines that you need to parse in a few seconds). Hence the need for a lot of Perl code that looks like $a{$b}-{$c}-{$d}-[$e]. Igh. Don't get me wrong. I have written large applications in many languages (several thousand lines as a full-time professional software developer using C and ksh). In the past three years, I have written several thousand lines of Perl and accomplished more work than I would ever have imagined ten years ago. Perl is, for me, the best computer language in the world. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 11:51 AM To: Multiple recipients of list ORACLE-L Robert, Perl isn't really that hard. As with any language, it can be difficult to grasp at first, but once you begin to understand it, it can be extremely powerful. Regarding Cary's comments about the difficulty of complex Perl data structures, I would have to say that they aren't any more difficult than complex C data structures. Data::Dumper is your friend. It can be used to print the contents and structure of any Perl data structure so that it is more easily understood. As for Stephen Lee's comments that there's no need for Perl if you have ksh: I'll bet Stephen has a toolbox in his garage that contains a single flat blade screwdriver and no phillips screwdrivers nor any wrenches. ;) You can do most anything in ksh: I know, I've done it. Then I learned Perl. Jared Robert Freeman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2003 10:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Perl - Was unix time conversion function Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -Original Message- Sent: Friday, January 24, 2003 4:29 PM To: Multiple recipients of list ORACLE-L At the risk of stating the obvious, doing it in Perl looks like this: #!/usr/bin/perl use Date::Format qw(time2str); my $t = 1043447100; # for example print time2str(%T %A %d %B %Y, $t), \n; Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium on OracleR System Performance, Feb 9?12 Dallas - RMOUG Training Days 2003, Mar 5?6 Denver - Hotsos Clinic 101, Mar 26?28 London -Original Message- Sent: Friday, January 24, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Kinda...you can change the year to 1970 if you want, this also converts to minutes, not seconds. It is a really ugly function but it seems to work. You could always use perl. function f_minutes { # Funky function I use to calculate the number of minutes since 2000 MIN_YEAR=$( date +%Y ) MIN_YEAR=$( expr ${MIN_YEAR} - 2000 ) MIN_YEAR=$( expr ${MIN_YEAR} \* 525600 ) MIN_DAYS=$( date +%j ) MIN_DAYS=$( expr ${MIN_DAYS} - 1 ) MIN_DAYS=$( expr ${MIN_DAYS} \* 1440 ) MIN_HOURS=$( date +%H ) MIN_HOURS=$( expr ${MIN_HOURS} \* 60 ) MIN_MINS=$( date +%M ) MIN_TOTAL=$(( ${MIN_YEAR} + ${MIN_DAYS} + ${MIN_HOURS} + ${MIN_MINS} )) print ${MIN_TOTAL} } -Original Message- Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Anybody got a handy little function to convert a standard unix seconds-since-Jan-1970 epoch time (stored as a number) to a readable date? It would save me a lot of time not having to re-invent the wheel. Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. -- 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
RE: Base conversion
Stephen, The code I posted earlier is easily adapted to do base 64. Please share your mods. :) Jared Stephen Lee [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 09:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Base conversion Oracle made rowids base 64. Try to identify a corrupt block number from that when you do select rowid, last_column_of_table from table; to see where the thing breaks. Does anyone, perchance, know if they provided a base 64 converter to go with the base 64 rowids? -Original Message- Sounds like something the bright spark or Duhveloper should fix. It's better when they experience their own pain. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
Global Stats
Title: Global Stats Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: Perl - Was unix time conversion function
-Original Message- I'll bet Stephen has a toolbox in his garage that contains a single flat blade screwdriver and no phillips screwdrivers nor any wrenches. ;) - Don't need tools. Got pshycokinesis (got milk too). Been taking lessons from Carrie (and dating her mom!). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Case of the Missing Rows
Title: Message I'm thinking it is related to the execution plan. When you compute stats, you don't seem to have a problem. Run the same tests and check the execution plans in autotrace. -Original Message-From: Weiss, Rick [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 28, 2003 10:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Case of the Missing Rows Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 7:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Case of the Missing Rows Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message-From: Weiss, Rick [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: Case of the Missing Rows I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss
Re: DataGuard
amazingly the oracle docs are pretty good on data guard implementation(as compared to oracle docs in v5/v6 days). :) joe Ball, Terry wrote: I am going to be taking over the implementation of DataGuard for 9.2. Before I do, I'd like to learn as much about it as I can. Have any books been written about DataGuard? Or do any books out there have portions addressing DataGuard? Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Case of the Missing Rows
Yes, only after an empty build from scratch No triggers at all on either table Rick -Original Message- Sent: Tuesday, January 28, 2003 11:34 AM To: Multiple recipients of list ORACLE-L swag -- is there a trigger on either of those tables? sounds like the possibility of an AFTER STATEMENT trigger this is of course, total guesswork but it only occurs when you create the table from scratch, or at least that's how I'm reading what you wrote --- Weiss, Rick [EMAIL PROTECTED] wrote: Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -Original Message- Sent: Tuesday, January 28, 2003 7:45 AM To: Multiple recipients of list ORACLE-L Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -Original Message- Sent: Monday, January 27, 2003 4:54 PM To: Multiple recipients of list ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Weiss, Rick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Windows 2000 Cluster on oracle
LMAOROTF --- Gogala, Mladen [EMAIL PROTECTED] wrote: HP has such a software. It used to be know as Compaq TrueCluster. As for migrating from Solaris to Windows,it's a good decision. Many software packages do not work at all with Solaris or any Unix version. One that has become very popular lately is called slammer. It only works with Win2k and SQL Server. That should be motivation enough to migrate. Your life is about to get interesting. -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:40 PM To: Multiple recipients of list ORACLE-L Subject: Windows 2000 Cluster on oracle Hi I wanted to migrate my database from SUN solaris to WINDOWS 2000 platform.Curetly I am having sun cluster as failover with shared disk. I wanted to setup similar kind of setup with windows 2000. Is any failover option available in Windows2000? Let me know if anyone does such kind of setup earlier ? Thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rich rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was Unix time conversion function
- because it would be fun to write your own app - sense of accomplishment - you'd get a better handle on the language knowing it's strength and weaknesses etc... Not saying that you should go and write your own dbms or word processor or OS. But sometimes writing a little utility from scratch is more rewarding than downloading a pre-packaged app. mohammed --- [EMAIL PROTECTED] wrote: Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Why? Look up OraC and OracleTool on google. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Perl - Was Unix time conversion function For me, it was either Perl or an icky bass-ackward pipe-laden awk/sed/regex unmaintainable bastion. OK, I couldn't get rid of the regex. While I'll not be entering the Obfuscated Perl contest anytime soon, I think Perl is much easier to understand for a traditional programmer (Assembly, BASIC, COBOL, FORTRAN, and a little C). I bought O'Reilly's Learning Perl, and most of what I needed to do was in the book as an example. Also, on scant nights I've even been rolling my own KISS-method Perl/Tk OEM replacement. Sorry Jared, but sometimes I like GUIs! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Base conversion
-Original Message- Stephen, The code I posted earlier is easily adapted to do base 64. Please share your mods. :) - OK. I figured out that A is zero (I think). Now, only 63 more to go! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DataGuard
On Tue, 2003-01-28 at 13:29, Ball, Terry wrote: I am going to be taking over the implementation of DataGuard for 9.2. Before I do, I'd like to learn as much about it as I can. Have any books been written about DataGuard? Or do any books out there have portions addressing DataGuard? Go to tahiti.oracle.com and read the Data Guard Concepts and Administration manual. I've found the documentation pretty decent; you might find that you really don't need anyone else to iterpret it for you. DG is a nice improvement over the standby database features of 8i. My experience thus far is that it works very well indeed. -- Glenn Stauffer Swarthmore College Swarthmore, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Global Stats
Lisa, On our home grown partitioned databases, I am still using the ANALYZE command for partitions. There were some bugs associated with DBMS_STATS and partitions, although I don't remember the specifics at the moment (it may have been with 8.1.6). I haven't had a chance to go back and see if we want to switch to DBMS_STATS. On our 11i database (8.1.7), Oracle provided scripts specifically check to make sure that global stats to not exist on partitioned tables (search for bde_last_analyzed.sql on MetaLink). We analyze these tables with FND_STATS and a granularity of PARTITION. Others may have more specific info on where the issue stands beyond 8.1.7. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 01/28/03 02:10PM Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
extents question on LMT
Hi: Oracle 8173 on Sun 2.8. When we had tablespace created as DMT, I used to occuasionally find all indexes in a schema which have multiple extents and run a script to compress each of them into one single extent (maybe this is not necessary, but that's another topic). But I find lately that with LMT, I can not simply do that. Oracle will decide how many extents it allocates depending on the storage parameters I pass in: ALTER INDEX ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging; Does this mean I just can not compress some large index into a single extent in a LMT? Example: SQL ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 50K NEXT 50K pctincrease 0) nologging; Index altered. SQL select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK'; BYTESEXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT -- -- -- -- --- 516096 1 63 57344 516096 SQL ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging; Index altered. SQL select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK'; BYTESEXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT -- -- -- -- --- 5677056 116935242880 516096 TIA. Guang _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT - Raid
There is an excellent white paper by Gaja Krishna Vaidyanatha: http://www.quest.com/whitepapers/Raid1.pdf Mark Leith wrote: Hi All, Does anybody have any good resources that discuss the pros and cons of each RAID level, and their respective set up procedures? Thanks in advance for any pointers. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Base conversion
Here ya go: create or replace package radix is /* base code courtesy of Thomas Kyte */ function to_base( p_dec in number, p_base in number ) return varchar2; function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number; function to_hex( p_dec in number ) return varchar2; function to_bin( p_dec in number ) return varchar2; function to_oct( p_dec in number ) return varchar2; function to_36( p_dec in number ) return varchar2; function to_64( p_dec in number ) return varchar2; pragma restrict_references( to_base, wnds, rnds, wnps, rnps ); pragma restrict_references( to_dec, wnds, rnds, wnps, rnps ); pragma restrict_references( to_hex, wnds, rnds, wnps, rnps ); pragma restrict_references( to_bin, wnds, rnds, wnps, rnps ); pragma restrict_references( to_oct, wnds, rnds, wnps, rnps ); pragma restrict_references( to_36, wnds, rnds, wnps, rnps ); pragma restrict_references( to_64, wnds, rnds, wnps, rnps ); end radix; / show errors create or replace package body radix is function to_base( p_dec in number, p_base in number ) return varchar2 is l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(64) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; begin -- base 64 for Oracle extended rowid format if p_base = 64 then l_hex := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; end if; if ( trunc(p_dec) p_dec OR p_dec 0 ) then raise INVALID_NUMBER; end if; loop l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; l_num := trunc( l_num/p_base ); exit when ( l_num = 0 ); end loop; return l_str; end to_base; function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number is l_num number default 0; l_hex varchar2(64) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; begin -- base 64 for Oracle extended rowid format if p_from_base = 64 then l_hex := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; end if; for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num; end to_dec; function to_hex( p_dec in number ) return varchar2 is begin return to_base( p_dec, 16 ); end to_hex; function to_bin( p_dec in number ) return varchar2 is begin return to_base( p_dec, 2 ); end to_bin; function to_oct( p_dec in number ) return varchar2 is begin return to_base( p_dec, 8 ); end to_oct; function to_36( p_dec in number ) return varchar2 is begin return to_base( p_dec, 36 ); end to_36; function to_64( p_dec in number ) return varchar2 is begin return to_base( p_dec, 64 ); end to_64; end radix; / show errors test ol object new_value object col file new_value file col block new_value block col row new_value row select rowid , substr(rowid,1,6) OBJECT , substr(rowid,7,3) FILE , substr(rowiD,10,6) BLOCK , substr(rowid,16,3) ROW , dbms_rowid.rowid_to_restricted(rowid,0 ) RESTRICTED , dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','DUAL') FNO , dbms_rowid.rowid_object(rowid) OBJECT_ID , dbms_rowid.rowid_block_number(rowid) BLOCK_NUM , dbms_rowid.rowid_row_number(rowid) ROW_NUMBER from dual / select radix.to_dec('file',64) FNO , radix.to_dec('object',64) OBJECT_ID , radix.to_dec('block',64) BLOCK_NUM , radix.to_dec('row',64) from dual / Jared Stephen Lee [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Base conversion -Original Message- Stephen, The code I posted earlier is easily adapted to do base 64. Please share your mods. :) - OK. I figured out that A is zero (I think). Now, only 63 more to go! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET:
Re: extents question on LMT
1) No, you can't compress into a single extent if the TBS is LMT with uniform extent size, and the size of the index is extent size. Don't know about auto extent size feature. 2) Why would you want to compress into a single extent? There's no benefit to doing so. Jared Guang Mei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2003 12:06 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:extents question on LMT Hi: Oracle 8173 on Sun 2.8. When we had tablespace created as DMT, I used to occuasionally find all indexes in a schema which have multiple extents and run a script to compress each of them into one single extent (maybe this is not necessary, but that's another topic). But I find lately that with LMT, I can not simply do that. Oracle will decide how many extents it allocates depending on the storage parameters I pass in: ALTER INDEX ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging; Does this mean I just can not compress some large index into a single extent in a LMT? Example: SQL ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 50K NEXT 50K pctincrease 0) nologging; Index altered. SQL select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK'; BYTESEXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT -- -- -- -- --- 516096 1 63 57344 516096 SQL ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging; Index altered. SQL select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK'; BYTESEXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT -- -- -- -- --- 5677056 116935242880 516096 TIA. Guang _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Perl - Was Unix time conversion function
You can always outsource your perl development to me. :) On Tue, 28 Jan 2003, Hately, Mike (NESL-IT) wrote: Perl kind of makes sense but I haven't reached the point where it clicks and becomes natural. I still need to think about it very hard when I'm writing it. Hence, a lot of the time I fall back on shell scripts supplemented by pre-written (some would say shamelessly ripped off) perl code for the stuff that would get really messy is ksh. Regards, Mike Hately -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 28, 2003 1:40 AM To: Multiple recipients of list ORACLE-L Cary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses. Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277 This e-mail may be sent on behalf of a member of the Innogy group of companies. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Alex INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Net connection.
Is there any possible way to retain/reinstate/continue a SQLNet connection if there is say a 10-second network outage? For example: If a session is established and then the network cable is unplugged for 5 seconds and then replaced. Is there anyway to keep that connection alive? Michael Bond OC-ALC\LPRC Oracle DBA 405 736-3840 DSN 336-3840 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bond Mike A Contr OC-ALC/LPRC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Perl
I tinkered with Perl, but could never really get used to the syntax. I basically gave up (still maintain familiarity since Perl is very common) and started using Python. I've grown to enjoy coding in Python and use it now for all of the system maintenance and monitoring scripts I write as well as for my web programming work. I'm not qualified to compare the two languages, but I will say that Perl's Oracle support is better developed and the CPAN archives are a very useful thing. In my opinion, Python is a better designed language and it is perfectly viable for production-level applications in an Oracle environment. -- Glenn Stauffer Swarthmore College Swarthmore, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Perl - Was unix time conversion function
Title: RE: Perl - Was unix time conversion function Are you sure? It's written in C and is supposed to be relatively portable. I've not tried it on other platforms but, in addition to HP-UX and AIX, Python runs on: AS/400; Solaris; OS/2; Amiga; AROS; BeOS; PalmOS; QNX; VMS; VxWorks; Sony PlayStation; Sharp Zaurus; the MacIntosh, DOS; and all the Windows versions including WindowsCE. Then there's Jython, the Java implementation. Python comes packaged with most good O/S's. ;-) While Python doesn't have mindshare in the sysadmin community that's changing with the current exponential growth. Many PerlMongers have become Python converts and Pythonistas. Here's a quote: While the rest of the world has been catching on to the Perl scripting language, the Linux community, long since past the pleasing shock of Perl's power, has been catching on to a different scripting animal -- Python. http://www.linuxworld.com/linuxworld/expo/lw-python.html http://www.linuxjournal.com/article.php?sid=3882 To know Python is to love Python... maybe some day the sneaky-snake will sneak up on you and the teeth of the hydra will be upon ya. :-) Steve -Original Message- From: Gogala, Mladen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: Perl - Was unix time conversion function I haven't done anything with Python but my problem with Python or Ruby is that they don't come with many OS variants. Perl comes with any OS and there is a huge repository of ready made scripts. I have to confess stealing some from Jared's PDBA collection. I cannot do that with Python and then run them on both HP-UX and AIX. Therefore, I'll stick to the pathologically eclectic rubbish lister. -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Perl - Was unix time conversion function If you can do it in Perl chances are you can do it better in Python and someone can actually read and understand it without a PHD... AKA Perl Helper Dweeb. :-) In Perl it takes effort to write readable code but it comes naturally in Python. Check it out: www.python.org/doc/essays/blurb.html http://www.orbtech.com/web/python/why http://www.networkcomputing.com/unixworld/tutorial/005/005.html#Others http://www.developer.com/open/print.php/610691 Steve Orr Oracle DBA and part-time Python Evangelist ;-) -Original Message- From: Keith Moore [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Perl - Was unix time conversion function I've started writing some perl and it is hard to learn, but once you learn, it can do some great things. And if you learned it from the Larry Wall book like I did, then it's even harder. The thing I've discovered about perl it that it may be the only language (computer or otherwise) that is easier to write than it is to read. I'm sure a perl expert would gasp at my code, it's far too readable. Keith - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 10:09 AM please don't take umbridge, but I feel enticed to quote what you don't know, dosn't (really) matter Larry Wall, programming with perl, O'Reilly. just for a giggle. sorry apologies for any typos overlooked kr mr [EMAIL PROTECTED] 01/28/03 12:22 PM I've managed to successfully avoid learning Perl for a while now... my reaction, while not quite so dramatic as yours, was that it made my head hurt to try to understand it! :) --- Robert Freeman [EMAIL PROTECTED] wrote: unix time conversion functionCary I once thought I wanted to do some Perl coding... So I bought a book and started to play with it. It made my head bleed... literally I had little droplets of blood emerging from my head They rushed me to the hospital and put me in the Perl ward where I languished for days on IV's of Mountain Dew and pulverized Ritz crackers. it was close. In my mind there is nothing obvious about Perl, this coming from and old C coder who did pointers and linked lists in his sleep years ago. I don't know, maybe I was having a bad day and it's time to get my learning Perl book out again Anyone else feel that way about Perl or am I a lone wolf in a Perl world? RF
RE: Peoplesoft Oracle
Title: Peoplesoft & Oracle Welcome Lisa, I've got PeopleSoft with 8.1.7 and AIX also (peoplesoft 8.4). It's not quite the dark side, more like Dante's Inferno. There are a number of websites and lists, but I haven't found any to be all that useful. A lot of the problems you mention arise because PeopleSoft doesn't really understand databases, they provide the CREATE scripts, and a lot of people run them with no (or just minor) modifications. There are also lots of levels (webserver, tuxedo (including application server and process scheduler server), jolt, OS, database). Most of the issues I've seen have nothing to do with the database. (and you think you've had problems with Oracle support? heh, heh.). I'll be glad to tell you more, (or just rant). Let me know. Henry -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Koivu, LisaSent: Tuesday, January 28, 2003 4:09 PMTo: Multiple recipients of list ORACLE-LSubject: Peoplesoft Oracle Hello everyone, My apologies for bombarding the list with more questions than answers as of late. This one is pretty desparate. For those of you who support Peoplesoft databases - Are there any websites that you frequent to check for quirks, weird anomalies, etc. related to your specific Peoplesoft/Oracle combination? I've just inherited this environment. It's Oracle 8.1.7 on AIX. I don't know the version of AIX and I don't know the version(s) of the Peoplesoft modules being used. However I am seeing some very weird things - not the least of which is every tablespace is dictionary managed, all are on autoextend, there are 600 open database accounts but it looks like the app only connects as SYSADM, all users have SYSTEM for default/temp ts, the entire freaking database is on one disk, all redologs (one in each group) are on one disk, etc., etc., etc. The list goes on. I'm afraid to turn this environment upside down and fix it because management is only interested in keeping the system alive, not improving it. The previous DBA is elusive at best, probably because I yelled at him last year after catching him creating objects on the fly in my databases :) I'll do the obvious things like add redologs and check backup and recovery, etc. User error is extraordinarily high in this environment. Everything else I am afraid to touch without doing my homework first. Any websites, suggestions, comments, etc. would be greatly appreciated. The only good thing to come of all this is I'M BAACK in Unix again !!! Woo Hoo!!! Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Re: Global Stats
If I remember right, global stats are computed/inferred from partition level stats if its available. If thats the case, then it may make sense to compute/estimate the stats at the partition level. You also have the advantage of being able to run analyze each partition in parallel. Regards, Denny Quoting Koivu, Lisa [EMAIL PROTECTED]: Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Net connection.
If using TCP/IP, you don't have to do anything. 10 seconds is short enough time for the connection to still be alive. -- Lyndon Tiu Quoting Bond Mike A Contr OC-ALC/LPRC [EMAIL PROTECTED]: Is there any possible way to retain/reinstate/continue a SQLNet connection if there is say a 10-second network outage? For example: If a session is established and then the network cable is unplugged for 5 seconds and then replaced. Is there anyway to keep that connection alive? Michael Bond OC-ALC\LPRC Oracle DBA 405 736-3840 DSN 336-3840 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bond Mike A Contr OC-ALC/LPRC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN or SQLBacktrack?
We have been using SQLBacktrack to backup our databases on Unix and NT for several years. We have been VERY please with the product. We have a mixture of Oracle 8.1.7 and 7.3.4 databases. We are being pushed to use RMAN because it is free. Does anyone have any experience with both and be willing to share their experiences? Thanks! R. Smith If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
books recommendations
We are buying books for DBA group and developers . It will be great if u can provide ur feedback about following books: Oracle SQL High-Performance Tuning (2nd Edition) by Guy Harrison High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability [DOWNLOAD: ADOBE READER] by Geoff Ingram Oracle9i High-Performance Tuning with STATSPACK by Donald K. Burleson, Don Burleson Oracle9i RMAN Backup Recovery by Robert G. Freeman, Matthew Hart Oracle 9i New Features by Robert G. Freeman Oracle9i DBA Handbook by Kevin Loney, Marlene Theriault Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Net connection.
If you are using TCP/IP, then yes. It's highly dependent on your OS TCP/IP stack though. I tried it with Linux and it seems fine. -- Lyndon Tiu Quoting Bond Mike A Contr OC-ALC/LPRC [EMAIL PROTECTED]: Is there any possible way to retain/reinstate/continue a SQLNet connection if there is say a 10-second network outage? For example: If a session is established and then the network cable is unplugged for 5 seconds and then replaced. Is there anyway to keep that connection alive? Michael Bond OC-ALC\LPRC Oracle DBA 405 736-3840 DSN 336-3840 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bond Mike A Contr OC-ALC/LPRC INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Peoplesoft Oracle
You could try the following, set up by David Kurtz who has been specialising in Peoplesoft on Oracle for several years. web: www.go-faster.co.uk PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 January 2003 22:30 Hello everyone, My apologies for bombarding the list with more questions than answers as of late. This one is pretty desparate. For those of you who support Peoplesoft databases - Are there any websites that you frequent to check for quirks, weird anomalies, etc. related to your specific Peoplesoft/Oracle combination? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Advice needed on PL/SQL code
Hi all, Please forgive this newbie question; I'm just getting started with PL/SQL. I want to write a procedure to copy all rows from one table to another one with an identical structure. The table has many rows so I'm committing every thousand records (error handling to be added later). The table has 50+ columns and (if possible) I'd like to avoid listing them all in the VALUES clause. I'm not just being lazy - I hope to deploy this code to several databases and the source table, while having the same name, may have differences in the columns. I want to do something like this: DECLARE count_ NUMBER; CURSOR get_archive_records IS SELECT * FROM customer_order_table; BEGIN count_ := 0; FOR rec_ IN get_archive_records LOOP INSERT INTO customer_order_archive VALUES (rec_.*); -- OBVIOUSLY, THIS DOES NOT WORK count_ := count_ + 1; IF MOD(count_,1000) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; Is there a way to accomplish this? Or is my whole approach all wrong? Thanks in advance, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Beth Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Peoplesoft Oracle
Lisa, Sounds like a default install of PeopleSoft. Step 1 that you can do is re-assign everyone to use a real temp tablespace. Step 2 is break up that db onto seperate spindles. Step 3 is to turn off autoextend exept for those tablespaces that are nearing say 90% full, then set autoextend to something sensible. As far as the user accounts are concerned that's normal. I for one would revoke DBA from sysadmin grant alter any user instead. Afterwards, I'd get into PeopleTools turn off the creat user function. I could write more, but this should keep you VERY busy. Dick Goulet Koivu; Lisa [EMAIL PROTECTED] wrote on 1/28/03 1:08 pm: Hello everyone, My apologies for bombarding the list with more questions than answers as of late. This one is pretty desparate. For those of you who support Peoplesoft databases - Are there any websites that you frequent to check for quirks, weird anomalies, etc. related to your specific Peoplesoft/Oracle combination? I've just inherited this environment. It's Oracle 8.1.7 on AIX. I don't know the version of AIX and I don't know the version(s) of the Peoplesoft modules being used. However I am seeing some very weird things - not the least of which is every tablespace is dictionary managed, all are on autoextend, there are 600 open database accounts but it looks like the app only connects as SYSADM, all users have SYSTEM for default/temp ts, the entire freaking database is on one disk, all redologs (one in each group) are on one disk, etc., etc., etc. The list goes on. I'm afraid to turn this environment upside down and fix it because management is only interested in keeping the system alive, not improving it. The previous DBA is elusive at best, probably because I yelled at him last year after catching him creating objects on the fly in my databases :) I'll do the obvious things like add redologs and check backup and recovery, etc. User error is extraordinarily high in this environment. Everything else I am afraid to touch without doing my homework first. Any websites, suggestions, comments, etc. would be greatly appreciated. The only good thing to come of all this is I'M BAACK in Unix again !!! Woo Hoo!!! Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 6.0.6249.1 TITLEPeoplesoft amp; Oracle/TITLE /HEAD BODY !-- Converted from text/rtf format -- PFONT SIZE=2 FACE=ArialHello everyone, /FONT /P PFONT SIZE=2 FACE=ArialMy apologies for bombarding the list with more questions than answers as of late.nbsp; This one is pretty desparate. /FONT /P PFONT SIZE=2 FACE=ArialFor those of you who support Peoplesoft databases - Are there any websites that you frequent to check for quirks, weird anomalies, etc. related to your specific Peoplesoft/Oracle combination?nbsp; /FONT/P PFONT SIZE=2 FACE=ArialI've just inherited this environment.nbsp; It's Oracle 8.1.7 on AIX.nbsp; I don't know the version of AIX and I don't know the version(s) of the Peoplesoft modules being used.nbsp; However I am seeing some very weird things - not the least of which is every tablespace is dictionary managed, all are on autoextend, there are 600 open database accounts but it looks like the app only connects as SYSADM, all users have SYSTEM for default/temp ts, the entire freaking database is on one disk, all redologs (one in each group) are on one disk, etc., etc., etc.nbsp; The list goes on.nbsp; I'm afraid to turn this environment upside down and fix it because management is only interested in keeping the system alive, not improving it.nbsp; The previous DBA is elusive at best, probably because I yelled at him last year after catching him creating objects on the fly in my databases :)/FONT/P PFONT SIZE=2 FACE=ArialI'll do the obvious things like add redologs and check backup and recovery, etc.nbsp; User error is extraordinarily high in this environment.nbsp; Everything else I am afraid to touch without doing my homework first.nbsp; Any websites, suggestions, comments, etc. would be greatly appreciated.nbsp; /FONT/P PFONT SIZE=2 FACE=ArialThe only good thing to come of all this is I'M BAACK in Unix again !!! Woo Hoo!!!/FONT /P PFONT SIZE=2 FACE=TahomaLisa Koivu/FONT BRFONT SIZE=2 * This message has been truncated. The entire message is available on your desktop e-mail client. -- 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
Re: Perl - Was unix time conversion function
On Tue, Jan 28, 2003 at 09:59:46AM -0800, Orr, Steve wrote: If you can do it in Perl chances are you can do it better in Python and someone can actually read and understand it without a PHD... AKA Perl Helper Dweeb. :-) In Perl it takes effort to write readable code but it comes naturally in Python. Check it out: www.python.org/doc/essays/blurb.html http://www.orbtech.com/web/python/why http://www.networkcomputing.com/unixworld/tutorial/005/005.html#Others http://www.developer.com/open/print.php/610691 If you can do it in Perl chances are someone already has... Check it out: http://search.cpan.org Just search for something... Tim. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Bunce INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).