Re: Financials Client question
Hello Maria I got a call just yesterday about 8.1.6 on NT not installing on P4 machine. I researched metalink and they have some points. (BTW, we did DLL upgrade. Problem solved). However, all the fixes are upgrading a DLL or the JInitiator. So, I do not think that the fix will prevent you from working with machines that are less then P4. Make them prove their point. !!! Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 27, 2002 10:18 AM We have outsourced implementation and setup of a scaled down Oracle Applications 11i (rel 11.5.5) for Suse Linux... I have received a memo from the implementation team's application DBAs and they have informed me of a certain limitation for the specs of the client PC's. I was informed that the clients must either be Pentium 3s and below or all Pentium 4s. Mixed network of Pentium 3 and 4s will not work. I was told it was because of the JInitiator being invoked from the server. to quote: Let us further clarify that, through our proven installation/setup experience, the Shooman Application works on a network environment of mixed Pentium 3s and earlier computers. As of this writing, we have yet to prove that Shooman works properly in a network of Pentium 3 (or older) workstations mixed with Pentium 4s. However, we have proven that the application works if ALL workstations are Pentium 4s. It therefore follows that, should you purchase the latest Pentium 4 computers, you must issue ALL your Shooman users with this model in order for the system to work properly. (Shooman refers to the Oracle Application residing on a server named Shooman) I am completely ignorant of Oracle Applications as I have 0 experience in managing it. I'd really appreciate your opinion if we are being fooled or not. I am very concerned about this because as we all know PCs become obsolete in months... time will come we will have to change PCs...maybe move on to Pentium 5/6... This can pose a problem for us. Thanks a lot! -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega 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: Yechiel Adar 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).
sqlplus Connect Slow on
OS. Tru64 Unix ver 5.1 Oracle 8.1.7.2 m/c GS320 Alpha Server After 900 User processes are Connected to a Database , sqlplus system/manager@Connect String When issued DIRECTLY from the DB Server takes about 1 minute to Reach the SQL prompt Any parameters , pointers , Docs ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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).
RMAN: what if recovery catalog is lost?
Hi, That strange, but this has never come to my mind before. Ideally, the recovery catalog should be on other than production server (and it always was for me). And recovery catalog's database should be copied as well. Perhaps, using production database for recovery catalog. What if recovery catalog and production database crash at the same time and there is no way to recovery database with recovery catalog? I need the functionality of RMAN with recovery catalog, but I don't have separate server :( so I'm going to place it on the same server, but different database. I'm going to backup both databases symbiothically using each other as recovery catalog. If something goes wrong and server crashes with both databases, I will have backup copies but no catalog. Recovery scenarios: --1. I could create empty recovery catalog and collect all information again with CATALOG comand, but this works only when target database is open. Right? --2. Another possibility is to use RESYNC CATALOG FROM BACKUP CONTROL FILE should work fine, doesn't it? The only problem is that last structural changes are not available. --3. I'm not sure about third scenario - recovery target database without recovery catalog with backups taken while recovere catalog existed. I'm going to test this cases before set it up in production and I'd like to know about you experience in that area. Thanks in advance. Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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: sqlplus Connect Slow on
Probably configuration with shared server. Right? Maybe after 900 users new server process is starting and this takes time, but 1 minute looks too much. Is this only the case when connecting from the server prompt or from workstations as well? Alexandre OS. Tru64 Unix ver 5.1 Oracle 8.1.7.2 m/c GS320 Alpha Server After 900 User processes are Connected to a Database , sqlplus system/manager@Connect String When issued DIRECTLY from the DB Server takes about 1 minute to Reach the SQL prompt Any parameters , pointers , Docs ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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: Alexandre Gorbatchev 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: sqlplus Connect Slow on
Hello VIVEK_SHARMA, Do you use MTS? Look at dispatchers load. Tuesday, May 28, 2002, 3:33:18 PM, you wrote: V OS. Tru64 Unix ver 5.1 V Oracle 8.1.7.2 V m/c GS320 Alpha Server V After 900 User processes are Connected to a Database , sqlplus system/manager@Connect String V When issued DIRECTLY from the DB Server takes about 1 minute to Reach the SQL prompt V Any parameters , pointers , Docs ? V -- V Please see the official ORACLE-L FAQ: http://www.orafaq.com V -- V Author: VIVEK_SHARMA V INET: [EMAIL PROTECTED] V Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 V San Diego, California-- Public Internet access / Mailing Lists V V To REMOVE yourself from this mailing list, send an E-Mail message V to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in V the message BODY, include a line containing: UNSUB ORACLE-L V (or the name of mailing list you want to be removed from). You may V also send the HELP command for other information (like subscribing). -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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).
online backup script
Does anyone have an online backup script for Oracle 8.1.7 and AIX? Or can anyone provide a definitive list of the actions required to be taken? 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).
online backup and alter system switch logfile
I have a online backup script which issues the command ALTER SYSTEM SWITCH LOGFILE; immediately before copying the archived redo logs. Does this make sense? I am finding that the ARC process has not finished archiving the log before I copy the archive logs 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).
RE: online backup script
Does anyone have an online backup script for Oracle 8.1.7 and AIX? Or can anyone provide a definitive list of the actions required to be taken? John John, Look for the script named hotbackup.sql at http://www.oriole.com/frameindexTK.html. It's neither 8.1.7 or AIX specific, it uses 'cp' to backup the files which you may wish to replace with something else, but in any case it can provide you with a foundation on which to start building. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Diagnose Slow System
PERFORMANCE A Statspack report.txt Covering the problem period is Very Good Advice . 1) From report.txtpaste only the Section below "system-wide waits for non-background processes" Likewise from statspack paste the "top 5 wait" events section 2) v$session_wait is is additionally the most important view for event son which wait is occuring A Network issue Can sometimes be - 1) By Installing Running the Application DIRECTLY on the DB Server . If it Runsmuch better simply on the DB Server ,it could point to a network issue 2) By Comparing CPU Utilizations of APP DB Servers . If the DB Server APP ServerUtilization's are Exceedingly Different it may be due to a Network issue. 3) We "ftp" a 100M File (get put) from APP to DB Server vice-versa too . The Last part of the Outputin "(kbytes/sec)" is what is needed . Convert it into MegaBits/sec (MBPS) which is the Unit of Network Bandwidth . Commonly used network bandwidth is 10/100 MBPS . For heavy LoadedSystems10MBPS bandwidth sometimes proves insufficient . This is used on between Unix systemsThough there may be better network tools too. HTH -Original Message-From: Barbara Baker [mailto:[EMAIL PROTECTED]]Sent: Friday, May 24, 2002 9:43 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Diagnose Slow System Thanks, Tim. I do have a YAPP report from last week when the problems began -- I'll get that to you. I'll also grab some more data tomorrow. (If it's consistent, it'll start slowing down about 9:00 tomorrow morning.) Barb Tim Gorman [EMAIL PROTECTED] wrote: Barb,Can you take a BSTAT/ESTAT while the problem is occurring? Run the"utlbstat.sql" script from SVRMGRL and then 15-25 mins later run"utlestat.sql" from SVRMGRL. It's actually pretty important the"utlestat.sql" be run from SVRMGRL and not SQL*Plus. Please do this atleast once during the periods of slowness -- more than once if possible...Then, FTP the "report.txt" file(s) up to your PC and then browse to thehttp://www.oraperf.com site. Use the file-selection browse button at one ofthe upload sections to find one of the "report.txt" files and click"upload". The YAPP report will be produced automagically...What the YAPP report will do is give a great "top-down" breakdown of wherethe system has been spending the majority of what the end-user communityperceives as "response time" during the 15-25 mins of your BSTAT/ESTATsampl! ! e. In brief, the database is either working or waiting. If you like,you could email me the "report.txt" file and I'll look through the YAPPreport alongside you...There are some papers online at www.oraperf.com/whitepapers.htm which shouldexplain the YAPP methodology (written by Anjo) and also another paper aboutusing YAPP with STATSPACK. The latter paper largely applies to BSTAT/ESTATalso...From these reports, we should be able to get a pretty good idea of what isgoing on...Thanks!-Tim- Original Message -To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>Sent: Thursday, May 23, 2002 4:13 PM List: We've been fighting problems for several days. I've sort of overwhelmed myself with data, but I don't know what any of it means. Solaris 2.6, Oracle 8.0.5, MTS Users complain of extreme slowness. No errors in alert, no trace files! ! ; generated. Database is bounced every day. I capture wait statistics each day before the database goes down. The statistics from v$system_event for enqueue waits has gone up considerably since the problems started last Wednesday. But when I look at v$lock (I'm using Steve Adams' enqueue_locks.sql scripts), nothing pops up. Any ideas where I should start looking? I would appreciate any help. (I really believe this is a connectivity (networking) issue, but don'tknow how to confirm this) Thanks! Barb (accumulted since last night at 11:00 pm) EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- --- -- --- latch free 814316 4064 106360 130612686 enqueue 147 26 12033 81.8571429 free buffer waits 4 0 23 5.75 buffer busy waits 2959 0 567 19161879 log file parallel write 68177 0 78788 1.155639 log file sync 66683 1 77517 1.16247019 db file sequential read 1385334 0 144617 104391432 db file scattered read 1113301 0 142545 12803815 (The info captured below is unusual. running this repeatedly normallyshows nothing except smon TS resource wait) RESOURCE NSID SID HOLDING WANTING SECONDS - --- --- -- RT-1-0 4 LGWR X 0 TM-1949-0 46 46 SX 0 TM-1999-0 423 423 SX 4 46 46 SX 0 TM-2014-0 46 46 SX 0 TM-2106-0 46 46 SX 0 TM-2218-0 46
RE: online backup and alter system switch logfile
You Should Be Using Alter System Archive Log Current / ALL This will wait till the archive Process has written out the archivelog unlike the Alter System Switch Log file. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, May 28, 2002 3:08 PM To: Multiple recipients of list ORACLE-L I have a online backup script which issues the command ALTER SYSTEM SWITCH LOGFILE; immediately before copying the archived redo logs. Does this make sense? I am finding that the ARC process has not finished archiving the log before I copy the archive logs 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: Ganesh Raja 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).
TEMP segment is not being realsed.
Hi All, MY DB is 8.1.7 on AIX, TEMP space is not being realeasd after sort even after alter tablespace name coalesce. TEMP space was created as temporary. We didn't event put ant events to not to clear. Please let me know if there is any problem. Cheers, Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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: online backup and alter system switch logfile
John,Add a 'sleep' command to your UNIX-side shell script before you copy the logs to ensure the log has finished writing. You need to have that last archivelog - it contains the data regarding datafile header record changes you'll need to apply in the event you have to restore to sync up your controlfile.David A. BarbourOracle DBA, OCPAISD512-414-1002John Dunn [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]05/28/2002 03:08 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: online backup and alter system switch logfile I have a online backup script which issues the commandALTER SYSTEM SWITCH LOGFILE;immediately before copying the archived redo logs.Does this make sense? I am finding that the ARC process has not finishedarchiving the log before I copy the archive logsJohn--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-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: snapshot too old error - strange
no , i do not . Thanks DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Tue, May 28, 2002 4:53 AM To: Multiple recipients of list ORACLE-L Hello Andrey, Do you use autonomous transaction? Monday, May 27, 2002, 7:53:19 PM, you wrote: AB Dear list ! AB There is something strange going on in my production DB. AB There is a program that reads fom 2 huge tables (A and B - select only) and AB writes a fraction of records into some third table (let's call it C - AB inserts only). AB Now , NO ONE carries a DML agains A or B . AB But occasionally i get the Ora-1555 - snapshot too old error during the AB run of the aforementioned program. AB Any ideas , please ? AB TIA AB DBAndrey AB * 03-9254520 AB * 058-548133 AB * mailto:[EMAIL PROTECTED] -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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: Andrey Bronfin 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: TEMP segment is not being released.
Ravi, Try changing the next extent size to something other than its current setting, then change it back to what it was, and you should see any/all temp space not currently in use freed up. Coalesce only merges adjacent space, and not-in-use temp space is not freed up until someone needs it. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 8:08 AM To: Multiple recipients of list ORACLE-L Hi All, MY DB is 8.1.7 on AIX, TEMP space is not being realeasd after sort even after alter tablespace name coalesce. TEMP space was created as temporary. We didn't event put ant events to not to clear. Please let me know if there is any problem. Cheers, Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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: Sherman, Paul R. 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: Database backup question.Thank You
maybe 10i will allow table pt in time recovery :) [EMAIL PROTECTED] 05/25/02 05:53PM you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. I could be wrong. Also, exports are good at letting you clone users and application schemas --- Kevin Lange [EMAIL PROTECTED] wrote: If you truely mean that ALL of your databases are in ArchiveLog Mode, why would you do that to your Test and Dev databases ? -Original Message- Sent: Friday, May 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L my backup strategy, fwiw: prod - cold monthly, hot 2x week, exp weekly. test - cold, hot, exp occassional, always can refresh from prod. dev - cold hot occassional, exp daily. all dbs are in archivelogmode! gene [EMAIL PROTECTED] 05/24/02 03:04PM lets not forget the classic exp. 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman [EMAIL PROTECTED] wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of fail-safe mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly dead when you take your cold backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my
Senior DBA position - Dublin, Ireland
Before any public outcry I checked in with, and got permission, from list moderator to make this posting!!! Acknowledging we're geographically remote for most members of this list. (but maybe some other Irish based folk are here too!) My company, location North County Dublin, Ireland, has a vacancy for a full time permanent position for a Senior DBA. If anyone is interested check out Recruitment section of www.organon.ie No applications to me please, use directions from web site! - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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).
Legato oracle module.
Hi everybody, I've taken responsibility of a couple of Oracle systems on NT (in addition to my nice Solaris DBs). I'm setting up Legato to do the backups online using the Legato Networker Oracle Module. After checking the Legato docs, it seems that the only way of using this module is by integrating it with RMAN. is this right? Now, I'm sure that RMAN can do a good and flexible job. I've played with it a little but I don't like the meeesssyyy way of backing up the RMAN catalog. In the Legato docs , there's a section that warns you : Preparing For Disaster . Ensure that your RMAN Recovery catalog is being backed up regularly. . Doesn't this imply that you need to perform the catalog backup outside legato yourself?, so why did we buy it in the first place? I know you could create another catalog to perform cross catalog backups but that all sounds far to complicated and error prone. Anyone have any tips for me? [I'm much happier with my online backup scripts on Unix that I've been using and tweeking for years.] Mike. Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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: TEMP segment is not being realsed.
Thank you John, The things is When we run a partucular query based on view it takes around 1Gb space from TEMP tablespace and is not being relased and from that point on words It give space problem on other txns. that means It didn't mark fro Free to use does it? Ravi. -- Lau, John [EMAIL PROTECTED] wrote: Temporary segments in temporary tablespaces are managed differently - the temporary/sort segment is created when the first sort operation that needs to write to disk occurs and multiple transactions that need to sort to disk can share the same sort segment [but use different extents]. The sort segment expands by allocating new extents but extents are not de-allocated but marked free for re-use in an area of the SGA. Each statement that needs to sort in the temporary tablespace checks against this part of the SGA for free extents. What this means is that whilst the instance is up, you will see TEMP space apparently being used up but not being released [smon de-allocates the sort segment on instance startup]. To check actual free space you need to query against v$sort_segment. -Original Message- Sent: 28 May 2002 13:08 To: Multiple recipients of list ORACLE-L Hi All, MY DB is 8.1.7 on AIX, TEMP space is not being realeasd after sort even after alter tablespace name coalesce. TEMP space was created as temporary. We didn't event put ant events to not to clear. Please let me know if there is any problem. Cheers, Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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 message is for the named person's use only. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. CREDIT SUISSE GROUP and each legal entity in the CREDIT SUISSE FIRST BOSTON or CREDIT SUISSE ASSET MANAGEMENT business units of CREDIT SUISSE FIRST BOSTON reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lau, 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). __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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
Re: so when did you switch from NT to unix for oracle
Hi Mogens, What I wouldn't do to be a fly on that wall. Oh, the interesting discussions to be had! :-) I too have thought long and hard about this industry trend, and it has remarkable ramifications that we should all be aware of. One implication that you don't mention is the clear advantages of the federated shared-nothing architecture Microsoft currently has the lead in versus the shared-disk solutions that Oracle is an expert in. With a federated approach, you can afford to use "disposable" servers and provide excellent scalability. With cheaper machinesand operating systems providing fantastic performance but substandard stability, a federated approach gets you out of the woods. I am hoping Oracle picks up on this soon. However, I would like to voice my opinion that there is precious little missing from Linux. It used to be that the filesystems were lagging, but we've gotten excellent (I do not use that term lightly) performance from SGI's XFS filesystem. IBM's JFS is also available, as are some native filesystems. We run Linux in production for many customers, and where we do run into trouble, it's almost never as a result of the Linux. We do occasionally have difficulties because the hardware subsystems are not well-chosen and tuned to each other, however. Interestingly, the one company created to solve this problem, VAResearch, no longer creates hardware because it couldn't find a market. This vacuum is being quickly filled in by IBM and Dell, however. Should a company be willing to spend a comparable amount annually with their Linux provider and their hardware provider that they would give to (for instance) Sun Support, I believe they could easily achieve comparable levels of hardware and software reliability than any other commercial unix. Cheers, Paul - Original Message - From: Mogens Nørgaard To: Multiple recipients of list ORACLE-L Sent: Monday, May 27, 2002 1:18 PM Subject: Re: so when did you switch from NT to unix for oracle Maybe it's time to provoke a bit :-). Situation: I'm sitting here in Steve Adams' house (about 7 meters away from the IxOra server, which is SO small - just like the LITTLE mermaid in Copenhagen - very disappointing), and Anjo, Cary, Jonathan and the rest have gone to bed. Whiskies available on the oak table: Bowmore and Ardbeg. Provocative Thoughts (aimed at generating discussion, please): Basically a P4 processor can run circles round a Unix processor today (in other words: Unix processors are loosing the battle). A customer today would get most bang for the buck by bying Intel instead of Unix processors. The problem, of course, is that you can only choose between Windows and Linux on the Intel platform. If - this is no longer a choice - you could choose Solaris on Intel, you would get so much bang for the buck that nothing could compete with it. If Intel could handle many processors that would be interesting, too.I think Unix processors are dying. I didn't like it when VMS died (because it's the best operating system that was ever built). But it died. Now what?MogensHemant K Chitale wrote: Aah ! You _are_ looking at moving out of NT.Why I don't think it is an enterprise class platform 1. Much poorer memory management [2GB, memory leaks etc]than Unix. 2. Cannot scale beyond 4 CPUs.I AM surprised that you run a 450 users SAPapplication on 4CPU, 2GB on NT. Try that withOracle Applications ! 3. Any patch (e.g. the security patches that come outfrom Microsoft) requires a reboot of the server. I canunderstand OS patches requiring a Unix reboot but apatch to MSIE/Outlook/IIS on the same NT-box as thedatabase requiring a reboot of the server ? Unacceptable. 4. I don't know how good Online Backups are on NT.Hemant K Chitalehttp://hkchital.tripod.com- Original Message -To: "Multiple recipients of list ORACLE-L" ORACLE-L@fa tcity.comSent: Saturday, 25 May, 2002 4:33 AM 1) Not pulling any legs. That's what we run.2) We have a few reasons to switch to another platform.I'm lobbying for Solaris with Veritas Database Edition. Manygood reasons for doing so, but I'm beginning to have mydoubts about financing it.One of our current projects is to put in place an enterpriseclass backup and recovery system. The current one is lackingin several respects.One of damagement's questions: "What happens if we do nothing?"Another was "What's the ROI?"PHB's abound.JaredOn Friday 24 May 2002 08:03, Hemant K Chitale wrote: No way ! You're pulling a lot of legs[and hurting a lot of egos who take pride inpointing out that NT is _not_ an enterprise-classplatform, me included].Hemant K Chitale- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Friday, 24 May, 2002 8:00 AM How about 250 Gig, 450 users on SAP 4.0B?4 Cpu's 2 Gig Ram.Stop making me defend NT!!Jared"Disser, Arno" [EMAIL PROTECTED]Sent by:
Re: Legato oracle module.
I too am using Legato and Oracle, but haven't ventured to RMAN. I will start using it soon. What I plan to do: 1) Put RMAN db/catalog on same server as legato backup server. 2) Cold backup RMAN db to disk daily after nightly RMAN backups. 3) Backup RMAN disk backups to tape for offsite. [EMAIL PROTECTED] 05/28/02 10:23AM Hi everybody, I've taken responsibility of a couple of Oracle systems on NT (in addition to my nice Solaris DBs). I'm setting up Legato to do the backups online using the Legato Networker Oracle Module. After checking the Legato docs, it seems that the only way of using this module is by integrating it with RMAN. is this right? Now, I'm sure that RMAN can do a good and flexible job. I've played with it a little but I don't like the meeesssyyy way of backing up the RMAN catalog. In the Legato docs , there's a section that warns you : Preparing For Disaster . Ensure that your RMAN Recovery catalog is being backed up regularly. . Doesn't this imply that you need to perform the catalog backup outside legato yourself?, so why did we buy it in the first place? I know you could create another catalog to perform cross catalog backups but that all sounds far to complicated and error prone. Anyone have any tips for me? [I'm much happier with my online backup scripts on Unix that I've been using and tweeking for years.] Mike. Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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: Gene Sais 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: online backup and alter system switch logfile
On Tue, 28 May 2002, [EMAIL PROTECTED] wrote: Add a 'sleep' command to your UNIX-side shell script before you copy the logs to ensure the log has finished writing. You need to have that last archivelog - it contains the data regarding datafile header record changes you'll need to apply in the event you have to restore to sync up your controlfile. Sleep doesn't seem like a very reliable approach here. As has already been mentioned, Oracle provides a command that does not return until the current log is archived. alter system archive log current; In fact, this is one of the Oracle misconceptions I wrote about: http://www.speakeasy.org/~jwilton/oracle/switch-logfile-backups.html -- Jeremiah Wilton http://www.speakeasy.net/~jwilton John Dunn [EMAIL PROTECTED] I have a online backup script which issues the command ALTER SYSTEM SWITCH LOGFILE; immediately before copying the archived redo logs. Does this make sense? I am finding that the ARC process has not finished archiving the log before I copy the archive logs -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Database backup question.Thank You
isn't that supposed to be flashback query? :) --- Gene Sais [EMAIL PROTECTED] wrote: maybe 10i will allow table pt in time recovery :) [EMAIL PROTECTED] 05/25/02 05:53PM you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. I could be wrong. Also, exports are good at letting you clone users and application schemas --- Kevin Lange [EMAIL PROTECTED] wrote: If you truely mean that ALL of your databases are in ArchiveLog Mode, why would you do that to your Test and Dev databases ? -Original Message- Sent: Friday, May 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L my backup strategy, fwiw: prod - cold monthly, hot 2x week, exp weekly. test - cold, hot, exp occassional, always can refresh from prod. dev - cold hot occassional, exp daily. all dbs are in archivelogmode! gene [EMAIL PROTECTED] 05/24/02 03:04PM lets not forget the classic exp. 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman [EMAIL PROTECTED] wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of fail-safe mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly dead when you take your cold backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all
Re: TEMP segment is not being realsed.
there is no problem. Oracle leaves the temp segment allocated (you should be seeing only one) when your temp tablespace is created temporary to save allocation time for future sorts. It will go away when/if you shutdown and restart the database, but it is SUPPOSED to be there --- Nalla Ravi [EMAIL PROTECTED] wrote: Hi All, MY DB is 8.1.7 on AIX, TEMP space is not being realeasd after sort even after alter tablespace name coalesce. TEMP space was created as temporary. We didn't event put ant events to not to clear. Please let me know if there is any problem. Cheers, Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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).
Re: PREVIOUS SQL statement
Have you tried the ora_sql_txt event ? hth connor --- david hill [EMAIL PROTECTED] wrote: Hi Guys, I was wondering if someone could help me I'm trying to create an AFTER SERVERERROR trigger that will log all errors and the SQL that caused the error. Here's what I have so far CREATE OR REPLACE TRIGGER server_error_trig AFTER SERVERERROR ON DATABASE DECLARE text varchar2(1000); BEGIN select sql_text into text from v$session a , v$sql b where a.audsid = sys_context('USERENV','SESSIONID') and a.prev_sql_addr = b.address; INSERT INTO server_error_log VALUES(ora_sysevent, ora_login_user, SYS_CONTEXT('USERENV','OS_USER'), SYS_CONTEXT('USERENV','CLIENT_INFO'), sysdate, ora_server_error(1), dbms_utility.format_error_stack, text); END; my problem seems to be if I select * from v$session I can see my prev_sql_addr. BUT If I do this select prev_sql_addr from v$session where AUDSID = sys_context('USERENV','SESSIONID'); it returns 00 could someone try the sql above and tell me if works for you. I'm running 8.1.7.3 = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: snapshot too old error - strange
How about DML before (ie before your query starts)? If there are massive amount of blocks to be cleaned out, this can lead to ora-1555 hth connor --- Andrey Bronfin [EMAIL PROTECTED] wrote: I meant , no one runs a DML against those tables during the running of the program. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Tue, May 28, 2002 1:13 AM To: Multiple recipients of list ORACLE-L 2 huge tables A and B Now , NO ONE carries a DML agains A or B. If no one ever does DML against those two tables, how did they end up having so many rows? Do you run large batch update or insert operations against these tables from time to time? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: snapshot too old error - strange
On Mon, 27 May 2002, Nirmal Kumar Muthu Kumaran wrote: Increase rollback segment size for this transaction and make sure that the transaction will use the huge rollback segment I don't think that solution is correct. This is a common misconception about snapshot too old. Assigning your long-running select to a giant rollback segment will not help solve the problem. Here a short article on this misconception: http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html I suspect the original poster is encountering snapshot too old as a result of block cleanouts. See the following article for more information: http://home.clara.net/dwotton/dba/snapshot2.htm -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Senior DBA position - Dublin, Ireland
oh so tempting but I know England's restrictions on bringing in pets, what are Ireland's? --- O'Neill, Sean [EMAIL PROTECTED] wrote: Before any public outcry I checked in with, and got permission, from list moderator to make this posting!!! Acknowledging we're geographically remote for most members of this list. (but maybe some other Irish based folk are here too!) My company, location North County Dublin, Ireland, has a vacancy for a full time permanent position for a Senior DBA. If anyone is interested check out Recruitment section of www.organon.ie No applications to me please, use directions from web site! - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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).
RE: Covert tnsnames.ora to OID
Title: RE: Covert tnsnames.ora to OID OID?.. -Original Message- From: Yechiel Adar [SMTP:[EMAIL PROTECTED]] Sent: Mon, May 27, 2002 06:58 PM To: Multiple recipients of list ORACLE-L Subject: Covert tnsnames.ora to OID Hello All How can I covert all the names from tnsnames.ora to OID? I do not feel like entering all this data again, by hand, one by one. Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar 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: so when did you switch from NT to unix for oracle
Unfortunately, I, and all of us, have seen the Pentium processors in MS-NT/2K. The general opinion of Pentium/P4 is in the context of MS operating systems. And these don't perform as well as *nix. Hemant K Chitale - Original Message - From: Mogens Nørgaard To: Multiple recipients of list ORACLE-L Sent: Tuesday, 28 May, 2002 1:18 AM Subject: Re: so when did you switch from NT to unix for oracle Maybe it's time to provoke a bit :-). Situation: I'm sitting here in Steve Adams' house (about 7 meters away from the IxOra server, which is SO small - just like the LITTLE mermaid in Copenhagen - very disappointing), and Anjo, Cary, Jonathan and the rest have gone to bed. Whiskies available on the oak table: Bowmore and Ardbeg. Provocative Thoughts (aimed at generating discussion, please): Basically a P4 processor can run circles round a Unix processor today (in other words: Unix processors are loosing the battle). A customer today would get most bang for the buck by bying Intel instead of Unix processors. The problem, of course, is that you can only choose between Windows and Linux on the Intel platform. If - this is no longer a choice - you could choose Solaris on Intel, you would get so much bang for the buck that nothing could compete with it. If Intel could handle many processors that would be interesting, too.I think Unix processors are dying. I didn't like it when VMS died (because it's the best operating system that was ever built). But it died. Now what?MogensHemant K Chitale wrote: Aah ! You _are_ looking at moving out of NT.Why I don't think it is an enterprise class platform 1. Much poorer memory management [2GB, memory leaks etc]than Unix. 2. Cannot scale beyond 4 CPUs.I AM surprised that you run a 450 users SAPapplication on 4CPU, 2GB on NT. Try that withOracle Applications ! 3. Any patch (e.g. the security patches that come outfrom Microsoft) requires a reboot of the server. I canunderstand OS patches requiring a Unix reboot but apatch to MSIE/Outlook/IIS on the same NT-box as thedatabase requiring a reboot of the server ? Unacceptable. 4. I don't know how good Online Backups are on NT.Hemant K Chitalehttp://hkchital.tripod.com- Original Message -To: "Multiple recipients of list ORACLE-L" ORACLE-L@fa tcity.comSent: Saturday, 25 May, 2002 4:33 AM 1) Not pulling any legs. That's what we run.2) We have a few reasons to switch to another platform.I'm lobbying for Solaris with Veritas Database Edition. Manygood reasons for doing so, but I'm beginning to have mydoubts about financing it.One of our current projects is to put in place an enterpriseclass backup and recovery system. The current one is lackingin several respects.One of damagement's questions: "What happens if we do nothing?"Another was "What's the ROI?"PHB's abound.JaredOn Friday 24 May 2002 08:03, Hemant K Chitale wrote: No way ! You're pulling a lot of legs[and hurting a lot of egos who take pride inpointing out that NT is _not_ an enterprise-classplatform, me included].Hemant K Chitale- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Friday, 24 May, 2002 8:00 AM How about 250 Gig, 450 users on SAP 4.0B?4 Cpu's 2 Gig Ram.Stop making me defend NT!!Jared"Disser, Arno" [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]05/23/2002 10:23 AMPlease respond to ORACLE-LTo: Multiple recipients of list ORACLE-L[EMAIL PROTECTED] cc:fororacle Here are my 0.02EURTurn this reasoning around: Why would anyone use NT for a seriousOracle DB-server?Okay, for some minor development perhaps, but for an productionenvironment?b.t.w., ever considered a switch to VMS?Arno Disser--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Disser, Arno INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).
RE: _tru64_directio_disabled param Value on Digital Tru64 Unix
Thank You ! -Original Message- Sent: May 27, 2002 11:13 PM To: Multiple recipients of list ORACLE-L _tru64_directio_disabled is an NON-Documented Tru64 Unix Specific parameter To get List of ALL (Documented NON-Documented) parameters :- set echo on spool parm1 select a.ksppinm Parameter, a.ksppdesc Description, b.ksppstvl Session Value, c.ksppstvl Instance Value from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '/_%' escape '/' / spool off -Original Message- Sent: Monday, May 27, 2002 10:38 PM To: Multiple recipients of list ORACLE-L Tru64 5.0 pk3 and oracle 8.1.7.2 Where can I find this parameter ( _tru64_directio_disabled ) ? It is not in V$PARAMETER Thanks Darren -Original Message- Sent: May 27, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Vivek - I don't know if you checked Google, but the following page has some information. http://www.ixora.com.au/notes/direct_io.htm It sounds like on Tru64 5.0 this is yes by default, so you would only be setting it no. And sure I always try out new settings on the production server. That is how you get a reputation as an eXtremeDBA. Corporations like to trust their systems to live on the edge types. ;-) Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, May 27, 2002 3:38 AM To: Multiple recipients of list ORACLE-L Anjo , List If you mean it is SAFE to set this parameter to Either Value , Thanks Are there any Guidelines to Set this Value for Best Performance ? OR Should we Simply Set it See ? The only Concern is that it is a Production Database . Thanks -Original Message- Sent: Sunday, May 26, 2002 9:48 PM To: Multiple recipients of list ORACLE-L Well, If don't want direct io set it to true, else set it to false. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, May 26, 2002 5:23 PM _tru64_directio_disabled Stands for Direct IO support Disabled Current Default Value of _tru64_directio_disabled is FALSE ? Qs What would be the SAFE Advisable to set _tru64_directio_disabled to TRUE ? Oracle 8.1.7.2 on Digital Tru64 Unix 5.1 It is a Banking (Hybrid) Application having a Load of about 2 Million OLTP Transactions in a 5 Hour Window Everyday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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: DENNIS WILLIAMS 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: Browett, Darren 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: VIVEK_SHARMA 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
RE: Database backup question.Thank You
It is pretty easy to restore and recover a single table to an arbitrary point in time from a physical backup. I don't think Oracle needs to provide an extra feature. You restore a small subset of the database (system, rollbacks and the tablespace with the table in it), offline drop the datafiles you didn't restore, and roll the mini-clone forward to the point in time you want. Export/import the table from the mini-clone into the original database via named pipes. Query flashback won't work past a certain timeframe, and it won't work on tables that have been mangled by DDL (drop/truncate). And you have to use server-managed undo to use query flashback. Personally, there are a lot of queries the developers here have come up with that I have flashbacks of anyway, usually around 3 or 4 in the morning. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 28 May 2002, Rachel Carmichael wrote: isn't that supposed to be flashback query? :) --- Gene Sais [EMAIL PROTECTED] wrote: maybe 10i will allow table pt in time recovery :) [EMAIL PROTECTED] 05/25/02 05:53PM you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. Also, exports are good at letting you clone users and application schemas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: so when did you switch from NT to unix for oracle
Should a company be willing to spend a comparable amount annually with their Linux provider and their hardware provider that they would give to (for instance) Sun Support, I believe they could easily achieve comparable levels of hardware and software reliability than any other commercial unix. Perhaps an extreme example, but the NIH/CDC's recently signed the papers on a supercomputer for the Seattle lab. The box has 1000+ Intel It. procssors, 1.8Tb of core (no typo: Tera) and runs linux. For $23M you can have one too :-) The fact that people are using linux for something this heavy duty is interesting. The main reasons for choosing the O/S were scaleability, reliability, and support. Similar results came up from the DoD's recent software audit: they got better results for many app's from open source code than proprietary -- Billy wan't pleased in the least. Regardless of *NIX debates, linux is proving out as a nice, stable platform for cheap, reliable federated systems. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: Legato oracle module.
Mike, We did create another RMAN catalog on a small alternate host. We back up our main catalog. Yes, it is redundant but if you're going to go to all the work of getting RMAN working, you might as well go the extra distance and do this so you're covered. Cherie Machler Oracle DBA Gelco Information Network Jenner Mike M.Jenner@southampt To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] on.gov.ukcc: Sent by: Subject: Legato oracle module. [EMAIL PROTECTED] 05/28/02 09:23 AM Please respond to ORACLE-L Hi everybody, I've taken responsibility of a couple of Oracle systems on NT (in addition to my nice Solaris DBs). I'm setting up Legato to do the backups online using the Legato Networker Oracle Module. After checking the Legato docs, it seems that the only way of using this module is by integrating it with RMAN. is this right? Now, I'm sure that RMAN can do a good and flexible job. I've played with it a little but I don't like the meeesssyyy way of backing up the RMAN catalog. In the Legato docs , there's a section that warns you : Preparing For Disaster . Ensure that your RMAN Recovery catalog is being backed up regularly. . Doesn't this imply that you need to perform the catalog backup outside legato yourself?, so why did we buy it in the first place? I know you could create another catalog to perform cross catalog backups but that all sounds far to complicated and error prone. Anyone have any tips for me? [I'm much happier with my online backup scripts on Unix that I've been using and tweeking for years.] Mike. Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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).
storage parameters
Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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: what if recovery catalog is lost?
I have put my recovery catalog database in archivelog mode. I do a cold backup of the database and the archivelogs each Friday. This way I won't lose it. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 4:58 AM Hi, That strange, but this has never come to my mind before. Ideally, the recovery catalog should be on other than production server (and it always was for me). And recovery catalog's database should be copied as well. Perhaps, using production database for recovery catalog. What if recovery catalog and production database crash at the same time and there is no way to recovery database with recovery catalog? I need the functionality of RMAN with recovery catalog, but I don't have separate server :( so I'm going to place it on the same server, but different database. I'm going to backup both databases symbiothically using each other as recovery catalog. If something goes wrong and server crashes with both databases, I will have backup copies but no catalog. Recovery scenarios: --1. I could create empty recovery catalog and collect all information again with CATALOG comand, but this works only when target database is open. Right? --2. Another possibility is to use RESYNC CATALOG FROM BACKUP CONTROL FILE should work fine, doesn't it? The only problem is that last structural changes are not available. --3. I'm not sure about third scenario - recovery target database without recovery catalog with backups taken while recovere catalog existed. I'm going to test this cases before set it up in production and I'd like to know about you experience in that area. Thanks in advance. Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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: Ruth Gramolini 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: Database backup question.Thank You
oh god I was teasing about flashback query! I don't really think it's robust enough for production recoveries. Technically you don't need server-managed undo to use flashback query, it will work with manually managed rollback segments. But you have less of a chance for the data to still be in the rollback segment than you do with automatic undo management. and I've done the recover table from backup -- in fact that's what we did, on a 7.3.4 database when the programmer did an update without the where clause and then committed the transaction. although we used a database link and did our own update of the original data back into the production database as we couldn't take the table away from the app and it was historical data that had been changed. As for those developer flashbacks, see above. He did live after I got through with him. barely. :) --- Jeremiah Wilton [EMAIL PROTECTED] wrote: It is pretty easy to restore and recover a single table to an arbitrary point in time from a physical backup. I don't think Oracle needs to provide an extra feature. You restore a small subset of the database (system, rollbacks and the tablespace with the table in it), offline drop the datafiles you didn't restore, and roll the mini-clone forward to the point in time you want. Export/import the table from the mini-clone into the original database via named pipes. Query flashback won't work past a certain timeframe, and it won't work on tables that have been mangled by DDL (drop/truncate). And you have to use server-managed undo to use query flashback. Personally, there are a lot of queries the developers here have come up with that I have flashbacks of anyway, usually around 3 or 4 in the morning. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 28 May 2002, Rachel Carmichael wrote: isn't that supposed to be flashback query? :) --- Gene Sais [EMAIL PROTECTED] wrote: maybe 10i will allow table pt in time recovery :) [EMAIL PROTECTED] 05/25/02 05:53PM you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. Also, exports are good at letting you clone users and application schemas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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).
RE: Covert tnsnames.ora to OID
Title: RE: Covert tnsnames.ora to OID Oracle Internet Directory, which is Oracle's LDAP product. Matt Adams - GE Appliances - [EMAIL PROTECTED]Chaos! Panic! Disaster! (My work here is done) -Original Message-From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 28, 2002 11:33 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Covert tnsnames.ora to OID OID?.. -Original Message- From: Yechiel Adar [SMTP:[EMAIL PROTECTED]] Sent: Mon, May 27, 2002 06:58 PM To: Multiple recipients of list ORACLE-L Subject: Covert tnsnames.ora to OID Hello All How can I covert all the names from tnsnames.ora to OID? I do not feel like entering all this data again, by hand, one by one. Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar 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).
SQLLDR73 from a Win 2000 Machine
I need to run SQLLDR73 from a Win 2000 server, loading data to a 7.3.4 Unix server. I can't load 7.3.4 in the Win 2000 server. What might I need to get the load to run on the Win 2000 machine? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: TEMP segment is not being realsed.
Ravi, 1)Temporary solution: Around off-peak time do the following to get release of temp tablespace alter tablespace temp offline; Just after putting offline , run following immediately alter tablespace temp online; 2) Create temp tablespace using tempfile like create temporary tablespace temp tempfile '/u6/oracle/SID_NAME/data/temp_01.dbf' size 501M extent management local uniform size 10M; No need of coalescing or putting online/offline Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 28 May 2002 05:58:25 -0800 Thank you John, The things is When we run a partucular query based on view it takes around 1Gb space from TEMP tablespace and is not being relased and from that point on words It give space problem on other txns. that means It didn't mark fro Free to use does it? Ravi. -- Lau, John [EMAIL PROTECTED] wrote: Temporary segments in temporary tablespaces are managed differently - the temporary/sort segment is created when the first sort operation that needs to write to disk occurs and multiple transactions that need to sort to disk can share the same sort segment [but use different extents]. The sort segment expands by allocating new extents but extents are not de-allocated but marked free for re-use in an area of the SGA. Each statement that needs to sort in the temporary tablespace checks against this part of the SGA for free extents. What this means is that whilst the instance is up, you will see TEMP space apparently being used up but not being released [smon de-allocates the sort segment on instance startup]. To check actual free space you need to query against v$sort_segment. -Original Message- Sent: 28 May 2002 13:08 To: Multiple recipients of list ORACLE-L Hi All, MY DB is 8.1.7 on AIX, TEMP space is not being realeasd after sort even after alter tablespace name coalesce. TEMP space was created as temporary. We didn't event put ant events to not to clear. Please let me know if there is any problem. Cheers, Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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 message is for the named person's use only. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. CREDIT SUISSE GROUP and each legal entity in the CREDIT SUISSE FIRST BOSTON or CREDIT SUISSE ASSET MANAGEMENT business units of CREDIT SUISSE FIRST BOSTON reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lau, 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
RE: Legato oracle module.
Mike - RMAN has two options - the recovery catalog and using the control file of the target database. If you have only a single system, as I understand the RMAN philosophy, you should use the control file option. Also, from everything I've seen, export seems a fine way to keep the RMAN schema backed up after you use it. This probably wouldn't be ideal for a really large site with many RMAN activities occurring throughout the day. Another idea I've seen is if you have two production systems, cross-mount the RMAN catalogs so they back each other up. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Hi everybody, I've taken responsibility of a couple of Oracle systems on NT (in addition to my nice Solaris DBs). I'm setting up Legato to do the backups online using the Legato Networker Oracle Module. After checking the Legato docs, it seems that the only way of using this module is by integrating it with RMAN. is this right? Now, I'm sure that RMAN can do a good and flexible job. I've played with it a little but I don't like the meeesssyyy way of backing up the RMAN catalog. In the Legato docs , there's a section that warns you : Preparing For Disaster . Ensure that your RMAN Recovery catalog is being backed up regularly. . Doesn't this imply that you need to perform the catalog backup outside legato yourself?, so why did we buy it in the first place? I know you could create another catalog to perform cross catalog backups but that all sounds far to complicated and error prone. Anyone have any tips for me? [I'm much happier with my online backup scripts on Unix that I've been using and tweeking for years.] Mike. Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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: DENNIS WILLIAMS 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: storage parameters
Fawzia - If you're going to this much effort, consider looking into locally managed tablespaces (LMT) with uniform extents. Then you won't have to tidy up again. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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: DENNIS WILLIAMS 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: SQLLDR73 from a Win 2000 Machine
Recently, I loaded 7.3.4 on XP Pro and it seems to run fine, so I would expect you should be able to run under Win2000, though these combinations are not supported of course. Have you tried installing 7.3.4? If so, what problems are you running into? If it appears to be a compatibility issue, you might try changing the properties on the SQLLDR73 executable by right-clicking and then setting compatibility to NT. I've had to do this on an installation program for another product, but haven't run into that requirement on any Oracle product yet. Marc Perkowitz Senior Consultant TWJ Consulting, LLP 847-256-8866 x15 www.twjconsulting.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 11:18 AM I need to run SQLLDR73 from a Win 2000 server, loading data to a 7.3.4 Unix server. I can't load 7.3.4 in the Win 2000 server. What might I need to get the load to run on the Win 2000 machine? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: Marc Perkowitz 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: Limited SORT_AREA_SIZE
Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: so when did you switch from NT to unix for oracle
Mogens How is Steve Adam himself? Like other listers I am feeling his absence very much from this list. You may request on my behalf(or on behalf of other listers like myself) that he must participate in this list... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Mon, 27 May 2002 09:18:20 -0800 Maybe it's time to provoke a bit :-). Situation: I'm sitting here in Steve Adams' house (about 7 meters away from the IxOra server, which is SO small - just like the LITTLE mermaid in Copenhagen - very disappointing), and Anjo, Cary, Jonathan and the rest have gone to bed. Whiskies available on the oak table: Bowmore and Ardbeg. Provocative Thoughts (aimed at generating discussion, please): Basically a P4 processor can run circles round a Unix processor today (in other words: Unix processors are loosing the battle). A customer today would get most bang for the buck by bying Intel instead of Unix processors. The problem, of course, is that you can only choose between Windows and Linux on the Intel platform. If - this is no longer a choice - you could choose Solaris on Intel, you would get so much bang for the buck that nothing could compete with it. If Intel could handle many processors that would be interesting, too. I think Unix processors are dying. I didn't like it when VMS died (because it's the best operating system that was ever built). But it died. Now what? Mogens Hemant K Chitale wrote: Aah ! You _are_ looking at moving out of NT. Why I don't think it is an enterprise class platform 1. Much poorer memory management [2GB, memory leaks etc] than Unix. 2. Cannot scale beyond 4 CPUs. I AM surprised that you run a 450 users SAP application on 4CPU, 2GB on NT. Try that with Oracle Applications ! 3. Any patch (e.g. the security patches that come out from Microsoft) requires a reboot of the server. I can understand OS patches requiring a Unix reboot but a patch to MSIE/Outlook/IIS on the same NT-box as the database requiring a reboot of the server ? Unacceptable. 4. I don't know how good Online Backups are on NT. Hemant K Chitale http://hkchital.tripod.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, 25 May, 2002 4:33 AM 1) Not pulling any legs. That's what we run. 2) We have a few reasons to switch to another platform. I'm lobbying for Solaris with Veritas Database Edition. Many good reasons for doing so, but I'm beginning to have my doubts about financing it. One of our current projects is to put in place an enterprise class backup and recovery system. The current one is lacking in several respects. One of damagement's questions: What happens if we do nothing? Another was What's the ROI? PHB's abound. Jared On Friday 24 May 2002 08:03, Hemant K Chitale wrote: No way ! You're pulling a lot of legs [and hurting a lot of egos who take pride in pointing out that NT is _not_ an enterprise-class platform, me included]. Hemant K Chitale - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, 24 May, 2002 8:00 AM How about 250 Gig, 450 users on SAP 4.0B? 4 Cpu's 2 Gig Ram. Stop making me defend NT!! Jared Disser, Arno [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/23/2002 10:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: so when did you switch from NT to unix for oracle Here are my 0.02EUR Turn this reasoning around: Why would anyone use NT for a serious Oracle DB-server? Okay, for some minor development perhaps, but for an production environment? b.t.w., ever considered a switch to VMS? Arno Disser -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Disser, Arno 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
Re: storage parameters
No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN From: Malik, Fawzia [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: storage parameters Date: Tue, 28 May 2002 08:18:37 -0800 Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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: Limited SORT_AREA_SIZE
Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 12:32 PM To: Multiple recipients of list ORACLE-L Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: DENNIS WILLIAMS 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: Limited SORT_AREA_SIZE
Are u getting that error quite frequently -Original Message- Sent: Tuesday, May 28, 2002 1:52 PM To: Multiple recipients of list ORACLE-L Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 12:32 PM To: Multiple recipients of list ORACLE-L Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: DENNIS WILLIAMS 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: Arun Chakrapani 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 pick the lastes file using UTL_FILE package ?
Title: RE: how to pick the lastes file using UTL_FILE package ? Rahul, here is how you'd do it on a Unix box: ls -t|line ls -t would give you a list of files most recent first, then the line command would give you back the first filename only. In 2K the first part of that is DIR /O:-D, not sure how you get just the first filename from the list (the latest file will be first), but you could redirect that command to a file then read the first filename from the file! (DIR /O:-D files) HTH, Rich -Original Message- From: Rahul [mailto:[EMAIL PROTECTED]] Sent: Monday, May 27, 2002 2:18 AM To: Multiple recipients of list ORACLE-L Subject: how to pick the lastes file using UTL_FILE package ? list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: novice learns a new thing
Novice, There are no privileges associated with a synonym. Any account that has 'create synonym' privs may create a synonym for any object in any schema. Scott created a synonym for an object owned by hlledw. Hlledw may view scott.edw_week_lvl, as it is a synonym pointing to an object owned by hlledw. Scott however, has no access to it. Jared novicedba [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/27/2002 02:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: novice learns a new thing But the interesting point is that scott never granted the privileges on the object to test1 Coz I am a NoviceDBA Oracle Certifiable DBBS An obstacle is something you see when you take your eyes off the goal -- Anonymous - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 27, 2002 2:23 PM A quick look showed normal behaviour. You granted as the object owner select to test1 and scott than as test1 you tried describing a scott object that scott does not own but only has select rights on thus the error. after you created the synonym scott owned an object with that name (pointing to hlldew object) so the describe worked. jack novicedba novicedba@hotmaiTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] l.com cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: novice learns a new thing [EMAIL PROTECTED] 27-05-2002 10:28 Please respond to ORACLE-L Hi all, I noticed something strange. Logically it makes sense but nevertheless strange. I don't know how to pur it in words. So seniors please help === SQL conn hlledw/hlledw@orcl9i Connected. SQL grant select on edw_week_lvl to scott; Grant succeeded. SQL grant select on edw_week_lvl to test1; Grant succeeded. SQL conn scott/tiger@orcl9i Connected. SQL desc hlledw.edw_week_lvl NameNull?Type --- ID NOT NULL NUMBER(5) CODENOT NULL VARCHAR2(16) DSC NOT NULL VARCHAR2(40) END_DATENOT NULL DATE TIMESPANNOT NULL NUMBER(3) SQL conn test1/test1@orcl9i Connected. SQL desc hlledw.edw_week_lvl NameNull?Type --- ID NOT NULL NUMBER(5) CODENOT NULL VARCHAR2(16) DSC NOT NULL VARCHAR2(40) END_DATENOT NULL DATE TIMESPANNOT NULL NUMBER(3) SQL desc scott.edw_week_lvl ERROR: ORA-04043: object scott.edw_week_lvl does not exist SQL conn scott/tiger@orcl9i Connected. SQL create synonym edw_week_lvl for hlledw.edw_week_lvl; Synonym created. SQL desc edw_week_lvl NameNull?Type --- ID NOT NULL NUMBER(5) CODENOT NULL VARCHAR2(16) DSC NOT NULL VARCHAR2(40) END_DATENOT NULL DATE TIMESPANNOT NULL NUMBER(3) SQL conn test1/test1@orcl9i Connected. SQL desc hlledw.edw_week_lvl NameNull?Type --- ID NOT NULL NUMBER(5) CODENOT NULL VARCHAR2(16) DSC NOT NULL VARCHAR2(40) END_DATENOT NULL DATE TIMESPANNOT NULL NUMBER(3) SQL desc scott.edw_week_lvl NameNull?Type --- ID NOT NULL NUMBER(5) CODENOT NULL VARCHAR2(16) DSC NOT NULL VARCHAR2(40) END_DATENOT NULL DATE TIMESPANNOT NULL NUMBER(3) SQL = This is all strange to me Coz I am a novice DBA Oracle Certifiable DBBS An obstacle is something you see when you take your eyes off the goal -- Anonymous == 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
RE: Limited SORT_AREA_SIZE
Arun - No, I just needed to rebuild our largest table this weekend and re-create the indexes afterward. Fortunately with SORT_AREA_SIZE at 150-meg., the indexes build in a reasonable amount of time, so it was merely an inconvenience. I was able to enjoy one day of the weekend. I just wondered for next time I do this. Since I only perform this task once every couple of years, I'll probably forget most of the details before I do it again. I just wondered if anyone had a reason why I couldn't go past 150-meg. But I appreciate your suggestion because there are obviously many times when bouncing the instance is inconvenient. But that is the best part of this list, the opportunity to get ideas from the great minds. Most days it is pretty humbling. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Are u getting that error quite frequently -Original Message- Sent: Tuesday, May 28, 2002 1:52 PM To: Multiple recipients of list ORACLE-L Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 12:32 PM To: Multiple recipients of list ORACLE-L Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: DENNIS WILLIAMS 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
Re: storage parameters
An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that --- basher 59 [EMAIL PROTECTED] wrote: No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN From: Malik, Fawzia [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: storage parameters Date: Tue, 28 May 2002 08:18:37 -0800 Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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).
Re: Limited SORT_AREA_SIZE
Hi, Asked you sys admin to boost the maximum memory allowable for a process. I had that error on a hp box. I do not remember the name of the parameters. Once the sys admin had changed the memory process parameters, processes were able to use way more than 150M of ram. You can increase the sort_area_size for a particular session with alter session instead of bouncing the db. What I've done at a previous client was to change the session parameters using a database logon trigger who was checking in a table what sort/hash values to put for each user. --- DENNIS WILLIAMS [EMAIL PROTECTED] a écrit : This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.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).
RE: Limited SORT_AREA_SIZE
Are you using PQO? -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Khedr, Waleed 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: Statistical sampling and representative stats collection
Hi Larry, I sure wasn't disappointed to receive this note from a CBO/SQL guru such as yourself. Thanks for the ideas. My question here is would this database and your findings be applicable to other databases and the nature of their data? I wouldn't The nature of the data and the variances in between are so great I would never try and generalize everything. However, the number (or percentage) of generic cases is significant compared to the specific, and that is probably what I was trying to get to. In many cases, the act and cost of collecting the statistics by itself is so huge and significant compared to what is actually used out of all that data. since the characteristics can be so different, but, if I'm reading you correctly, you aren't saying that ESTIMATE is always the only way. But, the That is right. I think it was a case of moving on from carpet bombing (either ESTIMATE only or COMPUTE only) to some sort of precision bombing. There is still some collateral damage (as seen below) Sunday. Someone accidentally analyzed the schema at 30% on Monday and a lot of things went down the toilet. Going back to COMPUTE fixed things. Then again, maybe a 10% ESTIMATE would have fixed things. Jack and I both work with a guy who has talked about COMPUTE resulting in undesired plans, 10% did as well. They got the desired plans by going to 1%. So, even if one agrees that we don't necessarily have to COMPUTE, and in many (probably most?) cases we don't, there is still a lot of testing to be done to find the best estimate percent, and this could very well be different for various objects. And I think that's the battle we all face -- what is the best sampling percentage. And right now, it still seems to be done on a trial and error basis. I have some ideas on how one might attack this in an automated fashion, but it's still a *very* rough idea that I need to bounce off a few cohorts. I think you summarized it very well. How does one figure out what is the best percentage, given that we will NOT be able to complete a COMPUTE within the period allowed? When does one stop experimenting (significant DBA cost) and how does one make sure that the apple cart is not upset by new data patterns? I would be very interested in estimating the value using some automated fashion. I would be honored to be part of that bunch of cohorts! Btw, there is an option in 9i DBMS_STATS.AUTO_SAMPLE_SIZE. Has anyone tried this out? Or know how it works? for these stats on non-indexed columns? If so, I disagree on this point, even if it is general advice and not a rule. Stats on non-indexed columns can play a *large* role in CBO decisions. I'm not going to go into details and examples here illustrating that, but those stats can still help decide the driving table, the join methods between tables, etc. I built a sample case some time back to illustrate the importance of gathering these non-indexed column stats. Now, it might not be important for all systems, but if you are ever using indexed columns, and, still specifying criteria on non-indexed columns, the gathering of stats on the non-indexed columns could be *very* important. I can send you more details back-channel if you are interested. I agree with you, although I do have to contend that the effect is not very pronounced in all databases. This was revealed in some depth in Wolfgang's paper at IOUG where he was able to actually determine this in a 10053 trace, and it was an eye opener for me. The issue I have with this is that the default number of buckets is 2 and that is nowhere near what is needed. On the other hand, indiscriminately increasing the bucket size would leave you with a _Large_ number of histograms and result in the 'row cache lock' latch being taken out more that it should have been (as well as add to the cost of parsing). And your approach very well could take care of most cases for many people. It's an interesting idea and something certainly worth playing around with. Yep - and I did add a YMMV :) I would love to see this thread grow. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- 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).
RE: storage parameters
Hi Fazia, I would recommend the following white paper, it advocates using the SAFE methodology. HOW TO STOP DEFRAGMENTIING AND START LIVING Bhaskar Himatsingka, Oracle Corporation Juan Loaiza, Oracle Corporation You can find the paper on Cary Millsap's site. www.hotsos.com Sincerely, Jay Jay Earle, BSc(CS) ([EMAIL PROTECTED]) DBA, Operations Group SmartForce, Learning Solutions for the Human Enterprise -Original Message- Sent: Tuesday, May 28, 2002 3:30 PM To: Multiple recipients of list ORACLE-L An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that --- basher 59 [EMAIL PROTECTED] wrote: No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN From: Malik, Fawzia [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: storage parameters Date: Tue, 28 May 2002 08:18:37 -0800 Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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
Parallel degrees in DBMS_STATS
Hey all, I'm using DBMS_STATS (despite it's bugs) on 8.1.7.2.0 / HPUX 11.0. When I specify degree = 2 in the parms for either GATHER_SCHEMA_STATS or GATHER_TABLE_STATS, I notice that 4 p processes are kicked off (e.g. ora_p001_sid). Since this is on a test system, there is only one processor. Two of the stats processes have higher CPU and little or no I/Os, while the other two are almost all physical I/O and some CPU (probably for the I/O requests). So, why are there four processes? I ASSuMEd that there'd only be two. And I can't find the FM to R, nor anything suitable on MetaClink. Anyone? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: storage parameters
LOL! Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 2:58 PM I have to respectfully disagree. Creating everything in one huge extent and so called defragmentation are extremely important and save thousands of jobs at Seagate, Conner, Quantum, Maxtor, EMC and Hitachi. I cannot overemphasize an importance of such policy for the healthy growth of the storage industry. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: storage parameters An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that --- basher 59 [EMAIL PROTECTED] wrote: No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN From: Malik, Fawzia [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: storage parameters Date: Tue, 28 May 2002 08:18:37 -0800 Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia * * Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. * * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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:
RE: Statistical sampling and representative stats collection
Ian, John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others? To keep this simplistic, I wouldn't use Histograms (or let it default to 2) *unless* hardcoded values are known to be used, at least in 8i. The situation becomes different in 9i as the CBO is able to peek into these values even when bind variables are used. (I think there is a script out there on Steve Adam's site called 'Histogram Helper' which can suggest this for you). However, as Larry mentioned in a previous email, the CBO is influenced by distributions in non-indexed colummns. The issue here is that the number of buckets really matter, and the default of 2 can influence incorrect decisions (haven't we all seen 'em? :) So what I am essentially saying is this: Use COMPUTE and Histograms when you have to, but don't sweat over it unless it pinches ya. And how do we determine it is pinching? V$SYSSTAT is a pretty good indicator: (At the risk of being called a part of the 'ratios' group) Is the ratio of 'table scan blocks gotten' to 'table scan rows gotten' acceptable? Is the number of table scans acceptable? Is the number of 'db block gets' too much - as compared to 'physical reads'? I am in the process of determining the overheads of having 'too many' histograms - I am observing some 'row cache lock' latch waits and think that this could have been the result of too many histograms. Hope to post some info back to the list soon. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- 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).
SORT
Hi Which init.ora parameter allows a SORT operation to bypass the buffer cache? tHX -sEEMA _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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).
Datafile Corruption ........... or Not
Hey gang; I have an 8.0.5.0 database running on a Solaris platform. The developer is running a simple query which returns the error ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 191659) ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf' So I run dbverify (dbv) against the file and it says all is OK. $ dbv file=dynamici01.dbf DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = dynamici01.dbf DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 158376 Total Pages Failing (Index): 0 Total Pages Empty: 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Does anyone have any suggestions about this other than rebuilding the database (it is a clone of our production ... it will just set the developer back if we have to clone it) ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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: Statistical sampling and representative stats collection
John, I know in a previous job, we determined that histograms where not worth it. The following is from a test that we performed: *** Table- F_tab Uniform DistributionMax Distribution Field- P_code 0.65% 18% Therefore, from the above numbers, the field should be a good candidate for histograms so I did the following tests. Based on the following combinations of statistics and histograms, I timed how fast a sample query ran: w/o stats w/ statsw/stats w/stats P_Code no histograms 100 buckets 50 buckets -- -- -- -- -- 0101342 secs. 428 385 500 0101406 416 326 340 0101391 390 327 359 6501458 490 337 342 6501475 380 358 490 6501518 395 326 354 -- - -- -- -- Total Secs. 1730162913482085 (w/o high and low values) Avg time7Min 12Sec 6Min 47Sec 5Min 37Sec 5Min 51Sec per run However, to create the histogram it takes 1hr42min. Too long for the benefit that we gain. *** Tom -Original Message- Sent: Tuesday, May 28, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Ian, John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others? To keep this simplistic, I wouldn't use Histograms (or let it default to 2) *unless* hardcoded values are known to be used, at least in 8i. The situation becomes different in 9i as the CBO is able to peek into these values even when bind variables are used. (I think there is a script out there on Steve Adam's site called 'Histogram Helper' which can suggest this for you). However, as Larry mentioned in a previous email, the CBO is influenced by distributions in non-indexed colummns. The issue here is that the number of buckets really matter, and the default of 2 can influence incorrect decisions (haven't we all seen 'em? :) So what I am essentially saying is this: Use COMPUTE and Histograms when you have to, but don't sweat over it unless it pinches ya. And how do we determine it is pinching? V$SYSSTAT is a pretty good indicator: (At the risk of being called a part of the 'ratios' group) Is the ratio of 'table scan blocks gotten' to 'table scan rows gotten' acceptable? Is the number of table scans acceptable? Is the number of 'db block gets' too much - as compared to 'physical reads'? I am in the process of determining the overheads of having 'too many' histograms - I am observing some 'row cache lock' latch waits and think that this could have been the result of too many histograms. Hope to post some info back to the list soon. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- 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: Terrian, Tom 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).
passing a variable into the sqlloader control file
Looking for a way in Unix to pass a variable into a Sqlloader control, and then use that as a constant. Database version is 7.3.4 I tried something like: control file --- $1 = filename . . . . . SOURCE_FILE CONSTANT $SOURCE_FILE . . . When running, I get the error: SQL*Loader-350: Syntax error at line 13. Illegal combination of non-alphanumeric characters $1=SOURCE_FILE It does not like the $ on the imput variable. Anyone have any ideas or a work around? TIA. John K Line America, Inc. [EMAIL PROTECTED] www.kline.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fedock, John (KAM.RHQ) 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: Limited SORT_AREA_SIZE
One more best way also is to create those indexes with nologging along with ur sort_area_size,this i think u must be knowing, if that database has a standby then copy this file which has the index data Cause as u know only the structure of the index goes to the dictionary and not the index data hence u will have to copy this file. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: Parallel degrees in DBMS_STATS
Run following query and check whether relevent table/index has degree 1 select index_name,degree from dba_indexes where degree 1 / select table_name,degree from dba_tables where degree 1 / If degree 1 then it will use nymber of PQ process equivalent to number od degree Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 28 May 2002 11:18:30 -0800 Hey all, I'm using DBMS_STATS (despite it's bugs) on 8.1.7.2.0 / HPUX 11.0. When I specify degree = 2 in the parms for either GATHER_SCHEMA_STATS or GATHER_TABLE_STATS, I notice that 4 p processes are kicked off (e.g. ora_p001_sid). Since this is on a test system, there is only one processor. Two of the stats processes have higher CPU and little or no I/Os, while the other two are almost all physical I/O and some CPU (probably for the I/O requests). So, why are there four processes? I ASSuMEd that there'd only be two. And I can't find the FM to R, nor anything suitable on MetaClink. Anyone? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). MOHAMMAD RAFIQ _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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: Datafile Corruption ........... or Not
Kevin Lange wrote: Hey gang; I have an 8.0.5.0 database running on a Solaris platform. The developer is running a simple query which returns the error ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 191659) ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf' So I run dbverify (dbv) against the file and it says all is OK. $ dbv file=dynamici01.dbf DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = dynamici01.dbf DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 158376 Total Pages Failing (Index): 0 Total Pages Empty: 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Does anyone have any suggestions about this other than rebuilding the database (it is a clone of our production ... it will just set the developer back if we have to clone it) ? Thanks Kevin Kevin, Try to check what kind of segment this block belongs to (DBA_EXTENTS + DBA_SEGMENTS - extents are identified by the block# of their first block plus the number of contiguous blocks, so you have tolook for the extent for which 191659 is = first block and first block + extent size. It will tell you whether it's a table (and which one), index, temp or rollback segment, and you will be better armed to take a decision. There may be less time-costly than cloning the production database again. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: storage parameters
of course, I was selfishly thinking of this only from the perspective of the DBA and not the pocketbooks of the storage manufacturers --- Gogala, Mladen [EMAIL PROTECTED] wrote: I have to respectfully disagree. Creating everything in one huge extent and so called defragmentation are extremely important and save thousands of jobs at Seagate, Conner, Quantum, Maxtor, EMC and Hitachi. I cannot overemphasize an importance of such policy for the healthy growth of the storage industry. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: storage parameters An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that --- basher 59 [EMAIL PROTECTED] wrote: No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN From: Malik, Fawzia [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: storage parameters Date: Tue, 28 May 2002 08:18:37 -0800 Hi, Does Oracle reccommend having the initial and next extents set at the same values??? I am tidying up a current schema and the values are non uniform all the way through- any comments would be very much appreciated Rgds Fawzia * * Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. * * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia 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/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services
Re: storage parameters
Rachel Carmichael wrote: An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that Rachel, No benefit when the data is loaded, but there is justification to it if it avoids extent allocations during the load - at least for a dictionary managed tablespace. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: passing a variable into the sqlloader control file
John, Use ECHO commands to generate your control-file from within the shell script. You can then use the environmentals created by your input params. Something like: $1 = filename echo SOURCE_FILECONSTANT $1 control.ctl echo SECOND_LINECONSTANT $1 control.ctl then run sqlloader using the control file you created. make sense? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, May 28, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Looking for a way in Unix to pass a variable into a Sqlloader control, and then use that as a constant. Database version is 7.3.4 I tried something like: control file --- $1 = filename . . . . . SOURCE_FILE CONSTANT $SOURCE_FILE . . . When running, I get the error: SQL*Loader-350: Syntax error at line 13. Illegal combination of non-alphanumeric characters $1=SOURCE_FILE It does not like the $ on the imput variable. Anyone have any ideas or a work around? TIA. John K Line America, Inc. [EMAIL PROTECTED] www.kline.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fedock, John (KAM.RHQ) 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: Mercadante, Thomas F 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: Datafile Corruption ........... or Not
Hi, Find the corrupted object by the following statement: Select segment_name, segment_type from dba_extents where file_id=your_file# and your_block# between block_id and block_id + blocks -1 - If it's index, recreate it. - If not, recover the datafile - if no backup, tell us regards... Kevin Lange wrote: Hey gang; I have an 8.0.5.0 database running on a Solaris platform. The developer is running a simple query which returns the error ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 191659) ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf' So I run dbverify (dbv) against the file and it says all is OK. $ dbv file=dynamici01.dbf DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = dynamici01.dbf DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 158376 Total Pages Failing (Index): 0 Total Pages Empty: 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Does anyone have any suggestions about this other than rebuilding the database (it is a clone of our production ... it will just set the developer back if we have to clone it) ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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). -- Danisment Gazi Unal http://www.ubTools.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (ubTools) 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: Statistical sampling and representative stats collection
I have had some really good experiences with using histograms. They didn't always produce the improvements that I expected but in many cases, I saw 10 times, 100 times, or even 1,000 faster execution times after adding histograms. I don't have the specifics, but these were cases where the data was very heavily skewed and the column that the histogram was created on was included in the WHERE clause of the SELECT statement and set to some specified value with an equal sign (bind variables were not used). In my experience, histograms seem a bit hit or miss but in the cases where they've worked, the performance improvement has been good or even fantastic. In the cases where they haven't helped, I've simply removed them. Based on the scarcity of previous responses to emails on this list, it seems that histograms are not that widely used throughout the industry. I'm not sure why. Cherie Machler Oracle DBA Gelco Information Network Terrian, Tom tterrian@daas To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .dla.milcc: Sent by: Subject: RE: Statistical sampling and representative stats [EMAIL PROTECTED]collection om 05/28/02 02:55 PM Please respond to ORACLE-L John, I know in a previous job, we determined that histograms where not worth it. The following is from a test that we performed: *** Table- F_tab Uniform Distribution Max Distribution Field- P_code0.65% 18% Therefore, from the above numbers, the field should be a good candidate for histograms so I did the following tests. Based on the following combinations of statistics and histograms, I timed how fast a sample query ran: w/o statsw/ stats w/stats w/stats P_Code no histograms100 buckets 50 buckets -- -- -- -- -- 0101 342 secs.428 385 500 0101 406 416 326 340 0101 391 390 327 359 6501 458 490 337 342 6501 475 380 358 490 6501 518 395 326 354 -- --- -- -- Total Secs. 1730 1629 1348 2085 (w/o high and low values) Avg time7Min 12Sec 6Min 47Sec 5Min 37Sec5Min 51Sec per run However, to create the histogram it takes 1hr42min. Too long for the benefit that we gain. *** Tom -Original Message- Sent: Tuesday, May 28, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Ian, John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others? To keep this simplistic, I wouldn't use Histograms (or let it default to 2) *unless* hardcoded values are known to be used, at least in 8i. The situation becomes different in 9i as the CBO is able to peek into these values even when bind variables are used.
RE: SQLLDR73 from a Win 2000 Machine
I didn't think 7.3.4 supported multiple homes. I wouldn't want to screw up the registry entries. Ron -Original Message- Sent: Tuesday, May 28, 2002 12:26 PM To: Multiple recipients of list ORACLE-L Recently, I loaded 7.3.4 on XP Pro and it seems to run fine, so I would expect you should be able to run under Win2000, though these combinations are not supported of course. Have you tried installing 7.3.4? If so, what problems are you running into? If it appears to be a compatibility issue, you might try changing the properties on the SQLLDR73 executable by right-clicking and then setting compatibility to NT. I've had to do this on an installation program for another product, but haven't run into that requirement on any Oracle product yet. Marc Perkowitz Senior Consultant TWJ Consulting, LLP 847-256-8866 x15 www.twjconsulting.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 11:18 AM I need to run SQLLDR73 from a Win 2000 server, loading data to a 7.3.4 Unix server. I can't load 7.3.4 in the Win 2000 server. What might I need to get the load to run on the Win 2000 machine? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: Marc Perkowitz 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: Smith, Ron L. 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:RE: Limited SORT_AREA_SIZE
One item to remember on this note of using alter session is that if a pile of your SQL*Plus users have the alter session privilege and set sort_area_size to a large value at the same time, you can soon run out of memory. At which time all of the above listed users will come down complaining about a slow system, which is of course not your fault, but then it is your fault since everything is a database issue. Dick Goulet Having a really rotten Monday, or is it Tuesday. I hate Monday's, especially when they fall on Tuesday. Reply Separator Author: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 5/28/2002 10:30 AM Arun - No, I just needed to rebuild our largest table this weekend and re-create the indexes afterward. Fortunately with SORT_AREA_SIZE at 150-meg., the indexes build in a reasonable amount of time, so it was merely an inconvenience. I was able to enjoy one day of the weekend. I just wondered for next time I do this. Since I only perform this task once every couple of years, I'll probably forget most of the details before I do it again. I just wondered if anyone had a reason why I couldn't go past 150-meg. But I appreciate your suggestion because there are obviously many times when bouncing the instance is inconvenient. But that is the best part of this list, the opportunity to get ideas from the great minds. Most days it is pretty humbling. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Are u getting that error quite frequently -Original Message- Sent: Tuesday, May 28, 2002 1:52 PM To: Multiple recipients of list ORACLE-L Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 12:32 PM To: Multiple recipients of list ORACLE-L Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: Limited SORT_AREA_SIZE
There are limits to amount of memory a process can attach to. I have hit that wall with the size of the SGA from time to time. I would think that limit would be associated with the sort area also, sort_area + sga if you will. Take a look at document http://metalink.oracle.com/metalink/plsql/ml2_documents.showNot?p_id=1028623.6p_font or just do a search on sgabeg. Brian P. MacLean Oracle DBA, OCP8i DENNIS WILLIAMS DWILLIAMS@LIFETOTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] UCH.COM cc: Sent by: Subject: Limited SORT_AREA_SIZE [EMAIL PROTECTED] 05/28/02 09:53 AM Please respond to ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Statistical sampling and representative stats collection
tom, This is interesting. How did you determine max distribution and uniform distribution? Did you use standard deviation and variance? regards, jack silvey --- Terrian, Tom [EMAIL PROTECTED] wrote: John, I know in a previous job, we determined that histograms where not worth it. The following is from a test that we performed: *** Table-F_tab Uniform DistributionMax Distribution Field-P_code 0.65% 18% Therefore, from the above numbers, the field should be a good candidate for histograms so I did the following tests. Based on the following combinations of statistics and histograms, I timed how fast a sample query ran: w/o stats w/ statsw/stats w/stats P_Codeno histograms 100 buckets 50 buckets ---- -- -- -- 0101 342 secs. 428 385 500 0101 406 416 326 340 0101 391 390 327 359 6501 458 490 337 342 6501 475 380 358 490 6501 518 395 326 354 --- -- -- -- Total Secs. 1730162913482085 (w/o high and low values) Avg time 7Min 12Sec 6Min 47Sec 5Min 37Sec 5Min 51Sec per run However, to create the histogram it takes 1hr42min. Too long for the benefit that we gain. *** Tom -Original Message- Sent: Tuesday, May 28, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Ian, John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others? To keep this simplistic, I wouldn't use Histograms (or let it default to 2) *unless* hardcoded values are known to be used, at least in 8i. The situation becomes different in 9i as the CBO is able to peek into these values even when bind variables are used. (I think there is a script out there on Steve Adam's site called 'Histogram Helper' which can suggest this for you). However, as Larry mentioned in a previous email, the CBO is influenced by distributions in non-indexed colummns. The issue here is that the number of buckets really matter, and the default of 2 can influence incorrect decisions (haven't we all seen 'em? :) So what I am essentially saying is this: Use COMPUTE and Histograms when you have to, but don't sweat over it unless it pinches ya. And how do we determine it is pinching? V$SYSSTAT is a pretty good indicator: (At the risk of being called a part of the 'ratios' group) Is the ratio of 'table scan blocks gotten' to 'table scan rows gotten' acceptable? Is the number of table scans acceptable? Is the number of 'db block gets' too much - as compared to 'physical reads'? I am in the process of determining the overheads of having 'too many' histograms - I am observing some 'row cache lock' latch waits and think that this could have been the result of too many histograms. Hope to post some info back to the list soon. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- 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: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Limited SORT_AREA_SIZE
Arun - Yes, I have discovered the nologging option. Also someone asked whether I was using parallel, and the answer is no. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 28, 2002 3:01 PM To: Multiple recipients of list ORACLE-L One more best way also is to create those indexes with nologging along with ur sort_area_size,this i think u must be knowing, if that database has a standby then copy this file which has the index data Cause as u know only the structure of the index goes to the dictionary and not the index data hence u will have to copy this file. -Original Message- Sent: Tuesday, May 28, 2002 12:54 PM To: Multiple recipients of list ORACLE-L This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received: oracle.fin7maxmem Memory starts at: 5369596416 (1400d8a00) Memory ends at: 6399795195 (17d751ffb) Memory available: 1030198779 (3d6795fb) So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas? Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E 4-cpu. 4-gig. of system memory. shared_pool_size = 400-meg This is the only instance on this server. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Arun Chakrapani 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: DENNIS WILLIAMS 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).
Moving Autit Tables
Hi All, There has been some discussion in the past regarding moving the audit tables out of the SYSTEM table space to avoid fragmentation. Is this a good idea and if so does any have a procedure for doing so? Are there any problems to watch out for? O7345 - WinNT - RAID 0+1 -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris 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: Datafile Corruption ........... or Not
Typically 1578s are due to hardware hickups or a known bug. Dbverify will not capture all types of data corruption. The only truly safe way to capture physical corruption of a datablock is to get it into the buffer cache with a statement that exercises the datablock. The easiest way to determine the extent of the corruption is to dump the logical representation of the datablock(s) to a trace file. If this dump is successful it will give you the content of the block in a very raw format. It will also give you information regarding the type of block it is. Here is that syntax while logged in as sysdba: Alter system dump datafile 7 block 191659; If this is really important data you may want to open a TAR with Oracle Support as they have a tool that will interpret the data out of this block for you. I would also suggest that you look for other possible corruptions around this block by dumping them and selecting the data appropriately. Proactively, you may want to select from all the data you can that exists on that device. If the data is corrupted beyond recognition then you can try to mine the data out of related objects (e.g. if it is a table's datablock, then you can look at the related index or visa versa). You just have to be creative with your select statements (force the use of an index or not). To learn more about what is affected you can run the following query: SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 7 and 191659 between block_id AND block_id + blocks - 1; For more detail see the Oracle Metalink note number 28814.1. Regards, Michael Sale Co-author: Oracle 9i on Windows 2000 Tips Techniques -Original Message- Faroult Sent: Tuesday, May 28, 2002 2:05 PM To: Multiple recipients of list ORACLE-L Kevin Lange wrote: Hey gang; I have an 8.0.5.0 database running on a Solaris platform. The developer is running a simple query which returns the error ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 191659) ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf' So I run dbverify (dbv) against the file and it says all is OK. $ dbv file=dynamici01.dbf DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = dynamici01.dbf DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 158376 Total Pages Failing (Index): 0 Total Pages Empty: 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Does anyone have any suggestions about this other than rebuilding the database (it is a clone of our production ... it will just set the developer back if we have to clone it) ? Thanks Kevin Kevin, Try to check what kind of segment this block belongs to (DBA_EXTENTS + DBA_SEGMENTS - extents are identified by the block# of their first block plus the number of contiguous blocks, so you have tolook for the extent for which 191659 is = first block and first block + extent size. It will tell you whether it's a table (and which one), index, temp or rollback segment, and you will be better armed to take a decision. There may be less time-costly than cloning the production database again. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael P Sale 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: Datafile Corruption ........... or Not ------ Solved.
I really did not think you could recreate an object in the same tablespace once corrupted blocks were found I was wrong. It was a group of 20 indexes.Rachel told me offline to just drop and recreate the indexes. I did that and things worked just fine. The only other time I had this problem was a table and it was rough to recover. Thanks for everyones help. Kevin -Original Message- Sent: Tuesday, May 28, 2002 3:14 PM To: Multiple recipients of list ORACLE-L Hi, Find the corrupted object by the following statement: Select segment_name, segment_type from dba_extents where file_id=your_file# and your_block# between block_id and block_id + blocks -1 - If it's index, recreate it. - If not, recover the datafile - if no backup, tell us regards... Kevin Lange wrote: Hey gang; I have an 8.0.5.0 database running on a Solaris platform. The developer is running a simple query which returns the error ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 191659) ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf' So I run dbverify (dbv) against the file and it says all is OK. $ dbv file=dynamici01.dbf DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = dynamici01.dbf DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 158376 Total Pages Failing (Index): 0 Total Pages Empty: 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Does anyone have any suggestions about this other than rebuilding the database (it is a clone of our production ... it will just set the developer back if we have to clone it) ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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). -- Danisment Gazi Unal http://www.ubTools.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (ubTools) 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: Kevin Lange 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: storage parameters
but if the load is a one time thing (as he described) then the allocation hit happens only once and I still don't see a benefit and in fact can see how it might hurt -- tablespace fragmentation etc I'd rather see large extents but more of them --- Stephane Faroult [EMAIL PROTECTED] wrote: Rachel Carmichael wrote: An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. why? there is no benefit to that Rachel, No benefit when the data is loaded, but there is justification to it if it avoids extent allocations during the load - at least for a dictionary managed tablespace. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.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).
Re: Moving Autit Tables
Hi Chris, go to the following link you'll find a paper about that: http://www.oracle.com/oramag/oracle/99-Nov/index.html?69dbajj.html kader --- Guidry, Chris [EMAIL PROTECTED] wrote: Hi All, There has been some discussion in the past regarding moving the audit tables out of the SYSTEM table space to avoid fragmentation. Is this a good idea and if so does any have a procedure for doing so? Are there any problems to watch out for? O7345 - WinNT - RAID 0+1 -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kader Ben 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:Moving Autit Tables
From Metalink: Bookmark Default Font Go to End Doc ID: Note:1019377.6 Subject: Script to move SYS.AUD$ table out of SYSTEM tablespace Type: SCRIPT Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 02-JUL-1996 Last Revision Date: 18-MAR-2002 Disclaimer: ~~~ This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. Moreover, you should be aware that moving AUD$ out of SYSTEM tablespace is *not* a supported procedure. Oracle does not support changing ownership of AUD$, or any triggers on it. For a complete discussion on this topic see Note:72460.1 Abstract: ~ Oracle stores audit trail records in the SYS.AUD$ base data dictionary table. The problem is that this table grows inside the SYSTEM tablespace and must have records deleted from it or be truncated, otherwise it will take up all the room in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table will fragment the system tablespace. The following script allows a DBA to move SYS.AUD$ out of the SYSTEM tablespace. By moving it out of system tablespace, the table's size can be controlled without filling or fragmenting the system tablespace. Requirements: ~ This script should be run by the SYS user or as connect internal. Script: ~~~ --- cut -- cut -- cut -- SET ECHO off REM NAME: TFSAUDMV.SQL REMUSAGE:@path/tfsaudmv.sql REM -- REM REQUIREMENTS: REMShould be tun as SYS or connect internal REM -- REM AUTHOR: REMScott Gossett REM -- REM PURPOSE: REMThe purpose of this script is to move the existing SYS.AUD$ table REMand its associated index I_AUD1 to a different tablespace. REMThis script creates a new tablespace AUD that will be used to REMhold both objects. The example file size is too small for production REM environment!! REM --- REM EXPLANATION: REMOracle stores audit trail records in the SYS.AUD$ base data dictionary REMtable. The problem is this table grows inside the SYSTEM tablespace REMand must have records deleted from it or be truncated, otherwise it REMtakes up all the room in the system tablespace. This deleting and REMtruncating of the SYS.AUD$ table fragments the system tablespace. REM REMThe following script allows a DBA to move SYS.AUD$ out of the SYSTEM REMtablespace. By moving it out of system tablespace, control of the REMtable's size can be controlled without filling or fragmenting the REMsystem tablespace. REM --- REM DISCLAIMER: REMThis script is provided for educational purposes only. It is NOT REMsupported by Oracle World Wide Technical Support. REMThe script has been tested and appears to work as intended. REMYou should always run new scripts on a test instance initially. REM -- REM Main text of script follows: create tablespace AUDIT datafile '$HOME/data/aud01.dbf' size 500k default storage (initial 100k next 100k pctincrease 0) / create table audx tablespace AUDIT storage (initial 50k next 50k pctincrease 0) as select * from aud$ where 1 = 2 / rename AUD$ to AUD$$ / rename audx to aud$ / create index i_aud1 on aud$(sessionid, ses$tid) tablespace AUDIT storage(initial 50k next 50k pctincrease 0) / --- cut -- cut -- cut -- Remark: ~~~ Advice to not use reserved words as audit for tablespace as in example Reference: ~~
RE: Parallel degrees in DBMS_STATS
Nope. All tables are a degree of 1. And according to the docs, specifying the degree parameter when calling DBMS_STATS overrides the table default. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 3:01 PM To: Multiple recipients of list ORACLE-L Subject: Re: Parallel degrees in DBMS_STATS Run following query and check whether relevent table/index has degree 1 select index_name,degree from dba_indexes where degree 1 / select table_name,degree from dba_tables where degree 1 / If degree 1 then it will use nymber of PQ process equivalent to number od degree Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 28 May 2002 11:18:30 -0800 Hey all, I'm using DBMS_STATS (despite it's bugs) on 8.1.7.2.0 / HPUX 11.0. When I specify degree = 2 in the parms for either GATHER_SCHEMA_STATS or GATHER_TABLE_STATS, I notice that 4 p processes are kicked off (e.g. ora_p001_sid). Since this is on a test system, there is only one processor. Two of the stats processes have higher CPU and little or no I/Os, while the other two are almost all physical I/O and some CPU (probably for the I/O requests). So, why are there four processes? I ASSuMEd that there'd only be two. And I can't find the FM to R, nor anything suitable on MetaClink. Anyone? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Moving Autit Tables
Yep, and here's how (for 8i). alter table sys.aud$ move tablespace tools storage (initial 128K next 128K); alter index sys.i_aud1 rebuild tablespace tools storage (initial 128K next 128K); Guidry, Chris wrote: Hi All, There has been some discussion in the past regarding moving the audit tables out of the SYSTEM table space to avoid fragmentation. Is this a good idea and if so does any have a procedure for doing so? Are there any problems to watch out for? O7345 - WinNT - RAID 0+1 -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris 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: Suzy Vordos 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: Moving Autit Tables
Chris: It can be done, but Oracle, from my understanding, does not officially support this. Remember, in their eyes, disk is cheap. Here is a synopsis of what is needed. 1. After the database is built, create a copy of the $ORACLE_HOME/rdbms/admin/sql.bsq file. This contains the create scripts for the data dictionary; hence, the aud$ table and indexes are here. 2. Create another tablespace for these tables. 3. Drop and create these tables with improved storage parameters. Do the same for indexes. 4. You will need to grant the appropriate object level privileges on these to ensure that all functions properly. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, May 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Subject:Re: Moving Autit Tables Hi Chris, go to the following link you'll find a paper about that: http://www.oracle.com/oramag/oracle/99-Nov/index.html?69dbajj.html kader --- Guidry, Chris [EMAIL PROTECTED] wrote: Hi All, There has been some discussion in the past regarding moving the audit tables out of the SYSTEM table space to avoid fragmentation. Is this a good idea and if so does any have a procedure for doing so? Are there any problems to watch out for? O7345 - WinNT - RAID 0+1 -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kader Ben 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 contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen 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:Monitoring Oracle License
Raj, Yours is one of many license monitoring questions that keep haunting the list from time to time I regret that we seldomly get a decent answer. That being the case the fact that we've just gone through a visit from Oracle License Management Services I asked the LMS rep if he would like to comment on your question. He did send a response, which I will include, but asked that if we have additional questions please contact them at: http://www.oracle.com/corporate/lms/. The web site is not too bad, although a little weaselish at the same time. Anyway, his response: Dick, when we are looking at concurrent/named users we focus on 5 V$Performance tables (V$session, $V$version, V$option, V$license and dba_users). For the processor information it varies by OS (Unix, NT etc) but we would typically look at the PSRinfo to get the # of processors. Let me know if your friend has any questions. Thanks, Jay He also sent an attachment in word format on UPU/CPU licensing if anyone is interested, but I won't post it since ListGuru seems to have a dislike for attachments. Dick Goulet BTW: a visit from these folks is really not that painful. Or at least it hasn't become so yet. Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 5/21/2002 11:47 AM How does one monitor oracle license, is v$license the only option? Is this useful in case of processor based license? Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: snapshot too old error - strange
I meant , no one runs a DML against those tables during the running of the program. Right. If there is no DML while your program is running, then the snapshot error is due to DML that was run earlier. The solution is simple. In the programs that do large batch inserts or updates on these tables, simply put an ANALYZE TABLE statement at the end. Since the tables have undergone significant change, this is good practice because it will update statistics for the CBO. Also, it will visit the changed blocks in the table and perform block cleanout, and that will solve your snapshot error problem. -- 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).
ad-hoc update check
Hi folks - I have received a stream of requests from developers/production support ( yep, same group, dont ask ) to do ad-hoc data massaging in the production databases. Since I don't know the applications that well, it's hard for me to push back these requests when told that if the script don't get run today, marketing department won't be able to use the system etc. I wonder if other people on the list have the same problem and I am thinking about coming up with a document for the developers to fill out making sure the request won't hose up the database. I wonder how other shops deal with issues like these and can you let me know what you can do to check for potential issues with a sql script. TIA Dennis Meng Database Administrator Focal Communications Corp. -- 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: Statistical sampling and representative stats collection
Cherie, In my experience, histograms seem a bit hit or miss but in the cases where they've worked, the performance improvement has been good or even fantastic. In the cases where they haven't helped, I've simply removed them. As I observed before, histograms help only when literal predicates are used (until 9i). Steve Adams has this to say about Histograms: Quote Histograms enable the optimizer to more accurately estimate the selectivity of literal predicates against columns with skewed data distributions. This can help the optimizer to choose a better access path and possibly join order for certain queries than might otherwise be the case. However, redundant histograms on columns with uniformly distributed data, and unduly large histograms on columns with skewed data distributions just increase the CPU cost of parsing and waste space in the shared pool. Therefore histograms should only be created where they are beneficial, and should not be any larger than necessary. Please note that histograms on columns that are not indexed can nevertheless be beneficial because they inform the optimizer of the cardinality of their table for the query and can thus influence the join order. Unquote Based on the scarcity of previous responses to emails on this list, it seems that histograms are not that widely used throughout the industry. I'm not sure why. I remember that Steve Orr of this list used Histograms and enjoyed huge increase in performance. The reason why the Industry hasn't used Histograms as much as it should have been used is due to a combination of lack of knowledge, FUD as well as just plain lethargy. On the other hand, overuse also has its downsides. Btw, has anyone tracked V$ROWCACHE which provides a fair idea of the row cache (or DD) portion of the Shared pool - the figures against dc_histogram_data and dc_histogram_defs may provide some clues about what's going on within John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- 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).
Re: Parallel degrees in DBMS_STATS
I'd be interested to know of the bugs you've found on DBMS_STATS; most of the bugs I've seen logged against it were created due to differences with ANALYZE, and in the end it was determined that ANALYZE produced the wrong result, not DBMS_STATS... Anyway, the symptoms you describe match those for a two-stage parallel operation. One-stage parallel operations are simple SELECT on a table. Two-stage happens with queries that use GROUP BY (including ORDER BY and DISTINCT). You have requested a degree of parallelism (DOP) of 2. Oracle allocates 2 parallel execution slave processes to do one stage of the two-stage operation and 2 parallel execution slave processes to do the second stage; total of 4. The first 2 slave processes are considered producers and they are scanning the table or indexes. The second 2 slave processes are considered consumers and they are taking the results from the producers and grouping them for the GROUP BY. This explains why the the two sets of slave processes show different characteristics: the producers should show lots of physical I/O (which doesn't need a lot of CPU) and the consumers should show lots of CPU but no I/O. They should also be busy at different times, since you only have one CPU to timeshare between them... Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 1:18 PM Hey all, I'm using DBMS_STATS (despite it's bugs) on 8.1.7.2.0 / HPUX 11.0. When I specify degree = 2 in the parms for either GATHER_SCHEMA_STATS or GATHER_TABLE_STATS, I notice that 4 p processes are kicked off (e.g. ora_p001_sid). Since this is on a test system, there is only one processor. Two of the stats processes have higher CPU and little or no I/Os, while the other two are almost all physical I/O and some CPU (probably for the I/O requests). So, why are there four processes? I ASSuMEd that there'd only be two. And I can't find the FM to R, nor anything suitable on MetaClink. Anyone? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Tim Gorman 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: Legato oracle module.
Hi Mike, I've been using the Legato/RMAN combination for a couple of years now. The recovery catalog resides on the Legato Backup server. Once all the database backups finish, we then do a cold backup of the recovery catalog database straight to tape. I don't know what others do, but this if you can afford downtime for your recovery catalog db this is probably the best way to do it. If you have enough disk space on your server, you actually don't need the Legato plugin. Just dump the backups to disk and get the normal Legato Networker server backup pick them up onto tape. Regards, Sujatha -Original Message- Sent: Wednesday, 29 May 2002 12:23 AM To: Multiple recipients of list ORACLE-L Hi everybody, I've taken responsibility of a couple of Oracle systems on NT (in addition to my nice Solaris DBs). I'm setting up Legato to do the backups online using the Legato Networker Oracle Module. After checking the Legato docs, it seems that the only way of using this module is by integrating it with RMAN. is this right? Now, I'm sure that RMAN can do a good and flexible job. I've played with it a little but I don't like the meeesssyyy way of backing up the RMAN catalog. In the Legato docs , there's a section that warns you : Preparing For Disaster . Ensure that your RMAN Recovery catalog is being backed up regularly. . Doesn't this imply that you need to perform the catalog backup outside legato yourself?, so why did we buy it in the first place? I know you could create another catalog to perform cross catalog backups but that all sounds far to complicated and error prone. Anyone have any tips for me? [I'm much happier with my online backup scripts on Unix that I've been using and tweeking for years.] Mike. Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike 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: Sujatha Madan 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 Label Security
SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'LBACSYS'; if returns row then Label security installed. naren -Original Message- Sent: Wednesday, 29 May 2002 10:25 To: Multiple recipients of list ORACLE-L Hi List, How can I find out Oracle Label Security is installed or NOT? Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Narender Akula 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).
joining word/ lines in a file
List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: joining word/ lines in a file
$ perl -e 'print join \n, ' myfile; -- Suhen Pather [EMAIL PROTECTED] List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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). -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: ad-hoc update check
Dennis, If management is Ok with this ( have you asked? ) you need to take some steps to protect your database, your job and your reputation. 'cuz the duhvelopers will do their best to destroy all three. 1. You need a test database with a reasonable amount of test data 2. Your duhvelopers need to develop their data massage routines against the test database. 3. When they think they have it right, run the query on the QA database. If resource/time constraints demand it, this might be your test database. 4. They need to check their results. This means that an actual user that is very familiar with the application will use the application against the QA database, and sign off on the results. 5. Don't give them an account on the production database. They must supply you the DBA with script that you will run. They must supply documentation with the script. If the docs are imcomplete, don't run the script until the docs are complete. Anyway, this is what makes me happy. :) Jared On Tuesday 28 May 2002 15:25, [EMAIL PROTECTED] wrote: Hi folks - I have received a stream of requests from developers/production support ( yep, same group, dont ask ) to do ad-hoc data massaging in the production databases. Since I don't know the applications that well, it's hard for me to push back these requests when told that if the script don't get run today, marketing department won't be able to use the system etc. I wonder if other people on the list have the same problem and I am thinking about coming up with a document for the developers to fill out making sure the request won't hose up the database. I wonder how other shops deal with issues like these and can you let me know what you can do to check for potential issues with a sql script. TIA Dennis Meng Database Administrator Focal Communications Corp. -- 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).
Re: joining word/ lines in a file
oops, wrong direction -- you wanted to take out the newlines. use chomp and print the resulting array with $, left at the default value: $ perl -e 'chomp (my @a = ); print @a' myfile [anotherfile ...]; i.e., read from ARGV, put it all in an array, slice off the input record separators; print the array. -- Suhen Pather [EMAIL PROTECTED] List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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). -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: joining word/ lines in a file
Here's a perl one liner: perl -ne 'chomp; print; print qq{\n} if /\;$/' file1.txt newfile.txt If isn't perfect. An 'and' at the end of the line will be joined with the beginning of the next line, which is not right. I use the following two regular expressions to create executable SQL from v$sqltext. You may find them useful if you explore your Perl options. $sql =~ s/ (--\s*(([\w]+)\s+))? (?= and\s+[\w+\.+]+\s*(\=|between|\\|\!\=) |or\s+[\w+\.+]+\s*(\=|between|\\|\!\=) |where\s+[\w+\.+]+\s*(\=|between|\\|\!\=) |select |union |minus |intersection |from |where |order\s+by |group\s+by ) /\n$5/gomix; $sql =~ s/(\s+ --\s*where |--\s*from |--\s*group\s+by |--\s*order\s+by |--\s*select |--\s*union |--\s*minus |--\s*intersection #|select #|union #|minus #|intersection #|from #|where #|order\s+by #|group\s+by\s+ )/\n$1/gomix; Jared On Tuesday 28 May 2002 20:58, Suhen Pather wrote: List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- 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).
Re: joining word/ lines in a file
Flex, Bison some programming will probably do the trick. There is a nice O'Reilly book dealing with Lex Yacc wnd even nicer book dealing with the C programming language. The Good Book is: Brian Kernighan and Dennis Ritchie: The C Programming Language. You should get the King James (ANSII) edition. On 2002.05.28 23:58 Suhen Pather wrote: List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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).
joining word/ lines in a file
List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. I am trying using sed but cant seem to work it out. I could use tr but if there are multiple delete from ; then problems occur with blank lines. I would need to only do a tr in the file between delete and ;. and skip the blank lines. Not sure how to do this though. like tr -d \\012 FILE1 FILE.out FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 232 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '43 ' and GROUP_NAME = ' xTravel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 22; Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: joining word/ lines in a file
-- Jared Still [EMAIL PROTECTED] If isn't perfect. An 'and' at the end of the line will be joined with the beginning of the next line, which is not right. Don't strip the newlines, replace them with white space: perl -e 'undef $/; ($a=ARGV) =~ s/\n+/ /g; print $a' \ [file [file...]] [file] i.e., slurp the input whole, replace any sequence of one-or- more newlines with a single space and spit out the result. If the input doesn't have multiple spaces in the fields you might get better result to strip newlines followed by whitespace: ... ~= s/\n\s+/ /g will take any single newline and all the whitespace that follows it and replace the result with a space. If none of the data fields being hacked have spaces in them a further: $a =~ s/ +/ /g will replace one or more literal spaces with a single space to clean things up a bit: ... -e 'undef $/;($a=ARGV) =~ s/\n+/ /g;s/ +/ /g;print $a' ... will convert nearly anything you can give it into a nice, clean, single line. If you want to get things neater than this see the examples in Parse::RecDescent. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: joining word/ lines in a file
Hey this is a not a trick question, without buying the books listed below is there a way using the std awk, sed, tr *nix utilities. Suhen Flex, Bison some programming will probably do the trick. There is a nice O'Reilly book dealing with Lex Yacc wnd even nicer book dealing with the C programming language. The Good Book is: Brian Kernighan and Dennis Ritchie: The C Programming Language. You should get the King James (ANSII) edition. On 2002.05.28 23:58 Suhen Pather wrote: List, slightly off topic but Unix OS I need to join lines/ words in a file. So that it must be in a readable Oracle format. They are seperated by a newline. Here is a snippet of what the file looks like. FILE1 delete from JDAPROD.HBI_LOST_SALES where SKU_TECHNICAL_KEY = 1410 and STORE_TECH NICAL_KEY = 276 and STORE_NO = 315 and STORE_NAME = 'Glenfield SB 315 ' and SKU = '1516803' and SKU_NAME = 'WMERE ORGAN ISER Black ' and DEPT = '052' and DEPT_N AME = 'Travel Bags' and CLASS = '05211 ' and CLASS_NAME = 'Travel Bags' a nd FORMAT_EXISTS = 'Y' and STOCK_ON_HAND = 2 and STOCK_IN_WAREHOUSE = 433 and RE QUESTED_UNITS = 0 and ALLOCATED_UNITS = 0 and UNIT_SALES_CURRENT_DAY = 0 and UNI T_SALES_LAST_7_DAYS = 0 and UNIT_SALES_LAST_6_WEEKS = 2 and ON_HAND_COST = 41.23 7 and ON_HAND_RETL = 167.333 and GROUP_NO = '05 ' and GROUP_NAME = ' Travel' and EST_STOCK = 0 and INTRANSIT = 2 and DATE_RUN = TO_DATE('27MAY2002 00:00:00', 'DDMON HH24:MI:SS') and ON_ORDER = 150; I am trying using sed but cant seem to work it out. Any Ideas? Regards Suhen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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: Suhen Pather 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).