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