Monthly Archives: February 2007

Of course this would never happen in the real world

Posted in General musings

10046 tracing in another session

To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large): set pages 9999 verify off col serial new_value serial noprint select serial# serial from v$session where sid = &1 ; exec sys.dbms_system.set_bool_param_in_session(&1,&serial,’timed_statistics’,true)

Posted in Performance tuning, Scripts

Clone a database

The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file

Posted in Rman, Scripts

Slow SQL report

Lists slow SQL in library cache for tuning investigations: set pages 9999 spool c.lst select elapsed_time/1000000 secs, executions, elapsed_time/1000000/greatest(executions,1) secs_per_exec, sql_text from v$sql where executions > 50 and elapsed_time/1000000/greatest(executions,1) > 1 order by 3 desc / spool off ed c.lst

Posted in Performance tuning, Scripts

Silent installer on unix still needs X windows until 10g

It’s not always easy to get X windows properly, or to get hold of X windows emulator software for Windows PCs. So it was a bit of a step backwards when the installer moved away from the ugly but effective

Posted in Installs

Using 10g datapump and scheduler to copy schemas

Update 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below. Update 02-Mar-07: dba_ views rather than all_ views have to

Posted in RAC, Scripts