Oracle-L: RE: ORA-1410 Silliness

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 02 Jan 2003 07:09:42 -0800
Message-ID: <F001.0052564F.20030102070942@fatcity.com>

well, you are certainly shooting down all of the best ideas being offered! :)

I'd hate to be in your shoes!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Thursday, January 02, 2003 8:19 AM To: Multiple recipients of list ORACLE-L

Nope...

-----Original Message-----

Sent: Tuesday, December 31, 2002 4:54 PM To: Multiple recipients of list ORACLE-L

Did u do any migration recently .

"Koivu, Lisa" <Lisa.Koivu_at_efairfield.com> wrote:

Well, I don't think that's the issue. I'm issuing bulk inserts and using pl/sql tables in this procedure. That functionality has been in place since February and these errors only started surfacing in the last couple of months.

I could decrease the commit interval and try that. I just hope it doesn't (big) hammer my runtime, it's bad enough already, even with the screaming fast bulk insert. Thanks Kevin for your input.

-----Original Message-----

Sent: Tuesday, December 31, 2002 1:44 PM To: Multiple recipients of list ORACLE-L

Very straight forward..... and LONG ..... (Yea, I read to your last message).

Could that be the issue here ?? Is the record too long or some buffer being overwritten in Oracle when there is so long of record and so much data ?? Have you tried (or do you already) commit after each insert from the RAW table ?? I know that you would not think this could help , but buffers and such might get cleared on the commit ..... just a thought anyway.

-----Original Message-----

Sent: Tuesday, December 31, 2002 11:49 AM To: Multiple recipients of list ORACLE-L

You asked for it. Here goes. It is partitioned on julian_run_date. Please don't hammer me about the design. I wasn't given a chance to improve on it. It is basically a mainframe file that I have stored history of.

