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

Display Oracle SQL Developer XMLTYPE

Do you see annoying (XMLTYPE) in Oracle SQL Developer output? Wrap the column in the xmlType.getClobVal function to see the actual data. WITH q AS ( SELECT XMLTYPE ( ” ) AS col1 FROM DUAL ) SELECT q.col1 , XMLTYPE.GETCLOBVAL ( q.col1 ) FROM q ;

Read More Display Oracle SQL Developer XMLTYPE
October 10, 2016

One Comment

TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But: Need access to database server user_dump_dest directory. Has to be switched on in advance Has a performance impact, especially if tracing multiple sessions or tracing for a long time. And ASH is a good enough alternative much of the time. The […]

Read More TKProf by ASH
October 6, 2016

What to do if a database performance issue is reported

A high level overview. Expectation should be for all OLTP operations to take less than 1 second, and all batch processing jobs and background scheduled report jobs to take less than 1 hour each. This is deliverable on even large databases with moderate hardware, with some effort put in. 1) Is the issue even a […]

Read More What to do if a database performance issue is reported
December 14, 2015

Rman list most recent backups

List most recent rman database backups on target database sqlplus with: set pages 9999 lines 132 ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT b.checkpoint_time , b.incremental_level , COUNT(*) FROM v$backup_datafile b JOIN v$datafile f ON f.file# = b.file# JOIN v$tablespace ts ON f.ts# = ts.ts# WHERE ts.included_in_database_backup = ‘YES’ AND b.checkpoint_time > […]

Read More Rman list most recent backups
November 23, 2015