RE: Removing duplicate rows from a table !
Assuming that we have duplicate rows ( NOT duplicate keys ), you may try this. I have borrowed this from a friend of mine. We have used it successfully. Each of the columns in the table are included as predicates to confirm that we are deleting only the duplicate rows. delete from noderelatedreplicationqueue a where rowid (select max(rowid) from noderelatedreplicationqueue b where a.NODENBR = b.NODENBR and a.LASTUPDATETS = b.LASTUPDATETS and a.NODEABOVENBR = b.NODEABOVENBR and a.TRANSACTIONTYPEIND = b.TRANSACTIONTYPEIND and a.NODETYPECD = b.NODETYPECD and a.NODEABOVETYPECD = b.NODEABOVETYPECD and a.DISTANCENBR = b.DISTANCENBR and a.MARKETCD = b.MARKETCD and a.TOPLEVELIND = b.TOPLEVELIND and a.LOGICALDELETEIND = b.LOGICALDELETEIND and a.LASTUPDATENBR = b.LASTUPDATENBR ) Hope this helps. Govind -Original Message- Rama, Shreekantha (K.) Sent: Thursday, October 16, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Hi, I am trying to remove the duplicate rows from a table with the column data.. I cannot use PK as it's just a sequence number... I could find all the duplicate rows by grouping the column. but how can i delete only the duplicate ones and retain the original data.. Any help is gr8 ! ! ! Warm Regards Shreekanth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (K.) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Huge optimization costs with 9.2
Yes. You have to bounce the database for this to take effect. -Original Message- Joan Hsieh Sent: Thursday, October 02, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Govind, I will test it out today and post the updates, I should set optimize_feature_enable back to 9.2.0 before I test this out, right? JOan [EMAIL PROTECTED] wrote: Can you try to generate the query plan with these settings? These are the 9i CBO Hidden parameters to generate 8.1.7 like query plans. alter session set _UNNEST_SUBQUERY = FALSE; alter session set _ORDERED_NESTED_LOOP = FALSE; alter session set _ALWAYS_SEMI_JOIN = off; explain plan for query; -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:10 PM To: Multiple recipients of list ORACLE-L this is the explain plan for the 9i, sorry it is long sql. Rows Row Source Operation --- --- 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 us) 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 us) 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 time=1452575 us) 3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 time=971571531 us) 3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) 3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) 3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 time=651677 us) 3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 w=0 time=503807 us)(object id 51394) 3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 w=0 time=46636 us) 3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=15519 us)(object id 51375) 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 us) 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=903383 us) 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=552179 us) 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 time=26292679 us) 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=26817559 us)(object id 51357) 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=228394 us) 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=216366 us) 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 time=52143346 us) 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 r=0 w=0 time=353694 us) 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=115423379 us)(object id 51357) 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=289225 us) 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 us) 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 time=0 us)(object id 51357) 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 us) 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 w=0 time=376132 us) 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 time=292929 us)(object id 51360) 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 w=0 time=4703 us) 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=1925 us)(object id 51375) 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018 us) 0 HASH JOIN (cr=3848 r=0 w=0 time=417296 us) 0 NESTED
RE: Huge optimization costs with 9.2
Joan, Can you post the query with the plan in 8.1.7 and 9.2; We ran into certain types of queries that had totally different execution plans and got work-arounds. Thanks, Govind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Joan Kirtikumar Deshpande wrote: Were tables/indexes anlayzed after the upgrade? - Kirti --- Jeff Landers [EMAIL PROTECTED] wrote: Hello All Version OS: Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. Problem: We've captured the sql text and optimization plans for critical sql prior to upgrading to 9.2. After the upgrade we have noticed that the cost associated with every sql statement is now HUGE compared to its 9.0.1.4 counterpart. Per the statistics being captured via traces, these statement are noticeably slower per execution. Anyone experiencing/experienced the same problem with 9.2? Thank you in advance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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 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: Huge optimization costs with 9.2
Joan, Can you post the query in question? Thanks, GOvind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Malden, They all same. I didn't change any parameters after upgrade. The difference in the plans are one used all hash join vs nested loop to join tables. The histograms are all same. db_file_multiblock_read_count is 8, sort_area_size is 1mb, hash_area_size is 40096. optimize_index_caching and optimizer_index_cost is 0/100 all same as 8i. The interesting part is after I changed optimize_feature_enable to 8.1.7. They produced the same explain plan. Joan Mladen Gogala wrote: Joan, what is the difference in the plans? What specific feature made the difference? Are the values of optimizer_index_cost_adj and optimizer_index_caching same on both versions? How about histograms? What is with db_file_multiblock_read_count,sort_area_size and hash_area_size? Is everything same as in 8i? May be setting of those parameters can be tweaked to your benefit? On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote: Kirti, I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade, performance is good. After upgrade, one query run time from 2 min to 12 hours. Of course, I re-analyzed all tables and indexes. The explain plan changed from hash join to nested-loop. All the parameters are same. So I have to put optimized_feature_enable=8.1.7 to make run normal as usual. I hate to disable the new feature, but no choose. Joan Kirtikumar Deshpande wrote: Were tables/indexes anlayzed after the upgrade? - Kirti --- Jeff Landers [EMAIL PROTECTED] wrote: Hello All Version OS: Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3. Problem: We've captured the sql text and optimization plans for critical sql prior to upgrading to 9.2. After the upgrade we have noticed that the cost associated with every sql statement is now HUGE compared to its 9.0.1.4 counterpart. Per the statistics being captured via traces, these statement are noticeably slower per execution. Anyone experiencing/experienced the same problem with 9.2? Thank you in advance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Note: 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. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor 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 the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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
RE: Huge optimization costs with 9.2
Can you try to generate the query plan with these settings? These are the 9i CBO Hidden parameters to generate 8.1.7 like query plans. alter session set _UNNEST_SUBQUERY = FALSE; alter session set _ORDERED_NESTED_LOOP = FALSE; alter session set _ALWAYS_SEMI_JOIN = off; explain plan for query; -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:10 PM To: Multiple recipients of list ORACLE-L this is the explain plan for the 9i, sorry it is long sql. Rows Row Source Operation --- --- 1 LOAD AS SELECT (cr=14674449 r=2275 w=1831 time=787991194 us) 42647 NESTED LOOPS OUTER (cr=14673991 r=2273 w=0 time=5081221102 us) 42647NESTED LOOPS OUTER (cr=6448712 r=2187 w=0 time=1730062983 us) 42647 NESTED LOOPS OUTER (cr=3262940 r=1395 w=0 time=1075194825 us) 42647 NESTED LOOPS OUTER (cr=2917318 r=1375 w=0 time=973480801 us) 42647 TABLE ACCESS FULL PR_IDENTITY (cr=1207 r=1205 w=0 time=1452575 us) 3766 VIEW PUSHED PREDICATE (cr=2916111 r=170 w=0 time=971571531 us) 3766HASH JOIN (cr=2916111 r=170 w=0 time=971416648 us) 3766 NESTED LOOPS (cr=50183 r=25 w=0 time=759193 us) 3766 TABLE ACCESS BY INDEX ROWID PR_MED (cr=46415 r=25 w=0 time=651677 us) 3766 INDEX UNIQUE SCAN PR_MED_UNIQUE_TRUNK (cr=42649 r=24 w=0 time=503807 us)(object id 51394) 3766 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=3768 r=0 w=0 time=46636 us) 3766 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=15519 us)(object id 51375) 14182756 VIEW (cr=2865928 r=145 w=0 time=942647916 us) 14182756 UNION-ALL (cr=2865928 r=145 w=0 time=931367819 us) 14182756 HASH JOIN (cr=598795 r=145 w=0 time=243380379 us) 14182756NESTED LOOPS (cr=587497 r=145 w=0 time=195899818 us) 14182756 VIEW (cr=583730 r=145 w=0 time=124765499 us) 14182756 UNION-ALL (cr=583730 r=145 w=0 time=112440519 us) 0 HASH JOIN (cr=15064 r=0 w=0 time=1416201 us) 0TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=903383 us) 0TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 14182756 HASH JOIN (cr=568666 r=145 w=0 time=86101027 us) 105448TABLE ACCESS FULL PR_DEPARTMENTS (cr=15064 r=0 w=0 time=552179 us) 14182756TABLE ACCESS FULL PR_MED (cr=553602 r=145 w=0 time=26292679 us) 14182756 INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=26817559 us)(object id 51357) 90384TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=228394 us) 0 NESTED LOOPS (cr=2255835 r=0 w=0 time=665712789 us) 50935150HASH JOIN (cr=2252068 r=0 w=0 time=429854587 us) 90384 TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=216366 us) 50935150 HASH JOIN (cr=2240770 r=0 w=0 time=232393166 us) 50935150 TABLE ACCESS FULL PR_SIS (cr=2229472 r=0 w=0 time=52143346 us) 71554 TABLE ACCESS FULL PR_SIS_MAPAUCOLLEGES (cr=11298 r=0 w=0 time=353694 us) 0INDEX UNIQUE SCAN PR_ADMIN_TYPES_PK (cr=3767 r=0 w=0 time=115423379 us)(object id 51357) 0 HASH JOIN (cr=11298 r=0 w=0 time=900827 us) 0TABLE ACCESS FULL PR_ADMIN_GROUPS (cr=11298 r=0 w=0 time=289225 us) 0MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=0 us) 0 TABLE ACCESS FULL PR_AFFILIATE (cr=0 r=0 w=0 time=0 us) 0 BUFFER SORT (cr=0 r=0 w=0 time=0 us) 0 INDEX FULL SCAN PR_ADMIN_TYPES_PK (cr=0 r=0 w=0 time=0 us)(object id 51357) 481 VIEW PUSHED PREDICATE (cr=345622 r=20 w=0 time=101230049 us) 481 HASH JOIN (cr=345622 r=20 w=0 time=101019065 us) 481NESTED LOOPS OUTER (cr=43553 r=5 w=0 time=422888 us) 481 TABLE ACCESS BY INDEX ROWID PR_AFFILIATE (cr=43130 r=5 w=0 time=376132 us) 481 INDEX UNIQUE SCAN PR_AFFILIATE_PK (cr=42649 r=4 w=0 time=292929 us)(object id 51360) 421 TABLE ACCESS BY INDEX ROWID PR_DEPARTMENTS (cr=423 r=0 w=0 time=4703 us) 421 INDEX UNIQUE SCAN PR_DEPARTMENTS_PK (cr=2 r=0 w=0 time=1925 us)(object id 51375) 231361VIEW (cr=302069 r=15 w=0 time=95276432 us) 231361 UNION-ALL (cr=302069 r=15 w=0 time=95016018 us) 0 HASH JOIN (cr=3848 r=0 w=0 time=417296 us) 0 NESTED LOOPS (cr=3848 r=0 w=0 time=351280 us) 0VIEW (cr=3848 r=0 w=0 time=350781 us) 0 UNION-ALL (cr=3848 r=0 w=0 time=349902 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=222856 us) 0 TABLE ACCESS FULL PR_DEPARTMENTS (cr=1924 r=0 w=0 time=155040 us) 0 TABLE ACCESS FULL PR_HR (cr=0 r=0 w=0 time=0 us) 0 HASH JOIN (cr=1924 r=0 w=0 time=123298 us)
RE: Problems creading a Index
Run the following sql statement to see whether there are duplicate entries. Chances are that you will find duplicates hence you get the above error. You may choose to remove the duplicates or create a non-unique index otherwise. select COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM, count(*) from LAWSON2.OEINVCLINE group by COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM having count(*) 1; -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Teresita CastroSent: Monday, September 29, 2003 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Problems creading a Index HI!! I want to createthe next index: CREATE UNIQUE INDEX LAWSON2.IOEINVCLINE1 ON "LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM) TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 ) But I can't because Oracle send me the next error: The following error has occurred: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found I checked on TOAD ( with F4 on the table name) and It give me the next script. I don't have an index with the field ITEM on it, so I don't undestand what I am getting this error. DROP TABLE OEINVCLINE CASCADE CONSTRAINTS ; CREATE TABLE OEINVCLINE ( COMPANY NUMBER (4) NOT NULL, INVC_PREFIX CHAR (2) NOT NULL, INVC_NUMBER NUMBER (8) NOT NULL, LINE_NBR NUMBER (6) NOT NULL, LINE_TYPE CHAR (1) NOT NULL, ITEM CHAR (32) NOT NULL, DESCRIPTION CHAR (30) NOT NULL, ORDER_NBR NUMBER (8) NOT NULL, SHIPMENT_NBR NUMBER (10) NOT NULL, QUANTITY NUMBER (13,4) NOT NULL, INVC_CW_QTY NUMBER (13,4) NOT NULL, SPR_UOM CHAR (4) NOT NULL, SELL_UOM CHAR (4) NOT NULL, SEC_UOM CHAR (4) NOT NULL, MULT_SPR_FL CHAR (1) NOT NULL, SPR_TO_STOCK NUMBER (13,7) NOT NULL, SELL_TO_STOCK NUMBER (13,7) NOT NULL, SEC_UOM_MULT NUMBER (13,7) NOT NULL, LOCATION CHAR (5) NOT NULL, PRICE_STATUS CHAR (1) NOT NULL, ENTERED_PRICE NUMBER (13,5) NOT NULL, UNIT_PRICE NUMBER (13,5) NOT NULL, SELL_PRC_CURR NUMBER (15,7) NOT NULL, SELL_UNIT_PRC NUMBER (15,7) NOT NULL, UNIT_COST NUMBER (13,5) NOT NULL, CURRENT_COST NUMBER (13,5) NOT NULL, NO_CHARGE_FL CHAR (1) NOT NULL, ENTERED_DISC NUMBER (15,2) NOT NULL, ADD_ON_DISC NUMBER (15,2) NOT NULL, ALLOC_DISC NUMBER (15,2) NOT NULL, TAX_EXEMPT_CD CHAR (1) NOT NULL, TAX_CODE CHAR (10) NOT NULL, ENT_TAXABLE NUMBER (15,2) NOT NULL, TAXABLE_BSE NUMBER (15,2) NOT NULL, TAX_AMT_CURR NUMBER (15,2) NOT NULL, TAX_AMT_BSE NUMBER (15,2) NOT NULL, REASON_CODE CHAR (4) NOT NULL, DISC_CODE CHAR (10) NOT NULL, ORD_DISC_FL CHAR (1) NOT NULL, CONTRACT_NBR CHAR (14) NOT NULL, PROMOTION CHAR (10) NOT NULL, ACTIVITY CHAR (15) NOT NULL, ACCT_CATEGORY CHAR (5) NOT NULL, ATN_OBJ_ID NUMBER (12) NOT NULL, ACTIVITY_C CHAR (15) NOT NULL, ACCT_CATEG_C CHAR (5) NOT NULL, ATN_OBJ_ID_C NUMBER (12) NOT NULL, FINAL_INVC_FL CHAR (1) NOT NULL, SLS_ACCT_UNIT CHAR (15) NOT NULL, SLS_ACCOUNT NUMBER (6) NOT NULL, SLS_SUB_ACCT NUMBER (4) NOT NULL, SALES_MAJCL CHAR (4) NOT NULL, SALES_MINCL CHAR (4) NOT NULL, DSC_AMT_01 NUMBER (15,2) NOT NULL, DSC_AMT_02 NUMBER (15,2) NOT NULL, DSC_AMT_03 NUMBER (15,2) NOT NULL, DSC_ACCT_UNIT_01 CHAR (15) NOT NULL, DSC_ACCT_UNIT_02 CHAR (15) NOT NULL, DSC_ACCT_UNIT_03 CHAR (15) NOT NULL, DSC_ACCOUNT_01 NUMBER (6) NOT NULL, DSC_ACCOUNT_02 NUMBER (6) NOT NULL, DSC_ACCOUNT_03 NUMBER (6) NOT NULL, DSC_SUB_ACCT_01 NUMBER (4) NOT NULL, DSC_SUB_ACCT_02 NUMBER (4) NOT NULL, DSC_SUB_ACCT_03 NUMBER (4) NOT NULL, DSC_AMT_BASE NUMBER (15,2) NOT NULL, OFF_ACCT_UNIT CHAR (15) NOT NULL, OFF_ACCOUNT NUMBER (6) NOT NULL, OFF_SUB_ACCT NUMBER (4) NOT NULL, CGS_ACCT_UNIT CHAR (15) NOT NULL, CGS_ACCOUNT NUMBER (6) NOT NULL, CGS_SUB_ACCT NUMBER (4) NOT NULL, LAST_MISC_SEQ NUMBER (3) NOT NULL, LAST_COMM_SEQ NUMBER (3) NOT NULL, TERRITORY CHAR (4) NOT NULL, SALESMAN NUMBER (4) NOT NULL, SALESMAN_2 NUMBER (4) NOT NULL, COMM_RATE_1 NUMBER (7,7) NOT NULL, COMM_RATE_2 NUMBER (7,7) NOT NULL, COMM_SPLIT NUMBER (5,5) NOT NULL, USER_FLD1 CHAR (2) NOT NULL, USER_FLD2 CHAR (30) NOT NULL, USER_FLD3 CHAR (15) NOT NULL, LINE_GRS_CURR NUMBER (15,2) NOT NULL, INVL_OBJ_ID NUMBER (12) NOT NULL, PROD_TAX_CAT
RE: Problems creading a Index
Run the following sql statement to see whether there are duplicate entries. Chances are that you will find duplicates hence you get the above error. You may choose to remove the duplicates or create a non-unique index otherwise. select COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM, count(*) from LAWSON2.OEINVCLINE group by COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM having count(*) 1; -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Teresita CastroSent: Monday, September 29, 2003 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Problems creading a Index HI!! I want to createthe next index: CREATE UNIQUE INDEX LAWSON2.IOEINVCLINE1 ON "LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM) TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 ) But I can't because Oracle send me the next error: The following error has occurred: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found I checked on TOAD ( with F4 on the table name) and It give me the next script. I don't have an index with the field ITEM on it, so I don't undestand what I am getting this error. DROP TABLE OEINVCLINE CASCADE CONSTRAINTS ; CREATE TABLE OEINVCLINE ( COMPANY NUMBER (4) NOT NULL, INVC_PREFIX CHAR (2) NOT NULL, INVC_NUMBER NUMBER (8) NOT NULL, LINE_NBR NUMBER (6) NOT NULL, LINE_TYPE CHAR (1) NOT NULL, ITEM CHAR (32) NOT NULL, DESCRIPTION CHAR (30) NOT NULL, ORDER_NBR NUMBER (8) NOT NULL, SHIPMENT_NBR NUMBER (10) NOT NULL, QUANTITY NUMBER (13,4) NOT NULL, INVC_CW_QTY NUMBER (13,4) NOT NULL, SPR_UOM CHAR (4) NOT NULL, SELL_UOM CHAR (4) NOT NULL, SEC_UOM CHAR (4) NOT NULL, MULT_SPR_FL CHAR (1) NOT NULL, SPR_TO_STOCK NUMBER (13,7) NOT NULL, SELL_TO_STOCK NUMBER (13,7) NOT NULL, SEC_UOM_MULT NUMBER (13,7) NOT NULL, LOCATION CHAR (5) NOT NULL, PRICE_STATUS CHAR (1) NOT NULL, ENTERED_PRICE NUMBER (13,5) NOT NULL, UNIT_PRICE NUMBER (13,5) NOT NULL, SELL_PRC_CURR NUMBER (15,7) NOT NULL, SELL_UNIT_PRC NUMBER (15,7) NOT NULL, UNIT_COST NUMBER (13,5) NOT NULL, CURRENT_COST NUMBER (13,5) NOT NULL, NO_CHARGE_FL CHAR (1) NOT NULL, ENTERED_DISC NUMBER (15,2) NOT NULL, ADD_ON_DISC NUMBER (15,2) NOT NULL, ALLOC_DISC NUMBER (15,2) NOT NULL, TAX_EXEMPT_CD CHAR (1) NOT NULL, TAX_CODE CHAR (10) NOT NULL, ENT_TAXABLE NUMBER (15,2) NOT NULL, TAXABLE_BSE NUMBER (15,2) NOT NULL, TAX_AMT_CURR NUMBER (15,2) NOT NULL, TAX_AMT_BSE NUMBER (15,2) NOT NULL, REASON_CODE CHAR (4) NOT NULL, DISC_CODE CHAR (10) NOT NULL, ORD_DISC_FL CHAR (1) NOT NULL, CONTRACT_NBR CHAR (14) NOT NULL, PROMOTION CHAR (10) NOT NULL, ACTIVITY CHAR (15) NOT NULL, ACCT_CATEGORY CHAR (5) NOT NULL, ATN_OBJ_ID NUMBER (12) NOT NULL, ACTIVITY_C CHAR (15) NOT NULL, ACCT_CATEG_C CHAR (5) NOT NULL, ATN_OBJ_ID_C NUMBER (12) NOT NULL, FINAL_INVC_FL CHAR (1) NOT NULL, SLS_ACCT_UNIT CHAR (15) NOT NULL, SLS_ACCOUNT NUMBER (6) NOT NULL, SLS_SUB_ACCT NUMBER (4) NOT NULL, SALES_MAJCL CHAR (4) NOT NULL, SALES_MINCL CHAR (4) NOT NULL, DSC_AMT_01 NUMBER (15,2) NOT NULL, DSC_AMT_02 NUMBER (15,2) NOT NULL, DSC_AMT_03 NUMBER (15,2) NOT NULL, DSC_ACCT_UNIT_01 CHAR (15) NOT NULL, DSC_ACCT_UNIT_02 CHAR (15) NOT NULL, DSC_ACCT_UNIT_03 CHAR (15) NOT NULL, DSC_ACCOUNT_01 NUMBER (6) NOT NULL, DSC_ACCOUNT_02 NUMBER (6) NOT NULL, DSC_ACCOUNT_03 NUMBER (6) NOT NULL, DSC_SUB_ACCT_01 NUMBER (4) NOT NULL, DSC_SUB_ACCT_02 NUMBER (4) NOT NULL, DSC_SUB_ACCT_03 NUMBER (4) NOT NULL, DSC_AMT_BASE NUMBER (15,2) NOT NULL, OFF_ACCT_UNIT CHAR (15) NOT NULL, OFF_ACCOUNT NUMBER (6) NOT NULL, OFF_SUB_ACCT NUMBER (4) NOT NULL, CGS_ACCT_UNIT CHAR (15) NOT NULL, CGS_ACCOUNT NUMBER (6) NOT NULL, CGS_SUB_ACCT NUMBER (4) NOT NULL, LAST_MISC_SEQ NUMBER (3) NOT NULL, LAST_COMM_SEQ NUMBER (3) NOT NULL, TERRITORY CHAR (4) NOT NULL, SALESMAN NUMBER (4) NOT NULL, SALESMAN_2 NUMBER (4) NOT NULL, COMM_RATE_1 NUMBER (7,7) NOT NULL, COMM_RATE_2 NUMBER (7,7) NOT NULL, COMM_SPLIT NUMBER (5,5) NOT NULL, USER_FLD1 CHAR (2) NOT NULL, USER_FLD2 CHAR (30) NOT NULL, USER_FLD3 CHAR (15) NOT NULL, LINE_GRS_CURR NUMBER (15,2) NOT NULL, INVL_OBJ_ID NUMBER (12) NOT NULL, PROD_TAX_CAT CHAR (15) NOT NULL, LINE_GRS_BASE NUMBER (15,2) NOT NULL, OILSET2_SS_SW CHAR (1) NOT NULL, OILSET3_SS_SW CHAR (1) NOT NULL, OILSET4_SS_SW CHAR (1) NOT NULL, CONSTRAINT OILSET1 PRIMARY KEY ( COMPANY, INVC_PREFIX, INVC_NUMBER, LINE_NBR ) USING INDEX TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE ( INITIAL 40960 )) TABLESPACE
RE: DBMS_STATS and CBO
Prasad,
We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS.
Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the
indexes.
execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
Hope this helps.
Govind
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 9:24 PM
To: Multiple recipients of list ORACLE-L
We have a table (monthly fact table) which has 24 partitions and
partitioned by month. There is data in only 3 partitions. All the indexes
are locally partitioned.
In dev db, I analyzed the table and indexes with analyze table..compute
statistics. When I query the no. of rows group by month, it returns the
query in couple of seconds and does the index scan(bitmap) on month column.
I created this table in production db and this time I used the dbms_stats
to create the statistics (compute both on table and indexes). When I run
this query, it does full table scan. The only way I could make it to use
index scan by specifying hints.
All the parameters(init.ora) are exactly the same on both databases and it
is 8.1.7.4. In prod db, I tried various combinations of
optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
There was no use.
Then, I did analyze table .. compute statistics on prod table but it was
still doing the FTS. I did not delete the stats created by dbms_stats
before using analyze table stmnt.
Finally, I deleted the stats generated by dbms_stats/analyze table before
generating stats again with Analyze table table compute statistics. It is
doing the index scan now.
I have no clue 1. why it does not use index when I generated the stats
w/dbms_stats. 2. why I had to explicitly delete the stats before generating
the stats again to make use of the index.
Thanks for your help in advance.
Thanks.
Best Regards,
Prasad
860 843 8377
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [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: DBMS_STATS and CBO
Prasad,
Make sure that low_value and high_value columns do not have null values ie. generate
statistics on all columns. Analyze table generates the correct values for these
columns whereas FOR ALL INDEXED columns in DBMS_STATS do not. Once these values are
available through FOR ALL COLUMNS SIZE 1 ( ie do not generate histograms ), we seen
the queries to be using the indexes without any hints. We ran into this problem when
we went to 9i for the first time.
select
column_name,
NUM_DISTINCT ,
decode(LOW_VALUE ,null,null,'Full') Low ,
decode(HIGH_VALUE ,null,null,'Full') High ,
DENSITY ,
NUM_NULLS ,
NUM_BUCKETS ,
LAST_ANALYZED ,
SAMPLE_SIZE
from dba_tab_columns
where owner = 'your_owner'
and table_name = 'your_table';
Govind
-Original Message-
Sent: Saturday, August 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Hi!
Maybe you used analyze command without column analyzing clause, but used
dbms_stats package with column analyze clause (for all columns parameter).
Or it just could be because dbms_stats calculates some stats somewhat
differently (supposedly better), than old analyze command (average column
length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in
histogram. (In hist_head$ instead of histgrm$).
As an alternative to Govind's suggestion, you could increase SIZE parameter
(maximum is 254) to give CBO better understanding of data distribution.
Note that you should be careful with the METHOD_OPT parameter in
gather_schema_statistics procedure in version 9i, because if you supply
invalid parameter there, then the procedure just does nothing and returns
without error! You should verify from last_analyzed column to see whether a
segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it
might be fixed on newer patch levels).
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, August 09, 2003 7:29 AM
Thanks Govind for your reply and suggestion.
Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
Best Regards,
Prasad
860 843 8377
Govind.Arumugam@
alltel.com To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS
and CBO
.com
08/09/2003 12:14
AM
Please respond to
ORACLE-L
Prasad,
We ran into the same problem when we did FOR ALL INDEXED COLUMNS using
DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1.
Then
CBO started to use the indexes.
execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
Hope this helps.
Govind
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 9:24 PM
To: Multiple recipients of list ORACLE-L
We have a table (monthly fact table) which has 24 partitions and
partitioned by month. There is data in only 3 partitions. All the indexes
are locally partitioned.
In dev db, I analyzed the table and indexes with analyze table..compute
statistics. When I query the no. of rows group by month, it returns the
query in couple of seconds and does the index scan(bitmap) on month
column.
I created this table in production db and this time I used the dbms_stats
to create the statistics (compute both on table and indexes). When I run
this query, it does full table scan. The only way I could make it to use
index scan by specifying hints.
All the parameters(init.ora) are exactly the same on both databases and it
is 8.1.7.4. In prod db, I tried various combinations of
optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
There was no use.
Then, I did analyze table .. compute statistics on prod table but it was
still doing the FTS. I did not delete the stats created by dbms_stats
before using analyze table stmnt.
Finally, I deleted the stats generated by dbms_stats/analyze table before
generating stats again with Analyze table table compute statistics. It
is
doing the index scan now.
I have no clue 1. why it does not use index when I generated the stats
w/dbms_stats. 2. why I had to explicitly delete the stats before
generating
the stats again to make use of the index.
Thanks for your help in advance.
Thanks.
Best Regards,
Prasad
860 843 8377
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
Oracle user groups in Bangalore, INDIA
List gurus, Are there any oracle user groups in Bangalore, INDIA? My nephew is an upcoming Oracle DBA. He would like to participate in user meetings relevant to Oracle in Bangalore area. Any help will be greatly appreciated. Thanks, Govind -- 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: Parallel Query Server died
Shuan, Please let me know whether you are on 9i. When was the database bounced cleanly? After shutting down the oracle instance, check to make sure that no other orphaned background processes are present. ie. ps -ef | grep -i oracle should returnnothing. We got burntby this ORA-12805 several weeks back. We did bounce the database cleanly while implementingone of our application patches and the no. of ORA-12805 occurrences have come down dramatically. Also, the query plans have changed in 9i. Ifyou see more full table scans in 9i than in 8i versions, try to comparethe query plans with these session level settings. CBO Hidden parameter to generate 8.1.7 like query plans: alter session set "_UNNEST_SUBQUERY" = FALSE;alter session set "_ORDERED_NESTED_LOOP" = FALSE;alter session set "_ALWAYS_SEMI_JOIN" = off; explain plan for your parallelquery; HTH. Govind -Original Message-From: Mogens N?rgaard [mailto:[EMAIL PROTECTED]Sent: Saturday, May 31, 2003 10:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Parallel Query Server diedThis server is dead. It has gone to meet its maker. It is no more. It's pushing up the lillies... This... is an ex-server.Thanks to Monty Python.Jamadagni, Rajendra wrote: Funeral at 8pm EST, movieat11pm EST on TNT ... Raj -Original Message-From: Rajesh Dayal [mailto:[EMAIL PROTECTED]]Sent: Saturday, May 31, 2003 2:50 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Parallel Query Server died After long time .. LOL . ;-) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Vladimir BaracSent: Friday, May 30, 2003 2:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Parallel Query Server died Kool, now some cyber funeral will take place... - Original Message - From: shuan.tay(PCI¾G¸R³Ô) To: Multiple recipients of list ORACLE-L Sent: Friday, May 30, 2003 11:19 Subject: Parallel Query Server died Dear all DBAs, What should i check for this error? "ORA-12805: parallel query server died unexpectedly" The SQL statement was runningwell before. There's nothing in the alert log about this error. I'm using Oracle 8.1.6 on Redhat 7.2. Thanks and have a nice day. *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: Listener Hangs
This is how you can cleanup listener.log w/o taking the listener down. You can copy the listener.log to an archive location, then do the following: cat "/dev/null" and redirect to listener.log file. This will zero the current file and allow other updates to the log from the listener to continue. eg: cp listener.log wherever.logcat /dev/null listener.log ls -l listener.log-rw-r--r-- 1 oracle 0 Apr 5 13:22 listener.loglsnrctl statusls -l listener.log-rw-r--r-- 1 oracle 160 Apr 5 13:22 listener.log Here you can see the listener.log files gets emptied. Doing a 'status' command on the listeneryou can see the current tnslsnr process continues to write to the file. By redirecting the output of an empty file '/dev/null' or using a file that may contain thelast 100 lines of the Listener and redirecting to the "listener.log", you keep the same inodevalue that tnslsnr process uses to write to the "listener.log". The key is not to remove or move the current "listener.log" while the tnslsnr process is running.It is ok to copy the file for historical records. -Original Message-From: Sam Bootsma [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 04, 2003 12:05 PMTo: Multiple recipients of list ORACLE-LSubject: Listener Hangs We are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version 2.3.4.0.0. Yesterday morning our LISTENER process hung. ERROR: ORA-12203: TNS:unable to connect to destination. We resolved the problem using the following steps: a) Stop Listener using LSNRCTL utility (this hung did not succeed in stopping the listener) b) Issue UNIX command kill -9 process id to kill the corresponding Listener process. c) Start the listener using the LSNRCTL utility I checked the alert log and listener log. I found nothing in either file pointing to what the problem might have been. However, I did note that the listener.log file was 200 MB in size. Would the large size of the listener.log file contribute to the Listener hanging? This problem has occurred 6 to 8 times in the past couple of weeks. I do plan on starting a new listener.log file. Thanks, Sam Bootsma [EMAIL PROTECTED]
Oracle 9.2.0.2 bugs
List, We recently upgraded our Production system to 9i and gotburnt by these bugs. Installed Patch List:2785282 [ Base Bug(s): 2442125 ] Bug # 2808431WRONG RESULTS FOR PARALLEL QUERY WITH HASH AND INDEX JOIN Bug # 2783229 Base bug : 2442125 INCORRECT DATA RETURNED FROM DATABASE WHEN QUERIED FROM PROC I would greatly appreciate if we can share among ourselves the various bugs noted in 9i along withpossible fixes. Our version info. Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit ProductionWith the Partitioning and Real Application Clusters optionsJServer Release 9.2.0.2.0 - Production Sun Solaris 2.8 TIA, Govind
How to identify full table scans?
List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) COUNT, o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- 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: index rebuilding performance vs sort_area_size
We have not seen any performance gains after setting the sort_area_size in excess of 50Mb. We have set this as a standard in our re-indexing scripts to set this to 50Mb maximum. HTH. -Original Message- Sent: Monday, January 13, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Hi: Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to test index rebuild (with nologging) performance vs sort_area_size. I used alter session set sort_area_size = to set the sort_area_size value. Nothing else was changed. The temp tablespace is 8G. There is no other active sessions running during the test. I selected two indexes for the test. Their sizes are about 20M and 115M respectively so that they were fit into their initial extent after the rebuild. Here is the result: -- 1. rebuild an index with size of about 20M: alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next 20M); sort_area_size 20971520 Elapsed:00:00:12.49 00:00:11.6800:00:12.18 sort_area_size 80971520 Elapsed:00:00:09.9500:00:09.94 00:00:09.54 -- 2. rebuild an index with size of about 115M: alter index mt.TOPIC_INDEX rebuild nologging STORAGE (INITIAL 114688000 next 114688000); sort_area_size 20971520 Elapsed:00:00:51.06 00:00:50.4400:00:51.46 sort_area_size 80971520 Elapsed:00:00:52.17 00:00:51.6500:00:51.75 sort_area_size 150971520 Elapsed: 00:00:42.42 00:00:41.81 00:00:41.71 So with this very limited data points, I found 1. In the 1st example, the sort_area_size was increased almost 4 times, but we only got about 20% performance improvement. 2. In the 2nd example, we got 20% performance boost when sort_area_size was increased from 21M to 151M. Is what I see here typical? It seems that with the increase of sort_area_size, the index rebuild will be faster, but not as fast as I hoped. Any comments? Guang Mei _ The new MSN 8 is here: Try it free* for 2 months http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: How to identify full table scans?
Title: RE: How to identify full table scans? This helps to identify the queries that could be tunedfor LIO and/or PIO from a SQL Tuning perspective. We can give this list to the development or application teams so that they could independently work off this list (hopefully!). -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Monday, January 13, 2003 2:22 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to identify full table scans? Govind, Just curious why you are attacking the full table scans. I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql. It was very telling and very very useful. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: How to identify full table scans? List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour? Thanks, Govind /* Recent full table scan */ /* Should be run as user SYS */ set serverout on size 100 set verify off set pagesiz 300 set lin 120 col object_name form a30 col owner form a10 PROMPT Column flag in x$bh table is set to value 0x8, when PROMPT block was read by a sequential scan. spool recentfulltablescan.lst SELECT count(o.object_name) "COUNT", o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t WHERE x.obj=o.object_id and o.object_name=t.table_name -- AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS' group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ; spool off -- 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: Prevent FTS
Please check Jonathan Lewis book 'Practice Oracle 8i'. This book provides lot more
details about these two optimizer related parameters that could significantly affect
FTS situations.
optimizer_index_caching
optimizer_index_cost_adj
The general 'harmless' guidelines could be setting 10 to optimizer_index_caching and
35 to optimizer_index_cost_adj.
HTH.
Govind
-Original Message-
Sent: Monday, January 06, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L
What is the significance of parameter OPTIMIZER_INDEX_COST_ADJ ?
When I tried to change the value the explain plan has changed .
alter session set OPTIMIZER_INDEX_COST_ADJ = 10
table access for Z was by index rowid
alter session set OPTIMIZER_INDEX_COST_ADJ = 100
table access for Z was full
How can I make effective use of this parameter .
If i dont want to use this in alter session,can this value be
set elsewhere.
what is the difference between setting this parameter to some other value than
default
compared to using hints in sql stmt.
I've tried some hints like rowid,index but it did not work for the join stmt
as it still used the FTS of Z before I tried to change the above parameter to
10.
-Original Message-
Sent: Monday, January 06, 2003 11:03 PM
To: '[EMAIL PROTECTED]'
Hi All,
I've 3 tables table x,y,z
pk of x is (col_1,col_2),pk of y is (col_1,col_2,col_4,col_5),pk of z is
(col_1,col_2)
where clause is :
where x.col_1 = y.col_1
and x.col_2 = y.col_2
and x.col_1 = z.col_1
and x.col_2 = z.col_2
and y.col_1 = z.col_1
and y.col_2 = z.col_2
and x.col_3 in ('val1','val2','val3')
and.
..
Explain plan shows that there is a FTS of table z .
what can be the reason for this and how to prevent this.
Thanks
Manoj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [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).
Parallel degree on tables and indexes
List, Is there a 'magic' number for num_rows that you use in order to set the parallel degree on tables and/or indexes? We were told to use parallel degree of 4 for tables having more than 100,000 rows. Some of the on-line queries are running in parallel mode thus making batch jobs to go serial for lack of parallel slaves. Upon a closer look, we see tables/indexes having millions of rows having parallel degree set at 1 and tables/indexes having fewer than 100,000 rows are having parallel degree set at 4 etc., Is there a number for num_rows that you have been very successful to determine the parallel degree? Some of the indexes were re-built using parallel degree of 3 to reduce the 'create index' time but were not changed back to 'noparallel' recently and I need to answer these questions. TIA. Govind -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: New course for 9i - Dumps/Crashes from Oracle University
Title: RE: New course for 9i - Dumps/Crashes from Oracle University Oracle Corporation is conducting these 3 highly technical seminars. Each one of them is a full day class at a cost of $500 per class. http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12856GC10 http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12858GC10 http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12860GC10 -Original Message-From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]]Sent: Monday, December 16, 2002 12:05 PMTo: Multiple recipients of list ORACLE-LSubject: RE: New course for 9i - Dumps/Crashes from Oracle University There will probably be others in the series... Looks like one of the 8i Internals seminars has made it to 9i! Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Monday, December 16, 2002 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: RE: New course for 9i - Dumps/Crashes from Oracle University What is the world coming to? -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Friday, December 13, 2002 5:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: New course for 9i - Dumps/Crashes from Oracle University hmmm ... http://education.oracle.com/web_prod-plq-dad/plsql/cdesc?dc=D12856GC10p_org_id=1001lang=US 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! also send the HELP command for other information (like subscribing).
RE: STATSPACK
STATSPACK related can be found under $ORACLE_HOME/rdbms/admin directory in these documents. Version 8.1.6 or prior ( must be 8i ) $ORACLE_HOME/rdbms/admin/statspack.doc Version 8.1.7 $ORACLE_HOME/rdbms/admin/spdoc.txt Prior to 8i: ( No statspack! ) $ORACLE_HOME/rdbms/admin/utlbstat.sql $ORACLE_HOME/rdbms/admin/utlestat.sql Hope this helps. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
BLEVEL on bit-mapped indexes
According tooracle documentation or metalink sources,if the BLEVEL were to be more than 4, it is recommended to rebuild the index. select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL', 2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OKfrom dba_indexeswhere owner ='owner-name'; When troubleshooting aperformance problem ina data warehousing environmentI found thatsome of the indexes had BLEVEL at 5 or 6. I am curious to know whether these indexesare candidates for rebuild.Should be looking at BLEVEL for bit-mapped indexes at all? I am not that experienced in bit-mapped indexes hence posting it to the group. Thanks in advance. Govind
RE: BLEVEL on bit-mapped indexes
According tooracle documentation or metalink sources,if the BLEVEL were to be more than 4, it is recommended to rebuild the index. select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL', 2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OKfrom dba_indexeswhere owner ='owner-name'; When troubleshooting aperformance problem ina data warehousing environmentI found thatsome of the indexes had BLEVEL at 5 or 6. I am curious to know whether these indexesare candidates for rebuild.Should be looking at BLEVEL for bit-mapped indexes at all? I am not that experienced in bit-mapped indexes hence posting it to the group. Thanks in advance. Govind
RE: Single-task message waits
This is what I got from metalink for an earlier case. 'single-task message' waits are Waiting for a client message, but in single task mode. Single task mode is when a process communicates directly with the SGA, as opposed to communicating through a background shadow process. even though you are using the a client-server setup (as opposed to single-task), single task is still used internaly for some operation. Also opsdiag.sql ignores this wait event as an idle wait event. Govind -Original Message- Sent: Tuesday, December 03, 2002 7:54 PM To: Multiple recipients of list ORACLE-L Rick: ANjo Kolk classifies this wait event under one of the IDLE wait events.i.e nothing to worry if you see them in excessive. Best Regards, K Gopalakrishnan -Original Message- Sent: Tuesday, December 03, 2002 3:10 PM To: Multiple recipients of list ORACLE-L Hey all, Does anyone have an idea as to how to tackle single-task message waits on 8.1.7? The most I could find on this is that it may have to do with Context indexes, which are referenced in the massive package I'm helping test with a developer. I'd really like to drop the amount of waits I'm seeing on this because it accounts for ~300-400ms/sec wait according to Spotlight. I had a 10046 trace running too, but I'm having problems setting max_dump_file_size for another process, so I only have 10MB worth of logfile (about 3 minutes of a 30 minute process). But that's another post. :) 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- 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: ORA-1653: unable to extend table - Why?
My experience yesterday was that dropping an index and trying to rebuild the same index failed ( even after coalescing the tablespace) since we need to wait for SMON to clean up the extents to make them available. I don't know how we make SMON process to coalesce the free space faster enough( or immediately after we delete from the table or dropping an index ). Any ideas? -Original Message- Sent: Monday, December 02, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert. Any more ideas? -Original Message- Sent: Friday, November 29, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- 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.com -- 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: Partition Info
You may use the following query will identify the 'highest' partition by date range. select partition_name, partition_position , high_value, tablespace_name from dba_tab_partitions where table_name = 'table_name' and table_owner = 'owner' and partition_position = ( select max(partition_position) from dba_tab_partitions where table_name = 'table_name' and table_owner = 'owner' ) You can store the high_value in another table and start manipulating the way you want such as: select max(add_months( to_date( substr(high_value,3,19), 'S-MM-DD HH24:MI:SS' ), 1 )) into new_high_value from temp_table I don't know how we can directly manipulate HIGH_VALUE column in dba_tab_partitions. But I have tried the above in a PL/SQL program and it worked. Hope this helps. -Original Message- Sent: Monday, December 02, 2002 4:39 PM To: Multiple recipients of list ORACLE-L DBA_TAB_PARTITIONS -Original Message- Sent: Monday, December 02, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Hello everybody. I have a number of tables with range by date partitions. I need to write a script to monitor the latest partitions and send me an e-mail if it's close to a current date. I am just looking for tables or views that will help me find out the date of the last partition. Sergei. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- 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.com -- 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: Database up longer that host?
You may use following query will give you the uptime in hours and in minutes. select sysdate, startup_time, round( (sysdate - startup_time) *24*60 ,0 ) uptime_in_minutes, round( (sysdate - startup_time) *24 ,0) uptime_in_hours from v$instance SYSDATESTARTUP_TIME UPTIME_IN_MINUTES UPTIME_IN_HOURS -- -- - --- 20021202203918 20021202044608 953 16 Hope this is what you wanted. -Original Message- Sent: Monday, December 02, 2002 8:34 PM To: Multiple recipients of list ORACLE-L On Mon, 2 Dec 2002, Stephane Faroult wrote: Stephen Andert wrote: I use a script named db_uptime.sql (I think I got it from the list here) to calculate how long the database has been up. The output compares nicely to the unix uptime command. I hope that the query doesn't come from the list, because it is wrong. The error is to apply floor() before multiplying by 24 or 60 - you have tremendous rounding errors. My own database has not been up long enough to be 100% sure about it but I believe the following to be correct : Hi: Neither of the scripts works for me. Try this? select 'Host Name : '||host_name|| chr(10)|| 'Instance Name : '||instance_name|| chr(10)|| 'Uptime : ' ||floor(xx)||'days ' || floor( 24 * (xx - floor(xx)) ) || 'hours ' || round( 60 * (24 * xx - floor(24 * xx))) || 'minutes ' from ( select host_name,instance_name ,(sysdate-STARTUP_TIME) xx from v$instance ) / Meg Crocker -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Meg Crocker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- 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: Table Locks
We run the following script every 20 minutes to identify the pending transactions ( to be committed) and notify the appropriate application group (online or batch ) to take action in consultation with the DBA group. We filter this by username since we have some convention for batch programs and online programs; We set thresholds for minutes_pending 5 minutes for onlines and 60 for batch. select sysdate, '1' inst_id, sid, serial#, username, substr(terminal,1,10) termi nal, osuser, t.start_time, r.name, t.used_ublk ROLLB BLKS, decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status) )) status, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) minutes_pending from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time; Hope this helps. Govind -Original Message- Sent: Saturday, November 30, 2002 12:39 AM To: Multiple recipients of list ORACLE-L Seems to me you should just have your program try to lock tables in exclusive mode. If it succeeds, then rollback. If it fails (timeout), it opens another session while the 'lock table' is waiting, and finds the blocker. Otherwise, if you are only interested in sessions that are actually blocking other sessions, just look in v$lock where block = 1. As interesting as it seems, I think you won't succeed in trying to put triggers on x$kgllk or anything like that. They're not real tables - just table-like accessors for memory structures in the SGA. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Fri, 29 Nov 2002, [EMAIL PROTECTED] wrote: I would like to send an alert message to a client when a data row is locked for more than a certain period of time. For this can I write triggers on the system tables. If so on which table should I write a trigger to retrieve the table lock information. Are there any implications on writing triggers on the system tables. The alert message should be sent automatically in the sense, can I write an alert and signal it from a trigger written on some system table where the lock information is available? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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.com -- 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).
