Oracle grants generate commands

Script to generate commands for object grants: SQL: SELECT ‘GRANT ‘ || privilege || ‘ ON ‘ || LOWER ( owner || ‘.’ || table_name ) || ‘ TO ‘ || LOWER ( grantee ) || CASE WHEN grantable = ‘YES’ THEN ‘ WITH GRANT OPTION’ END || ‘ ;’ AS cmd FROM dba_tab_privs WHERE […]

Read More Oracle grants generate commands
November 14, 2017

listagg xml path Oracle Microsoft Sql Server

If you have a table with rows like this: typeName custName ——– ——– typeA cust1 typeA cust2 typeA cust3 typeB cust1 typeB cust4 typeB cust5 typeB cust6 And you want to display it’s data aggregated like this: typeName COUNT(*) fullListing ——– ——– —————————– typeA 3 cust1 , cust2 , cust3 typeB 4 cust1 , cust4 […]

Read More listagg xml path Oracle Microsoft Sql Server
November 14, 2017

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 […]

Read More Fix for datapump import ORA-01658: unable to create initial extent for segment in tablespace
November 14, 2017

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 […]

Read More Fix for ORA-00997: illegal use of LONG datatype for CTAS dba_triggers dba_tab_cols
November 14, 2017

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) […]

Read More Oracle AwrExtr cron to export backup AWR and ASH data
March 10, 2017

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 […]

Read More Oracle Foreign Key Constraints with missing indexes
March 10, 2017

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 […]

Read More Oracle select earliest and latest rows using keep group or first_value analytics
November 6, 2015

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 […]

Read More Windows PowerShell run script on all SQL Servers recovery mode
November 5, 2015

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 ( […]

Read More Oracle top session info in RAC with tracefile name
October 27, 2015

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 […]

Read More mtputty directory path title
October 17, 2015