Re: Data Subset Tool?
Hi Jared Disclaimer: I work for Net 2000 Ltd. the authors of DataBee - a database subsetting tool. > Disk is cheaper. :) It only seems so because manually creating subsets is such a nightmare - but if you have large databases it just isn't practical to give every developer, tester and trainer a full size copy of production. So what do you do? Well if your like most DBA's you create far fewer copies of the big database than is really needed and everybody has to share it. Then the trouble starts: the developers the collide with one another, the testers trash each others data and everybody squabbles because they have to wait for a time slot. Truth be known - developers, testers and trainers don't like to work on full size copys (it slows them down) and they don't like to share. As you say, manual subsets are not cost effective because of the DBA effort involved in making them. If you have an automated tool that just snips out subsets like a cookie cutter then you can have as many as you care to. Cheers Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] DataBee: http://www.databee.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dale Edgar 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: Data Subset Tool?
Hi Larry Disclaimer: I work for Net 2000 Ltd. the authors of DataBee. >...but does anyone have >recommendations on a tool that will create a subset of production data that >is referentially intact? I am aware of DataBee... I don't mind telling you who the competition is - we are confident that we are better value both in price and functionality:-) Two other subsetting tools on the market are DataManager from Quest http://quest.com/schema_manager/ and CheckMate from BitByBit http://www.bitbybit.co.uk >, but it might be more cost effective to buy > something if the price is right. The main feature I would be interested in > is a tool that maintained the distribution characteristics of the data. DataBee is designed specifically to cope with this requirement. You can set up the rules (called an Extraction Set) based on multiple drivers. You could, for example, sample 10% of a table (even on ora7.x) and then put on other rules which pick up specific date ranges. You can also use multiple tables: ie get 5% of all invoices from the INVOICE table and 25 specific customers from the CUSTOMER table. DataBee would cheerfully get all of the customers to support the invoices and all of the invoices to support the customers. Basically the rows are adjusted until every table has the data required required to support all of the rest and the subset is in balance. > I know it can be built by hand They can but it is (as I'm sure you know) a lot of hassle - and it can be a nightmare to get the FK constraints to re-enable if you have a lot of them. An automated tool really is cost-effective. One of our customers used to take a week and 45 skilled DBA hours to cut a subset database - even then only about 200 of the 500 constraints would enable. DataBee cuts the subset in 4 hours (its a push button operation once the Extraction Set is setup) and all 500 constraints enable. DataBee: http://www.databee.com Cheers Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dale Edgar 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).
Oracle Libraries ??
Hi, When we install 8i thru oui Oracle libraries are installed by default arnt they ?? Reagrds OraEtM!! _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eswar the MAD 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: Data Subset Tool?
Larry, Disk is cheaper. :) Jared On Friday 18 January 2002 17:25, Larry Elkins wrote: > Listers, > > I know this subject has been brought up before, and I will go through the > archives as well as pull out the Google Gun, but does anyone have > recommendations on a tool that will create a subset of production data that > is referentially intact? I am aware of DataBee (which Mark mentioned the > other day), and I'm sure I will turn up some others. > > I know it can be built by hand, but it might be more cost effective to buy > something if the price is right. The main feature I would be interested in > is a tool that maintained the distribution characteristics of the data. For > example, assume production has 20 years worth of data. For a query > specifying various criteria, criteria against a date range of a month would > be very selective (assuming even distribution). Now, if you throw only 2 > years in the development environment, a different plan using criteria other > than a month date range, maybe even a different driving table, might be > better. So you now have a case where one plan is better in DEV and a > different plan is better in PROD. An age old problem I'm sure lots of us > have dealt with. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 -- 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: Codde's Rules and Oracle
Here's a URL for a list of the rules: http://luna.pepperdine.edu/~ckettemb/class/Codd12R.html After taking a quick glance at the rules, I think that Oracle fails on 7 and 11, probably others, but those were the ones that stood out without too much pondering. :) Jared On Friday 18 January 2002 09:51, [EMAIL PROTECTED] wrote: > No. > > In fact, I don't believe that there is a database in existence that > does conform to all 12 rules. > > I'll let someone else answer the hard part, just not enough time for that. > > Jared > > > > > > "Vikas S" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/18/02 02:30 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:Codde's Rules and Oracle > > > > Dear All, > > Does Oracle confirm to all 12 Codde's Rules. Can anyone > point out the Rules violated by each version (6/7/8/9) of Oracle. > > Thanks, > Vikas > > > > _ > Chat with friends online, try MSN Messenger: http://messenger.msn.com -- 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).
trouble with 9i import
Hi again, I just wanted to let you know i found the answer and was able to import my data. My oracle_home was not in my system properties in windows and my I had to turn on my Oracle service in my service tools in Windows. After that it was real easy. Thanks for your input. -- 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: Resource management
welcome back, you were missed. Yes, Oracle has resource management. It's set as part of the profile -- look for information on profiles, specifically with a resource_type of "KERNEL" --- "Bellows, Bambi" <[EMAIL PROTECTED]> wrote: > Hi Guys! > > Hope everyone had a good holiday. I'm back from 3 weeks off and > feeling > rested. Maybe a bit too rested. > > I got a question on whether Oracle has a concept of resource > management... > that is, if user X consumes too much CPU time, or the query runs too > long, > whether there is some way to enter limits into Oracle which will kill > the > query and, based on rules, resubmit it for a different time. I > thought the > answer was yes because it sounded so dang familiar... now I'm not > sure if it > was Oracle or Ingres or Sybase or SQL Server, or whether it was > something > that could be set from Financials. > > Your help, as always, is appreciated! > Bambi. > __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- 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: LogMiner and ORA-03113
Just that there have been numerous bugs in logminer with blocksizes != 2K. joe Walter K wrote: > Block size is 8k. Interesting, how is the block size > related? > > > --- Joe Testa <[EMAIL PROTECTED]> wrote: > >>whats your blocksize, anything other than 2K has >>been know to cause all >>kinds of problems. >> >>joe >> >> >>Walter K wrote: >> >> >>>I'm trying to analyze some archive logs via >>> >>LogMiner >> >>>and I keep getting the error ORA-03113 (end of >>> >>file on >> >>>communications channel) when I query the >>>V$LOGMNR_CONTENTS view. I did a "new" and "start" >>>prior to running the query. This process that I am >>>going through works on some log files but not on >>>others. The log files aren't corrupted because I >>> >>can >> >>>restore/recover the database and apply the logs >>>successfully for a roll-forward. I'm just running >>> >>a >> >>>'select count(*)' query at this point. >>> >>>Has anyone else encountered this and come up with >>> >>a >> >>>solution? >>> >>>I've checked MetaLink, Google, etc and so far have >>> >>not >> >>>come up with anything that describes what may be >>> >>the >> >>>cause. I'm running 8.1.7.2 (64bit) on Solaris 8. >>> >>>MANY thanks in advance. >>> >>>-w >>> >>>__ >>>Do You Yahoo!? >>>Send FREE video emails in Yahoo! Mail! >>>http://promo.yahoo.com/videomail/ >>> >>> >> >>-- >>Joe Testa, Oracle DBA >>Want to have a good time with a bunch of geeks? >>Check out: >> >> > http://www.geekcruises.com/standard_interface/future_cruises.html > >>I'm presenting, when registering drop my name :) >> >> >> >> >> >> >>-- >>Please see the official ORACLE-L FAQ: >>http://www.orafaq.com >>-- >>Author: Joe Testa >> 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!? > Send FREE video emails in Yahoo! Mail! > http://promo.yahoo.com/videomail/ > -- Joe Testa, Oracle DBA Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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: HP vs SUN for a UNIX box
I believe that Sun is much cheaper to buy then HP. -Original Message- L. Sent: Friday, January 18, 2002 12:00 PM To: Multiple recipients of list ORACLE-L Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: HP vs SUN for a UNIX box
I work on both and like both. However, I would much rather work on HP. Number one, its the one I know best so I am biased in that way. When a Sun box crashes hard it takes much longer to come back up then a HP. At least on our site. There are pros and cons to both. -Original Message- Sergey Sent: Friday, January 18, 2002 10:51 AM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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: Kimberly Smith 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: Standby Instance questions and HA
It does reduce your maintenance windows though when you are upgrading. Really nice when you are a 24x7 shop. I do not have to have a database down when upgrading the software. I will actually run catalog and catproc with the database open for business and have not had an issue yet. So I do one side of the node, fail the databases over, run the upgrade script, and do the other side after that has successfully been completed. On my N-classes it really does not take long. -Original Message- Sent: Friday, January 18, 2002 9:21 AM To: Multiple recipients of list ORACLE-L Nah, I make sure all servers have the same uid/gid for oracle but I have naming standards for the lv's and filesystems. This allows me to failover multiple primary servers to a single secondary. I was just curious, b/c I have seen other sites that use your method. I prefer not having to synch multiple OH's. I can just see it happen 6 mos from now, failover occurs, but someone forgot the synch, o o :). Thanks. Gene >>> [EMAIL PROTECTED] 01/18/02 09:50AM >>> I have way to many f'ing Oracle Homes to deal with. When I first got here they were all different versions as well. So it was more of a maintenance thing. To tell you the truth someone else originally set it up that way and I liked it so I kept it. It does mean I have to keep more in sync manually then I normally would have to. If I only had one database on the server (or if they were all developed in-house) I probably would have installed the software on failover disks as well. Do you fail over the Unix account as well? -Original Message- Sent: Friday, January 18, 2002 4:50 AM To: Multiple recipients of list ORACLE-L When I failover, I bring the Oracle Home as well. Do you have special reasons for not bringing the Oracle Home over? *just curious* Gene >>> [EMAIL PROTECTED] 01/17/02 08:45PM >>> You will always have the same issues with fail over technology. Your users will get disconnected. My databases take less then 5 minutes to fail over and that is an acceptable time frame to the client. Its great from my standpoint for maintenance cause I can do it on one node, fail the databases over, and bring the other node up to date. I do not have the Oracle software itself in fail over, just the database. We do not find it to hard to work with here. I have no experience with Sun's so I cannot compare them. Whether or not you go with fail over technology all depends on what you are looking for.You will not lose any committed data with HP's (probably not with anyone else's either). Fail over is automatic when configured correctly. I have seen it happen once that I did not even know, it was that quick. Went to go look for my database on the server and it was not there:-) -Original Message- McCann Sent: Thursday, January 17, 2002 10:05 AM To: Multiple recipients of list ORACLE-L Thanks for your help everyone. Very useful advice, although your scaring me of Sun Clusters. At the minute, Parallel server looks the best, with a standby database remotely for disaster. Does anyone know what the HP solution is like (MC Service Guard)? I think some one on this list gave it a good review in the past . Thanks, Jim -Original Message- Sent: 17 January 2002 17:12 To: Multiple recipients of list ORACLE-L IBM HACMP works well. Ooops. guess that means you'll have to change some things. ;-) Seriously, we *did* get the Sun "clustering" working, but it required some serious feet-to-fire holding and gyrations. -Original Message- Sent: Thursday, January 17, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Thanks for the advice everyone. So what do you recommend on a Sun cluster/machines for failover other than OPS? Quest Shareplex? Standby database? Any others? Thanks, Jim -Original Message- Sent: 17 January 2002 16:22 To: Multiple recipients of list ORACLE-L I concur with BB.yea, I ran Sun "cluster" at and it broke ALOT. Kept me and two full time Sun Engineers (they got paid ALOT more) in consulting dollars, but i made a mental note not to use it in "my business". Caveat: this was 1.5 years ago. Things change. Mit Gluck, mein freund... - Ross "mit schuss" Mohan -Original Message- Jim: Sorry, you're not gonna like this answer. HA is a Sun product, not an Oracle product. Under Sun's High Availability, you can configure several modules like Sybase and Oracle. (The Oracle product is Sun Cluster HA-DBMS for Oracle.) It does require what I believe Sun calls a cluster but (IMHO) is a bastardization of the term. It truly is failover, not cluster. We've had lots of problems with it. It's caused us lots of grief, and only in a few instances gained us anything. It is NOT OPS, as the database does not run in parallel, but only on 1 box at a time. (Everything is double cabled, and so the drives are re-mounted on the 2nd box if a failover occurs.) Your users still get disconnected.
Re: Export 7.3.4 / Import 8.1.7?
YES. "Smith, Ron L." <[EMAIL PROTECTED]> wrote: Can I do a full export from 7.3.4 and then do a full import into 8.1.7?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-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).Do You Yahoo!? Send FREE video emails in Yahoo! Mail.
Re: Trouble with imp on 8.1.6
Hi Rick, Try imp system/pass@service file=file.dmp log=test.log fromuser=adtlitetouser=caler John [EMAIL PROTECTED] wrote: exp system/pass@service file=file.dmp log=exp.log user=(adtlite)The exp.log shows all objects exported correctly.imp system/pass@service file=file.dmp log=test.log fromuser=systemtouser=caler tables=(*)Rick orantdba <[EMAIL PROTECTED]> scape.net> cc: Sent by: Subject: Re: Trouble with imp on 8.1.6root@fatcity. com 01/18/2002 11:50 AM Please respond to ORACLE-L HI Rick,Could you share with us the export statement and the import statementyou used. Tha will help figure out what went wrong.John[EMAIL PROTECTED] wrote: Hi All,I did a successfull export on 8.1.6 for user=I am trying to import on same server into another user. I get followingmessage but no tables are created?I have full privs.Can someone help?Connected to: Oracle8i Release 8.1.6.0.0 - ProductionJServer Release 8.1.6.0.0 - ProductionExport file created by EXPORT:V08.01.06 via conventional pathimport done in WE8ISO8859P1 character set and US7ASCII NCHAR character setimport server uses US7ASCII character set (possible charset conversion)Import terminated successfully without warnings.ThanksRick --Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: orantdba 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).
Fw: backup data using tape drive in win2k
> hi, > is it possible to backup data using tape drive in win2k? wat tape drives > does it support and wat command to be used to copy it to tape? > thanks > > Best regards, > Grace Lim > Suy Sing Comm'l Corp. > 632-2474134 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
Regular expressions to the rescue! Here's a test case in case you want to try it. Jared drop table regex; create table regex ( test varchar2(20) ); create or replace function strip_str ( data_in varchar2 ) return varchar2 is test_str varchar2(4000); begin test_str := data_in; owa_pattern.change(test_str, '\x0a', '', 'g'); owa_pattern.change(test_str, '\x0c', '', 'g'); owa_pattern.change(test_str, '\x0d', '', 'g'); return test_str; end; / show error function strip_str insert into regex values( 'carriage' || chr(13) || 'return'); insert into regex values( 'line' || chr(10) || 'feeds' || chr(10)); insert into regex values( 'form feed' || chr(12)); commit; select test from regex; select strip_str(test) test from regex / "Hagedorn, Linda" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/18/02 03:36 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c I couldn't get translate to just strip off the chr(13), but was able to using this: update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid ) where medschoolid like '95701%' ; update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid ) where medschoolid like '95702%' ; Before: 'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED --- -- REG.AMA_LOG MEDSCHOOLID 95701 39353730310D REG.AMA_LOG MEDSCHOOLID 95701 39353730310D REG.AMA_LOG MEDSCHOOLID 95702 39353730320D REG.AMA_LOG MEDSCHOOLID 95704 39353730340D After: 'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED --- -- REG.AMA_LOG MEDSCHOOLID 95701 3935373031 REG.AMA_LOG MEDSCHOOLID 95701 3935373031 REG.AMA_LOG MEDSCHOOLID 95702 3935373032 REG.AMA_LOG MEDSCHOOLID 95704 39353730340D -Original Message- Sent: Friday, January 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L r(1 Linda, I just did something like this yesterday. You will need to use the TRANSLATE function. So you can use an SQL statement like: update set fld1 = translate(fld1,chr(09),'-'); Make sure you only have one weird character in the field though. I actually and a carriage return and a new line back to back and, of course, you can't see them. You might want to use the DUMP function to look at what is really in the field. Good luck! Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Hi, Sometime in the past, data was loaded into tables from spreadsheets and the tabs and form feeds were included in the data. I can locate all the bad data, and am looking for a clear method to remove only the 'bad' character from a field, despite where it occurs. For example, you can see 09 at the end of the rawtohex column. I need to change Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second example are in a numeric field. If anyone has had to do this, I'd appreciate knowing your method. Thanks, Linda Table Column Contents Rawtohex - - REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta 43616E6164612D416C62657274612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba 43616E6164612D4D616E69746F62612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia 43616E6164612D4E6F76612053636F7469612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario 43616E6164612D4F6E746172696F2009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec 43616E6164612D5175656265632009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan 43616E6164612D5361736B617463686577616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Angola416E676F6C612009 REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009 Table Column Contents Rawtohex --- --- -- REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84710 383437
Data Subset Tool?
Listers, I know this subject has been brought up before, and I will go through the archives as well as pull out the Google Gun, but does anyone have recommendations on a tool that will create a subset of production data that is referentially intact? I am aware of DataBee (which Mark mentioned the other day), and I'm sure I will turn up some others. I know it can be built by hand, but it might be more cost effective to buy something if the price is right. The main feature I would be interested in is a tool that maintained the distribution characteristics of the data. For example, assume production has 20 years worth of data. For a query specifying various criteria, criteria against a date range of a month would be very selective (assuming even distribution). Now, if you throw only 2 years in the development environment, a different plan using criteria other than a month date range, maybe even a different driving table, might be better. So you now have a case where one plan is better in DEV and a different plan is better in PROD. An age old problem I'm sure lots of us have dealt with. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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: problem with 9i import
Hi, This is everything on the screen before I answer yes to the last question. Sorry I had to attach it but it was to big to put in here. Any help would really be appreciated. Thanks oracle script.rtf Description: RTF file
RE: _system_trig_enabled=false - URGENT
Of course, had you looked at the script before you executed it, and then made sure that you understood what it was doing, you would never have given yourself such a fright. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 5:51 PM To: Multiple recipients of list ORACLE-L Thanks! This gives the correct information. I guess I'm inclined to trust anything from ixora blindly :) Jay Miller -Original Message- Sent: Friday, January 18, 2002 4:56 PM To: Multiple recipients of list ORACLE-L You are not reading the value. Try this: select a.INDX NUM, a.KSPPINM NAME, a.KSPPITY TYPE, a.KSPPDESC DESCRIPTION, b.KSPPSTVL VALUE, b.KSPPSTDF ISDEFAULT from sys.X_$KSPPI a, sys.X_$KSPPCV b where a.indx = b.indx and a.ksppinm like '%trig%' order by 2 -Original Message- Sent: Friday, January 18, 2002 3:21 PM To: Multiple recipients of list ORACLE-L So I was just checking out the ixora script to look at values for hidden parameters. And I discovered that _system_trig_enabled is FALSE on all my databases! If this is the default then why is it so important to specifically set it to false during 8.1.7 upgrades? This value was FALSE not only for databases I'd upgraded to 8.1.7 and therefore set specifically to false in the init.ora at some point but also in my old 8.1.6 databases and some initial installation 8.1.7 databases that were never upgraded. I'm very nervous just now. SQL> set linesize 128 column name format a42 select x.ksppinm name, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') sesmod, decode( bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE' ) sysmod, ksppdesc description from sys.x_$ksppi x where x.inst_id = userenv('Instance') and translate(ksppinm,'_','#') like '#%' and x.ksppinm like '%trig%' order by 1;SQL> SQL> 23456789 10 NAME SESMO SYSMODDESCRIPTION -- - - - --- _system_trig_enabled FALSE FALSE system triggers are e nabled Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: LogMiner and ORA-03113
Block size is 8k. Interesting, how is the block size related? --- Joe Testa <[EMAIL PROTECTED]> wrote: > whats your blocksize, anything other than 2K has > been know to cause all > kinds of problems. > > joe > > > Walter K wrote: > > > I'm trying to analyze some archive logs via > LogMiner > > and I keep getting the error ORA-03113 (end of > file on > > communications channel) when I query the > > V$LOGMNR_CONTENTS view. I did a "new" and "start" > > prior to running the query. This process that I am > > going through works on some log files but not on > > others. The log files aren't corrupted because I > can > > restore/recover the database and apply the logs > > successfully for a roll-forward. I'm just running > a > > 'select count(*)' query at this point. > > > > Has anyone else encountered this and come up with > a > > solution? > > > > I've checked MetaLink, Google, etc and so far have > not > > come up with anything that describes what may be > the > > cause. I'm running 8.1.7.2 (64bit) on Solaris 8. > > > > MANY thanks in advance. > > > > -w > > > > __ > > Do You Yahoo!? > > Send FREE video emails in Yahoo! Mail! > > http://promo.yahoo.com/videomail/ > > > > > -- > Joe Testa, Oracle DBA > Want to have a good time with a bunch of geeks? > Check out: > http://www.geekcruises.com/standard_interface/future_cruises.html > I'm presenting, when registering drop my name :) > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Joe Testa > 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!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K 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: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields I couldn't get translate to just strip off the chr(13), but was able to using this: update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid ) where medschoolid like '95701%' ; update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid ) where medschoolid like '95702%' ; Before: 'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED--- -- REG.AMA_LOG MEDSCHOOLID 95701 39353730310DREG.AMA_LOG MEDSCHOOLID 95701 39353730310DREG.AMA_LOG MEDSCHOOLID 95702 39353730320DREG.AMA_LOG MEDSCHOOLID 95704 39353730340D After: 'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED--- -- REG.AMA_LOG MEDSCHOOLID 95701 3935373031REG.AMA_LOG MEDSCHOOLID 95701 3935373031REG.AMA_LOG MEDSCHOOLID 95702 3935373032REG.AMA_LOG MEDSCHOOLID 95704 39353730340D -Original Message-From: Carle, William T (Bill), ALINF [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 12:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1 Linda, I just did something like this yesterday. You will need to use the TRANSLATE function. So you can use an SQL statement like: update set fld1 = translate(fld1,chr(09),’-‘); Make sure you only have one weird character in the field though. I actually and a carriage return and a new line back to back and, of course, you can’t see them. You might want to use the DUMP function to look at what is really in the field. Good luck! Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message-From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 1:57 PMTo: Multiple recipients of list ORACLE-LSubject: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1 Hi, Sometime in the past, data was loaded into tables from spreadsheets and the tabs and form feeds were included in the data. I can locate all the bad data, and am looking for a clear method to remove only the 'bad' character from a field, despite where it occurs. For example, you can see 09 at the end of the rawtohex column. I need to change Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second example are in a numeric field. If anyone has had to do this, I'd appreciate knowing your method. Thanks, Linda Table Column Contents Rawtohex - - REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta 43616E6164612D416C62657274612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba 43616E6164612D4D616E69746F62612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia 43616E6164612D4E6F76612053636F7469612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario 43616E6164612D4F6E746172696F2009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec 43616E6164612D5175656265632009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan 43616E6164612D5361736B617463686577616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Angola 416E676F6C612009 REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009 Table Column Contents Rawtohex --- --- -- REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
Re: HP vs SUN for a UNIX box
... running Linux [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/18/02 02:35 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: HP vs SUN for a UNIX box IBM David A. Barbour Oracle DBA, OCP AISD 512-414-1002 "Babich , Sergey" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: HP vs SUN for a UNIX box Sent by: [EMAIL PROTECTED] om 01/18/2002 12:50 PM Please respond to ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- 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: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields Thanks very much Bill. Regards, Linda -Original Message-From: Carle, William T (Bill), ALINF [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 12:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1 Linda, I just did something like this yesterday. You will need to use the TRANSLATE function. So you can use an SQL statement like: update set fld1 = translate(fld1,chr(09),’-‘); Make sure you only have one weird character in the field though. I actually and a carriage return and a new line back to back and, of course, you can’t see them. You might want to use the DUMP function to look at what is really in the field. Good luck! Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message-From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 1:57 PMTo: Multiple recipients of list ORACLE-LSubject: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1 Hi, Sometime in the past, data was loaded into tables from spreadsheets and the tabs and form feeds were included in the data. I can locate all the bad data, and am looking for a clear method to remove only the 'bad' character from a field, despite where it occurs. For example, you can see 09 at the end of the rawtohex column. I need to change Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second example are in a numeric field. If anyone has had to do this, I'd appreciate knowing your method. Thanks, Linda Table Column Contents Rawtohex - - REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta 43616E6164612D416C62657274612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba 43616E6164612D4D616E69746F62612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia 43616E6164612D4E6F76612053636F7469612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario 43616E6164612D4F6E746172696F2009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec 43616E6164612D5175656265632009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan 43616E6164612D5361736B617463686577616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Angola 416E676F6C612009 REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009 Table Column Contents Rawtohex --- --- -- REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
RE: Help
Yeah... It's a hidden init.ora parameter... _enable_magic=TRUE :-) -Original Message- Sent: Friday, January 18, 2002 1:31 PM To: Multiple recipients of list ORACLE-L Hi list, Can any body tell me about magic for oracle? TIA, Shreeni -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim 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: HP vs SUN for a UNIX box
here go the vendor wars again: for ha, go w/ hp, otherwise go for the most bang for the buck, depends on how desperate the sales person is? i've found hp's ha sw (mc serviceguard), backup sw omniback inexpensive compared to other vendors. look at the total package not just hardware, e.g. storage vendor, backup strategy, ha, etc? gene ps. i like all unix flavors just keep me away from windoze! >>> [EMAIL PROTECTED] 01/18/02 01:50PM >>> Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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: _system_trig_enabled=false - URGENT
Thanks! This gives the correct information. I guess I'm inclined to trust anything from ixora blindly :) Jay Miller -Original Message- Sent: Friday, January 18, 2002 4:56 PM To: Multiple recipients of list ORACLE-L You are not reading the value. Try this: select a.INDX NUM, a.KSPPINM NAME, a.KSPPITY TYPE, a.KSPPDESC DESCRIPTION, b.KSPPSTVL VALUE, b.KSPPSTDF ISDEFAULT from sys.X_$KSPPI a, sys.X_$KSPPCV b where a.indx = b.indx and a.ksppinm like '%trig%' order by 2 -Original Message- Sent: Friday, January 18, 2002 3:21 PM To: Multiple recipients of list ORACLE-L So I was just checking out the ixora script to look at values for hidden parameters. And I discovered that _system_trig_enabled is FALSE on all my databases! If this is the default then why is it so important to specifically set it to false during 8.1.7 upgrades? This value was FALSE not only for databases I'd upgraded to 8.1.7 and therefore set specifically to false in the init.ora at some point but also in my old 8.1.6 databases and some initial installation 8.1.7 databases that were never upgraded. I'm very nervous just now. SQL> set linesize 128 column name format a42 select x.ksppinm name, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') sesmod, decode( bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE' ) sysmod, ksppdesc description from sys.x_$ksppi x where x.inst_id = userenv('Instance') and translate(ksppinm,'_','#') like '#%' and x.ksppinm like '%trig%' order by 1;SQL> SQL> 23456789 10 NAME SESMO SYSMODDESCRIPTION -- - - - --- _system_trig_enabled FALSE FALSE system triggers are e nabled Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: HP vs SUN for a UNIX box
Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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).
OpenSSL & OAS 4082
Has anyone gotten OAS 4082 to work with a certificate generated with OpenSSL? Our sysadmin generated the cert with OpenSSL and registered it and all that, so it is bought and paid for. Before I have him use genreq to generate the certificate and go through the whole process again, I'd like to verify that it is necessary to do so. Can't find anything on Metalink or on the web that tells me one way or the other whether this works or doesn't. Thanks, Glenn Stauffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer 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: max_io_size
Patrice, You can determine this for yourself with a script: http://www.ixora.com.au/scripts/io_opt.htm The script is at the bottom of the page. It's a pretty simple test, you can easily do it manually as well. Jared "Boivin, Patrice J" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/18/02 11:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:max_io_size Where can I find information on this parameter on the Tru64 UNIX platform? I did a scan of the manuals for Tru64 UNIX at docs.oracle.com, but didn't find mention of it anywhere. I want to set db_file_multiblock_read_count properly... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: HP-UX 11/8.1.6.x/Index-Organized Tables
Title: RE: HP-UX 11/8.1.6.x/Index-Organized Tables Create as/Alter table rename prod to backup/Alter table rename new to prod/ -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 7:31 AM To: Multiple recipients of list ORACLE-L Subject: HP-UX 11/8.1.6.x/Index-Organized Tables Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? Thanx, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: SPACE FREE HOW?
Don't forget to disable any insert triggers... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] "Hit any PHB to continue..." orantdba@nets cape.net To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Re: SPACE FREE HOW? com 01/18/02 12:56 PM Please respond to ORACLE-L Hi Seema, In a word NO. From the documentation: Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace. Yo can free only unused space above the high water mark (that is, the point beyound which database blocks have not yet been formatted to receive data). Another lister provided you with the best solution i know of: 1. copy data to a holding table create table holding_table nologging as select * from my_table_that_is_too_big; 2. truncate the table truncate table my_table_that_is_too_big; 3. copy the data over insert into my_table_that_is_too_big select * from holding_table; 4. drop the holding table drop table holding_table; John [EMAIL PROTECTED] wrote: Hi Can I use following command to recover the space. alter table deallocate unused; I don't want to truncate and import. Thx -Seema From: "Ron Rogers" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 17 Jan 2002 13:55:47 -0800 Seema, The table has set it'd HWM where the old data resided. The easiest? method of recovering the space is to export the table and then truncate the table followed by importing the table data back into the table. The truncate function will remove all of the data and re-establish the size back to original. ROR mª¿ªm >>> [EMAIL PROTECTED] 01/17/02 03:57PM >>> Hi I deleted millions of rows from diffrent tables and I have not seen any impact on database size.What I have to do to get that free space? Is it necessary to shutdown the database? Thx -Seema _ 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: Seema Singh INET: oracledbam@hotmReceived: from CONNECT-MTA by galotterail.com Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers
HP vs SUN for a UNIX box
-Original Message- Sent: Friday, January 18, 2002 1:48 PM To: '[EMAIL PROTECTED]' Subject:HP vs SUN for a UNIX box Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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).
Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1
Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields Hi, Sometime in the past, data was loaded into tables from spreadsheets and the tabs and form feeds were included in the data. I can locate all the bad data, and am looking for a clear method to remove only the 'bad' character from a field, despite where it occurs. For example, you can see 09 at the end of the rawtohex column. I need to change Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second example are in a numeric field. If anyone has had to do this, I'd appreciate knowing your method. Thanks, Linda Table Column Contents Rawtohex - - REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta 43616E6164612D416C62657274612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba 43616E6164612D4D616E69746F62612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia 43616E6164612D4E6F76612053636F7469612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario 43616E6164612D4F6E746172696F2009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec 43616E6164612D5175656265632009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan 43616E6164612D5361736B617463686577616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Angola 416E676F6C612009 REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009 Table Column Contents Rawtohex --- --- -- REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
RE: max_io_size
Ross, IMHO Depends on Your IO subsystem. I am on HP 11.0 and XP 256 and ORACLE 816 (ALL 64 BIT) and ORACLE gets data in 1M chunk of data in one IO call while all the others being the same except storage being AUTORAID we are getting 256 MB chunks. Steve Admas has a script and some goodies on this and you could set your value as per your results. Check teh following link http://www.ixora.com.au/scripts/io_opt.htm and click multiblock_read_test.sql . HTH Cheers, RS --- "Mohan, Ross" <[EMAIL PROTECTED]> wrote: > oracle still does 64K read afaik. > > so, 64K divided by block size, ceteris paribus > > -Original Message- > Sent: Friday, January 18, 2002 2:56 PM > To: Multiple recipients of list ORACLE-L > > > Where can I find information on this parameter on > the Tru64 UNIX platform? > > I did a scan of the manuals for Tru64 UNIX at > docs.oracle.com, but didn't > find mention of it anywhere. > > I want to set db_file_multiblock_read_count > properly... > > Regards, > Patrice Boivin > Systems Analyst (Oracle Certified DBA) > > Systems Admin & Operations | Admin. et Exploit. des > systèmes > Technology Services| Services technologiques > Informatics Branch | Direction de > l'informatique > Maritimes Region, DFO | Région des Maritimes, > MPO > > E-Mail: [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Boivin, Patrice J > 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: Mohan, Ross > 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!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sakthi , Raj 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: max_io_size
Patrice - I was unable to catch my sys admin before he left. I went to Google and did a search for max_io_size and received several interesting links. In one document it was referred to as the "operating system's maximum I/O size". On the Tru64, that is a very large number, so I suspect that you are unlikely to exceed the limit unless you're doing something really "cutting-edge". Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 1:56 PM To: Multiple recipients of list ORACLE-L Where can I find information on this parameter on the Tru64 UNIX platform? I did a scan of the manuals for Tru64 UNIX at docs.oracle.com, but didn't find mention of it anywhere. I want to set db_file_multiblock_read_count properly... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Trouble with imp on 8.1.6
exp system/pass@service file=file.dmp log=exp.log user=(adtlite) The exp.log shows all objects exported correctly. imp system/pass@service file=file.dmp log=test.log fromuser=system touser=caler tables=(*) Rick orantdba scape.net> cc: Sent by: Subject: Re: Trouble with imp on 8.1.6 root@fatcity. com 01/18/2002 11:50 AM Please respond to ORACLE-L HI Rick, Could you share with us the export statement and the import statement you used. Tha will help figure out what went wrong. John [EMAIL PROTECTED] wrote: >Hi All, > >I did a successfull export on 8.1.6 for user= >I am trying to import on same server into another user. I get following >message but no tables are created? >I have full privs. > >Can someone help? > >Connected to: Oracle8i Release 8.1.6.0.0 - Production >JServer Release 8.1.6.0.0 - Production > >Export file created by EXPORT:V08.01.06 via conventional path >import done in WE8ISO8859P1 character set and US7ASCII NCHAR character set >import server uses US7ASCII character set (possible charset conversion) >Import terminated successfully without warnings. > >Thanks >Rick > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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:Resource management
Bambi, Boy you ARE rested!! Yes you can, their in the user profile & they work darn well if you set 'resource_limit=true' in the init.ora file. The only problem is that it does not re-submit the query, it just terminates the session. Saves my bacon more times than I care to add up. DickG. Reply Separator Author: "Bellows; Bambi" <[EMAIL PROTECTED]> Date: 1/18/2002 1:05 PM Hi Guys! Hope everyone had a good holiday. I'm back from 3 weeks off and feeling rested. Maybe a bit too rested. I got a question on whether Oracle has a concept of resource management... that is, if user X consumes too much CPU time, or the query runs too long, whether there is some way to enter limits into Oracle which will kill the query and, based on rules, resubmit it for a different time. I thought the answer was yes because it sounded so dang familiar... now I'm not sure if it was Oracle or Ingres or Sybase or SQL Server, or whether it was something that could be set from Financials. Your help, as always, is appreciated! Bambi. http://www.w3.org/TR/REC-html40";> cid:[EMAIL PROTECTED]";> Hi Guys! Hope everyone had a good holiday. I'm back from 3 weeks off and feeling rested. Maybe a bit too rested. I got a question on whether Oracle has a concept of resource management... that is, if user X consumes too much CPU time, or the query runs too long, whether there is some way to enter limits into Oracle which will kill the query and, based on rules, resubmit it for a different time. I thought the answer was yes because it sounded so dang familiar... now I'm not sure if it was Oracle or Ingres or Sybase or SQL Server, or whether it was something that could be set from Financials. Your help, as always, is appreciated! Bambi. -- 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: Unexplained table growth
Hmmm...the AVG_ROW_LEN is the same for both: 72. The part that sticks out is the production table has a NUM_FREELIST_BLOCKS of 28569, while test is at 5. Any ideas as to how this could be??? Almost like the TRUNCATE didn't clear out the freelist??? Or am I missing/misinterpreting something here? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, January 18, 2002 2:15 PM To: Multiple recipients of list ORACLE-L If I were you I'd try to analyze both tables (prod and test), posibly on a small sample, to check whether the average row length is identical. The 'comment' field, which can be close enough to 60%, may be NULL in your test database and full in most cases in prod. Something else one could think of is a PCTFREE difference. It may be higher on your prod database. There may also be differences in the per-tablespace minimum allocation values. S Faroult "Jesse, Rich" wrote: > > Hi all, > > 8.1.7.2.0 on HP/UX 11.0 -- A developer tested a table recreate in our test > DB. Here's the scenario: > > CREATE TABLE MY_BIG_TABLE ( > FISCAL_YEAR NUMBER (5), > PERIODCHAR (2), > ACCOUNTNO CHAR (12), > TRANSTYPE CHAR (2), > TRANSQTY FLOAT, > TRANSAMNT FLOAT, > COMMENT_TEXT CHAR (30), > TRANSDATE DATE) >TABLESPACE QT_APPS1 >PCTFREE 10 >PCTUSED 40 >INITRANS 2 >MAXTRANS 255 > STORAGE ( >INITIAL 209715200 >NEXT 10485760 >PCTINCREASE 0 >MINEXTENTS 1 >MAXEXTENTS 249 >FREELISTS 1 FREELIST GROUPS 1 ) >NOCACHE; > > The table previously had a total of 14 extents, giving it a size of 330MB. > The dev TRUNCATED the table (I don't know if "REUSE STORAGE" was used), and > added 4,054,632 (4M) rows. The table is still at 14 extents and 330MB. > > So, all's well in test, the dev did the same in production. The row count > was *slightly* higher, at 4.069,106, but the table size jumped 60% to 560MB. > The DB_BLOCK_SIZE on both DBs is 8K. Of course, this filled up the TBS and > caused havoc. > > I looked in DBA_AUDIT_TRAIL and DBA_TAB_MODIFICATIONS, but I can't find any > significant difference between what was done to the table in test and prod. > The table in production has NO deletes recorded in DBA_TAB_MODIFICATIONS, > just inserts. > > Can anyone think of a scenario as to why this table would grow in prod but > not test with relatively the same number of rows and the exact same table > layout??? The only thing I can think of is that a "REUSE STORAGE" was > issued on one TRUNCATE, but not another, but I still don't see how that > could account for the table growth. > > I'm going thru LogMiner now, but as our test DB is in archivelog mode, I can > only look in production (and it's taking forever!). > > TIA! > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: HP vs SUN for a UNIX box
-- "Babich , Sergey" <[EMAIL PROTECTED]> > Hi, dear listers, > My boss has decided to switch production to a UNIX box and now he's asking > me for a recommendation between HP and SUN. I am a newbee to UNIX (to put > it the softest way). I've heard people say HP is better, but need more > than that... > The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice > is greatly appreciated. > Regards, HP are generally more managable than Sun's. One advantage is HP's use of LVM (simpler to handle than most) and the ability to add devices on the fly (via ioscan and insf). It has enough foibles to drive anyone batty, but so does everything else so that's probably par :-) The main drawback to HP-UX is that compiling thing on it tends to be a bit of a pain. HP's lib's are a bit flakier than most and gcc doesn't handle them well. You can always use HP's compiler but it ain't cheap (neither is Sun's but gcc handles Solaris & its lib's better). You also have more control over the file system. Combining larger file system pages w/ appropraite striping can give you a nice boost in performance w/ RAID sytsems. -- 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: SPACE FREE HOW?
Seema, with alter table deallocate, you can reclaim only the space above the high water mark in a segment. The only way the high water mark can be moved downward is to truncate the segment or drop and recreate the segment. Sunil Nookala Dell Computer Corp. Austin, TX -Original Message- Sent: Thursday, January 17, 2002 2:58 PM To: Multiple recipients of list ORACLE-L Hi I deleted millions of rows from diffrent tables and I have not seen any impact on database size.What I have to do to get that free space? Is it necessary to shutdown the database? Thx -Seema _ 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: 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). -- 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: SPACE FREE HOW?
Hi Seema, In a word NO. From the documentation: Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace. Yo can free only unused space above the high water mark (that is, the point beyound which database blocks have not yet been formatted to receive data). Another lister provided you with the best solution i know of: 1. copy data to a holding table create table holding_table nologging as select * from my_table_that_is_too_big; 2. truncate the table truncate table my_table_that_is_too_big; 3. copy the data over insert into my_table_that_is_too_big select * from holding_table; 4. drop the holding table drop table holding_table; John [EMAIL PROTECTED] wrote: Hi Can I use following command to recover the space. alter table deallocate unused; I don't want to truncate and import. Thx -Seema From: "Ron Rogers" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 17 Jan 2002 13:55:47 -0800 Seema, The table has set it'd HWM where the old data resided. The easiest? method of recovering the space is to export the table and then truncate the table followed by importing the table data back into the table. The truncate function will remove all of the data and re-establish the size back to original. ROR mª¿ªm >>> [EMAIL PROTECTED] 01/17/02 03:57PM >>> Hi I deleted millions of rows from diffrent tables and I have not seen any impact on database size.What I have to do to get that free space? Is it necessary to shutdown the database? Thx -Seema _ 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: Seema Singh INET: oracledbam@hotmReceived: from CONNECT-MTA by galotterail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
Re: HP vs SUN for a UNIX box
IBM David A. Barbour Oracle DBA, OCP AISD 512-414-1002 "Babich , Sergey" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: HP vs SUN for a UNIX box Sent by: [EMAIL PROTECTED] om 01/18/2002 12:50 PM Please respond to ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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).
Export 7.3.4 / Import 8.1.7?
Can I do a full export from 7.3.4 and then do a full import into 8.1.7? 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:RE: HP vs SUN for a UNIX box
Sergey, I asked my SA to add his 2 cents, guess he's not inclined. Anyway, I've been on HP for the last 10 years, coming from VMS, & I love it. VERY stable, easy to use, although I don't do the admin any longer it was easy with SAM even when it was a character based tool. Rman/OmniBack integration is well documented in the OmniBack manuals & works as advertised. HP support is pretty darn good as well, especially in the middle of the night when the system won't boot. I've had HP support tech's remotely logged in to the system helping get it back online. Also if you have a problem that is not in the area of the tech you call they normally conference in the needed assistance. Problems can be resolved as fast as they occur. We also don't use disk arrays from HP, but then HP & EMC really like each other. If you want an inexpensive disk alternative look into NetAppliance. Dick Goulet Senior Oracle DBA Reply Separator Author: "Babich ; Sergey" <[EMAIL PROTECTED]> Date: 1/18/2002 1:25 PM Thank you, Paul, I'll forward it to my boss... Anyone else to add to it? Thank you very much, Sergey Babich -Original Message- Sent: Friday, January 18, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Stay with HP. It's a hell of a lot more reliable, and does not require anywhere near the # of patches, and its patches work far more often that Sun's. I've done 5 years with each, and there's no question that HP is superior. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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.o
RE: HP-UX 11/8.1.6.x/Index-Organized Tables
ALTER TABLE ... MOVE HTH Tim -Original Message- Sent: Friday, January 18, 2002 10:31 AM To: Multiple recipients of list ORACLE-L Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? Thanx, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: Johnston, Tim 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: SQL question
[EMAIL PROTECTED] wrote: > > Hi i am trying to insert records into a table by selectiog from table 2, i > want to insert a sequence,, and the distinct values from table 1 but i get > an error: > > Query used: > INSERT INTO contacts (cont_id,cont_contact_surname) > (SELECT seq_cont.nextval,distinct delegated_person > FROM sierras > WHERE delegated_person is not null) > > Error; > > select seq_cont.nextval,distinct delegated_person >* > ORA-00936: missing expression > > How can i modify the query to obtain the correct results > > cheers all. > INSERT INTO contacts (cont_id,cont_contact_surname) SELECT seq_cont.nextval, a.delegated_person FROM (select distinct delegated_person from sierras WHERE delegated_person is not null) a should probably work but is ugly (whenever I can get rid of DISTINCT, I do). -- Regards, Stephane Faroult Oriole Ltd -- 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: LogMiner and ORA-03113
whats your blocksize, anything other than 2K has been know to cause all kinds of problems. joe Walter K wrote: > I'm trying to analyze some archive logs via LogMiner > and I keep getting the error ORA-03113 (end of file on > communications channel) when I query the > V$LOGMNR_CONTENTS view. I did a "new" and "start" > prior to running the query. This process that I am > going through works on some log files but not on > others. The log files aren't corrupted because I can > restore/recover the database and apply the logs > successfully for a roll-forward. I'm just running a > 'select count(*)' query at this point. > > Has anyone else encountered this and come up with a > solution? > > I've checked MetaLink, Google, etc and so far have not > come up with anything that describes what may be the > cause. I'm running 8.1.7.2 (64bit) on Solaris 8. > > MANY thanks in advance. > > -w > > __ > Do You Yahoo!? > Send FREE video emails in Yahoo! Mail! > http://promo.yahoo.com/videomail/ > -- Joe Testa, Oracle DBA Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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).
Help
Hi list, Can any body tell me about magic for oracle? TIA, Shreeni BEGIN:VCARD VERSION:2.1 N:Kodikal Bhaskar Rao;Shreenivasa;R FN:Shreenivasa Rao NICKNAME:Seena ORG:e-Zing Technologies Inc TITLE:Oracle DBA TEL;WORK;VOICE:(212) 233-9861 TEL;HOME;VOICE:(732) 326-1899 TEL;CELL;VOICE:732-670-7066 TEL;WORK;FAX:(212) 233-9862 ADR;WORK:;;41-43, Beekman Street;NY;NY;10010;United States of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:41-43, Beekman Street=0D=0ANY, NY 10010=0D=0AUnited States of America ADR;HOME:;;741, Cheryl Dr;Iselin;NJ;08830-3110;USA LABEL;HOME;ENCODING=QUOTED-PRINTABLE:741, Cheryl Dr=0D=0AIselin, NJ 08830-3110=0D=0AUSA X-WAB-GENDER:2 URL;HOME:http://www.sritri.com URL;WORK:http://www.e-zingtech.com BDAY:19630202 EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20011230T034250Z END:VCARD
RE: _system_trig_enabled=false - URGENT
You are not reading the value. Try this: select a.INDX NUM, a.KSPPINM NAME, a.KSPPITY TYPE, a.KSPPDESC DESCRIPTION, b.KSPPSTVL VALUE, b.KSPPSTDF ISDEFAULT from sys.X_$KSPPI a, sys.X_$KSPPCV b where a.indx = b.indx and a.ksppinm like '%trig%' order by 2 -Original Message- Sent: Friday, January 18, 2002 3:21 PM To: Multiple recipients of list ORACLE-L So I was just checking out the ixora script to look at values for hidden parameters. And I discovered that _system_trig_enabled is FALSE on all my databases! If this is the default then why is it so important to specifically set it to false during 8.1.7 upgrades? This value was FALSE not only for databases I'd upgraded to 8.1.7 and therefore set specifically to false in the init.ora at some point but also in my old 8.1.6 databases and some initial installation 8.1.7 databases that were never upgraded. I'm very nervous just now. SQL> set linesize 128 column name format a42 select x.ksppinm name, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') sesmod, decode( bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE' ) sysmod, ksppdesc description from sys.x_$ksppi x where x.inst_id = userenv('Instance') and translate(ksppinm,'_','#') like '#%' and x.ksppinm like '%trig%' order by 1;SQL> SQL> 23456789 10 NAME SESMO SYSMODDESCRIPTION -- - - - --- _system_trig_enabled FALSE FALSE system triggers are e nabled Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: HP vs SUN for a UNIX box
Sergey - One factor you may want to consider is the timeframe of your decision. If it is just a decision for your Lawson box, then that isn't so critical. It is pretty easy to move Lawson from one system to another. Last fall we were looking at making a decision for all our systems. We are on Compaq Tru64, and that doesn't have a long future ahead of it. The META Group advice was that only three operating systems can be considered to have a stable future: 1. MS W2K 2. Linux 3. Solaris Most of the other Unix vendors are facing the next generation of computer chip architecture. I don't believe that HP is investing in the next generation itself, but will be switching to the new Intel 64-bit chips. Based on that advice, we chose Solaris because W2K and Linux are not up to the same standards as Solaris today. To repeat, we are looking at a lot of programming beyond Lawson, so our decision was based on more than simply purchasing the next box. We will have our Lawson system on Tru64 for several more years. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Thank you, Paul, I'll forward it to my boss... Anyone else to add to it? Thank you very much, Sergey Babich -Original Message- Sent: Friday, January 18, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Subject:RE: HP vs SUN for a UNIX box Stay with HP. It's a hell of a lot more reliable, and does not require anywhere near the # of patches, and its patches work far more often that Sun's. I've done 5 years with each, and there's no question that HP is superior. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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
RE: SQL question
try INSERT INTO contactsc (cont_id,cont_contact_surname) (SELECT seq_cont.nextval,distinct delegated_person FROM sierras WHERE delegated_person is not null and delegated_person <>c.cont_contact_surname) > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Friday, January 18, 2002 11:51 AM > To: Multiple recipients of list ORACLE-L > Subject: SQL question > > Hi i am trying to insert records into a table by selectiog from table 2, i > want to insert a sequence,, and the distinct values from table 1 but i get > an error: > > Query used: > INSERT INTO contacts (cont_id,cont_contact_surname) > (SELECT seq_cont.nextval,distinct delegated_person > FROM sierras > WHERE delegated_person is not null) > > Error; > > select seq_cont.nextval,distinct delegated_person >* > ORA-00936: missing expression > > How can i modify the query to obtain the correct results > > cheers all. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mac Isaac, 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).
RE: max_io_size
oracle still does 64K read afaik. so, 64K divided by block size, ceteris paribus -Original Message- Sent: Friday, January 18, 2002 2:56 PM To: Multiple recipients of list ORACLE-L Where can I find information on this parameter on the Tru64 UNIX platform? I did a scan of the manuals for Tru64 UNIX at docs.oracle.com, but didn't find mention of it anywhere. I want to set db_file_multiblock_read_count properly... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Mohan, Ross 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).
Resource management
Hi Guys! Hope everyone had a good holiday. I'm back from 3 weeks off and feeling rested. Maybe a bit too rested. I got a question on whether Oracle has a concept of resource management... that is, if user X consumes too much CPU time, or the query runs too long, whether there is some way to enter limits into Oracle which will kill the query and, based on rules, resubmit it for a different time. I thought the answer was yes because it sounded so dang familiar... now I'm not sure if it was Oracle or Ingres or Sybase or SQL Server, or whether it was something that could be set from Financials. Your help, as always, is appreciated! Bambi.
Re: Lookup Table Usage
Auditing would be more reliable, it's static. Using v$db_object_cache would require monitoring the view, determining the difference from the last time you checked it, etc. You would lose any data between the last time you checked and a database shutdown. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/17/02 10:45 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: Lookup Table Usage Try using V$DB_OBJECT_CACHE , I think it does exist in 7.3.4 Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd [EMAIL PROTECTED] 17/01/2002 07:07 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: Re: Lookup Table Usage Use database auditing. Lookup 'audit' in the SQL manual. Jared "Whittle Jerome Contr NCI"To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Lookup Table Usage Sent by: [EMAIL PROTECTED] 01/16/02 01:20 PM Please respond to ORACLE-L Hi, Oracle 7.3.4 on Unix. The database has dozens of little lookup tables. I'd like to cache those used the most. Is there a way to see how often a table is queried? Thanks, Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- 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: used segment space in bytes
Thanks for the response Diego. I will try running this on my system. Rgds Deepak --- Diego Cutrone <[EMAIL PROTECTED]> wrote: > Deepak: > As a matter of fact I think you can find how > many blocks below HWM > are unused (have no rows at all), by counting the > number of blocks that are > below the HWM and hold at least one row. (check my > previous mail) > > SELECT COUNT(DISTINCT > SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM TABLE; > > This query will count how many blocks have data (and > of course) are below > HWM. > I know this is not a perfect calclulation, because > it can report 10 blocks > used and these blocks could only have 1 row each, > but it's better than the HWM value or than the > dbms_space.free_blocks, I > think. > > Please correct me if I'm wrong... > > Greetings > DC > > - Original Message - > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Sent: Thursday, January 17, 2002 9:05 PM > > > > Hi Do, > > > > Here is the breakup for a space usage for a > segment: > > > > 1. Allocated size (use dba_segments) > > > > 2 Used Blocks in segments(use dba_tables.blocks) > > --> Truly Used ( ??) > > --> Free Blocks (??) > > > > 3. Unused Blocks (use dba_tables.empty_blocks) > > > > the caveat i guess is in step 2. The used block > number > > is based on the high watermark. which means that > the > > number you will get for point 2. above will also > > include "space that contains no rows" becuz its > below > > the high water mark. > > > > Afaik, there is'nt a way to find these "free > blocks" > > below the high water mark.. unless you re-build > table > > and then get the value from dba_tables.blocks .. > > > > feel free to correct me .. > > > > Deepak > > > > > -Original Message- > > > Sent: Thursday, January 17, 2002 12:12 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > hi guys! > > > > > > what would be the easiest way to compute the > space > > > in bytes used by a segment? > > > it is rather easy to determine the # of used > blocks > > > by a specific segment by looking at the > dba_extents > > > for example. but how many blocks have been > really > > > used > > > in one of those extents. i guess i would be able > > > to compute the free blocks by using the > dbms_space > > > package but it just seems to complicated. > > > any hints ... ? > > > > > > thank you > > > > > > -do > > > > > > > > > > > > > > > __ > > Do You Yahoo!? > > Send FREE video emails in Yahoo! Mail! > > http://promo.yahoo.com/videomail/ > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Deepak Thapliyal > > 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: Diego Cutrone > 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!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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: HP vs SUN for a UNIX box
Thanks, it is very important to me Regards, Serge -Original Message- Sent: Friday, January 18, 2002 4:05 PM To: Multiple recipients of list ORACLE-L Subject:RE: HP vs SUN for a UNIX box Using both HP 11.0 and Solaris 2.8, I have a preference for HP. Just little toolset things like SAM for a GUI SysAdmin, "top" and "GlancePlus", although GlancePlus, a system monitor, I guess I would consider a huge advantage over what Sun offers. As far as the hardware, I guess I don't have a huge preference either way. But if you do go with HP -- DO NOT GET AN AutoRAID! Biggest performance mistake we've made, IMO. Super slow writes. Just my $.02... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- 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: Babich , Sergey 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: HP-UX 11/8.1.6.x/Index-Organized Tables
can't you create table parallel 5 nologging as select /*+ parallel(iot 5) */ * from iot Then Truncate your IOT Then Make sure the the IOT has nologging insert --+ append into IOT select --+ enable parallel here * from -Original Message- Sent: Friday, January 18, 2002 1:21 PM To: Multiple recipients of list ORACLE-L Sigh. I was afraid of that. Any other ideas? --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -Original Message- Sent: Friday, January 18, 2002 9:44 AM To: Vergara; Michael (TEM); Multiple recipients of list ORACLE-L export/drop/import. Reply Separator Author: "Vergara; Michael (TEM)" <[EMAIL PROTECTED]> Date: 1/18/2002 7:30 AM Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: Unexplained table growth
If I were you I'd try to analyze both tables (prod and test), posibly on a small sample, to check whether the average row length is identical. The 'comment' field, which can be close enough to 60%, may be NULL in your test database and full in most cases in prod. Something else one could think of is a PCTFREE difference. It may be higher on your prod database. There may also be differences in the per-tablespace minimum allocation values. S Faroult "Jesse, Rich" wrote: > > Hi all, > > 8.1.7.2.0 on HP/UX 11.0 -- A developer tested a table recreate in our test > DB. Here's the scenario: > > CREATE TABLE MY_BIG_TABLE ( > FISCAL_YEAR NUMBER (5), > PERIODCHAR (2), > ACCOUNTNO CHAR (12), > TRANSTYPE CHAR (2), > TRANSQTY FLOAT, > TRANSAMNT FLOAT, > COMMENT_TEXT CHAR (30), > TRANSDATE DATE) >TABLESPACE QT_APPS1 >PCTFREE 10 >PCTUSED 40 >INITRANS 2 >MAXTRANS 255 > STORAGE ( >INITIAL 209715200 >NEXT 10485760 >PCTINCREASE 0 >MINEXTENTS 1 >MAXEXTENTS 249 >FREELISTS 1 FREELIST GROUPS 1 ) >NOCACHE; > > The table previously had a total of 14 extents, giving it a size of 330MB. > The dev TRUNCATED the table (I don't know if "REUSE STORAGE" was used), and > added 4,054,632 (4M) rows. The table is still at 14 extents and 330MB. > > So, all's well in test, the dev did the same in production. The row count > was *slightly* higher, at 4.069,106, but the table size jumped 60% to 560MB. > The DB_BLOCK_SIZE on both DBs is 8K. Of course, this filled up the TBS and > caused havoc. > > I looked in DBA_AUDIT_TRAIL and DBA_TAB_MODIFICATIONS, but I can't find any > significant difference between what was done to the table in test and prod. > The table in production has NO deletes recorded in DBA_TAB_MODIFICATIONS, > just inserts. > > Can anyone think of a scenario as to why this table would grow in prod but > not test with relatively the same number of rows and the exact same table > layout??? The only thing I can think of is that a "REUSE STORAGE" was > issued on one TRUNCATE, but not another, but I still don't see how that > could account for the table growth. > > I'm going thru LogMiner now, but as our test DB is in archivelog mode, I can > only look in production (and it's taking forever!). > > TIA! > > -- 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: Trouble with imp on 8.1.6
imp user/pass@service file=file.dmp log=test.log fromuser=caler touser=newuser tables=(*) I have also done above with charset=us7ascii no difference Rick [EMAIL PROTECTED] .tenet.edu To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Re: Trouble with imp on 8.1.6 01/18/2002 11:10 AM Please respond to ORACLE-L What does your imp command look like? David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Rick_Cale@team health.com To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Trouble with imp on 8.1.6 om 01/18/2002 09:00 AM Please respond to ORACLE-L Hi All, I did a successfull export on 8.1.6 for user= I am trying to import on same server into another user. I get following message but no tables are created? I have full privs. Can someone help? Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and US7ASCII NCHAR character set import server uses US7ASCII character set (possible charset conversion) Import terminated successfully without warnings. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- 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: HP vs SUN for a UNIX box
Thank you, Paul, I'll forward it to my boss... Anyone else to add to it? Thank you very much, Sergey Babich -Original Message- Sent: Friday, January 18, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Subject:RE: HP vs SUN for a UNIX box Stay with HP. It's a hell of a lot more reliable, and does not require anywhere near the # of patches, and its patches work far more often that Sun's. I've done 5 years with each, and there's no question that HP is superior. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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: HP vs SUN for a UNIX box
Using both HP 11.0 and Solaris 2.8, I have a preference for HP. Just little toolset things like SAM for a GUI SysAdmin, "top" and "GlancePlus", although GlancePlus, a system monitor, I guess I would consider a huge advantage over what Sun offers. As far as the hardware, I guess I don't have a huge preference either way. But if you do go with HP -- DO NOT GET AN AutoRAID! Biggest performance mistake we've made, IMO. Super slow writes. Just my $.02... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- 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: SPACE FREE HOW?
Title: RE: SPACE FREE HOW? Nick, I have a question regarding LiveReorg's method that prevents modifications to the original table when it's time to switch to the newly reorged one. AS the mechanism to prevent DML, LiveReorg creates a temporary trigger on Insert, Update and Delete. It simply raises a user-defined exception. With our 24x7 Siebel environment executing massive insert and updates against the base tables, we expect that the application will receive this error when we switch to the new table. The problem is that the application is not coded to handle the exception and the user will get an error displayed on the client. This is preventing us from utilizing the Live feature of LiveReorg and is becoming as sore spot with our internal clients. They expected uninterrupted maintenance and frankly, so did we. Please correct me if I'm wrong or if I'm not using the tool correctly. Tony Aponte Home Shopping Network -Original Message-From: Nick Wagner [mailto:[EMAIL PROTECTED]]Sent: Friday, January 18, 2002 12:51 PMTo: Multiple recipients of list ORACLE-LSubject: RE: SPACE FREE HOW? 24x7... that does make it tough.. you might want to try LiveReorg from Quest Software... it allows you to reorganize those tables while users are still accessing them. It will even tell you which tables to reorg to free up the most space... without even deleting data. you can find out info about the products at www.quest.com/livereorg you can even get a free trial version to test it out, and make sure it is what you need. Nick -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 7:41 AM To: Multiple recipients of list ORACLE-L Subject: RE: SPACE FREE HOW? Ethan The database is in production and users are accesing 24x7.What will be best approach to do? DO u have any scripts how to find HWM? Thanks -Seema >From: "Post, Ethan" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: SPACE FREE HOW? >Date: Thu, 17 Jan 2002 14:25:37 -0800 > >Deleting records from a table does not free up space in the database. >Search for high water mark in the concepts manual and you should find an >explanation of how this works. The statement below only frees up space >above the high water mark on the table. > >You can... > >create table foo nologging as (select * from big_table); > >truncate table big_table; > >insert into big_table (select * from foo); > >drop table foo; > > >-Ethan > >-Original Message- >Sent: Thursday, January 17, 2002 3:27 PM >To: Multiple recipients of list ORACLE-L > > >ALTER TABLE table DEALLOCATE UNUSED KEEP integer; >the keep clause is optional. > >-sunil > >-Original Message- >Sent: Thursday, January 17, 2002 2:58 PM >To: Multiple recipients of list ORACLE-L > > >Hi >I deleted millions of rows from diffrent tables and I have not seen any >impact on database size.What I have to do to get that free space? >Is it necessary to shutdown the database? >Thx >-Seema > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Post, Ethan > 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: 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).
RE: Checkpoint & Redo ratio(High)
Seema: Regarding the "Checkpoint not completed." message, make sure you're checkpointing only at redo log switches. Check loc_checkpoint_timeout and log_checkpoint_interval values. If these values are OK and you still keep getting "Checkpoint not completed." messages in the alert.log you should enlarge your redo logs. HTH Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 4:25 PM > Hi > I checked my database and see redo ratio is high. > I ran the folling query > select (req.value * 5000) / entries.value "Redo Ratio" >from v$sysstat req, v$sysstat entries >where req.name = 'redo log space requests' >and entries.name = 'redo entries'; > But the > background checkpoints completed and background checkpoints started are > having diffrence 1. > When I do large insert and delete I receive message in alert log > "Checkpoint not completed." but not regulary. > My redo log file size is 20m. > oracle is running on 8.1.6 on unix. > LEt me know group suggestion. > Thanks > -Seema > > > > _ > 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Diego Cutrone 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: HP vs SUN for a UNIX box
Stay with HP. It's a hell of a lot more reliable, and does not require anywhere near the # of patches, and its patches work far more often that Sun's. I've done 5 years with each, and there's no question that HP is superior. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Our shop is switching from HP to Sun. No one can tell me why. There must be a cost savings somewhere. -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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). -- 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:Help
Are you talking about Support Magic from NAI?? Dick Goulet Reply Separator Author: "Shreeni" <[EMAIL PROTECTED]> Date: 1/18/2002 10:30 AM Hi list, Can any body tell me about magic for oracle? TIA, Shreeni Shreenivasa Rao.vcf Description: Binary data
max_io_size
Where can I find information on this parameter on the Tru64 UNIX platform? I did a scan of the manuals for Tru64 UNIX at docs.oracle.com, but didn't find mention of it anywhere. I want to set db_file_multiblock_read_count properly... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1
Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields Linda, I just did something like this yesterday. You will need to use the TRANSLATE function. So you can use an SQL statement like: update set fld1 = translate(fld1,chr(09),’-‘); Make sure you only have one weird character in the field though. I actually and a carriage return and a new line back to back and, of course, you can’t see them. You might want to use the DUMP function to look at what is really in the field. Good luck! Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Subject: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1 Hi, Sometime in the past, data was loaded into tables from spreadsheets and the tabs and form feeds were included in the data. I can locate all the bad data, and am looking for a clear method to remove only the 'bad' character from a field, despite where it occurs. For example, you can see 09 at the end of the rawtohex column. I need to change Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second example are in a numeric field. If anyone has had to do this, I'd appreciate knowing your method. Thanks, Linda Table Column Contents Rawtohex - - REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta 43616E6164612D416C62657274612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba 43616E6164612D4D616E69746F62612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia 43616E6164612D4E6F76612053636F7469612009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario 43616E6164612D4F6E746172696F2009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec 43616E6164612D5175656265632009 REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan 43616E6164612D5361736B617463686577616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009 REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009 REG.AMA_COUNTRIES COUNTRY_NAME Angola 416E676F6C612009 REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009 Table Column Contents Rawtohex --- --- -- REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84708 38343730380D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84709 38343730390D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
Re: Codde's Rules and Oracle
No. In fact, I don't believe that there is a database in existence that does conform to all 12 rules. I'll let someone else answer the hard part, just not enough time for that. Jared "Vikas S" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/18/02 02:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Codde's Rules and Oracle Dear All, Does Oracle confirm to all 12 Codde's Rules. Can anyone point out the Rules violated by each version (6/7/8/9) of Oracle. Thanks, Vikas _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas S 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: SQL question
Try this: INSERT INTO contacts (cont_id,cont_contact_surname) (SELECT seq_cont.nextval, delegated_person from (select distinct delegated_person FROM sierras WHERE delegated_person is not null)); -Original Message- Sent: Friday, January 18, 2002 12:51 PM To: Multiple recipients of list ORACLE-L Hi i am trying to insert records into a table by selectiog from table 2, i want to insert a sequence,, and the distinct values from table 1 but i get an error: Query used: INSERT INTO contacts (cont_id,cont_contact_surname) (SELECT seq_cont.nextval,distinct delegated_person FROM sierras WHERE delegated_person is not null) Error; select seq_cont.nextval,distinct delegated_person * ORA-00936: missing expression How can i modify the query to obtain the correct results cheers all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov 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: SPACE FREE HOW?
If you have good stats on the table just take the following and compare. actual size of object vs. (#rows * average row size ) If actual size is much larger than #rows * average row size, you have a lot of unused space in the table, this however does not tell you the high water mark. There are some various methods to determine this but I can't recall offhand, DBMS_SPACE package I think. I have never really been interested in my HWM, just if I have a lot of empty space which the above would show you. More importantly is know that and how well space at the block level is being used (%FREE $USED). I think the suggestion about rebuilding the table online by moving it to another tablespace and then back again will be your best bet if you are 24/7 shop. Better check the docs on this but I am pretty sure you can do while the db is up, just pick a time with low activity. Sorry I can be more helpful, really in the weeds here today. Thanks, Ethan -Original Message- Sent: Friday, January 18, 2002 9:41 AM To: Multiple recipients of list ORACLE-L Ethan The database is in production and users are accesing 24x7.What will be best approach to do? DO u have any scripts how to find HWM? Thanks -Seema >From: "Post, Ethan" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: SPACE FREE HOW? >Date: Thu, 17 Jan 2002 14:25:37 -0800 > >Deleting records from a table does not free up space in the database. >Search for high water mark in the concepts manual and you should find an >explanation of how this works. The statement below only frees up space >above the high water mark on the table. > >You can... > >create table foo nologging as (select * from big_table); > >truncate table big_table; > >insert into big_table (select * from foo); > >drop table foo; > > >-Ethan > >-Original Message- >Sent: Thursday, January 17, 2002 3:27 PM >To: Multiple recipients of list ORACLE-L > > >ALTER TABLE table DEALLOCATE UNUSED KEEP integer; >the keep clause is optional. > >-sunil > >-Original Message- >Sent: Thursday, January 17, 2002 2:58 PM >To: Multiple recipients of list ORACLE-L > > >Hi >I deleted millions of rows from diffrent tables and I have not seen any >impact on database size.What I have to do to get that free space? >Is it necessary to shutdown the database? >Thx >-Seema > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Post, Ethan > 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: SQL question
Try this distinct(delegated_person) Chuck Speaks Database Administrator Lithonia Lighting 770-860-3450 http://www.lithonia.com -Original Message- Sent: Friday, January 18, 2002 12:51 To: Multiple recipients of list ORACLE-L Hi i am trying to insert records into a table by selectiog from table 2, i want to insert a sequence,, and the distinct values from table 1 but i get an error: Query used: INSERT INTO contacts (cont_id,cont_contact_surname) (SELECT seq_cont.nextval,distinct delegated_person FROM sierras WHERE delegated_person is not null) Error; select seq_cont.nextval,distinct delegated_person * ORA-00936: missing expression How can i modify the query to obtain the correct results cheers all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Speaks, Chuck W. 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).
_system_trig_enabled=false - URGENT
So I was just checking out the ixora script to look at values for hidden parameters. And I discovered that _system_trig_enabled is FALSE on all my databases! If this is the default then why is it so important to specifically set it to false during 8.1.7 upgrades? This value was FALSE not only for databases I'd upgraded to 8.1.7 and therefore set specifically to false in the init.ora at some point but also in my old 8.1.6 databases and some initial installation 8.1.7 databases that were never upgraded. I'm very nervous just now. SQL> set linesize 128 column name format a42 select x.ksppinm name, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') sesmod, decode( bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE' ) sysmod, ksppdesc description from sys.x_$ksppi x where x.inst_id = userenv('Instance') and translate(ksppinm,'_','#') like '#%' and x.ksppinm like '%trig%' order by 1;SQL> SQL> 23456789 10 NAME SESMO SYSMODDESCRIPTION -- - - - --- _system_trig_enabled FALSE FALSE system triggers are e nabled Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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).
LogMiner and ORA-03113
I'm trying to analyze some archive logs via LogMiner and I keep getting the error ORA-03113 (end of file on communications channel) when I query the V$LOGMNR_CONTENTS view. I did a "new" and "start" prior to running the query. This process that I am going through works on some log files but not on others. The log files aren't corrupted because I can restore/recover the database and apply the logs successfully for a roll-forward. I'm just running a 'select count(*)' query at this point. Has anyone else encountered this and come up with a solution? I've checked MetaLink, Google, etc and so far have not come up with anything that describes what may be the cause. I'm running 8.1.7.2 (64bit) on Solaris 8. MANY thanks in advance. -w __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K 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: Unexplained table growth
Hi, Jesse, Didn't your dev use parallel insert on production to speedup things? This may cause different segment allocation HTH Vadim -Original Message- Sent: Friday, January 18, 2002 12:41 PM To: Multiple recipients of list ORACLE-L Hi all, 8.1.7.2.0 on HP/UX 11.0 -- A developer tested a table recreate in our test DB. Here's the scenario: CREATE TABLE MY_BIG_TABLE ( FISCAL_YEAR NUMBER (5), PERIODCHAR (2), ACCOUNTNO CHAR (12), TRANSTYPE CHAR (2), TRANSQTY FLOAT, TRANSAMNT FLOAT, COMMENT_TEXT CHAR (30), TRANSDATE DATE) TABLESPACE QT_APPS1 PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 209715200 NEXT 10485760 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249 FREELISTS 1 FREELIST GROUPS 1 ) NOCACHE; The table previously had a total of 14 extents, giving it a size of 330MB. The dev TRUNCATED the table (I don't know if "REUSE STORAGE" was used), and added 4,054,632 (4M) rows. The table is still at 14 extents and 330MB. So, all's well in test, the dev did the same in production. The row count was *slightly* higher, at 4.069,106, but the table size jumped 60% to 560MB. The DB_BLOCK_SIZE on both DBs is 8K. Of course, this filled up the TBS and caused havoc. I looked in DBA_AUDIT_TRAIL and DBA_TAB_MODIFICATIONS, but I can't find any significant difference between what was done to the table in test and prod. The table in production has NO deletes recorded in DBA_TAB_MODIFICATIONS, just inserts. Can anyone think of a scenario as to why this table would grow in prod but not test with relatively the same number of rows and the exact same table layout??? The only thing I can think of is that a "REUSE STORAGE" was issued on one TRUNCATE, but not another, but I still don't see how that could account for the table growth. I'm going thru LogMiner now, but as our test DB is in archivelog mode, I can only look in production (and it's taking forever!). 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: Vadim Gorbounov 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: multiple extents are OK, dagnabbit!
Paul, With LMT's. uniform extents sizes and properly place objects I think you avoid most of the situations you described. Cary's paper at hotsos.com shows that in a system with a lot of activity your disk head is never going to fulfill the request for a full tablescan in a single operation anyway because of all of the competing requests. Also it shows that the probability of in another request in fact being beneficial to your processes disk read goes up. 9 out of 10 DBA's agree, LMT's, uniform extents sizes and objects with < 1000 extents are just fine for most databases. These are always general rules and everyone's situation is different. Perhaps you want to keep everything under 100 extents for a DSS DB with few users, I would say that would be fine but to still stay with uniform LMT's as they are easier to manage. - Ethan -Original Message- Sent: Friday, January 18, 2002 10:41 AM To: Multiple recipients of list ORACLE-L Hello, My 2 cents: It does make a difference to reorg, esp. when done thoughtfully, with a specific goal in mind. For example, if you have a order_log table that started with first extent 1MB and next extent 1MB, and this table has grown in size to say, 10 million rows (business is good), you would have hundreds of extents, and each of those new extents took some time to extend that would have been avoided if you had started with 100MB first and 25MB next. Indexes take a far worse performance hit. You also expose yourself to other issues (fragmentation, full table scans (yuck) run slower, table drops run slower, more extent overhead, recovery time runs slower, risk of failure increases). Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: smtp via PL/SQL
Steve, I just happen to have an example script. :) Here's a wrapper and a test. First, the wrapper: --- create or replace PROCEDURE sendmail ( sender IN VARCHAR2, recipient IN VARCHAR2, subject IN VARCHAR2, message IN VARCHAR2) IS v_message varchar2(4000); mailhostVARCHAR2(30) := 'mail.radisys.com'; mail_conn utl_smtp.connection; lf VARCHAR2( 1 ):= CHR( 10 ); BEGIN v_message := 'Subject: ' || subject || lf || message; mail_conn := utl_smtp.open_connection(mailhost, 25); utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, sender); utl_smtp.rcpt(mail_conn, recipient); utl_smtp.data(mail_conn, v_message); utl_smtp.quit(mail_conn); --EXCEPTION --WHEN OTHERS THEN -- Handle the error --raise; END; / show errors procedure sendmail create public synonym sendmail for sys.sendmail; grant execute on sendmail to public; - And here is the test: -- declare v_database global_name.global_name%type; begin select global_name into v_database from global_name; sendmail( sender => '[EMAIL PROTECTED]', -- multiple addresses not allowed from mail server -- relaying disabled --recipient => '[EMAIL PROTECTED],[EMAIL PROTECTED]', recipient => '[EMAIL PROTECTED]', subject => 'test from utl_smtp at radisys', message => 'this is a test from utl_smtp@'|| v_database); end; / Please feel free to change the recipient addresses. :) If you need to install utl_tcp and utl_smtp: $ORACLE_HOME/rdbms/admin/utltcp.sql $ORACLE_HOME/rdbms/admin/prvttcp.plb $ORACLE_HOME/rdbms/admin/utlsmtp.sql $ORACLE_HOME/rdbms/admin/prvtsmtp.plb Jared "Steve McClure" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/17/02 04:35 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:smtp via PL/SQL I am digging into the docs I can find on utl_smtp and utl_tcp, but I am really not finding much. I have Oracle's package reference docs, but that doesn't shed all that much light on the subject. I am pretty well a newbie to tcp and smtp. Geeze all that talking and no question yet. Can anyone recommend a book or white paper on implementing 'email' from within an Oracle database? I have downloaded some sample code from Orafaq, and actually gotten it working on our db. I would just like to actually understand what I am doing, and expand on what we have. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure 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: HP-UX 11/8.1.6.x/Index-Organized Tables
Hi, Mike, ALTER TABLE ... MOVE TABLESPACE ...; rebuilds IOT's PK. HTH Vadim -Original Message- Sent: Friday, January 18, 2002 10:31 AM To: Multiple recipients of list ORACLE-L Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? Thanx, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: Vadim Gorbounov 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: ARCO trace file
Trace files related to arc are normal upon startup and shutdown. - E -Original Message- Sent: Friday, January 18, 2002 10:11 AM To: Multiple recipients of list ORACLE-L Every night after I shutdown immediate and restart the instance I get this same trace file in bdump, it just keeps appending. It is associated with my Archive log process. The two entries are for the nights of the 15th and 16th. Note that it happens right at 22:18 when my instance is restarting and the ARCH is coming back up. Is this normal and can anybody explain this to me? *** SESSION ID:(11.1) 2002-01-15 22:18:10.756 *** 2002-01-15 22:18:10.756 *** 2002-01-16 22:15:10.865 Dump file D:\Oracle\admin\ispestar\bdump\ispestarARC0.TRC Wed Jan 16 22:18:04 2002 ORACLE V8.1.6.0.0 - Production vsnsta=0 vsnsql=e vsnxtr=3 Windows NT Version 4.0 Service Pack 6, CPU type 586 Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Windows NT Version 4.0 Service Pack 6, CPU type 586 Instance name: ispestar Redo thread mounted by this instance: 0 Oracle process number: 12 Windows thread id: 351, image: ORACLE.EXE *** SESSION ID:(11.1) 2002-01-16 22:18:03.990 *** 2002-01-16 22:18:03.990 *** 2002-01-17 22:15:16.303 Dump file D:\Oracle\admin\ispestar\bdump\ispestarARC0.TRC Thu Jan 17 22:18:10 2002 ORACLE V8.1.6.0.0 - Production vsnsta=0 vsnsql=e vsnxtr=3 Windows NT Version 4.0 Service Pack 6, CPU type 586 Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Windows NT Version 4.0 Service Pack 6, CPU type 586 Instance name: ispestar Redo thread mounted by this instance: 0 Oracle process number: 12 Windows thread id: 471, image: ORACLE.EXE David Ehresmann Oracle DBA 8 & 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann 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: Post, Ethan 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).
SQL question
Hi i am trying to insert records into a table by selectiog from table 2, i want to insert a sequence,, and the distinct values from table 1 but i get an error: Query used: INSERT INTO contacts (cont_id,cont_contact_surname) (SELECT seq_cont.nextval,distinct delegated_person FROM sierras WHERE delegated_person is not null) Error; select seq_cont.nextval,distinct delegated_person * ORA-00936: missing expression How can i modify the query to obtain the correct results cheers all. -- 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: When should a table be partitioned?
Where the business use of the data is such that the applications' selects can be satisfied by querying a known number of partitions (less than all partitions). Also where data can be archived partition-wise. Generally this means time sensitive data. I don't see much benefit to partitioning data when the data for a single select will span all partitions. Of course, I could be wrong there. Cherie_Machle rTo: Multiple recipients of list ORACLE-L @gelco.com <[EMAIL PROTECTED]> Sent by: rootcc: Subject: When should a table be partitioned? 01/18/2002 08:20 AM Please respond to ORACLE-L We have a number of partitioned tables in a couple of existing data warehouses. We are working on the design for a new warehouse and need to decide which tables should be partitioned. For you folks that have partitioned tables, how do you decide which tables to be partition? Some tables with very large row counts are obvious candidates. If you go off of row counts solely, what is the cut-off point for where you should start partitioning? Is there a rule-of-thumb? I'm having difficulty with the not-as-huge, not-as-obvious candidates. What other criteria do you use besides row-count? Perhaps archival requirements? I guess it would depend on what you are using the partitioning to achieve. Partition exclusion for read performance improvement or for culling off old data, etc. Any shared insights for where to draw the line on partitioning candidates would be greatly appreciated. Thanks, Cherie Machler -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- 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: used segment space in bytes
Deepak: As a matter of fact I think you can find how many blocks below HWM are unused (have no rows at all), by counting the number of blocks that are below the HWM and hold at least one row. (check my previous mail) SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM TABLE; This query will count how many blocks have data (and of course) are below HWM. I know this is not a perfect calclulation, because it can report 10 blocks used and these blocks could only have 1 row each, but it's better than the HWM value or than the dbms_space.free_blocks, I think. Please correct me if I'm wrong... Greetings DC - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 9:05 PM > Hi Do, > > Here is the breakup for a space usage for a segment: > > 1. Allocated size (use dba_segments) > > 2 Used Blocks in segments(use dba_tables.blocks) > --> Truly Used ( ??) > --> Free Blocks (??) > > 3. Unused Blocks (use dba_tables.empty_blocks) > > the caveat i guess is in step 2. The used block number > is based on the high watermark. which means that the > number you will get for point 2. above will also > include "space that contains no rows" becuz its below > the high water mark. > > Afaik, there is'nt a way to find these "free blocks" > below the high water mark.. unless you re-build table > and then get the value from dba_tables.blocks .. > > feel free to correct me .. > > Deepak > > > -Original Message- > > Sent: Thursday, January 17, 2002 12:12 PM > > To: Multiple recipients of list ORACLE-L > > > > > > hi guys! > > > > what would be the easiest way to compute the space > > in bytes used by a segment? > > it is rather easy to determine the # of used blocks > > by a specific segment by looking at the dba_extents > > for example. but how many blocks have been really > > used > > in one of those extents. i guess i would be able > > to compute the free blocks by using the dbms_space > > package but it just seems to complicated. > > any hints ... ? > > > > thank you > > > > -do > > > > > > > > > __ > Do You Yahoo!? > Send FREE video emails in Yahoo! Mail! > http://promo.yahoo.com/videomail/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deepak Thapliyal > 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: Diego Cutrone 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: HP-UX 11/8.1.6.x/Index-Organized Tables
Sigh. I was afraid of that. Any other ideas? --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -Original Message- Sent: Friday, January 18, 2002 9:44 AM To: Vergara; Michael (TEM); Multiple recipients of list ORACLE-L export/drop/import. Reply Separator Author: "Vergara; Michael (TEM)" <[EMAIL PROTECTED]> Date: 1/18/2002 7:30 AM Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: SPACE FREE HOW?
Title: RE: SPACE FREE HOW? 24x7... that does make it tough.. you might want to try LiveReorg from Quest Software... it allows you to reorganize those tables while users are still accessing them. It will even tell you which tables to reorg to free up the most space... without even deleting data. you can find out info about the products at www.quest.com/livereorg you can even get a free trial version to test it out, and make sure it is what you need. Nick -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 7:41 AM To: Multiple recipients of list ORACLE-L Subject: RE: SPACE FREE HOW? Ethan The database is in production and users are accesing 24x7.What will be best approach to do? DO u have any scripts how to find HWM? Thanks -Seema >From: "Post, Ethan" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: SPACE FREE HOW? >Date: Thu, 17 Jan 2002 14:25:37 -0800 > >Deleting records from a table does not free up space in the database. >Search for high water mark in the concepts manual and you should find an >explanation of how this works. The statement below only frees up space >above the high water mark on the table. > >You can... > >create table foo nologging as (select * from big_table); > >truncate table big_table; > >insert into big_table (select * from foo); > >drop table foo; > > >-Ethan > >-Original Message- >Sent: Thursday, January 17, 2002 3:27 PM >To: Multiple recipients of list ORACLE-L > > >ALTER TABLE table DEALLOCATE UNUSED KEEP integer; >the keep clause is optional. > >-sunil > >-Original Message- >Sent: Thursday, January 17, 2002 2:58 PM >To: Multiple recipients of list ORACLE-L > > >Hi >I deleted millions of rows from diffrent tables and I have not seen any >impact on database size.What I have to do to get that free space? >Is it necessary to shutdown the database? >Thx >-Seema > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Post, Ethan > 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: 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).
RE: Trouble with imp on 8.1.6
Rick, By default Oracle will import from/to the same user only. You can do what you want by using the fromuser/touser option. Something like imp user/pwd file=x.dmp fromuser=from touser=to Type imp help=y for the specifics. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 18, 2002 10:01 AM To: Multiple recipients of list ORACLE-L Hi All, I did a successfull export on 8.1.6 for user= I am trying to import on same server into another user. I get following message but no tables are created? I have full privs. Can someone help? Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and US7ASCII NCHAR character set import server uses US7ASCII character set (possible charset conversion) Import terminated successfully without warnings. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Unexplained table growth
Hi all, 8.1.7.2.0 on HP/UX 11.0 -- A developer tested a table recreate in our test DB. Here's the scenario: CREATE TABLE MY_BIG_TABLE ( FISCAL_YEAR NUMBER (5), PERIODCHAR (2), ACCOUNTNO CHAR (12), TRANSTYPE CHAR (2), TRANSQTY FLOAT, TRANSAMNT FLOAT, COMMENT_TEXT CHAR (30), TRANSDATE DATE) TABLESPACE QT_APPS1 PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 209715200 NEXT 10485760 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249 FREELISTS 1 FREELIST GROUPS 1 ) NOCACHE; The table previously had a total of 14 extents, giving it a size of 330MB. The dev TRUNCATED the table (I don't know if "REUSE STORAGE" was used), and added 4,054,632 (4M) rows. The table is still at 14 extents and 330MB. So, all's well in test, the dev did the same in production. The row count was *slightly* higher, at 4.069,106, but the table size jumped 60% to 560MB. The DB_BLOCK_SIZE on both DBs is 8K. Of course, this filled up the TBS and caused havoc. I looked in DBA_AUDIT_TRAIL and DBA_TAB_MODIFICATIONS, but I can't find any significant difference between what was done to the table in test and prod. The table in production has NO deletes recorded in DBA_TAB_MODIFICATIONS, just inserts. Can anyone think of a scenario as to why this table would grow in prod but not test with relatively the same number of rows and the exact same table layout??? The only thing I can think of is that a "REUSE STORAGE" was issued on one TRUNCATE, but not another, but I still don't see how that could account for the table growth. I'm going thru LogMiner now, but as our test DB is in archivelog mode, I can only look in production (and it's taking forever!). 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).
HP vs SUN for a UNIX box
Hi, dear listers, My boss has decided to switch production to a UNIX box and now he's asking me for a recommendation between HP and SUN. I am a newbee to UNIX (to put it the softest way). I've heard people say HP is better, but need more than that... The DB itself is about 40G on RAID (currently on Windoze 2k). Any advice is greatly appreciated. Regards, Sergey Babich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey 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: multiple extents are OK, dagnabbit!
Hello, My 2 cents: It does make a difference to reorg, esp. when done thoughtfully, with a specific goal in mind. For example, if you have a order_log table that started with first extent 1MB and next extent 1MB, and this table has grown in size to say, 10 million rows (business is good), you would have hundreds of extents, and each of those new extents took some time to extend that would have been avoided if you had started with 100MB first and 25MB next. Indexes take a far worse performance hit. You also expose yourself to other issues (fragmentation, full table scans (yuck) run slower, table drops run slower, more extent overhead, recovery time runs slower, risk of failure increases). Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 8:41 AM To: Multiple recipients of list ORACLE-L Jerry, Tell the client that you will be HAPPY to reorg the tables and indexes over 10 extents. It will cost X dollars and take Y hours of downtime/slowdown. Insert inappropriately huge numbers into X and Y. It's amazing how quickly people will change their minds when you talk hours and dollars. Some people don't see the light until they are on fire. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 > -Original Message- > From: Cunningham, Gerald [SMTP:[EMAIL PROTECTED]] > > Hi there - > > I'm trying to convince a client that multiple extents for a table will > not hurt their performance. It's a PeopleSoft app, and PeopleSoft is > telling them that they need to reorg any object with greater than 10 > extents (even indexes). This Oracle 8.1.6. > > I've referenced the "How to Stop Defragmenting and Start Living: The > Definitive Word on Fragmentation" white paper by Bhaskar Himatsingka > and Juan Loaiza of Oracle. That didn't convince them. I tried to > explain that Oracle reads BUFFERS and not extents, etc., but that > didn't work. > > I'm about to open a vein. > > Does anybody have any references that they can point me to? (Something > from PeopleSoft would be ideal, though I would be suprised if it > existed.) I read a rant on somebody's web site a while back that was > really good, but alas I cannot remember his name or URL. (I blame my > kids for my failing memory). > > > Thanks! > > - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI 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: Sorta OT: Metalink Humor
Thanks for sharing the laugh! Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 18, 2002 12:10 PM > Listers, > Does anyone else see the humor in this? From the Metalink web site, the top > two New & Notes topics are this: > > How to Ensure Uninterrupted MetaLink Access. 17-JAN-2002 N > Temporary Interruption in iTAR functionality: US, Canada and Latin America > Only! 14-JAN-2002 N > > And topic "How to ensure uninterrupted MetaLink access?" has the first line > of: > "The easiest way to ensure uninterrupted access to MetaLink is to use > MetaLink." REALLY?? Access is that easy? When it's down, USE it? Hmm > > Hey, can I try it at 2am this Sunday? No? Oh that's right, the iTar piece > is down. But if I use it, it's there, right? Something is missing in logic > here... > > "Thank you for using MetaLink! The MetaLink Team" > > Now that I've had my giggle for the day, it's back to work. > Margaret > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Murray, Margaret > 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: smtp via PL/SQL
Hey, Thanks all. Who would'a figured that Metalink could actually be usefull for researching this topic. When I sent the initial email I was actually thinking I had already searched for help on Metalink. Anyway It is certain that I hadn't, because there is actually a lot of helpfull info there. Thanks again, Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure 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: Trouble with imp on 8.1.6
HI Rick, Could you share with us the export statement and the import statement you used. Tha will help figure out what went wrong. John [EMAIL PROTECTED] wrote: >Hi All, > >I did a successfull export on 8.1.6 for user= >I am trying to import on same server into another user. I get following >message but no tables are created? >I have full privs. > >Can someone help? > >Connected to: Oracle8i Release 8.1.6.0.0 - Production >JServer Release 8.1.6.0.0 - Production > >Export file created by EXPORT:V08.01.06 via conventional path >import done in WE8ISO8859P1 character set and US7ASCII NCHAR character set >import server uses US7ASCII character set (possible charset conversion) >Import terminated successfully without warnings. > >Thanks >Rick > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba 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: RMAN - restoring archive logs
Walter, You have restore the production database backup on the test server and opened the database with resetlogs ...right. This has created a new incarnation of the database. RMAN does not recoginize the new incarnation of your test database since yu have not done a "reset database". HTH - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 18, 2002 7:30 AM > Hi, > > I want to analyze some archive log files with > LogMiner. The logs are stored in an RMAN backup on > tape. I am trying to restore the files to a > development box but when I perform the restore I get > the error message "RMAN-20003: target database > incarnation not found in recovery catalog". The syntax > I am using is: > > run { > allocate channel t1 type 'sbt_tape'; > restore archivelog from logseq 200 until logseq 233; > } > > Can the archive logs only be restored back to the > original database? The target database I am using is a > restored backup of the same production database but on > a development box. > > Any suggestions would be appreciated. Thanks. > -w > > __ > Do You Yahoo!? > Send FREE video emails in Yahoo! Mail! > http://promo.yahoo.com/videomail/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Walter K > 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: Sona 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).
Undo Tablespace in 9i
I am converting to 9i and am thinking about using the undo tablespace rather than rollback segments. Does anyone know if there are any performance implications in using the undo tablespace? I tried a big import that ran 21 minutes using rollback segments and 23 minutes using the undo tablespace, but that is only one test. Any thoughts? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALINF 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: Standby Instance questions and HA
I've seen a similar idea running on Silicon Graphics kit : 2 servers; heartbeat check between them; Drives mount on the other box when the 'live' system fails It was nice when it worked...Testing the failover caused barely a ripple, although it did disconnect any open sessions. Alas, it rarely worked that well when there was an actual problem. As with the Sun solution, no extra charge from Oracle, but added costs from SG. Still, who in their right mind would choose to run a production database on discontinued SGI machines? Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:(bcc: Simon Anderson/SSplc) We have this in place on Sun as well. It is similar to a cluster in that it has a separate box (Ultra 2) monitoring a heartbeat between both database servers. You will have a significant impact during failover. All drives common to both boxes will be unmounted on the primary and remounted on the secondary (which then becomes the primary). Since a "fsck" is run for each file system it can take upwards of 30 minutes for the failover. The database is shut down and brought back up so all connections are severed which means an interruption of service. If I wanted H/A on Sun I would move to 9i and implement a RAC. No application changes are necessary with this new version of OPS. Good luck. --Michael -Original Message- Sent: Thursday, January 17, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Jim: Sorry, you're not gonna like this answer. HA is a Sun product, not an Oracle product. Under Sun's High Availability, you can configure several modules like Sybase and Oracle. (The Oracle product is Sun Cluster HA-DBMS for Oracle.) It does require what I believe Sun calls a cluster but (IMHO) is a bastardization of the term. It truly is failover, not cluster. We've had lots of problems with it. It's caused us lots of grief, and only in a few instances gained us anything. It is NOT OPS, as the database does not run in parallel, but only on 1 box at a time. (Everything is double cabled, and so the drives are re-mounted on the 2nd box if a failover occurs.) Your users still get disconnected. You'd probably lose less data than with a standby (since you pick up with the same drives mounted on the other box), but it depends on how you have the standby implemented. There's no additional cost from Oracle to run this crap, but you'll be paying Sun great sums of money. The Sun web site has more info on HA. Let me know if you need more info. Good luck! Barb -- 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: SPACE FREE HOW?
The dba_space package has routines that will tell you wherer the hwm mark. With that infomration and 1.75 you can get a cup of coffee :-). Knowing "where" the HWM mark is, is not helpful. The only way to move it, is to truncate the table. I know this is difficult in a 24X7 system but frankly is unavoidable. BTW, you have a perfect situation for a partitioned table based on months. Then instead of deleting date you can drop the partition. John [EMAIL PROTECTED] wrote: > Ethan > The database is in production and users are accesing 24x7.What will be > best approach to do? > DO u have any scripts how to find HWM? > Thanks > -Seema > > >> From: "Post, Ethan" <[EMAIL PROTECTED]> >> Reply-To: [EMAIL PROTECTED] >> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >> Subject: RE: SPACE FREE HOW? >> Date: Thu, 17 Jan 2002 14:25:37 -0800 >> >> Deleting records from a table does not free up space in the database. >> Search for high water mark in the concepts manual and you should find an >> explanation of how this works. The statement below only frees up space >> above the high water mark on the table. >> >> You can... >> >> create table foo nologging as (select * from big_table); >> >> truncate table big_table; >> >> insert into big_table (select * from foo); >> >> drop table foo; >> >> >> -Ethan >> >> -Original Message- >> Sent: Thursday, January 17, 2002 3:27 PM >> To: Multiple recipients of list ORACLE-L >> >> >> ALTER TABLE table DEALLOCATE UNUSED KEEP integer; >> the keep clause is optional. >> >> -sunil >> >> -Original Message- >> Sent: Thursday, January 17, 2002 2:58 PM >> To: Multiple recipients of list ORACLE-L >> >> >> Hi >> I deleted millions of rows from diffrent tables and I have not seen any >> impact on database size.What I have to do to get that free space? >> Is it necessary to shutdown the database? >> Thx >> -Seema >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- >> Author: Post, Ethan >> 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: orantdba 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: Trouble with imp on 8.1.6
What does your imp command look like? David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Rick_Cale@team health.com To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Trouble with imp on 8.1.6 om 01/18/2002 09:00 AM Please respond to ORACLE-L Hi All, I did a successfull export on 8.1.6 for user= I am trying to import on same server into another user. I get following message but no tables are created? I have full privs. Can someone help? Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and US7ASCII NCHAR character set import server uses US7ASCII character set (possible charset conversion) Import terminated successfully without warnings. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- 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: Standby Instance questions and HA
Nah, I make sure all servers have the same uid/gid for oracle but I have naming standards for the lv's and filesystems. This allows me to failover multiple primary servers to a single secondary. I was just curious, b/c I have seen other sites that use your method. I prefer not having to synch multiple OH's. I can just see it happen 6 mos from now, failover occurs, but someone forgot the synch, o o :). Thanks. Gene >>> [EMAIL PROTECTED] 01/18/02 09:50AM >>> I have way to many f'ing Oracle Homes to deal with. When I first got here they were all different versions as well. So it was more of a maintenance thing. To tell you the truth someone else originally set it up that way and I liked it so I kept it. It does mean I have to keep more in sync manually then I normally would have to. If I only had one database on the server (or if they were all developed in-house) I probably would have installed the software on failover disks as well. Do you fail over the Unix account as well? -Original Message- Sent: Friday, January 18, 2002 4:50 AM To: Multiple recipients of list ORACLE-L When I failover, I bring the Oracle Home as well. Do you have special reasons for not bringing the Oracle Home over? *just curious* Gene >>> [EMAIL PROTECTED] 01/17/02 08:45PM >>> You will always have the same issues with fail over technology. Your users will get disconnected. My databases take less then 5 minutes to fail over and that is an acceptable time frame to the client. Its great from my standpoint for maintenance cause I can do it on one node, fail the databases over, and bring the other node up to date. I do not have the Oracle software itself in fail over, just the database. We do not find it to hard to work with here. I have no experience with Sun's so I cannot compare them. Whether or not you go with fail over technology all depends on what you are looking for.You will not lose any committed data with HP's (probably not with anyone else's either). Fail over is automatic when configured correctly. I have seen it happen once that I did not even know, it was that quick. Went to go look for my database on the server and it was not there:-) -Original Message- McCann Sent: Thursday, January 17, 2002 10:05 AM To: Multiple recipients of list ORACLE-L Thanks for your help everyone. Very useful advice, although your scaring me of Sun Clusters. At the minute, Parallel server looks the best, with a standby database remotely for disaster. Does anyone know what the HP solution is like (MC Service Guard)? I think some one on this list gave it a good review in the past . Thanks, Jim -Original Message- Sent: 17 January 2002 17:12 To: Multiple recipients of list ORACLE-L IBM HACMP works well. Ooops. guess that means you'll have to change some things. ;-) Seriously, we *did* get the Sun "clustering" working, but it required some serious feet-to-fire holding and gyrations. -Original Message- Sent: Thursday, January 17, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Thanks for the advice everyone. So what do you recommend on a Sun cluster/machines for failover other than OPS? Quest Shareplex? Standby database? Any others? Thanks, Jim -Original Message- Sent: 17 January 2002 16:22 To: Multiple recipients of list ORACLE-L I concur with BB.yea, I ran Sun "cluster" at and it broke ALOT. Kept me and two full time Sun Engineers (they got paid ALOT more) in consulting dollars, but i made a mental note not to use it in "my business". Caveat: this was 1.5 years ago. Things change. Mit Gluck, mein freund... - Ross "mit schuss" Mohan -Original Message- Jim: Sorry, you're not gonna like this answer. HA is a Sun product, not an Oracle product. Under Sun's High Availability, you can configure several modules like Sybase and Oracle. (The Oracle product is Sun Cluster HA-DBMS for Oracle.) It does require what I believe Sun calls a cluster but (IMHO) is a bastardization of the term. It truly is failover, not cluster. We've had lots of problems with it. It's caused us lots of grief, and only in a few instances gained us anything. It is NOT OPS, as the database does not run in parallel, but only on 1 box at a time. (Everything is double cabled, and so the drives are re-mounted on the 2nd box if a failover occurs.) Your users still get disconnected. You'd probably lose less data than with a standby (since you pick up with the same drives mounted on the other box), but it depends on how you have the standby implemented. There's no additional cost from Oracle to run this crap, but you'll be paying Sun great sums of money. The Sun web site has more info on HA. Let me know if you need more info. Good luck! Barb > -- > From: James McCann[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Thursday, January 17, 2002 5:40 AM > To: Multiple recipients of list ORACLE-L > Subject:
RE: Disaster recovery using RMAN
Yechiel - Thank you for your concern. Yes, in fact we back the non-database files on the system up every night, so the RMAN file will actually get written to several tapes. I just didn't mention it in detail. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 2:15 AM To: Multiple recipients of list ORACLE-L Hello Dennis I think that you need to backup the disk files to tape A.S.A.P. What will you do if your disk has gone to the great disk scrap yard in the sky? One more point: Is the disk on the same machine or in the same room? What will you do if a fire will destroy your server farm? To get a good backup you need to replicate the disks to a remote site or backup them to tapes and put the tapes in remote site. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] > Sent: Fri, January 18, 2002 4:05 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Disaster recovery using RMAN > > Sona - > Please keep in mind that we are RMAN novices at this stage. Of all > the DBA duties, I feel that the ability to recover the data is the most > important. > I picked our smallest database to start getting some operational > experience with. We still do weekly cold backups, writing the results to > tape because I haven't worked with RMAN long enough to have full > confidence > in it. The database is archivelog, of course. > Currently I do a weekly RMAN level 0 backup to disk. We don't have a > Media Manager like Legato or Veritas yet, and we were warned that RMAN > gets > much more complex when these are used, so I thought disk backups sounded > like a good way to begin. > During the week we do RMAN incremental backups each night. This > takes up very little disk space. My feeling is that with the weekly cold > backup we are heavily dependent on our archive logs and with the RMAN > incremental backup we would only be vulnerable for a day if an archive log > was lost. Recall that during recovery, Oracle reads the archive logs in > sequence and if for some reason an archive log is missing or unusable, > recover stops there. > We keep the week's RMAN backups on disk. Presumably if we had a > failure, RMAN would have what it needs on disk. Before a backup is deleted > from disk it is written to tape as part of the regular full system > backups. > I was having RMAN archive the archivelogs, but I couldn't figure out > what this was buying me and it doubled the amount of disk needed for the > archive logs. If we were using a media manager so RMAN was writing to > tape, > then this would be great, but since we are only backing up to disk, I felt > it wasn't needed. > I hope this gives you the details you need. As I said, I am an RMAN > novice, but always willing to answer more questions as I am able. > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Thursday, January 17, 2002 11:46 AM > To: Multiple recipients of list ORACLE-L > > > Dennis, > Could you please share your regular backup strategies with me for me to > understand this better? > > Thanks > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, January 17, 2002 9:00 AM > > > > > > Dennis, > > > > I hope you can clarify something in your plan for me. > > > > Why are you backing up to disk? By doing so, you are losing one of the > > chief benefits of RMAN. RMAN keeps a catalog of the tapes and knows > > which tapes to request to restore a backup, and which files to retrieve > > from those tapes. > > > > Do you backup the disk files to tape without the benefit of any tape > > management software? Do you manually label the tapes? > > > > Thanks, > > > > Jared > > > > > > > > > > > > DENNIS WILLIAMS > > list ORACLE-L <[EMAIL PROTECTED]> > > TOUCH.COM>cc: > > Sent by: Subject: RE: Disaster > recovery using RMAN > > [EMAIL PROTECTED] > > m > > > > > > 01/17/02 05:05 > > AM > > Please respond > > to ORACLE-L > > > > > > > > > > > > > > Sona - I am planning to test a disaster recovery using RMAN also. My > > company > > won't completely trust it until I can. Like you, I currently use RMAN to > > back up to disk, and write the backup to tape, keeping a Level 0 backup > on > > disk. Here are the steps I am planning for disaster recovery. > > > > 1. Export the RMAN catalog and FTP a copy to the target system each time > > nightly backups are complete. > > 2. Back the Level 0 backup to tape, along with RMAN catalog export. > > 3. Mount the tape on the disaster recovery test system. > > 4. Assume that same version of
Re:HP-UX 11/8.1.6.x/Index-Organized Tables
export/drop/import. Reply Separator Author: "Vergara; Michael (TEM)" <[EMAIL PROTECTED]> Date: 1/18/2002 7:30 AM Hey all...I have to do some reorgs of some malformed IOTs (Index- Organized Tables) in one of my databases. ALTER INDEX ... REBUILD doesn't work, on the IOT or it's PK index. Does anyone have an elegant method for doing a reorg on IOTs? Thanx, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: When should a table be partitioned?
Cherie - I am relatively new to partitioned tables, but I have two criteria. 1. Partition the table for performance. If you have some significant queries that will be doing full table scans (not unusual for data warehouses), then may partition so the full table scans just scan a selected partition. 2. Partition for manageability. If the table is really large you could partition by the year or some such so that you can add a new year and remove an old year. Another possibility is if you have many disks, even if the full table scan is going to hit the entire table, you can partition the table across several disks so that multiple disks are scanning simultaneously. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 7:21 AM To: Multiple recipients of list ORACLE-L We have a number of partitioned tables in a couple of existing data warehouses. We are working on the design for a new warehouse and need to decide which tables should be partitioned. For you folks that have partitioned tables, how do you decide which tables to be partition? Some tables with very large row counts are obvious candidates. If you go off of row counts solely, what is the cut-off point for where you should start partitioning? Is there a rule-of-thumb? I'm having difficulty with the not-as-huge, not-as-obvious candidates. What other criteria do you use besides row-count? Perhaps archival requirements? I guess it would depend on what you are using the partitioning to achieve. Partition exclusion for read performance improvement or for culling off old data, etc. Any shared insights for where to draw the line on partitioning candidates would be greatly appreciated. Thanks, Cherie Machler -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: SPACE FREE HOW?
Hi Can I use following command to recover the space. alter table deallocate unused; I don't want to truncate and import. Thx -Seema >From: "Ron Rogers" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: SPACE FREE HOW? >Date: Thu, 17 Jan 2002 13:55:47 -0800 > >Seema, > The table has set it'd HWM where the old data resided. The easiest? >method of recovering the space is to export the table and then truncate >the table followed by importing the table data back into the table. The >truncate function will remove all of the data and re-establish the size >back to original. >ROR mª¿ªm > > >>> [EMAIL PROTECTED] 01/17/02 03:57PM >>> >Hi >I deleted millions of rows from diffrent tables and I have not seen any > >impact on database size.What I have to do to get that free space? >Is it necessary to shutdown the database? >Thx >-Seema > > >_ >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: Seema Singh > INET: oracledbam@hotmReceived: from CONNECT-MTA by galotterail.com > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ron Rogers > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the 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: 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).
RE: Freeable memory
Title: RE: Freeable memory Hi Mike, I did a test on one of our 7.3 databases. It seems that some (but not all) freeable chunks get coalesced by flushing the shared pool. I tested it on a database with no other users logged in and took before/after pictures of the chunk breakdown. SQL> select ksmchcom contents, count(*) chunks, sum(decode(ksmchcls, 'recr', ksmchsiz)) recreatable, sum(decode(ksmchcls, 'freeabl', ksmchsiz)) freeable, sum(ksmchsiz) total from sys.x$ksmsp where ksmchcls not like 'R%' group by ksmchcom / CONTENTS CHUNKS RECREATABLE FREEABLE TOTAL -- --- -- -- KGL handles 188 54776 54776 PL/SQL DIANA 72 6604 134196 140800 PL/SQL MPCODE 20 6496 25264 31760 PLS cca hp desc 1 164 164 PLS non-lib hp 1 2096 2096 character set m 5 21456 21456 dictionary cach 85 172468 172468 fixed allocatio 26 832 832 free memory 25 82956796 kzull 6 284 284 library cache 460 78924 67472 146396 multiblock rea 1 1040 1040 permanent memor 1 13179484 row cache lru 30 1320 1320 session param v 9 19764 19764 sql area 119 186336 149732 336068 16 rows selected. SQL> alter system flush shared_pool; System altered. SQL> select ksmchcom contents, count(*) chunks, sum(decode(ksmchcls, 'recr', ksmchsiz)) recreatable, sum(decode(ksmchcls, 'freeabl', ksmchsiz)) freeable, sum(ksmchsiz) total from sys.x$ksmsp where ksmchcls not like 'R%' group by ksmchcom / CONTENTS CHUNKS RECREATABLE FREEABLE TOTAL -- --- -- -- KGL handles 67 19812 19812 PL/SQL DIANA 66 4508 125668 130176 PL/SQL MPCODE 12 3036 13648 16684 PLS cca hp desc 1 164 164 PLS non-lib hp 1 2096 2096 character set m 5 21456 21456 dictionary cach 78 144728 144728 fixed allocatio 26 832 832 free memory 35 83352232 kzull 6 284 284 library cache 164 27060 24940 52000 permanent memor 1 13179484 row cache lru 30 1320 1320 session param v 9 19764 19764 sql area 46 64152 60320 124472 15 rows selected. SQL> As for determining the chunks per session, the only way I know is to dump the heaps via ORADEBUG DUMP commands. The trace files will have the breakdown of type and status for each chunk. I don't remember off the top of my head how to dump it for individual sessions but it can be researched. Here is a sample of ORADUBG DUMP HEAPDUMP 10: *** 2002.01.18.10.43.55.000 *** SESSION ID:(7.8650) 2002.01.18.10.43.55.000 ** HEAP DUMP heap name="sga heap" desc=0x801c extent sz=0xfc4 alt=44 het=32767 rec=1 flg=2 opc=0 parent=0 owner=0 nex=0 xsz=0xc91a64 EXTENT 0 Chunk 85009b7c sz= 13179484 perm "perm " alo=7200716 EXTENT 1 Chunk 84009b80 sz= 15604404 free " " Chunk 84eeb634 sz= 560 recreate "library cache " latch=0 ds 84eeb870 sz= 560 Chunk 84eeb864 sz= 96 freeable "library cache " Chunk 84eeb8c4 sz= 172 recreate "KGL handles " latch=0 Chunk 84eeb970 sz= 288 recreate "KGL handles " latch=0 Chunk 84eeba90 sz= 560 recreate "library cache " latch=0 ds 84eebccc sz= 560 Chunk 84eebcc0 sz= 96 freeable "library cache " Chunk 84eebd20 sz= 172 recreate "KGL handles " latch=0 Chunk 84eebdcc sz= 560 recreate "library cache " latch=856ca7e0 ds 84eec008 sz= 560 Chunk 84eebffc sz= 96 freeable "library cache " Chunk 84eec05c sz= 3952 freeable "sql area " ds=84eee5e0 Chunk 84eecfcc sz= 296 recreate "KGL handles " latch=856ca7e0 Chunk 84eed0f4 sz= 168 recreate "library cache " latch=856ca7e0 ds 84eed330 sz= 168 Chunk 84eed19c sz= 392 freeable "library cache " ds=84eef190 Chunk 84eed324 sz=