select sum(d.bytes) "undo" from v$datafile d, v$tablespace t, dba_tablespaces s where s.contents = 'UNDO' and s.status = 'ONLINE' and t.name = s.tablespace_name and d.ts# = t.ts#; select d.undo_size/(1024*1024) "Current UNDO SIZE", SUBSTR(e.value,1,25) "UNDO RETENTION", (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "Necessary UNDO SIZE" 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'; select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME, to_char(end_time,'hh24:mi:ss') END_TIME, maxquerylen,nospaceerrcnt,tuned_undoretention from v$undostat; ## select s.sid, s.username, t.used_urec, t.used_ublk from v$session s, v$transaction t where s.saddr = t.ses_addr order by t.used_ublk desc; select s.sid, t.name, s.value from v$sesstat s, v$statname t where s.statistic# = t.statistic# and t.name = 'undo change vector size' order by s.value desc; select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sql where t.addr = s.taddr and s.sql_id = sql.sql_id and s.username ='&USERNAME'; select a.inst_id, a.sid, c.username, c.osuser, c.program, b.name, a.value, d.used_urec, d.used_ublk from gv$sesstat a, v$statname b, gv$session c, gv$transaction d where a.statistic# = b.statistic# and a.inst_id = c.inst_id and a.sid = c.sid and c.inst_id = d.inst_id and c.saddr = d.ses_addr and b.name = ‘undo change vector size’ and a.value > 0 order by a.value; ## select * from (select a.tablespace_name, sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, (select sum(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024) free_mb from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); ## select se.sid, se.username, su.blocks * ts.block_size / 1024 / 1024 mb_used, su.tablespace, su.sqladdr address, sq.hash_value, sq.sql_text from v$sort_usage su, v$session se, v$sqlarea sq, dba_tablespaces ts where su.session_addr = se.saddr and su.sqladdr = sq.address (+) and su.tablespace = ts.tablespace_name; select se.sid, se.username, se.osuser, pr.spid, se.module,se.program, sum (su.blocks) * ts.block_size / 1024 / 1024 mb_used, su.tablespace, count(*) sorts from v$sort_usage su, v$session se, dba_tablespaces ts, v$process pr where su.session_addr = se.saddr and se.paddr = pr.addr and su.tablespace = ts.tablespace_name group by se.sid, se.serial#, se.username, se.osuser, pr.spid, se.module, se.program, ts.block_size, su.tablespace; ## select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id from gv$sesstat a,gv$statname b,gv$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name='opened cursors current' select saddr, sid, user_name, address,hash_value,sql_id, sql_text from gv$open_cursor where sid in (select sid from v$open_cursor group by sid having count(*) > &threshold); ## set serveroutput on declare mycase clob; begin dbms_sqldiag.export_sql_testcase (directory =>'TEST_DIR1', sql_text => 'select * from my_mv where max_amount_sold >100000 order by 1', user_name => 'SH', exportData => TRUE, testcase => mycase ); end; / begin dbms_sqldiag.import_sql_testcase (directory=>'TEST_DIR2', filename=>'oratcb1_008602000001main.xml', importData=>TRUE ); end; /