Re: Query Database while shutdown in progress

Hi All,

I have an idea to achieve the objective need your comments,

Objective: To report time taken for shutdown immediate to complete, while doing rollbacks
Material to use: DB trigger with before shutdown event, utl output

CREATE OR REPLACE TRIGGER before_shutdown BEFORE SHUTDOWN ON DATABASE BEGIN my new challenge is to write this trigger, (oh man... I forgot most of the syntax already)
convert the calculation base on note 117316.1 point 9 with utl output ? The report say shutdown in progress and the is used_ublk * db_block_size to rollback, base on 1 minute 20 blocks this shutdown progress is estimated to required 3 hours to complete.

END; /

According to Note 117316.1 Point 9
select used_ublk from v$transaction

If there is a value there, this is the number of undo blocks used by the transaction. Wait one minute and again select "used_ublk" from "v$transaction" where ADDR=<value from TADDR in v$session>; . Note the value. If it is decreasing, a rollback is occuring and based on the difference between these values, you can "guesstimate" the time required to complete the rollback. For example, if the first query returns a value of 80000 and the second one returns 70000, it took 1 minute to rollback 10000 blocks. Based on this number, you can guestimate the time to complete the rollback. In this case, it would be 7 minutes.

Will this work ? :)

Thanks for your feedback

--
http://www.freelists.org/webpage/oracle-l