List indexes on large tables, with listagg to show the indexed columns in a comma separated line, and outer join to include tables with no indexes: SELECT t.owner , t.table_name , ROUND ( t.blocks * 8 / 1024 / 1024 ) AS gb — assuming each block is default 8kb , t.num_rows , i.index_name , […]
Read More List oracle indexes and their columns with listaggOracle Optimizer doesn’t cope so well with distributed queries, joining to tables in different databases. 1) The leading and/or driving_site hints can dramatically improve distributed query performance. SELECT /*+ leading(tab1) driving_site(tab2) */ … FROM table1 AS tab1 JOIN table2@db2 AS tab2 … Driving_site specifies the database you want doing the bulk of the work, likely […]
Read More Oracle distributed query join over remote database link slow performance leading driving_site hint fixOracle User Group Scotland Conference 2015 1) SQLcl (SQL command line) new enhanced SQL*Plus, easy to output index info, DDL, output/load data in csv, xml. Early adopter (beta) version available for download now, production version expected soon and to be bundled by default into Oracle 12cR2. More at Kris Rice. 2) Relax and Recover Martin […]
Read More Oracle User Group Scotland Conference 2015To run this via execute immediate: CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’ ; Either use new style quote format: EXECUTE IMMEDIATE q'[CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’]’ ; Or older style still works also: EXECUTE IMMEDIATE ‘CREATE TABLE t2 AS SELECT * FROM […]
Read More execute immediate quote escape oracle pl/sqlI 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 caseIf you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below. Sample data: DROP TABLE a ; CREATE TABLE a ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ; INSERT INTO a ( id […]
Read More oracle update select mergehttp://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 in