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