Tag Archives: oracle

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 […]

how to set up cx_Oracle on ubuntu [solved]

I tried installing oracle client to use it with sqlalchemy and ended up with a nasty error message: distutils.errors.DistutilsSetupError: cannot locate Oracle include files To setup cx_Oracle so that it can be imported from python, proceed as follow: install libaio.so.1 by running: sudo apt-get install libaio1 download instant client appropriate for your platform from oracle website: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html SDK and ODBC packages must be downloaded too. note: on my side i downloaded: oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm convert into .deb package: sudo alien -d *instantcl*.rpm note: you may need to install alien (sudo apt-get install alien) if not already done install created package: […]

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: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html 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 – 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 http://stackoverflow.com/questions/7147982/oracle-systimestamp-sysdate-to-milliseconds-seconds http://dumplings-blog.logdown.com/posts/178885-oracle-sql-convert-a-datetime-string-into-a-unix-timestamp https://blogs.oracle.com/mock/entry/converting_oracle_dates_to_unix http://dba.stackexchange.com/questions/16461/convert-a-unix-timestamp-to-a-datetime-in-a-view

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 http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle http://en.wikipedia.org/wiki/DUAL_table http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm

[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 […]

[oracle] 11g – export data with expdp

Approach is as follow: create directory object inside oracle grant export rights perform export step by step create physical directory on the drive where dump will be created: sudo mkdir /u01/app/oracle/exported_dbs sudo chown oracle:dba /u01/app/oracle/exported_dbs login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object: create directory db_dumps  as ‘/u01/app/oracle/exported_dbs’; grant read,write on directory db_dumps to <dbuser>; grant export rights to db user: grant create any directory to <dbuser>; grant exp_full_database to <dbuser>; generate dump file: $ORACLE_HOME/bin/expdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log note: replace values between <xxx> with your own values source http://el.web.id/how-to-export-and-import-oracle-database-using-impdp-and-expdp-in-linux-212 (awesome tutorial!) http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1009049 […]