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;)
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
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= …
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;
Use insert append hint for fast inserts. Almost as fast as bulk load. alter session enable parallel dml; alter index <INDEX> unusable; INSERT /*+ append parallel (<ALIAS1>) */ INTO <TABLE1> <ALIAS1> ( COL1, COL2 ) SELECT /*+ parallel(ALIAS2) */ COL1, COL2 …
–USE ANALYTIC FUNCTION TO DELETE ALL BUT ORIGINAL delete from <TABLE_NAME> where (key1, ins_timestamp) in (select key1, ins_timestamp from ( select key1, ins_timestamp, upd_timestamp , row_number() over (partition by key1 order by ins_timestamp) as rownumber from <TABLE_NAME> ) t1 where …
SELECT TRIM(REGEXP_REPLACE(<str>,’ +’,’ ‘)) AS compressed_str FROM <TABLE>
http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle –first gather stats for table… begin dbms_stats.gather_table_stats(‘MYSCHEMA’,’MYTABLE’); end; –or schema… begin dbms_stats.gather_table_stats(‘MYSCHEMA’); end; –get table size select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB, num_rows, avg_row_len, last_analyzed from all_tables where owner = ‘<WHOEVER>’ and table_name in (‘<WHATEVER>’) order by MB desc Also …
Issue: Trying to issue an EXECUTE IMMEDIATE command from within a PL/SQL procedure and geting: ORA-01031: insufficient privileges while issuing an EXECUTE IMMEDIATE. The same EXECUTE IMMEDIATE command works from within an anonymous PL/SQL block. The procedure and the anonymous block …