Grant permissions to all tables in schema
BEGIN FOR TAB_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = ‘‘ ) LOOP EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘ || TAB_NAME.TABLE_NAME || ‘ TO ‘; END LOOP; END;
Category: Oracle
Oracle: Find tables referring to another
select table_name, constraint_name, status, owner from all_constraints where r_constraint_name in (–get primary and unique constraints on the target table. These can be used for FK references by other tables select constraint_name from all_constraints where constraint_type in (‘P’, ‘U’) and table_name …
Oracle QUOTE function: Use to avoid complicated escape character sequences
Rather than fret about how to escape 2 single quotes for example, just use the quote function and native text: QUOTE: q'{}’; vSQL := q'{ALTER TABLE DIM_ISSUE ADD (CONSTRAINT DIM_ISSUE_C9 CHECK (length(regexp_replace(UPPER(issue_name),'[ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_]’,”)) = 0 AND regexp_like(issue_name,’^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+.*$’)))}’; EXECUTE IMMEDIATE(vSQL;)
Oracle: Drop table / column if it exists
DECLARE table_exists NUMBER :=0; –column_exists NUMBER :=0; BEGIN –Drop table / column if it exists SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = ‘‘; –SELECT COUNT(*) INTO column_exists ALL_TAB_COLUMNS WHERE OWNER = ‘‘ AND TABLE_NAME = ‘‘ AND COLUMN_NAME …
XMLAgg to create CLOB
Use XmlAgg function to build up a dynamic string over the 4000 character limit, by using .getClobVal() to create a CLOB string –get rid of the final UNION ALL select regexp_replace ( nSQL, ‘(^.*) UNION ALL $’, ‘\1’ ) vSQL …
Oracle SQLPlus
Question: How to connect to sqlplus? Answer: Launch cmd window and enter: sqlplus user/password@server Question: How do I execute a SQL script file in SQLPlus? Answer: To execute a script file in SQLPlus, type @ and then the file name. …
Oracle DBMS_METADATA
Built in package to pull out create statements. Can be used to copy a table along with indexes and constraints etc: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm
Oracle: CUBE and ROLLUP aggregations
Use CUBE and ROLLUP to calculate aggregates at many levels in one statement: http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8613 ROLLUP example ============== SELECT channel_desc, calendar_month_desc, countries.country_iso_code, TO_CHAR(SUM(amount_sold), ‘9,999,999,999’) SALES$ FROM sales, customers, times, channels, countries WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND customers.country_id = countries.country_id AND sales.channel_id= …
Oracle script runtime template
set serveroutput on; alter session enable parallel dml; –NB: not possible within partition or on non-partitioned table for delete/update/merge operations alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’; select sysdate as script_start from dual; <STUFF> select to_char(sysdate, ‘YYYY/MM/DD HH:mi:ss’) as script_end from dual;