Re: How to apply patch to Solaris for Intel
Dave, zip files can be handled on many platforms these days and I'd hope that Intel Solaris can process them if they're zipping system patches. Try which unzip to try to locate the executable or look for a man entry. I can unzip files on Sparc Solaris and AIX so I suspect you won't have a problem. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle error after recovery
Ron, Check that the instance lock file LK isn't still present in your $OACLE_HOME/dbs directory. It's there to stop a database being mounted twice and in the case of a database crash there's a good chance that it's been left lying about. Cheers, Mike Hately Oracle DBA PS 1st email to the list from this site (which uses a Lotus Notes email client). Can someone tell me is it sending html or text? I don't want to flood the server with huge messages. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: deletion of data from a large table
Hi folks, Rick Cale was correct in saying that a truncate would release the storage by default. The only other option is to drop and recreate the table. Oracle will only release allocated storage when it is specifically asked to. The overhead on having a process monitoring which space it can drop would be prohibitive and needless. ( Imagine an example where an interface table is constantly filled and emptied - do you really want the storage to be constantly allocated and deallocated? ). Coalescing the tablespace will group adjacent free extents together but will have no effect on allocated blocks. Regards, Mike Hately, Oracle DBA |+-- || Ramasamy, Baskar | || Baskar.Ramasamy@cal| || anais.com | || | || 07/26/01 01:17 PM | || Please respond to | || ORACLE-L| || | |+-- -| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED]| | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: deletion of data from a large table | -| Rukmini, Try to do coalesce on the tablespace on which the table is sitting in after deleting rows. alter tablespace TSNAME coalesce; Baskar -Original Message- Sent: 26 July 2001 12:41 To: Multiple recipients of list ORACLE-L Hi All, I have deleted 3 lakhs records from a large table. But there is no effect on tablespace i.e. before I delete the data freespace in TS is 100MB , after deletion also it is showing 100MB. What could be the reason ? How to get the freespace after deleting the data ? Thanks rukmini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rukmini Devi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramasamy, Baskar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Solaris 8 Question
Sam, you may find that you need to issue archive log stop and archive log start to force the database to start archiving again. Once it's failed to archive a log it seems to lose heart and give up until you nudge it. Ironically the ARCHIVER will keep writing to the alert log to say that it's still trying. But it isn't. No doubt it's a feature. I've seen this on AIX, HP, Solaris, you name it. Regards, Mike Hately |+ || Sam Roberts | || rabbit@emirat| || es.net.ae| ||| || 07/24/01 04:05| || PM| || Please respond| || to ORACLE-L | ||| |+ | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Solaris 8 Question | | This happened to me today: Solaris 5.8 we have a large data load exercise going on and the archive log directory filled - database stopped of course. but after I had moved all the archive logs to another location the system still hung - I tried manual switching of log file ,still hung. I had to stop and start instance - I was surprised. did I miss something ? sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: get yesterdays date
I'll save you the trouble John; it was Sunday June 22nd. Sossy, couldn't resist. Alternatively you can select SYSDATE -1 from DUAL. Cheers, Mike Hately |+--- || John Dunn| || john.dunn@se| || fas.co.uk | || | || 07/23/01 | || 12:35 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: get yesterdays date | | Anyone know how to get yesterdays date in PL/SQL? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to download website
WebReaper isn't bad. NetVampire is OK too. Some site will detect this kind of activity and will kill your connection. Regards, Mike Hately |+--- || prasad | || maganti | || prasadm_g@ya| || hoo.com | || | || 07/23/01 | || 03:46 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: How to download website | | hi dba's is there anyway to download complete website including links in a single shot. i got this problem, when i need to copy many html pages, that are linked to a single site. if i need to copy them, i hv to open every file and have to say save from windows. so can anybody tell me easy way of finishing my task. i need to copy 500 pages from that site. thanx in advance prasad __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: prasad maganti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent Select statement SOS
The only way you can do this is to store SYSDATE into a column in the table when you insert the row and to use that column value to order the rows when you SELECT from the table. Yoiu can generate the date from an ON INSERT trigger. Cheers, Mike Hately Oracle DBA -Original Message- Sent: Thursday, July 19, 2001 8:55 AM To: Multiple recipients of list ORACLE-L Hi all, Do you guys know how to select column order by time of insert into table. Thank you, Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i RAC's
Early publicity for RACs specifically said that it was an OPS upgrade and made great play of cache fusion which was already available in 8.1.7. I agree with the previous point that OPS was often thrown at applications that were badly suited and so customers perceived it as a poor technology. RAC seeks to improve over OPS by giving it the capability to scale any application, whether or not it's specifically designed to run in a parallel environment. Whether they've succeeded yet is still to be discovered. Regards, Mike Hately -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Linux Download and ADSL
UK telecomms is in a hell of state. For most of us the best option is a fast connection at work and a CD writer. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to execute Unix Command/program within a pl/sql block.
Hi, you can't do this from PL/SQL but you can do it from Java and it's fairly easy too. I'm not able to check the name of the class tht does it but armed with the phrase java os command and either the Metalink or Ask Tom web site you should be able to track it down. Regards, Mike |+--- || Stephane | || Faroult | || sfaroult@ori| || ole.com | || | || 07/10/01 | || 12:20 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: Multiple recipients of list | | ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: How to execute Unix | | Command/program within a pl/sql block. | | Seema Singh wrote: Hi How to execute unix command in PL/SQL ? Thanks in advance. -Seema You can't. The nearest you can get is to write a daemon program which waits for commands on a (dbms) pipe, executes them (popen(), while(fgets()) {}, pclose() in C) and feeds the output back to the pipe. Needless to say, it can be a serious security gap if you do not screen the commands, since you are likely to execute them with the privileges of the user under which the said program is run. Many moons ago, there used to be something named 'flex' developed by Oracle consultants and freely available on the web which was more or less doing that. It may or may not still be around. I have had a look at it after having developed my own (in Pro*Fortran and under VMS, nothing stops me - no pipe, but a /OUTPUT=... was appended to the command and I was reading and sending back the ouput file) and I can tell you that Flex was unnecessarily complicated. To make simple seems very difficult to many people. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OFA (Optimal Flexible Architecture) in practice
Hi, If you can cd to a single directory and list all of your files then it means that all of your files are in a single filesystem probably (unless you've been VERY clever) sharing the same disks. I agree with the suggestion that you can create links to the datafiles from a central directory but that needs maintenance for each file added. If you have an OFA compliant structure then ls /u*/oradata/db_name1/*.dbf will give you the names of your datafiles. Regards, Mike Hately |+ || Lord, David -| || CS | || David.Lord@ha| || yscsg.com| ||| || 07/06/01 09:45| || AM| || Please respond| || to ORACLE-L | ||| |+ | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: OFA (Optimal Flexible Architecture) in practice | | Well, you could always put a directory of symbolic links under your $ORACLE_BASE. Regards David Lord -Original Message- Sent: 06 July 2001 01:25 To: Multiple recipients of list ORACLE-L Does anyone use OFA as their company standard? If so, are there any complaints about it's structure? For example, the commingling of different database subdirectories under a given mount point? e.g.. /u02/oradata/db_name1/userdata01.dbf /u02/oradata/db_name2/userdata01.dbf /u03/oradata/db_name1/userdata02.dbf /u03/oradata/db_name2/userdata02.dbf You cannot cd to a given subdirectory, ls -ltR | more to see all the datafiles associated with a given database. Instead, from / you have to ls -ltR | grep db_name1 to scan the full file system. Seems like there should be a better way. Any input, pro or con, is appreciated. Thanks, Linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: can i place online redologs with oracle engine ?
Hi, There's very little I/O done to the ORACLE_HOME unless you've chosen to store and database files under it. As for Unix, it would depend on how your filesystems are alocated. Steer clear of /tmp and you should be OK but don't let anything fill up any 'OS' filesystems such as /, /var etc. Cheers, Mike Hately -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle9i on Suse Linux
Jonathan, I saw almost exactly the same thing with 512MB , 512MB swap, Suse Linux 7.1 also.. Some stuff that springs to mind: Did you dynamically change the SHMMAX setting before the install and if so, did you make the same change once the machine had rebooted? When you're prompted for the JDK location don't you specify the location of the Blackdown JDK files? This is from memory with no way to check so excuse any mistakes =) Regards, Mike |+--- || Jonathan | || Gennick | || jonathan@gen| || nick.com| || | || 07/06/01 | || 03:35 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Oracle9i on Suse Linux | | I installed Oracle9i on Suse Linux 7.1 (384MB RAM, 400MB swap), and ran into a bit of a snag. The install ran fine until the end when the database creation assistant attempted to start the seed database. At that point I received an ORA-03113: End of File on Communication Channel error, and my Linux box froze up. Having Linux freeze up is a pretty rare event in itself, but I'm really wondering what caused the error. After rebooting, I was able to run the database creation assistant, create another database, and start that one. I then tried to manually start the seed database, and received the same ORA-03113 error. Could this somehow be related to memory? Was it too much to have the installer and the creation assistant running at the same time? A couple of other oddities I noticed: During the install, I was asked to specify a location for a JDK. I dutifully created a directory and typed in its path, but the installer never actually put any files into it. Why did it ask me for a directory that it wasn't going to use? When I run the oraenv script, it doesn't create an ORACLE_HOME environment variable. Instead of adding $ORACLE_HOME/bin to my path, it adds the full path to the bin directory. Is $ORACLE_HOME going away? Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle9i on Suse Linux
Jared, Oracle 9i is certified with Suse 7.1 out of the box. It specifically needs the 2.4.4 kernel and glibc 2.2. There should be no need for any patches. Regards, Mike |+--- || Jared Still | || jkstill@cybc| || on.com | || | || 07/06/01 | || 04:41 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Oracle9i on Suse Linux | | Jonathan, SuSE 7.1 uses glibc 2.2, while previous versions used version 2.1. You can find the location of the patch to fix this on both www.suse.com and at MetaLink. Sorry, can't help with the other stuff, though I did have problems with Java. There's some MetaLink stuff about that too. Jared On Friday 06 July 2001 07:35, Jonathan Gennick wrote: I installed Oracle9i on Suse Linux 7.1 (384MB RAM, 400MB swap), and ran into a bit of a snag. The install ran fine until the end when the database creation assistant attempted to start the seed database. At that point I received an ORA-03113: End of File on Communication Channel error, and my Linux box froze up. Having Linux freeze up is a pretty rare event in itself, but I'm really wondering what caused the error. After rebooting, I was able to run the database creation assistant, create another database, and start that one. I then tried to manually start the seed database, and received the same ORA-03113 error. Could this somehow be related to memory? Was it too much to have the installer and the creation assistant running at the same time? A couple of other oddities I noticed: During the install, I was asked to specify a location for a JDK. I dutifully created a directory and typed in its path, but the installer never actually put any files into it. Why did it ask me for a directory that it wasn't going to use? When I run the oraenv script, it doesn't create an ORACLE_HOME environment variable. Instead of adding $ORACLE_HOME/bin to my path, it adds the full path to the bin directory. Is $ORACLE_HOME going away? Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Schema organization
Stephane, I'm sure they would prefer to operate as the schema owner but to me it's sloppy practice. You're right; create the objects as DWH and grant the necessary permissions to the application users. That way only the schema owner has 'admin' rights (create, drop, alter etc.). If they can't come up with an excellent reason (it will things easier for us is NOT an excellent reason) then you should stick to your guns. Regards, Mike |+- || paquette stephane | || stephane_paquette@| || yahoo.com | || | || 07/05/01 11:40 AM | || Please respond to | || ORACLE-L | || | |+- | || | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Schema organization | | Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PFILE location in NT
Hi, sorry but no, there's not. You can see your ifile setting in v$parameter but the init.ora isn't available. Regards, Mike |+-- || Shahid Nasir| || shahidnasir@emi| || rates.com | || | || 07/03/01 10:30 | || AM | || Please respond | || to ORACLE-L | || | |+-- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: PFILE location in NT | ---| Hi all, I have two parameter files in two different directories on the same machine. Is there a way I can find out which parameter file is being used by the database. One way I tried was to make the changes in one file and see if it takes effect when the database is restarted. but I have more than 52 machines and it will be time consuming to do the same for all machines especially when these machines are on remote locations. Thanks. Shahid. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Two indexes same columns
No, there would be no benefit and it probably wouldn't be allowed anyway unless you reversed the columns in the 2nd index. Regards, Mike Hately. |+--- || Connie | || Milliken | || cemail@sprin| || tmail.com | || | || 06/29/01 | || 04:46 PM | || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Two indexes same columns | --| If there is a primary key on a table (which inherently builds an index), is it still necessary for performance to build a seperate index on the same columns as those identified in the primary key or is the index generated by the primary key sufficient? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i Open Day (UK Listers)
Reading or bracknell, may as well be the moon. I've got a living to make. I read the blurb on my invitation and it looked like a barely-scratch-the-surface-sales-job. I've got 9i installed and I can glean the same depth of information from the Oracle web site. Mind you, if I was in the area I might go along for a free sausage roll so if anyone does atend could they mail me one? It would have been nice to have faces to go with names though. Maybe someone can host a rogues' gallery? Thanks, Mike |+--- || Robertson Lee| || - lerobe | || lerobe@acxio| || m.co.uk | || | || 06/29/01 | || 04:46 PM | || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: 9i Open Day (UK Listers) | --| mais non. Oracle HQ Thames Business Park in Reading. http://www.oracle.com/uk/start/9iopenday Regards Lee -Original Message- Sent: 29 June 2001 14:52 To: Multiple recipients of list ORACLE-L I am sure lots of people will want to meet you now that you have annnouced to the list that you have got chicken pox Where is the meet? - Bracknell I assume, and how do you get an invite? John -Original Message- Sent: 29 June 01 13:31 To: Multiple recipients of list ORACLE-L Hi all UK listers, I was just wondering how many people from the UK on here have actually registered for the 9i Open Day? I think it would be a great chance to get together for a quick drink and put faces to Signatures :) Thoughts? 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.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network
Re: Speed up massive delete
Jack, First of all you could explore these options : Could you truncate the table ? Could you copy out the rows you want to keep (using CTAS NOLOGGING) /drop the table/rename 'kept-rows' table to original name? Are the rows in a partition that you could drop? If none of these are possible these measures will improve performance : Disable log archiving. Make sure your redo logs are on the fastest disk available. Consider allocating larger logs temporarily if you checkpoint frequently. Remove any unnecessary indexes. Oracle won't have to keep them updated PLUS the optimiser will be likely to scan the table (which is probably what you want) if there's no suitable index. Remove any unnecessary foreign keys and triggers. You may be advised to use NOLOGGING. Ignore this advice. NOLOGGING does not affect deletes and will make no difference. Regards, Mike Hately, Oracle DBA |+--- || [EMAIL PROTECTED]| || L| || | || 06/28/01 | || 10:45 AM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Speed up massive delete| ---| Hi All, I have to do some pretty big deletes on a test environment and they are estimated to take for ever. Are there any options to speed it up? Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California--
Re: Fragmentation Locally Managed Tablespaces
Rachel, That maximum isn't a hard limit. Oracle themselves impose no limit of this kind. After you use all of the available space in the segment header block, any additional space map entries are overflowed into additional extent maps within the segment. This means that because the used extent information isn't cached in the data dictionary, queries against dba_extents etc. can cause many blocks to be read from LMTs if the map entries have overflowed in this manner. Therefore, the limitation is more of a recommendation to ensure that good performance is maintained. Regards, Mike |+--- || Rachel | || Carmichael | || carmichr@hot| || mail.com| || | || 06/28/01 | || 10:46 AM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Re: Fragmentation Locally Managed Tablespaces| ---| so we are going back to the Oracle specified maximum extents for a particular blocksize? From: Paul Drake [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Fragmentation Locally Managed Tablespaces Date: Thu, 28 Jun 2001 00:15:50 -0800 VIVEK_SHARMA wrote: Is Fragmentation of Objects meaningless in Locally managed Tablespaces Assuming EXTENTS is 3,000 for Some of the Objects Or Do the Objects need to be DE-Fragmented using exp/imp ? Vivek, funny, I was just looking into this tonight. what is your block size? I would recommend a quick visit to our friend Steve Adams site: http://www.ixora.com.au/tips/creation/extents.htm For this reason, we recommend that the number of extents per segment in locally managed tablespaces be limited to the number of rows that can be accommodated in the extent map within the segment header block - that is, approximately (db_block_size / 16) - 7. for an 8 KB block size - that is 505 extents per segment. Those objects would be good candidates to move to a different (new?) tablespace with a larger (uniform) extent size. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: Exploding the Mythns
I think I've missed a bit of this thread so apologies in advance. I always set timed_statistics-true and I don't worry unduly about lots of extents. I'm aware of problems with both approaches (bugs etc.) but as general principles I'm happy with them and they've never caused a problem. What's to repent about? Regards, Mike |+--- || Rachel | || Carmichael | || carmichr@hot| || mail.com| || | || 06/28/01 | || 10:45 AM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Re: OT: Exploding the Mythns | ---| repent? that presentation deliberately contained truth AND non-truth to make people think lots= 4000 in no way shape or form was it written for 9i From: Paul Drake [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: OT: Re: Common Oracle RDBMS Misconceptions Date: Thu, 28 Jun 2001 01:09:44 -0400 Rachel, In the presentation, I saw: always set timed_statistics=true having lots of extents is not a problem - |lots|= ??? Care to repent - or stipulate that it was for a de-supported (or soon to be) version? Paul _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Speed up massive delete
Ron, How does creating indexes as UNRECOVERABLE save time when you insert data? As far as I'm aware the only operation it will save time on is the initial creation. Regards, Mike |+--- || Ron Rogers | || RROGERS@galo| || ttery.org | || | || 06/28/01 | || 02:51 PM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Re: Speed up massive delete| ---| Jack Drop the indexes first before you do the deletes will save you time by not creating logs for the indexes being deleted. On my large activity files I have created the indexes as Unrecoverable saving time when I add data or delete data. ROR mªÖªm [EMAIL PROTECTED] 06/28/01 05:45AM Hi All, I have to do some pretty big deletes on a test environment and they are estimated to take for ever. Are there any options to speed it up? Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the
Re: Common Oracle RDBMS Misconceptions
Hi, Can't remember who started this thread but the most common misconception I see is : NOLOGGING (UNRECOVERABLE) stops redo log generation. though another favourite of mine is : The Universal Installer is useful and You can use the 8i database assistant to reliably create a database. Regards, Mike Disclaimer : 2 of the above statements are only my opinion and are not necessarily shared by my other personalities. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: Exploding the Mythns
Rachel, Thanks for that. Ive read and enjoyed that paper. I was actually defending your corner as the previous post seemed to be implying that you were in the wrong. Confusion reigned. I've experimented with TIMED_STAISTICS and high numbers of extents and my experience bears out your assertions. Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Speed up massive delete
Ron, I can see why it would save redo generation on direct load inserts. I can't understand why it would save any time on ordinary inserts, updates, deletes or import. I'm sure that your loads are direct loads and they make use of NOLOGGING/UNRECOVERABLE which is where you'll see the savings. Your original post mentioned that you are saving on your deletes also and I doubt that this is the case. Joe, Thanks, that's the same list that I usually produce at times like this. Cheers to all, Mike |+--- || Ron Rogers | || RROGERS@galo| || ttery.org | || | || 06/28/01 | || 04:56 PM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Re: Speed up massive delete| ---| Mike, When you insert data into a table redo logs are written for both the data info and the index info. If the index is created as unrecoverable then the redo log and archive log info is not written. That is where the time savings is derived from. During my daily load of my tables (approx 20 rows, 13 tables ,40 + indexes) the redo log generation and archivelog generation has decreased twice fold. Of course you should take a back up of your data and indexes after the work is completed. Time is saved during the IMP of the data also if the indexes are unrecoverable and or you EXP with index=N. [EMAIL PROTECTED] 06/28/01 10:24AM Ron, How does creating indexes as UNRECOVERABLE save time when you insert data? As far as I'm aware the only operation it will save time on is the initial creation. Regards, Mike |+--- || Ron Rogers | || RROGERS@galo| || ttery.org | || | || 06/28/01 | || 02:51 PM | || Please | || respond to | || ORACLE-L | || | |+--- ---| | | | To: Multiple recipients of list ORACLE-L| | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH)| | Subject: Re: Speed up massive delete| ---| Jack Drop the indexes first before you do the deletes will save you time by not creating logs for the indexes being deleted. On my large activity files I have created the indexes as Unrecoverable saving time when I add data or delete data. ROR mªÖªm [EMAIL PROTECTED] 06/28/01 05:45AM Hi All, I have to do some pretty big deletes on a test environment and they are estimated to take for ever. Are there any options to speed it up? Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Virus again
Just got it. 3 posts in a row. It seems to be replying directly to anyone who posts to the list. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Common Oracle RDBMS Misconceptions
Sorry, that attempt at humour has been withdrawn due to quality control issues. Regards, Mike |+--- || novicedba | || novicedba@ho| || tmail.com | || | || 06/27/01 | || 06:20 AM | || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]| | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Common Oracle RDBMS Misconceptions| --| well it does not exist will you 'please' be more clear as to what you want to convey coz I am a novice Oracle Certifiable DBBS - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 26, 2001 8:16 PM I've never found hot backups shocking myself. Is it possible that rather than visiting Jeremiah's site at www.speakeasy.net poor old novicedba visited www.spankeasy.net (I'm not even sure it exists and I'm at work so I won't be checking). If it does exist I'm sure that switching logs means something entirely different there. Regards, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: novicedba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT RE: 24 x 7 on NT?
More on the same vein. Sorry if this thread is developing a me too feel. I'm happy to use GUI tools when they save me time. I know what they're doing and how they achieve their results. The availability of GUI tools is however a huge disincentive for new DBAs to find out how the database actually works and leads to a lack of understanding of the way the beast works as a whole. If your goal is to be a knowledgeable and effective DBA then you can't beat starting from the command line and working your way towards GUI when you find your feet. That requires a long-term viewpoint though, and the world is focussing increasingly on the short term. If I sound like a Luddite then I've given the wrong impression; that;s just my 4 groats-worth. Regards, Mike |+- || Peter McLarty | || peter.mclarty@| || incts.com | || | || 06/27/01 01:30 | || PM | || Please respond | || to ORACLE-L| || | |+- -| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED]| | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: OT RE: 24 x 7 on NT? | -| Oh I agree As a newbie to the Oracle fold I found it oh so easy to dive into stuff with the DBA Studio, I suppose the thing that stops me is some 7 or more years hacking around Linux and Unix systems. Graphical tools are great for some things but you just cant beat the capabilities of a good script and Windows will be a lot better for it now it has a good scripting system. What i found DBA studio for thogh was the show SQL button I could set up some task to do and then have a look at the SQL that was going to happen so I could better understand it. I am sure when we start seeing more Oracle up on Win2K we will see a lot more scripting on that side to do those mixed OS and SQLPlus tasks. Peter McLarty [EMAIL PROTECTED] 04 0209 4238 System Administrator L plate Oracle DBA At 03:56 AM 27/06/2001 -0800, you wrote: On June 27, 2001 07:55 am, Mark Leith wrote: slapping developers - what's the problem with them? No problem slapping developers rather enjoy it actually The issue I have with point click is the increasing number of database admins who can only use these tools. Put them in front of a command line and they will sit and stare blankly. GUI tools can be great (except when they're written in java :), but you should actually know what you are doing and the 'how why' behind it before you start clicking a database to death. Just my cynical $.02. Cheers, GC -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter McLarty INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOLOGGING FEATURE
Greg, In 9i our good old Relational Trace File Management has been renamed to Real Trace File Management. Worse than that. The Logical Oracle Listener option is now called Real Oracle Transmission Failover Listener Management Advanced Option. Regards, Mike Hately Oracle DBA Greg Solomon [EMAIL PROTECTED] Hi Raj RTFM is a relational trace file management system. Originally released as an add-on with 8.0.0.6, I think it now comes standard with 9i. Cheers Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: NOLOGGING
Hi Greg, Delete isn't an operation that supports nologging. So you should see exactly the same performance. Regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: License high water mark
Dave, I've never seen the license HWM cause a problem. It's usually informational in order for the DBA or auditors to tell if them have purchased sufficient licenses. Internal limit messages often indicate that a process is trying to exceed a user or system limit (memory/semaphores/file handles etc.). If your DB crashed you should look for any core or trace files that might have further info. You could also check that the instance has not left shared memory segments lying in RAM. EMN0 is a process which is used as part of Advanced Queueing to notify processes that a message has arrived. If you don't use AQ then you can ignore these messages. The alert log seems to show a perfectly normal shutdown. Is it possible that someone has accidentaly kicked off a backup? Regards, Mike Hately, Oracle DBA |+- || Farnsworth, Dave | || DFarnsworth@Ashleyfurn| || iture.com | || | || 06/25/01 04:06 PM | || Please respond to | || ORACLE-L | || | |+- | || | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: License high water mark | | I mentioned the license high water mark as the culprit because I do not see it listed in my SIDAlert file any other time that the database was shut down. For all I know I am way off base. I was looking for something different from the other shutdowns. I should also be considering the EMNO process that restarted right before the database shutdown. Someone else on the list mentioned that this was a problem for them also. What I do not like is that the database just up and shut itself down with no warning. I suppose this is the time to open a TAR(oh the agony of it) with Oracle. Thanks, Dave -Original Message- From: JOE TESTA [mailto:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 8:58 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: License high water mark License high water mark is exactly that, the max number of connections to the database(if i remember correctly), what lends you to believe thats the culprit? Also what is emn0 process? thanks, joe [EMAIL PROTECTED] 06/25/01 10:01AM My database is down!! In my SIDAlert file is the following entries Fri Jun 22 19:00:00 2001 Restarting dead background process EMN0 EMN0 started with pid=18 Fri Jun 22 19:00:00 2001 Shutting down instance (immediate) License high water mark = 9 Fri Jun 22 19:00:04 2001 ALTER DATABASE CLOSE NORMAL Fri Jun 22 19:00:04 2001 SMON: disabling tx recovery SMON: disabling cache recovery Fri Jun 22 19:00:04 2001 Thread 1 closed at log sequence 438 Fri Jun 22 19:00:05 2001 Completed: ALTER DATABASE CLOSE NORMAL Fri Jun 22 19:00:05 2001 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT archiving is disabled I would think that the high water mark is the culprit. What exactly is the high water mark?? When I type in svrmgrl I get the following error ORA-12540 TNS: internal limit restriction exceeded. Any help or hints would be greatly appreciated. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Re: timed statistics
Greg, No, though in early versions of Oracle 8 there are bugs that can trip you up. At 8.1.6 timed_statistics is stable and low-cost in my experience. I've tried to measure the performance hit but I've never noticed any difference at all. I personally wouldn't run without it. Regards, Mike Hately, Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i On Linux
Hi, The recommendation is definitely 512MB RAM + a swap partition of twice that size. I installed on Suse 7.1 with 512MB RAM and a 600MB swap partition and encountered relatively few problems. RedHat should be OK as it's based on the 2.4 kernel. I can't recommend Suse highly enough though. BTW Suse 7.2 has now been released and it's equally 9i-compatible. Regards, Mike Hately, Oracle DBA Failed to install on rh7.1, PIII-450/256M (512M Swap). The installation consumed 460M Swap at max. Roger Liu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Digestive
Agh!! Ice. Chills the taste buds. Impairs the flavour. Sometime a little water if it's a strong malt. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 9i database for Linux available for download on Technet
Coincidentally, when you open Disk3 in Winzip the 'packed' size is almost exactly a tenth of the size reported on the TechNet site. I think someone put an extra 5 on the end of the size on the web page. I haven't tried to use it yet though. It's being burned to CD right now. Regards, Mike Hately, Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Digestive
Weird. When I saw the word digestive Lagavulin and Laphroiag both sprang to mind. Just shows you. great minds ... They're an acquired taste I admit. Regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Digestive
Laphroiag is very very nice. I still prefer my Talisker and I have a 30-year old Glenfiddich at home that I'm working my way through VERY slowly. I plan to buy a bottle of something from the year I was born ( '67 ) but the older I get, the more expensive they become. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: S.A.M.E. and HP XPs
SAME, It's easy, it's fast, it's convenient and it should guarantee that you get sub-optimal performance. Never terrible but never great either. You get out what you put in. Regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Quer
Hi, Oracle 8 datafile limits: The maximum number of files at Oracle 8 is OS dependant but usually it's 1022 per tablespace and 65533 per database. As with any program, the values of variables are held in control structures and it's up to the developer to arrive at sensible maximum limits for values in order to save memory and disk. That, in essence, is why Oracle has these limits. If the developers don't set limits then their code is less efficient as a result and that is a BAD THING tm. Regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOT (Little OT): Interesting Oracle related URL's.
Folks, Just my opinion but : Hosting a document on your site without the author's express or implied permission is bad. Providing a 'deep' link direct to a document within another site is OK if you check with the site owner. It's plain bad manners if you don't OK it first. Linking to the home page of the site containing the document is fine (unless, as mentioned, the link is along the lines of 'This bloke is a total arse and his paper is pitiful - here it is'). Another thing I hate to see is links to sites which pop the target site into a frame within the source site. Also, I'm no lawyer but contrary to an earlier post I'm sure that posting copyrighted material on your own site without gaining permission IS a crime. Perhaps someone can correct me there = ) Regards, Mike Hately -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT / LOL/ Serious Question (believe it or not)...
Speilberg eh? Can we call it Saving Private Synonym. Woo hoo hoo hoo hoo hee hee !!! [sounds of general thigh slapping and side-splitting] Cheers, Mike PS Sorry folks, I realise how pathetic that was but it's Friday so give me a break =) PPS Yes John (G), I KNOW! On Thu, 7 Jun 2001,Luis DeUrioste suggested (quite rightly in my opinion): -Well I think if we all pitch in a bit, and put out one heck of a book and -convince Steven Spielberg to take it to the screen, yes we would be rolling in -Do. -.. . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OFA Question
In performance terms it won't really make a difference but from an admin point of view it's not a great idea. It's way too easy to accidentally move or remove a file during a routine operation and it can can confuse backup and restore operations. If I ever have to do this I create a subdirectory so that at least they're logically separate and risk is minimised. regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PCTUSED??
Hi If the pattern of deleted dta leaves you with many almost empty leaf blocks then you may find that you would benefit from and index COALESCE rather than a REBUILD. On the subject of wasted space in table blocks it's very difficult to generalise. The best way to go may be to analyse the table immediately after the delete and see how the space is being used. You could then decide on appropriate table settings. It's possible to 'rebuild' a table by using ALTER TABLE MOVE . . . though it does have restrictions so is not universaly applicable. Would partitioning be an option for you? If you're dropping years of data at a time then you could drop a whole year's partition at a single stroke. It would be faster and would release the space immediately. Hope that some of that helps. I also hope you hadn't already thought of it all = ) Regards, Mike Hately Oracle DBA. Greg My concern is How can I reuse the deleted space, I have 20Gb Table with 15Gb Indexes, So I may not touch the table due to massiveness, I can do atleast indexes rebuild for availing 3-4Gb back. So in that regard pct_used has greater significance for reusing the space for future insertions. Thank you all for your responses and valueble input. Thanks Raghu. From: Greg Moore [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: PCTUSED?? Date: Wed, 06 Jun 2001 15:56:25 -0800 I have big growing tables has default PCT_USED 40%, But we delete data some times one year or 2yrs data. What is optimum value for this parameter?? I may consider my database is oltp. So how can decide a correct value for this??? PCT_USED might not have any effect for you at all. If you keep inserting rows by date, the blocks will fill with rows where the date is the same for every row in the block, or close. Then if the only time you delete is when you delete an entire year's worth of data, entire blocks will become empty. That's going to cause them to go on the free list no matter what you set PCT_USED to. As the other reply implied, your main concern may be tables where, after the mass deletes, there are many blocks below the high water mark that are completely empty. This would take up a little disk space until they are filled, but the main concern would be that full table scans would take as long after the deletes as before, due to the need to read all blocks up to the high water mark (empty or full). So if that is a concern you could reorg the table. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT RE: SUMMARY: Thanks everyone - tru64
I'd like to apply for evil-flying-monkey-thing or failing that, the muchkin who says Follow the yellow brick road! first. I have previous experience of being evil, flying and saying Follow the yellow brick road!. I have studied being a monkey to degree level. I'm ready for my closeup. Regards, Mike Hately, Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i is on technet (NT)
Yeah, I created my Solaris Intel CDs at the weekend but it won't help me with 9i unfortunately 'cos the download's only for Sparc architecture. Maybe I'll need to spend cash. =( Regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to prevent oracle from committing a transaction?
Hi, If you set autocommit off it will rollback any uncommitted transactions by default when you exit SQL*Plus. regards, Mike Hately Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to prevent oracle from committing a transaction?
Wow, you're right. Let this be a warning to you all; this is what happens to your brain when you mess with DB2. Just say no kids. = ) Thanks, Mike Hi Mike, I was also thinking so. But it doesn't do so. Setting autocommit off will implicitly commit all your transactions for that session. For doing this I tried exit rollback as suggested by Marco, one of the list members. Thanks and Regards, Ranganath *** Phone: (01670) 593900 Fax: (01670) 593909 Web Site: http://www.etech-uk.com E-MAIL DISCLAIMER The information in this email is confidential and may be legally privileged.It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. All information provided, including but not limited to, quotations, system specifications and suggestions concerning hardware/software (and services) configurations are strictly subject to our standard terms and conditions of business, copies of which are available on request. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Messing with DB2
I'm having my first brush with it and it's a bit of a shift. Seeing as IBM now own Informix I'd expect them to rip off its features and incorporate them into DB2. First impressions are that it's easy to set up a default database but not as tunable as Oracle but having said that I'm at no great depth yet. It has some handy features but its core architecture doesn't seem as good as Oracle's. Anyway, it looks like it may be a contender in years to come. The latest version, 7.2 was scheduled for release this week. Given that the previous version is available for Linux and IBM are forging closer links with the Linux community I'll be amazed if Oracle don't release 9i for Linux fairly quickly. I'd have it next on the release schedule if I were in charge. Hopefully, I'll manage some sort of comparison paper of the 2 products when I get time and a little more production exposure to DB2. Regards, Mike Hately Oracle ( and DB2! ) DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Auto Extend
Hi John, I still haven't learned not to make sweeping statements on mailing lists = ) I expected someone to raise SMON or checkpoint overheads so I added necessarily very deliberately. Agreed, I'd tend to keep the number of files down to a reasonable number but I've run a database with 3,500 datafiles and it didn't choke on log switches at all. As you say, appropriately sized redo logs can mitigate the effects to some extent. Regards, Mike Hately, Oracle DBA John Kanagaraj [EMAIL PROTECTED] Neither large files nor small files are evil. As with all things, fit the solution to the problem. Small files can leave you with lots of separate datafiles which isn't necessarily a drag on performance but can be an administrative chore and can cause more wasted space due to that pesky 'end-of-datafile' free extent. Mike, I will have to disagree with you about small files not necessarily being a drag on performance. Given a specific size of a database, it would be better to go in for larger files (avoiding the 2Gb pitfall if possible) since each datafile adds its own component to the flurry of I/O activity that takes place during log switch, i.e. when all datafile headers need to be updated. In simpler terms, (Large number of) Small datafiles + small redo logs + high DML activity = disaster during (the frequent) log switches... People who want to set up databases larger than a terabyte will inevitably have a budget to invest in new kit and buy-in to up to date OS and software levels. Not everyone has that luxury and a lot of legacy kit doesn't like 2GB+ files. That's only my opinion but really, why play around with any area that is demonstrably strewn with bugs unless you need to? Fully agreed! John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i is on technet (NT)
That's definitely what they do. Anyone want to sell me a Sparc box? Not you Spence, the shipping costs would bankrupt me !!! = ) Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Auto Extend
Hi, Neither large files nor small files are evil. As with all things, fit the solution to the problem. Small files can leave you with lots of separate datafiles which isn't necessarily a drag on performance but can be an administrative chore and can cause more wasted space due to that pesky 'end-of-datafile' free extent. Large files can fall prey to inode contention and are less portable (discuss!). Files over 2GB are best avoided unless there is a compelling reason to use them or you are certain that all of your software can handle them. People who want to set up databases larger than a terabyte will inevitably have a budget to invest in new kit and buy-in to up to date OS and software levels. Not everyone has that luxury and a lot of legacy kit doesn't like 2GB+ files. That's only my opinion but really, why play around with any area that is demonstrably strewn with bugs unless you need to? Regards, Mike Hately, Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how determine chaining if using gather_stats
Hi, GATHER_STATS only returns statistics that Oracle considers to be useful to the CBO. That doesn't include information on chained rows unfortunately. Cheers, Mike Hately, Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).