In this post I will describe how to change Oracle SID using utility DBNEWID. As I can see DBNEWID is available from 9i version but I’ve never heard about it. Till now, I have used procedure where I’m manually editing and re-creating control file.
But I think it’s much better (more...)
This will be just short post about restriction on parallel DML that I wasn't aware of.
Last week we had to perform quick update of several million rows in very big table. Parallel DML has proven to be the best tool to speed up large DML operation.
A DML can (more...)
Last month we had annoying problems caused by stuck Oracle process holding locks and not releasing them. Every few days one process hangs with status “ACTIVE”
and “SQL*Net message from client”
In that state it holds locks and never releases them. As this is active OLTP database, new sessions arrive wanting those locked resources but they could not get them. So after some time you get lots of waiters and if you don’t react quickly maximum number of processes could be reached. Then your database refuses new connections and you're system becomes unresponsive.
To complicate a problem little (more...)
This post could be interesting for those who are running active/passive failover clusters with Oracle Clusterware used for managing resources. In this case owner of the Clusterware installation is not the same as owner of the Oracle RDBMS installation. Among others, Clusterware is assigned for managing listener resource (start,stop,check).
Till 11g version listener logs were by default written in “$ORACLE_HOME/network/log”
but with 11g ADR (Automatic Diagnostic Repository) is introduced and location for listener log is changed. From 11g default location is “$ORACLE_BASE/diag/tnslsnr/<hostname>/listener_<dbname>/trace”
I’ve noticed that when you start listener as owner of Oracle RDBMS everything is working fine. Listener log (more...)
To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:
How to Create a Database Link in Another User’s Schema
Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.
Recently I had situation when I didn’t know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.
In that case my (more...)
Yesterday colleague reported that he’s receiving wrong/odd result from query so we decided to examine closely what's happening. Query had more then 150 lines with inline view, group by clause, several outer joins, function, decodes and cases included. So the first step was to exclude all the stuff that wasn’t important for clearer diagnosis.
Environment: Oracle EE 220.127.116.11 on OEL 5.7
I will post shortened version of the query with fake table names and columns.
FROM table_1 r,
MAX ( DECODE (ri.c_name, 'SPACE', ri. (more...)