Re: ORA-904 after table rename
Does his version of TOAD support your Oracle server version ? We tried to run our (outdated) version of SQL Navigator against a 9i DB and would get weirdness like this from time to time when accessing the Oracle data dictionary. mvg/regards Jo Norris, Gregory T [ITS] [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/16/2004 16:09 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ORA-904 after table rename We're developing some schema update scripts for an in-house application, which includes renaming an existing table, and creating a new version using the original name. No problem... or so I thought. :( All seems well under OEM and SQL+, but I have a developer who consistently gets an ORA-904 error (invalid column name) when trying to access the new table under TOAD. I can't think of anything weird about this table, except that the original has some column-level grants (but not to his userid... he has select/insert/update/delete on both tables). I had him try exiting and restarting TOAD, in case it was caching something relevant, but that didn't make any apparent difference. Any idea what might be going on? SQL desc tool_request_old NameNull?Type --- TREQ_TOOLS_REQUEST_PKEY NOT NULL NUMBER(6) TREQ_PEOPLE_FKEYNOT NULL NUMBER(6) TREQ_SUBMIT_DATENOT NULL DATE TREQ_COMPLETE_DATE DATE TREQ_STATUS NOT NULL NUMBER(6) TREQ_COMMENTSVARCHAR2(2024) TREQ_BYPASS_STARTDATE TREQ_BYPASS_END DATE SQL desc tool_request NameNull?Type --- TREQ_ID NOT NULL NUMBER(6) TREQ_PERS_IDNOT NULL NUMBER(6) TREQ_STATUS_ID NOT NULL NUMBER(6) TREQ_SUBMIT_TMSTNOT NULL DATE TREQ_BYPASS_START_TMST DATE TREQ_BYPASS_END_TMST DATE TREQ_COMPLETE_TMST DATE TREQ_COMMENTSVARCHAR2(1024) -- My employers like me, but not enough to let me speak for them. Greg Norris Sprint LTD Database Administration -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Norris, Gregory T [ITS] 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).
Re: D'oh, forgot my title! (Bit twiddling and bit manipulation...)
Maybe a lookup table filled once with 256 input/output pairs ? mvg/regards Jo Connor McDonald [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/06/2004 14:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: D'oh, forgot my title! (Bit twiddling and bit manipulation...) As a first cut, bit 'i' appears to map to a new value of power(2,i*2-1)*1.5 eg 8th bit set = 1000 binary = 128 decimal = 1100 under new scheme = 49152 decimal = power(2,8*2-1)*1.5 so you could have something like: new_num := 0; x := 1; for i in 0 .. 7 loop if bitand(orig_num,x) = x then new_num := new_num + x*x*3; end if; x := x*2 end loop; or something like that... Cheers Connor --- Bobak, Mark [EMAIL PROTECTED] wrote: Sorry, first time I sent this, I typoed the mailing list address, when I cut-and-paste to resend, I forgot the title. -Original Message- From: Bobak, Mark Sent: Tue 1/6/2004 4:39 AM To:Multiple recipients of list ORACLE-L Cc: Subject: Hi, Well, since I can't sleep, I may as well try solving a problem. This is a bit odd, and I'm trying to think of the most efficient way to do it. I've set up some bitmaps in my app. Consider we have documents that we want to sell. In order to be able to sell a given doc, we need to have it stored in the vault and we also need to have negotiated the proper contract w/ the publisher. So, I've got two bitmaps, STORAGE and PERMISSIONS. But, here's the hook. There are 8 different types of storage, so I have an 8 bit mask. However, for every storage type, there are two types of permission. So, I have a 16 bit permissions mask. What I'd like to do is take my 8-bit STORAGE mask, say it's 10110011 and convert it to 1100. Note that all I did there was take each bit in the input mask, and make the same value repeat. So, 0 would become 00, 1 would become 11, 10 would become 1100. Does that make sense? Once I've done that, I can take my STORAGE mask that's now stretched to 16 bits, and directly AND it with my PERMISSIONS mask. So, my question is: Is there a nice, scalable way to take my 8 bits and expand them into 16 bits, in the way that I'm describing? A clever bit twiddling expression would be perfect, but anything that's efficient and scalable will do. (The end product will be hidden behind a view or stored procedure.) Any thoughts, anyone? Thanks in advance, -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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). ATTACHMENT part 2 application/ms-tnef name=winmail.dat = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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
RE: OEM permissions
I believe a role 'OEM_MONITOR' is created in 9i when you create a DB; pre-9i you can create it yourself (via catsnmp.sql or something like that) and you can use that instead of granting specific other privileges. Oracle claims that it contains a minimum set of privileges for OEM use, but maybe you can trim it down further for your specific needs. There are several notes on MetaLink about this; e.g. 216731.1. mvg/regards Jo DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/18/2003 16:34 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: OEM permissions Raj - Thanks for your reply. Were this a consultant, my reply would mirror yours, and maybe not so diplomatically. But basically I manage these databases on behalf of this manager, so when he asks for read-only access, I can't really refuse. And I think he is pretty competent as a DBA. He says that he prefers to use OEM instead of Toad. What I'm really asking is what could these grants be used for besides just reading data? If there are other actions that could be done, I could at least ask him not to perform those actions, so if something bad happens I have provided an alert ahead of time. For those who use OEM in your environment, does the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges sound pretty usual for OEM to be able to scout out the info it needs to paint the pretty displays? Yes, I am checking out how this exposes links and what is available on the other systems the links point to. I have also asked his group not to create any database links. Fortunately we have relatively few links. Again, thanks for your advice. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 18, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 18, 2003 8:24 AM To: Multiple recipients of list ORACLE-L We have a new manager that wants his group to use OEM for development access, as an alternative to Toad. He has requested a special Oracle userid with the following grants: SELECT_CATALOG_ROLE SELECT ANY DICTIONARY SELECT ANY TABLE Does this seem reasonable for OEM? The manager is responsible for the data in the database, so I don't see a problem with him viewing the data. There are few database links, and I'll be reviewing them. Any ideas on what mischief could occur? Thanks. ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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,
Re: Good news from Oracle
Or perhaps ora-600 [x] I didn't expect the Spanish Inquisition ! mvg/regards Jo Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/16/2003 17:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Good news from Oracle Raj, you proven once again that you're an invaluable source of good information. Let me share it with the others. The note that Raj is talking about has the tfollowing title: ORA-600 [12235] Oracle process has no purpose in life ! I guess it will be accompanied by ora-600 [x] Look at the bright side of life! rather soon. On 12/16/2003 09:54:38 AM, Jamadagni, Rajendra wrote: check the subject heading for tech note 33174.1 Type: REFERENCE Status: PUBLISHED Howzzat? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 16, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Of course they are. Support and development cost them much less now that they've outsourced it to Elbonia. The quality of the code is another matter. Fortunately, they have broad and wide open beta testing program so that we all know what we can expect. If the first relase of 10g is usable, I'll change my name to ora-600. ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
(OT !!!!!!!) Re: Good news from Oracle
Most definitely ! Smoke me a kipper and all that. I'm heavily biased in favour of anything British as far as comedy goes : Blackadder (absolute no. 1), AbFab, ... mvg/regards Jo Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/16/2003 18:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Good news from Oracle Nobody expects the Spanish Inquisition! Why is that there are so many Monty Python lovers among us oraclites? The other day, Bambi has used the velocity of a laden swallow thing from the Holy Grail, almost everybody knows Brian and some other sketches. Do we have any lovers of the Red Dwarf and Ace Rimmer here? On 12/16/2003 12:14:25 PM, [EMAIL PROTECTED] wrote: Or perhaps ora-600 [x] I didn't expect the Spanish Inquisition ! mvg/regards Jo Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/16/2003 17:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Good news from Oracle Raj, you proven once again that you're an invaluable source of good information. Let me share it with the others. The note that Raj is talking about has the tfollowing title: ORA-600 [12235] Oracle process has no purpose in life ! I guess it will be accompanied by ora-600 [x] Look at the bright side of life! rather soon. On 12/16/2003 09:54:38 AM, Jamadagni, Rajendra wrote: check the subject heading for tech note 33174.1 Type: REFERENCE Status: PUBLISHED Howzzat? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 16, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Of course they are. Support and development cost them much less now that they've outsourced it to Elbonia. The quality of the code is another matter. Fortunately, they have broad and wide open beta testing program so that we all know what we can expect. If the first relase of 10g is usable, I'll change my name to ora-600. ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
Re: Oracle AS 10g
Anyone know if this includes the new OEM 4.0 ? mvg/regards Jo Boivin, Patrice J [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/09/2003 18:14 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Oracle AS 10g Has been posted for HP/UX and Solaris on OTN. http://otn.oracle.com/software/products/ias/devuse.html I don't see Windows in their certification matrix, but they mention Red Hat linux 2.1. No SuSE. http://otn.oracle.com/software/products/ias/files/as-certification-904.html Maybe they will update this later. Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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).
RE: Analytic bug in 9.2.0.4
ORA-00600: internal error code, arguments: [kkqwrm_noref: COLFDNF set], [], [], [], [], [], [], [] on 9.2.0.4 64 bit; Solaris 2.8 mvg/regards Jo Prem Khanna J [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/05/2003 03:14 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Analytic bug in 9.2.0.4 You are right Jared. ORA-00600: internal error code, arguments: [kkqwrm_noref: COLFDNF set], [], [], [], [], [],[], [] this is on oracle 9.2.0.3 Win2k+SP3. Regards, Jp. -Original Message- While playing around with SQL for some PGA scripts, I managed to create some SQL that will consistently cause ORA-600 [kkqwrm_noref: COLFDNF set] This appears to be Bug # 2507421, which was supposedly fixed in 9.2.0.3. This bit of SQL is a bit useless as is, that is, for anything other than causing ORA-600. This is on 9.3.0.4 on RH Linux 7.2 Kernel 2.4.20-18.7smp It also appears on 9.2.0.4 on Win2k SP3. Anyone else see similar results? On a test database of course. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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).
Re: dba studio in oracle9.2
The functionality is now integrated in the console. mvg/regards Jo [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/05/2003 11:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:dba studio in oracle9.2 Hi all, I have installed oracle 9.2 server and managemenst server on windows. I have perform full installation. But there is no DBA Studio in oracle tools. Any comment. Rgds. Arslan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
Re: unix question
Something our Unix admins tend to do is move the files along different directories. E.g. they start in dir1; after succesfull backup, move them to dir2, etc. and after succesfull backup in dir4 delete them. So they should always get backed up 4 times even if you miss a run. Of course your backup needs to start in dir4 and work back to dir1 for obvious reasons. mvg/regards Jo [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/19/2003 21:20 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:unix question I want to store some files. I make a new copy every night. I want to archive it back 4 days. So after 4 days, I want to delete the old copy. How do I do this? However, if i miss a nightly batch and have less than 4 copies, I do not want to delete any? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
Re: How do you genrate primary keys?
I'm a bit surprised no one's mentioned it, but there's an article about the use of surrogate keys at Ixora : http://www.ixora.com.au/tips/design/synthetic_keys.htm mvg/regards Jo Jonathan Gennick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 14:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How do you genrate primary keys? The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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).
Re: question on comments -sanity check
user_col_comments, all_col_comments, dba_col_comments regards Jo Bob Metelsky [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/22/2003 22:44 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:question on comments -sanity check All The powers that be have it in their minds that there is a place for comments on each column in a table. afaik.. comments are only associated with tables not columns Eg Select * from user_tab_comments; People here seem to think they can document their columns by comments. Maybe I'm just burnt out... or am I missing something? A sanity check please. Also, is there a relatively easy way to maintain comments? Thanks! bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky 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).
Re: Tuning help required
Well, I'd start by writing the date part as :
MEPAI.MPAI_AS_OF_DATE between to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
It will at the very least make the query easier to read and understand
(also for the optimizer : it will know it's filtering on a range instead
of distinct values).
regards
Jo
New DBA [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/24/2003 09:39
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Tuning help required
Hi All,
I need help in tuning the following query. It takes around 6-7 minutes to
run. I hope that someone will be able to go through the details and give
me a few pointers.
I have gathered a few statistics, but don't know where to go from here.
Please view the mail in a fixed size font e.g. courier to preserve the
formatting. If the lines wrap over copying and pasting in a text editor
might help, though I'm not sure.
I apologize for the long message in advance.
Following is the query:
SELECT UNIQUE
MEPAI.MPAI_NAV_MOD ,
MEPAI.MPAI_NAV_MODS,
MEPAI.MPAI_SYS_NO,
MEPAI.MPAI_PAI_SYS_NO,
MEPAI.MPAI_AS_OF_DATE,
PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID,
CUR.CUR_CURRENCY_NAME,
CUR.CUR_CURRENCY_CODE,
CUR.CUR_SYS_NO
FROM
EPR_CURRENCIESCUR,
EPR_GEOGRAPHIES GEO,
EPR_PRODUCTS PRODUCTS,
MOD_EPR_PRICING_ASSET_INFOMEPAI
WHERE nb! sp; MEPAI.MPAI_ISS_SYS_NO = PRODUCTS.ISS_SYS_NO
AND MEPAI.MPAI_GEO_SYS_NO = GEO.GEO_SYS_NO
AND MEPAI.MPAI_CUR_SYS_NO = CUR.CUR_SYS_NO
AND MEPAI.MPAI_AS_OF_DATE IN
(
to_date('03/01/2003','MM/DD/'), to_date('03/02/2003','MM/DD/')
, to_date('03/03/2003','MM/DD/'), to_date('03/04/2003','MM/DD/')
, to_date('03/05/2003','MM/DD/'), to_date('03/06/2003','MM/DD/')
, to_date('03/07/2003','MM/DD/'), to_date('03/08/2003','MM/DD/')
, to_date('03/09/2003','MM/DD/'), to_date('03/10/2003','MM/DD/')
, to_date('03/11/2003','MM/DD/'), to_date('03/12/2003','MM/DD/')
, to_date('03/13/2003','MM/DD/'), to_date('03/14/2003','MM/DD/')
, to_date('03/15/2003','MM/DD/'), to_date('03/16/2003','MM/DD/')
, to_date('03/17/2003','MM/DD/'), to_date('03/18/2003','MM/DD/')
! ;, to_date('03/19/2003','MM/DD/'),
to_date('03/20/2003','MM/DD/')
, to_date('03/21/2003','MM/DD/'), to_date('03/22/2003','MM/DD/')
, to_date('03/23/2003','MM/DD/'), to_date('03/24/2003','MM/DD/')
, to_date('03/25/2003','MM/DD/'), to_date('03/26/2003','MM/DD/')
, to_date('03/27/2003','MM/DD/'), to_date('03/28/2003','MM/DD/')
, to_date('03/29/2003','MM/DD/'), to_date('03/30/2003','MM/DD/')
, to_date('03/31/2003','MM/DD/')
)
AND PRODUCTS.ISS_INSTR_ID in (1321,1339,1344,1342,1343,1341,1340)
AND CUR.CUR_SYS_NO in (200,226)
Order By MEPAI.MPAI_SYS_NO
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=515 Card=122 Bytes=8296)
10 SORT (UNIQUE) (Cost=503 Card=122 Bytes=8296)
21 CONCATENATION
32 NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
43 HASH JOIN (Cost=223 Card=61 Bytes=3965)
54 INLIST ITERATOR
65 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS'
(Cost=3 Card=16 Bytes=128)
76 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID'
(NON-UNIQUE) (Cost=2 Card=16)
84 NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
98 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES'
(Cost=1 Card=1 Bytes=21)
109 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
118 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF
'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
12 11 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE)
(Cost=217 Card=92720)
133 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
142 NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
15 14 HASH JOIN (Cost=223 Card=61 Bytes=3965)
16 15 INLIST ITERATOR
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS'
(Cost=3 Card=16 Bytes=128)
18 17 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID'
(NON-UNIQUE) (Cost=2 Card=16)
19 15 NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
20 19 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES'
(Cost=1 Card=1 Bytes=21)
21 20 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
22 19 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF
'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
23 22 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE)
(Cost=217 Card=92720)
24 14 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
The output of the following query before running the SQL and after running
the SQL are
Re: Bug in 9.2.0.4
I tried it on 9.2.0.4 in Win2k and it does indeed disconnect my session :( Nothing in udump, but it does write to core.log in cdump. regards Jo Munish Bajaj [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/22/2003 10:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Bug in 9.2.0.4 Hi Listers Any idea about this Bug in 9.2.0.4. If you try select 'Hello' from dual order by 1 desc; you may run into the bug. It creates a dump in udump and could disconnect your session. Oracle is creating a one-off patch to fix. The bug is for any order by x, where x is a number, desc and the column is a constant. It does not happen on my instance. Any Details will be appreciated. Regards Munish Bajaj -- 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: Undo Analyze Table
Analyze table ... delete statistics; regards Jo Hussain Ahmed Qadri [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/16/2003 08:54 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Undo Analyze Table Hi, We have two DBs, a production and a development, identical query was running very quickly on the Development and very slowly on the Production. Both have the similar structures, same number of indexes and everything. When I checked the explain plan, I found out that on the Production DB, it was doing a FTS on a couple of tables and was doing an Index scan on the Development server. The only difference was that the tables on the Production were ANALYZED. To confirm my theory, I analyzed the tables on Development and it started doing a FTS there as well hence slowing the query down. I know the optimizer, after analyzing, would have chosen the better path in its own sense but its not producing the desired result and it is taking ages now. is there any way to undo that? Regards, Hussain Ahmed Qadri DBA SKMCHRC -- 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: Rebuilding MLOG tables
IIRC you need to lock the parent table in one session and then do whatever you need to do to the mlog table in a SECOND session (because, as another poster pointed out, the lock will be released too soon otherwise). hth, Jo Stephen Lee [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/12/2003 22:41 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Rebuilding MLOG tables I don't think we can go with the truncate table thing since there is too much weirdness around here in when a client -- and there are multiple clients -- might update: Network problems, box crashed, sunspots (Don't forget about the sunspots!). So if there are entries still hanging around in the MLOG table, we want to keep them. -Original Message- I do it all the time. Actually you don't have to lock the table; you may simply quiesce the table, meaning no transations will be allowed. Steps: Quiesce the table Apply all the pending logs in the deferred trans queue on secondary database Truncate The MLOG$ table. No issues; in fact I think (note sure) it is supported by Oracle. And it should be; MLOG$ tables are just plain simple tables anyway. Hope this helps. Arup Nanda -- 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). -- 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: SYS not able to GRANT -- Strange !
There's a new sys priv in 9i called grant any object privilege that can be used for this. regards Jo Joe Testa [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/13/2003 06:34 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: SYS not able to GRANT -- Strange ! its always been that way, its not strange, sys has NEVER been able to grant privs on other owner's objects. i think that has changed in 9i but its late and my brain is fuzzy. joe Prem Khanna J wrote: Guys, CONNECT SYS AS SYSDBA; create user testuser1 identified by testuser1 ; grant connect, resource to testuser1; create user testuser2 identified by testuser2 ; grant create session to testuser2; create table testuser1.table1 ( a int ) ; grant select on testuser1.table1 to testuser2; error at line 1: ora-01031: insufficient privileges WHERE AS : connect testuser1/testuser1; grant select on testuser1.table1 to testuser2; grant succeeded. why is it so ? why sys is not able to GRANT ? seems to be strange ! the env. is 8.1.6.0./win2k. Jp. -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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).
