Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used. Run this code to see the anomaly for yourself: set serverout on declare var1 number ; begin select […]
Read More Bug? with wrong results from all_objects in stored plsql proceduresTo 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 sessionThe 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 databaseLists 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 reportIt’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 10gUpdate 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 schemasSQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least not intuitive enough for me 🙁 To fix them after the event, see http://www.campus.ncl.ac.uk/databases/sqlser/SQL2005/setupsql2005.html and […]
Read More SQL Server 2005The Monty Hall Paradox can’t be right, or so I thought. Suppose you’re on a game show, and you’re given the choice of three doors: Behind one door is a car; behind the others, goats. You pick a door, say No. 1, and the host, who knows what’s behind the doors, opens another door, say […]
Read More Of Goats and Porsches: A Monty Hall Paradox SimulatorSuspect too much auditing is on on a database? This script will show you what auditing is on: set pages 9999 select * from DBA_STMT_AUDIT_OPTS ; select * from DBA_PRIV_AUDIT_OPTS ; set termout off lines 192 spool 1.tmp select * from DBA_OBJ_AUDIT_OPTS ; spool off set termout on lines 80 host egrep ‘(S/|A/|/S|/A)’ 1.tmp Note […]
Read More Identify what auditing is on