SQL> desc vegas_mart

 Name                          Null?    Type
 ----------------------------- -------- ------------------
 ID                            NOT NULL NUMBER(38)
 CONTRACT_NUMBER               NOT NULL NUMBER(9)
 LOAD_JOB_ID                   NOT NULL NUMBER(12)
 JULIAN_RUN_DATE               NOT NULL NUMBER(12)
 FAC_CODE &n! bsp;                             VARCHAR2(1)
 OWNER_LAST_NAME                        VARCHAR2(20)
 OWNER_FIRST_NAME                       VARCHAR2(15)
 OWNER_ADDRESS1                         VARCHAR2(25)
 OWNER_ADDRESS2                         VARCHAR2(25)
 OWNER_CITY             !                 VARCHAR2(18)
 OWNER_STATE                            VARCHAR2(2)
 OWNER_ZIP                              VARCHAR2(9)
 NET_PURCHASE_PRICE                     NUMBER(11,2)
 ORIGINAL_DOWN_PAYMENT                  NUMBER(11,2)
 TOTAL_DOWN_PAYMENT                     NUMBER(11,2)
 CR_BAL&nbs! p;                                NUMBER(11,2)
 INTEREST_RATE                          NUMBER(5,2)
 FIRST_PAYMENT_DATE                     DATE
 QUALIFICATION_CODE                     VARCHAR2(1)
 PAYMENT_AMOUNT                         NUMBER(9,2)
 PAYMENT_FREQUENCY              &! nbsp;       VARCHAR2(1)
 AGING_10_TO_30_DAYS_DUE                NUMBER(9,2)
 AGING_31_TO_60_DAYS_DUE                NUMBER(9,2)
 AGING_61_TO_90_DAYS_DUE                NUMBER(9,2)
 ASSIGNED_LOAN_ADMIN_REP                VARCHAR2(2)
 DATE_OF_SALE                           DATE
 STATUS_OF_ACCOUNT                      VARCHAR2(1)
 CONTRACT_TYPE    &nb! sp;                     VARCHAR2(1)
 WAS_PENDER                             VARCHAR2(1)
 CREDIT_LIFE_ON_CONTRACT                VARCHAR2(1)
 DOCUMENT_STATUS_CODE                   VARCHAR2(1)
 FIXED_WEEK_SALE                        VARCHAR2(1)
 UDI_SALE                             &nbs! p; VARCHAR2(1)
 PHASE_NUMBER                           VARCHAR2(6)
 FAIRSHARE_PLUS_MEMBER                  VARCHAR2(1)
 POINTS_OWNED                           NUMBER(7)
 DEED_DATE                              DATE
 PRE_AUTH_DRAFT_ACCOUNT                 VARCHAR2(1)
 RESERVATION_CODE               !         VARCHAR2(4)
 INTERNATIONAL_CODE                     VARCHAR2(1)
 UNIT_PHASE_COMPLETION_CODE             VARCHAR2(1)
 AGING_0_TO_90_DAYS_DUE                 NUMBER(9,2)
 AGING_91_TO_120_DAYS_DUE               NUMBER(9,2)
 AGING_121_TO_150_DAYS_DUE              NUMBER(9,2)
 AGING_151_OVER                         NUMBER(9,2)
 LOT_LOCATION          !                  VARCHAR2(12)
 PAYMENTS_MADE                          NUMBER(3)
 SUPPRESSION_CODE                       VARCHAR2(1)
 ACCRUED_INTEREST_BAL                   NUMBER(9,2)
 PAC_FREEZE_CODE                        VARCHAR2(1)
 CREDIT_CARD_FREEZE_CODE                VARCHAR2(1)
 ASSOCIATION_NUMBER       ! ;              VARCHAR2(4)
 RFS_ASSIGNMENT_DATE                    DATE
 OVERRIDE_MAINT_FEE_BALANCE             VARCHAR2(1)
 RESERVATION_PENDING                    VARCHAR2(1)
 CREDIT_REPORTING_CODE                  VARCHAR2(2)
 CANCEL_DEFERMENT_REASON_CODE           VARCHAR2(2)
 EQUITY_IN                              NUMBER(9,2)
 DATE_CO! DED_7                           DATE
 ADJUSTABLE_RATE_MORTGAGE               VARCHAR2(1)
 NUMBER_OF_PAYS_LEFT                    NUMBER(4)
 DEFERRED_INTEREST                      NUMBER(9,2)
 DEFERRED_PRINCIPAL                     NUMBER(9,2)
 CURRENT_YEAR_DEFERMENTS                NUMBER(5)
 CURRENT_YEAR_TOTAL_DEFERMENTS       &nb! sp;  NUMBER(5)
 LAST_PAYMENT_DATE                      DATE
 NEXT_PAYMENT_DATE                      DATE
 PAC_DUE_DATE                           DATE
 EFT_ROUTING_NUMBER                     VARCHAR2(8)
 EFT_ACCOUNT_NUMBER                     VARCHAR2(30)
 EFT_MANUAL_NUMBER                     ! ; VARCHAR2(30)
 BANK                                   VARCHAR2(35)
 STATUS_CHANGE_DATE                     DATE
 ASSIGNED_LOAN_REP                      VARCHAR2(2)
 CREDIT_CARD_PAC_ACCOUNT                VARCHAR2(20)
 PRINCIPAL_BALANCE                      NUMBER(11,2)
 CR_DISCOUNT_BALANCE                &! nbsp;   NUMBER(11,2)
 CREDIT_LIFE_PREM_BAL                   NUMBER(11,2)
 RFP_PAC_CODE                           VARCHAR2(1)
 RFP_DRAFT_CODE                         VARCHAR2(1)
 RFP_ROUTE_NUMBER                       VARCHAR2(8)
 RFP_EFT_NUMBER                         VARCHAR2(30)
 RFP_MANUAL_NUMBER          &n! bsp;           VARCHAR2(30)
 RFP_BANK_NAME                          VARCHAR2(35)
 FPPA_PAC_CODE                          VARCHAR2(1)
 FPPA_DRAFT_CODE                        VARCHAR2(1)
 FPPA_ROUTE_NUMBER                      VARCHAR2(8)
 FPPA_EFT_NUMBER                        VARCHAR2(30)
 FPPA_MANUAL_NUMBE! R                     VARCHAR2(30)
 FPPA_BANK_NAME                         VARCHAR2(35)
 TS_BAL_DUE_RECOGNIZED                  NUMBER(9)
 TS_LATE_FEE_RECOGNIZED                 NUMBER(9,2)
 TS_YTD_MAINT_FEE_COLL                  NUMBER(9,2)
 TS_MAINT_FEE_AMOUNT                    NUMBER(9,2)
 PO_BIRTH_DATE           &nbs! p;              DATE
 TS_LOCATION                            VARCHAR2(12)
 CR_DATE_REC_IN_DEEDING                 DATE
 CR_REFUND                              VARCHAR2(11)
 CR_CREDIT_LIFE_TYPE                    VARCHAR2(1)
 CR_QUALIFICATION_DATE                  DATE
 CR_EQT_IN_FROM_CONT_NO1       &nb! sp;        VARCHAR2(9)
 CR_EQT_IN_FROM_CONT_NO2                VARCHAR2(9)
 CR_HC_AMT1                             NUMBER(9)
 CR_HC_AMT2                             NUMBER(9)
 CR_HC_POST1                            NUMBER(9)
 CR_HC_POST2                            NUMBER(9)
 CR_HC_D! ATE1                            DATE
 CR_HC_DATE2                            DATE
 CR_DATE_IN_LR                          DATE
 CR_TRADE_ALLOW                         NUMBER(9)
 CR_TITLE_INS_CHARGED                   NUMBER(11)
 CR_TITLE_INS_COLLECTED                ! ; NUMBER(11)
 CR_FILING_FEE_CHARGED                  NUMBER(11)
 CR_FILING_FEE_COLLECTED                NUMBER(11)
 CR_ACCRUED_INT_BAL_RSV                 NUMBER(11)
 CR_LATE_FEE_BAL_RSV                    NUMBER(11)
 AREA_CODE                              VARCHAR2(3)
 PHONE_NUMBER                           VARCHAR2(7)
 PAI! D_OFF                               VARCHAR2(1)
 EDIT_DATE_TIME                         DATE
 BIANNUAL_FLAG                          VARCHAR2(10)
 FICO_SCORE                             NUMBER(3)
 SOCIAL_SECURITY_NUMBER                 NUMBER(9)

