Re: Index update = Delete + insert ?
Effectively so, unless you regularly put the same value back into the index. I haven't verified it, but in this case my understanding is that oracle does not the work at all. Of course, any index operation could invoke more work in terms of re-arranging blocks to keep the validity of the index structure. hth connor --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? -- 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ 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).
Newbie to Oracle DBA
Hello list, I wanted to know of some good sites where i can learn the basics as well as advanced DBA topics. Could anybody help me out ? Thanks and Regards, Santosh
Accessing LOB
Friends, Find below the script of a table with a CLOB datatype CREATE TABLE lob_content_display ( content_display_id VARCHAR2(30), business_id VARCHAR2(30), language_id VARCHAR2(30), content_typeVARCHAR2(30), detail CLOB DEFAULT EMPTY_CLOB(), CONSTRAINT pk_lob_cont_dsp PRIMARY KEY(content_display_id, business_id, language_id,content_type) USING INDEX TABLESPACE indx, CONSTRAINTfk_clcd_contdsp_bsl_ct FOREIGN KEY(content_display_id, business_id, language_id,content_type) REFERENCES content_display(content_display_id,business_id, language_id, content_type) ) LOB (detail) STORE AS (TABLESPACE ts_lobs CHUNK 16K PCTVERSION 10); As can be seen in the script, the LOB is stored in a separate tablespace. I am able to access the data in all the columns except the 'detail' column. Oracle throws an error - 'ORA-03120: two-task conversion routine: integer overflow' when the mentioned column is accessed. The oracle documentation says : ORA-03120: two-task conversion routine: integer overflow Cause: An integer value in an internal Oracle structure overflowed when being sent or received over a heterogeneous connection. This can happen when an invalid buffer length or too great a row count is specified. It usually indicates a bug in the user application. Action: Check parameters to Oracle calls. If the problem recurs, reduce all integer parameters, column values not included, to less than 32767. Please let me know how to solve this problem. Should I change any of the parameters in the init.ora file. Regards, Narasimhan -- 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: [new info] Redhat Advanced Server Dev Edition - RAC
I heard Mogens talk about this at the UKOUG Unix SIG in London at the end of last month (You Probably Don't Need RAC, or: pRos And Cons). It was truly an eye opener! The upshot was, if you don't have a requirement to be up from a failure within 5 minutes, then you don't need RAC. As has already been pointed out, in the case of a SAN failure, then even this may not count. Mogens also mentioned some pretty interesting up time statistics. A single Unix box can have an availability of 99.9%. A two node Unix cluster has an availability of 98% (due to software patching/upgrades). There is also still a brown out period with RAC when a node fails, whilst the other node or nodes play catch up to re-assign the resources and recover any work that the failed node was doing at the point of failure. Of course, there are also pros to having RAC, workload partitioning (running batch on one node, OLTP type work on another), you can scale your CPUs as and when the increase is needed. There seemed to be far more cons than pros imo though. Mogens goes through a lot more in his talk, it is certainly worth your time! It certainly helps to widen your perspective from the constant marketing jargon ;) I really must remember to send Mogens an email, he loves email, send it to him directly, I'm sure he'll be ecstatic! :D Mark -Original Message- Sent: 11 February 2003 23:34 To: Multiple recipients of list ORACLE-L FYI, I am headed to Mogens RAC or Not to RAC presentation at the hotsos symposium, let you know what I learn! -Original Message- Sent: Tuesday, February 11, 2003 10:30 AM To: Multiple recipients of list ORACLE-L This is all cool technology, and fun stuff to play with. It all begs the questions, How many of us work for a business that actually need this? Are they willing to pay $400/user $20k/CPU above the cost of Oracle 9i EE to use it? Are they willing to pay the extra overhead required to maintain it? I'm not sure the ROI is there for many of us. Though downtime at our business is somewhat expensive, I think that a failover system or even standby database will provide adequate coverage for us, which is indeed a hot topic here right now, after our Dell SAN put us out of business for 36 hours. RAC wouldn't have helped much there. Niether would a cluster for that matter. Standby DB would have been perfect. This whole push of RAC by Oracle reminds me very much of the mlife phone campaign by ATT. Do you really need to take pictures with your phone? And what is the point of sending text messages to someone elses phone when you could just call them? ATT needs you to buy this stuff, because they have it for sale. I see RAC in a similar light. Do you need RAC? Oracle needs you to 'need' it, because they need some reason for you to spend more money on their product. Jared On Saturday 08 February 2003 21:23, Richard Ji wrote: To those who are interested in running RAC on Linux. I know we have been talking about RAC on linux lately. This is great news Redhat has made a special developer's edition for their Advanced Server which only costs $60! So we don't have to shell out $699 for a copy of RHAS 2.1 to play with RAC. http://www.redhat.com/software/advancedserver/developer/ Have fun. Richard Ji -- 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: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
Accessing LOB
Friends, Find below the script of a table with a CLOB datatype CREATE TABLE lob_content_display ( content_display_id VARCHAR2(30), business_id VARCHAR2(30), language_id VARCHAR2(30), content_typeVARCHAR2(30), detail CLOB DEFAULT EMPTY_CLOB(), CONSTRAINT pk_lob_cont_dsp PRIMARY KEY(content_display_id, business_id, language_id,content_type) USING INDEX TABLESPACE indx, CONSTRAINTfk_clcd_contdsp_bsl_ct FOREIGN KEY(content_display_id, business_id, language_id,content_type) REFERENCES content_display(content_display_id,business_id, language_id, content_type) ) LOB (detail) STORE AS (TABLESPACE ts_lobs CHUNK 16K PCTVERSION 10); As can be seen in the script, the LOB is stored in a separate tablespace. I am able to access the data in all the columns except the 'detail' column. Oracle throws an error - 'ORA-03120: two-task conversion routine: integer overflow' when the mentioned column is accessed. The oracle documentation says : ORA-03120: two-task conversion routine: integer overflow Cause: An integer value in an internal Oracle structure overflowed when being sent or received over a heterogeneous connection. This can happen when an invalid buffer length or too great a row count is specified. It usually indicates a bug in the user application. Action: Check parameters to Oracle calls. If the problem recurs, reduce all integer parameters, column values not included, to less than 32767. Please let me know how to solve this problem. Should I change any of the parameters in the init.ora file. Regards, Narasimhan -- 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).
Select Query -- Return all rows of column as a single record ??
guys, SQL select A from test ; A --- 1 2 3 4 . . . 10 i need the result as A -- 1234...10 how to do this ? can someone help me ? the env. is oracle 8.1.6. TIA. Prem J. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-03113: End of File on Communication Channel
Hello guys, I have an unexpected problem with this error. It appears when I executeand select statement showing bellow. The strange things isuntil nowI don't catch itnevermore. I use two RDBMS: Oracle 8.1.7 EE for Windows NT4 andOracle 8.1.7 EE for HPUX. The select statemen is: select tgr_error_id, count(tgr_error_id) from tim_logger where (TGR_CODICE_ID, TGR_DATETIME) not in (select tgr_codice_id, max(tgr_datetime) from tim_logger group by tgr_codice_id) and tgr_error_id'000' group by tgr_error_id order by tgr_error_id The table is: TABLE TIM_LOGGER ( TGR_ID NUMBER, TGR_CODICE_ID VARCHAR2 (14), TGR_DATETIME DATE, TGR_ERROR_ID CHAR (3)) Thanks
RE: Newbie to Oracle DBA
This will keep you busy. Enjoy. http://web.singnet.com.sg/~petermag/oracle.html Dave -Original Message-From: Santosh Varma [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 12, 2003 4:09 AMTo: Multiple recipients of list ORACLE-LSubject: Newbie to Oracle DBA Hello list, I wanted to know of some good sites where i can learn the basics as well as advanced DBA topics. Could anybody help me out ? Thanks and Regards, Santosh
Oracle connection through firewall
Hi everybody Since I'm a networking dummy, here's a question that might be easy to answer: I have to setup client access (Oracle Net) to an Oracle Database through a firewall. So far, I only know that the listener listens on a dedicated port (like 1521). After a client requested a connection, a dedicated server process is started (this is not an MTS environment) and the listener is informed about the port the server process wants to use to communicate with the client. The listener sends this information to the client and from thereon, the client can communicate with the server through this port. Now, I'm wondering about what ports do I have to keep open on the firewall between client and Oracle server ? 1521 is probably not enough, since this let's the client only reach the listener itself. What happens then ? Can I restrict Oracle Net to a range of ports for the server processes to be used (didn't find that in the fine manual) ? If so, how is this done ? Or do I have to go with Oracle connection manager ? Regards, Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED]. Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Installer, iAS and memory requirements
I had fun at home, I have an old Pentium II with 700M of RAM running Windows 2000 Pro... Installed 9iR2 -- no problem, I shrank the SGA as low as I dared. The OracleHTTPServer service works OK. Installed iDS9iR2 -- works, provided I stop most of the Oracle services in the services applet. I also only start one component of iDS at a time, as much as possible. I did work on a Designer project on this machine before though and ran into memory problems -- Designer would crash sometimes, or I would click on a utility icon and nothing would show up (not enough memory to start it, I imagine). Installed 9iAS -- Because I wanted Oracle Portal. it installed twelve new services on my machine. My wife at home ended up setting all the Oracle-related services to Startup Manual, and stopped every single one of them because she couldn't use the computer with all these services competing for resources. It seems to be there is a subset of components that reappear in the Oracle Suites -- OracleHTTPServer, TNSService, etc. It would be nice if the Installer was smart enough to detect these and not re-install copies. Or, if we had the option of installing only the components we want -- e.g. Portal, OID, Designer, with nothing else because we know we have the other pieces in other Oracle Homes. Come to think of it... it would be nice to have an Oracle Home cleanup tool to remove old Oracle Homes from a host. Or list Oracle Homes that are compatible, and merge them. It would also be nice to have the option of installing the current version of Apache instead of 1.x. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 11, 2003 7:44 PM To: Multiple recipients of list ORACLE-L And what about all that talk in this group (some time ago) , all the hope in making OEM a better product, this is the result ? A version what DEMANDS the install of that dinosaur-size, no-brain, unmanageable 9iAS ?? Crap, crap, a pile of crap. *** REPLY SEPARATOR *** On 24/01/03 at 09:48 [EMAIL PROTECTED] wrote: Sounds pretty interesting, IF your in a completely Oracle environment! On the other hand if you don't have 9IAS you may be out in the cold. Dick Goulet Reply Separator Author: Boivin; Patrice J [EMAIL PROTECTED] Date: 1/24/2003 5:11 AM Oracle just posted an item, they announced the next version of the OEM. http://196.30.226.221/sections/enterprise/2003/0301240730.asp?A=TES http://196.30.226.221/sections/enterprise/2003/0301240730.asp?A=TESS=Soft w are%20TestingT=SectionO=FPSH S=Software%20TestingT=SectionO=FPSH I checked on OTN, nothing is available for download there. Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 TITLERE: Program name in v$ views/TITLE META content=MSHTML 6.00.2800.1126 name=GENERATOR/HEAD BODY DIVSPAN class=487032612-24012003FONT face=Arial color=#ff size=2Oracle just posted an item, they announced the next version of the OEM./FONT/SPAN/DIV DIVSPAN class=487032612-24012003FONT face=Arial color=#ff size=2FONT size=2 PA href=http://196.30.226.221/sections/enterprise/2003/0301240730.asp?A=TESa mp;S= Software%20Testingamp;T=Sectionamp;O=FPSHhttp://196.30.226.221/sections /ente rprise/2003/0301240730.asp?A=TESamp;S=Software%20Testingamp;T=Sectionamp ;O=FP SH/A/P Pnbsp;/P/FONT/FONT/SPAN/DIV DIVSPAN class=487032612-24012003FONT face=Arial color=#ff size=2I checked on OTN, nothing is available for download there./FONT/SPAN/DIV DIVSPAN class=487032612-24012003FONT face=Arial color=#ff size=2/FONT/SPANnbsp;/DIV DIVSPAN class=487032612-24012003 PFONT face=Courier New size=2Patrice Boivin/FONT BRFONT face=Courier New size=2Systems Analyst (Oracle Certified DBA)/FONT /P PFONT face=Courier New size=2Systems Admin amp; Operations | Admin. et Exploit. des systèmes/FONT BRFONT face=Courier New size=2Technology Servicesnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; | Services technologiques/FONT BRFONT face=Courier New size=2Informatics Branchnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; | Direction de l'informatique /FONTBRFONT face=Courier New size=2Maritimes Region, DFOnbsp;nbsp;nbsp;nbsp;nbsp; | Région des Maritimes, MPO/FONT /P PFONT face=Courier New size=2E-Mail: [EMAIL PROTECTED]/FONT /PBR/SPAN/DIV/BODY/HTML -- Please see
RE: Breaking down values in a large table
Thanks Waleed, that is exactly what I wanted. And thanks to all the others who responded John -Original Message- Sent: 11 February 2003 22:54 To: Multiple recipients of list ORACLE-L I hope this helps: -- drop table test_bal ; -- -- create a sample table -- create table test_bal( my_pk number); -- -- Insert sample data -- begin for i in 100..21000 loop insert into test_bal values (i); end loop; end; -- select b.rows_cnt as table_rows_cnt, c.mrownum as acc_rows_cnt, c.my_pkas bucket_end_inclusive, ceil(c.mrownum * 4/ b.rows_cnt) as bucket_id from (select a.*,rownum mrownum from (select my_pk from test_bal order by 1) a) c, (select count(*) rows_cnt from test_bal) b where ceil((c.mrownum + 1)* 4/ b.rows_cnt) ceil( c.mrownum * 4/ b.rows_cnt); -- Regards, Waleed -Original Message- Sent: Tuesday, February 11, 2003 9:19 AM To: Multiple recipients of list ORACLE-L Listers, I have a table of 125M rows (not partitioned) which I am exporting. I want to break the export into 4 dmp files using the query command on the pk column. I am looking at how the best way of finding the values of the PK (number) which are at 25%, 50% and 75% ish for the table so that I can get 4 evenly sized exports My query line in the parameter file will be along the lines of where 1) log_no xx 2) log_no = xx and yy 3) log_no = yy and zz 4) log_no = zz I am thinking of a sql something like the following Select /*+ index ffs(table_name index_name) */ Log_no , floor(log_no / 4), count(*) From table_name group by floor(log_no / 4), log_no Version is 8.1.7.1 Can anybody help please Thanks John -- 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: [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).
ocfs
Hello, When mounted, oracle's cluster filesystem is listening on udp port specified by parameter ip_port in /etc/ocfs.conf. Does that mean, i can mount it over the lan? If yes, then how (mount -t ocfs -o ...)? If no, then what is the true usage of this port? Bruno Vanters Junior DBA TietoEnator Financial Solutions 41 Lacplesa str., Riga, LV-1011, Latvia phone: +371 7286660, fax: +371 7243000 Mob. phone: +371 6337831 www.tietoenator.lv -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruno Vanters INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBA (Oracle) /Peoplesoft Developer Needed in Colorado
Boulder, Colorado company needs a Peoplesoft Developer/DBA (Oracle) for a full time staff position. The opportunity is an engineering position requiring intimate knowledge of PeopleSoft, not just a PeopleSoft administration/DBA position. Local Candidates preferred, but the company will consider candidates who will pay their own relocation expenses. PLEASE Do Not send your resume for this position Unless you have the skills outlined below for this position. Please Do Not send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. No H-1B candidates please. *Description: PeopleSoft developer and/or PeopleSoft Database administrator to help architect and code interfaces into PeopleSoft for flagship enterprise application management product. This position will work directly with the core engineering team from product inception to delivery. *Responsibilities: - Architectural design, development, delivery and maintenance/enhancements of the PeopleSoft specific components of the product. - Implementation and accountability for functional completeness and delivery against performance, supportability, scalability, and testability goals. - Complete testing at unit and system levels. - Installation support for beta customers and live customers. - Works with other professional services team members to ensure successful engagements. - Act as technical guide and mentor to other team members. - Create and meet accurate schedule estimates. - Strong problem solving skills with an eye for the big-picture and capable of delving into details when required. Must be creative; be able to develop new simple approaches to complex design problems. *Requirements: -4+ years experience developing in the PeopleSoft environment (PeopleSoft 7.x, and 8.x required) -Intimate knowledge of PeopleCode -Intimate knowledge and experience using PeopleTools -4+ years experience in database administration of PeopleSoft in significant PeopleSoft installation -Oracle administration (of PeopleSoft) required. -5+ years experience in the role of architect designing and implementing advanced applications employing a back end database -5+ years experience in shrink wrapped application development. -5+ years software development experience (Java/C++). -Strong object-oriented, abstraction, analysis design skills. -Must have a solid history of delivering quality software as well as having enterprise application development experience. -Travel to customer sites could be required. -The ability to handle multiple concurrent activities and have a flexible positive attitude. -Works well with team members, fosters learning and helping attitude, motivated to brainstorm and solve problems his or his team members, and able to mentor other engineers. -Works well in small teams -Excellent written and verbal communication skills. -U.S. citizenship or permanent residecy is also required. Base salary- depending on experience. For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/Boulder/Peoplesoft/Oracle/JB ph: 1-800 -549-8502 All Submissions are handled in confidence. *We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the position described above- if it is not a match for your skills. Thanks, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: Oracle connection through firewall
-Ursprüngliche Nachricht- Von: Stefan Jahnke [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 12. Februar 2003 13:14 An: Multiple recipients of list ORACLE-L Betreff: Oracle connection through firewall Hi everybody Since I'm a networking dummy, here's a question that might be easy to answer: I have to setup client access (Oracle Net) to an Oracle Database through a firewall. So far, I only know that the listener listens on a dedicated port (like 1521). After a client requested a connection, a dedicated server process is started (this is not an MTS environment) and the listener is informed about the port the server process wants to use to communicate with the client. The listener sends this information to the client and from thereon, the client can communicate with the server through this port. Now, I'm wondering about what ports do I have to keep open on the firewall between client and Oracle server ? yes, you have to do this. 1521 is probably not enough, since this let's the client only reach the listener itself. What happens then ? Can I restrict Oracle Net to a range of ports for the server processes to be used (didn't find that in the fine manual) ? As far as I know OracleNet can not do this for you- this is an operating system issue. This means you should configure your OS regarding the acceptable range of local ports, but this range will be for all servers on this host, not only for Oracle. For more info, you can follow this link http://en.tldp.org/LDP/solrhe/Securing-Optimizing-Linux-RH-Edition-v1.3/cha p6sec70.html (Linux specific, although the other OSes should have similar parameters). Then you should configure your firewall to accept from outside the configured range of ports. But would make the existence of the firewall pointless. If so, how is this done ? Or do I have to go with Oracle connection manager ? Definitely. Best regards. Milen Kulev Regards, Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED]. Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kulev, Milen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Count(*) last 30 seconds
Title: Message Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*)-- 1466196 Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 secondsYou are doing Full-Table-Scans.1. What's the average row length ? How many columns does the table have ?2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of theblocks ? What is the query-run-time if you re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 -0800, you wrote: Hi list,I issue a select count(*) from mytable and last 30 seconds.The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space.I consider that time exagerated.The TBS is LMT with a Uniform size of 128 MB.The block size is 8MB, version 9.2.0.1.0 in Windows 2000.Where should I start looking ???TIARamon E. Estevez[EMAIL PROTECTED]809-565-3121 Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index update = Delete + insert ?
Vijay,List When Updating to the Field to the SAME (Previously Existent) Data Value , Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 12:50 PM To: VIVEK_SHARMA Hi Vivek, Index rows are first deleted and then inserted rather than update... regards, Vijaya Chander V.S -Original Message- Sent: Wednesday, February 12, 2003 12:43 PM To: LazyDBA.com Discussion Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- 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).
RE: Count(*) last 30 seconds
That's approx 100 records per blocks. What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ? Also, what is the elapsed time for the query if you re-run the query immediately ? [the first run fetched everything in physical reads, the second run should still find some or most blocks in the SGA, unless the DB_CACHE_SIZE or DB_BLOCK_BUFFERS is very small]. Hemant At 05:18 AM 12-02-03 -0800, you wrote: Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*) -- 1466196 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics -- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K Chitale Sent: Tuesday, February 11, 2003 10:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Count(*) last 30 seconds You are doing Full-Table-Scans. 1. What's the average row length ? How many columns does the table have ? 2. How many consistent gets does the count(*) cause ? [ie, how many blocks does it actually have to read ?] 3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of the blocks ? What is the query-run-time if you re-run the query immediately again ? Hemant At 08:19 AM 11-02-03 -0800, you wrote: Hi list, I issue a select count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. I consider that time exagerated. The TBS is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 2000. Where should I start looking ??? TIA Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle License for Training
It never hurts to diversify your skills. As everyone on this list will tell you MySQL is no where near on equal footing with Oracle. However, it is used in A LOT of small shops in the Linux/Apache/PHP/MySQL combination. Keeping your options open and current is always a good idea. Rodd Holman On Tue, 2003-02-11 at 17:04, Weiss, Rick wrote: A question for the DBA Gods on this list: Is it worth the time/effort to download MySQL and learn it? Is there going to be a viable (meaning $$) market for the product in the future? Or should I leave all the egg$ in the Oracle basket? Musing for fun and profit. Rick Weiss -Original Message- Sent: Tuesday, February 11, 2003 3:34 PM To: Multiple recipients of list ORACLE-L MS SQL costing less than Oracle is only partly true. If you load up MS with the extras that constitute a std feature set on Oracle, Oracle is very competitive. Been lots of comparisons on that. Now PostgreSQL and MySQL, those *are* less expensize than MS SQL and Oracle. :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Rodd Holman [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Oracle connection through firewall
This is from one of previous postings: Oracle has a registry setting that will force all traffic for a session through the same port that the listener connection was made on (e.g. 1521). Place the USE_SHARED_SOCKET parameter in the registry under HKEY_LOCAL_MACHINE:Software:Oracle with a value of TRUE, and restart Oracle and the listener for it to take effect. It also doesn't hurt to set this parameter as a system environment variable as well. And yes, Connection Manager is another option. Also, there are firewalls that are Net8 aware. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 12, 2003 7:13 AM Hi everybody Since I'm a networking dummy, here's a question that might be easy to answer: I have to setup client access (Oracle Net) to an Oracle Database through a firewall. So far, I only know that the listener listens on a dedicated port (like 1521). After a client requested a connection, a dedicated server process is started (this is not an MTS environment) and the listener is informed about the port the server process wants to use to communicate with the client. The listener sends this information to the client and from thereon, the client can communicate with the server through this port. Now, I'm wondering about what ports do I have to keep open on the firewall between client and Oracle server ? 1521 is probably not enough, since this let's the client only reach the listener itself. What happens then ? Can I restrict Oracle Net to a range of ports for the server processes to be used (didn't find that in the fine manual) ? If so, how is this done ? Or do I have to go with Oracle connection manager ? Regards, Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED]. Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT:Disk Array performance benchmark?
Hi, friends: We plan to buy new disk array for new splitted database server, Server will be V880 with 8CPU and 8G memory, but we are considering whether to buy new T3 Disk array or use diskarray from other vendor. We have old T3ES with 256M cache, and it seems does not meet the requrement of our database server, high IO wait during peak time. We tried to fine tune the sql but still get wait event with IO the top wait event. So I want to try to verify how much the new T3 is better than old T3. There must be friends in this who have experience in IO system benchmarking , please share your advice, also your experience with the New T3-ES storage. Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) -- 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: Count(*) last 30 seconds
Title: Message That's not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. Depending on your disk layout that's pretty optimal, I think. Mario -Original Message-From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 2003 14:19To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*)-- 1466196 Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 secondsYou are doing Full-Table-Scans.1. What's the average row length ? How many columns does the table have ?2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of theblocks ? What is the query-run-time if you re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 -0800, you wrote: Hi list,I issue a select count(*) from mytable and last 30 seconds.The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space.I consider that time exagerated.The TBS is LMT with a Uniform size of 128 MB.The block size is 8MB, version 9.2.0.1.0 in Windows 2000.Where should I start looking ???TIARamon E. Estevez[EMAIL PROTECTED]809-565-3121 Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Count(*) last 30 seconds
Title: Message Ramon, Our Win2k boxes get between 1000-2000 gets a second off a SAN. Are you using compressed folders to store your datafiles? Whats Multi_block_read_countset to? Set MBRC to 32 (32x8K=256K). Make your extent sizesare divisable by 256Kto reduce gets Regards Adrian -Original Message-From: Broodbakker, Mario [mailto:[EMAIL PROTECTED]]Sent: 12 February 2003 14:09To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds That's not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. Depending on your disk layout that's pretty optimal, I think. Mario -Original Message-From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 2003 14:19To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*)-- 1466196 Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 secondsYou are doing Full-Table-Scans.1. What's the average row length ? How many columns does the table have ?2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of theblocks ? What is the query-run-time if you re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 -0800, you wrote: Hi list,I issue a select count(*) from mytable and last 30 seconds.The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space.I consider that time exagerated.The TBS is LMT with a Uniform size of 128 MB.The block size is 8MB, version 9.2.0.1.0 in Windows 2000.Where should I start looking ???TIARamon E. Estevez[EMAIL PROTECTED]809-565-3121 Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Newbie to Oracle DBA
Santosh - Web sites are excellent for finding the answer to specific questions, but may leave gaps in your knowledge. If I may offer a suggestion, get a good fundamentals book, and work through it trying all the examples you can. A good recommendation is Oracle9i DBA 101 http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1045060858/sr=8 -2/ref=sr_8_2/104-5488440-6447968?v=glance http://www.amazon.com/exec/obidos/tg/detail/-/0072224746/qid=1045060858/sr= 8-2/ref=sr_8_2/104-5488440-6447968?v=glances=booksn=507846 s=booksn=507846 Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 4:09 AM To: Multiple recipients of list ORACLE-L Hello list, I wanted to know of some good sites where i can learn the basics as well as advanced DBA topics. Could anybody help me out ? Thanks and Regards, Santosh -- 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).
parallel index creation again:in which case, can we use parallel with single cpu env?
hi, dba friends: some paper said, pqo should only be used in SMP machines, while others say, We can also use pqo in uniprocessor machines in some case. I am trying to use parallel index creation in the following env: Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory. Oracle 9.2 Table contains 2200 records,1.2GB Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate 3 disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on seperate 3 disks. SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Elapsed: 00:00:00.16 SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Elapsed: 00:06:48.04 This machine is exclusived used my me and It seems that PQO is rather slower than single thread. So is it still possible to use PQO on single processor machines? Please share your experience and idear. Thanks. Wait event like: Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- PX qref latch 48,371 41540.94 PX Deq: Execute Reply 176 34033.54 PX Deq Credit: send blkd 47,704 24824.47 control file parallel write 112 5 .48 PX Deq Credit: need buffer 1,835 4 .38 - ^LWait Events for DB: ORA9 Instance: ora9 Snaps: 19 -20 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) -- 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: Question About RMAN
RMAN and export are two different animals. RMAN, in general, should not be though of as a logical backup facility. That is to say, you should not backup your database with the though of just recovering one table. RMAN really isn't meant for that. However, that being said, it is very possible to recover a lost table using an RMAN backup. You could do this either by using tablespace point in time recovery, if the table is in it's own tablespace by itself, or you could recover your database to a temporary database and then export the table. The primary difference between a logical backup and recovery with exp/imp and a database recovery with RMAN is in terms of consistency. With a logical backup, you have little promice of consistency of the data you recover, and of course you don't have point in time recovery either. With RMAN, you will have consistency in your data, and point in time recovery. HTH, RF Robert G. Freeman TUSC - The Oracle Experts - www.tusc.com Author of Oracle Press Books Oracle9i RMAN Backup and Recovery Oracle9i New Features -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 6:13 AM Hi, I'm planning work with this aplication(RMAN). I think that everybody agree with me after many questions in this group. Today, my database are in Archive mode and I make manually the copy of datafiles. And I run the export utility too. About RMAN. If I want to recover only a table ? Can I do it ? It´s very easy to do using a export file. And about using RMAN application ? Thanks -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rman and database shutdowns
List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
View HELP Please!
I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Commit boundary - Stripe Unit Size Co-relation
Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: parallel index creation again:in which case, can we use parallel with single cpu env?
Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and the result is persistent across difference test case: So, I think buffer is not the cause of the parallel execution slower. But I really do not get other parameter to tune:( SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.16 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:48.04 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:51.92 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging; Index created. Elapsed: 00:06:26.23 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:44.58 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:49.09 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:46.79 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.14 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:44.51 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 18:40:00 ,you wrote£º=== Dear Chao. Did you try change order of index's creating- first noparallel, second with parallel. I think you will look other results. hi, dba friends: some paper said, pqo should only be used in SMP machines, while others say, We can also use pqo in uniprocessor machines in some case. I am trying to use parallel index creation in the following env: Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory. Oracle 9.2 Table contains 2200 records,1.2GB Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate 3 disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on seperate 3 disks. SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Elapsed: 00:00:00.16 SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Elapsed: 00:06:48.04 This machine is exclusived used my me and It seems that PQO is rather slower than single thread. So is it still possible to use PQO on single processor machines? Please share your experience and idear. Thanks. Wait event like: Top 5 Timed Events ~~ Total Event WaitsTime (s) Ela Time --- PX qref latch 48,371 41540.94 PX Deq: Execute Reply 176 34033.54 PX Deq Credit: send blkd 47,704 24824.47 control file parallel write 112 5 .48 PX Deq Credit: need buffer 1,835 4 .38 - ^LWait Events for DB: ORA9 Instance: ora9 Snaps: 19 -20 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second -- Best regards Michael Ivanov, TD ERA = = = = = = = = = = = = = = = = = = = = -- 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
buffer pools
Hello, Env: 9.2.0.2 on Solaris 2.9 We are currently considering a proposal regarding the use of the 3 buffer pools represented by db_cache_size, db_keep_cache_size, and db_recycle_cache_size. I am wondering if this is a good idea or a bad idea. The proposal follows. The buffer pool space can be divided into 3 separate pools: db_cache_size, db_keep_cache_size, and db_recycle_cache_size. Despite the names, the blocks are all treated exactly the same with regard to the Least Recently Used (LRU) algorithm. The retention time of a database block in any of these pools is dependent upon the size of the pool, how often the block is referenced, and the probability of the block being displaced by a more popular block. The names Oracle has assigned to these pools reflect more of an intention than anything else; the keep pool is intended to be sized large enough to retain all frequently-referenced data; the recycle pool is intended to be sized small to recycle blocks not desired in memory, and the db_cache_size pool is intended for everything else. Funtionally, Oracle could have named these db_cache1, db_cache2 and db_cache3. Currently, we utilize just 1 cache, the db_cache_size. I am proposing that we utilize all 3 caches in some way; the rationale for this is that it is better to have 3 smaller caches of 800M, each managing 1000 objects, than it is to have 1 large cache of 2.4G managing 3000 objects. The rest of this proposal suggests a method for distributing the various tables and indexes in our system to the 3 caches; it suggests a roughly equal division among the 3 caches based upon subject area and usage stats. 1) Is the information above accurate? 2) Is it better to have 3 smaller caches than 1 large cache? (Assuming the objects are distributed among the caches about equally based upon usage stats) Thanks to those who read this far. More thanks to any responders. Most thanks to responders with helpful suggestions. -- 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).
Schema specific grants
Good Morning All Im looking at trying to grant privilidges to a guest user (who does not own the tables) I know I can do it for individual tables Eg GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest; but I need to grant to an entrie schema Like GRANT INSERT ANY SCHEMA_NAME.* TO Guest; Anyone have the syntax for that? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please!
Freeman Robert - IL, Hi, can you show us the different execution path for the view and the sql? I think that is the key to solve the performance problem?Maybe hint like no_merge help? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 07:18:00 ,you wrote£º=== I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
database relationship chart ??
Hi, I got the Erwin(3.5.2) working for generate charts for one schema. Thank you for all the help. The chart doesn't show PK, FK relationship. There is on lines between entities to show that they are related. How does Erwin do that? Or maybe is there other tools that can generate db chart with relations? Thanks. Janet __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: Re[2]: Re[2]: Re[2]: RMAN: I don't trust it
Lyndon A rule of thumb in job-seeking is when you don't have experience, your education counts all the more. This applies when you are just starting your career or when you are changing careers. I haven't seen too many DBA job postings that require a BSCS (always glad to be educated, though), but I can see if there were two candidates who had no Oracle experience or other IT experience, the one with a BSCS might be selected. Have you considered getting the OCP? One heck of a lot less effort than a BSCS, and might carry more weight when being considered for a position. I don't think the OCP is a cure-all, but I think it can demonstrate a sincere interest in an Oracle career. Think of it as a way to separate yourself from other wannabes. My impression is that during the dot-com wave a lot of people crowded into the IT field, and some of them jumped on Oracle. That might leave the field crowded at the moment, but any field has turnover. Some people become discouraged, others find other careers that suit them better, etc. If you take a 20-year perspective, the Oracle DBA field has nearly always had more demand than supply. Hey what am I saying . . . no way! If anyone on this list is getting discouraged, this is the time to pursue that truck driving career you've always dreamed of. Go! Hurry! ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 11, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Quoting DENNIS WILLIAMS [EMAIL PROTECTED]: Lyndon To me, being a DBA is more an attitude than an HR position. Study what DBAs do and that will carry you forward. I don't see what having a BSCS has to do with it. And I speak as someone who has done a lot of computer science at the graduate level. During the dark days, prepare, so when the industry picks up again, you are in a position to ride the surging wave. B.Sc. more from the standpoint of getting your foot into that door. I don't think it actually helps you get the job done, only that it will get you the job in the first place. Even junior position DBA require a B.Sc. (those that I've read). As far as the wave is concerned, it's getting too crowded at the crest. Too many people wannbea DBA. -- Lyndon Tiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rman and database shutdowns
Ron, Are you saying that you use VMS backup commands to backup the database data files while the database is open? This will result in unreliable backups unless you put the tablespaces in hot backup mode. If you are not using RMAN, you should have a script that backs up each datafile without using the /incremental qualifier on the VMS backup command. If you are doing a cold backup with RMAN, the database should be a MOUNT state. If it is mounted and open, then RMAN will do a hot backup. RMAN won't care how many times you did a shutdown/startup before you execute your RMAN backup. Jay [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: copyright
Tim Whatever you write is automatically copyrighted. In other words, someone can't just freely copy what someone else has written and claim it is their own (at least in the last dozen years). A good short resource is: http://www.ott.caltech.edu/security/copyright_tutorial_Basic.htm Now, as we all know there is a difference between rights and being able to defend those rights. If you have placed a copyright notice on your script, that enhances your claims and is a practical measure that makes other parties aware of your rights that they might otherwise unwittingly trespass. Next, can you prove prior existence of your script? If it was published in a magazine that would be very strong proof. You can file your script with the Copyright Office for a small fee. I think your request for downloaders to keep your name on the script is very wise. As far as posting it on a Web site, the Internet is notoriously stateless. It is difficult to establish original dates. The other problem with scripts, at least very simple ones, is the question of whether given the same requirements, is it possible someone by chance would write the same script? As to actions, it all begins with a simple notice to the other person. My impression is that the vast majority of these situations are cleared up by simply notifying the other party that they stepped on your toes. Large companies in particular are very conscientious about not getting caught in a violation. Of course, you may have the idea that they will keep publicizing the script with your name attached and their action may be to just remove the script. Or they may reply that another party claims to have originated the script. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 11, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Dave, I'm not saying that what they did is OK. But I don't see scripts posted on the internet as IP, plain and simple. You referenced my script; I posted it there purposely for people to download and use. On my website, I request downloaders to provide attribution by keeping my name in the script, but I don't claim any legal right to force them to do so. Please notice the lack of the word copyright in the script. Removing my name from the script would simply be bad manners and irritating to me, nothing more... Jared had a copyright notice on the script? I'm not a lawyer, but a defensible copyright requires more than simply saying so, doesn't it? Kind of like a no trespassing sign on your property; you had better have the plattes and surveys to back your assertion of ownership in court, else you risk countersuit. Same with a copyright; you have to prove that you really wrote it, didn't copy it from someone else, etc, etc, etc. Life is too short for all that... Also, I did not say that the battle was futile nor did I say that illegal actions should not be fought. I simply don't believe that what happened enters the realm of litigation. My reference to tilting at windmills was not meant to convey an image of futility; it was meant to convey someone who is attacking misguidely, while under delusion. I simply do not believe that IP theft occurred here, merely bad etiquette and bad manners (which I find far more offensive). Further, I think that any discussion of intellectual property neccesitates more humility. Surely, nothing that any of us does is truly original. I am constantly reminded of two sayings: If I have seen further it is because I stand on the shoulders of giants and Look, listen, and learn; for an original mistake is as rare as an original idea. Intellectual property is deeper than simply slapping the word copyright on everything one has written. There is a code of honor surrounding the use of material written by others. Codes of honor can be broken by anyone without honor, any minute of any day, legally. It is only those with that sense of honor who would not dream of betraying that trust. I might be naive, but I believe the entire free software movement is based on that sense of honor. Can you imagine what would happen to the world if GNU suddenly demanded payment? Not a world I want to inhabit... I do not wish to debate this any further -- you have your view, I have mine. End of story. The entire reason for my response to you was not to provoke you, nor to earn your enmity, nor to debate property law. Rather, I was irritated that Jared would be branded as doing a disservice to anyone because he did not agree that there were bigger ramifications. I requested an apology from you on his behalf, and I believe you have done so. End of story. Peace. Unskyld. Back to work... -Tim - Original Message - To: [EMAIL PROTECTED]; Oracle RDBMS Community Forum [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 11, 2003 12:06 PM Hi Tim, A few thoughts, It's a script - Oracle RDBMS is a bunch of scripts
Re: Re[2]: Re[2]: Re[2]: Re[2]: RMAN: I don't trust it
On Wednesday 12 February 2003 08:14 am, DENNIS WILLIAMS wrote: Lyndon A rule of thumb in job-seeking is when you don't have experience, your education counts all the more. I agree. No Comp. Sci. education means - To quote Oracle 9i: Unemployable, Can't break-in, Just can't get it. This applies when you are just starting your career or when you are changing careers. I haven't seen too many DBA job postings that require a BSCS (always glad to be educated, though), but I can see if there were two candidates who had no Oracle experience or other IT experience, the one with a BSCS might be selected. Have you considered getting the OCP? I have OCP, SCJP, LPI, SCSA, SCNA, EIEIO. I do believe that these certifications are worthless without job experience. Order of importance: 1) People you know (i.e. kiss ass) 2) Experience 3) Education 4) Certifications One heck of a lot less effort than a BSCS, and might carry more weight when being considered for a position. I don't think the OCP is a cure-all, but I think it can demonstrate a sincere interest in an Oracle career. Think of it as a way to separate yourself from other wannabes. I work for a company where a third are wannabes, and they kiss ass better than me : \ My impression is that during the dot-com wave a lot of people crowded into the IT field, and some of them jumped on Oracle. That might leave the field crowded at the moment, but any field has turnover. Some people become discouraged, others find other careers that suit them better, etc. If you take a 20-year perspective, the Oracle DBA field has nearly always had more demand than supply. Hey what am I saying . . . no way! If anyone on this list is getting discouraged, this is the time to pursue that truck driving career you've always dreamed of. Go! Hurry! ;-) I will be while before things get cleared up. By then, I'll be a pensioner. -- Lyndon Tiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Re[2]: [new info] Redhat Advanced Server Dev Edition
Dennis, We're going from 2 database servers to 1 for production. Therefore it's already licensed as needed to be a standby served, saving us the extra dollars. Dick Goulet DENNIS WILLIAMS [EMAIL PROTECTED] wrote on 2/11/03 1:54 pm: Dick How is the standby database cheaper? I understood from previous list discussions that you had to license the standby server as well. As the hardware and O/S become commodities, I think Oracle would like to avoid becoming a commodity. Commodity prices are low, as any farmer can tell you. But the further issue is how do Oracle DBAs avoid becoming a commodity?. Maybe the next question coming is why should we pay more for a DBA when we're getting the computer and software so cheap? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 11, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Jared, I don't know about the rest of the list members, but the company I work for would like to have the technology but without the additional license expense. Therefore were going to do the standby database thing instead of RAC. Now if your into using Linux with low end PC's then maybe you can justify it. I don't know, it gives me the whillies when the software costs more than the hardware and OS combined. Dick Goulet Reply Separator Author: Jared Still [EMAIL PROTECTED] Date: 2/11/2003 8:29 AM This is all cool technology, and fun stuff to play with. It all begs the questions, How many of us work for a business that actually need this? Are they willing to pay $400/user $20k/CPU above the cost of Oracle 9i EE to use it? Are they willing to pay the extra overhead required to maintain it? I'm not sure the ROI is there for many of us. Though downtime at our business is somewhat expensive, I think that a failover system or even standby database will provide adequate coverage for us, which is indeed a hot topic here right now, after our Dell SAN put us out of business for 36 hours. RAC wouldn't have helped much there. Niether would a cluster for that matter. Standby DB would have been perfect. This whole push of RAC by Oracle reminds me very much of the mlife phone campaign by ATT. Do you really need to take pictures with your phone? And what is the point of sending text messages to someone elses phone when you could just call them? ATT needs you to buy this stuff, because they have it for sale. I see RAC in a similar light. Do you need RAC? Oracle needs you to 'need' it, because they need some reason for you to spend more money on their product. Jared On Saturday 08 February 2003 21:23, Richard Ji wrote: To those who are interested in running RAC on Linux. I know we have been talking about RAC on linux lately. This is great news Redhat has made a special developer's edition for their Advanced Server which only costs $60! So we don't have to shell out $699 for a copy of RHAS 2.1 to play with RAC. http://www.redhat.com/software/advancedserver/developer/ Have fun. Richard Ji -- 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E- * This message has been truncated. The entire message is available on your desktop e-mail client. -- 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).
Ewrin question ??
Hi all, I have couple of questions about ERWIN. -- I need to reverse engineer for just one schema. I specify the login name/pwd for that schema, but ERWIN generates charts for objects do NOT belong to that schema too! How do I get charts for a specific schema only? -- When connect to the db, Erwin asks about target server, ef: ODBC, Oracle, Sybase, etc. I can connect to the db through ODBC, but cann't connect using Oracle directly. Why ? Really appreciate any thoughts. Thank you in advance. Janet __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: utf8
Title: Message Thanks Ian, Unfortunately I haven't been able to get this to work either Running the command below from a Windows sqlplus session SQL update product set name = chr(2122) where sku = 'IB1'; 1 row updated. SQL commit; Commit complete. SQL select dump(name) from product where sku = 'IB1'; DUMP(NAME)Typ=1 Len=2: 8,74 the windows NLS_LANG is set to AMERICAN_AMERICA.WE8MSWIN1252 which according to Metalink correct.. -Original Message-From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 11, 2003 4:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: utf8 How about insert into TABLE_NAME (FIELD_NAME) values (CHR(2122)); Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message-From: Steve Main [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 11, 2003 2:44 PMTo: Multiple recipients of list ORACLE-LSubject: utf8 Hello List, I have built a database with a character set of UTF8 and I'm trying to insert character code 2122 "(TM) Trademark". I'm having trouble coming up with a way to do this. anyone had to deal with this before? Thanks Steve
RE: Oracle License for Training
Rick - I agree that it can't hurt. Looking at another database can even give you a fresh perspective on Oracle. Some assignments involve converting an application from MySQL to Oracle. The critical question in my mind is whether you see yourself as more of a development DBA or a production DBA? I could see many more opportunities for a development DBA to be involved with MySQL than a production DBA. Hopefully this won't restart the discussion of what constitutes each. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 11, 2003 5:04 PM To: Multiple recipients of list ORACLE-L A question for the DBA Gods on this list: Is it worth the time/effort to download MySQL and learn it? Is there going to be a viable (meaning $$) market for the product in the future? Or should I leave all the egg$ in the Oracle basket? Musing for fun and profit. Rick Weiss -Original Message- Sent: Tuesday, February 11, 2003 3:34 PM To: Multiple recipients of list ORACLE-L MS SQL costing less than Oracle is only partly true. If you load up MS with the extras that constitute a std feature set on Oracle, Oracle is very competitive. Been lots of comparisons on that. Now PostgreSQL and MySQL, those *are* less expensize than MS SQL and Oracle. :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Weiss, Rick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Count(*) last 30 seconds
Title: Message Hermant and Chitale, DB_FILE_MULTIBLOCK_READ_COUNT=32 DB_CACHE_SIZE big integer 16777216 DB_BLOCK_BUFFERS = 0 Tablespace is LMT with a uniform size of 128 MB, DB not in archive mode is for a DW system. The time for the first run and the re-run last the same. To my understanding the table has only one extent. This query runs in about 7 seconds. In my production DB runs inmediately that is in NT also but 8.1.7. SELECT TABLESPACE_NAME, EXTENT_ID, BYTES/1048576, BLOCKSFROM DBA_EXTENTSWHERESEGMENT_NAME = 'DM_VENTAS' TABLESPACE_NAMEEXTENT_ID BYTES/1048576BLOCKS-- -- - --DTMVENTAS 0 128 16384 TKS -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Wednesday, February 12, 2003 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 secondsThat's approx 100 records per blocks.What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ?Also, what is the elapsed time for the query if you re-run the query immediately ?[the first run fetched everything in physical reads, the second run should stillfind some or most blocks in the SGA, unless the DB_CACHE_SIZE or DB_BLOCK_BUFFERSis very small].HemantAt 05:18 AM 12-02-03 -0800, you wrote: Hermant, SergeyThe table has 13 columns, the PK is formed for the first 11.There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows.This is the result with an auto trace. COUNT(*)-- 1466196Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K Chitale Sent: Tuesday, February 11, 2003 10:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Count(*) last 30 seconds You are doing Full-Table-Scans. 1. What's the average row length ? How many columns does the table have ? 2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?] 3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of the blocks ? What is the query-run-time if you re-run the query immediately again ? Hemant At 08:19 AM 11-02-03 -0800, you wrote: Hi list, I issue a select count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. I consider that time exagerated. The TBS is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 2000. Where should I start looking ??? TIA Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Re: View HELP Please!
Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schema specific grants
CREATE ROLE GUEST_USER; SPOOL GIMME.SQL SELECT 'GRANT INSERT ON '||TABLE_NAME||' TO GUEST_USER;' FROM USER_TABLES; SPOOL OFF @GIMME GRANT GUEST_USER TO GUEST; Bob Metelsky bmetelsky To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @cps92.com cc: Sent by: rootSubject: Schema specific grants 02/12/2003 10:30 AM Please respond to ORACLE-L Good Morning All Im looking at trying to grant privilidges to a guest user (who does not own the tables) I know I can do it for individual tables Eg GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest; but I need to grant to an entrie schema Like GRANT INSERT ANY SCHEMA_NAME.* TO Guest; Anyone have the syntax for that? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schema specific grants
Thnaks for the response. I realised that immediately after I posted. This is what Ive done /*@D:\createinsert.sql drop user Guest; create user Guest identified by * default tablespace tables temporary tablespace temp; grant connect to Guest; */ set echo off set feedback off set pages 0 set heading off set lines 80 set verify off Spool D:\insertperm.sql select 'GRANT INSERT ON SCHEMA_NAME.'||'' || table_name ||' TO Guest' ||';' from sys.all_tables where owner = 'SCHEMA_NAME'; select 'GRANT SELECT ON SCHEMA_NAME.'||'' || table_name ||' TO Guest' ||';' from sys.all_tables where owner = 'SCHEMA_NAME'; select 'GRANT UPDATE ON SCHEMA_NAME.'||'' || table_name ||' TO Guest' ||';' from sys.all_tables where owner = 'SCHEMA_NAME'; select 'GRANT DELETE ON SCHEMA_NAME.'||'' || table_name ||' TO Guest' ||';' from sys.all_tables where owner = 'SCHEMA_NAME'; select 'GRANT EXECUTE ON SCHEMA_NAME.'||'' || object_name ||' TO Guest' ||';' from all_objects where object_type = 'PACKAGE' and owner = 'SCHEMA_NAME' and object_name like 'CPS%'; spool off; start D:\insertperm.sql -- exit ; I was hoping for a syntax parameter but this works as well Thanks Bob Bob, the best thing I can offer is the following: set lines 150 set pages 2000 set trimspool on select 'grant insert on ' || table_name || ' to Guest_Role;' from user_tables / Create the role named in the script (or change the role name to the actual Oracle username) and grant the role to the user. Run it from the schema where the tables exist. Spool the output to a .sql file and run the resulting file back thru sqlplus. You may also want to create either public or private synonyms for the user to make their life a little easier. I like Roles better than assigning stuff directly to the user - just easier to manage. good luck! Tom Mercadante Oracle Certified Professional -Original Message- From: Bob Metelsky [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Subject: Schema specific grants Good Morning All Im looking at trying to grant privilidges to a guest user (who does not own the tables) I know I can do it for individual tables Eg GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest; but I need to grant to an entrie schema Like GRANT INSERT ANY SCHEMA_NAME.* TO Guest; Anyone have the syntax for that? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
database file sizes....more of smaller size or less of larger size?
Hi DBAs, Oracle 8.1.7 WinNT I would like pros/cons on datafile sizes. It is better to have more of less size or less with larger sizes and why? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rman and database shutdowns
Ron - I reread your question a little closer, and am still a little confused. Okay, you are doing both RMAN and cold backups? That is okay as long as you don't shut down the database while RMAN is performing its backup. We have been doing both for several months until we complete our RMAN disaster recovery test. You don't want the O.S. to back up the Oracle files when they are open and not in a hot backup state. So I don't follow the incremental backup / timestamp comment. You wrote Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? Absolutely. You could really confuse RMAN and personally I have enough challenges from a non-confused RMAN. You should choose to either recover using RMAN or recover without RMAN (taking an RMAN backup afterward), but no hybrids. While we've been doing both backups, thankfully we haven't had to perform a recovery, but if we had, I would tend to do an RMAN recovery because that is much simpler and it would increase my confidence in RMAN. Of course, since we are backing RMAN up to disk, I would have to ensure the RMAN backup files were available where RMAN last left them. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: buffer pools
Bill I don't think too many analytical studies have been done on multiple buffer pools, but since I am extremely interested in this subject, I'll provide my opinion, which is worth every bit you paid for it. Just dividing the buffer pool into three pieces, in my opinion, would degrade performance. The reason is you are denying Oracle the opportunity to use the total space most efficiently. The normal situation would probably be one buffer being overtaxed and the others undertaxed. So, why would we use three buffer pools? Well the answer must be that we have knowledge about the tables and their usage that Oracle doesn't have access to. The only thing Oracle knows about a data or index block is when it was last used and whether it was retrieved as a distinct select or a table scan. The usual philosophy is that you can pick some small tables that are used a lot and tell Oracle keep the blocks from this table in memory. Hence the KEEP pool. Again, not just any small tables, but the ones that are hit often. Then you pick some really vast tables whose access pattern is broad. That is, no hot blocks. You tell Oracle don't bother to keep blocks from this table in memory. Hence the RECYCLE pool. I've tried applying this philosophy, and it seems to help some, but hasn't made a vast difference. But I am very receptive to alternate philosophies, which is why I am responding to your question. Part of the problem is the usage pattern of the data changes frequently. Somebody kicks off a report that needs a lot of blocks that haven't been used for awhile. That is the fallacy of measuring the Buffer Hit Ratio. As you take successive measurements, it changes quite dynamically. So when you really make a change that will affect BHR, you must take quite a few measurements over time to confirm the effect of the change. Ideally you would apply some statistics principles to compare two samples of measurements and determine if they are indeed different. And of course keep track of the wait statistics to make sure your BHR measurements are valid ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Hello, Env: 9.2.0.2 on Solaris 2.9 We are currently considering a proposal regarding the use of the 3 buffer pools represented by db_cache_size, db_keep_cache_size, and db_recycle_cache_size. I am wondering if this is a good idea or a bad idea. The proposal follows. The buffer pool space can be divided into 3 separate pools: db_cache_size, db_keep_cache_size, and db_recycle_cache_size. Despite the names, the blocks are all treated exactly the same with regard to the Least Recently Used (LRU) algorithm. The retention time of a database block in any of these pools is dependent upon the size of the pool, how often the block is referenced, and the probability of the block being displaced by a more popular block. The names Oracle has assigned to these pools reflect more of an intention than anything else; the keep pool is intended to be sized large enough to retain all frequently-referenced data; the recycle pool is intended to be sized small to recycle blocks not desired in memory, and the db_cache_size pool is intended for everything else. Funtionally, Oracle could have named these db_cache1, db_cache2 and db_cache3. Currently, we utilize just 1 cache, the db_cache_size. I am proposing that we utilize all 3 caches in some way; the rationale for this is that it is better to have 3 smaller caches of 800M, each managing 1000 objects, than it is to have 1 large cache of 2.4G managing 3000 objects. The rest of this proposal suggests a method for distributing the various tables and indexes in our system to the 3 caches; it suggests a roughly equal division among the 3 caches based upon subject area and usage stats. 1) Is the information above accurate? 2) Is it better to have 3 smaller caches than 1 large cache? (Assuming the objects are distributed among the caches about equally based upon usage stats) Thanks to those who read this far. More thanks to any responders. Most thanks to responders with helpful suggestions. -- 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: DENNIS WILLIAMS INET: [EMAIL
RE: Schema specific grants
Bob, the best thing I can offer is the following: set lines 150 set pages 2000 set trimspool on select 'grant insert on ' || table_name || ' to Guest_Role;' from user_tables / Create the role named in the script (or change the role name to the actual Oracle username) and grant the role to the user. Run it from the schema where the tables exist. Spool the output to a .sql file and run the resulting file back thru sqlplus. You may also want to create either public or private synonyms for the user to make their life a little easier. I like Roles better than assigning stuff directly to the user - just easier to manage. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Good Morning All Im looking at trying to grant privilidges to a guest user (who does not own the tables) I know I can do it for individual tables Eg GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest; but I need to grant to an entrie schema Like GRANT INSERT ANY SCHEMA_NAME.* TO Guest; Anyone have the syntax for that? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Rman and database shutdowns UPDATE
Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schema specific grants
select 'grant insert on '||table_name||' to guest;' from user_tables spool \tmp\grant_insert.sql / @\tmp\grant_insert.sql there is no one shot command that I know of --- Bob Metelsky [EMAIL PROTECTED] wrote: Good Morning All Im looking at trying to grant privilidges to a guest user (who does not own the tables) I know I can do it for individual tables Eg GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest; but I need to grant to an entrie schema Like GRANT INSERT ANY SCHEMA_NAME.* TO Guest; Anyone have the syntax for that? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.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: Rman and database shutdowns
Ron RMAN documentation uses the terms open and closed. RMAN can back up an open database. RMAN can back up a closed database (not usual, except for a non-archivelog database). You can shut the database down when RMAN is not performing a backup. RMAN can even bring the database up or down. Just don't shut the database down while RMAN is backing it up :-) And buy Robert Freeman's book Oracle 9i RMAN Backup and Recovery. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rman and database shutdowns
Ron, I'm not sure I understand your question. You are shutting down your database for an OS level backup? The incremental backup you say is not working, is this an RMAN incremental backup or an OS level backup? As for the shutdown of Oracle, if RMAN is in the middle of a backup at that time, the shutdown of the database will kill the Oracle backup. HTH, RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:18 AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 pools
Bill - i certainly don't profess to be an Oracle PT expert, but i think you are on a potentially fruitful path if there is a performance issue in need of a solution. it can make sense to assign a large infrequently queried table that has a history of flushing hot blocks to a separate cache. similarly, you may want a small frequently queried table assigned to a separate cache where you know its hot blocks are more likely to stay in memory. hence the names of these caches. i would caution you to be sure that the action you take is solving a real problem. ideally you have a test environment and can replicate a production like workload there to verify the changes you make do some good. at minimum, gather meaningful performance statistics before and after your change from production. by meaningful i do not mean BCHR ;) and don't be afraid to re-evaluate if your after statistics are not what you had hoped. all part of the fine art of PT. HTH. - Steve -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Hello, Env: 9.2.0.2 on Solaris 2.9 We are currently considering a proposal regarding the use of the 3 buffer pools represented by db_cache_size, db_keep_cache_size, and db_recycle_cache_size. I am wondering if this is a good idea or a bad idea. The proposal follows. The buffer pool space can be divided into 3 separate pools: db_cache_size, db_keep_cache_size, and db_recycle_cache_size. Despite the names, the blocks are all treated exactly the same with regard to the Least Recently Used (LRU) algorithm. The retention time of a database block in any of these pools is dependent upon the size of the pool, how often the block is referenced, and the probability of the block being displaced by a more popular block. The names Oracle has assigned to these pools reflect more of an intention than anything else; the keep pool is intended to be sized large enough to retain all frequently-referenced data; the recycle pool is intended to be sized small to recycle blocks not desired in memory, and the db_cache_size pool is intended for everything else. Funtionally, Oracle could have named these db_cache1, db_cache2 and db_cache3. Currently, we utilize just 1 cache, the db_cache_size. I am proposing that we utilize all 3 caches in some way; the rationale for this is that it is better to have 3 smaller caches of 800M, each managing 1000 objects, than it is to have 1 large cache of 2.4G managing 3000 objects. The rest of this proposal suggests a method for distributing the various tables and indexes in our system to the 3 caches; it suggests a roughly equal division among the 3 caches based upon subject area and usage stats. 1) Is the information above accurate? 2) Is it better to have 3 smaller caches than 1 large cache? (Assuming the objects are distributed among the caches about equally based upon usage stats) Thanks to those who read this far. More thanks to any responders. Most thanks to responders with helpful suggestions. -- 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: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: parallel index creation again:in which case, can we use p
My experience shows that a parallel degree of less than 4 is nearly always slower than serial. I would recommend tring parallel degree of 4. -Original Message- Sent: Wednesday, February 12, 2003 10:59 AM To: Multiple recipients of list ORACLE-L parallel with single cpu env? Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and the result is persistent across difference test case: So, I think buffer is not the cause of the parallel execution slower. But I really do not get other parameter to tune:( SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.16 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:48.04 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:51.92 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging; Index created. Elapsed: 00:06:26.23 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:44.58 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:49.09 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:46.79 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.14 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:44.51 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 18:40:00 ,you wrote£º=== Dear Chao. Did you try change order of index's creating- first noparallel, second with parallel. I think you will look other results. hi, dba friends: some paper said, pqo should only be used in SMP machines, while others say, We can also use pqo in uniprocessor machines in some case. I am trying to use parallel index creation in the following env: Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory. Oracle 9.2 Table contains 2200 records,1.2GB Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate 3 disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on seperate 3 disks. SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Elapsed: 00:00:00.16 SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Elapsed: 00:06:48.04 This machine is exclusived used my me and It seems that PQO is rather slower than single thread. So is it still possible to use PQO on single processor machines? Please share your experience and idear. Thanks. Wait event like: Top 5 Timed Events ~~ Total Event WaitsTime (s) Ela Time --- PX qref latch 48,371 41540.94 PX Deq: Execute Reply 176 34033.54 PX Deq Credit: send blkd 47,704 24824.47 control file parallel write 112 5 .48 PX Deq Credit: need buffer 1,835 4 .38 - ^LWait Events for DB: ORA9 Instance: ora9 Snaps: 19 -20 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second -- Best regards Michael Ivanov, TD ERA = = = = = = = = = = = = = = = = = = = = -- 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
RE: View HELP Please!
Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Schema specific grants
Here is some code, which creates synonyms and grants permissions:
REM This script create public synonyms for all Tables and Views owned by
SCHEMA_NAME
REM and grants privileges on those objects to 'other' users.
declare lSyn integer;
BEGIN
-- Get Table(View) name
FOR Objects IN
(SELECT object_name FROM dba_objects
WHERE owner = 'SCHEMA_NAME'
AND object_type IN ('TABLE', 'VIEW')) LOOP
-- Find, if it has Synonym
SELECT COUNT(*) INTO lSyn
FROM dba_synonyms
WHERE synonym_name = Objects.object_name;
IF (lSyn = 0) THEN
-- Create Synonym
EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || Objects.object_name ||
' for SCHEMA_NAME.' || Objects.object_name;
END IF;
-- Grant Privileges
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' ||
Objects.object_name || ' TO Guest';
END LOOP;
END;
/
You can modify it, if you don't want to create public synonyms, or want to
grant only specific privileges (i.e. only select).
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, February 12, 2003 10:30 AM
Good Morning All
Im looking at trying to grant privilidges to a guest user (who does
not own the tables)
I know I can do it for individual tables
Eg
GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest;
but I need to grant to an entrie schema
Like
GRANT INSERT ANY SCHEMA_NAME.* TO Guest;
Anyone have the syntax for that?
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: database file sizes....more of smaller size or less of larger size?
the ever popular answer it depends how large are the tables you are storing in these datafiles? Is this a data warehouse where you will be storing massive amounts of data? (1000 2GB datafiles to store one table's worth of data is somewhat obscene) Is it a large database? Heavily used? OLTP? I'm not making fun, it's just that a blanket statement what are the pros and cons just doesn't make sense --- [EMAIL PROTECTED] wrote: Hi DBAs, Oracle 8.1.7 WinNT I would like pros/cons on datafile sizes. It is better to have more of less size or less with larger sizes and why? 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). __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.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: Ewrin question ??
Janet - 1. Piece of Cake. When you choose the Reverse Engineer tool, the 'Set Options' window has an Options panel on the right-hand side. You should see a 'Tables/Views owned by' choice. The default is 'All'. Change the radio button to 'Owners (comma separated)' and type in the schema name. 2. That's a little more difficult. My first instinct would be to check your path and make sure that the Oracle binaries are in it. Are you able to successfully connect to the database via SQL*Plus? Can you TNSPING it? Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | -- -Original Message- Linsy Sent: Wednesday, February 12, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Hi all, I have couple of questions about ERWIN. -- I need to reverse engineer for just one schema. I specify the login name/pwd for that schema, but ERWIN generates charts for objects do NOT belong to that schema too! How do I get charts for a specific schema only? -- When connect to the db, Erwin asks about target server, ef: ODBC, Oracle, Sybase, etc. I can connect to the db through ODBC, but cann't connect using Oracle directly. Why ? Really appreciate any thoughts. Thank you in advance. Janet __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
RE: Oracle License for Training
If MySql continues as planned, I think Oracle will find it a force to be reckoned with, much as MS has discovered to be true about Linux. Of course by that time, according to the latest IT business intelligence as seen in Computer World, most of our jobs will have been outsourced by then, and it wont' matter much. Jared Weiss, Rick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/11/2003 03:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle License for Training A question for the DBA Gods on this list: Is it worth the time/effort to download MySQL and learn it? Is there going to be a viable (meaning $$) market for the product in the future? Or should I leave all the egg$ in the Oracle basket? Musing for fun and profit. Rick Weiss -Original Message- Sent: Tuesday, February 11, 2003 3:34 PM To: Multiple recipients of list ORACLE-L MS SQL costing less than Oracle is only partly true. If you load up MS with the extras that constitute a std feature set on Oracle, Oracle is very competitive. Been lots of comparisons on that. Now PostgreSQL and MySQL, those *are* less expensize than MS SQL and Oracle. :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Weiss, Rick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rman and database shutdowns UPDATE
Ron, While Vms *can* backup the open datafile, it is worthless when you need to recover it. I know, I've been there. Worked on Open Vms for about 8 years, including Oracle for 3 years. The file would need recovery immediately upon an attempt to open it. And I would not bet my job that I could recover it properly. If you wish, you could put the tablespace in Hot Backup mode and let Vms back it up - that would work fine. Just be aware of the issues. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please!
Title: RE: View HELP Please! I remember some parameter about Query re-writes... it may be only for Materialized Views... but could be the culprit here. Also, do you have Oracle Label Security set on this table, or Fine-Grain Auditing? -Original Message- From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: View HELP Please! Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
INSERT ... RETURNING ROWIDTOCHAR(ROWID) INTO problem on 9.2.0.2.1
Hello All,
We have Oracle 9.2.0.2.1 running on our server. When we run the following
command from Sql Plus, we get the end-of-file on communication channel.
This occurs consistently on all four of our 9.2.0.2.1 instances on this
server. It has also occured on a new installation of 9.2.0.2.1 on a
different server. When we run the insert statement separately, it succeeds.
We have not encountered this problem when running the command from developer
workstations running 9i release 2 without the patch. It has also worked on
the other Oracle versions.
We have NT 4.0 SP6.
Does anybody have any insight into what the problem might be? Here is the
command.
SQL connect canadian_575/sql@paristest
Connected.
SQL begin
2 declare
3 cRow varchar2(18);
4 begin
5 INSERT INTO MEMBER_PLAN_FUND
6 (CLNT,PLAN,MKEY,FUND,TRADATE,ERKEY) VALUES
7 ('0001','1', '000-3','ER',SYSDATE,Misc.GetDefaultErKey('0001','000-3'))
8 RETURNING ROWIDTOCHAR(ROWID) INTO cRow;
9 end;
10 end;
11 /
begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL connect canadian_575/sql@paristest
Connected.
Thanks for any help ...
Sam Bootsma, OCP
Technical Support Analyst
CPAS Systems Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sam Bootsma
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: View HELP Please!
Predicate - values in the where clause. -Original Message- To: '[EMAIL PROTECTED]' Cc: Freeman Robert - IL Sent: 2/12/2003 1:24 PM Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rman and database shutdowns UPDATE
Ron - Yes they can back it up and recover it from their perspective. You've brightened my day. Let us know if you want some more explanations to discuss this with them again. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: parallel index creation again:in which case, can we use p
If you only have one CPU, then is parallel either not supported, or simply a waste of time? I actually thought it was not supported. If you only have one CPU, what do you expect to gain? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 12:54 PM To: Multiple recipients of list ORACLE-L p My experience shows that a parallel degree of less than 4 is nearly always slower than serial. I would recommend tring parallel degree of 4. -Original Message- Sent: Wednesday, February 12, 2003 10:59 AM To: Multiple recipients of list ORACLE-L parallel with single cpu env? Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and the result is persistent across difference test case: So, I think buffer is not the cause of the parallel execution slower. But I really do not get other parameter to tune:( SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.16 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:48.04 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:51.92 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging; Index created. Elapsed: 00:06:26.23 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:44.58 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:49.09 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:46.79 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.14 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:44.51 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 18:40:00 ,you wrote£º=== Dear Chao. Did you try change order of index's creating- first noparallel, second with parallel. I think you will look other results. hi, dba friends: some paper said, pqo should only be used in SMP machines, while others say, We can also use pqo in uniprocessor machines in some case. I am trying to use parallel index creation in the following env: Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory. Oracle 9.2 Table contains 2200 records,1.2GB Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate 3 disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on seperate 3 disks. SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Elapsed: 00:00:00.16 SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Elapsed: 00:06:48.04 This machine is exclusived used my me and It seems that PQO is rather slower than single thread. So is it still possible to use PQO on single processor machines? Please share your experience and idear. Thanks. Wait event like: Top 5 Timed Events ~~ Total Event WaitsTime (s) Ela Time --- PX qref latch 48,371 41540.94 PX Deq: Execute Reply 176 34033.54 PX Deq Credit: send blkd 47,704 24824.47 control file parallel write 112 5 .48 PX Deq Credit: need buffer 1,835 4 .38 - ^LWait Events for DB: ORA9 Instance: ora9 Snaps: 19 -20 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second -- Best regards
RE: Rman and database shutdowns UPDATE
Ron - the text below is from the fine VMS manual. I suspect your SAs are referring to the backup/ignore=interlock command. I certainly would not rely on it as part of my Oracle Disaster Recovery plan. The cold backup decision sounds to me like a wise one. BACKUP Invokes the Backup utility (BACKUP) to perform the following BACKUP operations: o Make copies of disk files. o Save disk files to a BACKUP save set. o Restore files from a BACKUP save set to disk. o Compare disk files with other disk files or files in a BACKUP save set. o List information about the files in a BACKUP save set. o Create and list BACKUP journal files. The two ways to back up your system disk are: o If you have access to the CD-ROM for the current version of OpenVMS Alpha or VAX, you can use the menu system supplied on the CD-ROM to back up your system disk. o If you do not have access to the CD-ROM for the current version of OpenVMS VAX, you must use standalone BACKUP to back up your system disk (VAX only). Standalone BACKUP is a form of the Backup utility (VAX only) that you boot into main memory (instead of executing under the control of the operating system). For more information about BACKUP and standalone BACKUP tasks refer to the OpenVMS System Manager's Manual. Format: BACKUP input-specifier output-specifier Additional information available: Parameters Qualifiers /ALIAS /ASSIST/BACKUP/BEFORE/BLOCK_SIZE /BRIEF /BUFFER_COUNT /BY_OWNER /COMMENT /COMPARE /CONFIRM /CONVERT /CRC /CREATED /DELETE/DENSITY Example /EXACT_ORDER /EXCLUDE /EXPIRED /FAST /FULL /GROUP_SIZE /IGNORE/IMAGE /INCREMENTAL /INITIALIZE /INTERCHANGE /JOURNAL /LABEL /LIST /LOG /MEDIA_FORMAT /MODIFIED /NEW_VERSION /NOINCREMENTAL/OVERLAY /OWNER_UIC /PHYSICAL /PROTECTION /RECORD/RELEASE_TAPE /REPLACE /REWIND/SAVE_SET /SELECT /SINCE /TAPE_EXPIRATION /TRUNCATE /UNSHELVE /VERIFY/VOLUME Examples BACKUP Subtopic? /ignore BACKUP /IGNORE /IGNORE=option Command Qualifier Specifies that a BACKUP save or copy operation will override restrictions placed on files or will not perform tape label processing checks. The /IGNORE=option qualifier has the following options: ACCESSIBILITYProcesses files on a tape that is protected by a volume accessibility character, or on a tape created by HSC Backup. The option applies only to tapes. It affects the first tape mounted and all subsequent tapes in the save set. INTERLOCKProcesses files that otherwise could not be processed due to file access conflicts. Use this option to save or copy files currently open for writing. You must have the user privilege SYSPRV, a system UIC, or ownership of the volume to use this option. LABEL_ Saves or copies the contents of files to the PROCESSING specified magnetic tape volume regardless of the information contained in the volume header record. BACKUP does not verify the volume label or expiration date before writing information to the tape volume. Note that you cannot use this option with the /EXACT_ORDER qualifier. NOBACKUP Saves or copies both the file header record and the contents of files marked with the NOBACKUP flag by the /NOBACKUP qualifier of the DCL command SET FILE. If you do not specify this option, BACKUP saves only the file header record of files marked with the NOBACKUP flag. -Original Message- Sent: Wednesday, February 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not
Re: View HELP Please!
Freeman Robert - IL wrote: Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert Robert, The way I understand it is that your view is pretty complex. My experience with views is that past some degree of complexity (a hard notion to quantify) Oracle gives up any attempt to rewriting or recombining them and uses them 'as is'. I guess that the boundary increases in complexity with each release of Oracle, but here it is and you usually easily see from the plan whether the stored view was used or if it was broken into its constituent parts. I guess that the execution path it takes doesn't start with the table which contains the 'claim_carrier_key' column when you create the view. I would probably try to do what is considered bad practice by Oracle, i.e. a hint in the view. Some 'ordered' starting with the table which contains claim_carrier_key *might* be appropriate. The snag is that when you apply _other_ conditions to your view, queries may then be far more slower than your 6 mn ... -- 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: View HELP Please!
Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Concat SQL_TEXT from Statspack tables.
H. It seems that I don't get replies to a lot of my posts. Do I ask
the hard questions or just stupid ones? On sencond thought, don't answer
that... ;)
In any case, in reinventing the wheel, I decided to just create a function
that uses a REF CURSOR to generically concat the column for me:
CREATE OR REPLACE FUNCTION newwheel (p_tabname IN VARCHAR2, p_colname IN
VARCHAR2, p_whereclause IN VARCHAR2) RETURN VARCHAR2 AS
TYPE rc_type IS REF CURSOR;
rc rc_type;
v_col VARCHAR2(2000);
v_resultVARCHAR2(4000);
BEGIN
OPEN rc FOR
'SELECT '||p_colname||' FROM '||p_tabname||'
'||p_whereclause;
LOOP
FETCH rc INTO v_col;
EXIT WHEN rc%NOTFOUND;
v_result := v_result||v_col;
END LOOP;
CLOSE rc;
RETURN(v_result);
END newwheel;
Then I can:
SELECT newwheel('PERFSTAT.STATS$SQLTEXT','sql_text','WHERE HASH_VALUE =
1232131312')
FROM dual;
Hope this can help someone else!
Rich
Well I'm sorry, but I'm going to have to shoot you.
Right-oh, sir. THUMP
What a senseless waste of human life.
-- Monty Python
-Original Message-
Sent: Monday, February 10, 2003 10:29 AM
To: Multiple recipients of list ORACLE-L
Hey all,
In 8.1.7.4, does anyone have a SQL that will take the output from the
following:
SELECT sql_text
FROM PERFSTAT.STATS$SQLTEXT SS
WHERE SS.HASH_VALUE = :myhash
ORDER BY PIECE
...and append/concat all the rows into a single column.
I *know* someone's done this before and I don't want to re-invent the wheel
unless I have to.
TIA!
Rich
Rich JesseSystem/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: View HELP Please!
The explain plans indicate that the predicate is being filtered out after the view itself executes. I don't see how a no_merge hint would help, since I'm not getting view merging anyway. Thanks! Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Freeman Robert - IL, Hi, can you show us the different execution path for the view and the sql? I think that is the key to solve the performance problem?Maybe hint like no_merge help? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 07:18:00 ,you wrote?o=== I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: database file sizes....more of smaller size or less of larger
Rick - Here is the way I see it: Larger file sizes - Fewer files to manage. - May exceed O.S. limits (be careful on WinNT) Smaller file sizes - More flexible, can just back up or move a single file. - Fewer tables per file, may be easier on tablespace recovery. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 10:09 AM To: Multiple recipients of list ORACLE-L size? Hi DBAs, Oracle 8.1.7 WinNT I would like pros/cons on datafile sizes. It is better to have more of less size or less with larger sizes and why? 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View HELP Please!
Robert, Have you considered the current values of _complex_view_merging, _push_join_predicate, _push_join_union_view. Some of them may be obsolete in 9iR2, but there are some published and recommended setting for these for specific Apps 11i environments. You could also look at/play with the value of 'optimizer_max_permutations'. You might have this piece of code up your sleeve, but if you don't here ya go! column ksppinm format a45 heading Name column ksppstvl format a30 heading Value column ksppdesc format a79 heading Description select x.ksppinm, y.ksppstvl, x.ksppdesc from x$ksppi x, x$ksppcv y where (x.indx= y.indx) and (x.ksppinm like '\_undoc_parm' escape '\') order by x.ksppinm / John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: View HELP Please! Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Rman and database shutdowns UPDATE
I'd tell them, great, then lets test it. Trust no one. Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:23 AM Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please!
Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Shopping - Send Flowers for Valentine's Day http://shopping.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).
interMedia Textkey (DRG-10826)
All...
One more submission to see if there are any takers, then I'll stop
nagging and keep looking...
I'm running Oracle 8.1.6 on Solaris 8.
Will someone please help me understand the Textkey parameter in
the ctx_doc.themes function? I am receiving a DRG-10826 error on:
exec
ctx_doc.themes('ctx_webdocs', 1, 'MYTHEMES', 1, full_themes = TRUE);
The index is on a BLOB column that, for the moment, contains only
textual data (not that it really matters, anyway). I don't understand
the relationship of the textkey to the table and/or index. Any help is
greatly appreciated.
TIA!!
Gary Chambers
//--
// Lucent Technologies CIO/Servers/Unix
// Senior Unix System Administrator
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Chambers
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: database file sizes....more of smaller size or less of
Rick, The answer is (YES, 42, IT DEPENTS). Not ot be smart but there in NO correct answer. The answer depents on may factors. 1. If the table is large, Why have it in may little extents. That could hurt performance if the data becomes chained. 2. If the table is small, why have it in a large extent wasting space. 3. If the table is static, I try the fill the tablespace to the limit and make the tablespace read-only. Back it up once and place it on the shelf. 4. If the table can be partitioned, set the tablespace and datafile accordingly. 5. etc etc etc. There is no correct answer. Some of the dependancies are the disk arrangements and size that are available, the backup capabilities of the server, the capacity of the backup media, The mean time to repair in case of an error (human or machine). The answer is the one where you used your best judgement and were comfortable with. I have datafiles from 4 M to 4 GIG. Ron [EMAIL PROTECTED] 02/12/03 11:09AM Hi DBAs, Oracle 8.1.7 WinNT I would like pros/cons on datafile sizes. It is better to have more of less size or less with larger sizes and why? 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please!
There are 2 or 3 parameters, but none help. Nope, no FGAC... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 12:59 PM I remember some parameter about Query re-writes... it may be only for Materialized Views... but could be the culprit here. Also, do you have Oracle Label Security set on this table, or Fine-Grain Auditing? -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, February 12, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Ron, man, you got me on the version thing. I yell at people who don't say what version they are on, and here I am forgetting to do the same. I'm on 9iR2. You are correct that the view would not use the index if I just did a select * from it with no additional predicates. However, if I do a select * from a view with a predicate in that select statement (like user_id=100) then the additional predicate should be merged into the view and a new execution plan (using index lookup) should be generated. Thanks! RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 10:04 AM Robert, I will make the assumption that you are on a newer version of Oracle. If I remember correctly, a view does not use an index and will use a full table scan. That could be the cause for the time difference. I know that this doesn't answer your question but it might trigger other thoughts that solve the problem. Ron [EMAIL PROTECTED] 02/12/03 10:18AM I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rman and database shutdowns UPDATE
Ron, Back when we had Oracle on VMS (Oracle 7.1 - pre RMAN), we used this backup command for each datafile: backup/log/ignore=(interlock,nobackup)/new This will backup open files. But if your tablespace isn't in hot backup mode, it doesn't help you for database recovery. You might get lucky if there is little-to-no activity at the time of your backup, but this is a risk few DBAs would take. Jay [EMAIL PROTECTED] 02/12/03 11:23AM Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rman and database shutdowns UPDATE
Ron - Thought of a simple way to explain the situation to your VMS administrators. I've simplified a few things, but it should suffice. If I've missed anything critical, someone will correct me ;-) Each Oracle datafile has a transaction number recorded. The Oracle control file has a transaction number for each datafile. Before Oracle will open the database, the transaction number on the control file must match the transaction on each data file. When you shut Oracle down normal, all these numbers match, so a cold backup after a normal shutdown is valid. This is also why a cold backup after a shutdown abort or database crash can't be guaranteed. Now, if you back the database files up while Oracle is open, the backup will take some time, and if transactions occur in Oracle, the transaction numbers will change during the backup, so the control file may not match some file transaction numbers. Someone mentioned that you should test it. Be careful. If the database is quiet, it is possible to get a valid backup while the database is open. You wouldn't want to bet on this as a backup method, but in a test situation it could lead you to the wrong conclusion. Okay I'll shut up and let you get back to your nap ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: database file sizes....more of smaller size or less of larger
Title: RE: database file sizesmore of smaller size or less of larger size? How about consistent file sizes? For raw the VLDB whitepaper recommends something like 128MB, 1GB, and 8GB but isn't it also a good practice for cooked? It depends... -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Subject: Re: database file sizesmore of smaller size or less of larger size? Importance: High the ever popular answer it depends how large are the tables you are storing in these datafiles? Is this a data warehouse where you will be storing massive amounts of data? (1000 2GB datafiles to store one table's worth of data is somewhat obscene) Is it a large database? Heavily used? OLTP? I'm not making fun, it's just that a blanket statement what are the pros and cons just doesn't make sense --- [EMAIL PROTECTED] wrote: Hi DBAs, Oracle 8.1.7 WinNT I would like pros/cons on datafile sizes. It is better to have more of less size or less with larger sizes and why? Thanks Rick
RE: View HELP Please!
Robert, Is the view part of an application that you can make use of an OUTLINE? I think that is the proper terminology. Then you could force the desired optimization. Ron [EMAIL PROTECTED] 02/12/03 01:39PM Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please!
Tom - Human grammar terms adapted to computer syntax :-) http://www.student.math.uwaterloo.ca/~cs448/db2_doc/html/db2s0/frame3.htm#ch 2pred Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Robert, please excuse my ignorance, but what do you mean by predicate? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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
RE: View HELP Please!
Hi Rachel, I've actually tried it both ways, with no joy. best luck I've had so far is turning max_purm's to about 100 which makes it run in about 2 minutes. Still to slow though. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 1:54 PM Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Shopping - Send Flowers for Valentine's Day http://shopping.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: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Re: parallel index creation again:in which case, can we use p
Better utilization of the CPU. While one process is I/O-ing (or waiting on an I/O) the other process can use the CPU. Various tests I have performed on various unixes (unicies?) have shown the parallelism usually scales to between 3 6 times the number of CPUs before performance degrades. Quite often adding another parallel thread to a process helps even when the CPU is shown as 100% busy. -Original Message- Sent: Wednesday, February 12, 2003 2:24 PM To: Multiple recipients of list ORACLE-L If you only have one CPU, then is parallel either not supported, or simply a waste of time? I actually thought it was not supported. If you only have one CPU, what do you expect to gain? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 12, 2003 12:54 PM To: Multiple recipients of list ORACLE-L p My experience shows that a parallel degree of less than 4 is nearly always slower than serial. I would recommend tring parallel degree of 4. -Original Message- Sent: Wednesday, February 12, 2003 10:59 AM To: Multiple recipients of list ORACLE-L parallel with single cpu env? Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and the result is persistent across difference test case: So, I think buffer is not the cause of the parallel execution slower. But I really do not get other parameter to tune:( SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.16 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:48.04 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:51.92 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging; Index created. Elapsed: 00:06:26.23 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.06 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:44.58 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.13 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:49.09 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:14:46.79 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.14 SQL SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Index created. Elapsed: 00:06:44.51 SQL drop index idx_serial; Index dropped. Elapsed: 00:00:00.07 Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-12 18:40:00 ,you wrote£º=== Dear Chao. Did you try change order of index's creating- first noparallel, second with parallel. I think you will look other results. hi, dba friends: some paper said, pqo should only be used in SMP machines, while others say, We can also use pqo in uniprocessor machines in some case. I am trying to use parallel index creation in the following env: Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory. Oracle 9.2 Table contains 2200 records,1.2GB Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate 3 disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on seperate 3 disks. SQL set term on timing on echo on feedback on SQL alter session set sort_area_size = 1; Session altered. Elapsed: 00:00:00.01 SQL create index idx_serial on viewcount( SID_LIST) nologging parallel (degree 2) tablespace pqind; Index created. Elapsed: 00:18:01.36 SQL drop index idx_serial; Elapsed: 00:00:00.16 SQL create index idx_serial on viewcount( SID_LIST) nologging tablespace pqind; Elapsed: 00:06:48.04 This machine is exclusived used my me and It seems that PQO is rather slower than single thread. So is it still possible to use PQO on single processor machines? Please share your experience and idear. Thanks. Wait event like: Top 5 Timed Events ~~ Total Event WaitsTime (s) Ela Time --- PX qref latch 48,371
RE: Rman and database shutdowns UPDATE
Ron, the analogy I always use is who has to recover the database when it crashes? When they say, well, YOU, then I say Fine. We are going to back it up the way I see fit. End of story. Have fun testing~!! Good Luck. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax:954-935-3639 Cell:954-683-4459 -Original Message- Sent: Wednesday, February 12, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Ron - Yes they can back it up and recover it from their perspective. You've brightened my day. Let us know if you want some more explanations to discuss this with them again. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Just got out of a meeting with the systems group. The Incremental backup was not working because the command was written wrong. There has been a decision to do cold backups each night. The system people swear that the OpenVms can successfully backup an open file (datafile) and have it recovered properly. They can backup it up with an Ignore flag that backups up open files? I will go with a cold backup methodology. Ron [EMAIL PROTECTED] 02/12/03 10:18AM List, OS OpenVms 7.3-1 Oracle 8.1.7 rel 3 Archivelog mode. We are trying to work through backup issues here and I have a question. The system nightly backup procedures backup up the disks used by Oracle (software and data). A full backup works but an incremental does not backup any files. Inorder to get the changed files to be backed up as an incremental the sysadmin shutdown the database to flag the files as changed. Questions: How will the shutdown effect the RMAN backup if it does not know that it was shutdown for a cold backup? Are there any possible problems doing a restore from the cold backup and then using the RMAN backup/restore? I am still trying to figure out the OpenVms OS. Perserverience required...! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: View HELP Please - Problem solved
I think I fixed the problem... within my view, there was actually aggrigation of the predicate going on. I'm thinking that this is why Oracle could not push the predicate into the view. Once I removed the aggregation (I moved it to a higher level view) it worked great. Thanks to everyone for your thoughts. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 1:54 PM Robert, have you tried recoding the view with the hint in the view text? Or am I misunderstanding your original post which looks like you've put the hints on the select count(*) statement? Rachel --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Done that Dennis. The difference in the two is how the plan is being crafted by the optimizer. Bottom line is that the excellent plan I'm getting from the query by itself is not being replicated when it's moved into a view. It appears that this is because the predicate is not being pushed into the view properly. Since it's not, the view ends up just being a two table join with two full table scans who's row set is returned and then filtered. What I want is the predicate to be pushed into the view, which will allow for an index scan based on that predicate. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 9:53 AM Robert - I always try EXPLAIN PLAN as a starting point. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 9:19 AM To: Multiple recipients of list ORACLE-L I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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! Shopping - Send Flowers for Valentine's Day http://shopping.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:
RE: RMAN or SQLBacktrack?
Hi Robert, Can you suggest a good RMAN book to start out. We're also in the process of looking at RMAN to replace in-house scripts. TIA Tom P. --- Robert Freeman [EMAIL PROTECTED] wrote: We converted from SQLBacktrack to RMAN. RMAN is a very robust product, and offers more features that SQLBacktrack. In fact, SQLBacktrack I've heard will simply become a front end to RMAN in the future. Why pay for something with less functionality? As someone already said, if you are running 7, RMAN won't work. Cheers! Robert -Original Message- L. Sent: Tuesday, January 28, 2003 4:17 PM To: Multiple recipients of list ORACLE-L We have been using SQLBacktrack to backup our databases on Unix and NT for several years. We have been VERY please with the product. We have a mixture of Oracle 8.1.7 and 7.3.4 databases. We are being pushed to use RMAN because it is free. Does anyone have any experience with both and be willing to share their experiences? Thanks! R. Smith If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = ,--o -\\__ Do you ride? (*)/-'(*) __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: TP INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Feedback
I tried setting the NLS_DATE_FORMAT at the command line to try and get some decent date formats. Worked fine from both linux and Win2k. Jared Kurth, Michael J. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/11/2003 05:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RMAN Feedback Try setting: export NLS_DATE_FORMAT='Mon DD HH24:MI:SS' -Original Message- Sent: Tuesday, February 11, 2003 6:19 AM To: Multiple recipients of list ORACLE-L RMAN reports its progress provinding such information as channel c1: starting piece 1 at 11-FEB-03 channel c1: finished piece 1 at 11-FEB-03 piece handle=df_485669402_63_1 comment=API Version 2.0,MMS Version 2.2.1.0 channel c1: starting piece 2 at 11-FEB-03 channel c1: finished piece 2 at 11-FEB-03 piece handle=df_485669402_63_2 comment=API Version 2.0,MMS Version 2.2.1.0 Is there anyway to have it rpeort the time as well as the date? 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). Privileged/Confidential information may be contained in this message. The information contained in this message is intended only for the use of the recipient(s) named above and their co-workers who are working on the same matter. The recipient of this information is prohibited from disclosing the information to any other party unless this disclosure has been authorized in advance. If you are not intended recipient of this message or any agent responsible for delivery of the message to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of this message is strictly prohibited. You should immediately destroy this message and kindly notify the sender by reply E-Mail. Please advise immediately if you or your employer does not consent to Internet E-Mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the firm shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kurth, Michael J. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN or SQLBacktrack?
Tom Just in case Robert is too modest, I'll suggest the best: Oracle9i RMAN Backup and Recovery by Robert Freeman and Matthew Hart The best, the most extensive. Covers the most popular MMLs. There are only 2 others that I am aware of: Oracle RMAN Pocket Reference by Darl Kuhn Scott Schulze Keep this small volume at your elbow, only $13 U.S. Oracle 101 Backup Recovery by Kenny Smith Stephan Haisley About half this is RMAN. VERY basic for those of us that need to start really slowly (like me). Has step by step by step tutorials. -Original Message- Sent: Wednesday, February 12, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Hi Robert, Can you suggest a good RMAN book to start out. We're also in the process of looking at RMAN to replace in-house scripts. TIA Tom P. --- Robert Freeman [EMAIL PROTECTED] wrote: We converted from SQLBacktrack to RMAN. RMAN is a very robust product, and offers more features that SQLBacktrack. In fact, SQLBacktrack I've heard will simply become a front end to RMAN in the future. Why pay for something with less functionality? As someone already said, if you are running 7, RMAN won't work. Cheers! Robert -Original Message- L. Sent: Tuesday, January 28, 2003 4:17 PM To: Multiple recipients of list ORACLE-L We have been using SQLBacktrack to backup our databases on Unix and NT for several years. We have been VERY please with the product. We have a mixture of Oracle 8.1.7 and 7.3.4 databases. We are being pushed to use RMAN because it is free. Does anyone have any experience with both and be willing to share their experiences? Thanks! R. Smith If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Robert Freeman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = ,--o -\\__ Do you ride? (*)/-'(*) __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: TP INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: interMedia Textkey (DRG-10826)
All... slapping hand to forehead STUPID, STUPID, STUPID!!! Will someone please help me understand the Textkey parameter in the ctx_doc.themes function? I am receiving a DRG-10826 error on: I withdraw my question -- I was looking at it MANY layers too deeply, and absolutely overlooked the braindead obvious. Sorry for the noise. Gary Chambers //-- // Lucent Technologies CIO/Servers/Unix // Senior Unix System Administrator // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Chambers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Teradata baned from IOUG???
DBA's Check the article's comment on Oracle trying to ban Teradata from IOUG Teradata Steals Oracle's Data Mart Users ... Teradata pushes consolidation and woos away Oracle customers. But Oracle strikes back. Sort of. Will bean counters surf the Web with Excel? Will Steve Ballmer and Larry Ellison become immortal? http://computerworld.com/newsletter/0%2C4902%2C78375%2C0.html?nlid=DM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Feedback
Nice to know Jared, I'll add that to the Oracle9i RMAN Errata and give you credit! Thanks! Robert -Original Message- To: Multiple recipients of list ORACLE-L Sent: 2/12/2003 3:39 PM I tried setting the NLS_DATE_FORMAT at the command line to try and get some decent date formats. Worked fine from both linux and Win2k. Jared Kurth, Michael J. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/11/2003 05:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RMAN Feedback Try setting: export NLS_DATE_FORMAT='Mon DD HH24:MI:SS' -Original Message- Sent: Tuesday, February 11, 2003 6:19 AM To: Multiple recipients of list ORACLE-L RMAN reports its progress provinding such information as channel c1: starting piece 1 at 11-FEB-03 channel c1: finished piece 1 at 11-FEB-03 piece handle=df_485669402_63_1 comment=API Version 2.0,MMS Version 2.2.1.0 channel c1: starting piece 2 at 11-FEB-03 channel c1: finished piece 2 at 11-FEB-03 piece handle=df_485669402_63_2 comment=API Version 2.0,MMS Version 2.2.1.0 Is there anyway to have it rpeort the time as well as the date? 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). Privileged/Confidential information may be contained in this message. The information contained in this message is intended only for the use of the recipient(s) named above and their co-workers who are working on the same matter. The recipient of this information is prohibited from disclosing the information to any other party unless this disclosure has been authorized in advance. If you are not intended recipient of this message or any agent responsible for delivery of the message to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of this message is strictly prohibited. You should immediately destroy this message and kindly notify the sender by reply E-Mail. Please advise immediately if you or your employer does not consent to Internet E-Mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the firm shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kurth, Michael J. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Teradata baned from IOUG???
Title: RE: Teradata baned from IOUG??? Guys, With everything going on - should I take up a second career? -Original Message- From: James Howerton [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Teradata baned from IOUG??? DBA's Check the article's comment on Oracle trying to ban Teradata from IOUG Teradata Steals Oracle's Data Mart Users ... Teradata pushes consolidation and woos away Oracle customers. But Oracle strikes back. Sort of. Will bean counters surf the Web with Excel? Will Steve Ballmer and Larry Ellison become immortal? http://computerworld.com/newsletter/0%2C4902%2C78375%2C0.html?nlid=DM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RBS datafile recovery
Hi, I am doing some recovery testing and am testing the corruption of the rbs datafile. However, I am running into some problems. What I've done so far: 1) Shutdown db 2) Copied the backup datafile to proper location 3) Commented the rollback_segments parameter in the init.ora 4) startup mount 5) Took the rbs datafile offline. 6) Tried to open the database. When I open the database, I thought it is meant to open cleanly because I have taken the datafile offline. However, the db refuses to open and says that the rbs datafile needs media recovery. When I query v$datafile it shows the status as Recover. Is it meant to show a status of Offline??? Now I can't get the db to open. I have done this kind of recovery before and I remember that the steps involved are: 1) to 5) as above 6) Open database 7) Create temporary rbs's 8) Recover RBS tablespace Could some one please help me with this problem? ... or am I doing something totally wrong ??? Oracle: 8.1.7 on W2K ... Test db and machine only. Cheers, Sujatha --- Sujatha Madan Database Administrator Custom Management Centre Optus Business Operations 'yes' OPTUS PH # +61 2 9775 9868 Mobile # +61 402 354 347 FAX # +61 2 9775 9510 Email [EMAIL PROTECTED] WEB http://www.optusbusiness.com.au/ --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sujatha Madan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Teradata baned from IOUG???
I can't imagine why they would want to replace Oracle with Teradata. It's expensive. It runs only on NCR or Windoze. The architecture is nothing special. I imagine the PHB's had their fingers in those moves. Been there, got the T-shirt, now it's a dust rag. Jared James Howerton [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/12/2003 01:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Teradata baned from IOUG??? DBA's Check the article's comment on Oracle trying to ban Teradata from IOUG Teradata Steals Oracle's Data Mart Users ... Teradata pushes consolidation and woos away Oracle customers. But Oracle strikes back. Sort of. Will bean counters surf the Web with Excel? Will Steve Ballmer and Larry Ellison become immortal? http://computerworld.com/newsletter/0%2C4902%2C78375%2C0.html?nlid=DM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Teradata baned from IOUG???
Title: RE: Teradata baned from IOUG??? should I take up a second career? You're only on your first career?Isn't DBA usually a second or third career at the least? Environments change and the ability to adapt/learn is an evolutionary survival factor. Yours truly, Old Dog learning new tricks... Meanwhile the fat dogs of the software industry are up to their old tricks. Sigh... Will ... Larry Ellison become immortal? Only by the power of God, Who doesn't think He is Larry Ellison, btw. ;-) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 12, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Teradata baned from IOUG??? Guys, With everything going on - should I take up a second career? -Original Message- From: James Howerton [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Teradata baned from IOUG??? DBA's Check the article's comment on Oracle trying to ban Teradata from IOUG Teradata Steals Oracle's Data Mart Users ... Teradata pushes consolidation and woos away Oracle customers. But Oracle strikes back. Sort of. Will bean counters surf the Web with Excel? Will Steve Ballmer and Larry Ellison become immortal? http://computerworld.com/newsletter/0%2C4902%2C78375%2C0.html?nlid=DM
Re: View HELP Please!
Robert, Why not run a trace on both the view and standalone SQL, and share the results? At least the explain plans. Jared Freeman Robert - IL [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/12/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:View HELP Please! I'm fighting a view Someone hand me a big dagger with which to kill it. I have a view that takes 6 minutes to run when I query it like this: select count(*) from TST_XVW a where claim_carrier_key=41721; Yet, if I take the SQL from the view, add the claim_carrier_key predicate to it, I get a run time of 6 seconds. I've tried several different hints (push_pred, use_nl, etc) and I'm just not seeming to be able to get the optimizer to give me a good plan. Any help on this would be appreciated. Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
