Uncategorized – Page 2 – Andrew Fraser DBA

Fix for datapump import ORA-01658: unable to create initial extent for segment in tablespace

Datapump errors: ORA-39171: Job is experiencing a resumable wait. ORA-01658: unable to create INITIAL extent for segment in tablespace If doing an import including data, then fix is obvious: just need to add more space. But if doing a metadata only import to bring over empty tables, you don’t want to waste a lot of […]

November 14, 2017
Posted in Uncategorized

Fix for ORA-00997: illegal use of LONG datatype for CTAS dba_triggers dba_tab_cols

CTAS doesn’t work for columns of LONG datatype – fails with error ORA-00997: illegal use of LONG datatype. That’s irritating if you want to make a copy of dictionary views like dba_triggers, dba_tab_cols, etc – and a copy like that can be very useful for checking everything is the same as you expect after some […]

November 14, 2017
Posted in 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 # 1) Set environment variables export ORACLE_SID=myOracleSID export PATH=$PATH:/usr/local/bin export ORAENV_ASK=NO . oraenv # 2) […]

March 10, 2017
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 their script handles multiple schemas. It also displays the size of the tables involved, because […]

March 10, 2017
Posted in Uncategorized
One Comment

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 ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date DESC ) AS latest_order_amount FROM t1 WHERE t1.order_amount […]

November 6, 2015
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 command window. Optionally uncomment the EXEC line to have this script switch databases to full […]

November 5, 2015
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 , s.osuser , s.program || ‘ ‘ || s.module AS program , s.status , ROUND ( […]

October 27, 2015
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 can switch that behaviour off with Tools > Settings > Show Display name. Microsoft Remote […]

October 17, 2015
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 trigger is needed: CREATE OR REPLACE TRIGGER tu_mytable BEFORE UPDATE ON mytable FOR EACH ROW […]

October 16, 2015
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’]’ ; Or older style still works also: EXECUTE IMMEDIATE ‘CREATE TABLE t2 AS SELECT * FROM […]

April 25, 2015
Posted in Uncategorized
One Comment