saya pake syntaks querynya : SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'
sumber : http://www.akadia.com/services/ora_optimize_undo.html --- Pada Jum, 15/5/09, Leonard Sukendro <[email protected]> menulis: Dari: Leonard Sukendro <[email protected]> Topik: Re: [indo-oracle] Error ORA 1555 Kepada: [email protected] Tanggal: Jumat, 15 Mei, 2009, 2:44 AM to pak teguh, waduh, klo utk cari settingan undo retention yg pas, saya jg bingung, pak hehe... klo setau saya, supaya gk kluar "snapshot too old", undo retentionnya msti d kecilin, tp klo undo retention nya kekecilan, long processes bakalan error :(... mungkin perintah "alter tablespace resize datafile" itu bs dcoba, pak... saya ndiri dulu pake perintah tp utk sizenya ms pake cara kuno, trial n error hehe btw, mo gantian nanya, gmana cara ngeluarin hasil "actual undo size" n "need undo size" nya itu ya, pak? :D koq saya query dr v$undostat, hasilnya bkn gtu? thx... CMIIW, [L] --- On Fri, 5/15/09, Teguh Susanto <teguh4milis@ yahoo.com> wrote: From: Teguh Susanto <teguh4milis@ yahoo.com> Subject: Re: [indo-oracle] Error ORA 1555 To: indo-oracle@ yahoogroups. com Date: Friday, May 15, 2009, 11:06 AM makasih pak leon atas sharenya... he3 kebetulan sy ini programmer&ngarangk ap admin.... tadi sy coba ALTER SYSTEM SET UNDO_RETENTION = 1200 ini setelah sy query table v$undostat nya ACTUAL UNDE SIZE = 2048.00390625 UNDO RETENTION = 1200 NEED UNDO SIZE = 4.921875 apakah sy harus meng alter : alter tablespace undo_ts_01 add datafile ‘/u006/oradata/ SID1/undo_ ts_01_02. dbf’ <sesuai NEED UNDO SIZE nya?> --- Pada Kam, 14/5/09, Leonard Sukendro <leon_190699@ yahoo.com> menulis: Dari: Leonard Sukendro <leon_190699@ yahoo.com> Topik: Re: [indo-oracle] Error ORA 1555 Kepada: indo-oracle@ yahoogroups. com Tanggal: Kamis, 14 Mei, 2009, 10:33 PM to pak teguh, dl saya prnah punya mslh yg sama, n untungnya bs solve stlh saya extend undo tablespace nya... utk tau ukuran idealnya, mungkin bs d monitor undo usagenya pas proses2 gede itu... ato klo gk bisa, minta tlong programmernya utk commit lbh sering bila ada DML yg besar... misal, commit per-500 record... moga2 bisa membantu, pak... CMIIW, [L] --- On Fri, 5/15/09, Teguh Susanto <teguh4milis@ yahoo.com> wrote: From: Teguh Susanto <teguh4milis@ yahoo.com> Subject: [indo-oracle] Error ORA 1555 To: indo-oracle@ yahoogroups. com Date: Friday, May 15, 2009, 8:54 AM kepada miliser&senior DBA mau minta tolong nich, gimana yach ngatasi error ORA 1555-snapshot too old: rollback segment number setiap sy melakukan query dgn join,union dan transaksi yg banyak pasti error tsb muncul Lebih bersih, Lebih baik, Lebih cepat - Yahoo! Mail: Kini tanpa iklan. Rasakan bedanya! http://id.mail. yahoo.com [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] Menambah banyak teman sangatlah mudah dan cepat. Undang teman dari Hotmail, Gmail ke Yahoo! Messenger sekarang! http://id.messenger .yahoo.com/ invite/ [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ___________________________________________________________________________ Nama baru untuk Anda! Dapatkan nama yang selalu Anda inginkan di domain baru @ymail dan @rocketmail. Cepat sebelum diambil orang lain! http://mail.promotions.yahoo.com/newdomains/id/ [Non-text portions of this message have been removed]
