Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Scripts | Oracle DBA – Tips and Techniques | Page 3
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Redo Log File Switches – By hour of the day

  • Posted by Gavin Soorma
  • On June 18, 2009
  • 0 Comments
prompt prompt "Morning .........." select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18" from v$log_history WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7 group by to_char(first_time,'DD/MON'); prompt prompt Prompt "Evening ........" prompt select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23", to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06" from v$log_history WHERE […]
Read More
 1

Check if block change tracking file is being used

  • Posted by Gavin Soorma
  • On June 18, 2009
  • 0 Comments
select file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks) * 100 as "% read for backup" from v$backup_datafile where incremental_level > 0 and used_change_tracking = 'YES' group by file# order by file#;
Read More
 4

Check for dependant foreign keys before dropping Primary Key

  • Posted by Gavin Soorma
  • On June 18, 2009
  • 0 Comments
SELECT A.owner foreign_owner, a.table_name foreign_table, b.owner primary_owner, b.table_name primary_table from dba_constraints a, dba_constraints b where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R' and b.constraint_type = 'P' and b.table_name = 'DEPT';
Read More