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 …
Category: Database
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;
Oracle Fast Parallel Insert
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 …
Oracle Analytic Function
–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 …
Oracle remove double / multiple spaces
SELECT TRIM(REGEXP_REPLACE(<str>,’ +’,’ ‘)) AS compressed_str FROM <TABLE>
Get oracle table size
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 …