Re: SQL help needed
Tim Gorman wrote: Great idea, Mark! By the way, does anyone remember the yes command in UNIX? Same concept. I think it was invented to answer ³yes² to any program which mindlessly prompt for ³yes/no² responses in situations where only ³yes² makes sense. Case in point: ³fsck². As in: ³Do you want to repair this block (y/n)? ³ Tim, You made me discover a great command. yes rtfm has opened new vistas to me. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Configuring RMAN
If $OH/bin was in the PATH then which rman would have got you the result. I think that was not the case. That's exactly why it connected to rman when you typed $OH/bin/rman. Therefore include $ORACLE_HOME/bin in the path after :/bin:/usr/sbin etc. HTH GovindanK Hi list, Recalling the yesterday problem, here is my profile, what arrangement should I do To make RMAN work from the ORACLE_HOME/bin without to specify the path. Kind of new in UNIX/LINUX. TIA, export EDITOR=vi export TERM=xterm #* # Variables de Oracle | #* export ORACLE_SID=BDRP export ORACLE_BASE=/u01/oracle/product export ORACLE_HOME=/u01/oracle/product/8.1.7 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 export ORACLE_TERM=xterm #* # Variables de Linux| #* export LD_ASSUME_KERNEL=2.2.5 #source /usr/i386-glibc21-linux/bin/i386-glibc21-linux-env.sh export JAVA_HOME=/usr/local/java export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes12.zip:$ORACLE_HOME/jlib:$ORACLE_ H OME/rdbms/jlib:$ORACLE_HOME/network/jlib:.:$ORACLE_HOME/JRE Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Joe Testa Sent: Tuesday, September 16, 2003 2:05 PM To: Multiple recipients of list ORACLE-L your path could still be pointing elsewhere first and not the current directory. $ORACLE_HOME/bin/rman joe Ramon E. Estevez wrote: Tks Joe and Per But the same results Red Hat Linux Advanced Server release 2.1AS/i686 (Pensacola) login: oracle Password: DELL-installed Red Hat Linux Mon Aug 25 09:59:40 CDT 2003 Profile ejecutado [EMAIL PROTECTED] oracle]$ cd $ORACLE_HOME/bin [EMAIL PROTECTED] bin]$ export ORACLE_SID=BDRP [EMAIL PROTECTED] bin]$ rman [EMAIL PROTECTED] bin]$ rman rman/[EMAIL PROTECTED] rman: can't open rman/[EMAIL PROTECTED] [EMAIL PROTECTED] bin]$ Please any help would be appreciated. Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Joe Testa Sent: Tuesday, September 16, 2003 12:05 PM To: Multiple recipients of list ORACLE-L only on some OS, like linux. joe Per Berghäll wrote: Try this instead: $ORACLE_HOME/bin/rman rman/[EMAIL PROTECTED] mailto:rman/[EMAIL PROTECTED] If you don't use $ORACLE_HOME/bin it finds another rman that exists in the O/S. Med vänliga hälsningar/ Kind regards -- *Veriba AB* Per Berghäll Brigadgatan 10 581 31 Linköping Tele: +46 (0)13-362600 Fax: +46 (0)13-362625 mailto:[EMAIL PROTECTED] _http://www.veriba.se_ http://www.veriba.se/ -- -Ursprungligt meddelande- *Från:* Ramon E. Estevez [mailto:[EMAIL PROTECTED] *Skickat:* den 16 september 2003 16:19 *Till:* Multiple recipients of list ORACLE-L *Ämne:* RE: Configuring RMAN Thanks Ruth and Belinda, That's what I'm trying to do, connect to the DB to create the catalog and register the DB. This is what I get when trying to connect to rman [EMAIL PROTECTED] oracle]$ rman rman/[EMAIL PROTECTED] mailto:rman/[EMAIL PROTECTED] rman: can't open rman/[EMAIL PROTECTED] mailto:rman/[EMAIL PROTECTED] [EMAIL PROTECTED] oracle]$ TIA *Ramon E. Estevez* [EMAIL PROTECTED] 809-535-8994 -Original Message- *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Ruth Gramolini *Sent:* Tuesday, September 16, 2003 8:54 AM *To:* Multiple recipients of list ORACLE-L *Subject:* RE: Configuring RMAN You have to create the catalog. Check the docs to see how to do this. In 8.1.7 I think it is simply create catalog. Then you have to register the databases you want to use this catalog for, etc. The docs are quite good. HTH, Ruth -Original Message- *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of* Ramon E. Estevez *Sent:* Monday, September 15, 2003 7:24 PM *To:* Multiple recipients of list ORACLE-L *Subject:* Configuring RMAN Hi list, I am implementing RMAN in an RH AS 2.1 environment on Oracle 8.1.7 and at the prompt When using rman catalog rman/[EMAIL PROTECTED] I get rman: can't open catalog I recreated the password file and the same error. TIA, *Ramon E. Estevez* [EMAIL PROTECTED] 809-535-8994 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GovindanK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list
Re: control_file_record_keep_time
Note that starting from 8.0, the controlfile can dynamically grow, thus instead of overwriting reusable sections before their keep time expires, controlfile is expanded instead. Only reusable records can be overwritten after control_file_record_keep_time days. These include log history, archive history, rman records etc.. which are not crucial for running your database anyway. It might affect some custom backup scripts or rman nocatalog operations, though. Select from v$controlfile_record_section to get a brief overview about your current controlfile state (check records_total, records_used to check % of record usage and first_index to see where writing occurring in full circularbuffer). If you want to get more information, you can dump controlfile information to trace using: alter session set events 'immediate trace name controlf level 3'; or use level 10 if you wan't very detailed information. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 12:39 AM http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch126.htm#1015367 I backup once a week. Default as per the manual is 7 days. I might skip taking backup due to various reasons i a week. How will i ever know what % of my control file records are avbl.for rewriting and that i will not run into any error? Thanks Quriyat ___ No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: quriyat 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: Tanel Poder 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: weird
Which version are you on? Just wondering if it might have something to do with some bug in automatic undo management? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:09 PM No question here. Just something weird. This is a long-running insert with NO NONE ZERO ZIP ZILCH NADA commit. It makes me wonder if something weird is going on, or if I am overlooking something in the query. SQL select a.username,sum(b.used_ublk) x from v$session a, v$transaction b where a.taddr=b.addr group by a.username; USERNAMEX -- -- SYSTEM418 1 row selected. SQL / USERNAMEX -- -- SYSTEM893 1 row selected. SQL / USERNAMEX -- -- SYSTEM 2 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3181 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: Apps 11.5.8 and 9i
Tim, The FormsReports are still there, the main part of Apps will continue using them long time. I've not touched 11.5.9 yet, maybe they've modified new installation config that way it doesn't require jinitiator on Windows platforms anymore, but the java forms you see, are still generated using Oracle Forms (with the exception of SSF, self service framework, which is HTMLjsp) and reports are still run using Oracle Reports. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 8:34 AM Plus, no more jinitiator in 11.5.9! Finally, the removal of SQL*Form/Oracle*Forms/Forms is complete... on 9/19/03 4:24 PM, Ron Thomas at [EMAIL PROTECTED] wrote: John, Thanks for the info. You may want to skip 11.5.8 and go to 11.5.9. There are major patches (the family pack variety) to apply subsequent to 11.5.8 Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] s.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Apps 11.5.8 and 9i .com 09/19/2003 04:54 PM Please respond to ORACLE-L Ron, We are 'in the process' of moving from 11.5.7/8.1.7.4 to 11.5.8/9.2.0.4 - planning/testing starts after approval. We went ahead and installed a test db in any case. Make sure that you start out with 9.2.0.4 - the older 9.2.0.3 is buggy and Oracle seems to have ratified .4 recently. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Apps 11.5.8 and 9i Has anyone upgraded Oracle Applications 11.5.8 from database version 8.1.7 to 9i? Was it good, bad, indifferent in regards to performance? I'd like to because of some of the database enhancements, but the CIO asked the performance question. Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
Re: Stuck Query
Hi! How do you verify that your session is still waiting on sequential read? From v$session_event? Is the status column saying WAITING there? (if it says WAITED%, then your CPU is doing something else already, and this record in session wait just shows the last wait). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 12:54 AM SQL describe chanarch_nlc.archive_wave_i Name Null?Type - -- PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES SQL describe chanarch_nlc.arch_wave_i Name Null?Type - -- PV_ID NUMBER(38) TIMESTAMP DATE NANOSECS NUMBER(9) STAT NUMBER(8) SEVR NUMBER(8) OSTAT NUMBER(16) VALUE CHANARCH_NLC.INT_VALUES SQL describe chanarch_nlc.int_values chanarch_nlc.int_values VARRAY(16384) OF NUMBER(38) Select * from chanarch_nlc.arch_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' ORDER BY TIMESTAMP, NANOSECONDS / Returns 1 row plus the associated varray data in one second Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' ORDER BY TIMESTAMP, NANOSECONDS Returns 0 rows in about a second Select * from chanarch_nlc.arch_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Union all Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Returns a single row with the varray data in a second However Select * from chanarch_nlc.arch_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Union all Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Order by 2,3 Never returns. It waits forever on a sequential dbfile read event of one the files used by the lob segment which contains the varray data for the value column of chanarch_nlc.arch_wave_i. Nothing appears to be blocking the session. The query plan is as one would expect. Similar queries against different tables with the same structure proceed without incident. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. 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: Tanel Poder 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: SQL help needed
y y y y y .. On Fri, 2003-09-19 at 22:09, Tim Gorman wrote: Great idea, Mark! By the way, does anyone remember the yes command in UNIX? Same concept. I think it was invented to answer yes to any program which mindlessly prompt for yes/no responses in situations where only yes makes sense. Case in point: fsck. As in: Do you want to repair this block (y/n)? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: weird
Stephen, Was this the only running transaction? No background stuff going on? Jared On Fri, 2003-09-19 at 20:14, Khedr, Waleed wrote: You need to monitor it on the transaction level not rolled up to the user level. Waleed -Original Message- Sent: Friday, September 19, 2003 11:10 AM To: Multiple recipients of list ORACLE-L No question here. Just something weird. This is a long-running insert with NO NONE ZERO ZIP ZILCH NADA commit. It makes me wonder if something weird is going on, or if I am overlooking something in the query. SQL select a.username,sum(b.used_ublk) x from v$session a, v$transaction b where a.taddr=b.addr group by a.username; USERNAMEX -- -- SYSTEM418 1 row selected. SQL / USERNAMEX -- -- SYSTEM893 1 row selected. SQL / USERNAMEX -- -- SYSTEM 2 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3181 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SGA Max size
My understanding of SGA is SGA = x + y + z where x = (dbblksize*db_blk_buf OR db_cache_Size if 9i) y=shared_pool z=java pool, log_buffer If 9i oracle introduced SGA_MAX_SIZE; the sum of x+y+z can be SGA_MAX_SIZE; if so, which part of x/y/z expands when need arises. Thanks Quriyat ___ No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: quriyat 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).
FW: SQL help needed
How incredibly frustrating! My original response keeps getting truncated just before the good stuff! Here is one more try with HTML turned off... -- Forwarded Message Date: Fri, 19 Sep 2003 22:47:43 -0700 To: [EMAIL PROTECTED] Sorry for the last response in this thread which was truncated. Very annoying! The full response looks as follows (hope this goes through in it¹s entirety)... -- Forwarded Message Date: Fri, 19 Sep 2003 22:05:19 -0700 To: [EMAIL PROTECTED] Great idea, Mark! By the way, does anyone remember the yes command in UNIX? Same concept. I think it was invented to answer ³yes² to any program which mindlessly prompt for ³yes/no² responses in situations where only ³yes² makes sense. Case in point: ³fsck². As in: ³Do you want to repair this block (y/n)? ³. Later on, the ³fsck y² option made the separate ³yes² command unnecessary. Nice thing about the ³yes² command is that it could also answer ³no² endlessly (i.e. ³yes n²) or other themed responses (i.e. ³yes eat me²)... Anyway, here's your INFINITE_DUAL table, obviously requiring Oracle9i or above... SQL create type InfiniteDualType as object (dummy number); 2 / Type created. SQL SQL create type InfiniteDualTable as table of InfiniteDualType; 2 / Type created. SQL SQL create function f_infinite_dual(upper_limit in number default null) 2 return InfiniteDualTable 3 pipelined 4 is 5 v_rtn InfiniteDualType; 6 i integer := 1; 7 begin 8 -- 9 v_rtn := InfiniteDualType(null); 10 while true loop 11 v_rtn.dummy := i; 12 if upper_limit is not null and i upper_limit then 13 exit; 14 end if; 15 i := i + 1; 16 pipe row (v_rtn); 17 end loop; 18 -- 19 return; 20 -- 21 end f_infinite_dual; 22 / Function created. SQL SQL select * from table(f_infinite_dual(10)); DUMMY -- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL SQL create view infinite_dual 2 as 3 select * from table(f_infinite_dual); View created. SQL SQL select * from infinite_dual; DUMMY -- 1 2 3 4 5 6 7 8 9 10 11 DUMMY -- 12 13 14 15 16 17 18 19 20 21 22 DUMMY -- 23 24 25 26 27 28 29 30 31 32 33 ..and so on, and so on, and so on, until you hit CTRL-C (or 42, which ever represents infinity in your own belief system)... Just for grins, I had installed an UPPER_LIMIT parameter to the F_INFINITE_DUAL function, but it defaults to infinity. Great idea, Mark! -Tim on 9/17/03 6:24 PM, Mark Richard at [EMAIL PROTECTED] wrote: You raise an interesting idea in my mind... How useful would it be for us if Oracle created an INFINITE_DUAL table - One where you could select as many rows as you wished. I guess it might be dangerous but it would at least be very efficient if Oracle coded it as a special table. Then you could just do things like select rownum from infinite_dual where rownum = 100 without concern or consideration. As you said - you wouldn't use it every day, but I'm sure it would be handy to have. It would also provide new ways to crash Oracle I guess, with queries like select rownum the_row from infinite_dual order by the_row. Hmm, perhaps this idea needs some additional thought? I'm sure it would be practical to create something which works efficiently and safely though. Regards, Mark. Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: orp.com Subject: RE: Re: SQL help needed Sent by: [EMAIL PROTECTED] .com 17/09/2003 18:39 Please respond to ORACLE-L - --- Original Message --- - From: Mark Richard [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 16 Sep 2003 19:59:41 I guess I should have added some criteria like: 1) user_objects must have enough rows in it to cover the range (if not consider some other table) This one is a very interesting consideration. The use of 'pivot' tables, as in this case, without being something you meet daily is fairly frequent (completing
Re: Apps 11.5.8 and 9i
Tanel, You're absolutely right. I had really meant to say that the 8.0.6 software stack finally goes away, but stated it poorly. Unfortunately, I was wrong even there, as the 8.0.6 software stack remains, as you've indicated. All I can say is that I was in the db-tier environment when looking for it, not in the app-tier as I should have been. Sloppy work and sloppy assumptions. Unskyld! But the Jinitiator is still nowhere to be seen, at least the stuff I've played with (i.e. Conc Mgr forms, SYSADMIN stuff, etc). I've not seen any documentation to confirm this though... 11.5.9 installs the db-tier in v9.2.0.3, but (not unexpectedly) the database that is scraped out of the 22-CD set of installation CDs is not set up very intelligently. For example, the locally-managed tablespaces have all been migrated from dictionary-managed instead of created as UNIFORM or AUTOALLOCATE, which leaves some interesting anomalies for the conscientious DBA, such as NEXT_EXTENT settings which are larger than any of the non-autoextend-enabled datafiles. Yay! for using locally-managed, boo! for migrating instead of doing it right. Kind of like asking kids to pick up their clothes -- they just move them from the floor to and place them under the bed, making the floor clean but not improving the process of getting clean clothes back into the closet and drawers. Anyway, this is the case in some 70-odd tablespaces. I'm sure this situation has always been present, but it makes one wonder whether Oracle ever consulted a practicing Apps DBA when burning its pre-installed databases onto the CDs. Still, 2 steps forward, 1 step back... Maybe they did ask someone and regretted it, due to negative feedback? After all, one time-honored way of getting rid of bad news is to kill the messenger. How hard would it be to fix the one template database correctly, before scattering the thing onto a thousand unsuspecting servers? Maybe that is being saved for 11.5.10? Anyway, thanks for the correction! You are indeed great to have on the list! I hope to see you in Toronto at IOUG, perhaps? -Tim on 9/20/03 2:59 AM, Tanel Poder at [EMAIL PROTECTED] wrote: Tim, The FormsReports are still there, the main part of Apps will continue using them long time. I've not touched 11.5.9 yet, maybe they've modified new installation config that way it doesn't require jinitiator on Windows platforms anymore, but the java forms you see, are still generated using Oracle Forms (with the exception of SSF, self service framework, which is HTMLjsp) and reports are still run using Oracle Reports. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 8:34 AM Plus, no more jinitiator in 11.5.9! Finally, the removal of SQL*Form/Oracle*Forms/Forms is complete... on 9/19/03 4:24 PM, Ron Thomas at [EMAIL PROTECTED] wrote: John, Thanks for the info. You may want to skip 11.5.8 and go to 11.5.9. There are major patches (the family pack variety) to apply subsequent to 11.5.8 Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] s.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Apps 11.5.8 and 9i .com 09/19/2003 04:54 PM Please respond to ORACLE-L Ron, We are 'in the process' of moving from 11.5.7/8.1.7.4 to 11.5.8/9.2.0.4 - planning/testing starts after approval. We went ahead and installed a test db in any case. Make sure that you start out with 9.2.0.4 - the older 9.2.0.3 is buggy and Oracle seems to have ratified .4 recently. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Apps 11.5.8 and 9i Has anyone upgraded Oracle Applications 11.5.8 from database version 8.1.7 to 9i? Was it good, bad, indifferent in regards to performance? I'd like to because of some of the database enhancements, but the CIO asked the performance question. Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City
Re: wrapping packages
Hi Very true, but if there was the wrap process wouldn't be much use as anyone could un wrap your code. But you are right the main reason to be cautious is to not delete your source code locally. kind regards Pete In article [EMAIL PROTECTED], bhabani s pradhan [EMAIL PROTECTED] writes one caution: there is no unwrap cmd/exe Regards -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: asynch I/O
Dick, With all due respect, I'd like to interject. Due to the many levels of abstraction imposed by the various RAID schemes, volume managers, dynamic multi-pathing, file-systems, and databases, my eyes tend to cross whenever someone starts talking about the movements of the disk heads, rotational latency, and so forth. The perception of contiguousness in a file-system or database datafile on a modern server in relation to disk surfaces is purely illusory. It is somewhat akin to the idea that every US dollar bill is backed by a sliver from a gold bar deep in the bowels of Ft Knox -- the facts are much more complex, by design. Your other comments about WAFL's side-effects are interesting and thought-provoking. It's been a few years since I've worked on NetApp and just this week I was called in to help improve performance on a large Oracle environment over NetApp. At this point, I'm glad that I had not blurted out my long-standing misgivings about the product, as it seems that its ability to support higher volumes of I/O from Oracle has improved. It just requires different methods of administration and configuration. It's not your grandfather's file-system, that's for sure... Respectfully, -Tim on 9/19/03 9:34 AM, Goulet, Dick at [EMAIL PROTECTED] wrote: Matt, Well I'm happy to see that you consider WAFL as crafty. In my book it does not have such a nice connotation. Consider the typical disk drive where you layout your files as contiguous blocks of space around the disk drive. So long as the file remains it's current size all of the data is gathered together and easy to read/write. You don't need to constantly slam that head around to get where you want. With WAFL all of that heads for the hills. Sure the original file is contiguous, but hit the first update and bingo that's history. Now the head has to fly around reassembling the file from blocks scattered all over the place, and what's the one thing about disk drives that has remained a constant over the years, seek time. Therefore WAFL file systems will slow over time, yuck. One other nasty item. Remember that tree you need to update, well until a 'snapshot' (NetApp speak) occurs those blocks that have been updated several times can't be reused therefore that 1GB ! disk file that you originally laid out could easily consume 100GB due to the updates, inserts, etc... Double YUCK! How is that so you say, remember that when you tell Oracle to create a datafile it acquires and formats all of the disk space it needs, say 100MB, but all of it is empty blocks. Now you run a SQL*Loader command to upload 50MB of data into that file. Well WAFL now needs 50MB of additional disk space to place all of those 'updated' blocks of data into, so in reality the data file is now occupying ~150MB of space, but 50MB of that is hidden from view until the snapshot fires. Fun part, your DB stops running in the middle of the day due to a lack of disk space on your NetAppliance. Your boss wants to know why your 10GB database has burned up a 100GB NET App Filer. Of course you as a DBA don't know because the database hasn't grown any. Add more egg on your face when the snapshot fires bingo there is 90GB of free space that 'suddenly' appears. The work! around of course is to fire snapshots frequently and limit th! e number retained, but that just adds workload to the NetApp when I want it servicing the database! As an old mentor once said, You can't win for loosing!. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, September 19, 2003 11:50 AM To: Multiple recipients of list ORACLE-L This is actually platform dependent. For example, if you're using UDP mounts under Linux, you can only have one request outstanding per mount. Consequently, multiple mounts can improve performance by allowing parallel operations. A side benefit of Oracle on Netapp is WAFL, which as Dick pointed out, stands for Write Anywhere File Layout. Basically, an update to a block does not cause a disk seek and an update - the system simply goes to the first available raid stripe that's free and writes the block there, then updates the tree. Besides being rather crafty, it creates a situation where compound writes to multiple files - like a tablespace update and an index update - migrate close to each other on disk. I/O patterns train the filesystem structure. To actually answer your original question, it will not make a difference on most platforms that are properly configured. What will make a difference is your network settings. Are you using Gigabit + jumbo frames? Matt *still pleased with how crafty WAFL is* -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel Poder Sent:
Re: SQL help needed
y y y y Continue to repair or build a new filesystem- Do you want to build a new filesystem (y/n)? y Oops ;-) .. On Fri, 2003-09-19 at 22:09, Tim Gorman wrote: Great idea, Mark! By the way, does anyone remember the yes command in UNIX? Same concept. I think it was invented to answer yes to any program which mindlessly prompt for yes/no responses in situations where only yes makes sense. Case in point: fsck. As in: Do you want to repair this block (y/n)? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: wrapping packages
Hi Pete I must point out that there must be a unwrap, since the Oracle database can run the wrapped pl/sql code :-) It is based on trust in Oracle cooperation / development. Some times it would make since to write the code in c/c++ since it harder to revers. Pete Finnigan wrote: Hi Very true, but if there was the wrap process wouldn't be much use as anyone could un wrap your code. But you are right the main reason to be cautious is to not delete your source code locally. kind regards Pete In article [EMAIL PROTECTED], bhabani s pradhan [EMAIL PROTECTED] writes one caution: there is no unwrap cmd/exe Regards -- Peter Gram, Miracle A/S Phone : +45 2527 7107, Fax : +45 4466 8856, Home +45 3874 5696 mail : [EMAIL PROTECTED] - http://MiracleAS.dk Upcoming events: DatabaseForum 2003, Lalandia 2-4 October Visit http://miracleas.dk/events/DBF2003/invitation.html Miracle Master Class with Tom Kyte, 12-14 January 2004 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram 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: Apps 11.5.8 and 9i
Hi! You're absolutely right. I had really meant to say that the 8.0.6 software stack finally goes away, but stated it poorly. Unfortunately, I was wrong even there, as the 8.0.6 software stack remains, as you've indicated. All I can say is that I was in the db-tier environment when looking for it, not in the app-tier as I should have been. Sloppy work and sloppy assumptions. Unskyld! :) I think Oracle has hard time keeping even this years-old tech stack configuration up and running when releasing new version, thus we probably wont see any new FormsReports client libraries before 12i (or should is say 12g... (or e or x or whatever)). But the Jinitiator is still nowhere to be seen, at least the stuff I've played with (i.e. Conc Mgr forms, SYSADMIN stuff, etc). I've not seen any documentation to confirm this though... But Jinitiator (basically Oracle modified version of Sun JVM) is completely client side thing, that for Windows you need to install it. Kind of strange, one of biggest benefits was supposed to be platform independence ability to run on any JVM, but the reason is probably Mirosoft (again) unable or unwilling to write a standards-compatible JVM. On linux solaris GUI seems to work with standard JVMs. I don't have any 11.5.9s here, but you could check for following profile options under system administrator responsibility: APPLET_PLUGIN_URL APPLET_PLUGIN_TYPE In my 11.5.7 they are set to oajinit.exe and application/x-oajinit-applet;version=1.1.7.27 respectively. This should state which java plugin to request. Also, under $OA_HTML there should be an oajinit.exe file. If above options and file are there, then 11.5.9 is probably still using requiring Jinitiator for Windows clients. 11.5.9 installs the db-tier in v9.2.0.3, but (not unexpectedly) the database that is scraped out of the 22-CD set of installation CDs is not set up very intelligently. For example, the locally-managed tablespaces have all been Yeah, luckily I've mostly dealed with upgrade projects with platform change, thus had the option of creating database from scratch. But yep, if you have a spare weekend (and downtime), you could spend them moving tables around rebuilding tablespaced. present, but it makes one wonder whether Oracle ever consulted a practicing Apps DBA when burning its pre-installed databases onto the CDs. Still, 2 steps forward, 1 step back... I think, that was exactly what they did - they consulted with Apps DBA, who didn't know much more than how to patch Apps or restart concurrent manager. Anyway, thanks for the correction! You are indeed great to have on the list! I hope to see you in Toronto at IOUG, perhaps? There sure are plans to come to Toronto, it depends whether any of my (very interesting :) abstracts get accepted there. This year I spoke about one topic on 11i, both the event speaking experience were awesome. So, let's keep thumbs up hopefully I see you as well next year :) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: wrapping packages
I checked, the wrap executable in 8.0.6 dist for solaris is about 3MB, but for 9.2 in Windows it's only about 40k. It shouldn't be that hard to reverse engineer it. Probably the ones who already have cracked the algorithm aren't spreading the knowledge - why should they anyway?! Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 21, 2003 12:29 AM Hi Pete I must point out that there must be a unwrap, since the Oracle database can run the wrapped pl/sql code :-) It is based on trust in Oracle cooperation / development. Some times it would make since to write the code in c/c++ since it harder to revers. Pete Finnigan wrote: Hi Very true, but if there was the wrap process wouldn't be much use as anyone could un wrap your code. But you are right the main reason to be cautious is to not delete your source code locally. kind regards Pete In article [EMAIL PROTECTED], bhabani s pradhan [EMAIL PROTECTED] writes one caution: there is no unwrap cmd/exe Regards -- Peter Gram, Miracle A/S Phone : +45 2527 7107, Fax : +45 4466 8856, Home +45 3874 5696 mail : [EMAIL PROTECTED] - http://MiracleAS.dk Upcoming events: DatabaseForum 2003, Lalandia 2-4 October Visit http://miracleas.dk/events/DBF2003/invitation.html Miracle Master Class with Tom Kyte, 12-14 January 2004 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram 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: Tanel Poder 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: asynch I/O
Can you be a little more specific? What kind of administration would you recommend? On 2003.09.20 17:14, Tim Gorman wrote: Dick, With all due respect, I'd like to interject. Due to the many levels of abstraction imposed by the various RAID schemes, volume managers, dynamic multi-pathing, file-systems, and databases, my eyes tend to cross whenever someone starts talking about the movements of the disk heads, rotational latency, and so forth. The perception of contiguousness in a file-system or database datafile on a modern server in relation to disk surfaces is purely illusory. It is somewhat akin to the idea that every US dollar bill is backed by a sliver from a gold bar deep in the bowels of Ft Knox -- the facts are much more complex, by design. Your other comments about WAFL's side-effects are interesting and thought-provoking. It's been a few years since I've worked on NetApp and just this week I was called in to help improve performance on a large Oracle environment over NetApp. At this point, I'm glad that I had not blurted out my long-standing misgivings about the product, as it seems that its ability to support higher volumes of I/O from Oracle has improved. It just requires different methods of administration and configuration. It's not your grandfather's file-system, that's for sure... Respectfully, -Tim on 9/19/03 9:34 AM, Goulet, Dick at [EMAIL PROTECTED] wrote: Matt, Well I'm happy to see that you consider WAFL as crafty. In my book it does not have such a nice connotation. Consider the typical disk drive where you layout your files as contiguous blocks of space around the disk drive. So long as the file remains it's current size all of the data is gathered together and easy to read/write. You don't need to constantly slam that head around to get where you want. With WAFL all of that heads for the hills. Sure the original file is contiguous, but hit the first update and bingo that's history. Now the head has to fly around reassembling the file from blocks scattered all over the place, and what's the one thing about disk drives that has remained a constant over the years, seek time. Therefore WAFL file systems will slow over time, yuck. One other nasty item. Remember that tree you need to update, well until a 'snapshot' (NetApp speak) occurs those blocks that have been updated several times can't be reused therefore that 1GB ! disk file that you originally laid out could easily consume 100GB due to the updates, inserts, etc... Double YUCK! How is that so you say, remember that when you tell Oracle to create a datafile it acquires and formats all of the disk space it needs, say 100MB, but all of it is empty blocks. Now you run a SQL*Loader command to upload 50MB of data into that file. Well WAFL now needs 50MB of additional disk space to place all of those 'updated' blocks of data into, so in reality the data file is now occupying ~150MB of space, but 50MB of that is hidden from view until the snapshot fires. Fun part, your DB stops running in the middle of the day due to a lack of disk space on your NetAppliance. Your boss wants to know why your 10GB database has burned up a 100GB NET App Filer. Of course you as a DBA don't know because the database hasn't grown any. Add more egg on your face when the snapshot fires bingo there is 90GB of free space that 'suddenly' appears. The work! around of course is to fire snapshots frequently and limit th! e number retained, but that just adds workload to the NetApp when I want it servicing the database! As an old mentor once said, You can't win for loosing!. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, September 19, 2003 11:50 AM To: Multiple recipients of list ORACLE-L This is actually platform dependent. For example, if you're using UDP mounts under Linux, you can only have one request outstanding per mount. Consequently, multiple mounts can improve performance by allowing parallel operations. A side benefit of Oracle on Netapp is WAFL, which as Dick pointed out, stands for Write Anywhere File Layout. Basically, an update to a block does not cause a disk seek and an update - the system simply goes to the first available raid stripe that's free and writes the block there, then updates the tree. Besides being rather crafty, it creates a situation where compound writes to multiple files - like a tablespace update and an index update - migrate close to each other on disk. I/O patterns train the filesystem structure. To actually answer your original question, it will not make a difference on most platforms that are properly configured. What will make a difference is your network settings. Are you using Gigabit + jumbo frames? Matt *still pleased with how crafty WAFL is* -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com
query rewrite system privilege
List, I finally got an answer to the problem I was facing with materialized views and the 'query rewrite' system privilege . Thanks for all your help especially mladen and thomas. - Original Message - To: [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 00:42 Hello, The answer to your question is that the query rewrite system privilege has been deprecated in Oracle 9i. So the behavior you see is correct. I think we need to fix the documentation. Also, the user does not need special privileges to enable query rewrite in the session or for queries to get rewritten with materialized views. All privilege checks are done only when creating the materialized view (similar to an index). Regards, Shilpa Lawande. [EMAIL PROTECTED] wrote: I am using oracle 9.2.0.1.0 enterprise edition on windows A user in my database is able to create materialized views with query rewrite enabled. But I have not given him 'query rewrite' system privilege. Isn't it required to create a mat. view with query rewrite enabled ? I quote from Oracle9i SQL Reference docs : To create a materialized view in another user's schema You must have the CREATE ANY MATERIALIZED VIEW system privilege. ...To create the materialized view with query rewrite enabled, in addition to the preceding privileges The owner of the master tables must have the QUERY REWRITE system privilege. If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege or the QUERY REWRITE object privilege on each table outside your schema. SQL sho user USER is SYS SQL create user test identified by test 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; User created. SQL grant create session, create table, create materialized view to test ; Grant succeeded. SQL connect test/[EMAIL PROTECTED] Connected. SQL create table master as select * from user_tables ; Table created. create materialized view mymatview tablespace users build immediate refresh on demand enable query rewrite as select table_name, count(*) from master group by table_name; Materialized view created. How was this user able to create this materialized view with query rewrite enabled ? Not only this , I find that this user is also able to enable query rewrite in his session also, as I show below : SQL alter session set optimizer_mode=choose; Session altered. SQL alter session set query_rewrite_enabled=true; Session altered. SQL alter session set query_rewrite_integrity=enforced; Session altered. SQL @?\rdbms\admin\utlxplan Table created. SQL set autotrace traceonly explain SQL analyze table master compute statistics; Table analyzed. SQL select table_name, count(*) from master group by table_name; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=204 Bytes=612 0) 10 TABLE ACCESS (FULL) OF 'MYMATVIEW' (Cost=3 Card=204 Bytes= 6120) Any ideas as to how all this was possible without 'query rewrite' or 'global query rewrite' ? I would be very grateful if you could explain what I am doing wrong . I fear this might be an RTFM type of mistake on my part. ... -- 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: Apps 11.5.8 and 9i
I doubt it the jinitiator will ever go away for forms. It is Oracle's method of insuring an appropriate JVM on the client machine. FWIW, Sun's JVM 1.4.2 works out of the box for linux systems. No tweeks in the appsweb.cfg file at all! Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] ail.ee To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Apps 11.5.8 and 9i .com 09/20/2003 03:59 AM Please respond to ORACLE-L Tim, The FormsReports are still there, the main part of Apps will continue using them long time. I've not touched 11.5.9 yet, maybe they've modified new installation config that way it doesn't require jinitiator on Windows platforms anymore, but the java forms you see, are still generated using Oracle Forms (with the exception of SSF, self service framework, which is HTMLjsp) and reports are still run using Oracle Reports. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 8:34 AM Plus, no more jinitiator in 11.5.9! Finally, the removal of SQL*Form/Oracle*Forms/Forms is complete... on 9/19/03 4:24 PM, Ron Thomas at [EMAIL PROTECTED] wrote: John, Thanks for the info. You may want to skip 11.5.8 and go to 11.5.9. There are major patches (the family pack variety) to apply subsequent to 11.5.8 Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] s.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Apps 11.5.8 and 9i .com 09/19/2003 04:54 PM Please respond to ORACLE-L Ron, We are 'in the process' of moving from 11.5.7/8.1.7.4 to 11.5.8/9.2.0.4 - planning/testing starts after approval. We went ahead and installed a test db in any case. Make sure that you start out with 9.2.0.4 - the older 9.2.0.3 is buggy and Oracle seems to have ratified .4 recently. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Apps 11.5.8 and 9i Has anyone upgraded Oracle Applications 11.5.8 from database version 8.1.7 to 9i? Was it good, bad, indifferent in regards to performance? I'd like to because of some of the database enhancements, but the CIO asked the performance
Re: Apps 11.5.8 and 9i
I agree, it's easier for Oracle that way as well. Because all what client sees in case of problems, is Oracle doesn't work, it doesn't matter who's fault is it, JVMs or operating systems... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 21, 2003 12:54 AM I doubt it the jinitiator will ever go away for forms. It is Oracle's method of insuring an appropriate JVM on the client machine. FWIW, Sun's JVM 1.4.2 works out of the box for linux systems. No tweeks in the appsweb.cfg file at all! Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] ail.ee To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Apps 11.5.8 and 9i .com 09/20/2003 03:59 AM Please respond to ORACLE-L Tim, The FormsReports are still there, the main part of Apps will continue using them long time. I've not touched 11.5.9 yet, maybe they've modified new installation config that way it doesn't require jinitiator on Windows platforms anymore, but the java forms you see, are still generated using Oracle Forms (with the exception of SSF, self service framework, which is HTMLjsp) and reports are still run using Oracle Reports. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 8:34 AM Plus, no more jinitiator in 11.5.9! Finally, the removal of SQL*Form/Oracle*Forms/Forms is complete... on 9/19/03 4:24 PM, Ron Thomas at [EMAIL PROTECTED] wrote: John, Thanks for the info. You may want to skip 11.5.8 and go to 11.5.9. There are major patches (the family pack variety) to apply subsequent to 11.5.8 Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] s.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Apps 11.5.8 and 9i .com 09/19/2003 04:54 PM Please respond to ORACLE-L Ron, We are 'in the process' of moving from 11.5.7/8.1.7.4 to 11.5.8/9.2.0.4 - planning/testing starts after approval. We went ahead and installed a test db in any case. Make sure that you start out with 9.2.0.4 - the older 9.2.0.3 is buggy and Oracle seems to have ratified .4 recently. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Apps 11.5.8 and 9i Has anyone upgraded Oracle Applications 11.5.8 from database version 8.1.7 to 9i? Was it good, bad, indifferent in regards to performance? I'd like to because of some of the database enhancements, but the CIO asked the performance question. Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
Re: weird
Stephen, Check forum 154061.996 in Metalink. Maybe you are hitting a bug (which should have been fixed in 9i). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:09 PM No question here. Just something weird. This is a long-running insert with NO NONE ZERO ZIP ZILCH NADA commit. It makes me wonder if something weird is going on, or if I am overlooking something in the query. SQL select a.username,sum(b.used_ublk) x from v$session a, v$transaction b where a.taddr=b.addr group by a.username; USERNAMEX -- -- SYSTEM418 1 row selected. SQL / USERNAMEX -- -- SYSTEM893 1 row selected. SQL / USERNAMEX -- -- SYSTEM 2 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3181 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: wrapping packages
Tanel Poder wrote: I checked, the wrap executable in 8.0.6 dist for solaris is about 3MB, but for 9.2 in Windows it's only about 40k. Perhaps you're not aware of the way executables compiled on your Solaris and Windows platforms. It shouldn't be that hard to reverse engineer it. It's an extremely commendable plan... (a touch of irony here) Probably the ones who already have cracked the algorithm aren't spreading the knowledge - why should they anyway?! I suggest you to call Oracle legal and discuss this issue and your original plan of fixing it. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- 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: asynch I/O
Mladen, I don't know enough yet, but last Wednesday night when I was pulled into the before-mentioned situation, performance was horrible and I/O was high. When the VP-IT said they were on NetApps, I felt I instantly knew what was wrong, but I've learned to keep my mouth shut until I have facts. James Herriott wrote that veterinary practice gives one ample opportunity to make a complete ass of oneself, and I've found the same to be true in this line of work. So, later that night, they migrated the 150Gb database and all binaries from one filer (an F900?) to the other (an F960?) overnight. NetApps ponied up the new filer -- said they didn't want to be blamed. For the new filer, they made the following configuration changes: * using I/O statistics gathered from the filer, they divided the Oracle database files into sixteen buckets, each of approximately the same amount of I/O by their measurements * they created sixteen separate mount-points over four separate Gig Ethernet segments, distributing things pretty evenly ...I'm not quite sure that I got all of that right, but that's the basic gist... Of course, as always, people treat the volume of I/O coming from an Oracle database as an unchanging monolithic thing, and they always think the best thing is to make the cost per I/O better. That's OK, if you're made of money... As Anjo advises in his YAPP reports from www.oraperf.com, tuning I/O means tuning the volume of I/O as well as tuning the cost per I/O. The NetApps folks already had a plan to reduce the cost per I/O before I was even called, so I've kept my mouth shut and pursued tuning the volume of I/O. Anyway, performance was many-fold better after the changes. My standard query on ALL_INDEXES and ALL_IND_COLUMNS to find indexes belonging to a table took 22 mins on Wednesday night, but only the normal 60-90 seconds the following afternoon. When I/O is sick, the entire system sneezes. The other side of what they did is snapshots. They take snapshots four times per day and replicate them to another filer to backup to tape. I think they only backup one of those snapshots per day. I would prefer to use RMAN and can't see any way to use it here, but I'm not about to delve into that right now. My job is tuning, not kibitzing on backups. So, Dick's comments about how WAFL works and how snapshots impact space utilization on the filer triggered some things. Like why take four snapshots per day when you're only backing off one set to tape...? Anyway, I'll work with these folks some more during next week; they still haven't implemented any of my recommendations (i.e. adding function-based indices, applying tuning patches, purging workflow, changing some custom code and using histograms, etc). I plan to really mess up NetApp's neat little 16-part picture of this system's I/O by making large chunks of it disappear. But that's OK -- they'll just have to adjust again. So, administratively, I'm not quite sure what really works yet, but I'm watching and (hopefully) learning... -Tim on 9/20/03 2:44 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: Can you be a little more specific? What kind of administration would you recommend? On 2003.09.20 17:14, Tim Gorman wrote: Dick, With all due respect, I'd like to interject. Due to the many levels of abstraction imposed by the various RAID schemes, volume managers, dynamic multi-pathing, file-systems, and databases, my eyes tend to cross whenever someone starts talking about the movements of the disk heads, rotational latency, and so forth. The perception of contiguousness in a file-system or database datafile on a modern server in relation to disk surfaces is purely illusory. It is somewhat akin to the idea that every US dollar bill is backed by a sliver from a gold bar deep in the bowels of Ft Knox -- the facts are much more complex, by design. Your other comments about WAFL's side-effects are interesting and thought-provoking. It's been a few years since I've worked on NetApp and just this week I was called in to help improve performance on a large Oracle environment over NetApp. At this point, I'm glad that I had not blurted out my long-standing misgivings about the product, as it seems that its ability to support higher volumes of I/O from Oracle has improved. It just requires different methods of administration and configuration. It's not your grandfather's file-system, that's for sure... Respectfully, -Tim on 9/19/03 9:34 AM, Goulet, Dick at [EMAIL PROTECTED] wrote: Matt, Well I'm happy to see that you consider WAFL as crafty. In my book it does not have such a nice connotation. Consider the typical disk drive where you layout your files as contiguous blocks of space around the disk drive. So long as the file remains it's current size all of the data is gathered together and easy to read/write. You don't need to constantly slam that head around to get
