[Replication] Altering Master Object at materialized view replication environment
Hello How can i replicate DDL on table (modify column for example) in materialized view replication environment ? DBMS_REPCAT.ALTER_MASTER_REPOBJECT alter the object, put changes are not propagated to MV site ... I try DBMS_REFRESH.REFRESH and DBMS_REPCAT.REFRESH_MVIEW_REPGROUP at MV site, but I steel don't see the changes. Is it possible to propagate DDL to MV site ? Thnx. /sds -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Sorokin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Minimum required init.ora parameters
No, I tried to startup the DB without compatible parameter. It throws message as the same parameter is required. Nirmal. -Original Message- Sent: Monday, January 13, 2003 5:55 PM To: Multiple recipients of list ORACLE-L compatibility or compatible is not a mandatory init.ora parameter. I would think that only the first three are required. Hemant At 06:08 AM 13-01-03 -0800, you wrote: Nirmal - I believe there are four: db_name control_file db_block_size compatibility This is from John Hibbard, a great Oracle Education instructor. But why not try for yourself? Save off your init.ora, then create a new init.ora with just the above parameters. If Oracle comes up, then remove parameters. If there is another parameter, Oracle will tell you. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 4:59 AM To: Multiple recipients of list ORACLE-L List, I'm interested to know the minimum required parameters to startup the database. Pls anybody list out that? Nirmal., -- 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Info about running procs
Title: Info about running procs Hi list, I'm looking for a Oracle system view or table where I can see all actual running procedures. Where can I find this info. TIA Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de
Re: Different Backups - A Comparartive analysis
Hello Jared AFAIK SQLBackTrack can do a TABLE restore from the backup and RMAN can not do it. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:51 PM On Monday 13 January 2003 06:03, Tim Gorman wrote: Another question: should SQL BackTrack be included for consideration? What does SQL BackTrack to that RMAN doesn't do? Jared -- 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Foreign key indexes on individual columns or multiple columns
Hi List, I have a table by name Benchmark_hdr with the following columns: BENCHMARK_HDR_IDNUMBERNOT NULL CALENDAR_TYPE CHAR (1), UOM VARCHAR2 (10), CALENDAR_PORT CHAR (1), CUSTOMER_MOT_ID NUMBERNOT NULL RESP_PARTY_NAME VARCHAR2 (10), FROM_EVENTNUMBER, TO_EVENT NUMBER, LAST_UPDATED DATE I have created foreign key constraints using the below commands: ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMERESP_FK FOREIGN KEY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) REFERENCES XM.CUSTOMER_RESPONSIBLE_PARTY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) ; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (FROM_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHMARK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (TO_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; The foreign keys need to be indexed so that the child table is not locked when a record in the parent table is being updated or deleted. If the foreign keys are indexed the corresponding child record or records are only locked when the parent table record is being updated or deleted. In this context I would like to seek the advice of the list as to whether I should create indexes on individual foreign keys or should I create one or more composite index(es) on foreign keys? If I need to create a composite index on which columns should I base my index on? Could anybody advise me on this with the reasons for following that particular approach? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: bitmapped indexes
Hi ! I don't remember where I got the paper, but I am attaching it. Cheers. -Original Message- Sent: ? 13 ? 2003 19:40 To: [EMAIL PROTECTED]; Andrey Bronfin Andrey, Perhaps you could tell us what paper, and where to obtain it. From the context, it sounds like a reference to physical modifications rather than DML. Jared On Monday 13 January 2003 07:54, Andrey Bronfin wrote: Dear gurus ! A ( maybe ) stupid question : I always thought that bitmapped indexes are bad for tables that undergo many DMLs agains them. Today i have came across an Oracle white paper, which says modifications on tables with bitmap indexes can be done a lot faster than modifications with B-tree indexes.. Can you please sched some light on the matter ? Thanks a lot. Andrey,. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: N003_bitmap.pdf Description: Binary data
slowish query causing problems...
Title: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Foreign key indexes on individual columns or multiple columns
Hi, following script may help you. The brief description of the script is as follows. -- REMThis file checks the current users Foreign Keys to make sure of the REMfollowing: REM REM1) All the FK columns are have indexes to prevent a possible locking REM problem that can slow down the database. REM REM2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking REM problem the columns MUST be index in the same order as the FK is REM defined. REM REM3) If the script finds and miss match the script reports the correct REM order of columns that need to be added to prevent the locking REM problem. REM REM REM REM - (See attached file: foreign_key_locks.sql) Krishnaswamy, Ranganath [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] global.comcc: Sent by: [EMAIL PROTECTED] Subject: Foreign key indexes on individual columns or multiple columns 01/14/2003 12:29 PM Please respond to ORACLE-L Hi List, I have a table by name Benchmark_hdr with the following columns: BENCHMARK_HDR_IDNUMBERNOT NULL CALENDAR_TYPE CHAR (1), UOM VARCHAR2 (10), CALENDAR_PORT CHAR (1), CUSTOMER_MOT_ID NUMBERNOT NULL RESP_PARTY_NAME VARCHAR2 (10), FROM_EVENTNUMBER, TO_EVENT NUMBER, LAST_UPDATED DATE I have created foreign key constraints using the below commands: ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMERESP_FK FOREIGN KEY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) REFERENCES XM.CUSTOMER_RESPONSIBLE_PARTY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) ; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (FROM_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHMARK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (TO_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; The foreign keys need to be indexed so that the child table is not locked when a record in the parent table is being updated or deleted. If the foreign keys are indexed the corresponding child record or records are only locked when the parent table record is being updated or deleted. In this context I would like to seek the advice of the list as to whether I should create indexes on individual foreign keys or should I create one or more composite index(es) on foreign keys? If I need to create a composite index on which columns should I base my index on? Could anybody advise me on this with the reasons for following that particular approach? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: slowish query causing problems...
Eva, Can you send the explain plan and what the indexes are . Is 'INPRG' really a literal and is the sql dynamic and the below is just an example? Iain Nicoll -Original Message- Sent: 14 January 2003 11:29 To: Multiple recipients of list ORACLE-L Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nicoll, Iain INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: slowish query causing problems...
Title: slowish query causing problems... Ouch! I hate to see queries written this way...the query should answer the question you are trying to ask. It appears you are checking for the existance of a record in fwepcode1 that matches the criteria. If I understand the query correctly, you want to return 1 row if one of 2 conditions is met. 1) A row in fwepcode1 that matches 1 of the 3 where clauses 2) A row does not exist in valuelist with the below conditions. My suggestion for rewriting the query is below. Kevin SELECT 1 FROM fwepcode1 WHERE wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2' AND rownum = 1 UNION SELECT 1 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM valuelist WHERElistname = 'STATUS' ANDmaxvalue = 'A' ANDvalue= 'INPRG') )l -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:29 AMTo: Multiple recipients of list ORACLE-LSubject: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: bitmapped indexes
Title: RE: bitmapped indexes It was filtered by Listguru ... where did you get it from? Maybe we can pick it up at the source ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: bitmapped indexes Hi ! I don't remember where I got the paper, but I am attaching it. Cheers. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Oracle 9i release 3 coming?
Title: Internet file system Hi I think you are seeing notes on the latest release of 9iAS which is 9.0.3. Ben -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Boivin, Patrice JSent: January 13, 2003 9:24 PMTo: Multiple recipients of list ORACLE-LSubject: Oracle 9i release 3 coming? Oracle started posting items with the 9.0.3. number, does this imply they plan to release a 9i Release 3? 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]
RE: Question On High Parse to execute ratio
Title: RE: Question On High Parse to execute ratio If you are using 9202 and intermedia, intermedia will choke on anything other than exact for cursor_sharing parameter ... and yes, they finally have a patch that works. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Question On High Parse to execute ratio Success with using CURSOR_SHARING depends on your Oracle version. In earlier versions it's buggy. Igor Neyman, OCP DBA [EMAIL PROTECTED] *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: bitmapped indexes
Title: RE: bitmapped indexes But i got the attachment. AFAIK, listguru filters attachments, How did i get it then? Regards Naveen -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: bitmapped indexes It was filtered by Listguru ... where did you get it from? Maybe we can pick it up at the source ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: bitmapped indexes Hi ! I don't remember where I got the paper, but I am attaching it. Cheers.
RE: slowish query causing problems...
Title: slowish query causing problems... Eva, Is there an index on the fwepcode1 table with the three columns used in the where clause? Are the three columns varchar or varchar2? Make sure the EXP column is not a number! Secondly, I think I would change the query as follows: SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') ORnot exists(select 1 FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A' AND VALUE='INPRG' ) Tom Mercadante Oracle Certified Professional -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:29 AMTo: Multiple recipients of list ORACLE-LSubject: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
dates
I have a table with 1 column. I want to populate it with the dates of all the fridays for the last 2 years and the next 2 years from sysdate. so.. today is 14-JAN-2003 My column will have.. 10-jan-2003 03-jan-2003 ... .. 17-jan-2003 24-jan-2003 Any ideas? Cheers __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: bitmapped indexes
It's from a paper called Bitmap Indexing in Oracle7.3 and 8.0 which is available at http://www.newagetraining.com/library/ora_dev/sql_plsql/script/BitmapIndexes .pdf http://www.newagetraining.com/library/ora_dev/sql_plsql/script/BitmapIndexe s.pdf The theory is true in relation to data loads rather than OLTP operations. This quote from Tech Note 70067.1 outlines a benefit of bitmap indexes : - Very efficient parallel DML and loads: Bitmap indexes benefit data warehousing applications but they are not appropriate for OLTP applications with a heavy load of concurrent INSERTs, UPDATEs, and DELETEs. In a data warehousing environment, data is usually maintained by way of bulk inserts and updates. Index maintenance is deferred until the end of each DML operation. For example, if you insert 1000 rows, the inserted rows are placed into a sort buffer and then the updates of all 1000 index entries are batched. (This is why SORT_AREA_SIZE must be set properly for good performance with inserts and updates on bitmap indexes.) Thus, each bitmap segment is updated only once per DML operation, even if more than one row in that segment changes. Regards, Mike Hately -Original Message- Sent: 14 January 2003 12:54 To: Multiple recipients of list ORACLE-L It was filtered by Listguru ... where did you get it from? Maybe we can pick it up at the source ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Hi ! I don't remember where I got the paper, but I am attaching it. Cheers. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i release 3 coming?
If they release 9.0.3. items for iAS, will they call that iAS 9i Release 3 or iAS 10i? Maybe the version numbers don't mean anything to them anymore... Pat. -Original Message- Sent: Monday, January 13, 2003 11:49 PM To: Multiple recipients of list ORACLE-L doubtful, they've said that release 2 is the final release for 9i --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Oracle started posting items with the 9.0.3. number, does this imply they plan to release a 9i Release 3? 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] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Different Backups - A Comparative analysis
I don't believe RMAN does Oracle block level compression during the backup process. This was one of the original reasons Oracle support was giving SQLBT users trouble when they needed recovery support. Regards, Bill Burke The Kinder and Gentler DBA IOUG University Master Class Faculty 2001-2002 iDBA Management, High Performance Infrastructure and HA IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com www.KBMotorsports.biz -Original Message- Sent: Tuesday, January 14, 2003 3:59 AM To: Multiple recipients of list ORACLE-L Hello Jared AFAIK SQLBackTrack can do a TABLE restore from the backup and RMAN can not do it. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:51 PM On Monday 13 January 2003 06:03, Tim Gorman wrote: Another question: should SQL BackTrack be included for consideration? What does SQL BackTrack to that RMAN doesn't do? Jared -- 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Burke, William F (Bill) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dates
Title: RE: dates SELECT NEXT_DAY(SYSDATE + 7*(rnum-1),'Friday') FROM (SELECT ROWNUM rnum FROM DBA_OBJECTS) / Feel free to add a where clause Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Imran Ashraf [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Subject: dates I have a table with 1 column. I want to populate it with the dates of all the fridays for the last 2 years and the next 2 years from sysdate. so.. today is 14-JAN-2003 My column will have.. 10-jan-2003 03-jan-2003 ... .. 17-jan-2003 24-jan-2003 Any ideas? Cheers __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: encrypted user/passwd connection
http://documents.iss.net/literature/DatabaseScanner/reports/oracle/OraPolicy.pdf Sony kristanto Sony@polyfincaTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] nggih.com cc: Sent by: Subject: RE: encrypted user/passwd connection [EMAIL PROTECTED] m January 13, 2003 11:24 PM Please respond to ORACLE-L BTW rajesh, where can I find 'ORA_ENCRYPT_LOGIN variable and DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database link)' ? Pls respond ... -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 10:02 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: encrypted user/passwd connection All oracle passwords are encrypted is not a true statement. Failed login attempts, are retried by sending the password in an unencrypted format. Atleast, until 8.1.7. To avoid which, ORA_ENCRYPT_LOGIN variable and DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database link) should be set to TRUE. I could stand corrected though. Raj Sony kristanto Sony@polyfincaTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] nggih.com cc: Sent by: Subject: RE: encrypted user/passwd connection [EMAIL PROTECTED] m January 07, 2003 01:53 AM Please respond to ORACLE-L You're right Jared, all oracle password is encrypted. Btw Andrey if it is possible how to do it ? -Original Message- From: Jared Still [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Subject: Re: encrypted user/passwd connection Andre, Oracle does not send passwords across the network in clear text, they are encrypted by default. Jared On Monday 06 January 2003 05:43, Andrey Bronfin wrote: Dear list ! I have just been asked the following question: is it possible to make a connection from an Oracle client to an Oracle instance (both are 8.1.7) in an encrypted way. I.e. if someone is sitting with a sniffer between the server and the client, then i don't want him to be able to see the user/passwd i'm connecting with. Again , i am NOT asking how store the data in the DB in an encrypted way, but how to connect to an instance without showing my passwd. Thanks a lot! Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL
RE: Conversion from CLOB to RAW
Thanks Dennis, I'm meeting with them this morning at 10 to discuss this in more detail now that I have some feedback from the list, all of which I appreciate. I don't believe they are searching the information, but will know shortly. Regards, Bill Burke The Kinder and Gentler DBA IOUG University Master Class Faculty 2001-2002 iDBA Management, High Performance Infrastructure and HA IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com www.KBMotorsports.biz -Original Message- Sent: Monday, January 13, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Bill - Will you need to search for information that will be compressed? If yes, then performance could be really, really bad. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 2:07 PM To: Multiple recipients of list ORACLE-L I wasn't. I actually am still trying to look at the varchar2. The downside is the huge amount of data they want to keep in a compressed format hence the RAW or BLOB format. Regards, Bill Burke The Kinder and Gentler DBA IOUG University Master Class Faculty 2001-2002 iDBA Management, High Performance Infrastructure and HA IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com www.KBMotorsports.biz -Original Message- Sent: Sunday, January 12, 2003 12:34 AM To: Multiple recipients of list ORACLE-L Bill, If the data length is less than 2K, why not use varchar2? You get all the functionality like substr(), instr(), like etc. I'm not sure why you are leaning towards RAW, when the type you are storing is of character based. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 1:33 PM The amount of data being stored is fairly significant @60G of xml transaction data. The developers want to apply a compression routine to the xml string which will save about 70% of the space currently in use. BLOB was my original recommendation, they were pushing to go RAW instead. We've only got a couple of CLOB's out there, but they are taking up huge amounts of storage. On the overkill note, most all of the XML has been parsed to less than 2K in length so one of my thoughts was we had introduced LOB functionality without really needing it. The other aside to this is we will definately need to partition the data when we do the conversion as it currently resides in a traditional table as a CLOB. Regards, Bill Burke The Kinder and Gentler DBA IOUG University Master Class Faculty 2001-2002 iDBA Management, High Performance Infrastructure and HA IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com www.KBMotorsports.biz -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Bill, I agree with Michael. You've already got the data in a suitable datatype. Why move it to a cumbersome, soon-to-be-obsolete datatype? You can use DBMS_LOB functionality on LOBs, not on Raw. I'd be so happy if the couple dozen tables in our 3rd party Student Information system that have Long or Long Raw columns had CLOB or BLOB columns instead. It would make converting them to partitioned tables much easier. I definitely vote to keep your CLOBs. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Michael Fontana [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L et [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Conversion from CLOB to RAW 01/10/2003 02:15 PM Please respond to ORACLE-L At 11:30 AM 1/10/2003 -0800, Burke, William F (Bill) wrote: Here's where I get to ask the most likely simple question. I've inherited a database where it was built using a CLOB to hold XML data but we have now determined that was total overkill and want to move it to a RAW column or other suitable datatype. Looking for conversion issues or other alternatives. Since Oracle is moving us away from LONG and RAW datatypes, I assume you want to convert from CLOB to BLOB? BLOB is probably more storage-efficient, but since XML is made up of character data, I don't really understand the issue with keeping it a CLOB. What do you mean by overkill? -- 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
RE: Was 8.1.6 certified on Solaris 8?
Title: RE: Was 8.1.6 certified on Solaris 8? We run a couple of apps on 8.1.6/solaris 8 with no problems. Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing. -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 6:10 PM To: Multiple recipients of list ORACLE-L Subject: Was 8.1.6 certified on Solaris 8? We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 9i release 3 coming?
Actually, I see references to iAS 9.0.3 and 9.0.4 in various places on Metalink. I think the real difference will be when iAS 9.0.6 is released, which at one time was supposed to be this quarter. A lot of things are supposedly fixed/enhanced in 9.0.5 which won't be released to the public. It'll be interesting to see what gooberish name they come up with for 9.0.6. Jim -Original Message- Sent: Tuesday, January 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L If they release 9.0.3. items for iAS, will they call that iAS 9i Release 3 or iAS 10i? Maybe the version numbers don't mean anything to them anymore... Pat. -Original Message- Sent: Monday, January 13, 2003 11:49 PM To: Multiple recipients of list ORACLE-L doubtful, they've said that release 2 is the final release for 9i --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Oracle started posting items with the 9.0.3. number, does this imply they plan to release a 9i Release 3? 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] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [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: bitmapped indexes
Title: RE: bitmapped indexes you sure, you got it? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 14, 2003 8:34 AM Subject: RE: bitmapped indexes But i got the attachment. AFAIK, listguru filters attachments, How did i get it then? Regards Naveen -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: bitmapped indexes It was filtered by Listguru ... where did you get it from? Maybe we can pick it up at the source ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: bitmapped indexes Hi ! I don't remember where I got the paper, but I am attaching it. Cheers.
RE: Minimum required init.ora parameters
Which version and platform ? I've never found COMPATIBLE to be mandatory to startup a database. Every version does set a default value for COMPATIBLE [I believe it is 8.0 in 8.0.x and 8.1.x]. Hemant At 12:58 AM 14-01-03 -0800, you wrote: No, I tried to startup the DB without compatible parameter. It throws message as the same parameter is required. Nirmal. -Original Message- Sent: Monday, January 13, 2003 5:55 PM To: Multiple recipients of list ORACLE-L compatibility or compatible is not a mandatory init.ora parameter. I would think that only the first three are required. Hemant At 06:08 AM 13-01-03 -0800, you wrote: Nirmal - I believe there are four: db_name control_file db_block_size compatibility This is from John Hibbard, a great Oracle Education instructor. But why not try for yourself? Save off your init.ora, then create a new init.ora with just the above parameters. If Oracle comes up, then remove parameters. If there is another parameter, Oracle will tell you. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 4:59 AM To: Multiple recipients of list ORACLE-L List, I'm interested to know the minimum required parameters to startup the database. Pls anybody list out that? Nirmal., -- 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Was 8.1.6 certified on Solaris 8?
Yep. I have an old certification matrix hanging on my wall, and it shows that 8.1.6 was supported with 2.6, 2.7, and 2.8 (for 32-bit). (Guess it's time to clean up my cubie!) Barb --- Miller, Jay [EMAIL PROTECTED] wrote: We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Internet file system
Chris, iFS 1.X.X and below is alternately with the 8.1.7 database and 9iAS 1.0.2.X 9iFS 9.0.1 is with the Database 9iFS 9.0.2 is part of 9iAS 9.0.2 9iFS 9.0.3 has been renamed as Oracle Files and is part of Oracle Collaboration Suite. Check the MetaLink certification pages for Internet File System [which goes upto 9.0.1] 9i Internet Application Server [where 9iFS 9.0.2 is listed under components for 9iAS 9.0.2] Oracle Collaboration Suite [where Oracle Files 9.0.3 is listed under components for OCS 9.0.3] Hemant At 07:18 AM 13-01-03 -0800, you wrote: as per usual, technet website is confusing as heck. I am trying to locate the latest release on IFS. I am on the products website and see the following on the IFS webpage: Content Management Software Development Kit (CMSDK) Version 9.0.3 (Note: this release requires Oracle 9.2 or Oracle 9.0.1) Content Management Software Development Kit (CMSDK) version 9.0.3 for Windows Content Management Software Development Kit (CMSDK) version 9.0.3 for Linux Content Management Software Development Kit (CMSDK) version 9.0.3 for HP-UX Content Management Software Development Kit (CMSDK) version 9.0.3 for HP Tru64 Content Management Software Development Kit (CMSDK) version 9.0.3 for AIX Oracle Internet File System Downloads Version 9.0.2 Oracle Internet File System Release 9.0.2 can now be downloaded from the Oracle9iAS software page in the 'Supplemental CD's' section. == Has IFS been renamed to Content Management Software Development Kit?? or no? I don't want to waste my time downloading what I think is IFS v9.0.3 when v9.0.2 is the latest release and CMSDK is just for developers working with IFS. Thanks for your help. 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: slowish query causing problems...
Title: slowish query causing problems... Hello, Thanks for the replies Here is the description of the table and the indexes, pls remember I have removed and tested each index seperately, still insisted on a Full search. 9 indexes is not my idea of a perfect situation these are created by the developers ( another company) so politics plays a big roll here. CREATE TABLE FWEPCODE1 ( RECORDID INTEGER NOT NULL, FUNC VARCHAR2 (20), WOTYPE VARCHAR2 (20), EXP VARCHAR2 (20), PIK VARCHAR2 (20), FUNCDESC VARCHAR2 (80), EXPDESC VARCHAR2 (80), PIKDESC VARCHAR2 (80), EX1 VARCHAR2 (1), EX2 VARCHAR2 (10), EX3 VARCHAR2 (10), EX4 VARCHAR2 (10), EX5 VARCHAR2 (10), EX6 VARCHAR2 (10), EX7 VARCHAR2 (10), EX8 VARCHAR2 (10), EX9 VARCHAR2 (10), EX10 VARCHAR2 (10) ) ; FWEPCODE1_NDX1 ON FWEPCODE1(FUNC, WOTYPE, EXP, PIK);FWEPCODE1_NDX2 ON FWEPCODE1(FUNC); FWEPCODE1_NDX3 ON FWEPCODE1(EXP); FWEPCODE1_NDX4 ON FWEPCODE1(FUNC, WOTYPE); FWEPCODE1_NDX5 ON FWEPCODE1(FUNC, EX2); FWEPCODE1_NDX6 ON FWEPCODE1(EXPDESC); FWEPCODE1_NDX7 ON FWEPCODE1(FUNC, WOTYPE, PIK); FWEPCODE1_NDX8 ON FWEPCODE1(RECORDID); FWEPCODE1_NDX9 ON FWEPCODE1(WOTYPE, FUNC, EXP); I have added a CSV file as an attachment as requested by one lister of the plan_table. Once again appreciation for all the help. Regards Denham -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: slowish query causing problems... Eva, Is there an index on the fwepcode1 table with the three columns used in the where clause? Are the three columns varchar or varchar2? Make sure the EXP column is not a number! Secondly, I think I would change the query as follows: SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') ORnot exists(select 1 FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A' AND VALUE='INPRG' ) Tom Mercadante Oracle Certified Professional -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:29 AMTo: Multiple recipients of list ORACLE-LSubject: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA "UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity." Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER
RE: bitmapped indexes
Title: RE: bitmapped indexes Oops! I had just saved it to be read later. When i try to open it says, 'Corrupted File' Sorry for the confusion Regards Naveen -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 8:15 PMTo: Multiple recipients of list ORACLE-LSubject: Re: bitmapped indexes you sure, you got it? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 14, 2003 8:34 AM Subject: RE: bitmapped indexes But i got the attachment. AFAIK, listguru filters attachments, How did i get it then? Regards Naveen -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 6:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: bitmapped indexes It was filtered by Listguru ... where did you get it from? Maybe we can pick it up at the source ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: bitmapped indexes Hi ! I don't remember where I got the paper, but I am attaching it. Cheers.
RE: bitmapped indexes
The paper says performance is good but concurrency is a problem: Depending on the cardinality of data, this may mean locking several thousand rows at once. For this reason, it may not be a good idea to use bitmap indexes for environments where high levels of concurrent updates to the base data are required. Regards, Waleed -Original Message- Sent: Tuesday, January 14, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Hi ! I don't remember where I got the paper, but I am attaching it. Cheers. -Original Message- Sent: ? 13 ? 2003 19:40 To: [EMAIL PROTECTED]; Andrey Bronfin Andrey, Perhaps you could tell us what paper, and where to obtain it. From the context, it sounds like a reference to physical modifications rather than DML. Jared On Monday 13 January 2003 07:54, Andrey Bronfin wrote: Dear gurus ! A ( maybe ) stupid question : I always thought that bitmapped indexes are bad for tables that undergo many DMLs agains them. Today i have came across an Oracle white paper, which says modifications on tables with bitmap indexes can be done a lot faster than modifications with B-tree indexes.. Can you please sched some light on the matter ? Thanks a lot. Andrey,. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Oracle 8 DBA with SAP R/3 experience needed- New York
Large national known company in New York City, New York needs an Oracle DBA with SAP R/3 experience to join their corporate IT staff. *PLEASE Do Not send your resume for this position UNLESS you already live in the Greater New York City area and have the skills outlined below for this position. No relocation. Salary Range: $100,000-120,000 maybe more depends on experience. 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. *Responsibilities: To be responsible for the installation, maintenance, configuration, and backup/recovery operations for Oracle8 in a SAP R/3 environment. This includes data access and connectivity tools. It also includes SAP related tools of Taxware, Remit and Requisite. To provide customer support for new project development and production maintenance. *Requirements: -Oracle 8 DBA experience. -Minimum 2 years SAP R/3 experience. -Solaris, AIX, BIW experience. -Desired not requied: SAP tax tools: Taxware, Remit and Requisite. -Desired: Experience managing large-scale Relational Database Management Systems. -Ability to work independently and as a member of a team, Strong communication, analytical, and written skills. -U.S. citizenship or permanent residency is required. For immediate consideration, please send your resume as a Word attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Please use job code: One/NYC/DBA/SAP/TK (*NYC area candidates only) 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).
RE: Was 8.1.6 certified on Solaris 8?
We are running 8.1.6 against Solaris 8 patch level Generic_108528-15. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 3:10 PM To: Multiple recipients of list ORACLE-L We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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).
RE: Minimum required init.ora parameters
Hemant - I believe this applies to Oracle8i versions. The key point is try it. If you have a small test database you can bounce, it will take you just a couple of minutes to comment out the COMPATIBLE parameter and bounce the database. I agree with you that my assumption was that it was unnecessary -- until I proved it for myself. In college I was really looking forward to my philosophy class. Thought I would learn the secret of life or something. Anyway one of the main ideas I recall was a bunch of Greek philosophers (Aristotle ?) that spent an afternoon speculating on how many teeth a horse should have, and wrote up the discussion. My reaction was go count them. If you think you have a bad horse, take a survey. Oh well, the career paths for a philosophy major are limited, so I guess I didn't miss much. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 14, 2003 9:09 AM To: Multiple recipients of list ORACLE-L Which version and platform ? I've never found COMPATIBLE to be mandatory to startup a database. Every version does set a default value for COMPATIBLE [I believe it is 8.0 in 8.0.x and 8.1.x]. Hemant At 12:58 AM 14-01-03 -0800, you wrote: No, I tried to startup the DB without compatible parameter. It throws message as the same parameter is required. Nirmal. -Original Message- Sent: Monday, January 13, 2003 5:55 PM To: Multiple recipients of list ORACLE-L compatibility or compatible is not a mandatory init.ora parameter. I would think that only the first three are required. Hemant At 06:08 AM 13-01-03 -0800, you wrote: Nirmal - I believe there are four: db_name control_file db_block_size compatibility This is from John Hibbard, a great Oracle Education instructor. But why not try for yourself? Save off your init.ora, then create a new init.ora with just the above parameters. If Oracle comes up, then remove parameters. If there is another parameter, Oracle will tell you. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 4:59 AM To: Multiple recipients of list ORACLE-L List, I'm interested to know the minimum required parameters to startup the database. Pls anybody list out that? Nirmal., -- 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Oracle 9i release 3 coming?
Maybe the version numbers don't mean anything to them anymore... Pat. bingo! Oracle 9iAS 9.0.3 is still Release 2 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Parallel Replication of Single Table
Hi Larry, In the distributed manual it makes a comment about DML serializing when doing remote operations. I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - Accessible with your email software or over the web -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMOUG Training Days 2003
The Rocky Mountain Oracle User Group Training Days will be held in Denver, Colorado on March 5 6, 2003. This year's scheduled speakers include Rachel Carmichael,Gary Dodge, David Ensor, Tim Gorman, Ruth Gramolini, Stephan Haisley, John King, Anjo Kolk, Cary Millsap, Craig Shallahamer, and April Wells. We will have 99 sessions covering a wide range of topics and skill levels. There is an exhibition with over 20 vendors. Sign up by January 28 to receive the early registration price. New for this year, if you are a member of another user group, you qualify for the Member price, currently $210. This includes the conference, proceedings (printed and cd-rom), gift, 2 sit down meals and a welcome reception. For those wishing to stay and enjoy the Rocky Mountains in winter, information on weekend packages including ski passes is published on the website. Register online or get more information at www.rmoug.org.
Re: USER_TABLESPACES has more rows than DBA_TABLESPACES
I'm still confused. They are not in DBA_TABLESPACES, only USER_TABLESPACES.Keith- Original Message - From: "Stephane Faroult" [EMAIL PROTECTED]To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Monday, January 13, 2003 1:34 PMSubject: Re: USER_TABLESPACES has more rows than DBA_TABLESPACES Keith Moore wrote: Has anyone else seen this or can you explain it? I have 7 tablespaces in USER_TABLESPACES that don't exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear from USER_TABLESPACES. They have a status of INVALID. The database is version 8.0.5 (Yeah, I know, we'll be going to 9i real soon now)Keith Regular behaviour. Rows are never deleted from sys.ts$ (on which DBA_TABLESPACES is based). -- 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). The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
RE: USER_TABLESPACES has more rows than DBA_TABLESPACES
Keith, yes, it's regular behaviour that the TS$ entry remains but the discrepancy between those 2 views is a bug which is fixed in 8.0.6. Basically, DBA_TABLESPACES excludes 'INVALID' tablespaces but USER_TABLESPACES doesn't. There are duplicates for the bug. The numbers are 284887, 553723 and 546913. regards, Mike Hately -Original Message- Sent: 14 January 2003 15:34 To: Multiple recipients of list ORACLE-L I'm still confused. They are not in DBA_TABLESPACES, only USER_TABLESPACES. Keith - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Monday, January 13, 2003 1:34 PM Keith Moore wrote: Has anyone else seen this or can you explain it? I have 7 tablespaces in USER_TABLESPACES that don't exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear from USER_TABLESPACES. They have a status of INVALID. The database is version 8.0.5 (Yeah, I know, we'll be going to 9i real soon now) Keith Regular behaviour. Rows are never deleted from sys.ts$ (on which DBA_TABLESPACES is based). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] mailto:[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] mailto:[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: slowish query causing problems...
This query make sense. It should not use full table scan. Denham, did you try that, I am curious to know the answer. Joan Toepke, Kevin M wrote: Ouch! I hate to see queries written this way...the query should answer the question you are trying to ask. It appears you are checking for the existance of a record in fwepcode1 that matches the criteria. If I understand the query correctly, you want to return 1 row if one of 2 conditions is met. 1) A row in fwepcode1 that matches 1 of the 3 where clauses 2) A row does not exist in valuelist with the below conditions. My suggestion for rewriting the query is below. Kevin SELECT 1 FROM fwepcode1 WHERE wotype = 'TST' ANDfunc = 'C0NEPRF' ANDEXP= '2' ANDrownum = 1 UNION SELECT 1 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM valuelist WHERE listname = 'STATUS' ANDmaxvalue = 'A' ANDvalue= 'INPRG') )l -Original Message- From: Denham Eva [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Subject: slowish query causing problems... Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
User memory connections...
Hi guys... I would like to know how can I get: how much memory an user connection/session is using? Can u help me? Thanks in advance! Regards! JL __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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/system Crashing
Title: RE: Database/system Crashing Please do. Any information at this point will help. The only difference is that when it fails it doesn't even get to write the /var/adm/messages or the core dump. Oracle wasn't writing to the alert log file until I moved it off the root disk an onto another disk. That has helped but its definitely an OS problem. Is it possible to re-locate the messages and/or core dump file for UNIX to another disk? If we did, maybe it could write to it like Oracle was able to. I don't know if that's a possibility on the Unix system or does it have to reside on the root disk? Thanks in advance!Val -Original Message-From: Richard Ji [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 12:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Sorry for getting back to you late on this. In our case, the server crashes each time with CPU panic message in /var/adm/messages. We used adb to analyze the core dump and saw in each instance of the crash, NFS lead to it. It was trying to free memory twice which caused the panic. And talking to Sun confirmed that there was a bug in NFS. If you are interested in it I can send you the patch number. -Original Message-From: Webber Valerie H [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 5:26 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Richard, Yes this is a SUN platform. One SA now believes it is independent of the database since it happens when the database isn't running. I moved the background dest files to the other disk (other than the root disk.) Normally it would have crashed by now after starting the database and using Designer but so far so good. There are no error messages in /var/adm/messages. There's no core dump file either. Its like the system gets corrupt before it can write to the /var/adm/messages file. What were the symptoms of your Sun NFS related crashed and how did you diagnois the problem and what was the solution (if it were that simple) Val -Original Message-From: Richard Ji [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 4:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Val, Sorry I missed the previous messages. Was this a Sun platform? Did the system crash with a CPU panic in /var/adm/messages? We resovled a Sun NFS related crashes a couple of months ago. Richard Ji -Original Message-From: Webber Valerie H [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Yes there are NFS mounts involved. What you said about the OS locks on the audit directory makes a lot of sense. My SA's are back to thinking it's a OS problem because it crashed again with the database shut down. The odd thing is that there is nothing written to the Oracle alert log file nor are there any entries in the trace files. But when the system is rebooted and I bring the db back up, Oracle knows it previously crashed and recovers itself. That's in the alert log file. Its like the system is losing its pointers or something. I suggested reinstalling the OS and Oracle then put my database back and see if that helps. Are there huge risks with this scenario? Another odd thing that the SA's can't figure out is there are no entries in the message file nor can they get a dump file to determine why the system crashed. There is nothing. It crashed over the weekend with no activity and they got some sort of i-nodes error. Thanks for all your replies. Any ideas are helpful and I will relay them to our SA's... Val -Original Message- From: Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 10:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: Database/system Crashing I wonder if a file lock is being left in place when the instance crashes, and the OS does not clear the lock until a reboot. I would think the OS should clear this without a reboot, but stranger things have been seen with OS's ... even Unix. This doesn't explain why the instance crashes. I wonder if fuser would show anything. Are there any NFS mounts involved? -Original Message- Yes, you're correct and it can write the file to $ORACLE_HOME/rdbms/audit once the system is rebooted. Its just that when the database
RE: slowish query causing problems...
Eva, Don't know if the problem is only 3 out of 4 parts of the composite index are used so it can't work out how many distinct values there are of the first three parts only (are you able to try creating an index with only the 3 parts to see what would happen)? Iain Nicoll -Original Message- Sent: 14 January 2003 14:49 To: Multiple recipients of list ORACLE-L Hello, Thanks for the replies Here is the description of the table and the indexes, pls remember I have removed and tested each index seperately, still insisted on a Full search. 9 indexes is not my idea of a perfect situation these are created by the developers ( another company) so politics plays a big roll here. CREATE TABLE FWEPCODE1 ( RECORDID INTEGER NOT NULL, FUNC VARCHAR2 (20), WOTYPEVARCHAR2 (20), EXP VARCHAR2 (20), PIK VARCHAR2 (20), FUNCDESC VARCHAR2 (80), EXPDESC VARCHAR2 (80), PIKDESC VARCHAR2 (80), EX1 VARCHAR2 (1), EX2 VARCHAR2 (10), EX3 VARCHAR2 (10), EX4 VARCHAR2 (10), EX5 VARCHAR2 (10), EX6 VARCHAR2 (10), EX7 VARCHAR2 (10), EX8 VARCHAR2 (10), EX9 VARCHAR2 (10), EX10 VARCHAR2 (10) ) ; FWEPCODE1_NDX1 ON FWEPCODE1(FUNC, WOTYPE, EXP, PIK); FWEPCODE1_NDX2 ON FWEPCODE1(FUNC); FWEPCODE1_NDX3 ON FWEPCODE1(EXP); FWEPCODE1_NDX4 ON FWEPCODE1(FUNC, WOTYPE); FWEPCODE1_NDX5 ON FWEPCODE1(FUNC, EX2); FWEPCODE1_NDX6 ON FWEPCODE1(EXPDESC); FWEPCODE1_NDX7 ON FWEPCODE1(FUNC, WOTYPE, PIK); FWEPCODE1_NDX8 ON FWEPCODE1(RECORDID); FWEPCODE1_NDX9 ON FWEPCODE1(WOTYPE, FUNC, EXP); I have added a CSV file as an attachment as requested by one lister of the plan_table. Once again appreciation for all the help. Regards Denham -Original Message- Sent: Tuesday, January 14, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Eva, Is there an index on the fwepcode1 table with the three columns used in the where clause? Are the three columns varchar or varchar2? Make sure the EXP column is not a number! Secondly, I think I would change the query as follows: SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR not exists(select 1 FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A' AND VALUE='INPRG' ) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 14, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Hello List, Pls help me on this problem. Our application does a validation when it uses a certain screen, as it so happens this screen is used very intensively. The performance is very slow, I have isolated the main culprit. I have tried the following. I have dropped all the indexes and tried recreating them individually. Each time I have run an explain plan on the query, the optimizer (both rule and Choose) have chosen to do a FULL table scan on the fwepcode table. Even when using a hint to explicitly use the index it still uses FULL. This is very frustrating indeed. SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR 'INPRG' NOT IN (SELECT VALUE FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A') Is the reason that the optimizer does not use any of the indexes because of the SELECT DISTINCT (1)? I have tried adjusting this query slightly to remove this and it still insists on doing a full table scan. Funny enough the sub query on valuelist table does use a index. The table contains 8920 rows. The cost according to the explain plan is 703 and bytes 9834. The system is a Oracle 817 on Win2k. Pls advise, any options or help will be appreciated. Many Thanks Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender
Context/Intermedia/OracleText index rebuilds and ORA-29863
Hey all, Spent last night (til the Witching Hour) patching from 8.1.7.2 to 8.1.7.4. During the process, we had all three of our Context indexes trashed. My guess is that it was because CTXSYS.CTX_DDL.INDEX_SYNC() DBMS_JOB was running while the CTX upgrade scripts were running. I thought it was funny that there were no instructions to break this job before the upgrade, but it did work in test back in September. Anyway, I filed a TAR, and after re-running the dr0 admin scripts, we tried to rebuild the indexes online using: ALTER INDEX my_context_index REBUILD ONLINE; Alas, the error: ORA-29863: Warning in the Execution of ODCIINDEXCREATE Routine reared it's ugly puss. We checked the advice of the Metalink article on the error, but we didn't have any of those symptoms (out of Temp or Perm TS space) and ended up dropping and recreating the indexes. So, has anyone run across this error before? I'd hate to think I'm not able rebuild indexes, much less online... TIA! Rich Rich Jesse System/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).
strange SQL with TOAD
List, We use TOAD as one of our database tools and I was viewing some of the SQL statements that were processed and I can't explain the why the statement is the way it is. The developer wrote Select to_char(sysdate,'mm-dd-') from dual; in a VB application. We use the Oracle and MS ODBC drivers to connect to the database. What I saw as the SQL statement: select ROWID,to_char(sysdate,'mm-dd-') from dual where ROWID = :v1 Where did the select ROWID come from? In other statements where the select written is Select table.* ... I see Select table.ROWID, table.*... Again, where does the ROWID come from? Is it a VB,/+ODBC thing that I should not be concerned with? Any links to the info would be helpfull in understanding the query. Thanks, 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).
Re: Different Backups - A Comparative analysis
Ditto what Jared said. It was bad! --- Jared Still [EMAIL PROTECTED] wrote: Yes, well, I've heard that claim too. It may actually work now, but a very capable DBA ( 2 actually ) spent quite a bit of time testing this with no good results. And then there was the SQL BT glitch that left one of my former coworkers without a certain TBS in the DW for 6 weeks. Yes, 6 weeks. And you complain about Oracle Support. ;) Jared On Monday 13 January 2003 18:29, Deshpande, Kirti wrote: I think SQLBackTrack can get the table data back from the backup file without the need to create a partial database on another server and then export the table from it. I remember seeing a demo where a dropped table was recovered by the BMC rep. - Kirti -Original Message- Sent: Monday, January 13, 2003 6:15 PM To: Multiple recipients of list ORACLE-L RMAN Does everything that SQL Backtrack does, for free. :-) In fact, last I heard, BMC was planning on altering SQL Backtrack so that it is really nothing more than a nice fancy front end to RMAN. When I was at CSX we moved away from SQL Backtrack to RMAN and never had any regrets. The only issue with SQL Backtrack vs. RMAN was support for 7.x databases. RMAN does not support anything 8.0. RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Monday, January 13, 2003 4:39 PM To: Multiple recipients of list ORACLE-L I *think* that RMAN does everything SQL Backtrack does. Can't be positive, as I haven't used it for awhile. RMAN seems to have everything I recall SQL BT having, and then some. Jared On Monday 13 January 2003 12:38, Tim Gorman wrote: Don't know really. Just thought that it should probably be included, then I was hoping to find out... :-) - Original Message - To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED] Sent: Monday, January 13, 2003 10:49 AM On Monday 13 January 2003 06:03, Tim Gorman wrote: Another question: should SQL BackTrack be included for consideration? What does SQL BackTrack to that RMAN doesn't do? Jared -- 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dw tool question
From: Koivu, Lisa [EMAIL PROTECTED] Subject: RE: dw tool question We have two products here: Business Objects and BRIO. It depends on what kind of end-user you expect to support. The main difference I see between these two is that Business Objects can easily hide the metadata detail and joins from the L-user. Brio can do this too, via the setup of the actual hypercube you want to publish. Brio doesn't hide it as well, meaning that if the user really wants, they can see the underlying structure, but they can definitely mask it to make it simpler for the user. Basically, you set up the schema and define the joins yourself, then when a column is called from one table, the join is pre-written for the user. With Business Objects, the user community had been running their own reports for years with a very simple universe. You should be able to mimic the BO Universe in Brio, at least to the extent that the table joins are not visible and are pre-written for you. So I guess it's dependent upon what kind of end-user you plan to support. If you have some really savvy users, Brio is a good choice. If you have users who expect to just refresh and get their report without wanting to know why and how, then BO fits the bill. Brio allows you to embed reports and pivot tables into their portal concept, so that's one way you could do it. Create the portal with a few canned reports and/or queries, and then let the user have that. If they want to do ad-hoc reporting, just make sure the schema is set up before hand. We are being pushed away from BO to Brio and I don't like it... I just remind myself that it's just a job, sigh and remind myself that every company makes dumb decisions like this. I don't think either of those solutions will ultimately differ that much -- they are closer competitors than it would appear on the surface. Very similar offerings, just at different points on the complexity scale. Bruce, in your experience, has Brio been customized to the extent that I'm describing above with BO? I'd love to hear your comments, on the list or off. And yes, we essentially did just what I described above. Created a number of pre-built pivot tables (just to get things started) and then let them have at it with the raw materials. For the most part, I don't remember encountering many support issues. Part of it might have to do with your schema. A star schema is a lot easier for an end user to understand, and for Brio to hide, than, say, a snowflake or a normalized schema. Anyhoo, good luck! ;-) thanks, bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
redo log placement question
Hi, I understand the reasons most documents suggest not putting redo on raid-5 array. But my situation is limited to either on raid-5 with multiple controllers or on raid-1 with single controller. Raid-5 hosts only database files, Raid-1 hosts OS and Oracle software. What will be the better option in this case? The operations are mostly OLAP. Any comments and suggestions are appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eric Wang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dw tool question
One follow-up thought that I neglected to mention before: Business Objects definitely does metadata better. If you're looking for embed a lot of metadata about heirarchies, related tables, source information, etc., then BO will support that better. Brio is okay, but it's not really robust in that area. So if you want to make a case for BO over Brio, you might want to start pushing the importance of good metadata, and start entering a lot of it into your database. With that as a opening ante, you might be able to sway the decision a bit. thanks, bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Was 8.1.6 certified on Solaris 8?
Thanks everyone! -Original Message- Sent: Tuesday, January 14, 2003 10:51 AM To: Multiple recipients of list ORACLE-L We are running 8.1.6 against Solaris 8 patch level Generic_108528-15. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 3:10 PM To: Multiple recipients of list ORACLE-L We're upgrading from Solaris 2.6 to Solaris 8 and 8.1.6 to 8.1.7 on one of our boxes. I want to know if it's possible to do the OS upgrade first and then the database upgrade (e.g., I'd be running 8.1.6 on Solaris 8 for a day or so). The Oracle certification matrix only says that 8.1.6 is desupported and therefore doesn't list any certified OS versions for it. Did anyone run 8.1.6 on Solaris 8 or remember if it was ever certified? TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: strange SQL with TOAD
Do you have the Option, Show Rowid enabled under Options/Data Grids-Data ?? -Original Message- Sent: Tuesday, January 14, 2003 11:34 AM To: Multiple recipients of list ORACLE-L List, We use TOAD as one of our database tools and I was viewing some of the SQL statements that were processed and I can't explain the why the statement is the way it is. The developer wrote Select to_char(sysdate,'mm-dd-') from dual; in a VB application. We use the Oracle and MS ODBC drivers to connect to the database. What I saw as the SQL statement: select ROWID,to_char(sysdate,'mm-dd-') from dual where ROWID = :v1 Where did the select ROWID come from? In other statements where the select written is Select table.* ... I see Select table.ROWID, table.*... Again, where does the ROWID come from? Is it a VB,/+ODBC thing that I should not be concerned with? Any links to the info would be helpfull in understanding the query. Thanks, 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: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database/system Crashing
Title: RE: Database/system Crashing Val, Here is the Sun patch 108727-16 that was related to our problem. Does the system have savecore running? You can try to symbolic link /var/adm to another disk. In our case Oracle never wrote anything to alert log because it was a system crash. Sounds like your system is having IO related issues. Is it internal disks? or SAN? -Original Message-From: Webber Valerie H [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Please do. Any information at this point will help. The only difference is that when it fails it doesn't even get to write the /var/adm/messages or the core dump. Oracle wasn't writing to the alert log file until I moved it off the root disk an onto another disk. That has helped but its definitely an OS problem. Is it possible to re-locate the messages and/or core dump file for UNIX to another disk? If we did, maybe it could write to it like Oracle was able to. I don't know if that's a possibility on the Unix system or does it have to reside on the root disk? Thanks in advance!Val -Original Message-From: Richard Ji [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 12:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Sorry for getting back to you late on this. In our case, the server crashes each time with CPU panic message in /var/adm/messages. We used adb to analyze the core dump and saw in each instance of the crash, NFS lead to it. It was trying to free memory twice which caused the panic. And talking to Sun confirmed that there was a bug in NFS. If you are interested in it I can send you the patch number. -Original Message-From: Webber Valerie H [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 5:26 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Richard, Yes this is a SUN platform. One SA now believes it is independent of the database since it happens when the database isn't running. I moved the background dest files to the other disk (other than the root disk.) Normally it would have crashed by now after starting the database and using Designer but so far so good. There are no error messages in /var/adm/messages. There's no core dump file either. Its like the system gets corrupt before it can write to the /var/adm/messages file. What were the symptoms of your Sun NFS related crashed and how did you diagnois the problem and what was the solution (if it were that simple) Val -Original Message-From: Richard Ji [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 4:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Val, Sorry I missed the previous messages. Was this a Sun platform? Did the system crash with a CPU panic in /var/adm/messages? We resovled a Sun NFS related crashes a couple of months ago. Richard Ji -Original Message-From: Webber Valerie H [mailto:[EMAIL PROTECTED]]Sent: Monday, January 06, 2003 11:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Database/system Crashing Yes there are NFS mounts involved. What you said about the OS locks on the audit directory makes a lot of sense. My SA's are back to thinking it's a OS problem because it crashed again with the database shut down. The odd thing is that there is nothing written to the Oracle alert log file nor are there any entries in the trace files. But when the system is rebooted and I bring the db back up, Oracle knows it previously crashed and recovers itself. That's in the alert log file. Its like the system is losing its pointers or something. I suggested reinstalling the OS and Oracle then put my database back and see if that helps. Are there huge risks with this scenario? Another odd thing that the SA's can't figure out is there are no entries in the message file nor can they get a dump file to determine why the system crashed. There is nothing. It crashed over the weekend with no activity and they got some sort of i-nodes error. Thanks for all your replies. Any ideas are helpful and I will relay them to our SA's... Val -Original Message- From: Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003
RE: strange SQL with TOAD
Title: RE: strange SQL with TOAD Also, you might try turning off the 'editable queries' in the options. That causes it to do a select rowid, * from table, so that once you edit the grid, it will use the rowid to go back and actually make the update to the data. -Original Message- From: Kevin Lange [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: RE: strange SQL with TOAD Do you have the Option, Show Rowid enabled under Options/Data Grids-Data ?? -Original Message- Sent: Tuesday, January 14, 2003 11:34 AM To: Multiple recipients of list ORACLE-L List, We use TOAD as one of our database tools and I was viewing some of the SQL statements that were processed and I can't explain the why the statement is the way it is. The developer wrote Select to_char(sysdate,'mm-dd-') from dual; in a VB application. We use the Oracle and MS ODBC drivers to connect to the database. What I saw as the SQL statement: select ROWID,to_char(sysdate,'mm-dd-') from dual where ROWID = :v1 Where did the select ROWID come from? In other statements where the select written is Select table.* ... I see Select table.ROWID, table.*... Again, where does the ROWID come from? Is it a VB,/+ODBC thing that I should not be concerned with? Any links to the info would be helpfull in understanding the query. Thanks, 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: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: strange SQL with TOAD
Ron, Are you using VB with ADO to return XML? ADO does some mighty strange stuff under the covers. If so, then I would guess that this is the cuplrit. My other guess is the VB is doing it in preparation to perform an update using the ROWID column in a where clause. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, January 14, 2003 12:34 PM To: Multiple recipients of list ORACLE-L List, We use TOAD as one of our database tools and I was viewing some of the SQL statements that were processed and I can't explain the why the statement is the way it is. The developer wrote Select to_char(sysdate,'mm-dd-') from dual; in a VB application. We use the Oracle and MS ODBC drivers to connect to the database. What I saw as the SQL statement: select ROWID,to_char(sysdate,'mm-dd-') from dual where ROWID = :v1 Where did the select ROWID come from? In other statements where the select written is Select table.* ... I see Select table.ROWID, table.*... Again, where does the ROWID come from? Is it a VB,/+ODBC thing that I should not be concerned with? Any links to the info would be helpfull in understanding the query. Thanks, 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: 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: dw tool question
Title: RE: dw tool question Hi Bruce, Thank you so much for your comments. Turns out I have a lot to learn about Brio. It's not my job to create the metadata layer in the app so I passed that info on to our report programmer. She knew exactly what you were talking about whew! I really thought we were on the road to ruin. Lisa Koivu Oracle Daydream Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Bruce A. Bergman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: dw tool question One follow-up thought that I neglected to mention before: Business Objects definitely does metadata better. If you're looking for embed a lot of metadata about heirarchies, related tables, source information, etc., then BO will support that better. Brio is okay, but it's not really robust in that area. So if you want to make a case for BO over Brio, you might want to start pushing the importance of good metadata, and start entering a lot of it into your database. With that as a opening ante, you might be able to sway the decision a bit. thanks, bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: perl timeout
Thx I left your book in Missouri:( Now I'm trying to simulate a database with
hanging connections to test the script. Anyone know how best to simulate that?
Dave
On Mon, Jan 13, 2003 at 07:43:29PM -0800, Jared Still wrote:
Gee Dave, I know of a book that has scripts that
already do this. ;)
Here's an untested bit of code to demonstrate.
my $dbh;
eval {
local $SIG{ALRM} = sub {
die connection timeout\n;
};
alarm 60;
$dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
}
);
};
# the alarm reset must be outside the eval{}
alarm 0;
I say untested cuz I simplified it a bit after lifting
it from a script.
HTH,
Jared
On Monday 13 January 2003 15:49, David Turner wrote:
Does anyone have some perl code that will return an error if it take longer
than a certain number of seconds to connect to or return the results from a
database? I'd like to have some of my queries connect to an alternate
database if there is a problem connecting or returning results within 10
seconds. Any other suggestions are appreciated.
Dave
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Turner
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Foreign key indexes on individual columns or multiple columns
The only requirement for indexes to support an FK is that the leading columns of the index match the columns of the FK. I believe that they must also be in the same order, but I'll let you look that up in the fine manual. So, if you find that you need an index made up of the columns RESP_PARTY_NAME, CUSTIMER_MOT_ID and FROM_EVENT, the following index would prevent locking: create index benchmark_hdr_custresp_idx on benchmark_hdr ( RESP_PARTY_NAME ,CUSTIMER_MOT_ID , FROM_EVENT ); There's no need for a separate index on just the FK columns. HTH Jared Krishnaswamy, Ranganath [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2003 02:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Foreign key indexes on individual columns or multiple columns Hi List, I have a table by name Benchmark_hdr with the following columns: BENCHMARK_HDR_IDNUMBERNOT NULL CALENDAR_TYPE CHAR (1), UOM VARCHAR2 (10), CALENDAR_PORT CHAR (1), CUSTOMER_MOT_ID NUMBERNOT NULL RESP_PARTY_NAME VARCHAR2 (10), FROM_EVENTNUMBER, TO_EVENT NUMBER, LAST_UPDATED DATE I have created foreign key constraints using the below commands: ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMERESP_FK FOREIGN KEY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) REFERENCES XM.CUSTOMER_RESPONSIBLE_PARTY (RESP_PARTY_NAME, CUSTOMER_MOT_ID) ; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHAMRK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (FROM_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; ALTER TABLE BENCHMARK_HDR ADD CONSTRAINT BENCHMARK_HDR_CUSTOMEREVENT_FK FOREIGN KEY (TO_EVENT) REFERENCES XM.CUSTOMER_EVENT (CUSTOMER_EVENT_ID) ON DELETE CASCADE; The foreign keys need to be indexed so that the child table is not locked when a record in the parent table is being updated or deleted. If the foreign keys are indexed the corresponding child record or records are only locked when the parent table record is being updated or deleted. In this context I would like to seek the advice of the list as to whether I should create indexes on individual foreign keys or should I create one or more composite index(es) on foreign keys? If I need to create a composite index on which columns should I base my index on? Could anybody advise me on this with the reasons for following that particular approach? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
Problem with Oracle 8.0.6 and Cisco Intrusion Detection System (I
Hi! I'm encountering this error when the Cisco Intrusion Detection System (IDS)tries to connect to Oracle database (8.0.6): * ** Fatal OSN connect error 12545, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracle) (ARGS='(DESCR IPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO)) (CONNECT_DATA=(CID=(PRO GRAM=)(HOST=BR-DSPS01)(USER=netrangr VERSION INFORMATION: TNS for Solaris: Version 2.3.2.1.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 2.3.2.1.0 - Pro duction Time: 16-DEC-02 10:58:13 Tracing not turned on. Tns error struct: nr err code: 12206 TNS-12206: Message 12206 not found; No message file for product=NETWORK, fac ility=TNS ns main err code: 12545 TNS-12545: Message 12545 not found; No message file for product=NETWORK, fac ility=TNS ns secondary err code: 12560 nt main err code: 515 TNS-00515: Message 515 not found; No message file for product=NETWORK, facil ity=TNS nt secondary err code: 2 nt OS err code: 0 The tnsnames and listener of Oracle are correctly configured to answer via BEQUEATH, TCP and IPC! They (ORACLE and IDS) are in the same machine, and via shell I can connect to the database (HP OpenView is installed in the machine too, and he connects to the database without problems)! The OS is Solaris 8! Someone knows how I can solve this? Thank you very much! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?Q?Fl=E1vio_Reis?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
update on slow connections
List, OS Novell 4.2 Oracle 7.3.4.5.1 disks. OS and Oracle RAID 1 Data and Index RAID 5 Hardware Dell 6300 with Perc Controller Card. The users were experiencing a doggy_slow_connect_time (technical term) connecting to the database. Of course it was the database that was the problem. Nothing in the error or alert logs that was not normal. Once the connections were established the work was slow but functional. A shutdown of oracle and a reboot of the server solved the problem for a while. After a few days the slowness would build again until a reboot was performed by the network/sysadmin. I viewed the system error log and found an enter where the perc card showed an error to drive 0:0:0 on the days before the reboots. We split the RAID 1 and the users response was good. Hardware support was called and the perc card and drive 0:0:0 replaced and the server is now faster than when installed. The normal daily load now takes 3+ minutes rather than the 5+ minutes before. If you are having connection slow downs be sure to check ALL possible solutions and read all logs for errors, even small ones can point to a potential problem. 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).
RE: strange SQL with TOAD
Hey Ron! My guess is that the Dev has Show ROWID in data grids checked in the TOAD options, probably in Data Grids - Data if your version of TOAD is recent. Take care of them fine VMS boxes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Subject: strange SQL with TOAD List, We use TOAD as one of our database tools and I was viewing some of the SQL statements that were processed and I can't explain the why the statement is the way it is. The developer wrote Select to_char(sysdate,'mm-dd-') from dual; in a VB application. We use the Oracle and MS ODBC drivers to connect to the database. What I saw as the SQL statement: select ROWID,to_char(sysdate,'mm-dd-') from dual where ROWID = :v1 Where did the select ROWID come from? In other statements where the select written is Select table.* ... I see Select table.ROWID, table.*... Again, where does the ROWID come from? Is it a VB,/+ODBC thing that I should not be concerned with? Any links to the info would be helpfull in understanding the query. Thanks, Ron -- 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-directing database connection requests
Hi Is there a way to re-direct incoming database connection requests to another server? I believe Oracle Connection Manager can do this but it requires the database to be running in MTS mode. Is there another way? Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Parallel Replication of Single Table
Hi Larry, I do not know really how you want to parallelize this kind of operations! The final goal is to push the changes in site 1 to site 2 and get the data in both tables in sync. Changes include inserts, updates, and deletes. These changes (dml operations) could be cascaded on the same row which means that the final image of the data in the table is completely dependent on the order and sequence of these dml operations. This is why I think that a single table refresh requires it to be a serial operation. But on the other hand if a single refresh was requested for many tables, then this could be parallelized on the job level not the table level. Does it make sense or am I missing something? Regards, Waleed -Original Message- Sent: Monday, January 13, 2003 7:51 PM To: Multiple recipients of list ORACLE-L Listers, How can one use parallelism when fast refreshing a *single* table? Pulling my hair out on this one. We want to use parallelism when replicating updates/inserts from a single partitioned table between DB's -- partitioned on both DB's. Both are 8.1.7.4 Solaris 8 64 bit. Using fast refresh and primary key method. Have the MV in the target, have the source along with the associated MLOG$. The job queues, parallel parameters, etc are all way up there. We manually pull on occasion when doing restructuring or mass mods and have no problems getting parallelism on each side. But we have to initiate from the target side as a pull to get that. Have set degree 8 on MLOG$, source, and target. Specifically, when I crank up the refresh, with only updates, the update on the target serializes. Info in the docs is a little confusing. Much is made of partitioning a table in the DW guide to allow parallelism of the refresh. But it is unclear if they are talking an MV based on an object in the same DB, or, based on a remote object, or both. In the distributed manual it makes a comment about DML serializing when doing remote operations. Now someone looked into this a while back and opened a TAR to get an explanation -- the analyst said to simply use the parallelism parameter of the DBMS_SNAPSHOT.REFRESH. Well this didn't do it. Then he comes back and says it is possible to replicate a single table using parallelism if you use advanced replication (or maybe we read that somewhere). Guess I'll create a second 8.1.7 DB on my home machine and give that a go. But I'm probably missing something obvious here. So it's back to a simple question -- how can one use parallelism to refresh a single table? We can write our own routines to do this, but I would rather use native capabilities as opposed to re-inventing the wheel. Feeling pretty stupid here. Oh well, time to setup and test advanced replication. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
TSOUG
TriState Oracle Users Group is looking for speakers for the 2003 calendar year. January meeting is 1/28 with the remaining meetings scheduled for the third Tuesday of every month. Even months are scheduled to be geared more towards Developers, Odd months towards DBAs. If anyone is interested in presenting in Amarillo Texas, please contact me off line. Thank you April April Wells President TSOUGOracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
RE: re-directing database connection requests
Really? I don't remember such a requirement. I think it works with or without MTS. -Original Message- Sent: Tuesday, January 14, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Hi Is there a way to re-direct incoming database connection requests to another server? I believe Oracle Connection Manager can do this but it requires the database to be running in MTS mode. Is there another way? Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: perl timeout
Try killing the processes for an Oracle test database.
The goal is to fool the listener into thinking the database is
still up, and creating a server, which should then hang.
It's worked for me in the past, but not always 100%. May
take a few tries.
Jared
David Turner [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/14/2003 10:46 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: perl timeout
Thx I left your book in Missouri:( Now I'm trying to simulate a database
with
hanging connections to test the script. Anyone know how best to simulate
that?
Dave
On Mon, Jan 13, 2003 at 07:43:29PM -0800, Jared Still wrote:
Gee Dave, I know of a book that has scripts that
already do this. ;)
Here's an untested bit of code to demonstrate.
my $dbh;
eval {
local $SIG{ALRM} = sub {
die connection timeout\n;
};
alarm 60;
$dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
}
);
};
# the alarm reset must be outside the eval{}
alarm 0;
I say untested cuz I simplified it a bit after lifting
it from a script.
HTH,
Jared
On Monday 13 January 2003 15:49, David Turner wrote:
Does anyone have some perl code that will return an error if it take
longer
than a certain number of seconds to connect to or return the results
from a
database? I'd like to have some of my queries connect to an alternate
database if there is a problem connecting or returning results within
10
seconds. Any other suggestions are appreciated.
Dave
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Turner
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: USER_TABLESPACES has more rows than DBA_TABLESPACES
Keith Moore wrote: I'm still confused. They are not in DBA_TABLESPACES, only USER_TABLESPACES. Keith Because DBA_TABLESPACES filters them out. Try select name, online$ from sys.ts$ and you'll see them with online$ = 3 (out of memory). - Original Message - From: Stephane Faroult [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 1:34 PM Subject: Re: USER_TABLESPACES has more rows than DBA_TABLESPACES Keith Moore wrote: Has anyone else seen this or can you explain it? I have 7 tablespaces in USER_TABLESPACES that don't exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear from USER_TABLESPACES. They have a status of INVALID. The database is version 8.0.5 (Yeah, I know, we'll be going to 9i real soon now) Keith Regular behaviour. Rows are never deleted from sys.ts$ (on which DBA_TABLESPACES is based). -- 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: re-directing database connection requests
Ben wrote: Hi Is there a way to re-direct incoming database connection requests to another server? I believe Oracle Connection Manager can do this but it requires the database to be running in MTS mode. Is there another way? Thanks, Ben hacker modenetcat ?/hacker mode -- 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: Info about running procs
[EMAIL PROTECTED] wrote: Hi list, I'm looking for a Oracle system view or table where I can see all actual running procedures. Where can I find this info. TIA Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de I think that a join between v$session (where status = 'ACTIVE') and x$kglrd should do it (join SQL_ADDRESS + SQL_HASH_VALUE to kglrdhdl + kglnadhv). You need to be SYS of course, so perhaps you'll want to create a view. -- 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: Problem with Oracle 8.0.6 and Cisco Intrusion Detection Syste
What's the user account you have your IDS install under? I would check your Oracle environment variables first for that account. -Original Message- Sent: Tuesday, January 14, 2003 2:19 PM To: Multiple recipients of list ORACLE-L (I Hi! I'm encountering this error when the Cisco Intrusion Detection System (IDS)tries to connect to Oracle database (8.0.6): * ** Fatal OSN connect error 12545, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracle) (ARGS='(DESCR IPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO)) (CONNECT_DATA=(CID=(PRO GRAM=)(HOST=BR-DSPS01)(USER=netrangr VERSION INFORMATION: TNS for Solaris: Version 2.3.2.1.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 2.3.2.1.0 - Pro duction Time: 16-DEC-02 10:58:13 Tracing not turned on. Tns error struct: nr err code: 12206 TNS-12206: Message 12206 not found; No message file for product=NETWORK, fac ility=TNS ns main err code: 12545 TNS-12545: Message 12545 not found; No message file for product=NETWORK, fac ility=TNS ns secondary err code: 12560 nt main err code: 515 TNS-00515: Message 515 not found; No message file for product=NETWORK, facil ity=TNS nt secondary err code: 2 nt OS err code: 0 The tnsnames and listener of Oracle are correctly configured to answer via BEQUEATH, TCP and IPC! They (ORACLE and IDS) are in the same machine, and via shell I can connect to the database (HP OpenView is installed in the machine too, and he connects to the database without problems)! The OS is Solaris 8! Someone knows how I can solve this? Thank you very much! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?Q?Fl=E1vio_Reis?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: USER_TABLESPACES has more rows than DBA_TABLESPACES
Some of the system views have a lot going on under the covers, and sometimes don't act the way you would expect. Compare ALL_TABLES and DBA_TABLES sometime. They work pretty much the same from the command line for a user that has DBA privs. In a stored procedure however, ALL_TABLES suddenly becomes very restrictive due to this bit of code in the view: or /* user has system privileges */ exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) ) ) Jared Hately, Mike (NESL-IT) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2003 08:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: USER_TABLESPACES has more rows than DBA_TABLESPACES Keith, yes, it's regular behaviour that the TS$ entry remains but the discrepancy between those 2 views is a bug which is fixed in 8.0.6. Basically, DBA_TABLESPACES excludes 'INVALID' tablespaces but USER_TABLESPACES doesn't. There are duplicates for the bug. The numbers are 284887, 553723 and 546913. regards, Mike Hately -Original Message- Sent: 14 January 2003 15:34 To: Multiple recipients of list ORACLE-L I'm still confused. They are not in DBA_TABLESPACES, only USER_TABLESPACES. Keith - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Monday, January 13, 2003 1:34 PM Keith Moore wrote: Has anyone else seen this or can you explain it? I have 7 tablespaces in USER_TABLESPACES that don't exist in DBA_TABLESPACES. These have been dropped, but somehow did not disappear from USER_TABLESPACES. They have a status of INVALID. The database is version 8.0.5 (Yeah, I know, we'll be going to 9i real soon now) Keith Regular behaviour. Rows are never deleted from sys.ts$ (on which DBA_TABLESPACES is based). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] mailto:[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] mailto:[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: DATA MAPPING ISSUE
Title: RE: DATA MAPPING ISSUE Guys, Can anyone recommend a reasonably priced good data mapping tool that helps map legacy data to RDBMS's - Oracle and SQL Server and normalized to denormalized or normalized to different normalized schemas? Thanks, Paula -Original Message- From: Richard Ji [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 3:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: re-directing database connection requests Really? I don't remember such a requirement. I think it works with or without MTS. -Original Message- Sent: Tuesday, January 14, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Hi Is there a way to re-direct incoming database connection requests to another server? I believe Oracle Connection Manager can do this but it requires the database to be running in MTS mode. Is there another way? Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
XML functonality in Oracle XML Developer Kit
Good afternoon all Im interested in using xml to hide sql querries on web applications (and storing xml querries) in the db A cursorary search http://www.dbspecialists.com/presentations/xml_and_oracle.html Shows me that in fact XML can work well however Im interested in feedback from anyone who has installed or used the The XML Developer Kit and if so how it worked how much trouble was it to set up... In other words does it actually work and is it being used Any worthwhile docs/links would be appreciated 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: slowish query causing problems...
Denham, Some others have already made good suggestions - including Thomas' reply which I would like to extend upon... Thomas suggested: SELECT DISTINCT (1) FROM fwepcode1 WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP = '2') OR not exists(select 1 FROM valuelist WHERE listname = 'STATUS' AND MAXVALUE = 'A' AND VALUE='INPRG' ) I would change it slightly to: select 1 from fwepcode1 where ((wottype = 'TST' and func = 'CONEPRF' and exp = '2') or not exists (select 1 from valuelist where listname = 'STATUS' and maxvalue = 'A' and value = 'INPRG')) and rownum = 1; Using and rownum = 1 will cause it to use COUNT STOPKEY instead of SORT AGGREGATE. The sort is an unnecessarily large piece of work to test for existence. Perhaps this will also cause the optimisor to use an index - NDX9 seems the ideal candidate. If it still doesn't use the index then have you analyzed the table? Perhaps even try analyze table fwepcode1 compute statistics for table for all indexes for all indexed columns If the indexes are all analyzed then try the hint /*+ index( fewpcode1 fwepcode1_ndx9 ) */ . Let me know if it still refuses to use the index - although if you reach this point then perhaps a full table scan is the best approach, and the COUNT STOPKEY may be able to stop the FTS very quickly as soon as it finds 1 matching row anyway. Also, I know you mentioned that the indexes are defined by somebody else but some of them look redundant - particularly NDX2 and NDX4 and maybe NDX9 if the columns are rearranged. Are a lot of updates/inserts/deletes performed on fwepcode1? If so you might get some gain by removing some indexes. Regards, Mark. Denham Eva [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] za cc: Sent by: Subject: RE: slowish query causing problems... [EMAIL PROTECTED] om 15/01/2003 01:49 Please respond to ORACLE-L Hello, Thanks for the replies Here is the description of the table and the indexes, pls remember I have removed and tested each index seperately, still insisted on a Full search. 9 indexes is not my idea of a perfect situation these are created by the developers ( another company) so politics plays a big roll here. CREATE TABLE FWEPCODE1 ( RECORDID INTEGER NOT NULL, FUNC VARCHAR2 (20), WOTYPEVARCHAR2 (20), EXP VARCHAR2 (20), PIK VARCHAR2 (20), FUNCDESC VARCHAR2 (80), EXPDESC VARCHAR2 (80), PIKDESC VARCHAR2 (80), EX1 VARCHAR2 (1), EX2 VARCHAR2 (10), EX3 VARCHAR2 (10), EX4 VARCHAR2 (10), EX5 VARCHAR2 (10), EX6 VARCHAR2 (10), EX7 VARCHAR2 (10), EX8 VARCHAR2 (10), EX9 VARCHAR2 (10), EX10 VARCHAR2 (10) ) ; FWEPCODE1_NDX1 ON FWEPCODE1(FUNC, WOTYPE, EXP, PIK); FWEPCODE1_NDX2 ON FWEPCODE1(FUNC); FWEPCODE1_NDX3 ON FWEPCODE1(EXP); FWEPCODE1_NDX4 ON FWEPCODE1(FUNC, WOTYPE); FWEPCODE1_NDX5 ON FWEPCODE1(FUNC, EX2); FWEPCODE1_NDX6 ON FWEPCODE1(EXPDESC); FWEPCODE1_NDX7 ON FWEPCODE1(FUNC, WOTYPE, PIK); FWEPCODE1_NDX8 ON FWEPCODE1(RECORDID); FWEPCODE1_NDX9 ON FWEPCODE1(WOTYPE, FUNC, EXP); I have added a CSV file as an attachment as requested
How to categorize Oracle module complexity
Hi Folks ! We are attempting to categorize the technical complexity of ourOracle modules (Forms, Reports, Pro*C etc.) so that any future effort for migration/across the board changes can be estimated can be a little more accurate than a guestimate. Have you come across any methods of how this can be achieved ex. number of items, number of canvases, lies of code etc.? Any pointers would be helpful and appreciated. Thanx deepak E-Mail : [EMAIL PROTECTED]
Run obsolete Oracle 7 scripts against Oracle 8i database?
Hi Guys, In OEM reports, under the ELEMENTS tab, you can specify Table from SELECT statement for database versions. ie. the minimum version of the database for which a script will run. So you can have scripts that used to work in Oracle 7 and still have it run in Oracle 8i. ie. Even though the scripts now uses obsolete views or columns, they will still work against an Oracle 8i database. So how do we go about doing a similar thing in SQL*Plus? ie. get obsolete Oracle 7 scripts to run against Oracle 8i database in SQL*Plus? I'd like to test out my scripts in SQL*Plus before modifying the OEM reports. TIA, Leng. Email: [EMAIL PROTECTED] Phone: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM 9.2: breaks in reports
Hello again,
Does anyone know how to add breaks in a SQL output for OEM reports? ie. the SQL for
table from SELECT statement
What I need is something similar to this in SQL*Plus:
break on owner on table_name
Or even just a blank line between each break. Is this possible? How do
I go about doing it? The query I've got is something along this line:
select c.owner, c.table_name, c.constraint_name,
c2.column_name,c.constraint_type, c.search_condition
from dba_constraints c, dba_cons_columns c2,dba_tab_columns t
where c.owner not in ('SYS',
'SYSTEM','OUTLN','DBSNMP','OPS$ORACLE','ORDSYS','PERFSTAT')
and c.table_name = c2.table_name
and c.constraint_name = c2.constraint_name
and c.owner = c2.owner
and t.owner = c2.owner
and t.table_name = c2.table_name
and t.column_name = c2.column_name
-- check constraints
and ( (t.nullable != 'N' and c.constraint_type = 'C')
-- non-check constraints
or (c.constraint_type != 'C')
)
and c.owner = t.owner
and c.table_name = t.table_name
order by 1,2,3,4,c2.position
/
TIA,
Leng.
--
Leng Kaing
Email: [EMAIL PROTECTED]
Phone: +61-417-371-348
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kaing, Leng
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Subject:RE: Export/Import Error and Validation !!!
Tracy, I hit the same bug when doing reorg using export/import (Solaris 2.6, Oracle 8.1.7.4) for some tables in our baan erp database. The tables had unique constraints that were failing after the import. Found out that import has changed the values in some unique index columns. The export was done using 10MB buffer size. I didn't notice this issue with 2MB or less buffer size. Oracle support filed a Bug 2594794 EXPORT GENERATES ORA-1 ERROR WHEN BUFFER SIZE IS LARGE for it. Regards, Manmohan _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anubha Jalsingh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Win2k/8.1.7/SQL Question
My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: perl timeout
Cool, thx, Dave
On Tue, Jan 14, 2003 at 12:16:27PM -0800, [EMAIL PROTECTED] wrote:
Try killing the processes for an Oracle test database.
The goal is to fool the listener into thinking the database is
still up, and creating a server, which should then hang.
It's worked for me in the past, but not always 100%. May
take a few tries.
Jared
David Turner [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/14/2003 10:46 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: perl timeout
Thx I left your book in Missouri:( Now I'm trying to simulate a database
with
hanging connections to test the script. Anyone know how best to simulate
that?
Dave
On Mon, Jan 13, 2003 at 07:43:29PM -0800, Jared Still wrote:
Gee Dave, I know of a book that has scripts that
already do this. ;)
Here's an untested bit of code to demonstrate.
my $dbh;
eval {
local $SIG{ALRM} = sub {
die connection timeout\n;
};
alarm 60;
$dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
}
);
};
# the alarm reset must be outside the eval{}
alarm 0;
I say untested cuz I simplified it a bit after lifting
it from a script.
HTH,
Jared
On Monday 13 January 2003 15:49, David Turner wrote:
Does anyone have some perl code that will return an error if it take
longer
than a certain number of seconds to connect to or return the results
from a
database? I'd like to have some of my queries connect to an alternate
database if there is a problem connecting or returning results within
10
seconds. Any other suggestions are appreciated.
Dave
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Turner
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: David Turner
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 9i release 3 coming?
9i release 2 is the terminal release. There will not be any release 3 from Oracle. The next major release will be 10i. Got this info from Tom Kyte of Oracle Corp. Shaibal Talukder Sr.Oracle DBA Database Engineering Discover Financial Services From: Jared Still [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Oracle 9i release 3 coming? Date: Mon, 13 Jan 2003 19:39:04 -0800 Since 9iR2 is 9.2.0, I don't think that 9.0.3 will be Release 3. Jared On Monday 13 January 2003 18:23, Boivin, Patrice J wrote: Oracle started posting items with the 9.0.3. number, does this imply they plan to release a 9i Release 3? 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] Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shaibal Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Run obsolete Oracle 7 scripts against Oracle 8i database?
As long as you aren't using any x$ views or sys.$ tables, they should work as is, with a minimum of exceptions. There were some v$ views that changed, v$log_history for instance. There were also some cases of '#' being added to some column names, but I can't recall which views those were. Jared Kaing, Leng [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2003 02:29 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Run obsolete Oracle 7 scripts against Oracle 8i database? Hi Guys, In OEM reports, under the ELEMENTS tab, you can specify Table from SELECT statement for database versions. ie. the minimum version of the database for which a script will run. So you can have scripts that used to work in Oracle 7 and still have it run in Oracle 8i. ie. Even though the scripts now uses obsolete views or columns, they will still work against an Oracle 8i database. So how do we go about doing a similar thing in SQL*Plus? ie. get obsolete Oracle 7 scripts to run against Oracle 8i database in SQL*Plus? I'd like to test out my scripts in SQL*Plus before modifying the OEM reports. TIA, Leng. Email: [EMAIL PROTECTED] Phone: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Parallel Replication of Single Table
I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. We are able to achieve parallelism (8.1.7) with CTAS or inserts across DB's as long as we initiate the SQL from the *target* side. In this case you will see the parallelism on both the source and target side. Use it fairly often to move massive amounts of data. Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Parallel Replication of Single Table
Waleed, Thanks for chiming in. Regarding refreshing multiple tables in parallel, well yes, that could be done on a group level. Instead, though, they choose to have the scheduling tool kick off multiple single table refreshes. They start many at the same time, so they do in essence get parallelism of multiple tables at one time. And this way they get paged with a specific should something fail. Regarding the other comments, the replication logic seems to handle the dependency you are talking about. There really isn't any order per se, so the same issues you raise would apply to serial processing as well, right? All those rows are in the MLOG$ table with a 1/1/4000 date -- in what order did they occur? Obviously an insert has to occur before the update to occur (with xceptions -- e.g. update, delete, then insert same PK row, etc). And we don't care how many updates have occurred since we are only interested in pushing the current image of the row. Now, what about deletes? Let's say you see deletes and inserts in the MLOG$ table. Well, they will all have the 1/1/4000 date prior to kicking off. How do we know which occurred first -- did we delete an existing row and then re-insert? Or did we insert and then delete? Well, that's handled by joining to the core table itself -- if the row exists, then you are going to push it, the insert would have happened after the delete. If it doesn't exist, then you know the delete happened after the insert. So the deletes are processed first using the existence in the base table test. With tracing turned on you can see how the replication logic handles this: Step 1 -- get the != 'I' where it's not in the existing table -- e.g. we know the last action was a delete, not an insert or update since the row no longer exists: SELECT /*+ remote_mapped(link) */ DISTINCT LOG$.OBJECT_ID FROM (SELECT MLOG$.OBJECT_ID FROM SCHEMA.MLOG$_MY_PTEST@link MLOG$ WHERE SNAPTIME$$ :1 AND (DMLTYPE$$ != 'I')) LOG$ WHERE (LOG$.OBJECT_ID) NOT IN (SELECT MAS_TAB$.OBJECT_ID FROM SCHEMA.MY_PTEST@LINK MAS_TAB$ WHERE LOG$.OBJECT_ID = MAS_TAB$.OBJECT_ID) Step 2 -- process those deletes: DELETE FROM SCHEMA.MV_MY_PTEST SNAP$ WHERE OBJECT_ID = :1 Step 3 -- Look for the != 'D' where it *exists* in the table. This will be the I's and U's. And because you handled the true deletes (as in no longer exists) in the prior step, then you know these go: SELECT /*+ remote_mapped(link) */ CURRENT$.OWNER, CURRENT$.OBJECT_NAME,CURRENT$.SUBOBJECT_NAME,CURRENT$.OBJECT_ID, CURRENT$.DATA_OBJECT_ID,CURRENT$.OBJECT_TYPE,CURRENT$.CREATED, CURRENT$.LAST_DDL_TIME,CURRENT$.TIMESTAMP,CURRENT$.STATUS, CURRENT$.TEMPORARY,CURRENT$.GENERATED,CURRENT$.SECONDARY FROM (SELECT /*+ */ MY_PTEST.OWNER OWNER,MY_PTEST.OBJECT_NAME OBJECT_NAME,MY_PTEST.SUBOBJECT_NAME SUBOBJECT_NAME, MY_PTEST.OBJECT_ID OBJECT_ID,MY_PTEST.DATA_OBJECT_ID DATA_OBJECT_ID,MY_PTEST.OBJECT_TYPE OBJECT_TYPE, MY_PTEST.CREATED CREATED,MY_PTEST.LAST_DDL_TIME LAST_DDL_TIME, MY_PTEST.TIMESTAMP TIMESTAMP,MY_PTEST.STATUS STATUS, MY_PTEST.TEMPORARY TEMPORARY,MY_PTEST.GENERATED GENERATED, MY_PTEST.SECONDARY SECONDARY FROM SCHEMA.MY_PTEST@LINK MY_PTEST) CURRENT$, (SELECT DISTINCT MLOG$.OBJECT_ID FROM SCHEMA.MLOG$_MY_PTEST@LINK MLOG$ WHERE SNAPTIME$$ :1 AND (DMLTYPE$$ != 'D')) LOG$ WHERE CURRENT$.OBJECT_ID = LOG$.OBJECT_ID Steps 4 and 5 (intermixed) -- process the inserts/updates, if you update before you insert, no big deal since you want the current image and the insert will handle it and no row to update will not fail. And if you try to insert before you update, no big deal since you will still be pushing the current image. So this goes: UPDATE SCHEMA.MV_MY_PTEST SET OWNER = :1,OBJECT_NAME = :2, SUBOBJECT_NAME = :3,OBJECT_ID = :4,DATA_OBJECT_ID= :5,OBJECT_TYPE = :6,CREATED = :7,LAST_DDL_TIME = :8,TIMESTAMP = :9,STATUS = :10, TEMPORARY = :11,GENERATED = :12,SECONDARY = :13 WHERE OBJECT_ID = :4 INSERT INTO SCHEMA.MV_MY_PTEST (OWNER,OBJECT_NAME,SUBOBJECT_NAME, OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME, TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13) Now I may have missed a few things there, but it sounds like it handles the dependencies. And what code and action may happen that doesn't manifest itself as SQL -- you see analyzes computing number of distinct, max, min, etc. But you don't see clustering and density factor calculations in terms of SQL. I need to do some more work on this, and the Advanced Replication guide does talk about dependencies and their impact on whether or not some things can parallelized, but I've got to dig a bit more into that. But still focusing on basic replication. Now the fun part is doing the replication in the *same* DB. I could get parallelism on the SELECT's, or at least the trace file said so, but they weren't observed, nor were the delete/updates/inserts observed working in parallel. So I still need to do
HP-UX 11.0 mount parameter mincache
We have HP-UX 11.0 and are having IO problems. Oracle and HP say to set a mount parameter mincache=direct. We currently have it set to mincache=tmpcache. But when we set it to direct the db slows down to the point it's unusable. HP said to increase the db_block_buffers, so we increased then from 30k to 130k (8k block). That's 1GB. The db was a little more usable but still sucked. So we set the buffers back to 30k and reset mincache back to tmpcache. Now the db is again usable, but we there's still something wrong with IO. sar reports WIO at +80%. ORacle says that should be close to 0%. We are using HP's LVM (resold veritas) and cooked files. Any ideas? thanks __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: tony ynot INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Info about running procs
Take a look at Steve Adam's http://www.ixora.com.au/scripts/sql/executing_packages.sql This script lists the packages (and other stored code objects) that are currently being executed, and the SIDs of the executing sessions. It is listed as for 8.0 / 8.1 Remember to check out the prerequisites at http://www.ixora.com.au/scripts/prereq.htm HTH, Bruce Reardon -Original Message- Sent: Wednesday, 15 January 2003 7:29 AM [EMAIL PROTECTED] wrote: Hi list, I'm looking for a Oracle system view or table where I can see all actual running procedures. Where can I find this info. TIA Volker Schoen E-Mail: [EMAIL PROTECTED] http://www.inplan.de I think that a join between v$session (where status = 'ACTIVE') and x$kglrd should do it (join SQL_ADDRESS + SQL_HASH_VALUE to kglrdhdl + kglnadhv). You need to be SYS of course, so perhaps you'll want to create a view. Author: Stephane Faroult -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Win2k/8.1.7/SQL Question
Title: RE: Win2k/8.1.7/SQL Question Well that looks like ANSI compatible SQL that should run under Oracle9i. Take a look and the 9i docs to develop a strategy for the retrofit. This is so weird for me... having to unlearn Oracle syntax in order to write ANSI SQL. Sigh... -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: Win2k/8.1.7/SQL Question My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike
RE: Parallel Replication of Single Table
I remember too being able to achieve parallelism (8.1.7) with CTAS or inserts across DB's too but I'm not able to achieve this now. I tried it from the source side and the target side without success. It's always serial. Curious how you are able to test this. Thanks Waleed -Original Message- Sent: Tuesday, January 14, 2003 7:59 PM To: Multiple recipients of list ORACLE-L I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. We are able to achieve parallelism (8.1.7) with CTAS or inserts across DB's as long as we initiate the SQL from the *target* side. In this case you will see the parallelism on both the source and target side. Use it fairly often to move massive amounts of data. Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Run obsolete Oracle 7 scripts against Oracle 8i database?
some of the dba_ views changed as well. Can't remember them offhand, I know when we were doing the 101 book, we flagged new or different or obsolete columns when we discussed those views. --- [EMAIL PROTECTED] wrote: As long as you aren't using any x$ views or sys.$ tables, they should work as is, with a minimum of exceptions. There were some v$ views that changed, v$log_history for instance. There were also some cases of '#' being added to some column names, but I can't recall which views those were. Jared Kaing, Leng [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/14/2003 02:29 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Run obsolete Oracle 7 scripts against Oracle 8i database? Hi Guys, In OEM reports, under the ELEMENTS tab, you can specify Table from SELECT statement for database versions. ie. the minimum version of the database for which a script will run. So you can have scripts that used to work in Oracle 7 and still have it run in Oracle 8i. ie. Even though the scripts now uses obsolete views or columns, they will still work against an Oracle 8i database. So how do we go about doing a similar thing in SQL*Plus? ie. get obsolete Oracle 7 scripts to run against Oracle 8i database in SQL*Plus? I'd like to test out my scripts in SQL*Plus before modifying the OEM reports. TIA, Leng. Email: [EMAIL PROTECTED] Phone: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Parallel Replication of Single Table
Waleed, Initiated from the target side. Parallel DML is enabled with an alter session. And you know it's using parallelism by watching the sessions spawned off on each side, the work they are doing, and the PQ stats. Another member of the list I work with (a darn genius I tell you, with a mind that never forgets anything, never forgetting any nuance or issue ;-) ) spent a little time this afternoon ripping 230 million rows from a large dimension across the db's. That was the workaround because the standard replication as is just couldn't handle the recent increase in the volume of changes. So normal replication couldn't keep up. And probably right now he is switching the objects, renaming and handling the grants -- our only occasional window before nightly stuff kicks in. So do a full copy from the staging DB. We (or maybe just I, though I'm sure the other person would have preferred to be doing other things) want to avoid having to do that -- we would rather keep up with normal replication. And we normally do, just a little more changes now and for the next 6 months or so. And then the feeds will get things up to normal and the volume of changes, at least with regard to updates, will decrease significantly. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Khedr, Waleed Sent: Tuesday, January 14, 2003 8:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Parallel Replication of Single Table I remember too being able to achieve parallelism (8.1.7) with CTAS or inserts across DB's too but I'm not able to achieve this now. I tried it from the source side and the target side without success. It's always serial. Curious how you are able to test this. Thanks Waleed -Original Message- Sent: Tuesday, January 14, 2003 7:59 PM To: Multiple recipients of list ORACLE-L I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. We are able to achieve parallelism (8.1.7) with CTAS or inserts across DB's as long as we initiate the SQL from the *target* side. In this case you will see the parallelism on both the source and target side. Use it fairly often to move massive amounts of data. Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: exp from 9.2.1 to 9.0.1
I intend to export my user in database 9.2.1 into my notebook which is running at 9.0.1 anyone successfull import ? Did i need to xcopy the catexp file from 9.2.1 into my notebook and execute it ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Raymond Lee Meng Hong INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: HP-UX 11.0 mount parameter mincache
Have you tried mincache=direct,convosync=direct options? Here is a link to an article that might be of some help... http://www.interex.org/pubcontent/enterprise/may00/08sysadx.html - Kirti -Original Message- Sent: Tuesday, January 14, 2003 7:29 PM To: Multiple recipients of list ORACLE-L We have HP-UX 11.0 and are having IO problems. Oracle and HP say to set a mount parameter mincache=direct. We currently have it set to mincache=tmpcache. But when we set it to direct the db slows down to the point it's unusable. HP said to increase the db_block_buffers, so we increased then from 30k to 130k (8k block). That's 1GB. The db was a little more usable but still sucked. So we set the buffers back to 30k and reset mincache back to tmpcache. Now the db is again usable, but we there's still something wrong with IO. sar reports WIO at +80%. ORacle says that should be close to 0%. We are using HP's LVM (resold veritas) and cooked files. Any ideas? thanks __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: tony ynot INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Date data type
Hi Listers, Does the date data type is not consistence on oracleDB ? I have the query below : Create table test (dt date, name varchar230)); SQL Select * from test where dt between '01-DEC-02' and '31-DEC-02' - it returns 22 rows selected SQL Select * from test where to_char(dt,'mmrr')='1202' - it returns 26 rows selected Why the last query return more rows selected than first query ? All comment would be appreciated. TIA, Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Parallel Replication of Single Table
I knew I ran a parallel CTAS some time ago but was not successful doing this today. And here is why (you will like this): From 8.1.7 doc: DML statements and CREATE TABLE ... AS SELECT statements that reference remote objects can run in parallel. However, the remote object must really be on a remote database. The reference cannot loop back to an object on the local database (for example, by way of a synonym on the remote database pointing back to an object on the local database). I was testing using a database link pointing to the same database. Regards, Waleed -Original Message- Sent: Tuesday, January 14, 2003 10:04 PM To: Multiple recipients of list ORACLE-L Waleed, Initiated from the target side. Parallel DML is enabled with an alter session. And you know it's using parallelism by watching the sessions spawned off on each side, the work they are doing, and the PQ stats. Another member of the list I work with (a darn genius I tell you, with a mind that never forgets anything, never forgetting any nuance or issue ;-) ) spent a little time this afternoon ripping 230 million rows from a large dimension across the db's. That was the workaround because the standard replication as is just couldn't handle the recent increase in the volume of changes. So normal replication couldn't keep up. And probably right now he is switching the objects, renaming and handling the grants -- our only occasional window before nightly stuff kicks in. So do a full copy from the staging DB. We (or maybe just I, though I'm sure the other person would have preferred to be doing other things) want to avoid having to do that -- we would rather keep up with normal replication. And we normally do, just a little more changes now and for the next 6 months or so. And then the feeds will get things up to normal and the volume of changes, at least with regard to updates, will decrease significantly. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Khedr, Waleed Sent: Tuesday, January 14, 2003 8:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Parallel Replication of Single Table I remember too being able to achieve parallelism (8.1.7) with CTAS or inserts across DB's too but I'm not able to achieve this now. I tried it from the source side and the target side without success. It's always serial. Curious how you are able to test this. Thanks Waleed -Original Message- Sent: Tuesday, January 14, 2003 7:59 PM To: Multiple recipients of list ORACLE-L I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. We are able to achieve parallelism (8.1.7) with CTAS or inserts across DB's as long as we initiate the SQL from the *target* side. In this case you will see the parallelism on both the source and target side. Use it fairly often to move massive amounts of data. Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself
RE: Date data type
Your between is the same as: where dt = '01-DEC-02' and dt = '31-DEC-02' and since '31-DEC-02' is '31-DEC-02 00:00:00', you are excluding records after '31-DEC-02 00:00:00'. Hence less records are returned. -Original Message- Sent: Tuesday, January 14, 2003 11:49 PM To: Multiple recipients of list ORACLE-L Hi Listers, Does the date data type is not consistence on oracleDB ? I have the query below : Create table test (dt date, name varchar230)); SQL Select * from test where dt between '01-DEC-02' and '31-DEC-02' - it returns 22 rows selected SQL Select * from test where to_char(dt,'mmrr')='1202' - it returns 26 rows selected Why the last query return more rows selected than first query ? All comment would be appreciated. TIA, Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
HELP
HELP Stefan Jakobsson Programmerare Arel-Data Tele: 08-470 24 00 Fax: 08-470 24 24 Stefan Jakobsson (E-mail).vcf Stefan Jakobsson (E-mail).vcf Description: Binary data
Woops
Sorry, sent that to the wrong address ... :) Stefan Jakobsson Programmerare Arel-Data Tele: 08-470 24 00 Fax: 08-470 24 24 -Ursprungligt meddelande- Från: Stefan Jakobsson [mailto:[EMAIL PROTECTED]] Skickat: den 15 januari 2003 07:44 Till: Multiple recipients of list ORACLE-L Ämne: HELP HELP Stefan Jakobsson Programmerare Arel-Data Tele: 08-470 24 00 Fax: 08-470 24 24 Stefan Jakobsson (E-mail).vcf Stefan Jakobsson (E-mail).vcf Description: Binary data
