Author: Andrew Fraser

Oracle top session info in RAC with tracefile name

See real time top session info in either RAC or standalone database, including tracefile name qualified by instance name, with: — ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username ,

Posted in Performance tuning, Uncategorized

10046 trace a module using logon trigger

1) Update – thanks to Joaquin Gonzalez for pointing out a preferable safer method than logon trigger for 10046 tracing a particular module: EXEC DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => ‘myservice’ , module_name => ‘myModule.exe’ , binds => TRUE ) You can

Posted in Performance tuning

mtputty directory path title

MtPuTTY multi tabbed putty is an improved variant of putty ssh client, easier to use if working on several servers at the same time. However it has an annoying tendency to display long directory paths in each tab title. You

Posted in Uncategorized

Oracle date changed user changed columns and batch performance

Most core application tables benefit from columns like these: ALTER TABLE mytable ADD ( date_created DATE DEFAULT SYSDATE , user_created_by VARCHAR2(30) DEFAULT USER , date_changed DATE , user_changed_by VARCHAR2(30) ) ; The default values deal with inserts, for updates a

Posted in Uncategorized

Bash script to check for final closing /data xml tag in file

Bash script to check for final closing </data> xml tag in file: if [ -f file.xml ] then # if grep -1iq ‘</data>’ file.xml # simplest syntax # if tac file.xml | grep -1iq ‘</data>’ # faster syntax (tac is

Posted in Linux

List oracle indexes and their columns with listagg

List indexes on large tables, with listagg to show the indexed columns in a comma separated line, and outer join to include tables with no indexes: SELECT t.owner , t.table_name , ROUND ( t.blocks * 8 / 1024 / 1024

Posted in Performance tuning

Oracle distributed query join over remote database link slow performance leading driving_site hint fix

Oracle Optimizer doesn’t cope so well with distributed queries, joining to tables in different databases. 1) The leading and/or driving_site hints can dramatically improve distributed query performance. SELECT /*+ leading(tab1) driving_site(tab2) */ … FROM table1 AS tab1 JOIN table2@db2 AS

Posted in Performance tuning

Oracle User Group Scotland Conference 2015

Oracle User Group Scotland Conference 2015 1) SQLcl (SQL command line) new enhanced SQL*Plus, easy to output index info, DDL, output/load data in csv, xml. Early adopter (beta) version available for download now, production version expected soon and to be

Posted in Oracle User Group, Performance tuning

execute immediate quote escape oracle pl/sql

To run this via execute immediate: CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’ ; Either use new style quote format: EXECUTE IMMEDIATE q'[CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’]’ ;

Posted in Uncategorized

Partition an existing oracle table example

I used this SQL to convert an existing non-partitioned table to be partitioned. Table was mostly queried on snapshot_id=’Current’, which had the most up to date data, but had much more historic data that was rarely queried against, all flagged

Posted in Uncategorized