Re: log file sync Wait
First of all I'd like to have the full picture of your performance: Log file sync might be 57% of the wait time, but how much of the response time is wait time? Second, Log File Sync means Commit; So if your system is waiting a lot for commits there are two things you can do: Fewer commits (changes to applications) or faster commits (hardware striping, etc.). No changes to the log buffer will help here (except perhaps making it smaller, as Connor McDonald so brilliantly showed during the funniest presentation I've ever seen in my life at UKOUG in Birmingham). If the log buffer is being flushed constantly, it's better to make it small so that it doesn't have to go through the whole thing every time. Mogens VIVEK_SHARMA wrote: What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
how to update rbs?
I want to change TRANSACTIONS_PER_ROLLBACK_SEGMENT default parameters of rbs segments. but i don't know where this parameter is, how can i deal with it? Jim [EMAIL PROTECTED] 2003-01-02 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jim 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: log file sync Wait
Yes, the lessons I took from that presentation were to use a shorter piece of string and buy larger bottles of gin though I'm willing to admit that I may have got the wrong end of the stick. =) Cheers, Mike -Original Message- Sent: 02 January 2003 09:24 To: Multiple recipients of list ORACLE-L First of all I'd like to have the full picture of your performance: Log file sync might be 57% of the wait time, but how much of the response time is wait time? Second, Log File Sync means Commit; So if your system is waiting a lot for commits there are two things you can do: Fewer commits (changes to applications) or faster commits (hardware striping, etc.). No changes to the log buffer will help here (except perhaps making it smaller, as Connor McDonald so brilliantly showed during the funniest presentation I've ever seen in my life at UKOUG in Birmingham). If the log buffer is being flushed constantly, it's better to make it small so that it doesn't have to go through the whole thing every time. Mogens VIVEK_SHARMA wrote: What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Hately, Mike (NESL-IT) 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).
New year humor
Landing at Ben Gurion Airport... So True!!! As the plane settled down at Ben Gurion airport, the voice of the Captain came on: Please remain seated with your seatbelt fastened until this plane is at a complete standstill and the seat belt signs have been turned off. To those of you standing in the aisles, we wish you a Happy Chanukah. To those who have remained in their seats, we wish you a Merry Christmas Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar 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: Excessive Redo Generation
what makes you think you have excessive redo being generated? What does the application do? How many changes are made daily? How frequently? Just because a tablespaces is locally managed does not mean there will be no redo generated when a change is made, it just means that there will be less activity in the SYSTEM tablespace. --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: We seem to Be Generating Excessive Redo . All Tablespaces are LOCALLY Managed except SYSTEM . Size of Redo Logfile = 200 MB log_check_point_interval = 30 log_checkpoint_timeout = 0 log_buffer = 2MB NOTE - We have purposely kept increased log_check_point_interval = 30 based on past experience . Any /etc/system , init.ora parameter Changes too ? Concurrent Oracle processes = 1500 Approx. Machine Box = SF6800 Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 We shall be taking Logminer Outputs Anything in particular to Look for in the Logminer Output to Check for Excessive Redo Generation ? [VIVEK_SHARMA] Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: Invoker-rights/definer-rights response from Oracle Development
A point that Paul Needham could have mentioned - if an application user can execute the packaged procedure to set the role, then a malicious user could log in from SQL*Plus and do exactly the same. This is just security through obscurity. I believe a significant driver in the concept of an application role is that the application server should be connecting to Oracle through an application userid, and then using the proxy user facility to become another userid. In this case, the application userid can run the secure package, and the secure package can check that it is the application user running it as a proxy for the real end-user. Hence the real end-user can't set the role by logging in through SQL*Plus. (There still seems to be a loophole there for the highly competent end-user who can write C code and read Tom Kyte's book, of course). 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: 02 January 2003 05:49 Development So I forwarded the thread to her, and here's the response from Paul Needham of her team (who by the way was impressed with the knowledge level of the list contributors). - --- introduction of the invoker-rights facility. Oracle9i introduced the secure application role and global application context which are designed for proxy architectures. The secure application role restricts enabling a role to a set role command in a named security package. The security package can perform it's own security checks prior to invoking the set role command. - --- -- 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: Free Shared pool memory
I think it's safe to say that if the free memory is always very large then you can reinterpret it as 'wasted memory'. If the free memory is alway very small, I don't think it is possible to make any decision without know the application. It is possible that you need to increase the shared pool slightly (good app), it is also possible that your shared pool is just about the right size (great app) , but it is possible that your application design has a flaw in it. 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: 02 January 2003 05:39 Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: 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: log file sync Wait
Usual caveat: looking a v$system_event can be very misleading, you need to examine v$session_event to determine if anyone is actually noticing a problem. Usual caveat 2: A statspack report without a time interval is almost meaningless. However, in this case, log file sync at the top is sufficiently unusual to warrant a little hypothesis. Question: Was log file write really number two, or have you knocked out one or two lines between the two log-related waits ? Log file syncs are from the sessions, log file writes are from LGWR A log file sync is a call from a session to lgwr to write some log buffer to disc. As such, you could get multiple sessions calling at about the same time - and only the first one in gets lgwr to write, the rest have to wait until lgwr returns and notices that there is now a queue and does a piggyback write. Consequently, it is possible on a highly concurrent system for log file sync to have far more WAITS then log file write, and therefore look a much bigger problem than it really is. However, in your case, the number of log file sync WAITS is about the same as the number of log file write WAITS - so the fact that the TIME is five times as long suggests that concurrency of waits is not the issue, and you may have a proper problem. I suspect that the problem is the number of processes running on your system. Session A issues a log file sync, and goes off the run queue; some time later, lgwr gets the message and writes and posts session A to allow it to go back on the run queue. Session A sits on the run queue for ages, and finally becomes runnable. Solution - look at MTS, or get more CPUs on the box. But having said that - do check if any sessions are actually noticing a significant loss of time due to log file sync before worry about it. 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: 02 January 2003 07:48 What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- - --- log file sync 970,563 2,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- 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: Excessive Redo Generation
How are you deciding that you are generating excessive redo ? What is excessive for 1,500 concurrent users in a banking operation ? Using log miner to examine the problem sounds like a very painful last resort. Have you taken snapshots of session stats to try and pin down any patterns to redo generation so that you can (possibly) associate large volumes with specific processes ? Have you tried comparing redo size with commit / rollback counts to see if there is any pattern to activity that shows you were the most redo is generated ? Couple of thoughts: if your banking application is typical, then code to update a field on screen may be turned into SQL to update every single column in the table - this generates a lot of undo and redo (especially on wide tables). If you banking appliation is typical, then it could spend a lot of its time generating scratch data in working tables, then rolling it back or deleting it. This generates a lot of undo and redo. Both issues can be corrected, but only if you have access to source, or very compliant suppliers. 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: 02 January 2003 07:52 We seem to Be Generating Excessive Redo . All Tablespaces are LOCALLY Managed except SYSTEM . Size of Redo Logfile = 200 MB log_check_point_interval = 30 log_checkpoint_timeout = 0 log_buffer = 2MB NOTE - We have purposely kept increased log_check_point_interval = 30 based on past experience . Any /etc/system , init.ora parameter Changes too ? Concurrent Oracle processes = 1500 Approx. Machine Box = SF6800 Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 We shall be taking Logminer Outputs Anything in particular to Look for in the Logminer Output to Check for Excessive Redo Generation ? [VIVEK_SHARMA] Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: 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: log file sync Wait
Obviously you weren't listening attentively enough. The main thing you should have gleaned from the presentation was that if you bash an ugly squirrel with a hammer hard enough, then ... its still an ugly squirrel :-) Cheers Connor --- Hately, Mike (NESL-IT) [EMAIL PROTECTED] wrote: Yes, the lessons I took from that presentation were to use a shorter piece of string and buy larger bottles of gin though I'm willing to admit that I may have got the wrong end of the stick. =) Cheers, Mike -Original Message- Sent: 02 January 2003 09:24 To: Multiple recipients of list ORACLE-L First of all I'd like to have the full picture of your performance: Log file sync might be 57% of the wait time, but how much of the response time is wait time? Second, Log File Sync means Commit; So if your system is waiting a lot for commits there are two things you can do: Fewer commits (changes to applications) or faster commits (hardware striping, etc.). No changes to the log buffer will help here (except perhaps making it smaller, as Connor McDonald so brilliantly showed during the funniest presentation I've ever seen in my life at UKOUG in Birmingham). If the log buffer is being flushed constantly, it's better to make it small so that it doesn't have to go through the whole thing every time. Mogens VIVEK_SHARMA wrote: What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Hately, Mike (NESL-IT) 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
Re: how to update rbs?
It's a normal init.ora parameter. Why do you want to change it? Mogens Jim wrote: I want to change TRANSACTIONS_PER_ROLLBACK_SEGMENT default parameters of rbs segments. but i don't know where this parameter is, how can i deal with it? Jim [EMAIL PROTECTED] 2003-01-02 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness Vladimir, Thanks for your reply. I have tested the cursor. It does not include any bind variables. There are no broken rowids, as all objects passed analyze ... validate structure cascade. I also tested the scenario you describe in your code below. The code does break with that error, however there are no inline views in my code. Lisa -Original Message- From: Vladimir Begun [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-1410 Silliness Lisa . Enable SQL tracing . Launch your code . Identify (exactly) the cursor which fails with ORA-01410 and what bind vars are. . Pull out the statement from your code . Run it in 'standalone' mode . If it fails identify rowids which look broken. Check the phys. entities those rowids point out. Are those phys. entities Ok? . Any access BY ROWID in your statement is a potential problem. . You might want to dump error stack too but I suggest to contact oracle support first. Try to make a test case as simple as possible, it definitely would help. There is a simple scenario, may be it can give you some ideas (index_s is a simplified index simulator): DROP TABLE index_s; DROP TABLE tbl; CREATE TABLE index_s ( rid ROWID ); CREATE TABLE tbl ( p NUMBER ); INSERT INTO tbl VALUES(1); INSERT INTO index_s SELECT ROWID FROM tbl; SELECT * FROM tbl WHERE rowid = ( SELECT rid FROM index_s ); DROP TABLE tbl; CREATE TABLE tbl ( p NUMBER ); INSERT INTO tbl VALUES(1); COMMIT; SELECT * FROM tbl WHERE rowid = ( SELECT rid FROM index_s ); -- However this works well: SELECT * FROM tbl , index_s WHERE tbl.rowid = index_s.rid / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Koivu, Lisa wrote: Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at ELVIS.CLEANUP_VEGAS_PK, line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- 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: ORA-1410 Silliness
Title: ORA-1410 Silliness Hi Waleed, No. This is the name of the package. Thanks Lisa -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 3:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness I see "ELVIS.CLEANUP_VEGAS_PK" in the error message. Is it the primary key index for a table? If yes, then it may need to be rebuilt. Regards, waleed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-1410 Silliness Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Re: log file sync Wait
VIVEK_SHARMA, Hi, can you try use raw device for those redo log files? or use directio mode for your redo log volume.This do not need bounce your database and you can change it on fly. I ever hit the same problem before, and finally we modified the app to do much less commit and solved it successfully. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(ChinaOracle User Group) === 2003-01-01 22:53:00 ,you wrote£º=== What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: chao_ping 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: log file sync Wait
I was sensible enough to sit in the front row so I was soon distracted. Cheers Mike -Original Message- Sent: 02 January 2003 12:29 To: Multiple recipients of list ORACLE-L Obviously you weren't listening attentively enough. The main thing you should have gleaned from the presentation was that if you bash an ugly squirrel with a hammer hard enough, then ... its still an ugly squirrel :-) Cheers Connor --- Hately, Mike (NESL-IT) [EMAIL PROTECTED] wrote: Yes, the lessons I took from that presentation were to use a shorter piece of string and buy larger bottles of gin though I'm willing to admit that I may have got the wrong end of the stick. =) Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) 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: Different Oracle clients give different results
Oracle says this is intended behavior for desc function just more rigorously enforced in later versions. I am still skeptical as desc works in 8.1.6 svrmgrl. Perhaps enforced in sqlplus but not svrmgr...too many inconsistencies. Thanks Rick Brian McGraw brian.mcgraw@infinity-insu To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rance.comcc: Sent by: [EMAIL PROTECTED] Subject: RE: Different Oracle clients give different results 12/31/2002 01:28 PM Please respond to ORACLE-L Rick - Are all of the sqlnet.ora files resolving hostnames in the same order i.e., NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)? I'd also check for a rogue tnsnames.ora file in the same path as the executable. Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | -- -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 7:34 AM To: Multiple recipients of list ORACLE-L Hi DBAs, Oracle Server 8.1.6 Win 2000 Oracle Names I have the following Oracle clients installed locally. I connect to the server using the same USER. When I describe a package and procedure with package I get different results depending on the client. Any ideas what is going on here 8.0.5 desc pkg_charts WORKS desc pkg_charts.select_affiliatesWORKS 8.1.6 desc pkg_charts WORKS desc pkg_charts.select_affiliatesORA-04043: object pkg_charts.select_affiliates does not exist 8.1.7 desc pkg_charts WORKS desc pkg_charts.select_affiliatesORA-04043: object pkg_charts.select_affiliates does not exist 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian McGraw 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: ORA-1410 Silliness
Title: ORA-1410 Silliness Lisa, This is probably nottrue ... Oracle doesn't, has never been able to pinpoint exact line number (in cases such as these) especially with pl/sql packages. I believe the problems can be found by executing following query ... select line, type, source from user_source where name = your package_name) and line between 1960 and 1980 order by line, type / Usually in such cases (as you have described) Oracle will pointto the line which contains the actual INSERT word, because for Oracle that is the LINE-OF-CODE that it is executing. Line numbers from text editors are almost always wrong. Am I off the mark? I know the Vodka was really good, but I did drink lot of coffee too. Please correct me if I am wrong ... 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_martName Null? Type- --ID NOT NULL NUMBER(38)CONTRACT_NUMBER NOT NULL NUMBER(9)LOAD_JOB_ID NOT NULL NUMBER(12)JULIAN_RUN_DATE NOT NULL NUMBER(12)FAC_CODE VARCHAR2(1)OWNER_LAST_NAME VARCHAR2(20)OWNER_FIRST_NAME VARCHAR2(15)OWNER_ADDRESS1 VARCHAR2(25)OWNER_ADDRESS2 VARCHAR2(25)OWNER_CITY VARCHAR2(18)OWNER_STATE VARCHAR2(2)OWNER_ZIP VARCHAR2(9)NET_PURCHASE_PRICE NUMBER(11,2)ORIGINAL_DOWN_PAYMENT NUMBER(11,2)TOTAL_DOWN_PAYMENT NUMBER(11,2)CR_BAL NUMBER(11,2)INTEREST_RATE NUMBER(5,2)FIRST_PAYMENT_DATE DATEQUALIFICATION_CODE VARCHAR2(1)PAYMENT_AMOUNT NUMBER(9,2)PAYMENT_FREQUENCY VARCHAR2(1)AGING_10_TO_30_DAYS_DUE NUMBER(9,2)AGING_31_TO_60_DAYS_DUE NUMBER(9,2)AGING_61_TO_90_DAYS_DUE NUMBER(9,2)ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)DATE_OF_SALE DATESTATUS_OF_ACCOUNT VARCHAR2(1)CONTRACT_TYPE VARCHAR2(1)WAS_PENDER VARCHAR2(1)CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)DOCUMENT_STATUS_CODE VARCHAR2(1)FIXED_WEEK_SALE VARCHAR2(1)UDI_SALE VARCHAR2(1)PHASE_NUMBER VARCHAR2(6)FAIRSHARE_PLUS_MEMBER VARCHAR2(1)POINTS_OWNED NUMBER(7)DEED_DATE DATEPRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)RESERVATION_CODE VARCHAR2(4)INTERNATIONAL_CODE VARCHAR2(1)UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)AGING_0_TO_90_DAYS_DUE NUMBER(9,2)AGING_91_TO_120_DAYS_DUE NUMBER(9,2)AGING_121_TO_150_DAYS_DUE NUMBER(9,2)AGING_151_OVER NUMBER(9,2)LOT_LOCATION VARCHAR2(12)PAYMENTS_MADE NUMBER(3)SUPPRESSION_CODE VARCHAR2(1)ACCRUED_INTEREST_BAL NUMBER(9,2)PAC_FREEZE_CODE VARCHAR2(1)CREDIT_CARD_FREEZE_CODE VARCHAR2(1)ASSOCIATION_NUMBER VARCHAR2(4)RFS_ASSIGNMENT_DATE DATEOVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)RESERVATION_PENDING VARCHAR2(1)CREDIT_REPORTING_CODE VARCHAR2(2)CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2)EQUITY_IN NUMBER(9,2)DATE_CODED_7 DATEADJUSTABLE_RATE_MORTGAGE VARCHAR2(1)NUMBER_OF_PAYS_LEFT NUMBER(4)DEFERRED_INTEREST NUMBER(9,2)DEFERRED_PRINCIPAL NUMBER(9,2)CURRENT_YEAR_DEFERMENTS NUMBER(5)CURRENT_YEAR_TOTAL_DEFERMENTS NUMBER(5)LAST_PAYMENT_DATE DATENEXT_PAYMENT_DATE DATEPAC_DUE_DATE DATEEFT_ROUTING_NUMBER VARCHAR2(8)EFT_ACCOUNT_NUMBER VARCHAR2(30)EFT_MANUAL_NUMBER VARCHAR2(30)BANK VARCHAR2(35)STATUS_CHANGE_DATE DATEASSIGNED_LOAN_REP VARCHAR2(2)CREDIT_CARD_PAC_ACCOUNT VARCHAR2(20)PRINCIPAL_BALANCE NUMBER(11,2)CR_DISCOUNT_BALANCE NUMBER(11,2)CREDIT_LIFE_PREM_BAL NUMBER(11,2)RFP_PAC_CODE VARCHAR2(1)RFP_DRAFT_CODE VARCHAR2(1)RFP_ROUTE_NUMBER VARCHAR2(8)RFP_EFT_NUMBER VARCHAR2(30)RFP_MANUAL_NUMBER VARCHAR2(30)RFP_BANK_NAME VARCHAR2(35)FPPA_PAC_CODE VARCHAR2(1)FPPA_DRAFT_CODE VARCHAR2(1)FPPA_ROUTE_NUMBER VARCHAR2(8)FPPA_EFT_NUMBER VARCHAR2(30)FPPA_MANUAL_NUMBER VARCHAR2(30)FPPA_BANK_NAME VARCHAR2(35)TS_BAL_DUE_RECOGNIZED NUMBER(9)TS_LATE_FEE_RECOGNIZED NUMBER(9,2)TS_YTD_MAINT_FEE_COLL NUMBER(9,2)TS_MAINT_FEE_AMOUNT NUMBER(9,2)PO_BIRTH_DATE DATETS_LOCATION VARCHAR2(12)CR_DATE_REC_IN_DEEDING DATECR_REFUND VARCHAR2(11)CR_CREDIT_LIFE_TYPE VARCHAR2(1)CR_QUALIFICATION_DATE DATECR_EQT_IN_FROM_CONT_NO1 VARCHAR2(9)CR_EQT_IN_FROM_CONT_NO2 VARCHAR2(9)CR_HC_AMT1 NUMBER(9)CR_HC_AMT2 NUMBER(9)CR_HC_POST1 NUMBER(9)CR_HC_POST2 NUMBER(9)CR_HC_DATE1 DATECR_HC_DATE2 DATECR_DATE_IN_LR DATECR_TRADE_ALLOW NUMBER(9)CR_TITLE_INS_CHARGED
doubt
Dear all, we use select * from tab to list all objects in a particular user in oracle. could you tell me the equivalent query in sql server ? Regards Jai
Re: log file sync Wait
That was my understanding, too. Oh, and use bigger hammers, more nails and Australian beer. Hately, Mike (NESL-IT) wrote: Yes, the lessons I took from that presentation were to use a shorter piece of string and buy larger bottles of gin though I'm willing to admit that I may have got the wrong end of the stick. =) Cheers, Mike -Original Message- Sent: 02 January 2003 09:24 To: Multiple recipients of list ORACLE-L First of all I'd like to have the full picture of your performance: Log file sync might be 57% of the wait time, but how much of the response time is wait time? Second, Log File Sync means Commit; So if your system is waiting a lot for commits there are two things you can do: Fewer commits (changes to applications) or faster commits (hardware striping, etc.). No changes to the log buffer will help here (except perhaps making it smaller, as Connor McDonald so brilliantly showed during the funniest presentation I've ever seen in my life at UKOUG in Birmingham). If the log buffer is being flushed constantly, it's better to make it small so that it doesn't have to go through the whole thing every time. Mogens VIVEK_SHARMA wrote: What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800
functions/procedures and commits
Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: ORA-1410 Silliness
Nope... -Original Message-From: ora ak [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Did u do any migration recently . "Koivu, Lisa" [EMAIL PROTECTED] wrote: Well, I don't think that's the issue. I'm issuing bulk inserts and using pl/sql tables in this procedure. That functionality has been in place since February and these errors only started surfacing in the last couple of months. I could decrease the commit interval and try that. I just hope it doesn't (big) hammer my runtime, it's bad enough already, evenwith the screamingfast bulk insert. Thanks Kevin for your input. -Original Message-From: Kevin Lange [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 1:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Very straight forward. and LONG . (Yea, I read to your last message). Could that be the issue here ?? Is the record too long or some buffer being overwritten in Oracle when there is so long of record and so much data ?? Have you tried (or do you already) commit after each insert from the RAW table ?? I know that you would not think this could help , but buffers and such might get cleared on the commit . just a thought anyway. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_martName Null? Type- --ID NOT NULL NUMBER(38)CONTRACT_NUMBER NOT NULL NUMBER(9)LOAD_JOB_ID NOT NULL NUMBER(12)JULIAN_RUN_DATE NOT NULL NUMBER(12)FAC_CODEn! bsp; VARCHAR2(1)OWNER_LAST_NAME VARCHAR2(20)OWNER_FIRST_NAME VARCHAR2(15)OWNER_ADDRESS1 VARCHAR2(25)OWNER_ADDRESS2 VARCHAR2(25)OWNER_CITY! VARCHAR2(18)OWNER_STATE VARCHAR2(2)OWNER_ZIP VARCHAR2(9)NET_PURCHASE_PRICE NUMBER(11,2)ORIGINAL_DOWN_PAYMENT NUMBER(11,2)TOTAL_DOWN_PAYMENT NUMBER(11,2)CR_BALnbs! p; NUMBER(11,2)INTEREST_RATE NUMBER(5,2)FIRST_PAYMENT_DATE DATEQUALIFICATION_CODE VARCHAR2(1)PAYMENT_AMOUNT NUMBER(9,2)PAYMENT_FREQUENCY! nbsp; VARCHAR2(1)AGING_10_TO_30_DAYS_DUE NUMBER(9,2)AGING_31_TO_60_DAYS_DUE NUMBER(9,2)AGING_61_TO_90_DAYS_DUE NUMBER(9,2)ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)DATE_OF_SALE DATESTATUS_OF_ACCOUNT VARCHAR2(1)CONTRACT_TYPEnb! sp; VARCHAR2(1)WAS_PENDER VARCHAR2(1)CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)DOCUMENT_STATUS_CODE VARCHAR2(1)FIXED_WEEK_SALE VARCHAR2(1)UDI_SALEnbs! p; VARCHAR2(1)PHASE_NUMBER VARCHAR2(6)FAIRSHARE_PLUS_MEMBER VARCHAR2(1)POINTS_OWNED NUMBER(7)DEED_DATE DATEPRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)RESERVATION_CODE! VARCHAR2(4)INTERNATIONAL_CODE VARCHAR2(1)UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)AGING_0_TO_90_DAYS_DUE NUMBER(9,2)AGING_91_TO_120_DAYS_DUE NUMBER(9,2)AGING_121_TO_150_DAYS_DUE NUMBER(9,2)AGING_151_OVER NUMBER(9,2)LOT_LOCATION! VARCHAR2(12)PAYMENTS_MADE NUMBER(3)SUPPRESSION_CODE VARCHAR2(1)ACCRUED_INTEREST_BAL NUMBER(9,2)PAC_FREEZE_CODE VARCHAR2(1)CREDIT_CARD_FREEZE_CODE VARCHAR2(1)ASSOCIATION_NUMBER! ; VARCHAR2(4)RFS_ASSIGNMENT_DATE DATEOVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)RESERVATION_PENDING VARCHAR2(1)CREDIT_REPORTING_CODE VARCHAR2(2)CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2)EQUITY_IN NUMBER(9,2)DATE_CO! DED_7 DATEADJUSTABLE_RATE_MORTGAGE VARCHAR2(1)NUMBER_OF_PAYS_LEFT NUMBER(4)DEFERRED_INTEREST NUMBER(9,2)DEFERRED_PRINCIPAL NUMBER(9,2)CURRENT_YEAR_DEFERMENTS NUMBER(5)CURRENT_YEAR_TOTAL_DEFERMENTSnb! sp; NUMBER(5)LAST_PAYMENT_DATE DATENEXT_PAYMENT_DATE DATEPAC_DUE_DATE DATEEFT_ROUTING_NUMBER VARCHAR2(8)EFT_ACCOUNT_NUMBER VARCHAR2(30)EFT_MANUAL_NUMBER! ; VARCHAR2(30)BANK VARCHAR2(35)STATUS_CHANGE_DATE
RE: ORA-1410 Silliness
Title: ORA-1410 Silliness Lisa, I know someone else may have suggested this, but I am thinking that maybe you do need to rebuild the index that was being used by the query. It could be you have a bad index that still has an entry for a record, but the record does not exist? Sounds impossible but, what the hey? Tom Mercadante Oracle Certified Professional -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Hi Waleed, No. This is the name of the package. Thanks Lisa -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 3:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness I see "ELVIS.CLEANUP_VEGAS_PK" in the error message. Is it the primary key index for a table? If yes, then it may need to be rebuilt. Regards, waleed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-1410 Silliness Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness Amount of data, definately. This table grows by ~2.5GB weekly. Have you ever seen data volume begin to cause problems? Thanks Rachel -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 5:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1410 Silliness Okay this means that the statement itself, which is being pointed to by the error line, is not the real culprit. Unfortunately, I don't know of a good debugger for PL/SQL that lets you step through line by line. let me rephrase that, I know of no freeware one :) since you say this has only recently started happening, and since the sql runs on its own, what else has changed since the problem started? Amount of data? Other apps on the box taking away memory? Rachel --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hi Rachel, Just tried it and it works. Thanks for your suggestion. Lisa -Original Message- Sent: Tuesday, December 31, 2002 2:19 PM To: Multiple recipients of list ORACLE-L dumb question -- did you extract the insert statement and run it in sqlplus? Does it run there or does it go boom as well? --- Koivu, Lisa [EMAIL PROTECTED] wrote: You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_mart Name Null? Type - -- ID NOT NULL NUMBER(38) CONTRACT_NUMBER NOT NULL NUMBER(9) LOAD_JOB_ID NOT NULL NUMBER(12) JULIAN_RUN_DATE NOT NULL NUMBER(12) FAC_CODE VARCHAR2(1) OWNER_LAST_NAME VARCHAR2(20) OWNER_FIRST_NAME VARCHAR2(15) OWNER_ADDRESS1 VARCHAR2(25) OWNER_ADDRESS2 VARCHAR2(25) OWNER_CITY VARCHAR2(18) OWNER_STATE VARCHAR2(2) OWNER_ZIP VARCHAR2(9) NET_PURCHASE_PRICE NUMBER(11,2) ORIGINAL_DOWN_PAYMENT NUMBER(11,2) TOTAL_DOWN_PAYMENT NUMBER(11,2) CR_BAL NUMBER(11,2) INTEREST_RATE NUMBER(5,2) FIRST_PAYMENT_DATE DATE QUALIFICATION_CODE VARCHAR2(1) PAYMENT_AMOUNT NUMBER(9,2) PAYMENT_FREQUENCY VARCHAR2(1) AGING_10_TO_30_DAYS_DUE NUMBER(9,2) AGING_31_TO_60_DAYS_DUE NUMBER(9,2) AGING_61_TO_90_DAYS_DUE NUMBER(9,2) ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2) DATE_OF_SALE DATE STATUS_OF_ACCOUNT VARCHAR2(1) CONTRACT_TYPE VARCHAR2(1) WAS_PENDER VARCHAR2(1) CREDIT_LIFE_ON_CONTRACT VARCHAR2(1) DOCUMENT_STATUS_CODE VARCHAR2(1) FIXED_WEEK_SALE VARCHAR2(1) UDI_SALE VARCHAR2(1) PHASE_NUMBER VARCHAR2(6) FAIRSHARE_PLUS_MEMBER VARCHAR2(1) POINTS_OWNED NUMBER(7) DEED_DATE DATE PRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1) RESERVATION_CODE VARCHAR2(4) INTERNATIONAL_CODE VARCHAR2(1) UNIT_PHASE_COMPLETION_CODE VARCHAR2(1) AGING_0_TO_90_DAYS_DUE NUMBER(9,2) AGING_91_TO_120_DAYS_DUE NUMBER(9,2) AGING_121_TO_150_DAYS_DUE NUMBER(9,2) AGING_151_OVER NUMBER(9,2) LOT_LOCATION VARCHAR2(12) PAYMENTS_MADE NUMBER(3) SUPPRESSION_CODE VARCHAR2(1) ACCRUED_INTEREST_BAL NUMBER(9,2) PAC_FREEZE_CODE VARCHAR2(1) CREDIT_CARD_FREEZE_CODE VARCHAR2(1) ASSOCIATION_NUMBER VARCHAR2(4) RFS_ASSIGNMENT_DATE DATE OVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1) RESERVATION_PENDING VARCHAR2(1) CREDIT_REPORTING_CODE VARCHAR2(2) CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2) EQUITY_IN NUMBER(9,2) DATE_CODED_7 DATE ADJUSTABLE_RATE_MORTGAGE VARCHAR2(1) NUMBER_OF_PAYS_LEFT NUMBER(4) DEFERRED_INTEREST NUMBER(9,2) DEFERRED_PRINCIPAL NUMBER(9,2) CURRENT_YEAR_DEFERMENTS NUMBER(5) CURRENT_YEAR_TOTAL_DEFERMENTS NUMBER(5) LAST_PAYMENT_DATE DATE NEXT_PAYMENT_DATE DATE PAC_DUE_DATE DATE EFT_ROUTING_NUMBER VARCHAR2(8) EFT_ACCOUNT_NUMBER VARCHAR2(30) EFT_MANUAL_NUMBER VARCHAR2(30) BANK VARCHAR2(35) STATUS_CHANGE_DATE DATE ASSIGNED_LOAN_REP VARCHAR2(2) CREDIT_CARD_PAC_ACCOUNT VARCHAR2(20) PRINCIPAL_BALANCE NUMBER(11,2) CR_DISCOUNT_BALANCE NUMBER(11,2) CREDIT_LIFE_PREM_BAL NUMBER(11,2) RFP_PAC_CODE VARCHAR2(1) RFP_DRAFT_CODE VARCHAR2(1) RFP_ROUTE_NUMBER VARCHAR2(8) RFP_EFT_NUMBER VARCHAR2(30) RFP_MANUAL_NUMBER VARCHAR2(30) RFP_BANK_NAME VARCHAR2(35) FPPA_PAC_CODE VARCHAR2(1) FPPA_DRAFT_CODE VARCHAR2(1) FPPA_ROUTE_NUMBER VARCHAR2(8) FPPA_EFT_NUMBER VARCHAR2(30) FPPA_MANUAL_NUMBER VARCHAR2(30) FPPA_BANK_NAME VARCHAR2(35) TS_BAL_DUE_RECOGNIZED NUMBER(9) TS_LATE_FEE_RECOGNIZED NUMBER(9,2) TS_YTD_MAINT_FEE_COLL NUMBER(9,2) TS_MAINT_FEE_AMOUNT NUMBER(9,2) PO_BIRTH_DATE DATE TS_LOCATION VARCHAR2(12) CR_DATE_REC_IN_DEEDING DATE CR_REFUND VARCHAR2(11) CR_CREDIT_LIFE_TYPE VARCHAR2(1) CR_QUALIFICATION_DATE DATE CR_EQT_IN_FROM_CONT_NO1 VARCHAR2(9) CR_EQT_IN_FROM_CONT_NO2 VARCHAR2(9) CR_HC_AMT1 NUMBER(9) CR_HC_AMT2 NUMBER(9) CR_HC_POST1 NUMBER(9) CR_HC_POST2 NUMBER(9) CR_HC_DATE1 DATE CR_HC_DATE2 DATE CR_DATE_IN_LR DATE CR_TRADE_ALLOW NUMBER(9) CR_TITLE_INS_CHARGED
RE: doubt
In Query Analyzer select the database if interest and then run EXEC SP_HELP (really, this is not a joke) ;o) It will list system and user objects within that DB. Dave -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:04 AMTo: Multiple recipients of list ORACLE-LSubject: doubtDear all, we use "select * from tab" to list all objects in a particular user in oracle. could you tell me the equivalent query in sql server ? Regards Jai
Re: Free Shared pool memory
Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of thestatistic "free memory"was actuallya verb and a noun (i.e. as in "free Nelson Mandela"or "free Willy"), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time"N" bytes were freed from the Shared Pool, then the statistic was incremented by "N". At least, this explanation would haveaccounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term "free memory" became what everyone had thought it was, an adjective and a noun (i.e. as in "free beer" or "free time"). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAME BYTES-- --free memory 18208352miscellaneous 2378964DML locks 12PLS non-lib hp 2096trigger inform 944PL/SQL MPCODE 1146204PL/SQL DIANA 1223360PX subheap 123476db_block_hash_buckets 1411080sessions 377300KGK heap 48124State objects 267420message pool freequeue 124552Checkpoint queue 885168enqueue_resources 222912db_files 370988KGFF heap 649844KQLS heap 1709904dictionary cache 12670280table definiti 3228transactions 171264ksfv subheap 4248fixed allocation callback 1280library cache 89490788simulator trace entries 24sql area 187432036table columns 19520processes 123380partitioning d 152976db_block_buffers 1088event statistics per sess 607600 --sum 331067288 SQL show parameter shared_pool_size NAME TYPE VALUE--- --- -shared_pool_size string 314572800 I'm curious about the 16,494,488 bytes difference. Is it possible that V$SGASTAT is another "unlatched" data structure in memory, allowing errors in the interest of eliminating contention? There are other similar structures in the SGA (i.e. the data structure underlying table MONITORING statistics later flushed to SYS.TABMOD$)... Thanks for any and all insight! - Original Message - From: "Jonathan Lewis" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 3:28 AM Subject: Re: Free Shared pool memory I think it's safe to say that if the free memory is always very large then you can reinterpret it as 'wasted memory'. If the free memory is alway very small, I don't think it is possible to make any decision without know the application. It is possible that you need to increase the shared pool slightly (good app), it is also possible that your shared pool is just about the right size (great app) , but it is possible that your application design has a flaw in it. 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: 02 January 2003 05:39Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web
RE: Automatic backup on Oracle 9i -- For Jared
Title: RE: Automatic backup on Oracle 9i -- For Jared As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: OraCop [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic backup on Oracle 9i -- For Jared Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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: how to update rbs?
What you are referring to is an initialization parameter that is utilized by the mechanism of PUBLIC rollback segments. The default type of rollback segment is PRIVATE and they ignore the parameter. You are most likely utilizing PRIVATE rollback segments... Do you know what problem you are trying to solve? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 2:34 AM I want to change TRANSACTIONS_PER_ROLLBACK_SEGMENT default parameters of rbs segments. but i don't know where this parameter is, how can i deal with it? Jim [EMAIL PROTECTED] 2003-01-02 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jim 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: Tim Gorman 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: functions/procedures and commits
if I recall correctly, implicit commits are done a) on a clean exit from sqlplus b) on a clean exit from a stored program c) if you do a DDL statement after the DML statement but this is from memory and without checking manuals. --- John Dunn [EMAIL PROTECTED] wrote: Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: functions/procedures and commits
Title: RE: functions/procedures and commits No ... an implicit commit is performed 1. If you perform any DDL statement 2. If SQLPLUS you exit without issuing an explicit ROLLBACK. Otherwise, your transaction will remain open awaiting for an explicit commit or rollback. 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: John Dunn [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Subject: functions/procedures and commits Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). *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: ORA-1410 Silliness
Title: ORA-1410 Silliness Is the error reproducible if you replace the bulk insert with regular insert? Waleed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Hi Waleed, No. This is the name of the package. Thanks Lisa -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 3:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness I see "ELVIS.CLEANUP_VEGAS_PK" in the error message. Is it the primary key index for a table? If yes, then it may need to be rebuilt. Regards, waleed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-1410 Silliness Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: functions/procedures and commits
John, there is no such thing as an implicit commit within Oracle. the only implicit commit that I know of is during a sqlplus session when you exit the program. even this is settable by a sqlplus option. distributed transactions that are controlled by a transaction coordinator (like MS DTC) might issue commits only because the web application requires all updates to be handled by the app-server. but this is different from what you are asking, I think. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: ORA-1410 Silliness
Title: ORA-1410 Silliness Lisa I dont recollect exactly but I think I have seen this happen when you start getting too close to the max_open_cursor limit... something like Oracle cant open a cursor as it is at the max limit and a fetch call might be issued.. Checkyr max_open_cursor limit and also check the v$open_cursor when the error happens... Babu - Original Message - From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Thursday, January 02, 2003 8:14 AM Subject: RE: ORA-1410 Silliness Hi Waleed, No. This is the name of the package. Thanks Lisa -Original Message-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 3:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness I see "ELVIS.CLEANUP_VEGAS_PK" in the error message. Is it the primary key index for a table? If yes, then it may need to be rebuilt. Regards, waleed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-1410 Silliness Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Re:RE: functions/procedures and commits
Tom, Correction, all DDL statements do an implicit commit. Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 1/2/2003 7:09 AM John, there is no such thing as an implicit commit within Oracle. the only implicit commit that I know of is during a sqlplus session when you exit the program. even this is settable by a sqlplus option. distributed transactions that are controlled by a transaction coordinator (like MS DTC) might issue commits only because the web application requires all updates to be handled by the app-server. but this is different from what you are asking, I think. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). -- 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: Buffer Pool Testing
I think you are seeing expected behaviour. Blocks subject to tablescan are loaded in to the LRU end of the cache, even when using a RECYCLE cache. However, if there are free blocks in the cache (state = 0) Oracle uses those rather than flushing other blocks. Consequently, when you startup and scan a 400 block table with a 1,000 block cache, the whole 400 blocks will get into memory. Then the next 10,000 block scan will start by using the last 600 blocks of the cache before recycling the last db_file_multiblock_read_count blocks. By this time, though, the 1st 400 blocks are at the MRU end of the chain, and are not moved by the subsequent tablescan. If you start with the 10,000 block scan, the whole cache is filled. The second scan then keeps recycling the last db_file_multiblock_read_count blocks (though in your case I guess it's plus one - possibly a cleanout block, possibly the segment header block which may go into the Default pool in v9 - without pushing out any more of the first 1,000 blocks from the first scan. Periods of time shortly after startup are always likely to show anomalous behaviour. 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: 31 December 2002 19:31 I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: WORK_ORDER_STEP - 428 blocks of data ALRA_TRANSACTION_HISTORY - 14152 blocks of data The RECYCLE pool has 1000 blocks. I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 56914 0 .02 RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: First query - 1000 blocks are used as expected BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hitting a bug or just misunderstanding the buffer management algorithms? ALRA_TRANSACTION_HISTORY blocks should be LRU as I hit the WORK_ORDER_STEP table over and over. Thanks, Jay -- 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: ORA-1410 Silliness
well, you are certainly shooting down all of the best ideas being offered! :) I'd hate to be in your shoes! Tom Mercadante Oracle Certified Professional -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Nope... -Original Message-From: ora ak [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Did u do any migration recently . "Koivu, Lisa" [EMAIL PROTECTED] wrote: Well, I don't think that's the issue. I'm issuing bulk inserts and using pl/sql tables in this procedure. That functionality has been in place since February and these errors only started surfacing in the last couple of months. I could decrease the commit interval and try that. I just hope it doesn't (big) hammer my runtime, it's bad enough already, evenwith the screamingfast bulk insert. Thanks Kevin for your input. -Original Message-From: Kevin Lange [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 1:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Very straight forward. and LONG . (Yea, I read to your last message). Could that be the issue here ?? Is the record too long or some buffer being overwritten in Oracle when there is so long of record and so much data ?? Have you tried (or do you already) commit after each insert from the RAW table ?? I know that you would not think this could help , but buffers and such might get cleared on the commit . just a thought anyway. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_martName Null? Type- --ID NOT NULL NUMBER(38)CONTRACT_NUMBER NOT NULL NUMBER(9)LOAD_JOB_ID NOT NULL NUMBER(12)JULIAN_RUN_DATE NOT NULL NUMBER(12)FAC_CODEn! bsp; VARCHAR2(1)OWNER_LAST_NAME VARCHAR2(20)OWNER_FIRST_NAME VARCHAR2(15)OWNER_ADDRESS1 VARCHAR2(25)OWNER_ADDRESS2 VARCHAR2(25)OWNER_CITY! VARCHAR2(18)OWNER_STATE VARCHAR2(2)OWNER_ZIP VARCHAR2(9)NET_PURCHASE_PRICE NUMBER(11,2)ORIGINAL_DOWN_PAYMENT NUMBER(11,2)TOTAL_DOWN_PAYMENT NUMBER(11,2)CR_BALnbs! p; NUMBER(11,2)INTEREST_RATE NUMBER(5,2)FIRST_PAYMENT_DATE DATEQUALIFICATION_CODE VARCHAR2(1)PAYMENT_AMOUNT NUMBER(9,2)PAYMENT_FREQUENCY! nbsp; VARCHAR2(1)AGING_10_TO_30_DAYS_DUE NUMBER(9,2)AGING_31_TO_60_DAYS_DUE NUMBER(9,2)AGING_61_TO_90_DAYS_DUE NUMBER(9,2)ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)DATE_OF_SALE DATESTATUS_OF_ACCOUNT VARCHAR2(1)CONTRACT_TYPEnb! sp; VARCHAR2(1)WAS_PENDER VARCHAR2(1)CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)DOCUMENT_STATUS_CODE VARCHAR2(1)FIXED_WEEK_SALE VARCHAR2(1)UDI_SALEnbs! p; VARCHAR2(1)PHASE_NUMBER VARCHAR2(6)FAIRSHARE_PLUS_MEMBER VARCHAR2(1)POINTS_OWNED NUMBER(7)DEED_DATE DATEPRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)RESERVATION_CODE! VARCHAR2(4)INTERNATIONAL_CODE VARCHAR2(1)UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)AGING_0_TO_90_DAYS_DUE NUMBER(9,2)AGING_91_TO_120_DAYS_DUE NUMBER(9,2)AGING_121_TO_150_DAYS_DUE NUMBER(9,2)AGING_151_OVER NUMBER(9,2)LOT_LOCATION! VARCHAR2(12)PAYMENTS_MADE NUMBER(3)SUPPRESSION_CODE VARCHAR2(1)ACCRUED_INTEREST_BAL NUMBER(9,2)PAC_FREEZE_CODE VARCHAR2(1)CREDIT_CARD_FREEZE_CODE VARCHAR2(1)ASSOCIATION_NUMBER! ; VARCHAR2(4)RFS_ASSIGNMENT_DATE DATEOVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)RESERVATION_PENDING VARCHAR2(1)CREDIT_REPORTING_CODE VARCHAR2(2)CANCEL_DEFERMENT_REASON_CODE
RE: doubt
Excuse the heresy for answering an SS question on this list. Jai, you can fight with the INFORMATION_SCHEMA views, or use sp_help to see all the objects for allusers(not just tables). If you looking for just tables, and only for a given user, use select o.namefrom sysobjects o, sysusers u where o.uid = u.uidand o.xtype = 'U'and u.name = 'yourusernamehere' (obviously change the yourusernamehere bit to the username desired). You might want to try the usenet comp.databases.ms-sqlserver group for more info. Ciao Fuzzy (yech ... I'm sorry, the MS brainwashing must be stronger than I thought ... I have this irresistible urge to clap my hands and sway back and forth with a moronic grin on my face after thinking of SQL Server. Can anyone suggest a therapist? :-) :-) :-) ). -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED]Sent: Thursday, January 02, 2003 14:04To: Multiple recipients of list ORACLE-LSubject: doubtDear all, we use "select * from tab" to list all objects in a particular user in oracle. could you tell me the equivalent query in sql server ? Regards Jai
RE: Automatic backup on Oracle 9i -- For Jared
I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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).
RE: Automatic backup on Oracle 9i -- For Jared
Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, January 02, 2003 7:40 AM To: Multiple recipients of list ORACLE-L As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop -- 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).
create view using DBMS.SQL
Dear All,
I can CREATE and EXECUTE others procedures however can not execute procedure
with DBMS_SQL. I'm created procedure has parameters to create a view using
DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some
hints how make it works or any example.
TIA
Truong.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE
PROC_REFRESH_VIEW(pfileno IN VARCHAR2 DEFAULT NULL,
pfilename IN VARCHAR2 DEFAULT NULL,
pfileyear IN NUMBER DEFAULT NULL,
precordtype IN VARCHAR2 DEFAULT NULL)
IS
cursor_name INTEGER;
cursor_id INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,'CREATE OR REPLACE VIEW V_FILTER AS '
||'SELECT * FROM TBL_FILE_DEFINITIONS '
||'WHERE FILE_NUMBER_TX = '||||NVL(pfileno,'DUMMY')||
||' OR FILE_NAME_TX = '||||NVL(pfilename,'DUMMY')||
||' OR FILE_YEAR_NR = '||NVL(pfileyear,1800)
||' OR REC_TYPE_TX = '||||NVL(precordtype,'DUMMY')
||,DBMS_SQL.NATIVE);
cursor_id := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
SHOW ERROR
SQLProcedure created.
SQLNo errors.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
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: ORA-1410 Silliness
Title: Message I found this in the Oracle Concepts manual. Since your table is partitioned I thought it might be worth a look. Queries and Partition Maintenance Operations Queries whose execution starts before invocation of a partition maintenance operation, or before dictionary updates are done during a partition maintenance operation, correctly access via Consistent Read the data of the affected partitions as existing at query snapshot time. Such queries either successfully complete returning all relevant data as present at snapshot time, or fail to complete returning error ORA-8103 or ORA-1410. The application should reissue the query if one of these errors is returned. Queries that use a partitioned index, and that start with some of the index partitions marked as INDEX UNUSABLE, return an error when they actually access one of these partitions for the first time. This happens even if the partition has been made USABLE after query start. Good luck Ed -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of.
Re: Free Shared pool memory
I hadn't heard the historic explanation before, so I'll pass on that. As far as the 16MB is concerned - I believe the free memory includes any free space left in the shared_pool_reserved_size. Since the shared_pool_reserved_size defaults to 5% of the shared_pool_size (I think) it isn't necessarily a surprise that you have 16MB of free memory when your shared_pool size if 320MB. (On the other hand, is the reserved size supposed to be extracted from the main pool, or additional too the main pool) The latching thing is always good for a cop-out. I suspect that v$sgastat would become a major bottle neck if it were always latched and updated in real time. So it seems very likely that it would always be wrong. 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: 02 January 2003 15:13 Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of the statistic free memory was actually a verb and a noun (i.e. as in free Nelson Mandela or free Willy), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time N bytes were freed from the Shared Pool, then the statistic was incremented by N. At least, this explanation would have accounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term free memory became what everyone had thought it was, an adjective and a noun (i.e. as in free beer or free time). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAMEBYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap48124 State objects 267420 message pool freequeue 124552 Checkpoint queue 885168 enqueue_resources 222912 db_files 370988 KGFF heap 649844 KQLS heap 1709904 dictionary cache 12670280 table definiti 3228 transactions 171264 ksfv subheap 4248 fixed allocation callback1280 library cache89490788 simulator trace entries24 sql area187432036 table columns 19520 processes 123380 partitioning d 152976 db_block_buffers 1088 event statistics per sess 607600 -- sum 331067288 SQL show parameter shared_pool_size NAMETYPEVALUE --- --- - shared_pool_sizestring 314572800 I'm curious about the 16,494,488 bytes difference. Is it possible that V$SGASTAT is another unlatched data structure in memory, allowing errors in the interest of eliminating contention? There are other similar structures in the SGA (i.e. the data structure underlying table MONITORING statistics later flushed to SYS.TABMOD$)... Thanks for any and all insight! -- 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: Free Shared pool memory
Title: Message did somebody say free beer?! -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:44 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Free Shared pool memory Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of thestatistic "free memory"was actuallya verb and a noun (i.e. as in "free Nelson Mandela"or "free Willy"), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time"N" bytes were freed from the Shared Pool, then the statistic was incremented by "N". At least, this explanation would haveaccounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term "free memory" became what everyone had thought it was, an adjective and a noun (i.e. as in "free beer" or "free time"). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAME BYTES-- --free memory 18208352miscellaneous 2378964DML locks 12PLS non-lib hp 2096trigger inform 944PL/SQL MPCODE 1146204PL/SQL DIANA 1223360PX subheap 123476db_block_hash_buckets 1411080sessions 377300KGK heap 48124State objects 267420message pool freequeue 124552Checkpoint queue 885168enqueue_resources 222912db_files 370988KGFF heap 649844KQLS heap 1709904dictionary cache 12670280table definiti 3228transactions 171264ksfv subheap 4248fixed allocation callback 1280library cache 89490788simulator trace entries 24sql area 187432036table columns 19520processes 123380partitioning d 152976db_block_buffers 1088event statistics per sess 607600 --sum 331067288 SQL show parameter shared_pool_size NAME TYPE VALUE--- --- -shared_pool_size string 314572800 I'm curious about the 16,494,488 bytes difference. Is it possible that V$SGASTAT is another "unlatched" data structure in memory, allowing errors in the interest of eliminating contention? There are other similar structures in the SGA (i.e. the data structure underlying table MONITORING statistics later flushed to SYS.TABMOD$)... Thanks for any and all insight! - Original Message - From: "Jonathan Lewis" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 3:28 AM Subject: Re: Free Shared pool memory I think it's safe to say that if the free memory is always very large then you can reinterpret it as 'wasted memory'. If the free memory is alway very small, I don't think it is possible to make any decision without know the application. It is possible that you need to increase the shared pool slightly (good app), it is also possible that your shared pool is just about the right size (great app) , but it is possible that your application design has a flaw in it. 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: 02 January 2003 05:39Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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
RE: ORA-1410 Silliness
total speculation since I'm not a PL/SQL expert -- could the amount of data you are manipulating in the stored procedure somehow blow out available memory? --- Koivu, Lisa [EMAIL PROTECTED] wrote: Amount of data, definately. This table grows by ~2.5GB weekly. Have you ever seen data volume begin to cause problems? Thanks Rachel -Original Message- Sent: Tuesday, December 31, 2002 5:05 PM To: Multiple recipients of list ORACLE-L Okay this means that the statement itself, which is being pointed to by the error line, is not the real culprit. Unfortunately, I don't know of a good debugger for PL/SQL that lets you step through line by line. let me rephrase that, I know of no freeware one :) since you say this has only recently started happening, and since the sql runs on its own, what else has changed since the problem started? Amount of data? Other apps on the box taking away memory? Rachel --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hi Rachel, Just tried it and it works. Thanks for your suggestion. Lisa -Original Message- Sent: Tuesday, December 31, 2002 2:19 PM To: Multiple recipients of list ORACLE-L dumb question -- did you extract the insert statement and run it in sqlplus? Does it run there or does it go boom as well? --- Koivu, Lisa [EMAIL PROTECTED] wrote: You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_mart Name Null?Type - -- IDNOT NULL NUMBER(38) CONTRACT_NUMBER NOT NULL NUMBER(9) LOAD_JOB_ID NOT NULL NUMBER(12) JULIAN_RUN_DATE NOT NULL NUMBER(12) FAC_CODE VARCHAR2(1) OWNER_LAST_NAMEVARCHAR2(20) OWNER_FIRST_NAME VARCHAR2(15) OWNER_ADDRESS1 VARCHAR2(25) OWNER_ADDRESS2 VARCHAR2(25) OWNER_CITY VARCHAR2(18) OWNER_STATEVARCHAR2(2) OWNER_ZIP VARCHAR2(9) NET_PURCHASE_PRICE NUMBER(11,2) ORIGINAL_DOWN_PAYMENT NUMBER(11,2) TOTAL_DOWN_PAYMENT NUMBER(11,2) CR_BAL NUMBER(11,2) INTEREST_RATE NUMBER(5,2) FIRST_PAYMENT_DATE DATE QUALIFICATION_CODE VARCHAR2(1) PAYMENT_AMOUNT NUMBER(9,2) PAYMENT_FREQUENCY VARCHAR2(1) AGING_10_TO_30_DAYS_DUENUMBER(9,2) AGING_31_TO_60_DAYS_DUENUMBER(9,2) AGING_61_TO_90_DAYS_DUENUMBER(9,2) ASSIGNED_LOAN_ADMIN_REPVARCHAR2(2) DATE_OF_SALE DATE STATUS_OF_ACCOUNT VARCHAR2(1) CONTRACT_TYPE VARCHAR2(1) WAS_PENDER VARCHAR2(1) CREDIT_LIFE_ON_CONTRACTVARCHAR2(1) DOCUMENT_STATUS_CODE VARCHAR2(1) FIXED_WEEK_SALEVARCHAR2(1) UDI_SALE VARCHAR2(1) PHASE_NUMBER VARCHAR2(6) FAIRSHARE_PLUS_MEMBER VARCHAR2(1) POINTS_OWNED NUMBER(7) DEED_DATE DATE PRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1) RESERVATION_CODE VARCHAR2(4) INTERNATIONAL_CODE VARCHAR2(1) UNIT_PHASE_COMPLETION_CODE VARCHAR2(1) AGING_0_TO_90_DAYS_DUE NUMBER(9,2) AGING_91_TO_120_DAYS_DUE NUMBER(9,2) AGING_121_TO_150_DAYS_DUE NUMBER(9,2) AGING_151_OVER NUMBER(9,2) LOT_LOCATION VARCHAR2(12) PAYMENTS_MADE NUMBER(3) SUPPRESSION_CODE VARCHAR2(1) ACCRUED_INTEREST_BAL NUMBER(9,2) PAC_FREEZE_CODEVARCHAR2(1) CREDIT_CARD_FREEZE_CODEVARCHAR2(1) ASSOCIATION_NUMBER VARCHAR2(4) RFS_ASSIGNMENT_DATEDATE OVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1) RESERVATION_PENDINGVARCHAR2(1) CREDIT_REPORTING_CODE VARCHAR2(2) CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2) EQUITY_IN NUMBER(9,2) DATE_CODED_7 DATE ADJUSTABLE_RATE_MORTGAGE
RE: ORA-1410 Silliness
Title: ORA-1410 Silliness Hi Raj, Thanks for your reply. I always suspected that the line numbers were wrong but since I couldn't say decisively why I had to rely upon what the gui's told me (darn gui...) You proved it. That line is actually pointing to my generic error logging proc. It's going to be a lot easier to drop and recreate the one and only index on my error_log table than to do it on my big huge table. Or just drop the stupid index. How often do I need it anyway? Boy do I feel like a bonehead for not thinking of looking at dba_source... I learned something today. Still, it failed immediately. But I know now to look to my error log table (which has a purge running every day at 6am - there's the deletes) instead of focusing on another. I hadn't even considered this table until now. In true elvis style: "thankyouverymuch" Have a great day everyone. No more silly emails from me for today, anyway. Lisa Koivu Oracle Datatrash Ackministrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 8:31 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Lisa, This is probably nottrue ... Oracle doesn't, has never been able to pinpoint exact line number (in cases such as these) especially with pl/sql packages. I believe the problems can be found by executing following query ... select line, type, source from user_source where name = your package_name) and line between 1960 and 1980 order by line, type / Usually in such cases (as you have described) Oracle will pointto the line which contains the actual INSERT word, because for Oracle that is the LINE-OF-CODE that it is executing. Line numbers from text editors are almost always wrong. Am I off the mark? I know the Vodka was really good, but I did drink lot of coffee too. Please correct me if I am wrong ... 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_martName Null? Type- --ID NOT NULL NUMBER(38)CONTRACT_NUMBER NOT NULL NUMBER(9)LOAD_JOB_ID NOT NULL NUMBER(12)JULIAN_RUN_DATE NOT NULL NUMBER(12)FAC_CODE VARCHAR2(1)OWNER_LAST_NAME VARCHAR2(20)OWNER_FIRST_NAME VARCHAR2(15)OWNER_ADDRESS1 VARCHAR2(25)OWNER_ADDRESS2 VARCHAR2(25)OWNER_CITY VARCHAR2(18)OWNER_STATE VARCHAR2(2)OWNER_ZIP VARCHAR2(9)NET_PURCHASE_PRICE NUMBER(11,2)ORIGINAL_DOWN_PAYMENT NUMBER(11,2)TOTAL_DOWN_PAYMENT NUMBER(11,2)CR_BAL NUMBER(11,2)INTEREST_RATE NUMBER(5,2)FIRST_PAYMENT_DATE DATEQUALIFICATION_CODE VARCHAR2(1)PAYMENT_AMOUNT NUMBER(9,2)PAYMENT_FREQUENCY VARCHAR2(1)AGING_10_TO_30_DAYS_DUE NUMBER(9,2)AGING_31_TO_60_DAYS_DUE NUMBER(9,2)AGING_61_TO_90_DAYS_DUE NUMBER(9,2)ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)DATE_OF_SALE DATESTATUS_OF_ACCOUNT VARCHAR2(1)CONTRACT_TYPE VARCHAR2(1)WAS_PENDER VARCHAR2(1)CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)DOCUMENT_STATUS_CODE VARCHAR2(1)FIXED_WEEK_SALE VARCHAR2(1)UDI_SALE VARCHAR2(1)PHASE_NUMBER VARCHAR2(6)FAIRSHARE_PLUS_MEMBER VARCHAR2(1)POINTS_OWNED NUMBER(7)DEED_DATE DATEPRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)RESERVATION_CODE VARCHAR2(4)INTERNATIONAL_CODE VARCHAR2(1)UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)AGING_0_TO_90_DAYS_DUE NUMBER(9,2)AGING_91_TO_120_DAYS_DUE NUMBER(9,2)AGING_121_TO_150_DAYS_DUE NUMBER(9,2)AGING_151_OVER NUMBER(9,2)LOT_LOCATION VARCHAR2(12)PAYMENTS_MADE NUMBER(3)SUPPRESSION_CODE VARCHAR2(1)ACCRUED_INTEREST_BAL NUMBER(9,2)PAC_FREEZE_CODE VARCHAR2(1)CREDIT_CARD_FREEZE_CODE VARCHAR2(1)ASSOCIATION_NUMBER VARCHAR2(4)RFS_ASSIGNMENT_DATE DATEOVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)RESERVATION_PENDING VARCHAR2(1)CREDIT_REPORTING_CODE VARCHAR2(2)CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2)EQUITY_IN NUMBER(9,2)DATE_CODED_7 DATEADJUSTABLE_RATE_MORTGAGE VARCHAR2(1)NUMBER_OF_PAYS_LEFT
RE: Automatic backup on Oracle 9i -- For Jared
I have never known what lol stood for , I appreciated the general meaning by looking at the context it was used in but know I actually know. I knew that if I perservered long enough with this list long enough I would find something of interest (lol) John -Original Message- Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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).
RE: functions/procedures and commits
Title: Message Or if you set autocommit, in which case a commit is issued every X number of operations. John P WeathermanOracle Database AdministratorReplacements, Ltd. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: functions/procedures and commits No ... an implicit commit is performed 1. If you perform any DDL statement 2. If SQLPLUS you exit without issuing an explicit ROLLBACK. Otherwise, your transaction will remain open awaiting for an explicit commit or rollback. 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: John Dunn [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Subject: functions/procedures and commits Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: log file sync Wait
REPLIES TO YOUR QUESTIONS ARE IN CAPITALS BELOW :- THANKS -Original Message- Sent: Thursday, January 02, 2003 3:59 PM To: Multiple recipients of list ORACLE-L Usual caveat: looking a v$system_event can be very misleading, you need to examine v$session_event to determine if anyone is actually noticing a problem. Usual caveat 2: A statspack report without a time interval is almost meaningless. However, in this case, log file sync at the top is sufficiently unusual to warrant a little hypothesis. SOME STATSPACK OUTPUTS :- Snap Id Snap Time Sessions --- -- Begin Snap:124 31-Dec-02 12:53:001,237 End Snap:133 31-Dec-02 19:12:191,237 Elapsed: 379.32 (mins) Cache Sizes ~~~ db_block_buffers: 20 log_buffer:2097152 db_block_size: 8192shared_pool_size: 157286400 Load Profile Per Second Per Transaction --- --- Redo size:118,042.27 2,007.50 Instance Efficiency Percentages (Target 100%) ~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Wait Events for DB: NCB Instance: ncb Snaps: 124 -133 - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Event Waits Timeouts Time (cs)(ms) /txn -- --- -- -- log file sync 970,563537 2,597,831 270.7 log file parallel write 831,141 23 484,948 60.6 db file sequential read 8,310,890 0 416,355 16.2 log file switch completion 98 0 1,7121750.0 log buffer space 124 0 1,5511250.0 LGWR wait for redo copy 4,785 22 299 10.0 log file single write 123 0 49 40.0 Background Wait Events for DB: NCB Instance: ncb Snaps: 124 -133 - ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event Waits Timeouts Time (cs)(ms) /txn -- --- -- -- log file parallel write 831,123 23 484,945 60.6 log file sequential read 89,714 0 3,418 00.1 Instance Activity Stats for DB: NCB Instance: ncb Snaps: 124 -133 StatisticTotal per Secondper Trans - redo blocks written 5,875,674258.2 4.4 redo buffer allocation retries 218 0.0 0.0 redo entries 6,531,597287.0 4.9 redo log space requests 98 0.0 0.0 redo log space wait time 1,712 0.1 0.0 redo ordering marks 0 0.0 0.0 redo size2,686,523,912118,042.3 2,007.5 redo synch time 2,602,866114.4 1.9 redo synch writes 957,313 42.1 0.7 redo wastage 227,405,300 9,991.9169.9 redo write time 1,185,661 52.1 0.9 redo writer latching time 304 0.0 0.0 redo writes831,047 36.5 0.6 Latch Activity for DB: NCB Instance: ncb Snaps: 124 -133 PctAvg Pct Get Get Slps NoWait NoWait Latch Name Requests Miss /Miss Requests Miss - -- -- -- -- redo allocation8,204,6890.10.10 redo writing 4,932,1770.80.00 Latch Miss Sources for DB: NCB Instance: ncb Snaps: 124 -133 - only latches with sleeps are shown - ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps -- --- --
RE: functions/procedures and commits
Tom, Oracle issues an implicit commit any time DDL is performed or when a quit/exit is issued (Complete Ref p285) or after a set number of commands by using a set autocommit # (Complete Ref 283). With the latter is arguably not truly an implicit commit, as you do set it, DDL most certainly is. FWIW, John P Weatherman Oracle Database Administrator Replacements, Ltd. -Original Message- Sent: Thursday, January 02, 2003 10:10 AM To: Multiple recipients of list ORACLE-L John, there is no such thing as an implicit commit within Oracle. the only implicit commit that I know of is during a sqlplus session when you exit the program. even this is settable by a sqlplus option. distributed transactions that are controlled by a transaction coordinator (like MS DTC) might issue commits only because the web application requires all updates to be handled by the app-server. but this is different from what you are asking, I think. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Weatherman 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: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness Not at this point. I believe when the pl/sql tables get too big it blows up with ora-4031 or one of the common memory errors, I've seen it happen before. I may have only a stupid windows machine, but I have so much RAM... kudos to the brilliant people who didn't listen to me and decided what they were going to give me for hardware. But I digress. I will try it with bringing down the commit interval (which controls the size of the tables) for more giggles. And Waleed - I shudder to think of how long this procedure would take if I wasn't using bulk inserts. It would be forever. I could run this procedure many times over and still be waiting for the conventional insert to complete. At this point I'll live with the ora-1410 before I go that route. Thanks again for your input, both of you -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1410 Silliness total speculation since I'm not a PL/SQL expert -- could the amount of data you are manipulating in the stored procedure somehow blow out available memory? --- Koivu, Lisa [EMAIL PROTECTED] wrote: Amount of data, definately. This table grows by ~2.5GB weekly. Have you ever seen data volume begin to cause problems? Thanks Rachel -Original Message- Sent: Tuesday, December 31, 2002 5:05 PM To: Multiple recipients of list ORACLE-L Okay this means that the statement itself, which is being pointed to by the error line, is not the real culprit. Unfortunately, I don't know of a good debugger for PL/SQL that lets you step through line by line. let me rephrase that, I know of no freeware one :) since you say this has only recently started happening, and since the sql runs on its own, what else has changed since the problem started? Amount of data? Other apps on the box taking away memory? Rachel --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hi Rachel, Just tried it and it works. Thanks for your suggestion. Lisa -Original Message- Sent: Tuesday, December 31, 2002 2:19 PM To: Multiple recipients of list ORACLE-L dumb question -- did you extract the insert statement and run it in sqlplus? Does it run there or does it go boom as well? --- Koivu, Lisa [EMAIL PROTECTED] wrote: You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of. SQL desc vegas_mart Name Null? Type - -- ID NOT NULL NUMBER(38) CONTRACT_NUMBER NOT NULL NUMBER(9) LOAD_JOB_ID NOT NULL NUMBER(12) JULIAN_RUN_DATE NOT NULL NUMBER(12) FAC_CODE VARCHAR2(1) OWNER_LAST_NAME VARCHAR2(20) OWNER_FIRST_NAME VARCHAR2(15) OWNER_ADDRESS1 VARCHAR2(25) OWNER_ADDRESS2 VARCHAR2(25) OWNER_CITY VARCHAR2(18) OWNER_STATE VARCHAR2(2) OWNER_ZIP VARCHAR2(9) NET_PURCHASE_PRICE NUMBER(11,2) ORIGINAL_DOWN_PAYMENT NUMBER(11,2) TOTAL_DOWN_PAYMENT NUMBER(11,2) CR_BAL NUMBER(11,2) INTEREST_RATE NUMBER(5,2) FIRST_PAYMENT_DATE DATE QUALIFICATION_CODE VARCHAR2(1) PAYMENT_AMOUNT NUMBER(9,2) PAYMENT_FREQUENCY VARCHAR2(1) AGING_10_TO_30_DAYS_DUE NUMBER(9,2) AGING_31_TO_60_DAYS_DUE NUMBER(9,2) AGING_61_TO_90_DAYS_DUE NUMBER(9,2) ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2) DATE_OF_SALE DATE STATUS_OF_ACCOUNT VARCHAR2(1) CONTRACT_TYPE VARCHAR2(1) WAS_PENDER VARCHAR2(1) CREDIT_LIFE_ON_CONTRACT VARCHAR2(1) DOCUMENT_STATUS_CODE VARCHAR2(1) FIXED_WEEK_SALE VARCHAR2(1) UDI_SALE VARCHAR2(1) PHASE_NUMBER VARCHAR2(6) FAIRSHARE_PLUS_MEMBER VARCHAR2(1) POINTS_OWNED NUMBER(7) DEED_DATE DATE PRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1) RESERVATION_CODE VARCHAR2(4) INTERNATIONAL_CODE VARCHAR2(1) UNIT_PHASE_COMPLETION_CODE VARCHAR2(1) AGING_0_TO_90_DAYS_DUE NUMBER(9,2) AGING_91_TO_120_DAYS_DUE NUMBER(9,2) AGING_121_TO_150_DAYS_DUE NUMBER(9,2) AGING_151_OVER NUMBER(9,2) LOT_LOCATION VARCHAR2(12) PAYMENTS_MADE NUMBER(3) SUPPRESSION_CODE VARCHAR2(1) ACCRUED_INTEREST_BAL NUMBER(9,2) PAC_FREEZE_CODE VARCHAR2(1) CREDIT_CARD_FREEZE_CODE VARCHAR2(1) ASSOCIATION_NUMBER VARCHAR2(4) RFS_ASSIGNMENT_DATE DATE OVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1) RESERVATION_PENDING VARCHAR2(1) CREDIT_REPORTING_CODE VARCHAR2(2) CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2) EQUITY_IN NUMBER(9,2) DATE_CODED_7 DATE ADJUSTABLE_RATE_MORTGAGE VARCHAR2(1) NUMBER_OF_PAYS_LEFT NUMBER(4) DEFERRED_INTEREST NUMBER(9,2) DEFERRED_PRINCIPAL NUMBER(9,2) CURRENT_YEAR_DEFERMENTS NUMBER(5) CURRENT_YEAR_TOTAL_DEFERMENTS NUMBER(5) LAST_PAYMENT_DATE DATE NEXT_PAYMENT_DATE DATE PAC_DUE_DATE DATE EFT_ROUTING_NUMBER VARCHAR2(8)
RE: Automatic backup on Oracle 9i -- For Jared
I thought lol meant laughing out loud? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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: doubt
Excuse the heresy for answering an SS question on this list. When I first read that statement I was wondering why you would be asking a question about the Nazi SS on an Oracle List . then it dawned on me that you were talking about Microsoft SQL Server ... at which point it did not mater ... Nazi SS ... Microsoft ... prety much the same thing. -Original Message-From: Grant Allen [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 9:20 AMTo: Multiple recipients of list ORACLE-LSubject: RE: doubt Excuse the heresy for answering an SS question on this list. Jai, you can fight with the INFORMATION_SCHEMA views, or use sp_help to see all the objects for allusers(not just tables). If you looking for just tables, and only for a given user, use select o.namefrom sysobjects o, sysusers u where o.uid = u.uidand o.xtype = 'U'and u.name = 'yourusernamehere' (obviously change the yourusernamehere bit to the username desired). You might want to try the usenet comp.databases.ms-sqlserver group for more info. Ciao Fuzzy (yech ... I'm sorry, the MS brainwashing must be stronger than I thought ... I have this irresistible urge to clap my hands and sway back and forth with a moronic grin on my face after thinking of SQL Server. Can anyone suggest a therapist? :-) :-) :-) ). -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED]Sent: Thursday, January 02, 2003 14:04To: Multiple recipients of list ORACLE-LSubject: doubtDear all, we use "select * from tab" to list all objects in a particular user in oracle. could you tell me the equivalent query in sql server ? Regards Jai
RE: ORA-1410 Silliness
Title: ORA-1410 Silliness Lisa, The answer is very, very simple...Don't take a day off! No day off...no error...no failed loads... Seriously, it sounds like either an index corruption (temporary) or an oracle bug. Is there anything in the alert log or in trace files? Try setting a trap on the specific error and examine the trace file. Are you using direct loads? Is the phrase 'WHERE CURRENT OF' being used by the package or other functions that are being called? Dan Fink Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-1410 Silliness Hello all, 8.1.7, Windows 2000 SP2 Here's the error: * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 ORA-01410: invalid ROWID ORA-06512: at line 1 Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem: 1. No code references ROWID. Deletes are never applied to this table. 2. No inline views in any of the code. 3. Ran dbv on all datafiles while database was down. No problems identified. 4. Ran analyze table table name validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table. 5. Was able to export the entire table without any problem. I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import. Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens. Any ideas, suggestions, or thoughts are appreciated. Thanks everyone. Lisa Koivu Oracle Dorkbase Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Re[2]: Automatic backup on Oracle 9i -- For Jared
R I have philosophical trouble with it. I dislike the abbreviations. I R will use abbreviations to condense phrases (lol for lots of laughs) but R I really dislike seeing you written as u. It's not that hard to R type the extra two letters. And all this time I thought lol was laughing out loud. I guess I should have taken the class -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Eskridge 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 -- For Jared
Hmm, English really isn't my mother tongue, but I don't like such cryptic notations in English as well as in Latvian. It makes language more meagre and poor. Of course I also make mistakes but these are caused by my lack of knowledge and I can always accept them from another people. Gints Plivna -Original Message- Sent: Thursday, January 02, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, January 02, 2003 7:40 AM To: Multiple recipients of list ORACLE-L As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna 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: create view using DBMS.SQL
Donate:
Does the owner of the procedure have system privilege granted directly to
them to create a view? Not with a role , but with the system privilege
granted directly to the procedure owner. Remember, roles are disabled
inside a stored procedure.
RWB
Donate Clothes [EMAIL PROTECTED]@fatcity.com on 01/02/2003
09:30:13 AM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Dear All,
I can CREATE and EXECUTE others procedures however can not execute
procedure
with DBMS_SQL. I'm created procedure has parameters to create a view using
DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some
hints how make it works or any example.
TIA
Truong.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE
PROC_REFRESH_VIEW(pfileno IN VARCHAR2 DEFAULT NULL,
pfilename IN VARCHAR2 DEFAULT NULL,
pfileyear IN NUMBER DEFAULT NULL,
precordtype IN VARCHAR2 DEFAULT NULL)
IS
cursor_name INTEGER;
cursor_id INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,'CREATE OR REPLACE VIEW V_FILTER AS '
||'SELECT * FROM TBL_FILE_DEFINITIONS '
||'WHERE FILE_NUMBER_TX = '||||NVL(pfileno,'DUMMY')||
||' ORFILE_NAME_TX = '||||NVL(pfilename,'DUMMY')||
||' OR FILE_YEAR_NR = '||NVL(pfileyear,1800)
||' OR REC_TYPE_TX = '||||NVL(precordtype,'DUMMY')
||,DBMS_SQL.NATIVE);
cursor_id := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
SHOW ERROR
SQLProcedure created.
SQLNo errors.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
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: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness I am no pl/sql expert either, but me think that would cause a ORA-4031 ... been there ... done that ... before I learned to code in a better way. 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: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1410 Silliness total speculation since I'm not a PL/SQL expert -- could the amount of data you are manipulating in the stored procedure somehow blow out available memory? *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: Automatic backup on Oracle 9i -- For Jared
Title: RE: Automatic backup on Oracle 9i -- For Jared Thanks Dennis, It isn't my first language either (I started learning English in the 5th grade in India). I have seen this language before, and like Rachel said, it is more of a philosophical trouble. I used similar language in precisely 3 (company internal) emails (waay back in 1993) before my manager called me in his office and explained that if I wish to be a better programmer/analyst/what-ever-that-I wish-to-be I need to be clear in my thoughts, when I speak and when I write. I still think it is a valuable advise. At-least I am not the only one ... Happy New Year Everyone ... 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: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Automatic backup on Oracle 9i -- For Jared Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. *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: create view using DBMS.SQL
Title: RE: create view using DBMS.SQL
Is the create view privilege granted to the procedure owner?
-Original Message-
From: Donate Clothes [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject: create view using DBMS.SQL
Dear All,
I can CREATE and EXECUTE others procedures however can not execute procedure
with DBMS_SQL. I'm created procedure has parameters to create a view using
DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some
hints how make it works or any example.
TIA
Truong.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE
PROC_REFRESH_VIEW(pfileno IN VARCHAR2 DEFAULT NULL,
pfilename IN VARCHAR2 DEFAULT NULL,
pfileyear IN NUMBER DEFAULT NULL,
precordtype IN VARCHAR2 DEFAULT NULL)
IS
cursor_name INTEGER;
cursor_id INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,'CREATE OR REPLACE VIEW V_FILTER AS '
||'SELECT * FROM TBL_FILE_DEFINITIONS '
||'WHERE FILE_NUMBER_TX = '||||NVL(pfileno,'DUMMY')||
||' OR FILE_NAME_TX = '||||NVL(pfilename,'DUMMY')||
||' OR FILE_YEAR_NR = '||NVL(pfileyear,1800)
||' OR REC_TYPE_TX = '||||NVL(precordtype,'DUMMY')
||,DBMS_SQL.NATIVE);
cursor_id := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
SHOW ERROR
SQLProcedure created.
SQLNo errors.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
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: functions/procedures and commits
Tom, I must respectfully disagree. Explicit = a COMMIT or ROLLBACK is explicitly issued by the user/program. Implicit = a COMMIT or ROLLBACK is performed as the result of an action and not issued by the user/program. The SQL*Plus options AUTOCOMMIT and COPYCOMMIT do not control EXIT behavior. For the EXIT/QUIT commands, the default behavior is COMMIT. With WHENEVER OSERROR or SQLERROR, the behavior can also be altered to perform a COMMIT or ROLLBACK. Is there another option you are thinking of? COMMIT or ROLLBACK is tied very closely to TRANSACTIONs. In order to start a new transaction, the previous transaction must end. A COMMIT indicates a succesful end, while a ROLLBACK indicates a failure. Please keep in mind that this is not always coded in this manner, i.e. unhandled exceptions in PL/SQL followed by COMMIT in calling procs. DDL exists as a separate transaction. In order to start the DDL transaction, the previous transaction must end. Oracle terminates the previous transaction by COMMITting the changes and begins a new tx. I used a small anonymous PL/SQL block to test COMMIT/ROLLBACK behavior. I found it to be consistent with how I left SQL*Plus. If I killed the window, the change was not committed. If I typed 'EXIT', the change was committed. Dan Fink -Original Message- Sent: Thursday, January 02, 2003 8:10 AM To: Multiple recipients of list ORACLE-L John, there is no such thing as an implicit commit within Oracle. the only implicit commit that I know of is during a sqlplus session when you exit the program. even this is settable by a sqlplus option. distributed transactions that are controlled by a transaction coordinator (like MS DTC) might issue commits only because the web application requires all updates to be handled by the app-server. but this is different from what you are asking, I think. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan 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).
Direct privs vs Acess thru a role
Hello, How does Direct grant access differs from an access thru a role? and *WHY*? Thanks. OraCop __ 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: OraCop 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: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness Lisa, try to see if you can (I think you should) use dbms_session.free_unused_user_memory ... this is very handy for applications where large pl/sql tables (oops ... arrays) are used frequently. 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Not at this point. I believe when the pl/sql tables get too big it blows up with ora-4031 or one of the common memory errors, I've seen it happen before. I may have only a stupid windows machine, but I have so much RAM... kudos to the brilliant people who didn't listen to me and decided what they were going to give me for hardware. But I digress. I will try it with bringing down the commit interval (which controls the size of the tables) for more giggles. And Waleed - I shudder to think of how long this procedure would take if I wasn't using bulk inserts. It would be forever. I could run this procedure many times over and still be waiting for the conventional insert to complete. At this point I'll live with the ora-1410 before I go that route. Thanks again for your input, both of you *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: Free Shared pool memory
Title: RE: Free Shared pool memory Tim, I am pretty sure you are aware of Metalink note 100666.1 where it says that the free_memory value is unreliable if shared_pool_reserved_size is a non-zero value. Bug# 370903 .. BTW This is what it is on 9202 ... oraclei@elara-NCS1 sys SQL*Plus: Release 9.2.0.2.0 - Production on Thu Jan 2 11:22:09 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL show sga Total System Global Area 128032 bytes Fixed Size 742688 bytes Variable Size 973078528 bytes Database Buffers 268435456 bytes Redo Buffers 2191360 bytes SQL SELECT SUM(bytes)/1024/1024 FROM v$sgastat WHERE pool = 'shared pool'; SUM(BYTES)/1024/1024 848 SQL SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared pool' ; SUM(BYTES) -- 889192448 SQL show parameter shared NAME TYPE VALUE --- -- hi_shared_memory_address integer 0 max_shared_servers integer 20 shared_memory_address integer 0 shared_pool_reserved_size big integer 104857600 shared_pool_size big integer 771751936 shared_server_sessions integer 0 shared_servers integer 0 SQL exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production __ 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: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Subject: Re: Free Shared pool memory I hadn't heard the historic explanation before, so I'll pass on that. As far as the 16MB is concerned - I believe the free memory includes any free space left in the shared_pool_reserved_size. Since the shared_pool_reserved_size defaults to 5% of the shared_pool_size (I think) it isn't necessarily a surprise that you have 16MB of free memory when your shared_pool size if 320MB. (On the other hand, is the reserved size supposed to be extracted from the main pool, or additional too the main pool) The latching thing is always good for a cop-out. I suspect that v$sgastat would become a major bottle neck if it were always latched and updated in real time. So it seems very likely that it would always be wrong. 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: 02 January 2003 15:13 Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of the statistic free memory was actually a verb and a noun (i.e. as in free Nelson Mandela or free Willy), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time N bytes were freed from the Shared Pool, then the statistic was incremented by N. At least, this explanation would have accounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term free memory became what everyone had thought it was, an adjective and a noun (i.e. as in free beer or free time). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAME BYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform 944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap 48124 State objects 267420 message pool freequeue 124552 Checkpoint queue 885168 enqueue_resources 222912 db_files 370988 KGFF heap 649844 KQLS heap 1709904 dictionary cache 12670280 table definiti 3228 transactions 171264 ksfv subheap 4248 fixed allocation callback 1280 library cache 89490788 simulator trace entries 24 sql area 187432036 table columns 19520 processes 123380 partitioning d 152976
RE: Automatic backup on Oracle 9i -- For Jared
Well, I understand that writing you is not much trouble compared to writing U, but understanding U, should not be much trouble either. Don't U think so? OraCop. --- [EMAIL PROTECTED] wrote: I have never known what lol stood for , I appreciated the general meaning by looking at the context it was used in but know I actually know. I knew that if I perservered long enough with this list long enough I would find something of interest (lol) John -Original Message- Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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). __ 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: OraCop 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: create view using DBMS.SQL
Title: RE: create view using DBMS.SQL ummm directly? 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 11:46 AMTo: Multiple recipients of list ORACLE-LSubject: RE: create view using DBMS.SQL Is the create view privilege granted to the procedure owner? 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: Free Shared pool memory
The SHARED_POOL_RESERVED_SIZE is indeed defaulting to 15728640, which is 5% exactly of SHARED_POOL_SIZE... The reserved area is subtracted from the Shared Pool, so subtracting that amount from the difference still leaves 765,848 bytes. As a number, that doesn't divide by any of the powers-of-2 (i.e. 1024, 512, 256, etc) until you get down to 8, 4, and 2, so it kind of bugs me... Another possible explanation is that SHARED_POOL_SIZE is not the actual size of the Shared Pool, but rather a starting point to which Oracle adds extra space for some reason? The unlatched data structure explanation might be a cop-out, but I get suspicious when I see a statistic named miscellaneous, which in itself is a cop-out for a database engine. Miscellaneous? You've got to be kidding! Life is miscellaneous when you step back... :-) I hadn't heard the historic explanation before, so I'll pass on that. As far as the 16MB is concerned - I believe the free memory includes any free space left in the shared_pool_reserved_size. Since the shared_pool_reserved_size defaults to 5% of the shared_pool_size (I think) it isn't necessarily a surprise that you have 16MB of free memory when your shared_pool size if 320MB. (On the other hand, is the reserved size supposed to be extracted from the main pool, or additional too the main pool) The latching thing is always good for a cop-out. I suspect that v$sgastat would become a major bottle neck if it were always latched and updated in real time. So it seems very likely that it would always be wrong. 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: 02 January 2003 15:13 Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of the statistic free memory was actually a verb and a noun (i.e. as in free Nelson Mandela or free Willy), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time N bytes were freed from the Shared Pool, then the statistic was incremented by N. At least, this explanation would have accounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term free memory became what everyone had thought it was, an adjective and a noun (i.e. as in free beer or free time). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAMEBYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap48124 State objects 267420 message pool freequeue 124552 Checkpoint queue 885168 enqueue_resources 222912 db_files 370988 KGFF heap 649844 KQLS heap 1709904 dictionary cache 12670280 table definiti 3228 transactions 171264 ksfv subheap 4248 fixed allocation callback1280 library cache89490788 simulator trace entries24 sql area187432036 table columns 19520 processes 123380 partitioning d 152976 db_block_buffers 1088 event statistics per sess 607600 -- sum 331067288 SQL show parameter shared_pool_size NAMETYPEVALUE --- --- - shared_pool_sizestring 314572800 I'm curious about the 16,494,488 bytes difference. Is it possible that V$SGASTAT is another
RE: Automatic backup on Oracle 9i -- For Jared
Although I hate to make my first submission to The List in 2003 as negative one, I would still like to suggest that text messages have no place in a forum such as this. Quite apart from being needless, such a message format shows a total lack of respect for the recipient and a distinct arrogance towards technology by trying to interchange a message format between totally different media. Even if the message in question was indeed in an acceptable format, the content clearly shows that no effort has been made to do any research on the subject matter. I have a deep affection and respect for this List and how there is such readily available help from it. So OraCop, I would say that you should try a more considered approach and you may be pleasantly surprised. -Original Message- WILLIAMS Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, January 02, 2003 7:40 AM To: Multiple recipients of list ORACLE-L As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop -- 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). smime.p7s Description: application/pkcs7-signature
RE: Automatic backup on Oracle 9i -- For Jared
Even though i strongly think that there is nothing wrong in writing U instead of you, respecting your feelings towards the list, and considering the fact that we must think above such small matters, I will be careful in future. Thanks. OraCop. --- Martin Kendall [EMAIL PROTECTED] wrote: Although I hate to make my first submission to The List in 2003 as negative one, I would still like to suggest that text messages have no place in a forum such as this. Quite apart from being needless, such a message format shows a total lack of respect for the recipient and a distinct arrogance towards technology by trying to interchange a message format between totally different media. Even if the message in question was indeed in an acceptable format, the content clearly shows that no effort has been made to do any research on the subject matter. I have a deep affection and respect for this List and how there is such readily available help from it. So OraCop, I would say that you should try a more considered approach and you may be pleasantly surprised. -Original Message- WILLIAMS Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, January 02, 2003 7:40 AM To: Multiple recipients of list ORACLE-L As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop -- 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). ATTACHMENT part 2 application/x-pkcs7-signature name=smime.p7s __ 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: OraCop 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 -- For Jared
lol = laughing out loud lots of luck, lots of love, lots of laughs, little old lady. I will stop here now :) Richard Ji -Original Message- Sent: Thursday, January 02, 2003 11:14 AM To: Multiple recipients of list ORACLE-L I thought lol meant laughing out loud? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 10:30 AM To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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). -- 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: ORA-28030: Server encountered problems accessing LDAP directory service
Yes, that's correct. That is what the error message says. Anyone have any idea why it's trying to use LDAP to resolve the database link? I thought with my sqlnet.ora file set to use tnsnames.ora I'd be using my local files to resolve the service name. Any suggestions as to why it's trying to use LDAP are welcome. David -Original Message-From: ora ak [mailto:[EMAIL PROTECTED]]Sent: Tuesday, December 31, 2002 1:59 PMTo: Multiple recipients of list ORACLE-LSubject: Re: ORA-28030: Server encountered problems accessing LDAP directory service Looks like are trying to resolve service name using LDAP naming . David Mitchell [EMAIL PROTECTED] wrote: I've recently inherited an Oracle 8.1.6 system and was just trying tosetup a database link between two instances. I've granted the "createdatabase link" priv to the user account and can successfully create thelink but when I try to do a "select from table_name@linked_db;" I getthe following error:ERROR at line 1:ORA-28030: Server encountered problems accessing LDAP directory serviceEarlier I had a TNS error and found that my tnsnames.ora file wasmissing one of my instances. I fixed that problem and tnsping nowresponds correctly but I'm still getting the error listed on the subjectline. My sqlnet.ora file contains "NAMES.DIRECTORY_PATH= (TNSNAMES)" soeverything should be set to use my local tnsnames.ora file. I've beengoing through Oracle Net 8 documentation but still haven't foundanything. Does anyone have any suggestions? Thanks..! .David-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: David MitchellINET: [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). Do you Yahoo!?Yahoo! Mail Plus - Powerful. Affordable. Sign up now
RE: Automatic backup on Oracle 9i -- For Jared
Don't forget a lot of people uses PDA or wireless devices which has limitations on how many characters can be trasmitted. SMS for instance allows only 160 characters. Now why would someone write from such a device to ask an Oracle question is beyond me. Perhaps all the machines are down? :) With more people start to use wireless phones, PDAs, we will see more messages like this. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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).
NVL and index usage
Title: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). 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! *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
Best upgrade path Linux / 8.1.7
List:
We have a Red Hat Linux release 7.1 system with 8.1.7.0 installed. We're encountering the memory leak bug
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","begin dbms_output.disable; e...","PL/SQL MPCODE","BAMIMA: Bam Buffer")
I need to upgrade to at least 8.1.7.2 (and I cannot go to 9). Stability for this database is paramount, and is more important than additional functionality.
Any advice on the most stable Linux version for 8.1.7 on Red Hat? Should I go all the way up to 8.1.7.4?
Thanks for any advice.
BarbDo you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: Re: Free Shared pool memory
Jonathan Lewis, Hi, lewis,have you ever see any big system with large shared pool size? This week I saw a mobile telecom system running ops 8163, have 8GB sga, with 3GB of shared_pool_size and About 5GB of data buffer.(Physical memory is 16GB) I never configured a system with shared_pool_size larger than 200MB(my current system is 200MB).But that oracle is installed by oracle china, so i wonder if this kind of configuration is reasonable? I do not have enough time to analyze that system, but I do not think Oracle china is giving the customer the right parameter about shared_pool, can you give your opinion? Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(ChinaOracle User Group) === 2003-01-02 02:28:00 ,you wrote£º=== I think it's safe to say that if the free memory is always very large then you can reinterpret it as 'wasted memory'. If the free memory is alway very small, I don't think it is possible to make any decision without know the application. It is possible that you need to increase the shared pool slightly (good app), it is also possible that your shared pool is just about the right size (great app) , but it is possible that your application design has a flaw in it. 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: 02 January 2003 05:39 Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: 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). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: create view using DBMS.SQL
Title: RE: create view using DBMS.SQL Right. Forgot that... -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 12:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: create view using DBMS.SQL ummm directly? 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 11:46 AMTo: Multiple recipients of list ORACLE-LSubject: RE: create view using DBMS.SQL Is the create view privilege granted to the procedure owner?
RE: Re: Free Shared pool memory
How does this grab you? FTLP show sga Total System Global Area 2.2596E+10 bytes Fixed Size 103396 bytes Variable Size1120354304 bytes Database Buffers 2.1475E+10 bytes Redo Buffers1064960 bytes Got a bad application? Throw more hardware at it! QRO! QRO! -Original Message- can you give your opinion? -- 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).
RE: ORA-28030: Server encountered problems accessing LDAP directory service
Title: RE: ORA-28030: Server encountered problems accessing LDAP directory service
Yes. This was indeed the case. I was using
"current_user" to create the link. I just dropped and re-created the link
using an named user account and the link works fine now. Thanks for the
quick response!
David
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 9:57 AMTo: David MitchellSubject: RE: ORA-28030:
Server encountered problems accessing LDAP directory
service
Displayed below are the messages of the selected thread.
Thread Status: Closed From: TABAINET
MOHAMED 01-Aug-02 14:54 Subject: ORA-28030 Server
encountered problems accessing LDAP directory service
RDBMS Version:: 8.1.7.0 Operating
System and Version:: windows NT4 Error Number (if
applicable):: ORA-28030 Server encountered problems accessing LDAP directory
service Server Net Version:: 8.1.7.0 Client Operating System and Version:: 8.1.7.0 Client Net Version:: 8.1.7.0
ORA-28030 Server encountered problems accessing LDAP directory
service
I have 2 servers def03 and def64 I
have created a dblink in def03 to connect to def64 I
deinstalled Oracle advanced security in def03. but when I want to connect to
database in def64 using dblink whith current_user option, i have this message
ORA-28030 Server encountered problems accessing LDAP directory
service
SQL select name,value from v$parameter 2 where name in ('db_domain','db_name','global_names');
NAME VALUE --
db_domain global_names FALSE
db_name THORATFE
any helps please
morad
From: Oracle, MICHAEL SEIBT 01-Aug-02 16:09 Subject: Re : ORA-28030 Server encountered problems accessing LDAP
directory service
This is a bug (954379)
Workaround: ===
Don't use current_user or create user identified
globally. OR Set
_ENT_DOMAIN_NAME to ANY value in init.ora file for dblinks which use
current_user.
Is this your case ?? 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: David Mitchell [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-28030: Server encountered problems accessing LDAP
directory service
Yes, that's correct. That is what the error message
says. Anyone have any idea why it's trying to use LDAP to resolve the
database link? I thought with my sqlnet.ora file set to use tnsnames.ora
I'd be using my local files to resolve the service name. Any suggestions
as to why it's trying to use LDAP are welcome.
David
RE: Automatic backup on Oracle 9i -- For Jared
Maybe it's time to bring back all those Morse code Q abbreviations with appropriate adaptation to IT industry. Instead of QRO meaning increase your power, it might mean Here's a nickel kid, get yourself a better computer. I don't think you will ever see QRP used (reduce your power). -Original Message- With more people start to use wireless phones, PDAs, we will see more messages like this. -- 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).
RE: ORA-1410 Silliness
Title: RE: ORA-1410 Silliness Thanks again Raj. I will definately look into it. Lisa -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Lisa, try to see if you can (I think you should) use dbms_session.free_unused_user_memory ... this is very handy for applications where large pl/sql tables (oops ... arrays) are used frequently. 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: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: RE: ORA-1410 Silliness Not at this point. I believe when the pl/sql tables get too big it blows up with ora-4031 or one of the common memory errors, I've seen it happen before. I may have only a stupid windows machine, but I have so much RAM... kudos to the brilliant people who didn't listen to me and decided what they were going to give me for hardware. But I digress. I will try it with bringing down the commit interval (which controls the size of the tables) for more giggles. And Waleed - I shudder to think of how long this procedure would take if I wasn't using bulk inserts. It would be forever. I could run this procedure many times over and still be waiting for the conventional insert to complete. At this point I'll live with the ora-1410 before I go that route. Thanks again for your input, both of you
Centralized StatsPack Repository
To ALL, We'd like to establish a centralized stats pack repository, but OTS is telling us that it's NOT doable for a couple of reasons. As you can guess I don't believe them and am looking around to see if anyone else has done this before I break out the power tools and start building something on my own. Dick Goulet -- 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: Direct privs vs Acess thru a role
Dear whateveristhenamebehindyourstrangealias, I'm not - I really am not - trying to be impolite, but have you looked this up in the Oracle documentation? I beleive it's all nicely explained there. Correct me if I'm wrong (in other words: A more specific question ...) Best regards, OraFlop aka Mogens OraCop wrote: Hello, How does Direct grant access differs from an access thru a role? and *WHY*? Thanks. OraCop __ 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: NVL and index usage
Title: NVL and index usage In a quick test on 9013 it changed the index which it was using and went from Range scan to fast full scan. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, January 02, 2003 9:48 AM Subject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). 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!
RE: functions/procedures and commits
Title: Message The behavior of this may have changed... but I have seen autocommit not work as advertised. Just my opinion but I think explicit commits are good practice, if nothing else just for ease of reading code. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message-From: John Weatherman [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 10:41 AMTo: Multiple recipients of list ORACLE-LSubject: RE: functions/procedures and commits Or if you set autocommit, in which case a commit is issued every X number of operations. John P WeathermanOracle Database AdministratorReplacements, Ltd. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: functions/procedures and commits No ... an implicit commit is performed 1. If you perform any DDL statement 2. If SQLPLUS you exit without issuing an explicit ROLLBACK. Otherwise, your transaction will remain open awaiting for an explicit commit or rollback. 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: John Dunn [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Subject: functions/procedures and commits Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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: NVL and index usage
It won't be able to use the index as far as I know. Unless it's a functional index ;). Mogens Jamadagni, Rajendra wrote: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). 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! *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: NVL and index usage
Title: RE: NVL and index usage Thanks ... the development is rolling out a new change by adding a new nullable column to a table and adding following to all appropriate queries ... and nvl(new_column,'A') = nvl(some_value,'A') I learned of this few minutes ago and luckily they are releasing it to development tomorrow. Thanks once again 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: Thursday, January 02, 2003 1:22 PM To: Multiple recipients of list ORACLE-L Subject: Re: NVL and index usage It won't be able to use the index as far as I know. Unless it's a functional index ;). 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: Centralized StatsPack Repository
Title: RE: Centralized StatsPack Repository I am currently working on a design ... basically it is simple to set-up, but the problem I am facing is how to automatically move dataset for one snapshot from prod db to the central db. 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Subject: Centralized StatsPack Repository To ALL, We'd like to establish a centralized stats pack repository, but OTS is telling us that it's NOT doable for a couple of reasons. As you can guess I don't believe them and am looking around to see if anyone else has done this before I break out the power tools and start building something on my own. Dick Goulet -- 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). *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: Automatic backup on Oracle 9i -- For Jared
Dnt knw wht al the fss is abt. Why nt just drp al th vwls? V cn al undrstd tht cn't v? ;-) mhmd --- OraCop [EMAIL PROTECTED] wrote: Well, I understand that writing you is not much trouble compared to writing U, but understanding U, should not be much trouble either. Don't U think so? OraCop. --- [EMAIL PROTECTED] wrote: I have never known what lol stood for , I appreciated the general meaning by looking at the context it was used in but know I actually know. I knew that if I perservered long enough with this list long enough I would find something of interest (lol) John -Original Message- Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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). __ 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: OraCop 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
Re: Free Shared pool memory
I'm probably telling you guys something you already know, but at least it gives me a chance to write something on the list... Notice that the show sga command output doesn't contain a line saying shared pool size, but instead says variable size. That's because it's the size of the variable part of the SGA, which includes the row/dictionary cache, the library cache (usually together referred to as the shared pool) plus a number of other structures including the hash buckets and the buffer header structures of the buffer cache, the lru/lruw/whatever structures and more. Therefor Variable Size will always be bigger than shared pool size. I hope this answered somebody's question somewhere ;-) Mogens Jonathan Lewis wrote: I hadn't heard the historic explanation before, so I'll pass on that. As far as the 16MB is concerned - I believe the free memory includes any free space left in the shared_pool_reserved_size. Since the shared_pool_reserved_size defaults to 5% of the shared_pool_size (I think) it isn't necessarily a surprise that you have 16MB of free memory when your shared_pool size if 320MB. (On the other hand, is the reserved size supposed to be extracted from the main pool, or additional too the main pool) The latching thing is always good for a cop-out. I suspect that v$sgastat would become a major bottle neck if it were always latched and updated in real time. So it seems very likely that it would always be wrong. 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: 02 January 2003 15:13 Sorry for being so vague, but sometimes I can't help it... It was my understanding in the Oracle7 days that the name of the statistic free memory was actually a verb and a noun (i.e. as in free Nelson Mandela or free Willy), and the number shown alongside this statistic was the cumulative number of bytes freed in the Shared Pool. In other words, every time N bytes were freed from the Shared Pool, then the statistic was incremented by N. At least, this explanation would have accounted for the absurdly huge numbers seen in the V$SGASTAT view for this statistic in those versions and the unreliability in attempting to add the numbers seen in V$SGASTAT to sum to SHARED_POOL_SIZE... Then, sometime in the Oracle8 or Oracle8i timeframe, the meaning of the statistic was changed so that the term free memory became what everyone had thought it was, an adjective and a noun (i.e. as in free beer or free time). A much more useful statistic, certainly... Is this true? If not, is it close? The sum of the information in V$SGASTAT still does not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown below): SQL select name, bytes from v$sgastat 2 where pool = 'shared pool'; NAMEBYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap48124 State objects 267420 message pool freequeue 124552 Checkpoint queue 885168 enqueue_resources 222912 db_files 370988 KGFF heap 649844 KQLS heap 1709904 dictionary cache 12670280 table definiti 3228 transactions 171264 ksfv subheap 4248 fixed allocation callback1280 library cache89490788 simulator trace entries24 sql area187432036 table columns 19520 processes 123380 partitioning d 152976 db_block_buffers 1088 event statistics per sess 607600 -- sum 331067288 SQL show parameter shared_pool_size NAMETYPEVALUE --- --- - shared_pool_sizestring 314572800 I'm curious about the 16,494,488 bytes difference. Is it possible that V$SGASTAT is another unlatched data structure in memory, allowing errors in the interest of eliminating contention? There are other similar structures in the SGA (i.e. the data structure underlying table MONITORING statistics later flushed to SYS.TABMOD$)... Thanks for any
RE: Automatic backup on Oracle 9i -- For Jared
Lighten up Frances -Original Message- Sent: Thursday, January 02, 2003 10:46 AM To: Multiple recipients of list ORACLE-L Although I hate to make my first submission to The List in 2003 as negative one, I would still like to suggest that text messages have no place in a forum such as this. Quite apart from being needless, such a message format shows a total lack of respect for the recipient and a distinct arrogance towards technology by trying to interchange a message format between totally different media. Even if the message in question was indeed in an acceptable format, the content clearly shows that no effort has been made to do any research on the subject matter. I have a deep affection and respect for this List and how there is such readily available help from it. So OraCop, I would say that you should try a more considered approach and you may be pleasantly surprised. -Original Message- WILLIAMS Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L Raj - I go through episodes where I get pretty frustrated with the cryptic language, but then I take a deep breath and remember that for some people English isn't their first language. Also, I think text messages on cell phones are changing the way many people deal with English. I also get irritated with people who won't post their names. It is hard to reply to an anonymous person. But then I recall that I previously worked for a company that wouldn't let me post to newsgroups. It would be easier if people would pick a nom de plume that was more human, like Joe Smith. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, January 02, 2003 7:40 AM To: Multiple recipients of list ORACLE-L As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop -- 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). -- 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: Buffer Pool Testing
So it seems like my problem is the full table scan. Craig Shallahammer mentions this in his All About Oracle's Touch-Count Data Block Buffer Algoithm paper - The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once. Using my second example (query ALRA_TRANSACTION_HISTORY then WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans. I still expected multiple queries against a table (full-scan or otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way. Thanks, Jay [EMAIL PROTECTED] 01/02/03 10:20AM I think you are seeing expected behaviour. Blocks subject to tablescan are loaded in to the LRU end of the cache, even when using a RECYCLE cache. However, if there are free blocks in the cache (state = 0) Oracle uses those rather than flushing other blocks. Consequently, when you startup and scan a 400 block table with a 1,000 block cache, the whole 400 blocks will get into memory. Then the next 10,000 block scan will start by using the last 600 blocks of the cache before recycling the last db_file_multiblock_read_count blocks. By this time, though, the 1st 400 blocks are at the MRU end of the chain, and are not moved by the subsequent tablescan. If you start with the 10,000 block scan, the whole cache is filled. The second scan then keeps recycling the last db_file_multiblock_read_count blocks (though in your case I guess it's plus one - possibly a cleanout block, possibly the segment header block which may go into the Default pool in v9 - without pushing out any more of the first 1,000 blocks from the first scan. Periods of time shortly after startup are always likely to show anomalous behaviour. 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: 31 December 2002 19:31 I'm spending some time today experimenting with buffer pools in 8.1.7. I have two tables that I have assigned to the RECYCLE pool. I have been running various queries that perform full table scans, then checking the buffers to see what gets aged out. During my testing, it seems like the first blocks to get into the RECYCLE buffer pool will stay there. The following two tables are assigned to the RECYCLE pool. No other segments are assigned to it: WORK_ORDER_STEP - 428 blocks of data ALRA_TRANSACTION_HISTORY - 14152 blocks of data The RECYCLE pool has 1000 blocks. I startup the database, query the WORK_ORDER_STEP table (1 time), then run multiple queries against the ALRA_TRANSACTION_HISTORY table (6 times), I see the following in the buffers (the source for this query is at the end of my email): BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 56914 0 .02 RECYCLE WRKORD WORK_ORDER_STEP 431 1 0 .00 If I startup the database, query the ALRA_TRANSACTION_HISTORY table (1 time), then run multiple queries against the WORK_ORDER_STEP table (6 queries), I get these results: First query - 1000 blocks are used as expected BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 1000 2 0 .00 After querying the second table multiple times, I expected more than just 9 blocks to be given up. I expected more like 431 blocks. BP_NAME OBJ_OWNER NAME BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH --- -- - - - - - RECYCLE BIS ALRA_TRANSACTION_HISTORY 991 2 0 .00 RECYCLE WRKORD WORK_ORDER_STEP 9 4 0 .44 I expected the blocks (from the table that was queried first) to be aged out as I queried the second table (over and over). This does not occur. Am I hitting a bug or just misunderstanding the buffer management algorithms? ALRA_TRANSACTION_HISTORY blocks should be LRU as I hit the WORK_ORDER_STEP table over and over. Thanks, Jay **DISCLAIMER This
RE: functions/procedures and commits
Dan, I agree with you and I think I said the same thing - though not as lengthy! :) As you said, the Sql*Plus AutoCommit option controls issuing a commit upon exiting the program. As I said in a prior mail, I did not mention DDL forcing an implicit commit as the question pertained to INSERT statements. At least I got people answering the question! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Tom, I must respectfully disagree. Explicit = a COMMIT or ROLLBACK is explicitly issued by the user/program. Implicit = a COMMIT or ROLLBACK is performed as the result of an action and not issued by the user/program. The SQL*Plus options AUTOCOMMIT and COPYCOMMIT do not control EXIT behavior. For the EXIT/QUIT commands, the default behavior is COMMIT. With WHENEVER OSERROR or SQLERROR, the behavior can also be altered to perform a COMMIT or ROLLBACK. Is there another option you are thinking of? COMMIT or ROLLBACK is tied very closely to TRANSACTIONs. In order to start a new transaction, the previous transaction must end. A COMMIT indicates a succesful end, while a ROLLBACK indicates a failure. Please keep in mind that this is not always coded in this manner, i.e. unhandled exceptions in PL/SQL followed by COMMIT in calling procs. DDL exists as a separate transaction. In order to start the DDL transaction, the previous transaction must end. Oracle terminates the previous transaction by COMMITting the changes and begins a new tx. I used a small anonymous PL/SQL block to test COMMIT/ROLLBACK behavior. I found it to be consistent with how I left SQL*Plus. If I killed the window, the change was not committed. If I typed 'EXIT', the change was committed. Dan Fink -Original Message- Sent: Thursday, January 02, 2003 8:10 AM To: Multiple recipients of list ORACLE-L John, there is no such thing as an implicit commit within Oracle. the only implicit commit that I know of is during a sqlplus session when you exit the program. even this is settable by a sqlplus option. distributed transactions that are controlled by a transaction coordinator (like MS DTC) might issue commits only because the web application requires all updates to be handled by the app-server. but this is different from what you are asking, I think. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 02, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Under what circumstances is a COMMIT done implicitly? If I call a function or procedure that performs an insert, but does not do a commit, will a commit be implicitly performed when the function ends? i.e. is ... begin INSERT INTO ... etc. end; the same as begin insert_the_record; end; where insert_the_record is a procedure that does the insert, but nothing else. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan 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:
RE: create view using DBMS.SQL
I can create view in schema. I don't know why it is not allowed me to create view in DBMS_SQL within procedure. Do I need additional privilege. Please give me some hints. TIA Trung. SQL create or replace view mytest 2 as 3 select * from tbl_file_definitions; View created. SQL select count(*) from mytest; COUNT(*) -- 641 SQL From: Koivu, Lisa [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: create view using DBMS.SQL Date: Thu, 02 Jan 2003 10:02:16 -0800 Right. Forgot that... -Original Message- Sent: Thursday, January 02, 2003 12:19 PM To: Multiple recipients of list ORACLE-L ummm directly? 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: Thursday, January 02, 2003 11:46 AM To: Multiple recipients of list ORACLE-L Is the create view privilege granted to the procedure owner? _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Donate Clothes 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).
Hotsos Clinic
I have an opportunity to attend a Hotsos Clinic. It seems I have heard good things about them on this list, but I thought I might double-check. Is this 3-day class worthwhile or is it an expensive way to sell their product? Will this class be beneficial, even if we don't buy their product? Keith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry, Keith 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 -- For Jared
Title: RE: Automatic backup on Oracle 9i -- For Jared Well if you want an answer from the experts on this list (or anyone, really - I fall into the 'shmo' category) it's best to ask a question in the clearest way possible... otherwise your emails may go directly to the delete folder. Ur cl. -Original Message- From: mkb [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Automatic backup on Oracle 9i -- For Jared Dnt knw wht al the fss is abt. Why nt just drp al th vwls? V cn al undrstd tht cn't v? ;-) mhmd --- OraCop [EMAIL PROTECTED] wrote: Well, I understand that writing you is not much trouble compared to writing U, but understanding U, should not be much trouble either. Don't U think so? OraCop. --- [EMAIL PROTECTED] wrote: I have never known what lol stood for , I appreciated the general meaning by looking at the context it was used in but know I actually know. I knew that if I perservered long enough with this list long enough I would find something of interest (lol) John -Original Message- Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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). __ 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: OraCop
Re: NVL and index usage
If you mean where nvl(col,:x) = :y then yes it will be negated. If memory serves, I've even had problems in the past using nvl in a function based index, the workaround doing the equivalent with decode, but I can't remember the specifics hth connor --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra JamadagniMIS, 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! *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 = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
Re: ORA-1410 Silliness
Lisa
. I've read some messages of this thread -- there is 100% probability :)
that you incorrectly identified the statement which errors 1410.
PL/SQL engine could not point to the line 1970 -- it's in the middle
of the statement -- something is strange there. Do you handle exceptions
in your code? I can bet it's raised from the exceptions handler block.
Another 100% probability -- you do use GUI(?), which incorrectly shows
PL/SQL code lines? :)
ACCEPT l1 PROMPT 'From line: ';
ACCEPT l2 PROMPT 'To line: ';
ACCEPT l3 PROMPT 'Obj: ';
COLUMN LINE FORMAT 9;
COLUMN TEXT FORMAT A70;
SELECT line
, text
FROM user_source
WHERE name = UPPER('l3')
AND line BETWEEN l1 AND l2
ORDER BY type, line
/
. Inline view in the example is just an illustration how indexes can
point to the wrong rowid...
. Did you enable tracing? Do it -- you'll find *everything*.
Kind Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Koivu, Lisa wrote:
Vladimir,
Thanks for your reply.
I have tested the cursor. It does not include any bind variables.
There are no broken rowids, as all objects passed analyze ... validate
structure cascade.
I also tested the scenario you describe in your code below. The code
does break with that error, however there are no inline views in my code.
Lisa
--
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: create view using DBMS.SQL
Bailey,
I can create a view. I don't know why it not allow me to create view using
DBMS_SQL within Procedure. Can you give me one example system privilege
granted directly to them to create a view? Thanks Truong
SQL create or replace view mytest
2 as
3 select * from tbl_file_definitions;
View created.
SQL select count(*) from mytest;
COUNT(*)
--
641
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: create view using DBMS.SQL
Date: Thu, 02 Jan 2003 08:34:56 -0800
Donate:
Does the owner of the procedure have system privilege granted directly to
them to create a view? Not with a role , but with the system privilege
granted directly to the procedure owner. Remember, roles are disabled
inside a stored procedure.
RWB
Donate Clothes [EMAIL PROTECTED]@fatcity.com on 01/02/2003
09:30:13 AM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Dear All,
I can CREATE and EXECUTE others procedures however can not execute
procedure
with DBMS_SQL. I'm created procedure has parameters to create a view using
DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some
hints how make it works or any example.
TIA
Truong.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE
PROC_REFRESH_VIEW(pfileno IN VARCHAR2 DEFAULT NULL,
pfilename IN VARCHAR2 DEFAULT NULL,
pfileyear IN NUMBER DEFAULT NULL,
precordtype IN VARCHAR2 DEFAULT NULL)
IS
cursor_name INTEGER;
cursor_id INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,'CREATE OR REPLACE VIEW V_FILTER AS '
||'SELECT * FROM TBL_FILE_DEFINITIONS '
||'WHERE FILE_NUMBER_TX = '||||NVL(pfileno,'DUMMY')||
||' ORFILE_NAME_TX = '||||NVL(pfilename,'DUMMY')||
||' OR FILE_YEAR_NR = '||NVL(pfileyear,1800)
||' OR REC_TYPE_TX = '||||NVL(precordtype,'DUMMY')
||,DBMS_SQL.NATIVE);
cursor_id := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
SHOW ERROR
SQLProcedure created.
SQLNo errors.
SQL exec proc_refresh_view('010-03');
BEGIN proc_refresh_view('010-03'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYS.DBMS_SYS_SQL, line 826
ORA-06512: at SYS.DBMS_SQL, line 32
ORA-06512: at AISDB.PROC_REFRESH_VIEW, line 17
ORA-06512: at line 1
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
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).
_
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
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
RE: Automatic backup on Oracle 9i -- For Jared
It reminds me the text I have read few years ago, maybe even posted to this list: http://www.ahajokes.com/eng011.html BTW Personally I am on the philosophical side - use common abbreviations like BTW, ROTFL, etc but don't like the U, 4U, etc. But who knows what the future brings, especially with younger generation. I read few months ago that the Bible had been translated to use the abbreviations to make it more attractive to teenagers ;-) Witold -Original Message- Sent: 02 January 2003 2:24 PM To: Multiple recipients of list ORACLE-L Dnt knw wht al the fss is abt. Why nt just drp al th vwls? V cn al undrstd tht cn't v? ;-) mhmd --- OraCop [EMAIL PROTECTED] wrote: Well, I understand that writing you is not much trouble compared to writing U, but understanding U, should not be much trouble either. Don't U think so? OraCop. --- [EMAIL PROTECTED] wrote: I have never known what lol stood for , I appreciated the general meaning by looking at the context it was used in but know I actually know. I knew that if I perservered long enough with this list long enough I would find something of interest (lol) John -Original Message- Sent: 02 January 2003 15:30 To: Multiple recipients of list ORACLE-L I have philosophical trouble with it. I dislike the abbreviations. I will use abbreviations to condense phrases (lol for lots of laughs) but I really dislike seeing you written as u. It's not that hard to type the extra two letters. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: As far as we know there is no bandwidth limit on this list and problems can be understood better if you state them in a lucid and clear language. I had to really read twice (sorry haven't had my Great One yet) to understand the abbreviations. Am I the only one or is there anyone else who has trouble with such language? TIA Raj __ Rajendra JamadagniMIS, 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: Tuesday, December 31, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Jared what exactly U use Veritas Netbackup fr in Ur backup strategy. I use rman to take backup on disk. Please describe the role of veritas NB in detail and if u can send me example script to perform what tasks, that would be gr8. OraCop *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). -- 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official
Re: Re: Free Shared pool memory
It is often the case that Telecomms companies end up with a very large SGA. 3Gb does sound a little suspicious - but it would be silly to judge it without knowing more background, such as total number of users, number of applications embedded within the database, number of CPUs, nature of work, quality of code. And, of course, with 8.1.6.3 it is possible that the memory is being wasted to work around some bug with shared SQL handling that might be such an unusual bug that only that particular company has come across it. 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: 02 January 2003 18:05 Jonathan Lewis, Hi, lewis,have you ever see any big system with large shared pool size? This week I saw a mobile telecom system running ops 8163, have 8GB sga, with 3GB of shared_pool_size and About 5GB of data buffer.(Physical memory is 16GB) I never configured a system with shared_pool_size larger than 200MB(my current system is 200MB).But that oracle is installed by oracle china, so i wonder if this kind of configuration is reasonable? I do not have enough time to analyze that system, but I do not think Oracle china is giving the customer the right parameter about shared_pool, can you give your opinion? Thanks. -- 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: Free Shared pool memory
Brain was clearly not engaged when I wrote the last answer - I have a set of results I produced some time back when calibrating init.ora parameters with memory usage. (Out of date by now since it was 8.0). Simple test: Double the size of sessions in the init.ora, various entries in v$sgastat will increase in size, even though you haven't changed shared_pool_size. The shared_pool_size is just one component of the shared pool. 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: 02 January 2003 17:41 Another possible explanation is that SHARED_POOL_SIZE is not the actual size of the Shared Pool, but rather a starting point to which Oracle adds extra space for some reason? -- 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: NVL and index usage
Title: NVL and index usage Raj, the index will not be used. think about it. for those records that do not have a value (are null) for the indexed column- there is not an entry in the index - so they can not be evaluated to return a row. secondly, even if all rows were represented in the index - why perform two queries - one for the index, and then one for the table - to evaluate whether to use the row in the query? either way, it would not work properly. that's why Oracle gave us function based indexes - so that we could use a function and get fast results from a large table. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message-From: Shaleen [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 1:22 PMTo: Multiple recipients of list ORACLE-LSubject: Re: NVL and index usage In a quick test on 9013 it changed the index which it was using and went from Range scan to fast full scan. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, January 02, 2003 9:48 AM Subject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). 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!
