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) exec sys.dbms_system.set_int_param_in_session(&1,&serial,’max_dump_file_size’,999999999) exec sys.dbms_system.set_ev(&1,&serial,10046,8,”) And to switch it off: set pages 9999 verify off col […]

Read More 10046 tracing in another session
February 14, 2007

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 and directory names, and change cp to rcp or scp if cloning to another server. […]

Read More Clone a database
February 9, 2007

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 For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.

Read More Slow SQL report
February 7, 2007

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 clunky text display that it had in version 7 and below. Running the installer in […]

Read More Silent installer on unix still needs X windows until 10g
February 2, 2007

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 be used to identify what objects to drop prior to datapump, for reasons explained in […]

Read More Using 10g datapump and scheduler to copy schemas
February 1, 2007