Oracle 11g Rebuild indexes on partitions using ALTER

To rebuild indexes on partitions for a user in Oracle 11g run -

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;

Then run the output from that command

This is a fix for SQL Error: ORA-08102: index key not found, obj#

Converting Unix time to localtime in UNIX with PERL

Convert Unix time since the Jan 1st 1970 00:00 epoch to local time using PERL with this command :

$ perl -e 'print scalar (localtime(1246130142657481/1000000)),"\n"'

Sat Jun 27 20:15:42 2009

Use gmtime instead of localtime for GMT time.

Creating CSR with openssl

Use openssl to create a Certificate Request - csr

openssl genrsa -out blogspot.co.uk.key 2048
openssl req - new blogspot.co.uk.csr -key blogsport.co.uk.key - config.openssl.cnf

Then print the values to check the csr
openssl req -text -noout -in blogspot.co.uk.csr

Oracle OBIEE passing middle-tier user to database

When querying using OBIEE to Oracle database the connection pool username is passed to the database. To also pass the middle-tier user name you need to set the user identifier on the session. TO do this in OBIEE, open the RPD, edit the connection pool settings and create a new connection script to run at connect time.

Add the following line to the connect script -

call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')

This will then be available as the CLIENT_ID attribute when querying session history and also be available in the audit trail on queries.
EG. Select sid, client_identifier from v$session

Using SSH to forward ports

Ports on remote machines can be forwarded over a SSH tunnel using the ssh command line tool. Using the ssh config file it is possible to set up many ports to be forwarded at the same time running one command. Putty can be used to achieve similar results on Windows.

To forward a port the syntax is the ssh command followed by the local port then the local host then remote port and host
ssh -L 8080:me.local.org:80 myuser@s1.remote.org

To forward many ports at once created a config file in ~/.ssh/config as follows

host localme
  HostName s1.remote.org
  User myuser
  LocalForward 8080 me.local.org 80
  LocalForward 8443 me.local.org 443

Then run ssh localme and give the password for myuser