Re: Cold Fusion and Bind Variables
Jared, Try this link. It still worked for me this morning. http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html Scott >>> [EMAIL PROTECTED] 1/12/04 8:49:25 PM >>> Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
Re: Re: Little competition
Hey Richard, One of my desktop treasures is an autographed copy of Jonathan Lewis' book, Practical Oracle8i. The humorous, self-deprecating caption in the author's own hand: 'Never believe all you read.' Scott >>> [EMAIL PROTECTED] 12/12/03 7:24:24 AM >>> Hi Jonathan,SQL> create tablespace bowie_test 2 datafile 'c:/bowie/bowie_test01.dbf' size 100m 3 extent management local uniform size 1m 4 segment space management auto;Tablespace created.SQL> create table bowie_assm (ziggy number) 2 tablespace bowie_test 3 storage (initial 1m next 2m pctincrease 100 minextents 3);Table created.SQL> select owner, segment_name, blocks from dba_extents 2 where segment_name = 'BOWIE_ASSM';OWNER SEGMENT_NAME BLOCKS-- --BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 1287 rows selected.3 minextents = 1 initial + 2 next + (2 next + (2 * 1.0 pctincrease) = 7 * 1MextentsIt's actually quite a common misconception that NEXT, PCTINCREASE andMINEXTENTS are ignored for locally managed tablespaces when in fact they'reused to determine the initial size of the object and hence the number ofextents initially allocated.This was all a bit of fun but I think it did prove my little (mischievous)point. That it's really quite easy to base ones belief and certainty on a"fact" that turns out to be totally false because the basis on why youbelieve something also turns out to be false. On the surface it appeared tobe quite a reasonable conclusion, that pctfree is not permitted with ASSMobjects because the "evidence" strongly supported such a claim.Unfortunately the evidence was somewhat erroneous in that it stupidly reliedon incorrect syntax and so an incorrect conclusion resulted. This incorrectconclusion can then result in inappropriate behaviour and curses from DBAsas they experience all these "unavoidable" migrated rows. Before you knowit, other Oracle myth is born ...Of course everyone makes mistakes but to publish them does come with it'sown set of responsibilities. I can't stress enough that one be careful of"what" you read and be careful of "who" you read.The truth IS out there ;)CheersRichard> > This could be a serious issue for the Oracle professional unless they> remember that locally-managed tablespaces with automatic space management> ignore any specified values for NEXT and FREELISTS.>> >> There is another error here.> For a bonus 10 points can anyone spot it ?>> Hint - try the following in a tablespace> which is locallally managed, with automatic> space management, and either system managed> or uniform sized extents of no more than 1 M.>> create table test2(n1 number)> storage (initial 1M next 2M pctincrease 100 minextents 3);>> Regards>> Jonathan Lewis-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Richard Foote INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: DBV Cannot Load Module LIBDCE.A
Sam, I've never worked in a DCE environment, but here's a couple of things to verify: 1. If libdce.a is not installed in the first place, is your environment using DCE? I think this is an LPP (a separately licensed product) from IBM for AIX. Try the following from a unix prompt: $ lslpp -L |grep -i dce I think you should get hits like dce.client.rte.pthreads If you don't, then I wonder why dbv thinks it needs it... Try the following just to make sure you're running the dbv you think you are: $ which dbv It should return $ORACLE_HOME/bin/dbv, for example: $ which dbv /u01/app/oracle/product/8.1.7/bin/dbv If it returns the Oracle 'dbv', then you might check the $ORACLE_HOME/rdbms/lib/sysliblist to see if it has an entry for libdce. If so, the product was linked at some point with the DCE Advanced Networking Option (see MetaLink Note 1043700.6 This doc notes the following: NOTE: You may have to manually remove the library reference from "sysliblist". In Oracle 7.3.4, the Installer did not remove the library reference during a deinstallation.) In any case, I would be slow to add PTFs or relink or make other changes in a production environment just for the sake of dbv (if the rest of the environment is stable) until I had a better understanding of the opsys environment and the Oracle installation history. HTH, Scott >>> [EMAIL PROTECTED] 3/12/03 5:15:58 PM >>>Hi all,First, thanks Jared for the info.The libdce.a file does not exist on any of our AIX servers. It justAINT there!I typed libdce.a into google and from there downloaded PTF2,3, and 4that are supposed contain DCE fixes to this file. Instructions say torename the old file and replace it with this new file. After the download, I was not able to uncompress the files on my XPworkstation. So I ftp'd the file (in binary mode) to our AIX server andused uncompress to expand the files to libdce2.exe, libdce3.exe,libdce4.exe (originally the files were named libdce2.exe.Z, etc.). The.exe extension confused me (I am told .exe is Windows only, not UNIX). I ftp'd the files back to my workstation, and tried running it from DOS.I get an error message "Program too big to fit in memory". However, theexe is only 217,856 bytes (not kilobytes). My next step was to ask myneighbour if he could unzip the original libdce2.exe.Z on his W2Kworkstation - he was successful! However, he received the same messagewhen he attempted to run the resultant .exe on his workstation. I am relatively new to UNIX (took a course, and did some reading, butnot much hands-on). I am told by a more experience colleague that.exe's do not run on UNIX. So now I am at a loss on what to try next. Any ideas?Thanks,Sam BootsmaGeorge Brown College[EMAIL PROTECTED]416-415-5000 x4933-Original Message-Sent: March 11, 2003 7:14 PMTo: Multiple recipients of list ORACLE-LJust found it: on AIX ( 4.3 at least ) it's LIBPATH[EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/11/2003 03:23 PMPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L<[EMAIL PROTECTED]> cc: Subject: Re: DBV Cannot Load Module LIBDCE.AMake sure LD_LIBRARY_PATH includes /usr/lib.At least, I think it's LD_LIBRARY_PATH, been awhilesince doing anything on AIX. In any case, libdce.ashould be in /usr/lib.JaredPS. Google is your friend"Sam Bootsma" <[EMAIL PROTECTED]>Sent by: [EMAIL PROTECTED]03/11/2003 01:44 PMPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: DBV Cannot Load Module LIBDCE.AWe are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version 2.3.4.0.0. When I attempt to run DBV I get the following error messages$ dbv help=yexec(): 0509-036 Cannot load program dbv because of the followingerrors: 0509-150 Dependent module libdce.a(shr.o) could not be loaded. 0509-022 Cannot load module libdce.a(shr.o). 0509-026 System error: A file or directory in the path name doesnot exist.$I do not want to play around with a custom installation to try toinstall missing components because this is a production database and because Iam new here. The regular DBA is on holidays and not available. I did a search for file "libdce" on the original CD for "Oracle7.3.4.0.0 for AIX based systems", but did not find it there. I considered installing Oracle 7.3.4 on my workstation and ftp'ing the files over thenetwork to my workstation. Then doing the database verify. But total size of the data files is 25 GB, and this would unnecessarily clog upour network (even at night).Any ideas on how I can resolve the DBV problem not loading problem? Theshort answer is to find the module libdce.a file and any modulesdependent on this, but how do I do this?Thanks,Sam [EMAIL PROTECTED]
Re: Purely for your amusement
Thanks! I especially liked: TABLE...FULL OF 'WOE' That seems to be how the day is going for several of us. Scott >>> [EMAIL PROTECTED] 2/27/03 1:23:55 PM >>>And you thought your job was bad . . .(Yes, this IS a production job)-- 22 21 TABLE ACCESS (FULL) OF 'WOE'
Re: rootpre.sh
David, I haven't installed Oracle 8.1.7 or 9.2 to AIX 5.1 yet, but thanks to a data storage interrupt kernel panic on a 5.1 box, I do know that 'crash' has been replaced by 'kdb' for examining crash dumps. So, rootpre.sh is going to break on the line 'PKEXT_PATH='echo le | crash | blah blah blah...' I think the question is - do you need the post-wait kernel extension at AIX 5.1? I don't know about Oracle 8.1.7, but the 9.2 Installation Guide (p. 2-35) says: "For Oracle9i 9.2.0.1.0 on AIX 5L, the functon and performance benefits of the Oracle post-wait kernel extension are incorporated into the AIX kernel. Oracle 9i 9.2.0.1.0 on AIX 5L does not require the Oracle post-wait kernel extension to be loaded." (This begs the question as to whether I still would need to run rootpre.sh for asyncronous I/O configuration. Presumably, I could use smit to configure async I/O.) It seems to me I read somewhere that 8.1.7 still requires a 32-bit kernel extension and therefore, you may not be able to run a 64-bit AIX kernel with 8.1.7. Scott >>> [EMAIL PROTECTED] 2/11/03 12:59:53 PM >>>I am trying to run rootpre.sh. My admin just upgrade the kernel to 64-bit.We were running 32-bit on this box and everything was fine. No Oraclesoftware is running. I am going to install 64-bit 8.1.7 Ent Ed for AIX 5.1Lwhen I got this trying to run rootpre.sh.This is what I get when I run:#./rootpre.sh Installing kernel extension... Oracle Kernel Ext... Copyright Kernel Extension Version: 3 nkext ver: 3 ./rootpre.sh[199]: crash: not found. is not loaded : 1 No post-wait kernel is loaded on the machine. Copying new kernel extension. Oracle Kernel Extension Loader for AIX Copyright. Kernel Extension Version: 3 SYS_SINGLELOAD: Exec format error kmid: 0 (0x0) path '/etc/pw-syscall' libpath:'' Configuring Asynchronous I/O... Asynchronous I/O is already defined thanks,David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Ehresmann, David INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: AIX 4.3.3 / 8.1.7 & Timed Statistics & Tuning Question
Lisa, I have had TIMED_STATISTICS set to true on 5 different 8.1.7 databases (4 at 8.1.7.4 (64-bit), one at 8.1.7.0 (32-bit)) on 3 different servers for some time with no perceived problems. These servers are either at AIX maintenance level 9 or 10. I have struggled with swap space consistently growing until memory is exhausted on one of these servers. It is at AIX 4.3.3 maintenance level 9. There is a memory leak in fclose() at this level that I think is causing the problem. Refer to APAR IY22308. You don't mention whether the databases are on jfs file systems or not. If so, be aware that the 'fre' column of the 'vmstat' command will typically be small, since some real memory is used as a cache for file system data. (And the virtual memory management page stealer takes a lazy approach, only stealing to keep the free list above the 'minfree' value.) IBM has a white paper on paging space that may help: http://www-1.ibm.com/servers/aix/products/aixos/whitepapers/pagingspace.pdf Also, the following commands with give the top 5 users of memory and paging space, respectively: svmon -Put 5 svmon -Pgt 5 Haven't worked with PeopleSoft, so I can't speak to its effect. Hope this was relevant, Scott >>> [EMAIL PROTECTED] 2/6/03 9:23:58 AM >>>Good morning everyone - Quick poll for those of you on 8.1.7 and AIX 4.3.3: Do you have TIMED_STATISTICS = true? Have you encountered any problems with it? The databases I inherited have this set false all over the place, hence my tuning efforts are really limited. However I don't want to change it without checking around first. And a tuning question: This environment (peoplesoft) is very very low on memory. When the app servers and databases are up there's less than 50MB of memory free. Adding hardware is not a choice here. The databases have 100MB set for the SGA. It really looks like not much thought went into some of the parm settings. What I've read about tuning says that you must have a goal in mind. Well, afaik nothing is "broken", nothing is suffering - then again, no one really paid much attention to Oracle. It was up, fine, move on. Am I on the wrong path if my goal for tuning is to figure out if I can reduce the size of the SGA and redo logs without adversely affecting performance? Any comments are appreciated. Thanks everyone
Re: Hotsos Clinic
Keith, I had the great good fortune to be able to attend the Hotsos Clinic in March, 2002 and then, in November, to attend Jonathan Lewis' seminar. Best investments in Oracle I ever made. Scott Behrens>>> [EMAIL PROTECTED] 1/2/03 1:41:45 PM >>>I have an opportunity to attend a Hotsos Clinic. It seems I have heard good things about them on this list, but I thought I might double-check. Is this 3-day class worthwhile or is it an expensive way to sell their product? Will this class be beneficial, even if we don't buy their product?Keith--
Re: TNS-00510: Internal limit restriction exceeded
Funny you should mention... We had the same problem with an 8170 64bit db on AIX 4.3.3 yesterday at about 14:30. Paging space had become exhausted. In brief, I'm guessing the rash of memory leaks in 8170-8172 (or the temporary fix I did until I can patch to 8174) is the cause. Details below: The box is an M80 with 4GB main memory, 1GB paging space. Two dbs, db1 has sga of ~530M, db2 has sga of ~180M. When I looked at the server, paging space usage was at 97%. Trying to run commands at the unix prompt generated the following: ksh: 0403-031 The fork function failed. There is not enough memory available. To protect itself, the opsys had aparently also killed a few processes as evidenced by a handful of PGSP_KILL errors in the system error log. As users and developers (don't ask) began to bail off, paging space usage dropped to 86% and we were able to maintain connectivity until a (previously scheduled) maintenance window yesterday evening. Here is how the paging space looked as we brought the dbs down: Before either db is down:# lsps -a Page Space Physical Volume Volume Group Size %Used Active Auto Typehd6 hdisk0 rootvg 1024MB 86 yes yes lvAfter db2 is brought down (this indicates db2 with sga of 180M had 185M of paging space):# lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Typehd6 hdisk0 rootvg 1024MB 68 yes yes lvAfter db1 is brought down (this indicates db1 with sga of 530M had 481M of paging space): # lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Typehd6 hdisk0 rootvg 1024MB 21 yes yes lvAfter the reboot, before any dbs are up:# lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Typehd6 hdisk0 rootvg 1024MB 1 yes yes lvAfter the two dbs are back up:# lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Typehd6 hdisk0 rootvg 1024MB 1 yes yes lv I had kicked the shared pool up a good bit on db1 12 days earlier along with a couple of other init parm changes to deal with ORA-04031 errors due to the memory leaks. Here are the relevant init parm entries (the _db_handles_cached parm will impact performance): # ORA-04031 errors with BAMIMA upon login. Until# patched to 8.1.7.4, kick up shared_pool, make shared_pool_reserved_size =10-15%# and try to bounce the db on occasion. Add large_pool area for parallel query.# Finally, added _db_handles_cached=0 to keep from hitting one memory leaking bug.# Remove this parm after patched to 8.1.7.4_db_handles_cached=0shared_pool_size = 400M shared_pool_reserved_size = 60M # 10-15%ofshared_pool_size = 60M large_pool_size = 20M # start at 20, maybe go to 40 if ok I will probably cut back on the shared pool until I can get this patched to 8174 (and monitor paging space to bounce the dbs when needed). HTH, Scott >>> [EMAIL PROTECTED] 10/4/02 10:38:31 AM >>>HI all We had those messages yesterday in the listener.logfile TNS-12500: TNS:listener failed to start a dedicatedserver processTNS-12540: TNS:internal limit restriction exceeded TNS-12560: TNS:protocol adapter error TNS-00510: Internal limit restriction exceededAlso on the unix side, we had a message about the OSthat can not fork a new process.This is on 8172 32bits/AIX 4.3.3 The sga is 1.7G, the server has 8G of ram.There is between 150 and 300 users connected.The init.ora process parameter is set to 425. The unixnumber of process allowed is set to 500.I've check on metalink, but found nothing that we donot already do.Any ideas ?Thanks=Stéphane PaquetteDBA Oracle, consultant entrepôt de donnéesOracle DBA, datawarehouse consultant[EMAIL PROTECTED]