SQL> Here's the code. Line 1970 is cr_hc_amt2.

Insert the record into the reject table. INSERT INTO vegas_mart_reject SELECT id, load_date, load_job_id_v, contract_number, fac_code, owner_last_name, owner_first_name, owner_address1, owner_address2, owner_city, owner_state, owner_zip, net_purchase_price, original_down_payment, total_down_payment, cr_bal, interest_rate, first_payment_date, qualification_code, payment_amount, payment_frequency, aging_10_to_30_days_due, aging_31_to_60_days_due, aging_61_to_90_days_due, assigned_loan_admin_rep, date_of_sale, status_of_account, contract_type, was_pender, credit_life_on_contract, document_status_code, fixed_week_sale, udi_sale, phase_number, fairshare_plus_member, points_owned, deed_date, pre_auth_draft_account, reservation_code, international_code, unit_phase_completion_code, aging_0_to_90_days_due, aging_91_to_120_days_due, aging_121_to_150_days_due, aging! _151_over, lot_location, payments_made, suppression_code, accrued_interest_bal, pac_freeze_code, credit_card_freeze_code, association_number, r! fs_assignment_date, override_maint_fee_balance, reservation_pending, credit_reporting_code, cancel_deferment_reason_code, equity_in, date_coded_7, adjustable_rate_mortgage, number_of_pays_left, deferred_interest, deferred_principal, current_year_deferments, current_year_total_deferments, last_payment_date, next_payment_date, pac_due_date,! eft_routing_number, eft_account_number, eft_manual_number, bank, status_change_date, assigned_loan_rep, credit_card_pac_account, principal_balance, cr_discount_balance, credit_life_prem_bal, rfp_pac_code, rfp_draft_code, rfp_route_number, rfp_eft_number, rfp_manual_number, rfp_bank_name, fppa_pac_code, fppa_draft_code, fppa_route_number, fppa_eft_number, fppa_manual_number, fppa_bank_name, ts_bal_due_recognized, ts_late_fee_recognized, ts_ytd_maint_fee_coll, ts_maint_fee_amount, po_birth_date, ssn, ts_location, cr_date_rec_in_deeding, area_code, phone_number, cr_refund, cr_credit_life_type, cr_qualification_date, cr_eqt_in_from_cont_no1, cr_eqt_in_from_cont_no2, cr_hc_amt1, cr_hc_amt2, <-- Line 1970 cr_hc_post1, cr_hc_post2, cr_hc_date1, cr_hc_date2, cr_date_in_lr, cr_trade_allow, cr_title_ins_charged, cr_title_ins_collected, cr_filing_fee_charged, cr_filing_fee_collected, cr_accrued_int_bal_rsv, cr_late_fee_bal_rsv, fico_score FROM vegas_raw WHERE id = vegas_raw_rec.id;

Makes no sense at all. And if you read this far, don't say I didn't warn you.

-----Original Message-----

Sent: Tuesday, December 31, 2002 10:44 AM To: Multiple recipients of list ORACLE-L

How about posting the table structure and the lines around 1970 in the Elvis package.

-----Original Message-----

Sent: Tuesday, December 31, 2002 9:04 AM To: Multiple recipients of list ORACLE-L

Nope... nothing named rowid.

-----Original Message-----

Sent: Tuesday, December 31, 2002 9:29 AM To: Multiple recipients of list ORACLE-L

Is there a column with a datatype of rowid?

-----Original Message-----

Sent: Tuesday, December 31, 2002 6:44 AM To: Multiple recipients of list ORACLE-L

Hello all,

8.1.7, Windows 2000 SP2

Here's the error:
*
ERROR at line 1:

ORA-01001: invalid cursor 
ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970 
ORA-01410: invalid ROWID 
ORA-06512: at line 1

Has anyone seen this error before? I run into this error periodically during data loads. I have done the following to search for the root of the problem:

No code references ROWID. Deletes are never applied to this table.
No inline views in any of the code.
Ran dbv on all datafiles while database was down. No problems identified.
Ran analyze table <table name> validate structure cascade on all recent partitions. No rows found in INVALID_ROWS table.
Was able to export the entire table without any problem.

I can't easily drop the indexes and recreate them. This is a very large table - ~25GB, 38 million rows. I also can't easily export/drop/recreate/import.

Usually when this happens I can re-fire the load and it will complete, no problem. It's a big annoyance and it seems like every time I take a day off it happens.

Any ideas, suggestions, or thoughts are appreciated. Thanks everyone.

Lisa Koivu
Oracle Dorkbase Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063

Do you Yahoo!?
Yahoo! <http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com> Mail Plus - Powerful. Affordable. Sign up
<http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com> now

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Jan 02 2003 - 09:09:42 CST