Show locks in a Netezza Database

This SQL statement when run in Aginity or nzsql will show you all sessions that cause locks in a database.

select sessionid, 
       clientip,
       username, 
       relname as table_name, 
       requesttime as lock_request_time,
       granttime as lock_acquired_time, 
       command as sql, 
       lockstate  
from _t_pg_locks 
where relid in ( select relid from _t_pg_locks where lockstate='WAIT') 
order by requesttime, granttime

To kill a session / transaction you can simply right click it under current sessions in Netezza Administrator.

Unlock APEX admin user (when apxchpwd.sql doesn't work)

First we need to find the Apex admin accounts user_id. We do that with the following statement.
You might have two. Mine was the second one

select user_id from APEX_040200.WWV_FLOW_FND_USER where user_name = 'ADMIN' order by last_update_date desc;

Set a new password for the Apex Admin with the user_id we selected.

update APEX_040200.WWV_FLOW_FND_USER
set web_password = 'password'
where user_name = 'ADMIN'
and user_id = 56502607595642;
commit;

Then we unlock the Admin account with this statement.

alter session set current_schema = APEX_040200;
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
commit;
end;

Install Oracle Data Modeler 4.0 on Ubuntu 14.04

This little HOW-TO will explain how to install Oracle's Data Modeler 4.0 on Ubuntu 14.04
First download the Oracle Data Modeler 4 rpm from Oracle's website http://www.oracle.com

Install alien to be able to convert .rpm packages to .deb

sudo apt-get install alien

Convert and install the Data Modeler 4 .rpm
This way you will be able to link the program on the launcher

sudo alien --scripts datamodeler-4.0.*.rpm 
sudo dpkg -i datamodeler_4.0.*.deb 

Fix an install bug and the startscript for Data Modeler so it won't crash on startup
Edit /usr/local/bin/datamodeler and add:
unset -v GNOME_DESKTOP_SESSION_ID to the first line in the start script.

sudo touch /opt/datamodeler/datamodeler/types/defaultdomains.xml
sudo chown username.username /opt/datamodeler/datamodeler/types/defaultdomains.xml
sudo vi /usr/local/bin/datamodeler

Done! Now you can find and start it in unity!

Install Oracle SQL Developer 4.0 on Ubuntu 14.04

This little HOW-TO will explain how to install Oracle's SQL Developer 4.0 on Ubuntu 14.04
First download the Oracle SQL developer 4 rpm from Oracle's website http://www.oracle.com

Install alien to be able to convert .rpm packages to .deb

sudo apt-get install alien

Convert and install the SQL developer 4 .rpm
This way you will be able to link the program on the launcher

sudo alien --scripts sqldeveloper-4.0.*.noarch.rpm 
sudo dpkg -i sqldeveloper_4.0.*.deb 

Fix the startscript for SQL developer so it won't crash on startup
Edit /usr/local/bin/sqldeveloper and add:
unset -v GNOME_DESKTOP_SESSION_ID to the first line in the start script.

sudo vi /usr/local/bin/sqldeveloper

Done! Now you can find and start it in unity!

List current active users, sessions and programs in Oracle 11g

List current users, sessions and programs
(not background processes)

SELECT username, sid, serial#, program
FROM v$session
WHERE username is NOT NULL
ORDER BY logon_time;

Remove there WHERE clause if you want to include all background processes like pmon.

How to get command-line history in SQL*PLUS with rlwrap

sql*plus does not have a command-line history function under Linux and Unix.
rlwrap is a readline wrapper for shell commands which uses input from the controlling terminal.
It adds a persistent input history for each command and supports user-defined completion.

You can install it via yum if you have enabled the EPEL repository.
Or you can download the sources from here
Then open a terminal and go to the catalog you downloaded it to

If you have the EPEL repository enabled
Open a terminal

su - root
yum install rlwrap

Otherwise you do like this. Again in the folder you downloaded the file to.
Open a terminal

su - root
tar -zxvf rlwrap-0.37.tar.gz
./configure
make
make install

Now you can call sqlplus like this:

rlwrap sqlplus user/password@sid

I also recommend creating an alias like this

alias sqlp =”rlwrap sqlplus”

Now you can start the wrapped sqlplus with sqlp and have command history with the ↑ and ↓ keys on your keyboard.