I used this SQL to convert an existing non-partitioned table to be partitioned. Table was mostly queried on snapshot_id=’Current’, which had the most up to date data, but had much more historic data that was rarely queried against, all flagged with snapshot_id = ‘YYYYMMDD’ format. — 1) Create an empty table with one partition CREATE […]
Read More Partition an existing oracle table exampleConvert date duration (here customer age at time of first application) in years and months, e.g. ‘3408’ for 34 years and 8 months old: WITH customers AS ( SELECT SYSDATE AS appl_date , TO_DATE ( ’01-AUG-1980′ , ‘DD-MON-YYYY’ ) AS date_of_birth FROM DUAL ) SELECT TO_CHAR ( GREATEST ( 0 , LEAST ( 99 , […]
Read More SQL to display date duration as number of years and monthsRebuilds are beneficial in some rare circumstances such as after a mass delete operation. The below method is safer and faster than export/import. Example table: CREATE TABLE tab1 ( col1 VARCHAR2(1) , col2 CLOB ) TABLESPACE ts1 ; CREATE INDEX tab1i ON tab1 ( col1 ) TABLESPACE ts1 ; List objects and tablespaces: SELECT tablespace_name […]
Read More Rebuild table indexes lobs in oracleSo I needed to transform this source data: Into this output format: Seems simple, but required a combination of CASE, LAG, and ROW_NUMBER – as Tom Kyte steps through in detail in this article from 2004. Here is the solution: SELECT q5.event_category , q5.num , MIN ( q5.week ) AS start_week , MAX ( q5.week […]
Read More Oracle data transform using analytic lag row_number casehttp://superuser.com/questions/616206/run-interactive-task-even-if-user-is-not-logged-on-windows?rq=1 Problem arises when a windows scheduled task needs to fire up a GUI – if there is no session logged in then it fails to run because has nowhere to display the GUI. Caveat: I haven’t tried this solution myself yet, don’t know if there are any unpleasant side effects from it or not.
Read More Run Windows interactive scheduled task even if user not logged inTo transfer data from MySQL into Oracle 1) Best method is to use a database link. That way the MySQL table is available in the Oracle database for use in queries, joins, DML, PL/SQL. Simple guides on how to do to that are: Shishir Tekade http://www.shishirtekade.com/2014/01/connect-oracle-to-mysql-server-through.html Pythian http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/ My Oracle Support 1320645.1 ‘Detailed Overview of […]
Read More Data from MySQL to Oracle with dg4odbc database link or mysqldumpLet’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below. The second method will fire the combined select just the once, picking up all 11m rows in one go. The first method will fire the child select 10m times […]
Read More Analytics row_number to identify first parent row in oracle pl/sql cursor loopThe substrb 1 , 4000 function will chop a string down to its first 4000 bytes, e.g.: SELECT SUBSTR(‘1€3’,2,1) , SUBSTRB(‘1€3’,1,3) , LENGTH(‘1€3’) , LENGTHB(‘1€3′) FROM dual ; Needed if using the (non default) option of ‘char’ in column lengths: CREATE TABLE mytable ( col1 VARCHAR2(4000 CHAR) ) ; You can change the default setting […]
Read More oracle substrb function to chop a string down to its first 4000 bytesShell script to make more readable all sql files in a directory – pads commas and brackets with whitespace, converts tabs to spaces, makes SQL keywords uppercase. # FUNCTIONS pad () { sed -i “s/\([^ ]\)$1/\1 $1/g” *sql sed -i “s/$1\([^ ]\)/$1 \1/g” *sql } upper () { sed -i “s/\(\\)/\U\1/g” *sql } # MAIN […]
Read More Format sql files with sed: tab to space, pad commas with spaces, uppercase matchPL/SQL bulk collect will work with UNION ALL when written with an inline view: DECLARE TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ; l_big l_type_assoc ; BEGIN WITH q AS ( SELECT ename AS name FROM emp UNION ALL SELECT dname AS name FROM dept ) SELECT name BULK COLLECT INTO l_big FROM […]
Read More PL/SQL bulk collect union