RE: Row Migration
Actually, I first learned that trick from a Connor posting on this list (maybe around 2 or 3 years ago?) It has to conform to the same key preserved rules that updateable views do since that's what it is, just an in-line view as opposed to an actual physical view. So supposedly it's been available since 7.x when updateable views came along (and in-line views). There is an example in the Data Warehousing Guide (I think that's the one) in the 8i documentation, though the example is wrong (it omits the FROM clause). Anyway, I thought it was pretty cool the first time I saw Connor post it. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel > Carmichael > Sent: Friday, December 27, 2002 10:29 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Row Migration > > > don't feel too sheepish, I didn't know it either. Larry is the SQL guru > and I bow to his knowledge. and had already saved off this email as > this sort of update is something we do often and I ALWAYS have problems > figuring out the correct SQL :) > > rachel > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > Geez, I didn't know you could do that. > > > > Sheepishly, > > > > Jared > > > > On Friday 27 December 2002 03:38, Larry Elkins wrote: > > > Someone asked in a back channel email if parallelism is used. The > > select > > > portion of the update statement uses parallelism (though the > > updates > > > themselves get serialized) through the use of an in-line join > > update (to > > > avoid the second sub-query commonly used to constrain the rows > > being > > > updated): > > > > > > Update (Select /*+ parallel hints */ > > > From a,b > > > Where a.key = b.key) > > > Set a.col1 = b.col1, > > > a.col2 = b.col2 > > > . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Automatic backup on Oracle 9i
MetaLink is Oracle's support site. metalink.oracle.com No, I don't think your explanation is complicated, I just don't use OEM. I fired it up to take a look, but the backup portion requires the OEM repository to be setup, so I didn't learn anything. Yes, I *do* make backups, but use RMAN directly with Veritas NetBackup. Jared On Friday 27 December 2002 19:44, Sony kristanto wrote: > Jared, > Thanks Jared for your opinion, perhaps my explaination ain't quite right so > it looks like complicated but I will try to give detail explaination. By > the way what is MetaLink ? > > Rgrds, > > Sony > > > -Original Message- > > From: Jared Still [SMTP:[EMAIL PROTECTED]] > > Sent: Saturday, December 28, 2002 9:40 AM > > To: Sony kristanto; [EMAIL PROTECTED] > > Subject:Re: Automatic backup on Oracle 9i > > > > > > Hmm > > > > A lot of folks on this liststudiously avoid OEM. I know I do, > > and I'm not going to be much help on this. > > > > Have you tried MetaLink? > > > > Jared > > > > On Friday 27 December 2002 17:11, Sony kristanto wrote: > > > Yes, that's right Jared, by doing this we can make schedule when we > > > want > > > > to > > > > > backup our data onto hard disk or tape periodicaly (weekly or daily > > > even hour), thanks for your response and wishing you can help me to > > > solve it. > > > > > > > -Original Message- > > > > From: Jared Still [SMTP:[EMAIL PROTECTED]] > > > > Sent: Friday, December 27, 2002 10:06 AM > > > > To: [EMAIL PROTECTED]; Sony kristanto > > > > Subject:Re: Automatic backup on Oracle 9i > > > > > > > > > > > > Sony, > > > > > > > > What is an 'automatic' backup? > > > > > > > > Is this something supplied by that 'Oracle Enterprise Manager' > > > > thingy? > > > > > > > > Jared > > > > > > > > On Thursday 26 December 2002 17:23, Sony kristanto wrote: > > > > > Hi Listers, > > > > > I'm new on Oracle Database 9i after I migrated from Oracle 8i. > > > > > I try to use backup facility from Oracle 9i and I already follow > > > > > the instructions how to activate the automatic backup but when I > > > > > see the > > > > > > > > status > > > > > > > > > on history I get an error comment 'Failed'. I've try again and > > > > > again but the results are the same. Could someone out there tell me > > > > > why it can't runs. For your note I use 'SYS' as my user. I will > > > > > really appreciate > > > > > > > > your > > > > > > > > > help. > > > > > > > > > > Rgrds, > > > > > > > > > > Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Those Pesky Little Audit Files (ora_99999.aud)
Metalink Note #1022776.6 explains why.. :) - Kirti -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Those Pesky Little Audit Files (ora_9.aud)Yeah, it's a nuisance in most installations, but the idea is to be compliant with some security standard. Give me a 7.1 doc site (if it exists) and I'll find the details. I failed to find 7.1 doc on Google searches. Probably too much beer.MogensJamadagni, Rajendra wrote: O Oracle Guru's Please tell us, why _trace_files_public is *STILL* an underscore parameter?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Those Pesky Little Audit Files (ora_9.aud)They were put there in 7.1 in order to comply with some security standard. And their purpose is exactly to prevent a dba from logging in without being monitored. It's in the 7.1 new features manual, as far as I remember. That's also the version where it was suddenly not possible for the poor deveopers to see their own tracefiles, except if they set _trace_files_public=true.Mogens *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Row Migration
don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still <[EMAIL PROTECTED]> wrote: > > Geez, I didn't know you could do that. > > Sheepishly, > > Jared > > On Friday 27 December 2002 03:38, Larry Elkins wrote: > > Someone asked in a back channel email if parallelism is used. The > select > > portion of the update statement uses parallelism (though the > updates > > themselves get serialized) through the use of an in-line join > update (to > > avoid the second sub-query commonly used to constrain the rows > being > > updated): > > > > Update (Select /*+ parallel hints */ > > From a,b > > Where a.key = b.key) > > Set a.col1 = b.col1, > > a.col2 = b.col2 > > . > > > > Regards, > > > > Larry G. Elkins > > [EMAIL PROTECTED] > > 214.954.1781 > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > Larry > > > Elkins > > > Sent: Thursday, December 26, 2002 6:09 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Row Migration > > > > > > > > > Listers, > > > > > > 8.1.7.4 64 Bit Solaris > > > > > > Does row migration utilize DB File Sequential Reads on the table? > Off the > > > top of my head I would expect so, but I've never tested something > > > like that > > > before. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
Title: RE: Row Migration Gaaa!! Neither did I!!! (I've been looking for a better way to do that query for years...) > -Original Message- > From: Jared Still [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 27, 2002 6:49 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Row Migration > > > > Geez, I didn't know you could do that. > > Sheepishly, > > Jared > > On Friday 27 December 2002 03:38, Larry Elkins wrote: > > Someone asked in a back channel email if parallelism is used. The > > select portion of the update statement uses parallelism (though the > > updates themselves get serialized) through the use of an > in-line join > > update (to avoid the second sub-query commonly used to > constrain the > > rows being > > updated): > > > > Update (Select /*+ parallel hints */ > > From a,b > > Where a.key = b.key) > > Set a.col1 = b.col1, > > a.col2 = b.col2 > > . > > > > Regards, > > > > Larry G. Elkins > > [EMAIL PROTECTED] > > 214.954.1781 > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf > Of Larry > > > Elkins > > > Sent: Thursday, December 26, 2002 6:09 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Row Migration > > > > > > > > > Listers, > > > > > > 8.1.7.4 64 Bit Solaris > > > > > > Does row migration utilize DB File Sequential Reads on the table? > > > Off the top of my head I would expect so, but I've never tested > > > something like that before. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). >
RE: Distributed Option
Michael - Okay, this is a form of replication, known as "synchronous replication". That means that the updates occur synchronously, or within a 2-phase commit. This is implemented through database links. The drawback is that the transaction is as slow as the slowest database. If one database is unavailable, no transactions can complete. This can be okay for non-mission-critical situations. Someone earlier (I'm sorry, I didn't keep that message) pointed out that this may be an extra pay option back in 7.3.4. I think maybe you could query but to update you needed the extra option. Details are available in the Oracle manuals, which you can view online. I'm sorry, but I haven't worked on 7 in quite a few years. Based on your description of your situation, you may want to consider letting the application server do the honors. A simple app server like Tomcat can simply connect to each database. A more sophisticated app server like Oracle9iAS or WebLogic is capable of maintaining separate message queues, so you aren't limited by the slowest database and if a database is down, can maintain the queue of messages and apply them when it becomes available again. I'm speaking a little beyond my knowledge at this point, but you get the general idea and if this sounds promising you can investigate further with someone that actually knows what they are talking about. -Original Message- Sent: Friday, December 27, 2002 5:14 PM To: Multiple recipients of list ORACLE-L I believe this is different than replication, though many of the ideas and transactions would be the same. In this particular case, they are going to allow Name and address changes over the web. Those changes will cause updates to two some what different customer files on two different applications on two different "other" databases. What happens now is when they tried to do the update over the database link, and commit, they get "distributed option" not installed. This is further confused that one database has replication but still gets an error message about "distributed option" not installed. And the v$option shows "distributed option" as false or what ever. Connected to: Oracle7 Server Release 7.3.4.0.1 - Production With the parallel query option PL/SQL Release 2.3.4.0.0 - Production This is the sign on for one of the databases missing the option. My personal oracle shows: Connected to: Personal Oracle7 Release 7.2.2.3.1 - Production With the distributed and replication options PL/SQL Release 2.2.2.3.1 - Production Maks. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of DENNIS > WILLIAMS > Sent: Friday, December 27, 2002 4:19 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Distributed Option > > > Michael >By distributed, I assume you mean "replication"? >From what I can tell, basic replication is included with > Standard Edition > and advanced replication is included with Enterprise Edition. >I think you run a script, something like "catrep.sql" in > rdbms/admin, so > you should be able to get the pieces installed. >Replicating between different Oracle versions could be challenging, but > doable. Others on the list can probably offer specific advice on pitfalls. >We haven't implemented replication here, just studied. From what I can > tell, planning for replication is everything. I have only been > able to find > 2 books on it. The prize is Marie Buretta's Database Replication. > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613 > /sr%3D11-1 > /ref%3Dsr%5F11%5F1/102-1511927-6720101 > It really tells you everything you need as an organization to prepare for > replication. Replication takes a lot of administration so it should be a > gold mine since you are consulting. >The other book is Oracle Distributed Systems by Charles Dye > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613 > /sr%3D11-1 > /ref%3Dsr%5F11%5F1/102-1511927-6720101 > > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Friday, December 27, 2002 2:39 PM > To: Multiple recipients of list ORACLE-L > > > I've got a client that needs "distributed > option" installed on several databases, > versions 7.3.4, 8.0.5, and 8.1.7... > > Problem may be I'm not sure we'll have all > the CD's as vendors of applications did > most of the installs and we think we'll > find that they took the CD's with them. > After all, if it's up and running and > vendor supported(or was), why would the > client need the CD? > > Anyhow, is it just a "free" option that will > need to be selected with the Oracle installer, > or is it an "add on" that one is supposed to > contact Oracle on? Or perhaps it just involves > running one of those all but undocumented > packages. > > There are many ways to work around this if > we have to, but would like to know what all > is involved if we can get the distributed > option installed on all
RE: Automatic backup on Oracle 9i
Jared, Thanks Jared for your opinion, perhaps my explaination ain't quite right so it looks like complicated but I will try to give detail explaination. By the way what is MetaLink ? Rgrds, Sony > -Original Message- > From: Jared Still [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, December 28, 2002 9:40 AM > To: Sony kristanto; [EMAIL PROTECTED] > Subject: Re: Automatic backup on Oracle 9i > > > Hmm > > A lot of folks on this liststudiously avoid OEM. I know I do, > and I'm not going to be much help on this. > > Have you tried MetaLink? > > Jared > > > On Friday 27 December 2002 17:11, Sony kristanto wrote: > > Yes, that's right Jared, by doing this we can make schedule when we want > to > > backup our data onto hard disk or tape periodicaly (weekly or daily even > > hour), thanks for your response and wishing you can help me to solve it. > > > > > -Original Message- > > > From: Jared Still [SMTP:[EMAIL PROTECTED]] > > > Sent: Friday, December 27, 2002 10:06 AM > > > To: [EMAIL PROTECTED]; Sony kristanto > > > Subject: Re: Automatic backup on Oracle 9i > > > > > > > > > Sony, > > > > > > What is an 'automatic' backup? > > > > > > Is this something supplied by that 'Oracle Enterprise Manager' thingy? > > > > > > Jared > > > > > > On Thursday 26 December 2002 17:23, Sony kristanto wrote: > > > > Hi Listers, > > > > I'm new on Oracle Database 9i after I migrated from Oracle 8i. > > > > I try to use backup facility from Oracle 9i and I already follow the > > > > instructions how to activate the automatic backup but when I see the > > > > > > status > > > > > > > on history I get an error comment 'Failed'. I've try again and again > > > > but the results are the same. Could someone out there tell me why it > > > > can't runs. For your note I use 'SYS' as my user. I will really > > > > appreciate > > > > > > your > > > > > > > help. > > > > > > > > Rgrds, > > > > > > > > Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OAS
Does OAS already include in Oracle 9i ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row Migration
Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: > Someone asked in a back channel email if parallelism is used. The select > portion of the update statement uses parallelism (though the updates > themselves get serialized) through the use of an in-line join update (to > avoid the second sub-query commonly used to constrain the rows being > updated): > > Update (Select /*+ parallel hints */ > From a,b > Where a.key = b.key) > Set a.col1 = b.col1, > a.col2 = b.col2 > . > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry > > Elkins > > Sent: Thursday, December 26, 2002 6:09 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Row Migration > > > > > > Listers, > > > > 8.1.7.4 64 Bit Solaris > > > > Does row migration utilize DB File Sequential Reads on the table? Off the > > top of my head I would expect so, but I've never tested something > > like that > > before. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LMT- Migrated tablespaces
IIRC, a 160m table would be in an LMT with 4m extents. The 3 extent sizes recommended in the paper are 128k, 4m and 128m. > 1) Create LMT Tablespaces with an extent size of 160k ? ( This is > ignored by the import, tables will be one extent big) Not so. If you create an LMT of the correct name for imp to import a table into, it will be created with the uniform size extents you specified at tablespace creation. By 'correct name', I mean either a tablespace of the same name as the one the table was exported from, or the owners default tablespace is an LMT, and there is not a tablespace to match the name of that in the import file. > 2) Reset the "next_extent" on my apps tables to 160k ? > 3) Set pctincrease to 0 ? ( I think this is a given ) Both of these are invalid on an LMT. HTH, Jared On Friday 27 December 2002 10:13, Browett, Darren wrote: > I have crossposted this question on the Oracle-Apps list, I would like > to > get the opinion of this list as it is more of database issue as opposed > to apps. > > The question is about LMT and extent management with regards to Oracle > 11i. > > When upgrading to 11i, it creates "migrated" LMTS as opposed to > "uniform/system" ones, and therefore do not conform to the rules > of "correct" LMTS. > > My understanding is, even though my tablespaces are LMT, the tables > still act like they are dictionary managed with regards to extent > growth. > > According to "How to stop defrag, and start living ." for tables > under 160M I should have an extent size of 160k. > > With that in mind, should I > > 1) Create LMT Tablespaces with an extent size of 160k ? ( This is > ignored >by the import, tables will be one extent big) > 2) Reset the "next_extent" on my apps tables to 160k ? > 3) Set pctincrease to 0 ? ( I think this is a given ) > > Thanks > > Darren > > > > -- > Darren Browett P.Eng This > message was transmitted > Data Administratorusing > 100% recycled electrons > Information and Communication Technology > City of Coquitlam > P:(604)927 - 3614 > E:[EMAIL PROTECTED] > > --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Automatic backup on Oracle 9i
Hmm A lot of folks on this liststudiously avoid OEM. I know I do, and I'm not going to be much help on this. Have you tried MetaLink? Jared On Friday 27 December 2002 17:11, Sony kristanto wrote: > Yes, that's right Jared, by doing this we can make schedule when we want to > backup our data onto hard disk or tape periodicaly (weekly or daily even > hour), thanks for your response and wishing you can help me to solve it. > > > -Original Message- > > From: Jared Still [SMTP:[EMAIL PROTECTED]] > > Sent: Friday, December 27, 2002 10:06 AM > > To: [EMAIL PROTECTED]; Sony kristanto > > Subject:Re: Automatic backup on Oracle 9i > > > > > > Sony, > > > > What is an 'automatic' backup? > > > > Is this something supplied by that 'Oracle Enterprise Manager' thingy? > > > > Jared > > > > On Thursday 26 December 2002 17:23, Sony kristanto wrote: > > > Hi Listers, > > > I'm new on Oracle Database 9i after I migrated from Oracle 8i. > > > I try to use backup facility from Oracle 9i and I already follow the > > > instructions how to activate the automatic backup but when I see the > > > > status > > > > > on history I get an error comment 'Failed'. I've try again and again > > > but the results are the same. Could someone out there tell me why it > > > can't runs. For your note I use 'SYS' as my user. I will really > > > appreciate > > > > your > > > > > help. > > > > > > Rgrds, > > > > > > Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
8.1.7 with *what* version of Portal??
we have 8i (8.1.7.1) running at our shop and one of our developers wants to use WebDB (what I understand is now "Portal"). in checking OTN and other places, I can't figure out what version of Portal (or WebDB) I should be installing, nor where I can get it. can anyone tell me what version I should be trying to install, where I can get it and where any resources on installation and configuration might be located? it is my understanding that Portal is a collection of packages that work with our existing database and doesn't need anything extra. is that also truy? aside from the raw 8i database, will I need to install anything else (yes, we have apache running ok)? Deb Carbide Systems Group _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle Developer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automatic backup on Oracle 9i
Yes, that's right Jared, by doing this we can make schedule when we want to backup our data onto hard disk or tape periodicaly (weekly or daily even hour), thanks for your response and wishing you can help me to solve it. > -Original Message- > From: Jared Still [SMTP:[EMAIL PROTECTED]] > Sent: Friday, December 27, 2002 10:06 AM > To: [EMAIL PROTECTED]; Sony kristanto > Subject: Re: Automatic backup on Oracle 9i > > > Sony, > > What is an 'automatic' backup? > > Is this something supplied by that 'Oracle Enterprise Manager' thingy? > > Jared > > On Thursday 26 December 2002 17:23, Sony kristanto wrote: > > Hi Listers, > > I'm new on Oracle Database 9i after I migrated from Oracle 8i. > > I try to use backup facility from Oracle 9i and I already follow the > > instructions how to activate the automatic backup but when I see the > status > > on history I get an error comment 'Failed'. I've try again and again but > > the results are the same. Could someone out there tell me why it can't > > runs. For your note I use 'SYS' as my user. I will really appreciate > your > > help. > > > > Rgrds, > > > > Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
John, the $10 is on the way ;-) Right now I'm looking at the impact of rows migrating due to updates expanding the rows. So I was considering fetch row continued as opposed to analyze .. list chained rows (my first thought) before and after the update. To know how many rows migrated due to the updates, I could do a parallel fts prior to the update and record this number (or insert all the stats into a holding table). And then after the update do it again. The delta should give me the number of rows that migrated, and would probably be much faster than the analyze list chained rows (or a compute and looking at the chain_cnt) since I could use parallelism. And then was definitely looking at using this on both the staging version of the table and the production copy from a query perspective. But my entire test fell apart ;-) The table, both the staging and the "real" in the DM, without my knowing it was going to occur, was rebuilt with a pctfree of 40 overnight on Thursday. So I don't have a baseline to do a before and after comparison to gauge the impact of the rows migrating during the updates. It doesn't look like the process ran again after that. The person who wrote it is on vacation, and the person watching it is off on Fridays. And they are migrating that instance and domain to a new domain on a new machine this weekend, so I don't really see anything happening with this, at least not this weekend. But I did do a quick and dirty test. Slammed 1,000,000 rows into a two column table with pctfree of 0, with the second column null. Then updated the second column and got a timing on it (all the rows migrated). Then dropped, recreated, and repeated with a very high value (95) for pctfree. The update finished 4 times faster. Didn't do any detailed analysis or stats gathering -- just thought I would put together a quick and dirty. I would still like to put together a test that more realistically mimics the "real" case. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John > Kanagaraj > Sent: Friday, December 27, 2002 12:44 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Row Migration > > > Larry, > > Don't want to preach to the Guru, but have you checked the values > for 'table > fetch continued row'? > > StatisticTotal per Second > per Trans > - > > table fetch by rowid 577,820,727 40,129.2 > 61,248.8 > table fetch continued row 137,202 9.5 > 14.5 > > This when coming out of V$SESSTAT could give a good indication of > number of > fetches by migrated as well as chained rows for that session. You > could also > look at V$SESSION.MAX_WAIT for 'db file sequential read' events... > > Let us know what you find! > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > What would you see if you were allowed to look back at your life > at the end > of your journey in this earth? > > ** The opinions and statements above are entirely my own and not > those of my > employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
So I'm doomed? ;-) Ok, so how am I going to know which block it went to, the first step towards seeing if it was relatively nearby or maximum scatter? I'm guessing I would have to dump a block and look at the "placeholder" or "stub" in the original location and see where it points (I'm assuming it has to)? Just conjecture and the first thing I would think of since I can't think of any DD view or X$ that would tell me where a row migrated from/to. And I'm not so much concerned about the extra LIO's and latching at this point since I'm focused on the impact of a row migrating during an update. And don't think we will allow migrated rows in the table (though one might make a case for eating a few migrated rows for the sake of a significantly reduced number of blocks). But over time, this sort of update *will* eventually happen to all the rows anyway, so we would be looking at the higher number of blocks somewhere down the road. But it's all irrelevant now anyway since both the staging table and it's "real" counterpart in the DM were both re-orged with a pctfree of 40 (found that out this morning). I'll still need to keep an eye on migrating rows, but I'm not going to allow a handful of them make us go overboard on pctfree and "wasting" a lot of space. Not that I'm asking you to do our work, but curious what are the things and considerations *you* would consider in building such a test case? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan > Lewis > Sent: Friday, December 27, 2002 2:59 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Row Migration > > > > When you do your testing, don't forget to keep an > eye on the change in dependent logical I/O and latching. > > Fetching a migrated row will require an extra buffer > visit to find the row data. This MAY turn into an > extra disk read but at the least it IS another > buffer visit, which means another hit on the > cache-buffers-chains latch, and may mean further > work done getting another buffered block to the > correct read-consistent state. > > I think you'll have to model your test very carefully - > it wouldn't be too hard to produce two different models > with totally contradictory results - one based on the > migration going to a relatively nearby block, the other > based on the update and migration taking place in > a way that ensures maximum scatter of the migrated > row piece. > > The former may hide I/O problems, the latter may exaggerate > the I/O problems and hide the latch issues; and in either > case you may fail to emulate the read-consistency issue > properly. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon a new one-day tutorial: > Cost Based Optimisation > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
Thanks for those comments, but that's a little down the road for what I'm looking at right now -- trying to determine the overhead associated with updates and the update causing a row to migrate. We don't intend to let the chaining actually make it into the DM. But it's good to see someone put some numbers on it, and something I would be interested in repeating at some time in the future should migration/chaining occur in the target table. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nick WagnerSent: Friday, December 27, 2002 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up. It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message-From: Larry Elkins [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
RE: Distributed Option
I believe this is different than replication, though many of the ideas and transactions would be the same. In this particular case, they are going to allow Name and address changes over the web. Those changes will cause updates to two some what different customer files on two different applications on two different "other" databases. What happens now is when they tried to do the update over the database link, and commit, they get "distributed option" not installed. This is further confused that one database has replication but still gets an error message about "distributed option" not installed. And the v$option shows "distributed option" as false or what ever. Connected to: Oracle7 Server Release 7.3.4.0.1 - Production With the parallel query option PL/SQL Release 2.3.4.0.0 - Production This is the sign on for one of the databases missing the option. My personal oracle shows: Connected to: Personal Oracle7 Release 7.2.2.3.1 - Production With the distributed and replication options PL/SQL Release 2.2.2.3.1 - Production Maks. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of DENNIS > WILLIAMS > Sent: Friday, December 27, 2002 4:19 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Distributed Option > > > Michael >By distributed, I assume you mean "replication"? >From what I can tell, basic replication is included with > Standard Edition > and advanced replication is included with Enterprise Edition. >I think you run a script, something like "catrep.sql" in > rdbms/admin, so > you should be able to get the pieces installed. >Replicating between different Oracle versions could be challenging, but > doable. Others on the list can probably offer specific advice on pitfalls. >We haven't implemented replication here, just studied. From what I can > tell, planning for replication is everything. I have only been > able to find > 2 books on it. The prize is Marie Buretta's Database Replication. > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613 > /sr%3D11-1 > /ref%3Dsr%5F11%5F1/102-1511927-6720101 > It really tells you everything you need as an organization to prepare for > replication. Replication takes a lot of administration so it should be a > gold mine since you are consulting. >The other book is Oracle Distributed Systems by Charles Dye > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613 > /sr%3D11-1 > /ref%3Dsr%5F11%5F1/102-1511927-6720101 > > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Friday, December 27, 2002 2:39 PM > To: Multiple recipients of list ORACLE-L > > > I've got a client that needs "distributed > option" installed on several databases, > versions 7.3.4, 8.0.5, and 8.1.7... > > Problem may be I'm not sure we'll have all > the CD's as vendors of applications did > most of the installs and we think we'll > find that they took the CD's with them. > After all, if it's up and running and > vendor supported(or was), why would the > client need the CD? > > Anyhow, is it just a "free" option that will > need to be selected with the Oracle installer, > or is it an "add on" that one is supposed to > contact Oracle on? Or perhaps it just involves > running one of those all but undocumented > packages. > > There are many ways to work around this if > we have to, but would like to know what all > is involved if we can get the distributed > option installed on all of their databases. > > It's on my Personal Oracle, but seems to > be part of a general set of things that > get installed. > > > > Kline's Consulting > Michael Alan Kline, Sr., Owner > 13308 Thornridge Court; Midlothian, VA 23112, USA. > Work: 804-744-1545 Cell: 804-314-6262 > Pager: 877-705-1155 ICQ: 1009605, 975313 > [EMAIL PROTECTED]; [EMAIL PROTECTED]; > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Michael Kline > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXA
RE: Those Pesky Little Audit Files (ora_99999.aud)
Title: RE: Those Pesky Little Audit Files (ora_9.aud) that calls for a super-duper-pooper-scooper. :-) -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Those Pesky Little Audit Files (ora_9.aud) Or you might have to do the cleanup sooner if you have 9202 on AIX 5.1 and you have external tables and you run into that (yet unknown) pmon memory leak (where it supposedly corrupts first 80 bytes of memory). When the instance finally crashed, among 540 trace and trw files, one tracefile was 1.3GB (no it is not a type) in size. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 1:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: Those Pesky Little Audit Files (ora_9.aud) Yupp, I do the same thing. I figure if there's a problem documented somewhere in those files and I haven't responded to them in 30-60 days then its too old to worry about anyway. Sometimes OWS wants an alert log which goes back to the beginning of time but I just tell 'em to get real. I just up'd the listerner log renaming routine to run on a daily basis because it was too big too view. Now the average size of the listener.julianday file is 20-50MB. The process of cleaning up oracle log and trace files is like a "pooper-scooper" at the end of the rodeo parade. Steve Orr Bozeman, Montana
Re: Rebuilding Indexes...
fair enough. I retract the example :) --- Jared Still <[EMAIL PROTECTED]> wrote: > > Yes, but that's a special case. You are not rebuilding > the index as part of some regular index maintenance. > > Jared > > On Friday 27 December 2002 04:43, Rachel Carmichael wrote: > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > > the index to be rebuilt when I re-enable the primary key > constraint, it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
As Denny also suggested. I'm gonna try that on Monday, on my sandbox database. If this does work in 9i as well (and it should, I hope), I can just rebuild the unusable partition and not the entire index. The index build will only have to happen once. --- Jack Silvey <[EMAIL PROTECTED]> wrote: > Hey Rachel, > > Consider using a non-unique index for your primary key constraint. If > > you prebuild it and then add the constraint, Oracle will not drop the > > index when you drop the PK constraint, and you can control the index > build that a way (and build it in parallel to boot). > > hth, > > Jack > > > > > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > > the index to be rebuilt when I re-enable the primary key > constraint, > it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > > > > Thanks, > > Jack Silvey > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jack Silvey > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL
RE: Row Migration
Title: RE: Row Migration also, what version of Oracle and how many columns on the table? -Original Message- From: Nick Wagner [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up. It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message- From: Larry Elkins [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 3:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar N Sent: Friday, December 27, 2002 2:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration yes, row migration will degrade the performance..
Re: Rebuilding Indexes...
it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > I don't have access to 9.2.0.1 right now. But can you try creating > a non- > unique index instead of the unique index. If you create a unique > index, it gets > dropped. That's the behavior on 8.1.x also. But if it's a non-unique > index, it > shouldn't get dropped. > > Regards, > Denny > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > I created a unique index in the primary key columns > > I created the primary key constraint without specifying an index > > I checked that the index existed, it did > > I dropped the primary key constraint > > I checked that the index existed, it didn't > > > > try it I tried various combinations before posting this note > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > If you build a separate index to enforce the primary key, > Oracle > > > shouldn't > > > drop it when you disable or drop the primary key. > > > > > > Regards, > > > Denny > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > Here's a reason: > > > > > > > > have you ever tried to find the three duplicate rows in a 12 > > > million > > > > row table without using the primary key constraint? I've had to > > > > disable > > > > or drop the constraint in order to use the exceptions table. > Once > > I > > > do > > > > that, even if I've built a separate index that enforces the > > primary > > > > key > > > > constraint, Oracle drops the index. So I HAVE to rebuild it. If > I > > > > allow > > > > the index to be rebuilt when I re-enable the primary key > > > constraint, > > > > it > > > > builds it in the default tablespace of the table owner, not > where > > I > > > > want it. > > > > > > > > if anyone has a better way to fix this problem, I'm more than > > happy > > > to > > > > hear it! It's a data warehouse and the third party app has a > bug > > we > > > > can't find and on occasion sqlloads (via direct path) duplicate > > > rows > > > > > > > > Rachel > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > > > Though I have published a script for determining indexes that > > > > > need to be rebuilt, and then rebuilding them, I have to say > > that > > > > > this is almost never necessary. > > > > > > > > > > Why are you rebuilding indexes? About the only reason for > ever > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > Jared > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > TIA, > > > > > > Rich > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > name="Attachment: > > > 1" > > > > > Content-Transfer-Encoding: 7bit > > > > > Content-Description: > > > > > > > > > > -- > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > -- > > > > > Author: Jared Still > > > > > INET: [EMAIL PROTECTED] > > > > > > > > > > Fat City Network Services-- 858-538-5051 > > > http://www.fatcity.com > > > > > San Diego, California-- Mailing list and web hosting > > > > services > > > > > > > > > > > > > > > - > > > > > To REMOVE yourself from this mailing list, send an E-Mail > > message > > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and > > > in > > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > > (or the name of mailing list you want to be removed from). > You > > > may > > > > > also send the HELP command for other information (like > > > subscribing). > > > > > > > > > > > > > > > > > __ > > > > Do you Yahoo!? > > > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > > > http://mailplus.yahoo.com > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Rachel Carmichael > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California-- Mailing list and web hosting > > > services > > > > > > > > > > - > > > > To REMOVE yourself from this mailing list, send an E-Mail > message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > a
Re: Rebuilding Indexes...
I know I have 3 duplicates because that's how many I deleted when I got rid of them. If you use direct=true on sqlloader, the primary key constraint is NOT disabled even if the index partition is made unusable. and we know it's an app problem. That's a given. The app on occasion re-runs part of a load. What we don't know is why. As yet. --- [EMAIL PROTECTED] wrote: > > If you know you have 3 duplicate records in the table then the PK > must have > already been disabled so you have to rebuild anyway. I do not see > where you had to disable in order to use the exception table. It was > already disabled therefore it probably not an app problem but a > disable > constraint > problem unless direct load bypasses constraint checking which I am > not > sure. > > Rick > > > > > > Rachel > > Carmichael To: Multiple recipients > of list ORACLE-L <[EMAIL PROTECTED]> > > ahoo.com>Subject: Re: Rebuilding > Indexes... > Sent by: > > [EMAIL PROTECTED] > > om > > > > > > 12/27/2002 > > 07:43 AM > > Please respond > > to ORACLE-L > > > > > > > > > > Here's a reason: > > have you ever tried to find the three duplicate rows in a 12 million > row table without using the primary key constraint? I've had to > disable > or drop the constraint in order to use the exceptions table. Once I > do > that, even if I've built a separate index that enforces the primary > key > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > the index to be rebuilt when I re-enable the primary key constraint, > it > builds it in the default tablespace of the table owner, not where I > want it. > > if anyone has a better way to fix this problem, I'm more than happy > to > hear it! It's a data warehouse and the third party app has a bug we > can't find and on occasion sqlloads (via direct path) duplicate rows > > Rachel > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > __
Re: Rebuilding Indexes...
Jared, it was built with enable validate. Doesn't seem to matter if it's validate or not. Rachel --- Jared Still <[EMAIL PROTECTED]> wrote: > > Rick, > > You're not considering a PK built with 'enable novalidate'. > > Jared > > > On Friday 27 December 2002 05:38, [EMAIL PROTECTED] wrote: > > If you know you have 3 duplicate records in the table then the PK > must have > > already been disabled so you have to rebuild anyway. I do not see > > where you had to disable in order to use the exception table. It > was > > already disabled therefore it probably not an app problem but a > disable > > constraint > > problem unless direct load bypasses constraint checking which I am > not > > sure. > > > > Rick > > > > > > > > > > Rachel > > Carmichael To: Multiple > recipients of > > list ORACLE-L <[EMAIL PROTECTED]> > ahoo.com>Subject: Re: > Rebuilding > > Indexes... Sent by: > > [EMAIL PROTECTED] > > om > > > > > > 12/27/2002 > > 07:43 AM > > Please respond > > to ORACLE-L > > > > > > > > > > > > > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > > the index to be rebuilt when I re-enable the primary key > constraint, it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (not
Re: Those Pesky Little Audit Files (ora_99999.aud)
I've got hard-copy of the 7 docs if you can give me a clue where to start searching for it... --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > Yeah, it's a nuisance in most installations, but the idea is to be > compliant with some > security standard. Give me a 7.1 doc site (if it exists) and I'll > find > the details. I failed to find 7.1 doc on Google searches. Probably > too > much beer. > > Mogens > > Jamadagni, Rajendra wrote: > > > O Oracle Guru's > > Please tell us, why _trace_files_public is *STILL* an underscore > > parameter?? > > > > Raj > > __ > > > > Rajendra Jamadagni MIS, ESPN Inc. > > > > Rajendra dot Jamadagni at ESPN dot com > > > > Any opinion expressed here is personal and doesn't reflect that of > > ESPN Inc. > > > > QOTD: Any clod can have facts, but having an opinion is an art! > > > > -Original Message- > > From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]] > > Sent: Friday, December 27, 2002 2:09 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: Those Pesky Little Audit Files (ora_9.aud) > > > > They were put there in 7.1 in order to comply with some > security > > standard. And their purpose is exactly to prevent a dba from > > logging in without being monitored. It's in the 7.1 new > features > > manual, as far as I remember. That's also the version where it > > was suddenly not possible for the poor deveopers to see their > own > > tracefiles, except if they set _trace_files_public=true. > > > > Mogens > > > > > > > >*This > e-mail message is confidential, intended only for the named > recipient(s) above and may contain information that is privileged, > attorney work product or exempt from disclosure under applicable law. > If you have received this message in error, or are not the named > recipient(s), please immediately notify corporate MIS at (860) > 766-2000 and delete this e-mail message from your computer, Thank > you.*1 > > > > > > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Forms,Reports performance problem
Oracle 8.1.6 Win Nt Has anyone experience/heard of performace problems after migrating from forms 5 to forms 6.0.8.15 and from reports 2.5 to 3.0? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Distributed Option
Michael By distributed, I assume you mean "replication"? From what I can tell, basic replication is included with Standard Edition and advanced replication is included with Enterprise Edition. I think you run a script, something like "catrep.sql" in rdbms/admin, so you should be able to get the pieces installed. Replicating between different Oracle versions could be challenging, but doable. Others on the list can probably offer specific advice on pitfalls. We haven't implemented replication here, just studied. From what I can tell, planning for replication is everything. I have only been able to find 2 books on it. The prize is Marie Buretta's Database Replication. http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613/sr%3D11-1 /ref%3Dsr%5F11%5F1/102-1511927-6720101 It really tells you everything you need as an organization to prepare for replication. Replication takes a lot of administration so it should be a gold mine since you are consulting. The other book is Oracle Distributed Systems by Charles Dye http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613/sr%3D11-1 /ref%3Dsr%5F11%5F1/102-1511927-6720101 Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 27, 2002 2:39 PM To: Multiple recipients of list ORACLE-L I've got a client that needs "distributed option" installed on several databases, versions 7.3.4, 8.0.5, and 8.1.7... Problem may be I'm not sure we'll have all the CD's as vendors of applications did most of the installs and we think we'll find that they took the CD's with them. After all, if it's up and running and vendor supported(or was), why would the client need the CD? Anyhow, is it just a "free" option that will need to be selected with the Oracle installer, or is it an "add on" that one is supposed to contact Oracle on? Or perhaps it just involves running one of those all but undocumented packages. There are many ways to work around this if we have to, but would like to know what all is involved if we can get the distributed option installed on all of their databases. It's on my Personal Oracle, but seems to be part of a general set of things that get installed. Kline's Consulting Michael Alan Kline, Sr., Owner 13308 Thornridge Court; Midlothian, VA 23112, USA. Work: 804-744-1545 Cell: 804-314-6262 Pager: 877-705-1155 ICQ: 1009605, 975313 [EMAIL PROTECTED]; [EMAIL PROTECTED]; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Kline INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re:RE: Deadlock
I think I'll resist the temptation to review the entire trace file. However, since this is a v9 deadlock dump, I think you should find that you have a complete processstate dump after the initial deadlock graph. Somewhere near the end of the dump you should find the CURSOR section, which should list all the current cursors for the session. Read through these, they may give you a clue about the SQL that has pushed the TM lock from a 3 to a 5 on the problem table. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 27 December 2002 00:15 >Yes it is 9013. This is not an array based update. As per the trace file >same statement is being executed by both sessions. I can directly send you >the trace file if there is a need. > >There are triggers on the tables, I'll look into parent table activity. But >there are indexes on all foreign keys except one which corresponds to a >static master table containing PO TYPES. That table is not being updated. > >How can I dig deeper into this issue. > >Thanks >Shaleen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row Migration
When you do your testing, don't forget to keep an eye on the change in dependent logical I/O and latching. Fetching a migrated row will require an extra buffer visit to find the row data. This MAY turn into an extra disk read but at the least it IS another buffer visit, which means another hit on the cache-buffers-chains latch, and may mean further work done getting another buffered block to the correct read-consistent state. I think you'll have to model your test very carefully - it wouldn't be too hard to produce two different models with totally contradictory results - one based on the migration going to a relatively nearby block, the other based on the update and migration taking place in a way that ensures maximum scatter of the migrated row piece. The former may hide I/O problems, the latter may exaggerate the I/O problems and hide the latch issues; and in either case you may fail to emulate the read-consistency issue properly. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 27 December 2002 12:25 >Well, yes, I would agree with that ;-) > >What we are trying to determine here in this particular case is how much or >what percentage of the slowdown in the process is due to the migration of >rows. We aren't ready (until we do some testing) to make a blanket statement >that row migration *alone* is the cause of the significant slowdown. In >other words, I'm not willing to make a statement to the powers that be that >simply increasing the pctfree is going to make things normal again until we >have a chance to do some more detailed monitoring and testing. > >Regards, > >Larry G. Elkins -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Distributed Option
I've got a client that needs "distributed option" installed on several databases, versions 7.3.4, 8.0.5, and 8.1.7... Problem may be I'm not sure we'll have all the CD's as vendors of applications did most of the installs and we think we'll find that they took the CD's with them. After all, if it's up and running and vendor supported(or was), why would the client need the CD? Anyhow, is it just a "free" option that will need to be selected with the Oracle installer, or is it an "add on" that one is supposed to contact Oracle on? Or perhaps it just involves running one of those all but undocumented packages. There are many ways to work around this if we have to, but would like to know what all is involved if we can get the distributed option installed on all of their databases. It's on my Personal Oracle, but seems to be part of a general set of things that get installed. Kline's Consulting Michael Alan Kline, Sr., Owner 13308 Thornridge Court; Midlothian, VA 23112, USA. Work: 804-744-1545 Cell: 804-314-6262 Pager: 877-705-1155 ICQ: 1009605, 975313 [EMAIL PROTECTED]; [EMAIL PROTECTED]; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Kline INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Versioning the Database !
Used to use that method in a former company with our DB2 database. We had one DB with schemas of DBPROD, DBTEST, DBSTST, and DBRTST. At various testing stages we would move the objects to a different schema The application had a variable for who owned the structure. That way we could be developing (DBTEST), in the development test phase (DBRTST), into user acceptance testing (DBSTST), and into production (DBPROD) with different versions all at the same time. I could see it happening with Oracle as well..but why not just use different instances to house the different stages of development. That way you can have everything under the same schema and not have to worry about any synonym or schema switching. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 1:44 PM To: Multiple recipients of list ORACLE-L I am in a peculiar situation where the development & design is happening in parellel. It would table definitions, table data (Reference Data), View definitions, the design itself ( LDM). It would be a situation, where there are different schema's need to be maintained at different stages of the project (Devlp, IT, QA, Staging). Since these activities would be parellel versioning would help. Hence this versioning. Regards Shree -Original Message- Sent: Friday, December 27, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Versioning the database ? Take a backup of the database on a seperate tape each day ! What components of the database do you want to version ? Table definitions ? View definitions ? Packages/Procedures/Triggers ? Code Objects should be versioned, but data objects [Tables/Indexes/Sequences] would generally not vary once the design is done, save for a few changes/additions/enhancements. Hemant At 06:28 AM 27-12-02 -0800, you wrote: >Hi, > > I want to version the Database for development, IT, QA and staging >environment. > Can some one suggest different methods and best possible approach to >maintain the database. > > Database is in design stage & development has partially started. We >are using MKS for versioning. > >Regards >Shree >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Rama, Shreekantha (CAP, CARD) > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (CAP, CARD) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Those Pesky Little Audit Files (ora_99999.aud)
Title: RE: Those Pesky Little Audit Files (ora_9.aud) Or you might have to do the cleanup sooner if you have 9202 on AIX 5.1 and you have external tables and you run into that (yet unknown) pmon memory leak (where it supposedly corrupts first 80 bytes of memory). When the instance finally crashed, among 540 trace and trw files, one tracefile was 1.3GB (no it is not a type) in size. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 1:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: Those Pesky Little Audit Files (ora_9.aud) Yupp, I do the same thing. I figure if there's a problem documented somewhere in those files and I haven't responded to them in 30-60 days then its too old to worry about anyway. Sometimes OWS wants an alert log which goes back to the beginning of time but I just tell 'em to get real. I just up'd the listerner log renaming routine to run on a daily basis because it was too big too view. Now the average size of the listener.julianday file is 20-50MB. The process of cleaning up oracle log and trace files is like a "pooper-scooper" at the end of the rodeo parade. Steve Orr Bozeman, Montana *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Versioning the Database !
I am in a peculiar situation where the development & design is happening in parellel. It would table definitions, table data (Reference Data), View definitions, the design itself ( LDM). It would be a situation, where there are different schema's need to be maintained at different stages of the project (Devlp, IT, QA, Staging). Since these activities would be parellel versioning would help. Hence this versioning. Regards Shree -Original Message- Sent: Friday, December 27, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Versioning the database ? Take a backup of the database on a seperate tape each day ! What components of the database do you want to version ? Table definitions ? View definitions ? Packages/Procedures/Triggers ? Code Objects should be versioned, but data objects [Tables/Indexes/Sequences] would generally not vary once the design is done, save for a few changes/additions/enhancements. Hemant At 06:28 AM 27-12-02 -0800, you wrote: >Hi, > > I want to version the Database for development, IT, QA and staging >environment. > Can some one suggest different methods and best possible approach to >maintain the database. > > Database is in design stage & development has partially started. We >are using MKS for versioning. > >Regards >Shree >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Rama, Shreekantha (CAP, CARD) > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (CAP, CARD) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LMT- Migrated tablespaces
I have crossposted this question on the Oracle-Apps list, I would like to get the opinion of this list as it is more of database issue as opposed to apps. The question is about LMT and extent management with regards to Oracle 11i. When upgrading to 11i, it creates "migrated" LMTS as opposed to "uniform/system" ones, and therefore do not conform to the rules of "correct" LMTS. My understanding is, even though my tablespaces are LMT, the tables still act like they are dictionary managed with regards to extent growth. According to "How to stop defrag, and start living ." for tables under 160M I should have an extent size of 160k. With that in mind, should I 1) Create LMT Tablespaces with an extent size of 160k ? ( This is ignored by the import, tables will be one extent big) 2) Reset the "next_extent" on my apps tables to 160k ? 3) Set pctincrease to 0 ? ( I think this is a given ) Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Those Pesky Little Audit Files (ora_99999.aud)
My guess would be that since it is a security risk, it's probably not a good a idea to make it a supported parameter. Jared On Friday 27 December 2002 05:18, Jamadagni, Rajendra wrote: > O Oracle Guru's > Please tell us, why _trace_files_public is *STILL* an underscore > parameter?? > > Raj > __ > > Rajendra Jamadagni MIS, ESPN Inc. > > Rajendra dot Jamadagni at ESPN dot com > > Any opinion expressed here is personal and doesn't reflect that of ESPN > Inc. > > > QOTD: Any clod can have facts, but having an opinion is an art! > > -Original Message- > Sent: Friday, December 27, 2002 2:09 AM > To: Multiple recipients of list ORACLE-L > > > They were put there in 7.1 in order to comply with some security standard. > And their purpose is exactly to prevent a dba from logging in without being > monitored. It's in the 7.1 new features manual, as far as I remember. > That's also the version where it was suddenly not possible for the poor > deveopers to see their own tracefiles, except if they set > _trace_files_public=true. > > Mogens Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Those Pesky Little Audit Files (ora_99999.aud)
Probably because changing it from it's default value of FALSE introduces a potential security hole - trace files may be dumped at any time, and may contain information that is deemed to be confidential. . Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 27 December 2002 13:50 O Oracle Guru's Please tell us, why _trace_files_public is *STILL* an underscore parameter?? Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Deleting some//all the child records from the child tables and th e corresponding records in the parent table(s)
The other day you sent me the query to find the direct and indirect relationship between tables. In thesame way is it possible to delete all/few lower level records based on the column value of the parent table? If so, could you please send me the SQL queries for the same? Please note that I cannot enforce the 'ON DELETE CASCADE' rule on the foreign key constraints. Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED]> (by way of Jared Still <[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
Larry, Don't want to preach to the Guru, but have you checked the values for 'table fetch continued row'? StatisticTotal per Secondper Trans - table fetch by rowid 577,820,727 40,129.2 61,248.8 table fetch continued row 137,202 9.5 14.5 This when coming out of V$SESSTAT could give a good indication of number of fetches by migrated as well as chained rows for that session. You could also look at V$SESSION.MAX_WAIT for 'db file sequential read' events... Let us know what you find! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -Original Message- > From: Larry Elkins [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 26, 2002 4:09 PM > To: Multiple recipients of list ORACLE-L > Subject: Row Migration > > > Listers, > > 8.1.7.4 64 Bit Solaris > > Does row migration utilize DB File Sequential Reads on the > table? Off the > top of my head I would expect so, but I've never tested > something like that > before. > > Trying to figure out if row migration is the cause of the > slowdown in a > package (well, it's probably slowing it down, just trying to gauge the > impact). PctFree is 10, and new feeds contain lots of > elements that had been > empty before. As a result, a very large number of rows are > being updated > with the new info being applied, effectively doubling the row > length. Would > certainly expect row migration to occur. When running, > execution time has > quadrupled, and we see significant waits on DB File > Sequential Reads, with > the file/block values and dba_extents indicating the table, > not an index. > The working idea at this point is that all those DB File > Sequential Read > waits on the table are possibly related to rows being migrated. Anyone > tested for this? > > We will be building a test case on Friday. One with PctFree 10 and the > columns being updated having nulls. Will gather the waits, > before and after > sesstat's, analyze list chained rows, both before and after, > total blocks, > rows per block, etc. Then rebuild the test having a PCTFREE > of 50 and do the > same thing. Some wildcards -- with the blocks less tightly > packed, we will > have to visit nearly double the number of blocks (maybe offset by > migration), contention, and various other things to take into > account. But > the main thing we are focusing in on is if we continue to see > the db file > sequential read waits on the table. I guess the fact that we > are seeing > waits is indicative of some I/O contention, but trying to > determine if, and > how much, of that I/O is due to row migration, in which case a larger > PCTFREE could provide some more immediate relief. No FK/PK > stuff, unique > index is there, but it should resolve uniqueness using the > index, not the > table. Maybe have left some things out. This came up a few > days ago, but > just really started thinking about it and digging into it. And the end > result is we don't want migrated rows, just looking to see if the row > migration is the primary cause of the performance downturn. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Larry Elkins > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
Rachel, Try a pre-created non-unique index. This should remain after the constraint it dropped, and can be used to enforce the primary key constraint (not to mention be created in parallel nologging mode.) hth, Jack > 9.2.0.1 Solaris, and yes, it does drop it > > I created a unique index in the primary key columns > I created the primary key constraint without specifying an index > I checked that the index existed, it did > I dropped the primary key constraint > I checked that the index existed, it didn't > > try it I tried various combinations before posting this note > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > If you build a separate index to enforce the primary key, Oracle > > shouldn't > > drop it when you disable or drop the primary key. > > > > Regards, > > Denny > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > Here's a reason: > > > > > > have you ever tried to find the three duplicate rows in a 12 > > million > > > row table without using the primary key constraint? I've had to > > > disable > > > or drop the constraint in order to use the exceptions table. Once I > > do > > > that, even if I've built a separate index that enforces the primary > > > key > > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > > > allow > > > the index to be rebuilt when I re-enable the primary key > > constraint, > > > it > > > builds it in the default tablespace of the table owner, not where I > > > want it. > > > > > > if anyone has a better way to fix this problem, I'm more than happy > > to > > > hear it! It's a data warehouse and the third party app has a bug we > > > can't find and on occasion sqlloads (via direct path) duplicate > > rows > > > > > > Rachel > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > Though I have published a script for determining indexes that > > > > need to be rebuilt, and then rebuilding them, I have to say that > > > > this is almost never necessary. > > > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > > doing so is that the BLEVEL >= 5. > > > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > > > Currently, the third article may be of interest. > > > > > > > > Jared > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > TIA, > > > > > Rich > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > > 1" > > > > Content-Transfer-Encoding: 7bit > > > > Content-Description: > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Jared Still > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California-- Mailing list and web hosting > > > services > > > > > > > > > - > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > > in > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > (or the name of mailing list you want to be removed from). You > > may > > > > also send the HELP command for other information (like > > subscribing). > > > > > > > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > > http://mailplus.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rachel Carmichael > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > > services > > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like > > subscribing). > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Denny Koovakattu > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru'
RE: Row Migration
We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up. It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message-From: Larry Elkins [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
Re: Versioning the Database !
I generally follow as a practice to keep a variable v_version at package level or any script level . ( i have only packages ) and this v_version is nothing but $header$ in mks . This way I can always run a query to find out object versions in db . This is REALLY helpful specially when code is wrapped in production . -oramagic . Hemant K Chitale <[EMAIL PROTECTED]> wrote: Versioning the database ?Take a backup of the database on a seperate tape each day !What components of the database do you want to version ? Table definitions ?View definitions ? Packages/Procedures/Triggers ?Code Objects should be versioned, but data objects [Tables/Indexes/Sequences]would generally not vary once the design is done, save for a few changes/additions/enhancements.HemantAt 06:28 AM 27-12-02 -0800, you wrote:>Hi,>> I want to version the Database for development, IT, QA and staging>environment.> Can some one suggest different methods and best possible approach to>maintain the database.>> Database is in design stage & development has partially started. We>are using MKS for versioning.>>Regards>Shree>-->Please see the official ORACLE-L ! FAQ: http://www.orafaq.net>-->Author: Rama, Shreekantha (CAP, CARD)> INET: [EMAIL PROTECTED]>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com>San Diego, California -- Mailing list and web hosting services>->To REMOVE yourself from this mailing list, send an E-Mail message>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in>the message BODY, include a line containing: UNSUB ORACLE-L>(or the name of mailing list you want to be removed from). You may>also send the HELP command for other information (like subscribing).Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Hemant K ChitaleINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 h! ttp://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
RE: Those Pesky Little Audit Files (ora_99999.aud)
Title: RE: Those Pesky Little Audit Files (ora_9.aud) Yupp, I do the same thing. I figure if there's a problem documented somewhere in those files and I haven't responded to them in 30-60 days then its too old to worry about anyway. Sometimes OWS wants an alert log which goes back to the beginning of time but I just tell 'em to get real. I just up'd the listerner log renaming routine to run on a daily basis because it was too big too view. Now the average size of the listener.julianday file is 20-50MB. The process of cleaning up oracle log and trace files is like a "pooper-scooper" at the end of the rodeo parade. Steve Orr Bozeman, Montana -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 9:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: Those Pesky Little Audit Files (ora_9.aud) that's what I do Kevin. I have a cron job that cleans up all of the Oracle log files. These audit files, Listener logs, Alert Logs, Trace files etc. I run it twice a month, deleting anything that is 30 days or older. rename alert logs and listener logs, rman's sbtio.log file so that they will be deleted by a later run. works for me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 27, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Thanks. Guess its clean-up job time. -Original Message- Sent: Thursday, December 26, 2002 7:59 PM To: Multiple recipients of list ORACLE-L IIRC, these files are generated whenever someone logs in as sysdba or internal. I don't know of any way to stop them. --- Kevin Lange <[EMAIL PROTECTED]> wrote: > I thought I had these files stopped but apparently not. > > Is there somone out there who can tell me how to stop the Audit files > from > appearing in the audit_file_dest ??? I thought if I set the > audit_trail to > false then these would stop as well ... Apparently not. > > Anyone have an idea how to turn them off ?? > > Thanks > > Kevin > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kevin Lange > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
Yeah, it sure does, if the index is unique. Try out this test: drop table y; create table y(y number); create unique index ypkidx on y(y); alter table y add constraint ypk primary key(y); alter table y drop primary key; select table_name, index_name from user_indexes where index_name = 'YPKIDX' / drop table y; create table y(y number); create index ypkidx on y(y); alter table y add constraint ypk primary key(y); alter table y drop primary key; select table_name, index_name from user_indexes where index_name = 'YPKIDX' / Notice that the non unique index will still be available, as Jack has already pointed out. Don't know why the behaviors are different. Jared On Friday 27 December 2002 08:14, Denny Koovakattu wrote: > If you build a separate index to enforce the primary key, Oracle > shouldn't drop it when you disable or drop the primary key. > > Regards, > Denny > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 million > > row table without using the primary key constraint? I've had to > > disable > > or drop the constraint in order to use the exceptions table. Once I do > > that, even if I've built a separate index that enforces the primary > > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > > allow > > the index to be rebuilt when I re-enable the primary key constraint, > > it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > > > > services > > > > - > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
Rick, You're not considering a PK built with 'enable novalidate'. Jared On Friday 27 December 2002 05:38, [EMAIL PROTECTED] wrote: > If you know you have 3 duplicate records in the table then the PK must have > already been disabled so you have to rebuild anyway. I do not see > where you had to disable in order to use the exception table. It was > already disabled therefore it probably not an app problem but a disable > constraint > problem unless direct load bypasses constraint checking which I am not > sure. > > Rick > > > > > Rachel > Carmichael To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> ahoo.com>Subject: Re: Rebuilding > Indexes... Sent by: > [EMAIL PROTECTED] > om > > > 12/27/2002 > 07:43 AM > Please respond > to ORACLE-L > > > > > > > Here's a reason: > > have you ever tried to find the three duplicate rows in a 12 million > row table without using the primary key constraint? I've had to disable > or drop the constraint in order to use the exceptions table. Once I do > that, even if I've built a separate index that enforces the primary key > constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow > the index to be rebuilt when I re-enable the primary key constraint, it > builds it in the default tablespace of the table owner, not where I > want it. > > if anyone has a better way to fix this problem, I'm more than happy to > hear it! It's a data warehouse and the third party app has a bug we > can't find and on occasion sqlloads (via direct path) duplicate rows > > Rachel > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > 9.2.0.1 Solaris, and yes, it does drop it > > I created a unique index in the primary key columns > I created the primary key constraint without specifying an index > I checked that the index existed, it did > I dropped the primary key constraint > I checked that the index existed, it didn't > > try it I tried various combinations before posting this note > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > If you build a separate index to enforce the primary key, Oracle > > shouldn't > > drop it when you disable or drop the primary key. > > > > Regards, > > Denny > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > Here's a reason: > > > > > > have you ever tried to find the three duplicate rows in a 12 > > million > > > row table without using the primary key constraint? I've had to > > > disable > > > or drop the constraint in order to use the exceptions table. Once > I > > do > > > that, even if I've built a separate index that enforces the > primary > > > key > > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > > > allow > > > the index to be rebuilt when I re-enable the primary key > > constraint, > > > it > > > builds it in the default tablespace of the table owner, not where > I > > > want it. > > > > > > if anyone has a better way to fix this problem, I'm more than > happy > > to > > > hear it! It's a data warehouse and the third party app has a bug > we > > > can't find and on occasion sqlloads (via direct path) duplicate > > rows > > > > > > Rachel > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > Though I have published a script for determining indexes that > > > > need to be rebuilt, and then rebuilding them, I have to say > that > > > > this is almost never necessary. > > > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > > doing so is that the BLEVEL >= 5. > > > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > > > Currently, the third article may be of interest. > > > > > > > > Jared > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > TIA, > > > > > Rich > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > > 1" > > > > Content-Transfer-Encoding: 7bit > > > > Content-Description: > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Jared Still > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California-- Mailing list and web hosting > > > services > > > > > > > > > > - > > > > To REMOVE yourself from this mailing list, send an E-Mail > message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > > in > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > (or the name of mailing list you want to be removed from). You > > may > > > > also send the HELP command for other information (like > > subscribing). > > > > > > > > > > > > > __ > > > Do you Yahoo!? > > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > > http://mailplus.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rachel Carmichael > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > > services > > > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > > subscribing). > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Denny Koovakattu > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMO
Re: Rebuilding Indexes...
Yes, but that's a special case. You are not rebuilding the index as part of some regular index maintenance. Jared On Friday 27 December 2002 04:43, Rachel Carmichael wrote: > Here's a reason: > > have you ever tried to find the three duplicate rows in a 12 million > row table without using the primary key constraint? I've had to disable > or drop the constraint in order to use the exceptions table. Once I do > that, even if I've built a separate index that enforces the primary key > constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow > the index to be rebuilt when I re-enable the primary key constraint, it > builds it in the default tablespace of the table owner, not where I > want it. > > if anyone has a better way to fix this problem, I'm more than happy to > hear it! It's a data warehouse and the third party app has a bug we > can't find and on occasion sqlloads (via direct path) duplicate rows > > Rachel > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Those Pesky Little Audit Files (ora_99999.aud)
I have 71620 for DG/UX ... tell me what to look for ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Those Pesky Little Audit Files (ora_9.aud)Yeah, it's a nuisance in most installations, but the idea is to be compliant with some security standard. Give me a 7.1 doc site (if it exists) and I'll find the details. I failed to find 7.1 doc on Google searches. Probably too much beer.MogensJamadagni, Rajendra wrote: O Oracle Guru's Please tell us, why _trace_files_public is *STILL* an underscore parameter?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Those Pesky Little Audit Files (ora_9.aud)They were put there in 7.1 in order to comply with some security standard. And their purpose is exactly to prevent a dba from logging in without being monitored. It's in the 7.1 new features manual, as far as I remember. That's also the version where it was suddenly not possible for the poor deveopers to see their own tracefiles, except if they set _trace_files_public=true.Mogens *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Those Pesky Little Audit Files (ora_99999.aud)
Yeah, it's a nuisance in most installations, but the idea is to be compliant with some security standard. Give me a 7.1 doc site (if it exists) and I'll find the details. I failed to find 7.1 doc on Google searches. Probably too much beer. Mogens Jamadagni, Rajendra wrote: O Oracle Guru's Please tell us, why _trace_files_public is *STILL* an underscore parameter?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mogens Nørgaard[mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 2:09AM To: Multiple recipients of list ORACLE-L Subject: Re:Those Pesky Little Audit Files (ora_9.aud) They wereput there in 7.1 in order to comply with some security standard. And theirpurpose is exactly to prevent a dba from logging in without being monitored.It's in the 7.1 new features manual, as far as I remember. That's alsothe version where it was suddenly not possible for the poor deveopers to seetheir own tracefiles, except if they set_trace_files_public=true. Mogens *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Rebuilding Indexes...
9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > If you build a separate index to enforce the primary key, Oracle > shouldn't > drop it when you disable or drop the primary key. > > Regards, > Denny > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > > allow > > the index to be rebuilt when I re-enable the primary key > constraint, > > it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > > services > > > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Denny Koovakattu > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Servi
Re: PMON seems to not close sessions in a timely manner eg "Max
And finally, although I hate asking the question: Why are you running MTS in the first place? I'm not saying there aren't good reasons for it - I'm just curious. Or to be "funny": I've solved many MTS-problems in my time by turning it off. However, that might not be possible or sensible in all cases. Mogens Khedr, Waleed wrote: Try: 1) Force shared connections using (SRVR=SHARED) in the tnsnames.ora. 2) Change the service name for the MTS_service and restart te db and listener. Make sure the service is registered with the listener. Add a new entry pointing to the new service in tnsnames.ora and let you app use this entry. I think the entry in tnsnames.ora will need to be like (service_name = ) instead of (sid = ). 3) sqlnet tracing may need to be done. Regards, Waleed -Original Message- Sent: Friday, December 27, 2002 8:49 AM To: Multiple recipients of list ORACLE-L What do you get when run this on the server hosting Oracle: lsnrctl services Waleed, thanks for your input. Here is what I have (below are my MTS settings) MYDB has 1 service handler(s) DEDICATED SERVER established:259 refused:1 LOCAL SERVER MYDB has 6 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DISPATCHER established:2 refused:0 current:2 max:4000 state:ready D004 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276)) DISPATCHER established:4 refused:0 current:0 max:4000 state:ready D003 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D002 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D001 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273)) DISPATCHER established:1 refused:0 current:0 max:4000 state:ready D000 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272)) DB2 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DB3 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER ### MTS_DISPATCHERS="(protocol=tcp)(dispatchers=5)(pool=on)(tick=1) (connections=1000)(sessions=4000)" MTS_MAX_DISPATCHERS=32 MTS_SERVERS=5 MTS_MAX_SERVERS=64 MTS_SERVICE=MYDB MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))"
Re: Rebuilding Indexes...
If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > Here's a reason: > > have you ever tried to find the three duplicate rows in a 12 million > row table without using the primary key constraint? I've had to > disable > or drop the constraint in order to use the exceptions table. Once I do > that, even if I've built a separate index that enforces the primary > key > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > the index to be rebuilt when I re-enable the primary key constraint, > it > builds it in the default tablespace of the table owner, not where I > want it. > > if anyone has a better way to fix this problem, I'm more than happy to > hear it! It's a data warehouse and the third party app has a bug we > can't find and on occasion sqlloads (via direct path) duplicate rows > > Rachel > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Those Pesky Little Audit Files (ora_99999.aud)
that's what I do Kevin. I have a cron job that cleans up all of the Oracle log files. These audit files, Listener logs, Alert Logs, Trace files etc. I run it twice a month, deleting anything that is 30 days or older. rename alert logs and listener logs, rman's sbtio.log file so that they will be deleted by a later run. works for me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 27, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Thanks. Guess its clean-up job time. -Original Message- Sent: Thursday, December 26, 2002 7:59 PM To: Multiple recipients of list ORACLE-L IIRC, these files are generated whenever someone logs in as sysdba or internal. I don't know of any way to stop them. --- Kevin Lange <[EMAIL PROTECTED]> wrote: > I thought I had these files stopped but apparently not. > > Is there somone out there who can tell me how to stop the Audit files > from > appearing in the audit_file_dest ??? I thought if I set the > audit_trail to > false then these would stop as well ... Apparently not. > > Anyone have an idea how to turn them off ?? > > Thanks > > Kevin > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kevin Lange > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PMON seems to not close sessions in a timely manner eg "Max
Title: Message Tehe, don't worry, Bob, the developers here work for me, so I can be as un-diplomatic as I wanna be. I don't know how you would do it in Micro$oft; perhaps some kind of component (.NET? DCOM?) could do this for them. I can do it in Java and Perl. Can't imagine that there isn't some mechanism out there for ASP/M$. -Original Message- From: Bob Metelsky [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 8:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: PMON seems to not close sessions in a timely manner eg "Max > From: Bob Metelsky [mailto:[EMAIL PROTECTED]] > > > I have a developers stored procedure making repeaded calls > (logons/request for data and logoff) > Each call takes only seconds but it seems like PMON dosnt close the > process in a timely manner, leaving what look to be ghost > process. If a > few users hit the same app the processes go to = 50 then I get the > dreaded "ORA-nnn max processes (50) reached" 1. 50 max processes? Damn, dude, you're choking that thing to death. 2. That is one of the sorriest excuses for a data access methodology that exists. Tell them to start pooling their database connections or you'll turn your max processes down even further. Hummm I have to be more diplomatic than that .. ;-) They are using the Oracle 8.17 provider with a connection string like so Connect=Provider=OraOLEDB.Oracle;PLSQLRSet=1;Password=a;Persist Security Info=True;User ID=a;Data Source=mydb Jeremy, do you have any examples of using pooled connections with active server pages? I'd like to be able to at least point them in the right direction and also have the information myself many thanks Bob
Re: join after saving rowid Value into a Field - For Design , Dev. Gurus
Developers can also use the approach that Oracle uses with UROWID values, which are stored in secondary indexes on IOTs (i.e. replacing ROWIDs used in "normal" indexes). Store the ROWID as well as the PK/UK column values. Use the following algorithm to retrieve in future: 1. Retrieve the PK/UK values by ROWID 2. Compare retrieved PK/UK values to those stored 3. If PK/UK values do not match, then retrieve again by PK/UK The upsides and downsides should be pretty obvious, but it is certainly "safe"... > > You can use the rowid but do not keep it. > As a dev DBA I would not allow to store the rowid in a > table because its value is meaningless once you > export/import, ... > > > --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > > let us suppose there are two tables M and P. > > both Contain the field emp_id. other columns may be > > different. > > > > > > All records of M also Exist in P .Table M will have > > records in the range > > 1-5 lakhs. > > P table will contain Additional Records such that > > the Total Number of > > Records in P is 15-20 times the number of records in > > M. > > > > one way to join the two tables is to say M.emp_id > > P.emp_id. but > > because P has high number of records the select is > > slower. > > > > we found that select of a row from table "P" using > > "rowid" column was > > very QUICK . > > > > Is it a Correct practice :- > > > > 1) to Store the ROWID of Table P in M in a separate > > column (say > > "P_rowid") > > > > 2) Is it possible to do a Join like the follows :- > > > > select field1, field2,... from M,P > > where M.emd_id > > and > > > this way we hoped to select from M table (which has > > less number of > > records) and do a rowid based select on P table, > > which we found out is > > not allowed by ORACLE. > > > > we want a join because we want to create a view over > > table M and P. > > > > We do NOT want to use the following way :- > > > > select field1, field2 ..,P_rowid from M where emp_id > > > > Cursor & passing it to the Query as follows :- > > > > select * from P where rowid > > earlier) > > > > Are there Some Standard Practices that Should be > > Followed during > > Designing Tables , Fields, SQL Writing ? > > > > Any Dos , Don'ts ? > > > > > > > Stéphane Paquette > DBA Oracle et DB2, consultant entrepôt de données > Oracle and DB2 DBA, datawarehouse consultant > [EMAIL PROTECTED] > > __ > Lèche-vitrine ou lèche-écran ? > magasinage.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com San Diego, California-- > Mailing list and web hosting services > -- > --- To REMOVE yourself from this mailing list, > send an E-Mail message to: [EMAIL PROTECTED] (note > EXACT spelling of 'ListGuru') and in the message BODY, > include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also > send the HELP command for other information (like > subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle.exe , CPU running at near 85%
Hi, Oracle 8.1.6 on NT 4.0 Oracle.exe is running at about 85% CPU utilization. What can I check to see why that is the case? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Those Pesky Little Audit Files (ora_99999.aud)
Thanks. Guess its clean-up job time. -Original Message- Sent: Thursday, December 26, 2002 7:59 PM To: Multiple recipients of list ORACLE-L IIRC, these files are generated whenever someone logs in as sysdba or internal. I don't know of any way to stop them. --- Kevin Lange <[EMAIL PROTECTED]> wrote: > I thought I had these files stopped but apparently not. > > Is there somone out there who can tell me how to stop the Audit files > from > appearing in the audit_file_dest ??? I thought if I set the > audit_trail to > false then these would stop as well ... Apparently not. > > Anyone have an idea how to turn them off ?? > > Thanks > > Kevin > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kevin Lange > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Versioning the Database !
Versioning the database ? Take a backup of the database on a seperate tape each day ! What components of the database do you want to version ? Table definitions ? View definitions ? Packages/Procedures/Triggers ? Code Objects should be versioned, but data objects [Tables/Indexes/Sequences] would generally not vary once the design is done, save for a few changes/additions/enhancements. Hemant At 06:28 AM 27-12-02 -0800, you wrote: Hi, I want to version the Database for development, IT, QA and staging environment. Can some one suggest different methods and best possible approach to maintain the database. Database is in design stage & development has partially started. We are using MKS for versioning. Regards Shree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (CAP, CARD) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PMON seems to not close sessions in a timely manner eg "Max
Try: 1) Force shared connections using (SRVR=SHARED) in the tnsnames.ora. 2) Change the service name for the MTS_service and restart te db and listener. Make sure the service is registered with the listener. Add a new entry pointing to the new service in tnsnames.ora and let you app use this entry. I think the entry in tnsnames.ora will need to be like (service_name = ) instead of (sid = ). 3) sqlnet tracing may need to be done. Regards, Waleed -Original Message- Sent: Friday, December 27, 2002 8:49 AM To: Multiple recipients of list ORACLE-L > > What do you get when run this on the server hosting Oracle: > > lsnrctl services > > Waleed, thanks for your input. Here is what I have (below are my MTS settings) MYDB has 1 service handler(s) DEDICATED SERVER established:259 refused:1 LOCAL SERVER MYDB has 6 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DISPATCHER established:2 refused:0 current:2 max:4000 state:ready D004 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276)) DISPATCHER established:4 refused:0 current:0 max:4000 state:ready D003 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D002 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D001 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273)) DISPATCHER established:1 refused:0 current:0 max:4000 state:ready D000 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272)) DB2 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DB3 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER ### MTS_DISPATCHERS="(protocol=tcp)(dispatchers=5)(pool=on)(tick=1) (connections=1000)(sessions=4000)" MTS_MAX_DISPATCHERS=32 MTS_SERVERS=5 MTS_MAX_SERVERS=64 MTS_SERVICE=MYDB MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))" -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: compile errors
David, If the package is not too large, could you please show it (or the portions of the package that are involved in the error) to us on the list so we can see exactly what is going on? We need to see where and how the object is being referenced. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 27, 2002 9:49 AM To: Multiple recipients of list ORACLE-L You are right. I disabled the roles thru which the grants were made. But my schema owns the objects. I have a userprivs script that shows my schema owners privs. The schema owner does not have any privs on the objects it owns! How can that be? And I tried granting to myself(the schema owner), as you know you can't do that. What is the work around? thanks David Ehresmann. -Original Message- Sent: Thursday, December 26, 2002 7:19 PM To: Multiple recipients of list ORACLE-L Does the owner of the package have *direct* privs (not through a role) on the object in question? > List, > > Can anybody tell me what is happening here. I am constantly getting the > PLS-00201 error when I try to compile. It is looking inside the package at > a procedure and saying the identifier must be declared. I have gone over > metalink docs and notes. I first compile the package spec and then the > package body. I get the following response: > > SQL> alter package schema.p_messages compile package; > > Warning: Package altered with compilation errors. > > SQL> show errors > Errors for PACKAGE schema.P_MESSAGES: > > LINE/COL ERROR > > - > > 193/5PL/SQL: Declaration ignored > > 197/38 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared > > 218/5PL/SQL: Declaration ignored > > 219/34 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared > > > > SQL> alter package schema.p_messages compile body; > > Warning: Package Body altered with compilation errors. > > SQL> show errors > Errors for PACKAGE BODY schema.P_MESSAGES: > > LINE/COL ERROR > > - > > 0/0 PL/SQL: Compilation unit analysis terminated > > 1/14 PLS-00905: object schema.P_MESSAGES is invalid > > 1/14 PLS-00304: cannot compile body of 'P_MESSAGES' without its > > specification > > > > thanks, > > David Ehresmann > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ehresmann, David > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (o
Re: join after saving rowid Value into a Field - For Design , Dev. Gurus
You can use the rowid but do not keep it. As a dev DBA I would not allow to store the rowid in a table because its value is meaningless once you export/import, ... --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > let us suppose there are two tables M and P. > both Contain the field emp_id. other columns may be > different. > > > All records of M also Exist in P .Table M will have > records in the range > 1-5 lakhs. > P table will contain Additional Records such that > the Total Number of > Records in P is 15-20 times the number of records in > M. > > one way to join the two tables is to say M.emp_id = > P.emp_id. but > because P has high number of records the select is > slower. > > we found that select of a row from table "P" using > "rowid" column was > very QUICK . > > Is it a Correct practice :- > > 1) to Store the ROWID of Table P in M in a separate > column (say > "P_rowid") > > 2) Is it possible to do a Join like the follows :- > > select field1, field2,... from M,P > where M.emd_id = '6223' > and > > this way we hoped to select from M table (which has > less number of > records) and do a rowid based select on P table, > which we found out is > not allowed by ORACLE. > > we want a join because we want to create a view over > table M and P. > > We do NOT want to use the following way :- > > select field1, field2 ..,P_rowid from M where emp_id > = '6223' from a > Cursor & passing it to the Query as follows :- > > select * from P where rowid = P_rowid (selected > earlier) > > Are there Some Standard Practices that Should be > Followed during > Designing Tables , Fields, SQL Writing ? > > Any Dos , Don'ts ? > > = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rebuilding Indexes...
Title: Rebuilding Indexes... Thanks for the responses from all the great minds on this list! :) -Original Message-From: Richard Huntley [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26, 2002 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Rebuilding Indexes... Anyone have any useful scripts for doing this? TIA, Rich
RE: compile errors
You are right. I disabled the roles thru which the grants were made. But my schema owns the objects. I have a userprivs script that shows my schema owners privs. The schema owner does not have any privs on the objects it owns! How can that be? And I tried granting to myself(the schema owner), as you know you can't do that. What is the work around? thanks David Ehresmann. -Original Message- Sent: Thursday, December 26, 2002 7:19 PM To: Multiple recipients of list ORACLE-L Does the owner of the package have *direct* privs (not through a role) on the object in question? > List, > > Can anybody tell me what is happening here. I am constantly getting the > PLS-00201 error when I try to compile. It is looking inside the package at > a procedure and saying the identifier must be declared. I have gone over > metalink docs and notes. I first compile the package spec and then the > package body. I get the following response: > > SQL> alter package schema.p_messages compile package; > > Warning: Package altered with compilation errors. > > SQL> show errors > Errors for PACKAGE schema.P_MESSAGES: > > LINE/COL ERROR > > - > > 193/5PL/SQL: Declaration ignored > > 197/38 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared > > 218/5PL/SQL: Declaration ignored > > 219/34 PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be declared > > > > SQL> alter package schema.p_messages compile body; > > Warning: Package Body altered with compilation errors. > > SQL> show errors > Errors for PACKAGE BODY schema.P_MESSAGES: > > LINE/COL ERROR > > - > > 0/0 PL/SQL: Compilation unit analysis terminated > > 1/14 PLS-00905: object schema.P_MESSAGES is invalid > > 1/14 PLS-00304: cannot compile body of 'P_MESSAGES' without its > > specification > > > > thanks, > > David Ehresmann > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ehresmann, David > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automatic backup on Oracle 9i
If this is rman backup, perhaps try granting sysdba to sys, or connecting to target as sysdba? > -Original Message- > From: Sony kristanto [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 26, 2002 7:24 PM > To: Multiple recipients of list ORACLE-L > Subject: Automatic backup on Oracle 9i > > > Hi Listers, > I'm new on Oracle Database 9i after I migrated from Oracle 8i. > I try to use backup facility from Oracle 9i and I already follow the > instructions how to activate the automatic backup but when I > see the status > on history I get an error comment 'Failed'. I've try again > and again but the > results are the same. Could someone out there tell me why it > can't runs. For > your note I use 'SYS' as my user. I will really appreciate your help. > > Rgrds, > > Sony > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Sony kristanto > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Versioning the Database !
Hi, I want to version the Database for development, IT, QA and staging environment. Can some one suggest different methods and best possible approach to maintain the database. Database is in design stage & development has partially started. We are using MKS for versioning. Regards Shree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (CAP, CARD) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
Hey Rachel, Consider using a non-unique index for your primary key constraint. If you prebuild it and then add the constraint, Oracle will not drop the index when you drop the PK constraint, and you can control the index build that a way (and build it in parallel to boot). hth, Jack > Here's a reason: > > have you ever tried to find the three duplicate rows in a 12 million > row table without using the primary key constraint? I've had to disable > or drop the constraint in order to use the exceptions table. Once I do > that, even if I've built a separate index that enforces the primary key > constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow > the index to be rebuilt when I re-enable the primary key constraint, it > builds it in the default tablespace of the table owner, not where I > want it. > > if anyone has a better way to fix this problem, I'm more than happy to > hear it! It's a data warehouse and the third party app has a bug we > can't find and on occasion sqlloads (via direct path) duplicate rows > > Rachel > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PMON seems to not close sessions in a timely manner eg "Max
> > What do you get when run this on the server hosting Oracle: > > lsnrctl services > > Waleed, thanks for your input. Here is what I have (below are my MTS settings) MYDB has 1 service handler(s) DEDICATED SERVER established:259 refused:1 LOCAL SERVER MYDB has 6 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DISPATCHER established:2 refused:0 current:2 max:4000 state:ready D004 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276)) DISPATCHER established:4 refused:0 current:0 max:4000 state:ready D003 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D002 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274)) DISPATCHER established:1 refused:0 current:1 max:4000 state:ready D001 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273)) DISPATCHER established:1 refused:0 current:0 max:4000 state:ready D000 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272)) DB2 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER DB3 has 1 service handler(s) DEDICATED SERVER established:0 refused:0 LOCAL SERVER ### MTS_DISPATCHERS="(protocol=tcp)(dispatchers=5)(pool=on)(tick=1) (connections=1000)(sessions=4000)" MTS_MAX_DISPATCHERS=32 MTS_SERVERS=5 MTS_MAX_SERVERS=64 MTS_SERVICE=MYDB MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))" > > Waleed, thanks for the reply here is what I have > > I have 26 sessions and 34 process, only one of which (my sqlplus > session) that seems to indicate shared > The rest seem to be remanents of the stored procedure. > > At the point of running this test, it was only myself > monitoring and the developer repeadatly running his app which > never ran the processses > 37 in about 15 min > > How else can I analyse this? > > Im wondering why only one session is in shared mode and the > remainder are dedicated? And what mandates a shared > connection over a dedicated? > > My sql+ which initiated the shared session was simply > username/passwd@instance > > Thanks > bob > > LOCALUSER@MYDB -> select count(*) from v$session; > > COUNT(*) > -- > 26 > > LOCALUSER@MYDB -> select count(*) from v$process; > > COUNT(*) > -- > 37 > > LOCALUSER@MYDB -> select > decode(username,'SECUSER','LOCALUSER',NULL,'IS_NULL', > username), status, > program, server from v$session; > > DECODE(US STATUS PROGRAM > SERVER > - > > - > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > IS_NULL ACTIVE ORACLE.EXE > DEDICATED > username INACTIVE dbsnmp.exe > DEDICATED > LOCALUSER INACTIVE sqlplusw.exe > NONE > LOCALUSER INACTIVE sqlplusw.exe > NONE > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > NONE > LOCALUSER ACTIVE sqlplusw.exe > SHARED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > LOCALUSER INACTIVE > DEDICATED > > 26 rows selected. > > > > Do you know if the connections (sessions) created for the web > > app are shared or dedicated? If it's dedicated, did you try > > to connect using sqlplus from that win2k server and other hosts? > > > > Trying to isolate the problem? > > > > Regards, > > > > Waleed > > > > > > -Original Message- > > Sent: Thursday, December 26, 2002 10:44 AM > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Does your app connect to the database using the listener > or directly > > > (BEQ) without specifying a connect string? > > > > > > It should connect using the listener and a connect string. > > > > > > Regards, > > > > > > Waleed > > > > > > > Waleed > > > > Yes, it's a web application and uses a listener. The listener > > entry has not been modified. There was a vague reference in > > the MTS docs that eluded to using a special connection > > string. But I got the impression that you would use a special > > connection string *if* you wanted the abillity to chooose > > between direct connection or mts (shared). > > > > In my case Id simply like to have all connections to use MTS. > > > > Should I be using a special connection string or listener > > entry? If
Re: Rebuilding Indexes...
If you know you have 3 duplicate records in the table then the PK must have already been disabled so you have to rebuild anyway. I do not see where you had to disable in order to use the exception table. It was already disabled therefore it probably not an app problem but a disable constraint problem unless direct load bypasses constraint checking which I am not sure. Rick Rachel Carmichael To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Rebuilding Indexes... Sent by: [EMAIL PROTECTED] om 12/27/2002 07:43 AM Please respond to ORACLE-L Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still <[EMAIL PROTECTED]> wrote: > > Though I have published a script for determining indexes that > need to be rebuilt, and then rebuilding them, I have to say that > this is almost never necessary. > > Why are you rebuilding indexes? About the only reason for ever > doing so is that the BLEVEL >= 5. > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > Currently, the third article may be of interest. > > Jared > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > Anyone have any useful scripts for doing this? > > > > TIA, > > Rich > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you w
RE: Automatic backup on Oracle 9i
Title: RE: Automatic backup on Oracle 9i To me "Automatic Backup" means the backup jobs/scripts written by resident script kiddies (AKA Unix Admins). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 10:34 PM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic backup on Oracle 9i Not familiar with this. Is this something in Oracle Enterprise Manager, perhaps the "backup manager" forms? This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Those Pesky Little Audit Files (ora_99999.aud)
O Oracle Guru's Please tell us, why _trace_files_public is *STILL* an underscore parameter?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Those Pesky Little Audit Files (ora_9.aud)They were put there in 7.1 in order to comply with some security standard. And their purpose is exactly to prevent a dba from logging in without being monitored. It's in the 7.1 new features manual, as far as I remember. That's also the version where it was suddenly not possible for the poor deveopers to see their own tracefiles, except if they set _trace_files_public=true.Mogens *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Those Pesky Little Audit Files (ora_99999.aud)
They don't do a great job of monitoring as all they record is the fact that someone logged in. But then the other auditing Oracle does (or did in earlier versions, I haven't investigated it in 9i) didn't capture much information either. Since we used to automate, via cron, some of the startup/shutdown and DBA functions, and we created an account within the database for the oracle Unix account... the aud trace files never told us much of anything. I don't want to know only that someone tried to get in, I want to now how he/she tried --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > They were put there in 7.1 in order to comply with some security > standard. And their purpose is exactly to prevent a dba from logging > in > without being monitored. It's in the 7.1 new features manual, as far > as > I remember. That's also the version where it was suddenly not > possible > for the poor deveopers to see their own tracefiles, except if they > set > _trace_files_public=true. > > Mogens > > Rachel Carmichael wrote: > > >IIRC, these files are generated whenever someone logs in as sysdba > or > >internal. I don't know of any way to stop them. > > > > > >--- Kevin Lange <[EMAIL PROTECTED]> wrote: > > > > > >>I thought I had these files stopped but apparently not. > >> > >>Is there somone out there who can tell me how to stop the Audit > files > >>from > >>appearing in the audit_file_dest ??? I thought if I set the > >>audit_trail to > >>false then these would stop as well ... Apparently not. > >> > >>Anyone have an idea how to turn them off ?? > >> > >>Thanks > >> > >>Kevin > >>-- > >>Please see the official ORACLE-L FAQ: http://www.orafaq.net > >>-- > >>Author: Kevin Lange > >> INET: [EMAIL PROTECTED] > >> > >>Fat City Network Services-- 858-538-5051 http://www.fatcity.com > >>San Diego, California-- Mailing list and web hosting > services > >>- > >>To REMOVE yourself from this mailing list, send an E-Mail message > >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >>the message BODY, include a line containing: UNSUB ORACLE-L > >>(or the name of mailing list you want to be removed from). You may > >>also send the HELP command for other information (like > subscribing). > >> > >> > >> > > > > > >__ > >Do you Yahoo!? > >Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > >http://mailplus.yahoo.com > > > > > > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still <[EMAIL PROTECTED]> wrote: > > Though I have published a script for determining indexes that > need to be rebuilt, and then rebuilding them, I have to say that > this is almost never necessary. > > Why are you rebuilding indexes? About the only reason for ever > doing so is that the BLEVEL >= 5. > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > Currently, the third article may be of interest. > > Jared > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > Anyone have any useful scripts for doing this? > > > > TIA, > > Rich > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache on sysdate? --From 9i performance planning manual
Title: RE: Cache on sysdate? --From 9i performance planning manual Thanks Raj. That's very cool. Now I can do: SQL> delete from dual; 1 row deleted. SQL> declare 2 a date :=sysdate; 3 begin 4 dbms_output.put_line(to_char(a,'MMDD HH24:MI:SS')); 5* end; 20021227 05:36:54 PL/SQL procedure successfully completed. That further proves it no longer uses "select sysdate into a from dual;". Richard Ji -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26, 2002 5:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Cache on sysdate? --From 9i performance planning manual Richard, If you look in the tracefile ... there is no select from dual. It used to be like that but things changed (as Anjo mentions maybe be around 8iR3). The sysdate call is now a C function call. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Richard Ji [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cache on sysdate? --From 9i performance planning manual That's because doing dt := sysdate is more optimized, it's still select sysdate into dt from dual but more optimized. select from dual doing 4 db block gets in 8i and 2 db block gets in 9i, but you can tune it to cut it down. Richard Ji -Original Message- Sent: Thursday, December 26, 2002 2:45 PM To: Multiple recipients of list ORACLE-L That's what I thought too. But the results of testing are somewhat different. Maybe it's evaluated within the PL/SQL engine and does not require a context switch to the SQL engine. Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) DEV:43#14739-23049>@dual1 DEV:43#14739-23049>declare 2 dt date; 3 begin 4 5 for i in 1..1 6 loop 7 select sysdate into dt from dual; 8 -- dt := sysdate; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 DEV:43#14739-23049>@dual1 DEV:43#14739-23049>declare 2 dt date; 3 begin 4 5 for i in 1..1 6 loop 7 -- select sysdate into dt from dual; 8 dt := sysdate; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.92 Regards, Denny Quoting K Gopalakrishnan <[EMAIL PROTECTED]>: > Raj: > > Both are same. It is internally translated as a select call to dual. > > KG > > --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > > Can someone please explain me why you have to use > > > > select sysdate > > from dual > > / > > > > when > > > > my_date_Var := sysdate; > > > > just works fine? Maybe I am clueless ... but I can take an > > explanation > > > > Raj > > = > Have a nice day !! > > Best Regards, > K Gopalakrishnan, > Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row Migration
Row migration means extra IO's. If IO is taking up any significant part of your response time, then you don't want extra IO, of course. And the IO will be single-block IO (sequential reads) because a stub is left in the originating block pointing to the new block where the row migrates to - and that requires a single-block IO. Your test with pctfree 10, then collect stats, etc., then repeat the test with pctfree 50 sounds fine to me. Good luck. Mogens Anand Kumar N wrote: yes, row migration will degrade the performance.. - Original Message - From: Larry Elkins To: Multiple recipients of list ORACLE-L Sent: Friday, December 27, 2002 5:38AM Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does rowmigration utilize DB File Sequential Reads on the table? Off the top of myhead I would expect so, but I've never tested something like that before. Trying to figure out if row migration is the cause ofthe slowdown in a package (well, it's probably slowing it down, just tryingto gauge the impact). PctFree is 10, and new feeds contain lots of elementsthat had been empty before. As a result, a very large number of rows arebeing updated with the new info being applied, effectively doubling the rowlength. Would certainly expect row migration to occur. When running,execution time has quadrupled, and we see significant waits on DB FileSequential Reads, with the file/block values and dba_extents indicating thetable, not an index. The working idea at this point is that all those DBFile Sequential Read waits on the table are possibly related to rows beingmigrated. Anyone tested for this? We will be building a test case onFriday. One with PctFree 10 and the columns being updated having nulls.Will gather the waits, before and after sesstat's, analyze list chainedrows, both before and after, total blocks, rows per block, etc. Thenrebuild the test having a PCTFREE of 50 and do the same thing. Somewildcards -- with the blocks less tightly packed, we will have to visitnearly double the number of blocks (maybe offset by migration), contention,and various other things to take into account. But the main thing we arefocusing in on is if we continue to see the db file sequential read waitson the table. I guess the fact that we are seeing waits is indicative ofsome I/O contention, but trying to determine if, and how much, of that I/Ois due to row migration, in which case a larger PCTFREE could provide somemore immediate relief. No FK/PK stuff, unique index is there, but it shouldresolve uniqueness using the index, not the table. Maybe have left somethings out. This came up a few days ago, but just really started thinkingabout it and digging into it. And the end result is we don't want migratedrows, just looking to see if the row migration is the primary cause of theperformance downturn. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LarryElkins INET: [EMAIL PROTECTED] Fat City NetworkServices -- 858-538-5051 http://www.fatcity.com San Diego,California -- Mailing list and webhostingservices - ToREMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACTspelling of 'ListGuru') and in the message BODY, include a line containing:UNSUB ORACLE-L (or the name of mailing list you want to be removedfrom). You may also send the HELP command for other information (likesubscribing).
RE: Row Migration
Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
RE: Script to recreate schema
Yes. And bvi for binary files. -Original Message- Sent: Thursday, December 26, 2002 2:04 AM To: Multiple recipients of list ORACLE-L exp userid=system/manager file=schema.dmp rows=n owner=scott vi schema.dmp really. Jared On Wednesday 25 December 2002 09:53, Andrey Bronfin wrote: > Dear gurus ! > I'm sure many of you have scripts to recreate an Oracle schema including > objects (i am interested in tables, indexes , comments, views, sequences, > triggers, stored procs/functions etc..) > Would you please share. > Many thanks in advance ! > > Merry X-mas and Happy New Year to you all ! Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row Migration
Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry > Elkins > Sent: Thursday, December 26, 2002 6:09 PM > To: Multiple recipients of list ORACLE-L > Subject: Row Migration > > > Listers, > > 8.1.7.4 64 Bit Solaris > > Does row migration utilize DB File Sequential Reads on the table? Off the > top of my head I would expect so, but I've never tested something > like that > before. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: join after saving rowid Value into a Field - For Design , Dev. Gurus
Vivek, Bad, bad, bad idea. You can play with rowids in your programs - as long as you consider them to be transient values (get it/use it). Don't forget that they are physical addresses (BTW, DBMS were invented in the first place to hide the physical implementation from programs). Any export/import, ALTER TABLE MOVE, partition split etc., basically any kind of reorg which may shuffle your data on your disks will quietly make everything implode. Think about something as trivial as refreshing the development database. If your join doesn't run as fast as you hope, check that your stats are up-to-date, check the execution plan, and if then you still are left unsatisfied, try some hints (FIRST_ROWS, ALL_ROWS and ORDERED are the safest and my favorite ones). But never ever consider rowids as reliable technical data. HTH, SF >- Original Message - >From: "VIVEK_SHARMA" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Fri, 27 Dec 2002 02:28:41 > >let us suppose there are two tables M and P. =0D >both Contain the field emp_id. other columns may be >different. =0D > =0D > =0D >All records of M also Exist in P .Table M will have >records in the range=0D >1-5 lakhs.=0D >P table will contain Additional Records such that >the Total Number of=0D >Records in P is 15-20 times the number of records >in M.=0D > =0D >one way to join the two tables is to say M.emp_id >=3D P.emp_id. but=0D >because P has high number of records the select is >slower.=0D > =0D >we found that select of a row from table "P" using >"rowid" column was=0D >very QUICK . =0D > =0D >Is it a Correct practice :-=0D > =0D >1) to Store the ROWID of Table P in M in a separate >column (say=0D >"P_rowid")=0D > =0D >2) Is it possible to do a Join like the follows :- >=0D > =0D >select field1, field2,... from M,P =0D >where M.emd_id =3D '6223' =0D >and =0D > =0D >this way we hoped to select from M table (which has >less number of=0D >records) and do a rowid based select on P table, >which we found out is=0D >not allowed by ORACLE. =0D > =0D >we want a join because we want to create a view >over table M and P.=0D > =0D >We do NOT want to use the following way :-=0D > =0D >select field1, field2 ..,P_rowid from M where >emp_id =3D '6223' from a=0D >Cursor & passing it to the Query as follows :- >=0D > =0D >select * from P where rowid =3D P_rowid (selected >earlier)=0D > =0D >Are there Some Standard Practices that Should be >Followed during=0D >Designing Tables , Fields, SQL Writing ?=0D > =0D >Any Dos , Don'ts ?=0D > =0D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: join after saving rowid Value into a Field - For Design , Dev
Title: Message Not a good idea to store rowid in table M. If you ever move table P to a different tablespace or within the same tablespace, all it's rowid would change. Richard Ji -Original Message-From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 5:29 AMTo: Multiple recipients of list ORACLE-LSubject: join after saving rowid Value into a Field - For Design , Dev. Gurus let us suppose there are two tables M and P. both Contain the field emp_id. other columns may be different. All records of M also Exist in P .Table M will have records in the range 1-5 lakhs. P table will contain Additional Records such that the Total Number of Records in P is 15-20 times the number of records in M. one way to join the two tables is to say M.emp_id = P.emp_id. but because P has high number of records the select is slower. we found that select of a row from table "P" using "rowid" column was very QUICK . Is it a Correct practice :- 1) to Store the ROWID of Table P in M in a separate column (say "P_rowid") 2) Is it possible to do a Join like the follows :- select field1, field2,... from M,P where M.emd_id = '6223' andthis way we hoped to select from M table (which has less number of records) and do a rowid based select on P table, which we found out is not allowed by ORACLE. we want a join because we want to create a view over table M and P. We do NOT want to use the following way :- select field1, field2 ..,P_rowid from M where emp_id = '6223' from a Cursor & passing it to the Query as follows :- select * from P where rowid = P_rowid (selected earlier) Are there Some Standard Practices that Should be Followed during Designing Tables , Fields, SQL Writing ? Any Dos , Don'ts ?
join after saving rowid Value into a Field - For Design , Dev. Gurus
Title: Message let us suppose there are two tables M and P. both Contain the field emp_id. other columns may be different. All records of M also Exist in P .Table M will have records in the range 1-5 lakhs. P table will contain Additional Records such that the Total Number of Records in P is 15-20 times the number of records in M. one way to join the two tables is to say M.emp_id = P.emp_id. but because P has high number of records the select is slower. we found that select of a row from table "P" using "rowid" column was very QUICK . Is it a Correct practice :- 1) to Store the ROWID of Table P in M in a separate column (say "P_rowid") 2) Is it possible to do a Join like the follows :- select field1, field2,... from M,P where M.emd_id = '6223' andthis way we hoped to select from M table (which has less number of records) and do a rowid based select on P table, which we found out is not allowed by ORACLE. we want a join because we want to create a view over table M and P. We do NOT want to use the following way :- select field1, field2 ..,P_rowid from M where emp_id = '6223' from a Cursor & passing it to the Query as follows :- select * from P where rowid = P_rowid (selected earlier) Are there Some Standard Practices that Should be Followed during Designing Tables , Fields, SQL Writing ? Any Dos , Don'ts ?
Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
It would appear we're looking into yet another hit ratio, namely the ASS Hit Ratio. Used to be rather high in my younger days. Mogens Jonathan Lewis wrote: Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 19 December 2002 17:51 We were the other way around in our testing lately:-) We turned on auto space management to remove the contention. Afterwards-we removed quite a bit of header block/free list contention.. anyway, more tests to follow -Original Message- Sent: Thursday, December 19, 2002 10:09 AM To: Multiple recipients of list ORACLE-L As part of a RAC benchmark with 9.2 we had faced severe LOCKING on setting segment space management AUTO & had to REMOVE it HTH
Re: unable to create stored outline for sql inside a procedure --
Shaleen Def.Rights: Roles can be enabled or disabled -- an unit must not be dependent on the enabled/disabled roles. There is nothing bad to have such design. This design is well thought, IMHO. At least at it's [was] consistent [on the moment of its invention]. Inv.right Due to the context switching inv.right program units are a little bit (simplified) more expensive to be managed than def.rights. Such units require some more development efforts and accuracy (internal/external names). >>> 2) To take care of this problem invokers rights facility >>> was introduced. Then why this restriction on roles. The advantage is reusable and manageable code but not just the problem with roles. Def.rights units have their advantages too -- the biggest one, IMHO -- no 'context switching'. Stored Java stuff is also based on inv.right facility. Kind regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Shaleen wrote: Hmm. Makes sense. Thanks Tim. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 2:34 PM I don't agree that anyone "shirked". Roles are, by design, changeable within a session. The SET ROLE command is not DDL, altering the metadata of the database. Instead, it is only altering already-granted permissions to used subsequently by the session. So, why should "permanent" objects (such as views, procedure, packages, triggers, etc) be created using permissions which are inherently transitory (i.e. available via roles)? Just because very few people use SET ROLE during a session doesn't alter its basic properties... When that note says that "complexity would be raised to the Nth degree", they are not necessarily indicating that Oracle could not have implemented it. This stuff is simplicity itself compared to the transaction-consistency model. Rather, the complexity would have been on the database administration side (not in the database engine), and a major pain in everyone's behind. Think it through. Oracle made a good design decision to prevent unnecessary complexity in database administration. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Script to recreate schema
> > I'm sure many of you have scripts to recreate an Oracle schema including > > objects (i am interested in tables, indexes , comments, views, sequences, > > triggers, stored procs/functions etc..) > exp userid=system/manager file=schema.dmp rows=n owner=scott > vi schema.dmp Instead of "vi schema.dmp" use the freeware DBATool to extract a set of rebuild scripts from the export file. It is much easier and there are a lot of other options available too. DBATool: http://www.DataBee.com/dt_home.htm Regards Dale -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row Migration
yes, row migration will degrade the performance.. - Original Message - From: Larry Elkins To: Multiple recipients of list ORACLE-L Sent: Friday, December 27, 2002 5:38 AM Subject: Row Migration Listers,8.1.7.4 64 Bit SolarisDoes row migration utilize DB File Sequential Reads on the table? Off thetop of my head I would expect so, but I've never tested something like thatbefore.Trying to figure out if row migration is the cause of the slowdown in apackage (well, it's probably slowing it down, just trying to gauge theimpact). PctFree is 10, and new feeds contain lots of elements that had beenempty before. As a result, a very large number of rows are being updatedwith the new info being applied, effectively doubling the row length. Wouldcertainly expect row migration to occur. When running, execution time hasquadrupled, and we see significant waits on DB File Sequential Reads, withthe file/block values and dba_extents indicating the table, not an index.The working idea at this point is that all those DB File Sequential Readwaits on the table are possibly related to rows being migrated. Anyonetested for this?We will be building a test case on Friday. One with PctFree 10 and thecolumns being updated having nulls. Will gather the waits, before and aftersesstat's, analyze list chained rows, both before and after, total blocks,rows per block, etc. Then rebuild the test having a PCTFREE of 50 and do thesame thing. Some wildcards -- with the blocks less tightly packed, we willhave to visit nearly double the number of blocks (maybe offset bymigration), contention, and various other things to take into account. Butthe main thing we are focusing in on is if we continue to see the db filesequential read waits on the table. I guess the fact that we are seeingwaits is indicative of some I/O contention, but trying to determine if, andhow much, of that I/O is due to row migration, in which case a largerPCTFREE could provide some more immediate relief. No FK/PK stuff, uniqueindex is there, but it should resolve uniqueness using the index, not thetable. Maybe have left some things out. This came up a few days ago, butjust really started thinking about it and digging into it. And the endresult is we don't want migrated rows, just looking to see if the rowmigration is the primary cause of the performance downturn.Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Larry Elkins INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Cursor_space_for_time
Your problem is probably the large number of parses that seem to be happening. Also the stats have no meaning here if you don't tell us over what time period they have been collected. Anjo. On Thursday 26 December 2002 12:39, Arun Chakrapanirao wrote: > Hi, > Has any enabled cursor_Space_for_time=true in your databases,IF yes have > you faced any issues,Cause we are facing lots of contention on latch free > for library cache issue and one of the main culprit is that SYSDATE FROM > DUAL,Just wondering if we enable this parameter will it by some chance be > able reduce the contention. > Or is there any other way you people who might have been facing like these > kind of issues are able to sort it out in any other way > > SQL_TEXT||''||LOADS||''||EXECUTIONS||''||PARSE_CALLS||''||DISK_READS||''||B >U FFER_GETS||' '||ROWS_PROCESSED > --- >- > SELECT USER FROM SYS.DUAL 2 57825830 888196 0 75584 57822907 > > > > SELECT PHONE_EXTENSION,ANI,DNIS,MESSAGE_TYPE,MESSAGE_SUB_TYPE > FROM ATS_CTI_IN WHERE AGENT_ID = :b1 AND STATUS = 'A' > AND MESSAGE_TYPE = '0604' AND MESSAGE_SUB_TYPE = '0010' > FOR UPDATE OF STATUS NOWAIT > > LOADS||''||EXECUTIONS||''||PARSE_CALLS||''||DISK_READS||''||BUFFER_GETS||'' >| > > |R > > --- >- > 1 11181720 1206070 588283 -1807011658 1204738 > > > LATCH_WHERE_AND_LABEL COUNT(0) > -- -- > kglpnc: child[child] 1063 > kqreqd: rel enqueue[]1117 > kglhdgn: child:[latch] 2227 > kgllkdl: child: cleanup[latch] 3743 > kcbrls: kslbegin[buffer DBA] 4053 > kglpnal: child: before processing[latch] 4780 > kglupc: child[child] 5182 > > > Please let me know > Thanks in Advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: single-task message
This event is making a come back. Single task linking was a way in V5 and V6 (and V7) to make applications run very fast. It was no longer supported by oracle. However, now withe the Context option (or what ever it is called today) it is back. "single task message" = "SQL*net message from client". Anjo. On Thursday 26 December 2002 10:34, Seema Singh wrote: > Hi > Wondering one of database is showing "single-task message " high wait. > Let me know what to do to fix this pl? > thx > -Seema > > > > > > _ > Add photos to your e-mail with MSN 8. Get 3 months FREE*. > http://join.msn.com/?page=features/featuredemail&xAPID=42&PS=47575&PI=7324&; >DI=7474&SU= > http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_addphotos_ >3mf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
