Category: Uncategorized

Oracle AwrExtr cron to export backup AWR and ASH data

Oracle supplies the awrextr and awrload scripts to backup and copy AWR and ASH data by datapump. Those are designed to work interactively, prompting for user input. Script below will run awrextr from cron, without the interactive user input. #!/bin/bash

Posted in Uncategorized

Oracle Foreign Key Constraints with missing indexes

Thomas Gutzmann from Tom Kyte script to identify foreign key constraints with missing indexes works in only one schema. I had the problem of having two tightly integrated schemas with referential constraints going back and forth. The below version of

Posted in Uncategorized

Oracle select earliest and latest rows using keep group or first_value analytics

There are two ways of selecting the earliest and latest rows for each customer. 1) Keep group: SELECT t1.customer_ref , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date ASC ) AS earliest_order_amount , MAX ( t1.order_amount )

Posted in Uncategorized

Windows PowerShell run script on all SQL Servers recovery mode

Micrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases. To check all databases in a SQL Server instance, run the below script in a SQL

Posted in SQL server, Uncategorized

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

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

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

SQL to display date duration as number of years and months

Convert date duration (here customer age at time of first application) in years and months, e.g. ‘3408’ for 34 years and 8 months old: WITH customers AS ( SELECT SYSDATE AS appl_date , TO_DATE ( ’01-AUG-1980′ , ‘DD-MON-YYYY’ ) AS

Posted in Uncategorized