Printable View
Actually this is kind of vauge but maybe someone can point me in the right direction to start looking for an answer...
We have a view that has been workin on our system for over a year. As of yesterday whenever it is accessed it just hangs the client and the Oracle proccess has to be ended and the client killed, I also have tried to run and explain plan on it and it still hangs. I am thinking maybe it was somehow corrupted and maybe I should rebuild it???
Thanks,
SM
You can try to recompile the view, but that's probably not your problem:
alter view user.viewname compile;
I would take a look at the underlying structures to see if any changes have been made recently. For example, one of the indexes of the base table has been deleted.
Do you see that the user session is doing any work when it looks like it is hanging?
do you mean status of active or inactive in Instance Manager?
Also, I just found out this is happening in more than one view, I am going to go and look for any tables or indexes that the views share.
I don't manage by the GUI, I couldn't answer this question intelligently.
Run UTLBSTAT/UTLETSTAT, and you can find out where the wait occurs.
Could one of the underlying structures be locked?
It may not be applicable in your case but I've found that Toad (a db development tool) will sometimes aquire locks on things that have been browsed and not let them up until the program is closed, even if the user isn't looking at or using the information any more. This effectivly halts any selects/dml on the table, I can't really say why it happens. It's actually been a while since it happened, maybe the newer freeware release fixed it, but possibly you're experiencing something similar?
In the alrt.log it said there were errs in a .trc file yesterday, in that file it says this( I am going to bring the server down and reboot the box(Novell server) and hope it was a memory problem and that it will all be fine when it comes back up. *crossing fingers*
*** SESSION ID:(94.1727) 2000.12.05.12.41.31.000
FATAL ERROR IN TWO-TASK SERVER: error = 12571
Tue Dec 5 12:41:31 2000 -
ksedmp: internal or fatal error
Current SQL statement for this session:
UPDATE "PRODUCTION"."CMS_CONTRACT" SET "IS_UNDERWRITER_APPROVED"=:C1 WHERE "OBJECT_KEY" = :C2 AND "OBJECT_CLASS_NAME" = :C3 AND "CREATION_DATE" = :C4 AND "EMP_CREATE_KEY" = :C5 AND "MODIFICATION_DATE" = :C6 AND "EMPLOYEE_MOD_KEY" = :C7 AND "CONTRACT_NUMBER" = :C8 AND "CONTRACT_NAME" = :C9 AND "EFFECTIVE_DATE" = :C10 AND "RENEWAL_DATE" = :C11 AND "TERMINATION_DATE" IS NULL AND "CLIENT_KEY" = :C12 AND "CLIENT_ADDRESS_KEY" = :C13 AND "CLIENT_REFERENCE_NUMBER" = :C14 AND "SOURCE_KEY" = :C15 AND "SOURCE_ADDRESS_KEY" = :C16 AND "SOURCE_REFERENCE_NUMBER" = :C17 AND "CONTRACT_STYLE" = :C18 AND "PARTICIPATION_PERCENT" = :C19 AND "UNDERWRITER_KEY" = :C20 AND "ADMIN_KEY" = :C21 AND "OFFICE_LOCATION" = :C22 AND "ORIG_CONTRACT_KEY" = :C23 AND "IS_BILL_CLIENT" = :C24 AND "IS_BILL_SOURCE" = :C25 AND "IS_RETRO" IS NULL AND "IS_CLIENT_RETRO" = :C26 AND "IS_FRONT_CONTRACT" = :C27 AND "IS_RENEWED_CONTRACT" = :C28 AND "IS_CONTINUOUS" = :C29 AND "RENEWAL_DAYS" = :C30 AND "UNDERWRITTER_NOTICE_DAYS" = :C31 AND "IBNR_SCHEDULE" = :C32 AND "POLICY_HOLDER_STATE" IS NULL AND "AGENT_CODE" IS NULL AND "CALCULATION_TYPE" = :C33 AND "CLASSIFICATION" = :C34 AND "WANG_TREATY_NUMBER" IS NULL AND "IS_DELINQUENT" = :C35 AND "IS_BILLABLE" IS NULL AND "IS_CLOSED" = :C36 AND "IS_UNDERWRITER_APPROVED" = :C37 AND "MGT_COMPANY" = :C38 AND "PRODUCER_KEY" = :C39 AND "COUNTRY_CODE" IS NULL AND "SIC_CODE" IS NULL AND "CONTRACT_BASIS" IS NULL AND "DATE_INSERTED" = :C40 AND "DATE_TERM_ENTERED" IS NULL AND "PRINT_DAILY" IS NULL