Category Archives: mysql

oracle – apply query to multiple tables

Let’s say you want to grant rights on a set of tables to a given user, here is how to do it: begin for c in ( select ‘grant select on ‘||owner||’.’||table_name||’ to newuser’ cmd from all_tables where owner = ‘myowner’ and table_name like ‘TABLE_NAME_%’ ) loop execute immediate c.cmd; end loop; end; / Note: same logic can be extended to apply any type of query to multiple tables

oracle – how to dump query output into csv file via cli [solved]

A way to do it is to use sqlplus script. create file to execute with below content (vi my_query.sql): SET ECHO OFF; SET LINESIZE 32767; SET TAB OFF; SET NEWPAGE NONE; SET PAGESIZE 32767; SET LONG 100000000; SET HEADING ON; SET WRAP OFF; SET SCAN OFF; SET FEEDBACK OFF; SET SERVEROUTPUT ON; SET HEADSEP OFF; SET PAGESIZE 0; SET TRIMSPOOL ON; SET COLSEP ‘,’; ALTER SESSION SET NLS_DATE_FORMAT=’YYYYMMDD’; SPOOL /path/to/my_query.out <paste_sql_query_here> COMMIT; EXIT; call sqlplus as per below: $ORACLE_HOME/bin/sqlplus -S -l <user/password@server> @<path/to/my_query.sql> That’s it! Ouptut of run should be available under /path/to/my_query.out as specified in SPOOL variable. Tip: run SPOOL OFF; to turn […]

python 3.5 – install oracle mysql driver on ubuntu [solved]

Installing this mysql driver is surprisingly quite a painful experience. Steps Download package relevant for your distribution and python version from oracle website: Install downloaded package: sudo dpkg -i mysql-connector-python-py3 _2.1.3-1ubuntu15.04_all.deb Go into python and run “import mysql” For all virtualenvs, simply symlink: ln -s /usr/lib/python3/dist-packages/mysql <path_to_virtual_env>/lib/pythonX.X/site-packages It should be good by now.

oracle – truncate tables based on select query

PL/SQL allows to execute sql statements using a FOR loop, looping through results of another query. In below example we truncate all tables returned by another query: — Q1: empty all audit tables –/ BEGIN — Disable constraints — Truncate tables returned by SELECT query FOR entry IN ( SELECT table_name FROM user_tables WHERE table_name like ‘%AUDIT%’ — and rownum < 3 order by table_name ) LOOP –EXECUTE IMMEDIATE ‘select * from ‘ || entry.table_name || ‘ where rownum < 10’; EXECUTE IMMEDIATE ‘truncate table ‘ || entry.table_name; END LOOP; — Enable constraints END; / Note: make sure to enclose […]

how to setup cx_Oracle on python – windows [solved]

When trying to use cx_Oracle in python on windows, I faced following error: >>> import cx_Oracle Traceback (most recent call last): File “<stdin>”, line 1, in <module> ImportError: DLL load failed: The specified module could not be found. In my case, oracle instant client was not installed. Here is how to solve it. Steps Download and unzip oracle instant client appropriate for your configuration: note: on my side i downloaded basic client + odbc + jdbc + sqlplus Ensure environment variables ORACLE_HOME and PATH points to instant client folder note: in windows, go to control panel > System > […]

oracle – lead/lag functions returning inconsistent results [solved]

When using oracle lead() and lag() functions, I faced “inconsistent” results where running same query twice was returning different values for lead()/lag() calls. select reference_id, field1, lead(field1 1, 0) over (partition by col1, col2 order by STATUS_TIME_LONG)  next_val; This simply came from fact that my sorting key was not unique! Though STATUS_TIME_LONG recoreded entry timestamp in milliseconds, several entries within my grouping still shared same timestamp value for this field. Solution to get consistent output for lead/lag functions is simply to ensure unique-id field is part of sorting key. In my case, field reference_id is unique auto-incrementing field, so  perfect for […]

[oracle] steps to connect to oracle 11g via sqlplus

Steps: ensure variable ORACLE_HOME points to oracle installation path: echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/xe if not set, set variable to appropriate path via: export ORACLE_HOME=<path_to_oracle> ensure variable ORACLE_SID is set up to correct value (XE in this case): echo $ORACLE_SID XE if not set, set variable to appropriate path via: export ORACLE_HOME=<path_to_oracle> connect as admin user via: $ORACLE_HOME/bin/sqlplus sys as sysadmin <enter password> That should be it. Note: to permanently add env variables, simply add them to ~/.bashrc as per below: export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export ORACLE_OWNR=oracle export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=XE Upon new login, such variable will be loaded automatically

oracle – convert date to unix timestamp and vice versa

Convert date to unix timestamp (milliseconds): (what a pain!!! incredible there is no native oracle function in 11g) select extract(day from (systimestamp – timestamp ‘1970-01-01 00:00:00’)) * 86400000 + extract(hour from (systimestamp – timestamp ‘1970-01-01 00:00:00’)) * 3600000 + extract(minute from (systimestamp – timestamp ‘1970-01-01 00:00:00’)) * 60000 + extract(second from (systimestamp – timestamp ‘1970-01-01 00:00:00’)) * 1000 unix_time from dual; Warning: this function does not take into account timezone delay Convert unix timestamp (milliseconds)  to date: select TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’) + <unix_timestamp> / 86400000 from dual; sources

oracle – “DUAL” table

In oracle, there is a special table called “DUAL” which contains only one column and one row. It’s basically used to return queries with single select statement to return current system date for instance, as per below: select sysdate from dual; Should you run “select * from dual”, output is single row, single column called DUMMY with value X. sources

[oracle] 11g – import data with impdp

Approach is as follow: create directory object inside oracle grant import rights perform import step by step login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object pointing to folder where dump is located: create directory db_dumps as ‘/u01/app/oracle/dbs_to_load’; grant read,write on directory db_dumps to <dbuser>; grant import rights to db user: grant create any directory to <dbuser>; grant imp_full_database to <dbuser>; import dump file: $ORACLE_HOME/bin/impdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log remap_schema=<old_schema>:<new_schema> remap_tablespace=<old_schema>:<new_schema> note: replace values between <xxx> with your own values Importing with data filtering Data filtering can be added using QUERY=<table_name>:”<where clause>” When using […]